Диаграмма Waterfall в Minitab и Excel

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

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

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

Содержание:

  1. Что такое диаграмма Waterfall?
  2. Когда применять, а когда не применять?
  3. Как построить в Minitab?
  4. Как построить в MS Excel?

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

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

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

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

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

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

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

  • Выполнение производственного плана (план, факт и все эффекты, повлиявшие на разницу).
  • Поток наличности – cash-flow (все поступления, все расходы, результат).
  • Анализ простоев (общее время, плановые простои, доступное время, неплановые простои, результат).
  • Возврат или окупаемость инвестиций (вложенные деньги, расходы, прибыль).

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

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

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

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

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

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

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

  • Исходную ситуацию и результат достаточно внести только 1 раз на лист.
  • Все положительные и отрицательные эффекты следует внести дважды.
    • Например, высота “Положительный эффект” составляет 120. При этом следует указать высоту предыдущего столбца и самого эффекта отдельно.
    • Высота столбца “Отрицательный эффект” также составляет 120. Однако в данном случае следует вторым значением указать, на сколько понизился столбец (40), а первым – разницу между исходным значением и итоговым (120-40=80).

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

  • Всем исходным и результирующим значениям, а также значениям эффектов, присваиваем атрибут “a”.
  • Всем остальным значениям – “b”.

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

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

Нажмите ОК.

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

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

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

  1. Выделите красные колонки, затем дважды щелкните на них и в появившемся окне установите отсутствие заливки и рамки фигуры:
  2. Выделите положительный эффект, дважды кликните и установите зеленую заливку:
  3. Выделите отрицательный эффект, дважды кликните и установите красную заливку:
  4. Уберите все лишние подписи и данные, настройте ширину столбцов, добавьте подписи данных, настройте шкалы… Вот теперь диаграмма готова:

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

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

Лист с данными:

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

  • для построения графика;
  • для построения невидимого графика.

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

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

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

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

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

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

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

10.10.2017 / 670 / Загрузок: 0 / DMAgIC / | Теги: диаграмма Waterfall, графический анализ, Minitab
Всего комментариев: 0
avatar
SixSigmaOnline.ru © 2009-2017            Хостинг от uWeb