Показ тенденций изменения данных с помощью спарклайнов
Спарклайны — это небольшие диаграммы в ячейках листа, визуально представляющие данные. С помощью спарклайнов можно показывать тенденции в рядах значений (например, сезонные повышения и спады или экономические циклы) и выделять максимальные и минимальные значения. Чтобы добиться максимального эффекта, спарклайны следует располагать рядом с соответствующими данными.
Добавление спарклайна
- Выделите пустую ячейку в конце строки данных.
- На вкладке Вставка выберите тип спарклайна, например График или Гистограмма.
- Выделите ячейки в строке и нажмите кнопку ОК.
- Другие строки данных? Перетащите его, чтобы добавить спарклайн для каждой строки.
Форматирование спарклайн-диаграммы
- Выделите спарклайн-диаграмму.
- Выберите Спарклайн и выберите один из вариантов.
- Нажмите кнопку График, Гистограмма или Выигрыш/проигрыш, чтобы изменить тип диаграммы.
- Установите флажок Маркеры, чтобы выделить отдельные значения в спарклайн-диаграмме.
- Выберите стиль спарклайна в группе Стиль.
- Нажмите кнопку Цвет спарклайна и выберите цвет.
- Нажмите кнопку Цвет спарклайна и выберите пункт Толщина, чтобы выбрать ширину спарклайна.
- Нажмите кнопку Цвет маркера, чтобы изменить цвет маркеров.
- Если данные содержат положительные и отрицательные значения, нажмите кнопку Ось для отображения оси.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как построить диаграмму «водопад» (waterfall)
Все чаще и чаще встречаю в отчетности разных компаний и слышу просьбы от слушателей на тренингах объяснить как строится каскадная диаграмма отклонений — она же «водопад», она же «waterfall», она же «мост», она же «bridge» и т.д. Выглядит она примерно так:
Издали действительно похожа на каскад водопадов на горной реке или навесной мост — кто что видит 🙂
Особенность такой диаграммы том, что:
- Мы наглядно видим начальное и конечное значение параметра (первый и последний столбцы).
- Положительные изменения (рост) отображаются одним цветом (обычно зеленым ), а отрицательные (спад) — другим (обычно красным ).
- Иногда в диаграмме могут присутствовать ещё и столбцы промежуточных итогов ( серые , приземленные на ось Х столбцы).
В повседневной жизни такие диаграммы используются обычно в следующих случаях:
- Наглядное отображение динамики какого-либо процесса во времени: потока наличности (cash-flow), инвестиций (вкладываем деньги в проект и получаем от него прибыль).
- Визуализация выполнения плана (крайний левый столбик в диаграмме — факт, крайний правый — план, вся диаграмма отображает наш процесс движения к желаемому результату)
- Когда нужно наглядно показать факторы, влияющие на наш параметр (факторный анализ прибыли — из чего она складывается).
Есть несколько способов построения такой диаграммы — всё зависит от вашей версии Microsoft Excel.
Способ 1. Самый простой: встроенный тип в Excel 2016 и новее
Если у вас Excel 2016, 2019 или новее (или Office 365), то построение такой диаграммы не составит труда — в этих версиях Excel такой тип уже встроен по умолчанию. Нужно будет лишь выделить таблицу с данными и выбрать на вкладке Вставка (Insert) команду Каскадная (Waterfall) :
В результате мы получим практически готовую уже диаграмму:
Сразу же можно настроить желаемые цвета заливки для положительных и отрицательных столбцов. Удобнее всего это сделать, выделив соответствующие ряды Увеличение и Уменьшение прямо в легенде и, щёлкнув по ним правой кнопкой мыши, выбрать команду Заливка (Fill) :
Если нужно добавить в диаграмму столбцы с промежуточными итогами или финальный столбец-итог, то удобнее всего это сделать с помощью функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS) или АГРЕГАТ (AGGREGATE) . Они посчитает накопленную с начала таблицы сумму, исключив при этом из нее выше расположенные аналогичные итоги:
В данном случае, первый аргумент (9) — это код математической операции суммирования, а второй (0) заставляет функцию не учитывать в результатах уже вычисленные итоги за предыдущие кварталы.
После добавления строк с итогами останется выделить на диаграмме появившиеся итоговые колонки (сделать два последовательных одиночных щелчка по столбцу) и, щёлкнув правой кнопкой мыши, выбрать команду Установить в качестве итога (Set as total) :
Выбранный столбец «приземлится» на ось Х и автоматически поменяет цвет на серый.
Вот, собственно, и всё — диаграмма-водопад готова:
Способ 2. Универсальный: невидимые столбцы
Если у вас Excel 2013 или более древние версии (2010, 2007 и т.д.), то описанный выше способ вам не подойдёт. Придется идти обходным путем и выпиливать недостающую каскадную диаграмму из обычной гистограммы с накоплением (суммированием столбиков друг на друга).
Хитрость тут заключается в использовании прозрачных столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:
Для построения такой диаграммы нам потребуется добавить к исходным данным еще несколько вспомогательных колонок с формулами:
- Во-первых, нужно разделить наш исходный столбец, выделив положительные и отрицательные значения в разные колонки с помощью функции ЕСЛИ (IF) .
- Во-вторых, нужно будет добавить перед сделанными столбцами колонку Пустышки, где первое значение будет 0, а начиная со второй ячейки формулой будет вычисляться высота тех самых прозрачных подпирающих столбцов.
После этого останется выделить всю таблицу кроме исходного столбца Поток и создать обычную гистограмму с накоплением через Вставка — Гистограмма (Insert — Column Chart) :
Если теперь выделить синие столбцы и сделать их невидимыми (по ним правой кнопкой мыши — Формат ряда — Заливка — Нет заливки), то мы как раз и получим то, что требуется.
В плюсах подобного способа — простота. В минусах — необходимость считать вспомогательные колонки.
Способ 3. Если уходим в минус — всё сложнее
К сожалению, предыдущий способ адекватно работает только для положительных значений. Если хотя бы на каком-то участке наш водопад уходит в отрицательную область, то сложность задачи возрастает в разы. В этом случае необходимо будет формулами просчитать каждый ряд (пустышки, зеленые и красные) отдельно для отрицательной и положительной частей:
Чтобы не сильно мучиться и не изобретать велосипед, готовый шаблон для такого случая можно скачать в заголовке этой статьи.
Способ 4. Экзотический: полосы повышения-понижения
Этот способ основан на использовании специального малоизвестного элемента плоских диаграмм (гистограмм и графиков) — Полос повышения-понижения (Up-Down Bars) . Эти полосы попарно соединяют точки двух графиков, чтобы наглядно показать какая из двух точек выше-ниже, что активно используется при визуализации план-факта:
Легко сообразить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же «водопад».
Для такого построения нам потребуется добавить к нашей таблице еще два дополнительных столбца с простыми формулами, которые расчитают положение двух требуемых невидимых графиков:
Для создания «водопада» нужно выделить столбец с месяцами (для подписей по оси Х) и два дополнительных столбца График 1 и График 2 и построить для начала обычный график через Вставка — График (Insert — Line Сhart) :
Теперь добавим к нашей диаграмме полосы повышения-понижения:
- В Excel 2013 и новее для этого необходимо выбрать на вкладке Конструктор команду Добавить элемент диаграммы— Полосы повышения-понижения (Design — Add Chart Element — Up-Down Bars)
- В Excel 2007-2010 — перейти на вкладку Макет — Полосы повышения-понижения (Layout — Up-Down Bars)
Диаграмма после этого начнёт выглядеть примерно так:
Осталось выделить графики и сделать их прозрачными, щелкнув по ним по очереди правой кнопкой мыши и выбрав команду Формат ряда данных (Format series) . Аналогичным образом можно изменить и стандартные, весьма убого выглядящие, чёрно-белые цвета полос на зелёные и красные, чтобы получить в итоге более приятную картинку:
В последних версиях Microsoft Excel ширину полос можно изменить, щёлкнув по одному из прозрачных графиков (не по полосам!) правой кнопкой мыши и выбрав команду Формат ряда данных — Боковой зазор (Format series — Gap width) .
В старых версиях Excel для такого исправления приходилось использовать команду на Visual Basic:
- Выделите построенную диаграмму
- Нажмите сочетание клавиш Alt + F11 , чтобы попасть в редактор Visual Basic
- Нажмите сочтетание клавиш Ctrl + G , чтобы открыть панель прямого ввода команд и отладки Immediate (обычно она расположена внизу).
- Скопируйте и вставьте туда вот такую команду: ActiveChart.ChartGroups(1).GapWidth = 30 и нажмите Enter :
При желании можно, конечно, поиграться со значением параметра GapWidth, чтобы добиться нужной величины зазора:
Ссылки по теме
- Как в Excel построить диаграмму-шкалу (bullet chart) для визуализации KPI
- Новые возможности диаграмм в Excel 2013
- Как в Excel создать интерактивную «живую» диаграмму
Диаграмма темпов роста в Excel
Как в Excel создать диаграмму с динамикой темпов роста, где изменения данных показаны стрелками?
Все просто: рисуем столбцы и добавляем к ним полосы повышения и понижения. Плюс рисуем графики с накоплением — первый для роста, второй — уровень подписи.
Подготовили для вас подробные пошаговые инструкции. Решили, что вам будет удобнее сразу на листе Excel повторять все шаги и строить диаграмму.
Скачивайте и применяйте!
Предположим, нам нужно показать динамику темпов роста выручки. Чтобы построить диаграмму, потребуется несколько простых шагов.
Если тема про диаграммы вам интересна, смотрите статьи:
- Диаграммы Excel для презентаций
- Необычные диаграммы с точками
- Необычные диаграммы с областями
- Необычные пузырьковые и точечные диаграммы
- Диаграммы с группировкой
- Динамика плана и факта
- Нестандартные диаграммы Excel
Шаг 1. Добавить вычисления.
Добавим в таблицу строку «подписи» — с суммой немного больше исходного значения.
И строку «рост», где будет рассчитан прирост выручки к предыдущему периоду.
В первой колонке проставляем #Н/Д для того, чтобы не значения этого столбца не выводились в диаграмме.
Шаг 2. Создать диаграмму.
Чтобы создать диаграмму, выделите таблицу и выберите в меню Вставка → Гистограмма → Гистограмма с накоплением.
Шаг 3. Рост и подписи превратить в график
Выделите диаграмму мышкой, затем перейдите в меню Конструктор → Изменить тип диаграммы. Выберите тип диаграммы «Комбинированная». Для данных «подписи» и «рост» задаем тип диаграммы «График с накоплением». Благодаря этому график с малыми значениями наложится на график с большими значениями.
Шаг 4. Добавить подписи для линии роста
Добавляем подписи для линии роста: выделите на диаграмме линию роста.
Выберите в меню Конструктор → Добавить элемент диаграммы → Метки данных → выбираем Слева.
Делаем линии «рост» и «подписи» на диаграмме невидимыми: выделите линию правой кнопкой мышки, нажимаем Формат ряда данных. Назначаем тип линии = Нет линий.
Шаг 5. Добавить линии ряда данных
Чтобы добавить линии между столбцами, выделите столбцы гистограммы.
Перейдите в меню Конструктор → Добавить элемент диаграммы → Линии → Линии ряда данных.
* Если такая линия не появилась, проверьте, правильный ли у вас тип диаграммы — должна быть Гистограмма с накоплением.
Как создать диаграмму темпов роста в Microsoft Excel
Диаграмма с темпами роста показателей позволяет пользователю электронной таблицы Microsoft Excel отследить последние изменения и их качество. Визуальная демонстрация считается куда более наглядной, чем текстовая в виде функций, поэтому и пользуется такой популярностью. В этой статье я покажу два метода формирования соответствующей диаграммы на основе простой таблицы.
Создание таблицы с данными
Сначала вам понадобится сама таблица, где есть хотя бы две суммы за определенный диапазон, которые и сравниваются между собой. При этом нужно вычислить процентную разницу между ними, чтобы оценивать динамику роста. Если такой таблицы у вас еще нет и вы не знаете, как ее создать, обратитесь к руководству по ссылке ниже.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Вариант 1: Создание рекомендуемой диаграммы
Алгоритмы, присутствующие в Экселе, чаще всего корректно распознают диапазоны данных и отталкиваются от них при создании различных диаграмм. Я рекомендую воспользоваться как раз предложенным вариантом, если вы не хотите тратить время на ручную настройку графиков.
- Сначала выделите необходимую таблицу, затем перейдите на вкладку «Вставка».
- На ней нажмите кнопку «Рекомендуемые диаграммы».
- Если у вас примерно такая же таблица, появится первый рекомендуемый вариант с названием «Гистограмма с группировкой». Она вполне подойдет для того, чтобы сравнить показатели за несколько месяцев и узнать прирост или убыток в процентах.
- После двойного щелчка диаграмма автоматически добавляется на лист и доступна для дальнейшего взаимодействия. Как видно, практически все значения по умолчанию отображаются корректно, нужно только поменять название самой диаграммы.
- Однако не видно точного числа процентов, что не позволяет полностью оценить динамику. Для исправления ситуации нажмите по графику с процентами правой кнопкой мыши и из контекстного меню выберите пункт «Добавить подписи данных».
- Вы увидите, что каждая точка теперь будет отмечена процентами, даже если они имеют минусовое значение. Теперь в полной мере можно получить визуальное представление о том, насколько характерной является динамика роста за конкретный промежуток времени по разным показателям.
Однако не всегда Excel предлагает именно эту диаграмму, что может быть связано с небольшими отклонениями в самом формате таблицы. Если вы не нашли в списке рекомендуемых подходящий визуальный вариант, приступите к чтению следующей инструкции.
Вариант 2: Ручное создание диаграммы
Необходимая для отслеживания динамики роста диаграмма является комбинированной, поскольку она включает гистограмму с отображением сумм за определенный промежуток времени, а также график, показывающий проценты, их повышение и падение в сравнении с разными месяцами или суммами. Для создания используется несколько простых настроек, которые вы и увидите далее.
- Как обычно, сначала выделите таблицу для создания диаграммы из нее, затем перейдите на вкладку «Вставка» и нажмите кнопку «Рекомендуемые диаграммы».
- В новом окне перейдите на вкладку «Все диаграммы» и выберите последний вариант – «Комбинированная».
- Если у вас есть суммы чисел для сравнения, понадобится сделать эти ряды гистограммами с накоплением, чтобы обеспечить максимально правильное отображение значений.
- Сделайте это для всех рядов, а затем посмотрите, как это будет представлено на самой диаграмме. Как видно, общая сумма и соотношение для каждого месяца предельно наглядны.
- Для динамики, которая отражается в процентах, нужно выбрать линейный график. В идеале – «График с маркерами», чтобы возле каждого значения в процентах была точка для большей наглядности.
- Если проценты не видны так, как это показано на скриншоте ниже, поставьте напротив имени ряда галочку «Вспомогательная ось», после чего подтвердите добавление диаграммы в таблицу.
- Ознакомьтесь с ней и решите, подходит ли она для отображения темпов и динамики роста.
- Для отображения процентов возле каждой точки динамики нужно добавить подписи данных, выбрав соответствующий вариант в контекстном меню.
- На этом формирование диаграммы завершено. Она должна иметь примерно такой вид, как на следующем изображении.
В этой статье мы разобрались с тем, как создать не просто обычную диаграмму, а сделать ее комбинированной, с отображением всех входящих значений и их связок. Это поможет вам разобраться с визуальным представлением темпов и динамики роста в определенных областях за конкретный период времени. Используйте мою инструкцию и не стесняйтесь модернизировать диаграмму, если придумали, как можно ее улучшить.