Как построить диаграмму водопад в excel
Прародителем водопадной диаграммы считают американского ученого-информатика Уинстона Уокера Ройса, директора Lockheed Software Technology Center в Остине (штат Техас, США), пионера в области разработки программного обеспечения. В августе 1970 года в журнале ІЕЕЕ он опубликовал статью «Управление разработкой крупных программных систем», где показал модель для разработки программного обеспечения похожую на каскадную. Лишь в 1976 году ученые Томас Белл и Томас Тэйер дали ей название Waterfall.
Использовать водопадную диаграмму не для разработки ПО, а именно для анализа и визуализации в презентациях для клиентов впервые начала консалтинговая фирма McKinsey & Company, специализирующаяся в стратегическом менеджменте.
Специальных инструментов и приложений для создания диаграмм тогда ещё не было, всё рисовалось и анализировалось вручную. Сейчас же уйма способов создать водопадную диаграмму. Как с помощью обычного Excel, так и применяя специальные плагины и надстройки.
Например, компания Apple с помощью водопадной диаграммы показывает, за счёт чего изменился объем продаж, какие продуктовые линейки повлияли на снижение выручки, а какие, наоборот, были более востребованными в конкретный период времени. В результате анализа становится понятно, над чем стоит более усердно поработать, чтобы сменить минус на плюс.
По статистике доход от продаж за второй квартал 2020 года по сравнении с тем же периодом 2019 года практически не изменился – 58,015 млрд. долларов против 58,313 млрд. соответственно. Рост составил всего лишь 0,5%.
Если взять данные конкретно по каждой продуктовой линейке, можно увидеть, что уровень продаж снизился на 6,7 % у iPhone, на 10,3 % – у iPad, на 2,9% – у Mac. Зато результат от продаж прочих устройств, таких как Apple TV, Apple Watch, Beats, iPod и фирменных аксессуаров вырос на 22,5%, а сервисов на 16,6%. За счет этого итоговая сумма в плюсе, хоть и незначительном.
Исследование влияния промежуточных показателей называют факторным анализом, для его визуализации и применяют водопадную диаграмму.
Основные идеи факторного анализа разработаны английским психологом Ф. Гальтоном. В настоящее время этот метод широко используется не только в психологии, но и в нейрофизиологии, социологии, политологии, экономике, статистике и других науках.
Задача факторного анализа выявить причины (факторы), которые влияют на фактические результаты промышленной, хозяйственной и финансовой деятельности.
Взаимосвязь факторов и результатов обычно представляют в виде математического расчета. Численно определяют, какие показатели и на сколько провоцируют рост, а какие – падение.
Чаще всего метод используют для анализа выполнения производственных планов, инвестиций и финансовых потоков.
Давайте рассмотрим еще один простой пример факторного анализа. Допустим, есть некое значение плановой прибыли – 140 тыс. рублей. На конец года фактический доход оказался 171 тыс. рублей.
Первый и последний столбцы – это план и факт. Промежуточные – факторы влияющие на итог. Зеленым цветом изображены показатели роста, красным – снижения.
Как видим, из-за роста курса доллара подскочили цены на комплектующие, также значительно выросли прочие расходы. Но за счет увеличения объемов выпуска продукции и открытия новых филиалов, которые принесли большую прибыль, расширения линейки товаров и роста спроса на новую продукцию – фактический доход на конец года превысил плановый.
Согласитесь, цифры гораздо проще воспринимаются визуально, в виде диаграммы.
Простые каскадные диаграммы можно построить в обычном Excel или использовать надстройку Think-cell. Для продвинутых отчётов и больших объемов данных гораздо удобнее работать с платформой Power BI.
В Exсel более полусотни различных диаграмм, но самую популярную водопадную вы найдете лишь в Microsoft Office 2016 года и старше.
А как же быть со старыми версиями? Можно обойтись подручными средствами.
Каскадная диаграмма строится на основе обычной классической гистограммы. Эффект моста достигается вертикальными плюсующимися столбцами, нижний ряд которых делается прозрачным. Не совсем удобно, но по-другому никак.
В новых версиях всё гораздо проще. В функционал уже добавлена водопадная диаграмма. Для построения достаточно выделить диапазон данных в таблице, на основе которых необходимо построить график. На главной панели выбрать значок водопадной диаграммы.
На своё усмотрение изменить цвет столбцов, отредактировать, подправить легенду. И каскадная диаграмма готова.
Но, к сожалению, Microsoft все равно не смог обеспечить удобство использования новых возможностей. Чтобы установить итоговые столбцы, задать масштаб, подогнать размер нужно сделать много лишних телодвижений.
Для построения сложных сводных графиков всё-таки гораздо удобнее использовать дополнительные приложения и надстройки.
Сотни крупных компаний для создания каскадных диаграмм используют Think-cell – инструмент, который позволяет без особых усилий строить двухмерные графики в PowerPoint прямо на странице с презентацией из данных Excel. Профессионально, максимально точно и в соответствии со стандартами компании. При этом делать это можно в 3-5 раз быстрее, чем в обычном Excel.
Первый пакет надстроек презентовали немецкие разработчики в 2003 году. Их клиентом стал филиал ведущей консалтинговой фирмы по стратегическим вопросам. На сегодняшний день более 800 000 клиентов пользуются платной версией Think-cell.
Think-cell имеет простую настройку параметров. Диаграммы синхронизируются с данными из Excel. Подписи и суммарные значения распознаются автоматически. Фигуры и текстовые блоки легко связываются между собой, превращаясь в сложную диаграмму.
Но как же быть с обширными массивами данных, сводными таблицами и необходимостью быстро и безболезненно формировать отчеты? Для продвинутых аналитиков и компаний, которые ценят свое время, в Microsoft создали аналитическую платформу Power BI. Комплекс программ и сервисов, которые позволяют быстро выгружать информацию из любых источников, приводить её к единой модели данных и на их основе производить необходимые расчеты, строить графики и создавать отчёты и дашборды.
Работа над проектами доступна с любого устройства и одновременно для нескольких пользователей. Все функции автоматизированы. Можно кликнуть на любой показатель в отчёте, посмотреть, как он изменится, и проанализировать информацию под другим углом.
Водопадную диаграмму в Power BI построить достаточно быстро. Данные из таблицы Excel необходимо загрузить на платформу. Далее нажать на диаграммы на панели «Визуализации» и определить некоторые значения. Добавить в область «Распределение» фактор, по которому нужно произвести распределение – и детализация готова.
У новичков могут возникать сложности при подготовке и хранении данных. На курсе мы подробно разбираем все нюансы. Я рассказываю, как уменьшить трудозатраты на подготовку, каким вариантом водопадных диаграмм лучше воспользоваться в конкретной ситуации –строить на платформе Power BI или использовать дополнительные интерфейсы Simple Waterfall и Zebra BI.
Диаграмма Водопад в EXCEL
history 29 марта 2015 г.
- Группы статей
- Пользовательский формат
- Диаграммы и графики
Построим диаграмму Водопад, которая используется для визуализации динамики показателя (за одинаковые периоды), а также для демонстрации влияния различных факторов на показатель . Диаграмма Водопад также часто называют "каскадной диаграммой".
Сначала научимся создавать диаграмму Водопад только для положительных значений (проще), затем рассмотрим более общий случай (сложнее).
Диаграмму Водопад построим в EXCEL 2010 с использованием стандартной диаграммой типа Гистограмма с накоплением .
Примечание : Начиная с версии 2016 года в EXCEL имеется стандартная каскадная диаграмма. Подробнее о ее построении можно прочитать ]]> в статье на сайте Microsoft ]]> .
Диаграмма водопад. Динамика показателя (+)
Пусть дана таблица со значениями показателя в каждый период (остатки на складе на конец месяца, столбец В). Предполагается, что остатки на складе не могут быть отрицательными.
Сначала вычислим изменения за период. Увеличения и уменьшения разнесем по разным столбцам. Это нам позволит выделить цветом разнонаправленные изменения.
Также нам потребуется служебный столбец, который будет служить невидимой основой для столбцов-изменений (см. файл примера Лист Больше0 ).
Будем использовать Гистограмму с накоплением . В качестве рядов данных используем созданные выше столбцы (C, D, E).
Изменим по своему усмотрению цвета столбцов и зазор между ними. Добавим подписи данных. Чтобы не отражались 0 значения используйте пользовательский числовой формат .
Диаграмма водопад. Динамика показателя (+/-)
Используем исходную таблицу, в которой приведено значение прибыли в конце каждого периода (столбец В). Обратите внимание, что в отличие от предыдущего случая, значения прибыли может быть отрицательным. Изменения, которые соответствуют отрицательным значениям, будем выделять на диаграмме более светлым цветом, чтобы подчеркнуть, что эти изменения привели к убыткам.
Вычислим изменение за период (столбец С). Для построения диаграммы также потребуются вспомогательные столбцы (см. файл примера Лист ВсеЗначения ).
В остальном построение диаграммы аналогично предыдущей задаче.
Чтобы вывести подписи данных использован специальный столбец, значения которого выводятся на диаграмме с помощью вспомогательной оси (скрыта). В качестве подписей данных используются не сами значения, а имена категорий (в качестве категорий (ось х) указаны изменения за период).
СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .
Анализ влияния факторов
В отличие от предыдущих 2-х задач, где анализировалось изменение значения показателя за несколько периодов, в этом разделе визуализируем влияние каждого фактора на полученное фактическое значение.
Пусть в начале года было задано плановое значение для прибыли = 140. В конце года было получено значение прибыли = 171. При этом известен вклад каждого из факторов (столбец С).
Используя подходы, указанные в этой статье выше, можно построить диаграмму Водопад для анализа влияния факторов.
Эта диаграмма состоит из 3-х рядов данных. Первый ряд данных включает начальное и конечное значение прибыли, а также невидимые служебные столбцы. Для построения такого ряда проще всего сначала установить для столбцов диаграммы значение Нет заливки , а затем для крайнего правого и левого значений вручную установить нужный цвет (например, синий). Для этого нужно выделить на диаграмме столбцы ряда, через 1 сек выделить левый столбик, изменить его заливку. Затем тоже сделать для последнего столбика. Подробнее см. статью Гистограмма в MS EXCEL с накоплением .
Второй ряд содержит значения положительных отклонений (зеленый цвет), третий ряд — отрицательные (красные столбики). Диаграмма построена в файле примера на листе Факторы .