Что такое диаграмма «Водопад» в Excel и как пользоваться Think-Cell?

Диаграмма «Водопад» в Excel — это инструмент для визуализации данных. Он позволяет отслеживать последовательность изменений, влияющих на начальное и конечное значения. «Водопад» подходит для создания финансовых отчетов, анализа прибыли и убытков и планирования бюджета.

Как читать и использовать диаграмму «Водопад»

• Начальное и конечное значения: используются все листы, указанные между первым и последним именем в ссылке;

• Положительные и отрицательные изменения: отображаются столбцами различных цветов, обычно зеленым для роста и красным для спада;

• Промежуточные итоги: в диаграмму можно добавить столбцы, которые помогут понять, как складывается результат подсчетов. 

Диаграмма Waterfall на практике

Например, вам нужно подсчитать еженедельную выручку от продаж зерненого творога за год. Проблема в том, что среди выгруженных данных не только обычные продажи, но и промоакции, которые нельзя учитывать. В рабочем файле несколько тысяч строк, и его невозможно отсортировать вручную. Диаграмма Waterfall поможет значительно сэкономить время и силы. 

Используем функцию СУММЕСЛИМН, чтобы задать условия для каждой ячейки и посчитать сумму. Например, для первой: найти суммарную выручку по всем видам зерненого творога за последнюю неделю без учета промоакций. Так выглядит синтаксис этой функции:

= СУММЕСЛИМН (Диапазон для суммирования; Диапазон 1 для критерия; Критерий для диапазона 1; Диапазон 2 для критерия; Критерий для диапазона 2; …)

На первый взгляд, не очень дружелюбно. Однако суть формулы проста: диапазон для суммирования в нашем случае — выручка; первым диапазоном для критерия будет дата — выделяем диапазон $H$3:$H$3370; далее идет сам критерий, то. есть. дата K3; теперь выделяем диапазон для второго критерия (нам нужно исключить акции) — в нашем случае это будет диапазон $C$3:$C$3370; и сам критерий «Продажа».

В примере при первоначальной фильтрации можно было бы сразу убрать акции, чтобы написать формулу вдвое короче. Однако это был лишь первый шаг в решении более масштабной задачи, которая требовала дополнительных расчетов на том же листе. И тогда при снятии фильтров появился бы риск, что формулы съедут.

Дополнительные настройки диаграммы

Итоги и промежуточные итоги

Правильная настройка конечных значений в диаграмме «Водопад» помогает точно отобразить результаты. Для их отображения выберите Работа с таблицами — Конструктор и отметьте Строка итогов. Дальше примените функцию СУММ к каждому столбцу. Excel автоматически добавит формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Так вы сможете легко добавлять элементы и сделаете диаграмму информативнее.

Отображение и скрытие соединительных линий

Диаграмму можно сделать более читаемой, если настроить видимость соединительных линий между столбцами. Это особенно важно, когда диаграмма содержит много данных и каждое изменение требует внимания. 

Что такое надстройка Think-Cell для Диаграмма Waterfall

Think-Cell — мощный инструмент для создания презентаций, который предлагает более 40 типов диаграмм и множество визуальных улучшений для данных. С помощью Think-Cell можно быстро делать диаграммы разного уровня сложности. Они получаются более профессиональными, по сравнению со стандартной визуализацией в Excel. Ниже несколько функций надстройки Think-Cell:

1. Добавить на диаграмму стрелки, которые отражают изменение величин в %;

2. Выбрать, в какую сторону повернут график;

3. Обрезать базовые значения на диаграмме, оставив только верхние части, чтобы отразить маленькие изменения.

Построить диаграмму «Водопад» в Excel может быть сложновато, но с помощью Think-Cell процесс станет гораздо проще. Первое время интерфейс будет казаться неуклюжим, однако потом вам будет трудно отказаться от него. Это must-have на любых кейс-чемпионатах.

Например, на gif-анимации ниже я показываю, как за одну минуту сделать каскадную диаграмму. Она визуализирует денежный эффект от предложенных инициатив для страховой компании в чемпионате Oliver Wyman Impact. Дополнительно прокомментировать тут нужно только три момента:

• английская буква «e» суммирует все значения, которые написаны до нее;
• чтобы выделить необходимые столбцы для стрелки, которая показывает изменение в процентах, используйте клавишу Ctrl ;
• иногда клавиша вызова окна Excel не работает, в такой ситуации лучше сохранить файл, закрыть и снова открыть его.

После косметических правок конечный результат может выглядеть следующим образом:

 

---

На своем примере я убедился, что и в учебе, и в работе без продвинутого владения Excel не обойтись. 

Автор статьи: 

Тимофей Миненков — студент МГУ им. М.В. Ломоносова и выпускник онлайн-курса Changellenge >> ToolKit 2018.