Как сделать диаграмму водопад

Как построить диаграмму «водопад» (waterfall)

Особенность такой диаграммы том, что:

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

Способ 1. Самый простой: встроенный тип в Excel 2016 и новее

В результате мы получим практически готовую уже диаграмму:

Сразу же можно настроить желаемые цвета заливки для положительных и отрицательных столбцов. Удобнее всего это сделать, выделив соответствующие ряды Увеличение и Уменьшение прямо в легенде и, щёлкнув по ним правой кнопкой мыши, выбрать команду Заливка (Fill) :

После добавления строк с итогами останется выделить на диаграмме появившиеся итоговые колонки (сделать два последовательных одиночных щелчка по столбцу) и, щёлкнув правой кнопкой мыши, выбрать команду Установить в качестве итога (Set as total) :

Выбранный столбец «приземлится» на ось Х и автоматически поменяет цвет на серый.

Способ 2. Универсальный: невидимые столбцы

Если у вас Excel 2013 или более древние версии (2010, 2007 и т.д.), то описанный выше способ вам не подойдёт. Придется идти обходным путем и выпиливать недостающую каскадную диаграмму из обычной гистограммы с накоплением (суммированием столбиков друг на друга).

Хитрость тут заключается в использовании прозрачных столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:

Для построения такой диаграммы нам потребуется добавить к исходным данным еще несколько вспомогательных колонок с формулами:

К сожалению, предыдущий способ адекватно работает только для положительных значений. Если хотя бы на каком-то участке наш водопад уходит в отрицательную область, то сложность задачи возрастает в разы. В этом случае необходимо будет формулами просчитать каждый ряд (пустышки, зеленые и красные) отдельно для отрицательной и положительной частей:

Чтобы не сильно мучиться и не изобретать велосипед, готовый шаблон для такого случая можно скачать в заголовке этой статьи.

Способ 4. Экзотический: полосы повышения-понижения

Легко сообразить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же «водопад».

Для такого построения нам потребуется добавить к нашей таблице еще два дополнительных столбца с простыми формулами, которые расчитают положение двух требуемых невидимых графиков:

Теперь добавим к нашей диаграмме полосы повышения-понижения:

Диаграмма после этого начнёт выглядеть примерно так:

В старых версиях Excel для такого исправления приходилось использовать команду на Visual Basic:

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

Источник

Как построить диаграмму «водопад» (waterfall)

Особенность такой диаграммы том, что:

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

Способ 1. Самый простой: встроенный тип в Excel 2016 и новее

В результате мы получим практически готовую уже диаграмму:

Сразу же можно настроить желаемые цвета заливки для положительных и отрицательных столбцов. Удобнее всего это сделать, выделив соответствующие ряды Увеличение и Уменьшение прямо в легенде и, щёлкнув по ним правой кнопкой мыши, выбрать команду Заливка (Fill) :

После добавления строк с итогами останется выделить на диаграмме появившиеся итоговые колонки (сделать два последовательных одиночных щелчка по столбцу) и, щёлкнув правой кнопкой мыши, выбрать команду Установить в качестве итога (Set as total) :

Выбранный столбец «приземлится» на ось Х и автоматически поменяет цвет на серый.

Способ 2. Универсальный: невидимые столбцы

Если у вас Excel 2013 или более древние версии (2010, 2007 и т.д.), то описанный выше способ вам не подойдёт. Придется идти обходным путем и выпиливать недостающую каскадную диаграмму из обычной гистограммы с накоплением (суммированием столбиков друг на друга).

Хитрость тут заключается в использовании прозрачных столбцов-подпорок, приподнимающих наши красные и зеленые ряды данных на нужную высоту:

Для построения такой диаграммы нам потребуется добавить к исходным данным еще несколько вспомогательных колонок с формулами:

К сожалению, предыдущий способ адекватно работает только для положительных значений. Если хотя бы на каком-то участке наш водопад уходит в отрицательную область, то сложность задачи возрастает в разы. В этом случае необходимо будет формулами просчитать каждый ряд (пустышки, зеленые и красные) отдельно для отрицательной и положительной частей:

Чтобы не сильно мучиться и не изобретать велосипед, готовый шаблон для такого случая можно скачать в заголовке этой статьи.

Способ 4. Экзотический: полосы повышения-понижения

Легко сообразить, что если убрать линии графиков и оставить на диаграмме только полосы повышения-понижения, то мы получим все тот же «водопад».

Для такого построения нам потребуется добавить к нашей таблице еще два дополнительных столбца с простыми формулами, которые расчитают положение двух требуемых невидимых графиков:

Теперь добавим к нашей диаграмме полосы повышения-понижения:

Диаграмма после этого начнёт выглядеть примерно так:

В старых версиях Excel для такого исправления приходилось использовать команду на Visual Basic:

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

Источник

Как сделать диаграмму водопад

В арсенале MS Excel или Minitab найдется более полусотни различных диаграмм. Тем не менее, одного из самых популярнейших способов визуализации данных – каскадной диаграммы – там не отыскать. Разумеется, можно прибегнуть к помощи специализированных программ или надстроек для MS Excel. Однако красивые и функциональные решения, вроде think-cell, обойдутся недешево, а бесплатные варианты, как plusx, вряд ли можно назвать профессиональным решением.

В любом случае, если вы привыкли обходиться только тем ПО, которое всегда под руками, то эта статья – именно то, что вам нужно. Из нее вы узнаете, как построить каскадную диаграмму даже без наличия дополнительных надстроек в MS Excel и Minitab.

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

Содержание:

1. Что такое диаграмма Waterfall?

Waterfall переводится как водопад. Можно было бы назвать Waterfall-диаграмму графиком водопада, но специалисты из финансовой области скорее знают ее как bridge (мост) или каскадную диаграмму. Хотя не исключено, что вы можете встретить термин “диаграмма водопада”, “диаграмма мост” или “летающие кирпичи”. Как только не называют этот график.

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

К примеру, с помощью диаграммы “водопад” вы можете визуализировать динамику семейного бюджета за определенный период:

2. Когда применять, а когда не применять Waterfall?

Каскадная диаграмма применяется для визуализации изменений какого-либо параметра за определенный период. Используйте этот график, чтобы отразить воздействие всех эффектов (положительных и отрицательных) на конечное значение параметра.

Вот некоторые примеры:

Теоретически, с помощью диаграммы “водопад” можно изобразить любую динамику. К примеру, динамику посещаемости нашего сайта (среднесуточное количество посетителей по месяцам):

Хоть этого никто и не запрещает, но в данном случае лучше воспользоваться графиком временного ряда (Time Series), а не каскадной диаграммой. Дело в том, что главная идея Waterfall-диаграммы в том, чтобы показать, как те или иные факторы повлияли на конечный результат. В случае с посещаемостью сайта, посещаемость в июле 2016 года вряд ли повлияла на посещаемость в декабре того же года или на июль следующего года.

3. Как построить Waterfall-диаграмму в Minitab?

Лично я использую разные способы построения диаграммы “водопад” в MS Excel и Minitab. Это, скорее, дело привычки, нежели удобства или скорости выполнения. Вы можете попробовать оба подхода и выбрать тот, который будет удобнее для вас.

В качестве данных для анализа мы возьмем пример, указанный в начале статьи:

В первую очередь, нам потребуется перенести все цифры на лист:

Весь трюк в том, чтобы верно внести нужные данные:

Чтобы программа могла отличить наши значения, потребуется указать дополнительные атрибуты. В блоге Minitab-а, предлагают это сделать по-своему, но я это делаю несколько по-другому:

Затем в меню Graph выберите Bar chart. В появившемся окне выберите вначале опцию Values from a table, а затем кликните на Stack в рядке One column of values:

Нажмите ОК и укажите в следующем окне переменные в поле Graph variables и атрибуты – в следующем поле. Обратите внимание на очередность указания колонок с атрибутами:

Чтобы поменять синие и красные столбцы местами, кликните дважды по любой колонке (курсор должен находиться именно на колонке, а не просто в любо месте диаграммы) и перейдите на вкладку Chart Options:

Переместите флажок с Bottom of stack на Top of stack, как показано на картинке выше. Нажмите ОК:

Уже ближе к исходному варианту, но понадобится еще немного мануальных настроек:

Как построить Waterfall-диаграмму в MS Excel?

Как я писал выше, я использую разные способы построения диаграммы “водопад” в MS Excel и Minitab. Вы можете применить тот же подход или попробовать другой – из примера выше, с построением диаграммы в Minitab.

Мы используем те же данные, но не будем вносить эффект дважды и указывать дополнительные атрибуты. Вместо этого мы создадим 2 новые колонки:

Перенесем без изменений первое и последнее значения из колонки “Данные” в колонку “График”. Все промежуточные значения рассчитаем по формуле:

В колонке “Невидимка” первое и последнее значения умышленно оставим пустыми. Все промежуточные значения рассчитаем по формуле:

Нам понадобится вот эта диаграмма:

Кликнув по любой колонке дважды, вы откроете новое диалоговое окно. В этом окне установите ползунок перекрытия на 100% и передвиньте ползунок щели, например, на значение 25%:

Закройте окно. Затем выделите ряд “Невидимки” и кликните по нему 2 раза. В открывшемся окне перейдите на вкладку “Заливка” и установите белую заливку:

Тем же способом установите заливку для положительных эффектов зеленой, а для отрицательных – красной. Уберите или добавьте нужные детали, подписи, оси… Диаграмма готова:

Как видите, большинство графиков для этого материала выполнено вторым способом. На мой взгляд, он более гибкий и поддающийся автоматизации. Однако требует от вас применения разных формул для разных случаев (наборов данных). Тем не менее, я надеюсь, что хоть один из способов вам подойдет, а если вы используете свой собственный, то поделитесь им с читателями, используя форму комментариев ниже. Или пришлите мне описание вашего метода, а я опубликую его в блоге нашего сайта.

Источник

Как решать нестандартные задачи с помощью диаграммы «Водопад» и Think-Cell в Excel

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

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

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

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

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

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

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

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

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

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

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

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

Получите карьерную поддержку

Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Подписаться на карьерную рассылку

Подписывайтесь на рассылку и получайте карьерные советы — от выбора индустрии и компании до лайфхаков по самоорганизации и развитию коммуникативных навыков.

Источник