(Stacked Column).
У нее есть еще один вариант — нормированная гистограмма с накоплением (100% Stacked Column). Здесь все столбцы одинаковой высоты и показывается только распределение по составляющим в каждом месяце, но не абсолютные значения.
Для визуализации динамики также можно использовать график (Line).
В графике может быть и несколько рядов данных. А если нужно показать разницу между рядами, используйте полосы повышения и понижения (Up/Down Bars). Кстати, это может быть еще одним вариантом для анализа «план — факт».
Полосы повышения и понижения можно выделять и форматировать, как другие объекты. Здесь они сделаны красными и зелеными исходя из того, что перевыполнение плана — это хорошо.
Подойдет для динамики и вариант диаграммы «с областями» (Area) — в этом типе закрашивается площадь под графиком.
Кстати, если значений много (нужно показать динамику показателя за длинный период), можно обойтись вообще без диаграммы — подойдет и условное форматирование. Можно сделать столбец, в котором просто будут дублироваться данные, а потом вставить гистограммы условного форматирования. Можно убрать сами числа в ячейках с гистограммами — мы обсуждали, как это делать, выше в главе про условное форматирование.
Позиционное сравнение: когда нам нужно визуализировать несколько значений, но делать акцент не на их долях (как в круговой диаграмме), а на величине каждого относительно других, подходит линейчатая диаграмма (Bar).
Линейчатая диаграмма, как и гистограмма, бывает и с накоплением, и нормированной.
А еще здесь тоже можно использовать гистограммы условного форматирования.
Комбинированная диаграмма (Combo)
Не все данные можно сравнить в рамках одной диаграммы с одной осью. Нередко данные отличаются на один или несколько порядков, и такая разница не позволяет их показать с сохранением масштаба на одном графике.
В следующем примере продажи — это десятки тысяч, а численность — несколько сотен сотрудников. И численность просто «размазывается» на дне диаграммы из-за своего масштаба.
В таких случаях используются диаграммы с двумя осями, когда каждый из рядов данных показывается в своем масштабе со своей осью. Подойдет в случаях, когда вам нужно показать на одной диаграмме динамику какого-то абсолютного показателя (десятки тысяч штук или миллионы рублей выручки, например) и процентного показателя (какой-либо коэффициент, уровень выполнения плана и так далее) или еще одного абсолютного, но не сопоставимого по величине.
В таких случаях выбирайте тип диаграммы «Комбинированная» (Combo), который позволяет в рамках одной диаграммы использовать две оси (для этого нужно выбрать «Вспомогательная ось» для одного из рядов данных, и тогда каждый из них будет отображаться в своем масштабе).
Комбинированная диаграмма позволяет сравнивать десятки тысяч проданных единиц и десятки сотрудников в одном месте в динамике, несмотря на различие в порядке чисел
Можно изменить и тип существующей диаграммы, если щелкнуть правой кнопкой мыши и выбрать в контекстном меню «Изменить тип диаграммы» (Change Chart Type).
Гистограмма и график — это не единственная возможная комбинация. Можно комбинировать разные типы диаграмм, например «С областями» (график с заливкой всей площади под ним) и гистограмму.
Для упрощения восприятия диаграммы с двумя осями можно поменять цвет шрифта каждой оси на цвет, соответствующий ряду данных, который отображается по этой оси.
Каскадная диаграмма (Waterfall)
Эту диаграмму также называют водопадом (waterfall), факторной диаграммой. Ранее ее нужно было собирать с помощью формул и вспомогательных конструкций, а с Excel 2016 есть встроенный тип диаграммы.
Обычно ее используют, чтобы показать вклад каждого показателя или направления бизнеса или другой составляющей в общий результат. Например, если мы продавали три типа штук и у нас был план по каждой, отчетный период закончился и мы анализируем выполнение общего плана, мы можем посмотреть, как на этот результат повлиял каждый продукт.
Для построения каскадной диаграммы нужно подготовить данные: один столбец с подписями и один с данными (в нашем примере исходные данные в диапазоне A1:C5, а данные для диаграммы в E2:F6), в котором будет первоначальный показатель (в нашем случае план), далее влияние каждого показателя (мы вычитаем из факта план по каждой категории, чтобы вычислить, как она повлияла на общий результат, — например, по штуковинам вы недополучили 80 000) и, наконец, итоговый результат (общий факт).
После построения диаграммы будет следующая картина: каждое положительное значение будет считаться увеличением, отрицательное — уменьшением.
Мы можем сделать любое значение итогом — такие значения отсчитываются от нуля на оси, а не от предыдущего значения, и у них другой цвет. В нашем случае итоги — это план и факт (можно делать и предыдущие итоги, если необходимо). Чтобы сделать точку данных итогов, щелкаем по ней правой кнопкой и в контекстном меню выбираем «Установить в качестве итога» (Set as Total).
Теперь плановый и фактический результат — серого цвета, оба отсчитываются от нуля, и мы видим, как шли от запланированной выручки к фактической и как каждая из трех категорий (выполнение плана по ней) повлияла на результат.
Цвета увеличения и уменьшения можно сделать более уместными, например зелеными и красными. Проще всего изменить их цвета, нажав на соответствующий элемент на легенде.
Можно менять и цвет итоговых значений.
После изменений цветов легенду можно и удалить: все-таки при правильных цветах догадаться, что такое «улучшение», а что «уменьшение», несложно (к тому же об этом говорит и положение точек данных).
Эта диаграмма есть и в Google Таблицах.
Воронка (Funnel)
Чтобы анализировать конверсию (то, сколько клиентов остается на каждом этапе, например), пригодится новая (с Excel 2019) диаграмма «Воронка». Что-то вроде линейчатой, но с выравниванием по центру. Можно выделить отдельную точку данных цветом, если вы хотите сделать акцент на каком-то этапе «Воронки».
Дерево (Treemap)
Еще одна относительно новая диаграмма, которая позволяет показать структуру, но несколько иначе, чем стандартная круговая диаграмма, — это «Дерево» (Treemap). Она появилась в Excel 2016.
Здесь нельзя вставить в подписи доли и значения, как в круговой диаграмме, — только значения.
Так что, если вы хотите выводить именно доли, их придется рассчитать в ячейках и построить диаграмму на основе этих ячеек.
Парето (Pareto)
Помните про принцип Парето? «20% усилий дают 80% результата».
Если хотите визуально проанализировать данные и посмотреть, какой процент клиентов / филиалов / каналов продаж / продуктов и т. д. дает какой процент результата, можно использовать эту диаграмму, появившуюся в Excel 2019.
Можно построить эту диаграмму по данным из предыдущего примера.
Каналы будут визуализированы в виде столбиков (гистограммы) и отсортированы по убыванию. Сверху будет оранжевая линия (график), показывающая накопительный вклад.
Файл с примером: Сводные диаграммы.xlsx
Сводная диаграмма подходит для случаев, когда значений много и их невозможно визуализировать на нижнем уровне детализации, но есть признак, по которому можно сгруппировать данные, — тогда визуализация уже приобретает смысл.
Например, в следующем случае у нас несколько тысяч строк с данными об остатках.
Любая диаграмма, построенная непосредственно на основе этих данных, будет выглядеть абсурдно.
Но если сгруппировать данные (например, по городу или категориям товаров), это приобретает смысл. Можно сначала вставить сводную таблицу, настроить тот отчет, который вы хотите визуализировать, а потом построить сводную диаграмму. Можно сразу вставить сводную диаграмму. В любом случае будет построена сводная таблица тоже, без нее сводной диаграммы быть не может.
Если уже есть сводная таблица, можно вставить диаграмму (там же на ленте на вкладке «Вставка»).
Ну а самым быстрым способом будет опция «Рекомендуемые сводные таблицы» (она есть в Excel с 2013-й версии).
Если в данных есть что группировать, то Excel сам предложит в списке рекомендуемых сводные диаграммы.
После нажатия ОК вы получите сводную таблицу и диаграмму.
Сводные таблицы и диаграммы жестко связаны друг с другом: так, если вы отфильтруете данные в таблице, это тут же отразится на диаграмме, и наоборот. Если вы будете добавлять поля в строки, столбцы, фильтры или область значений сводной — они появятся и на диаграмме. И наоборот.
В следующем примере отсортировали данные в таблице — это привело к упорядочиванию и на диаграмме.
Обратите внимание, что областям сводных таблиц строго соответствуют области сводной таблицы:
— ось X (горизонтальная в случае графиков, гистограмм) — это область строк сводной таблицы;
— ось Y (вертикальная) — это область значений сводной таблицы;
— у каждого значения в области столбцов сводной таблицы свой график / столбец / сегмент столбца (в случае гистограммы или линейчатой диаграммы с накоплением, как на скриншоте выше).
То есть для следующей таблицы, где в столбцах годы (динамика), мы не получим желаемый результат на диаграмме (где временная ось будет горизонтальной) сразу.
Диаграмма будет выглядеть так:
Придется поменять столбцы и строки местами: либо в настройках полей сводной таблицы, либо на вкладке «Конструктор» (при выделенной диаграмме) — там есть опция «Строка/столбец» (Switch Row / Column), как и у обычных диаграмм.