Что такое диаграмма «Водопад» в 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:
-
Добавить на диаграмму стрелки, которые отражают изменение величин в %;
-
Выбрать, в какую сторону повернут график;
-
Обрезать базовые значения на диаграмме, оставив только верхние части, чтобы отразить маленькие изменения.
Построить диаграмму «Водопад» в Excel может быть сложновато, но с помощью Think-Cell процесс станет гораздо проще. Первое время интерфейс будет казаться неуклюжим, однако потом вам будет трудно отказаться от него. Это must-have на любых кейс-чемпионатах.
Например, на gif-анимации ниже я показываю, как за одну минуту сделать каскадную диаграмму. Она визуализирует денежный эффект от предложенных инициатив для страховой компании в чемпионате Oliver Wyman Impact. Дополнительно прокомментировать тут нужно только три момента:
- английская буква «e» суммирует все значения, которые написаны до нее;
- чтобы выделить необходимые столбцы для стрелки, которая показывает изменение в процентах, используйте клавишу Ctrl ;
- иногда клавиша вызова окна Excel не работает, в такой ситуации лучше сохранить файл, закрыть и снова открыть его.
После косметических правок конечный результат может выглядеть следующим образом:
На своем примере я убедился, что и в учебе, и в работе без продвинутого владения Excel не обойтись.
Автор статьи:
Тимофей Миненков — студент МГУ им. М.В. Ломоносова и выпускник онлайн-курса Changellenge >> ToolKit 2018.
Теги
