Статьи
Как решать нестандартные задачи и строить диаграмму «Водопад» в Exсel
bg

Как решать нестандартные задачи и строить диаграмму «Водопад» в Exсel

Как решать нестандартные задачи и строить диаграмму «Водопад» в Exсel

Эту функцию используют, когда нужно суммировать данные, удовлетворяющие сразу нескольким условиям. Я впервые столкнулся с ней, когда мне нужно было выполнить весьма специфичную задачу: подсчитать еженедельную выручку от продаж зерненого творога в течение года. Проблема заключалась в том, что среди выгруженных данных оказались не только обычные продажи, но и промоакции, которые не нужно было учитывать. Рабочий файл состоял из нескольких тысяч строк, поэтому отсортировать вручную было невозможно.

1595_oooo.plus.png

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


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

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

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

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

СУММЕСЛИМН.gif

Диаграмма «водопад» с помощью надстройки Think-Cell

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

  1. Добавить на диаграмму стрелки, которые отражают изменение величин в %.
  2. Выбрать, в какую сторону повернут график.
  3. Обрезать базовые значения на диаграмме, оставив только верхние части, чтобы отразить маленькие изменения.

Первое время TC может показаться неуклюжим из-за его интерфейса, однако потом вам будет трудно отказаться от него. Это must-have на любых кейс-чемпионатах.

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

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

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

 


Водопад-2.gif

На своем примере я убедился, что и в учебе, и в работе без продвинутого владения Excel не обойтись. Поэтому всем, кто работает с числами и данными, советую обратить внимание на курс «Excel для бизнеса» от Changellenge >> ToolKit. На нем вы пополните свой арсенал такими же полезными функциями как и те, о которых я рассказал в статье.

Прокачаться в Excel >>