Создание диаграммы Водопад с помощью надстройки MS EXCEL Waterfall Chart Studio

Диаграмма Водопад (или каскадная диаграмма) является стандартным средством визуализации изменения финансовых показателей компании. В этой статье для построения каскадной диаграммы воспользуемся надстройкой Waterfall Chart Studio от компании fincontrollex.com.

До версии MS EXCEL 2016 года каскадная диаграмма или диаграмма Водопад отсутствовала среди встроенных типов диаграмм. К сожалению, не все пользователи довольны ее реализацией в MS EXCEL 2016, поэтому многие продолжают самостоятельно строить этот тип диаграмм или приобретать специализированные надстройки.

В этой статье сначала кратко познакомимся с некоторыми бизнес-задачами, для которых удобно использовать каскадные диаграммы. Затем рассмотрим построение этого вида диаграмм с помощью надстройки Waterfall Chart Studio от компании ]]>fincontrollex.com]]>.

Примечание: Чтобы оценить трудоемкость построения диаграммы Водопад с помощью встроенных типов диаграмм MS EXCEL можно посмотреть мою статью «Диаграмма Водопад». Скажу сразу, если Вы не имеете опыта построения и настройки диаграмм с несколькими рядами данных, то самостоятельно построить каскадную диаграмму будет не просто.

Виды Каскадных диаграмм

Каскадная диаграмма хорошо известна специалистам, которые занимаются анализом экономических показателей компании.

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

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

  • Красным цветом выделяются факторы, которые привели к негативной динамике показателя (в нашем случае привели к снижению выручки);
  • Зеленым цветом выделяются факторы, которые оказали позитивное влияние на показатель (в нашем случае привели к увеличению выручки).

Также каскадная диаграмма может быть использована для анализа структуры показателя. Например, с помощью приведенной ниже диаграммы показана структура выручки от реализации.

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

Наконец, с помощью диаграммы Водопад можно проанализировать динамику интересующего показателя.

Как видно из диаграммы выше, вклад каждого месяца в прибыль был различным в течение года, например, из-за сезонности продаж.

Теперь приступим к построению диаграммы Водопад с помощью надстройки Waterfall Chart Studio от компании ]]>fincontrollex.com]]>. Надстройка работает начиная с версии MS EXCEL 2010.

Сначала установим и активируем надстройку, затем научимся ее использовать в версии MS EXCEL 2010.

Надстройка Waterfall Chart Studio

Скачать надстройку можно с сайта ]]>http://fincontrollex.com]]>, выбрав ее в меню Продукты или соответствующую иконку на главной странице сайта.

На сайте также можно найти подробную справку к надстройке (]]>http://help.fincontrollex.com/Waterfall_Chart_Studio_2.0/ru/obshchie_svedeniya.htm]]> ) и видеоурок, который быстро позволит составить первое впечатление о надстройке (]]>http://fincontrollex.com/?page=products&id=1&lang=ru]]> ).

На странице продукта нажмите кнопку «Скачать бесплатно». Надстройка будет скачана на компьютер в формате архива zip. В архиве содержится 2 файла надстройки *.xll: x64 – для 64 и x86 – для 32 – разрядной версии MS EXCEL. Чтобы узнать версию вашей программы в меню Файл выберите пункт Справка.

Запустите файл надстройки. После установки в MS EXCEL появится новая вкладка fincontrollex.com.

Нажмите кнопку Активировать, затем в появившемся окне выберите «Получить бесплатно лицензионный ключ для активации пробной версии».

Будет открыта страница бесплатной активации сайта fincontrollex.com. После ввода Вашего адреса электронной почты, Вам через несколько минут будет прислан ключ активации.

Ключ активации нужно скопировать в буфер обмена (CTRL+C) и, нажав поле «Активация продукта»,

вставить ключ в соответствующее поле (CTRL+V):

Нажмите кнопку Активировать. Установка надстройки завершена.

Построение простейшей диаграммы Водопад

Все мы живем в очень динамичном мире, где все постоянно меняется и регулярно не хватает времени. Поэтому, естественно, нам хочется получить результат быстро и желательно без потерь времени на изучение нового.

Разработчики из компании fincontrollex.com совершенно точно понимают эту потребность современного специалиста и поэтому разработали технологию ]]>OneClick]]>, позволяющую создать каскадную диаграмму буквально за 2 клика мыши.

В качестве первого простого примера рассмотрим таблицу влияния факторов на NPV (Net Present Value, Чистая Приведенная Стоимость) проекта.

Для построения диаграммы выделим числовые значения второго столбца и на вкладке fincontrollex.com нажмем кнопку Создать.

Вот и все! Диаграмма Водопад построена.

Примечание: При первом запуске Надстройки пользователю предлагается попробовать ]]>другую надстройку от Fincontrollex: Variance Analysis Tool]]>, используемую для факторного анализа.

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

  1. Сразу бросается в глаза фоновый рисунок, подчеркивающий, что мы используем пробную версию надстройки (срок действия – 30 дней с момента установки).
  2. Немного покликав по диаграмме, быстро приходим к заключению, что мы имеем дело со стандартным типом диаграммы MS EXCEL «Гистограмма с накоплением».

На мой взгляд, это преимущество надстройки, т.к. нам доступен весь функционал MS EXCEL по настройке макета и формата диаграммы (как видно на рисунке выше, при выделении диаграммы Водопад активируются соответствующие стандартные вкладки «Работа с диаграммами»).

3. Диаграмма содержит 8 рядов данных, содержащих в качестве значений массивы констант вида ={0;680;680;705;630;630;0} (ссылки на исходную таблицу отсутствуют).

Из анализа этих рядов становится понятна трудоемкость создания диаграммы (при самостоятельном создании простейшей диаграммы Водопад потребуется как минимум 3 ряда данных).

Сделаем несколько настроек диаграммы с помощью стандартного функционала MS EXCEL.

Во-первых, избавимся от фонового рисунка. Для этого выделите диаграмму, во вкладке Макет выберите Формат выделенного. В появившемся окне, во вкладке Заливка установите Сплошная заливка, цвет заливки – белая.

При обновлении или перестроении диаграммы фоновый рисунок появляется вновь, что неудобно, но справедливо – обновите надстройку до версии Pro и рисунок исчезнет.

Во-вторых, сделаем шрифт подписей категорий (факторов) поменьше, чтобы они разместились горизонтально. Также добавим рамку по краю диаграммы.

В итоге получим вот такую диаграмму.

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

Чтобы поместить подписи над синими столбцами их необходимо сначала найти. Это можно сделать 2-мя способами: либо кликайте по синему столбцу, пока не попадете на подпись (на самом деле там несколько подписей, нужно выбрать ту, которая длиннее, остальные содержат пустые значения от вспомогательных рядов), либо во вкладке Макет для ряда Totals выберите подписи данных.

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

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

Настройка диаграммы Водопад

Выделите диаграмму, чтобы произвести настройку диаграммы средствами самой Надстройки.

На вкладке fincontrollex.com нажмите кнопку Настройки. Ответив утвердительно на появившийся вопрос о Вашей уверенности насчет внесения изменений в диаграмму, будет открыто диалоговое окно настроек.

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

Этот нестандартный метод построения диаграммы Водопад позволяет разместить на диаграмме более длинные названия факторов, что часто бывает довольно удобно.

Остальные настройки не столь радикальные и относятся скорее к форматированию, нежели к макету:

  • Изменив Тип диаграммы с Доходы на Расходы тем самым заменим цвета положительных и отрицательных отклонений: теперь отрицательные отклонения станут зеленого цвета, т.е. будут интуитивно ассоциироваться с чем-то положительным, что разумно, когда речь идет о расходах, которые нужно снижать;
  • Сняв галочку в поле Подписи, можно убрать шкалу значений;
  • Кликнув на кнопки Фон, Итоги, Положительные и Отрицательные можно изменить цвета соответствующих столбцов. Остановимся на этих настройках чуть подробнее.

Настройка свойств диаграммы может работать в 2-х различных режимах:

  1. Если Пользователю требуется изменить цветовую гамму по умолчанию для ВСЕХ вновь создаваемых диаграмм, то кнопку Настройка нужно нажимать, лишь убедившись, что в текущий момент ни одна из диаграмм на листе не выделена. Все настройки, внесенные в этом режиме, сохранятся для текущего пользователя и будут применены по умолчанию для ВСЕХ вновь создаваемых диаграмм;
  2. Если перед вызовом окна Настроек пользователь выделил диаграмму, то все изменения цвета Фона, столбцов Итогов, Положительных и Отрицательных столбцов, будут применены только к ТЕКУЩЕЙ диаграмме. В этом случае пользователю будет выдано сообщение "Вы действительно хотите изменить настройки диаграммы?". К вновь созданным диаграммам будут по-прежнему применяться настройки, установленные по умолчанию.

Наконец, скажем несколько слов о кнопке Обновить, расположенной на вкладке Надстройки.

Если вы изменили данные в исходной таблице (не важно, текст или значения), то ВСЕГДА нажимайте кнопку Обновить. Дело в том, что не смотря на то, что ряды данных не связаны с данными исходной таблицы, подписи данных – связаны. Таким образом, при изменении числовых значений в таблице, подписи на диаграмме будут также соответственно изменяться, но это не будет приводить к увеличению или уменьшению высоты столбцов диаграммы.

На рисунке ниже видно, что значение второго сверху столбца -10 000 000,00, тем не менее, высота (длина) столбца не отображает величину этого значения (красный столбец должен быть значительно длиннее).

Теперь построим более сложную диаграмму с промежуточными итогами.

Диаграмма Водопад с промежуточными итогами

В качестве исходных данных возьмем информацию о прибыли компании по месяцам.

Как видно из таблицы, вклад в прибыль, которую компанию получит в конце года, распределен неравномерно в течение года. Например, за исключением мая и июня, деятельность компании приносила прибыль.

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

В правом столбце приведен относительный вклад каждого месяца в прибыль.

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

↑ # ##0,0%;[Красный]↓ -# ##0,0%

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

Теперь перейдем к построению диаграммы.

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

Для построения более сложной диаграммы Водопад с промежуточными итогами используем другой подход. Для этого:

  • выделите числовые значения в среднем столбце;
  • на вкладке fincontrollex.com нажмите кнопку Выбрать данные.

Надстройка выведет окно, в котором будут заполнены все поля.

Как видно из рисунка выше, программа правильно определила ячейку, в которой содержатся промежуточные итоги, а также корректно расставила ссылки на названия категорий и процент отклонений. Изменим только ссылку на Заголовок диаграммы, укажем ячейку 'Пример2'!A6.

В итоге получим следующую диаграмму:

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

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

Прежде чем двинуться дальше, рассмотрим более подробно использованные нами подходы по созданию диаграмм.

Как мы убедились, Надстройка позволяет создавать диаграммы двумя способами:

1)    За один клик (OneClick). Для этого необходимо предварительно выделить диапазон данных и нажать кнопку Создать. В этом случае Надстройка попытается автоматически обнаружить столбцы с другими данными и постарается включить их в диаграмму. Надстройка предполагает, что эти столбцы расположены в определенном порядке:

  • Диапазон с названиями категорий расположен слева от данных;
  • Диапазон с относительными отклонениями % расположен в столбце справа от данных;
  • В качестве Заголовка будет использован заголовок столбца с названиями категорий;
  • Будут автоматически определены ячейки с промежуточными итогами.

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

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

Этот режим также позволяет изменить диапазон исходных данных для уже созданной диаграммы. Для этого нужно выделить диаграмму и нажать кнопку Выбрать данные. Будет выведено диалоговое окно "Вы действительно хотите изменить исходные данные?". После нажатия кнопки Да, откроется окно со ссылками на исходные данные выбранной диаграммы и пользователь сможет их изменить.

Наконец, построим еще более сложную диаграмму с промежуточными итогами.

До настоящего времени мы строили диаграммы, в которых итоговые показатели зависели только от одного набора факторов (месяцы или статьи затрат). Ниже мы рассмотрим пример построения диаграммы Водопад, используемой для анализа зависимости показателей от двух наборов факторов.

В исходной таблице, приведенной ниже, рассмотрен пример анализа выручки компании Apple от 2-х категорий факторов: продуктового набора и месяца продаж.

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

Для построения каскадной диаграммы нам потребуется преобразовать таблицу к одномерному виду (расположим данные в один столбец).

После преобразования таблицы выделите столбец со значениями и нажмите кнопку Создать на вкладке fincontrollex.com.

Примечание: Для наглядности на диаграмме изменено минимальное значение итоговых показателей по вертикальной оси (с 0 до 32 000).

Подведем итоги

Мы на примерах убедились, что Надстройка Waterfall Chart Studio поддерживает различные варианты построения диаграмм типа Водопад:

  • горизонтальная и вертикальная диаграмма
  • диаграммы с промежуточными итогами (определяются автоматически)
  • диаграммы с одновременным отображением абсолютных и относительных отклонений
  • диаграммы с поддержкой настройки цветов положительных и отрицательных отклонений, а также итоговых показателей
  • для быстрого создания диаграммы имеется функция OneClick, позволяющая построить диаграмму всего за 2 клика.

Выводы:

  1. Диаграмма Водопад от ]]>fincontrollex.com]]> является быстрым и удобным средством построения диаграмм такого типа, что сэкономит Вам много времени и нервных клеток;
  2. Для полноценной работы с диаграммой от fincontrollex.com могут потребоваться базовые знания работы со стандартными диаграммами MS EXCEL;
  3. Сайт ]]>www.excel2.ru]]> рекомендует финансовым аналитикам и менеджерам использовать надстройку Waterfall Chart Studio от Fincontrollex.com для построения диаграмм типа Водопад поскольку она сочетает преимущества Надстроек выполнять специализированные задачи и возможность использовать встроенные средства MS EXCEL для тонкой настройки диаграмм.

 


Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)