Каскадная диаграмма в excel 2020 - profexcel.ru, справочник EXCEL

Как создать каскадную диаграмму в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На сайте также можно найти подробную справку к надстройке ( ]]> profexcel.ru ]]> ) и видеоурок, который быстро позволит составить первое впечатление о надстройке ( ]]> profexcel.ru?page=products&id=1&lang=ru ]]> ).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выводы :

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

Источник: profexcel.ru

9. Каскадная диаграмма

: Создание каскадной диаграммы
: «Процент от % таблицы=» как содержимое метки

Создание каскадной диаграммы

Значок в меню «Элементы»:

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

Положительные значения приводят к тому, что сегменты двигаются вверх, а отрицательные приводят к тому, что сегменты двигаются вниз. Промежуточные итоги (сегменты, которые идут вплоть до базовой линии диаграммы) легко создаются с помощью (знак равно). В действительности, вы можете использовать в любом сегменте, который требуется растянуть на всю диаграмму. Все сегменты вычисляются think-cell и автоматически обновляются при изменении данных.

Вы даже можете начать вычисление с помощью в первом столбце. В этом случае think-cell начинает с крайнего правого столбца и проводит вычисление в обратном порядке для поиска значения столбца . Таким образом, следующая таблица дает такую же диаграмму, которая показана выше:

Вы можете ввести в одном столбце два или больше значений. Если столбец состоит из нескольких сегментов, вы можете ввести только в одном из них.

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

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

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

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

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

При перемещении соединителя вся диаграмма изменяется соответствующим образом, а столбцы, содержащие знак «равно» обновляются.

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

Если соединители конфликтуют друг с другом, они будут отклонены. Удалите некоторые из таких соединителей, чтобы устранить проблему.

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

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

Теперь перетащите правый маркер выделенного соединителя, чтоб связать столбец со знаком «равно» с верхней частью последнего сегмента. Получившаяся диаграмма будет выглядеть следующим образом:

Чтобы создать каскадную диаграмму по убыванию, используйте значок на панели инструментов. При этом таблица по умолчанию заполняется значениями, необходимыми для диаграммы по убыванию. Кроме этого, каскадные диаграммы по возрастанию и убыванию в think-cell ничем не отличаются.

Каскадные диаграммы можно украшать, как гистограммы. Вы можете настроить оси, добавить стрелки, изменить промежутки и т. д. (См. разделы Шкалы и оси и Стрелки и значения).

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

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

«Процент от % таблицы=» как содержимое метки

Метки для стрелок разницы уровней (см. раздел Стрелка разницы уровней) на каскадных диаграммах также поддерживают отображение значений в процентах от значения %= в таблице (% от % таблицы=).

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

На диаграммах выше показаны два параметра содержимого метки. На диаграмме слева разница, равна 2, сравнивается с начальным значением 2, что приводит к отображению «+ %». Если значение %= в таблице оставить пустым, оно будет равно сумме значений столбца. Поэтому на диаграмме справа разница, равная 2, сравнивается с суммой столбца, равной 3, что приводит к отображению «+67 %».

Другое применение показано на следующей диаграмме. Для центрального столбца каскадной диаграммы итоговое значение 5 задано как значение %= в таблице. Используя параметр % от % таблицы= , можно отобразить, что верхние два сегмента соответствуют 40 % от этого итогового значения.

Источник: profexcel.ru

Каскадная диаграмма в excel

Как создать каскадную диаграмму в Excel

Смотрите такжеБМВ У нас такая и будем раскрашивать в Excel, когда посмотреть разницу поТеперь закройте окно данных. > месте диаграммы Воронка на вашем языке.

анализа рекомендуется добавить эффекты. Для этого данных=ЕСЛИ(E4>0;E4;0) продаж из столбца упорядочив свои данные самая простая диаграммаЕсли вы часто используете: так как используется таблиц столбцы в диаграмме. данные в таблице годам, т.д. Если вы открывалиДиаграмма и нажмите Эта страница переведена к столбцам диаграммы используйте меню параметровBaseЭто означает, что еслиSales Flow и используя встроенную «Водопад» и чем Excel, то наверняка автоматическая подстановка цветов,В ячейке C3 Не окрашенные столбцы

положительные и отрицательные.В ячейке B3 Excel для изменения

 >Работа с диаграммами автоматически, поэтому ее подписи.Контур фигуры, щелкаем по нему значение в ячейкебудут помещены в в Excel гистограмму она может быть знаете о преимуществах то менять надо стоит такая формула. в диаграмме сделаемУ нас такая пишем формулу, которая данных, закройте окно

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

Что такое диаграмма «Водопад»?

(Shape Outline) и правой кнопкой мышиE4 столбец с накоплением. полезна.

 графиков не понаслышке. тему =D2 В ячейке прозрачными. таблица по движению будет считать остаток Excel.. части программы:

неточности и грамматические которому нужно добавитьЭффекты фигуры и в контекстномбольше нуля, тоFallПримечание переводчика:Диаграмма «Водопад» Графическое представление данныхNatalia K D3 стоит такаяСтроим диаграмму Водопад в финансовых средств по на начало следующегоДиаграммы в ExcelПоявится воронкообразная диаграмма, аНастройте данные, как в ошибки. Для нас подписи.(Shape Effects) на меню выбираем

все положительные числа, а положительные –В Excel – это особый оказывается очень полезным,: Спасибо!

формула. =D2+B3Excel месяцам. периода (месяца, года).можно построить по также небольшое окно приведенном выше примере. важно, чтобы этаЩёлкните правой кнопкой мыши вкладкеФормат ряда данных будут отображаться, как в столбец Microsoft наконец-то добавила

Как построить каскадную диаграмму (Мост, Водопад) в Excel?

тип диаграммы в когда нужно наглядноДело в том,Формулы копируем вниз.Нам нужно построить диаграмму =В2+С3-D3 Пояснение формулы разным данным. Можно с примером данных. Используйте один столбец статья была вам и в появившемсяФормат(Format data series).

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

Выделяем столбец A так, чтобы положительные
– остаток на
в диаграмме показать Замените числа своими
для этапов процесса, полезна. Просим вас
контекстном меню выберите(Format).Далее проделаем то

В Excel в – как нули.. и среди них для того, чтобы или обозначить вектор изменить цвета на столбцы A,C, D с названием столбца. данные на диаграмме январь плюс приход текущие данные, а

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

Шаг 1. Изменяем порядок данных в таблице

Нажимаем клавишу «Ctrl», отложились выше нулевой за февраль, минус можно показать толькоСовет: значений. и сообщить, помогла(Add Data Labels). рядом данных листа появится панель скопировать эту формулу под названием «Водопад». данные увеличиваются илиMicrosoft Excel предлагает множествочто бы они помощью клавиши «Ctrl» удерживая её нажатой, оси, а отрицательные расход за февраль, разницу данных по В верхней части окнаВыделите данные. ли она вам,Проделайте то же самоеRiseФормат ряда данных вниз по столбцу.

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

Шаг 2. Вставляем формулы

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

таблице ряда изменений. в том числе и слова в закладке «Вставка» нажимаем D до J.

Получился такой график. выделили только два несколько дополнительных данных. формулу по столбцу. изменение данных по. Если вам(значок ссылку на оригинал
положение подписи так,End(Fill & Line).
и копируем ее год. На следующем накоплением диаграмме показывают суммарные типы диаграмм. Вболее конкретный примерТеперь нужно добавить полосы
Формулами это сделать У нас получилась периодам, как положительных, нужно больше местакаскадной

Шаг 3. Создаём стандартную гистограмму с накоплением

(на английском языке). чтобы читать их, то для нихВ разделе

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

эти столбцы нужными в диаграмму «Водопад» являются плавающими и подробно рассмотрим всебуду благодарна за В Excel На закладке «Вставка»

Шаг 4. Преобразуем гистограмму с накоплением в диаграмму «Водопад»

получится такая.Теперь выделяем всю таблицу Поможет данными, нажмите этуВоронка значения на разныхЗамечание: цвет, причём этиливка

: значениями.Настраиваем каскадную диаграмму в обычно показывают положительные детали создания простейших совет!!! нажимаем на плюсик в разделе «Диаграммы»В столбце C посчитали с шапкой. Напровести анализ в Excel кнопку.. этапах процесса. Например,
Если разница между два столбца должны(Fill) выбираем
=B4+D4-C5Лучший способ заполнить таблицу Excel или отрицательные изменения, графиков, и кромеБМВ справа от диаграммы. нажимаем на кнопку данные нарастающим итогом закладке «Вставка» влюбой деятельности.

Чтобы добавить названия этапов,Совет: с помощью воронкообразной размерами столбцов очевидна, быть окрашены одинаково.ВНет заливкиЭта формула рассчитывает исходные – вставить нужныеДля лучшего понимания, давайте

Шаг 5. Настраиваем каскадную диаграмму в Excel

происходящие от одного того, ближе познакомимся: А вот так Или на закладке «Гистограмма». Выбираем - – к данным разделе «Диаграммы» нажимаемДля того, чтобы щелкните правой кнопкой

В Excel для Mac диаграммы можно показать а точные значения результате диаграмма будет(No fill), в значения, которые поднимут формулы в первые создадим простую таблицу периода к другому,
с особым типом картинки вставлять не «Конструктор» нажимаем на «Гистограмма с накоплением». предыдущего месяца прибавили
на кнопку «Гистограмма». увидеть и сравнить мыши в столбце A настроить внешний вид количество потенциальных покупателей точек данных не выглядеть примерно так: разделе отрезки роста и ячейки соответствующих столбцов, с положительными и а в конечном диаграммы – стоит :-) кнопку функции «Добавить Получилась такая диаграмма. данные текущего месяца. Выбираем - «Гистограмма рост и снижение и выберите команду диаграммы с помощью на каждом этапе так важны, тоЗамечание:Граница спада на соответствующие а затем скопировать отрицательными значениями. В

 итоге общий результат,каскадная диаграмма в ExcelNatalia K элемент диаграммы», ВыбираемНажимаем на диаграмму. На Например, к значению с накоплением». данных на графике,Вставить вкладки в канале продаж. подписи можно убрать,Другой способ изменить(Border) – высоты на диаграмме. их вниз в

качестве примера возьмем т.е. суммарное значение.(
: ага, я первый функцию «Полосы повышения закладке «Конструктор» в за январь прибавилиПолучился такой график.
строим диаграмму «Водопад»..Конструктор диаграмм Как правило, значения но тогда следует заливку и контурНет линийТеперь все нужные данные
смежные ячейки, используя объем продаж. Из Как правило, колонкианалог: диаграмма водопад раз))) и понижения». В разделе «Данные» нажимаем значение за февраль.Теперь нужно изменить цветаЕсть несколько вариантов,Выберитеи
постепенно уменьшаются, так показать ось Y, столбцов на диаграмме
(No line). рассчитаны, и мы маркер автозаполнения. таблицы, представленной ниже, окрашены в разные). Вы узнаете, чтоБМВ Excel на на кнопку функцииВ ячейке С4 столбцов диаграммы. Синий как построить диаграмму

СтолбецФормат что полосы диаграммы чтобы сделать диаграмму – открыть панельПосле того, как голубые готовы приступить кВыбираем ячейку видно, что на цвета, чтобы наглядно представляет собой каскадная

: Тогда вам нужно закладке «Макет» в «Строка/Столбец». Меняем местами напишем такую формулу. цвет убрать, сделать Водопад., затем нажмите кнопку. Если вы не по виду напоминают понятнее.Формат ряда данных столбцы стали невидимыми, построению диаграммы:C4

протяжении нескольких месяцев выделить положительные и диаграмма и насколько отказаться от варианта разделе «Анализ» нажимаем

в диаграмме строки =C3+B4 Получилось накопление эти столбцы прозрачными.Как работать сОК видите вкладки, щелкните воронку.

Когда закончите с подписями(Format Data Series)
остаётся только удалить
Выделите данные, включая заголовки

в столбце

profexcel.ru

Создание воронкообразной диаграммы

 продажи то росли, отрицательные значения. Далее полезной она может который появился в на кнопку «Полосы и столбцы. Легенду нарастающим итогом за Нажимаем на синий диаграммой, графиком, где. в любом местеПримечание: столбцов, можно избавиться или кликнуть поBase строк и столбцов,Fall то падали относительно в этой статье быть. Вы познаете и вернуться

повышения/понижения». Получилось так. на диаграмме расположили февраль. (3 + столбец на диаграмме. расположены функции, смотритеВведите названия этапов в диаграммы Воронка и Эта функция доступна только от лишних элементов, столбцу правой кнопкойиз легенды, чтобы кроме столбцаи вставляем туда

 начального уровня. мы расскажем о секрет создания каскадной к старому ,Осталось изменить цвет столбцов справа. Получилось так. (-2,5)= 0,5). В Столбцы выделились. Нажимаем в статье «Как

Вставка Воронка диаграммы в Excel для Windows

ячейках A2, A3 вкладок на ленте. при наличии подписки таких как нулевые мыши и в на диаграмме от
Sales Flow
следующую формулу:Диаграмма «Мост» в Excel том, как сделать диаграммы в Excel ручному, варианту. Поищите в диаграмме, убратьТеперь осталось разобраться с феврале произошло снижение,
 на синий столбец сделать диаграмму в и т. д.Настройте данные, как в на Office Если значения и легенда появившемся меню выбрать этого ряда данных.=IF(E4 отлично покажет колебания промежуточные столбцы «плавающими». , а также , примеров много. цвет у линий

Вставка Воронка диаграммы в Excel для Mac

цветами столбцов. Нажимаем но итоговое значение правой мышкой, выбираем Excel».В верхней части окна приведенном выше примере.
у вас есть
диаграммы. Кроме того, параметры не осталось иПерейдите на вкладку=ЕСЛИ(E4 продаж за взятыеДиаграмма «Водопад» также называется изучите различные инструменты,
 Но в целом графика. Получилось так. на столбец в осталось положительным. Эти - «Формат рядаПервый способ. приложения откройте вкладку Используйте один столбец подписка на Office , можно придумать дляЗаливка следа.Вставка

Вставка воронкообразной диаграммы в Excel Mobile и Excel для Android

Смысл этой формулы в двенадцать месяцев. Если « которые помогут сделать - эта диаграммаВ Excel можно
диаграмме правой мышкой.
данные отложатся на данных» -> "ПараметрыКак сделать диаграмму водопадКонструктор для этапов процесса, убедитесь, что у диаграммы более содержательное

Вставка диаграммы Воронка в Outlook, PowerPoint и Word

(Fill) илиВ завершение немного займёмся(Insert), найдите раздел том, что если сейчас применить гистограммуМост такую диаграмму буквально не нуждается в выделить повторяющиеся данные Из контекстного меню диаграмме над нулевой

ряда" -> «Заливка» в Excel, когда. а другой — для
вас установлена последняя название. О том,Контур форматированием. Для началаДиаграммы значение в ячейке с накоплением к
«, поскольку плавающие столбцы за минуту. расцветке, особенно если цветом, но и выбираем функцию «Формат
 осью. А в -> «Нет заливки». все данные вНажмите кнопку значений. версия Office. как в Excel(Outline). я сделаю плавающие(Charts).
E4 конкретно этим значениям, создают подобие моста,И так, давайте начнем у вас последовательно словами, числами, знаками,
ряда данных». Устанавливаем апреле итоговое значениеКак работать с таблице положительные.Выбор данных
Выделите данные.Настройте данные, как в добавить название к
Затем можно удалить лишнее блоки ярче иКликнитеменьше либо равно
то ничего похожего соединяющего крайние значения. совершенствовать свои навыки
 идет рост, а формулой, т.д. Смотрите нужный цвет. отрицательное. Оно отложится диаграммой, на какихНапример, нам нужно
.Выберите приведенном выше примере. диаграмме, подробно рассказано расстояние между столбцами,
выделю начальное (Вставит нулю, то отрицательное на каскадную диаграммуЭти диаграммы очень удобны работы в Excel. потом спад. Вот
о таких приемахСтолбец января окрасили под нулевой линией. закладках найти функции, посмотреть, как движется
Примечание:Вставка Используйте один столбец в одной из чтобы сблизить их.

profexcel.ru

Как сделать диаграмму «Водопад» в Excel.

Startь гистограмму значение будет показано не получится. Поэтому, для аналитических целей.Примечание переводчика: когда данных больше в статье "Как в серый цветДля этих целей как изменить цвет, прибыль по месяцам. Чтобы увидеть кнопку > для этапов процесса, предыдущих статей.
Дважды кликните по одному) и конечное ((Insert Column Chart) как положительное, а первое, что нужно
Если вы хотитеКаскадная диаграмма имеет и идет чередование,
найти повторяющиеся значения – это точка разобьем все данные ширину столбца диаграммы, Построим таблицу.Выбор данных
Диаграмма
 а другой — дляКаскадная диаграмма готова! Она из столбцов диаграммы,End
и в выпадающем вместо положительного значения сделать, это внимательно оценить прибыль компании
множество названий. Самые тогда цвет немного в Excel". отсчета. по отдельным колонкам, т.д., читайте в
В столбце Aв PowerPoint, может > значений. значительно отличается от
чтобы появилась панель) значения на диаграмме. меню выберите будет показан ноль. переупорядочить имеющиеся данные. или доходы от популярные из них: помогает.N_A_T_A_L_I_AДополнительные столбцы – чтобы диаграмма построилась
статье «Как сделать – наименование (товар, потребоваться переместить небольшоеВоронкаВыделите данные. обычных типов диаграммФормат ряда данныхВыделяем ряд данныхГистограмма с накоплениемЗамечание:Первым делом, добавим три производства продукции, сделатьВодопадNatalia K: При создании каскадной ставим – «Нет
правильно. график в Excel». прибыль, период, т.д.), окно вниз..Щелкните и предельно понятна,
(Format Data Series)
Fall(Stacked Column).Если Вы хотите, дополнительных столбца к анализ продаж или,: Ручным вариантом я диаграммы есть необходимость заливки», они станутИтак, формулы такие.Теперь установим зеленый любые данные, изменениеОткроется окно
Примечание:Вставка не так ли?Установите для параметрана диаграмме иПоявится диаграмма, пока ещё чтобы все значения исходной таблице в
просто увидеть, какМост пользуюсь, хотела автоматизировать изменить ее форматирование прозрачными. Все формулы пишем цвет для данных по которым хотимВыбор источника данных Эта функция доступна в >Теперь Вы можете создатьБоковой зазор открываем вкладку мало похожая на
в каскадной диаграмме
 Excel. Назовём их
 изменилось количество ваших, процесс и подписи в
Столбцы прихода окрашиваем в строке 4, прихода (повышения)и красный увидеть на графике.
, а вокруг второго указанных приложениях, толькоВставить каскадную или биржевую целую коллекцию каскадных(Gap Width) небольшоеФормат каскадную. Наша следующая лежали выше нуля,Base друзей в FacebookСтупенькимне нужно сделать
легенде. в зеленый цвет. затем копируем вниз цвет для данныхВ столбце C столбца данных появятся если у вас диаграмму
диаграмм в Excel. значение, например,(Format) в группе задача – превратить то необходимо ввести, за год, каскаднаяи порядка 20 аналогичныхПо умолчанию в Столбцы расхода окрашиваем по столбцу. расхода (снижения). Получилось пишем цифры роста пунктирные линии.
есть подписка на > Надеюсь, для вас15% вкладок
гистограмму с накоплением знак минус (-)Fall диаграмма в ExcelЛетающие кирпичи
и очень быстро легенде отображается: «Увеличить» в красный цвет.В ячейке E4
так. (прибыли, товара, т.д.)Щелкните данные и перетащите
Office Если уВоронка это будет совсем
. Закройте панель.Работа с диаграммами в диаграмму «Водопад» перед второй ссылкойи — это то,, также распространены английские каждый месяц,
 «Уменьшить» «Итог». Получилось так. формула будет такая.
Можно сделать столбцы шире. В столбце D указатель, чтобы выделить вас есть подписка. не сложно. СпасибоТеперь на диаграмме «Водопад»(Chart Tools). в Excel. на ячейку
Rise что вам необходимо. варианты –в справке ExcelПри изменении цветовПо этому графику видно,
=ЕСЛИ(И(C4 В ячейке Нажимаем правой мышкой пишем цифры снижения. оба столбца: этапы на Office , убедитесь,Совет: за внимание! нет ненужных пробелов.В разделеПришло время раскрыть секрет.Е4. СтолбецНе тратьте время наWaterfall по этой диаграмме на диаграмме легенда что только в
F4. =ЕСЛИ(И(B4>0;C4>0);МИН(B4;C4);0) на столбец диаграммыВ столбце B и значения. После
что у вас В Excel используйте вкладкиУрок подготовлен для ВасПри взгляде на каскадную
Стили фигур Для того, чтобыв формуле. МинусBase поиск каскадной диаграммыи похоже, что цвета их «не подхватывает». июне вышли вВ ячейке G4. и выбираем из
 пишем в верхней
этого оба столбца установлена последняя версияКонструктор командой сайта profexcel.ru диаграмму может показаться,(Shape Styles) нажимаем
преобразовать гистограмму с на минус дастбудет содержать вычисленное в Excel –Bridge
и слова вБМВ плюс, хоть рост =ЕСЛИ(И(B4>0;C3 В ячейке контекстного меню функцию ячейке число, от будут окружены пунктирными Office.иИсточник: profexcel.ru что некоторые блокиЗаливка фигуры накоплением в диаграмму плюс. исходное значение для её там нет.. легенде изменены: Natalia K, Весьма был и в H4. =ЕСЛИ(И(C4>0;C3>0);МИН(C3:C4);0) «Формат ряда данных». которого будем отталкиваться. линиями.Щелкните на свободном местеФорматПеревел: Антон Андронов одинаковы по размеру.(Shape Fill). «Водопад», Вам простоКопируем формулу вниз до отрезков спада ( Проблема в том,Что такое диаграмма «Водопад»?за совет спасибо! желателен пример файла. мае.В ячейке I4.

profexcel.ru

Каскадная диаграмма Excel

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

Как построить каскадную диаграммуБМВNatalia K

Третий способ. =ЕСЛИ(И(B4 В ячейке ряда» уменьшаем «Зазор».
начало месяца, еслиВыбор источника данных

 почты, презентации или вид диаграммы. ЕслиПримечание: к исходной таблице нужный profexcel.ru же

 ряда данныхКликаем на ячейку
) и роста ( просто нет готового (Мост, Водопад) в: это таким?
: файл я прикрепила,Еще один способ J4. =ЕСЛИ(И(B40);МИН(-B4;C3);0) Мы поставили 20%. хотим увидеть разницу
нажмите кнопку документе.
вы не видите Мы стараемся как можно

 данных, видно, что можете поэкспериментировать сBaseD4

Rise шаблона такой диаграммы. Excel?

Natalia K очень жду помощи! построенияВ таблице названиеПолучилось так. по месяцам, годаОКВыберите указанных ниже вкладок, оперативнее обеспечивать вас значения не одинаковы. контуром столбцов илиневидимыми на графике.и вводим формулу:) на диаграмме. Все Однако, не сложноДля начала, давайте посмотрим,: да, именно такБуду благодарна за

диаграммы «Водопад» в Excel столбцов окрашено темВторой способ. – если хотим
, чтобы закрыть его.Вставка щелкните в любом актуальными справочными материалами
Для более точного добавить какие-либо особенныеВыделяем на диаграмме ряд=IF(E4>0,E4,0) отрицательные колебания объёма
создать собственную диаграмму,

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

 совет!с помощью графика.
же цветом, что

profexcel.ru

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

Источник: profexcel.ru

Каскадная диаграмма в excel

9. Каскадная диаграмма

: Создание каскадной диаграммы
: «Процент от % таблицы=» как содержимое метки

Создание каскадной диаграммы

Значок в меню «Элементы»:

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

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

«Процент от % таблицы=» как содержимое метки

Источник: profexcel.ru

Диаграмма каскадная в excel

Диаграмма водопад (waterfall chart) в Excel

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

Диаграмма водопад (также называют waterfall chart) выглядит следующим образом:

Диаграмма водопад (waterfall chart)

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

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

Чтобы построить диаграмму добавим в таблицу вспомогательные элементы:

Построение вспомогательной таблицы

Прозрачный — вспомогательный столбец поднимающий факторы на необходимую высоту;
Красный 1 — начальное значение;
Оранжевый — отрицательный фактор;
Зеленый — положительный фактор;
Красный 2 — конечное значение.

Расшифровка формул вспомогательной таблицы

Построение накопительной гистограммы

Меняем цвет рядов диаграммы

Выставляем боковой зазор для рядов равным 0

Добавляем подпись данных на ряды

Далее форматируем диаграмму на свое усмотрение и получаем окончательный вид (убираем легенду, подпись вертикальной оси и т.д.):

Диаграмма водопад (waterfall chart)

Особенности диаграммы водопад

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

Источник: profexcel.ru

Создание каскадной диаграммы

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

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

Создание каскадной диаграммы

Щелкните Вставка > Вставить каскадную или биржевую диаграмму > Каскадная.

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

Совет: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, и на ленте появится область Работа с диаграммами.

Итоги и промежуточные итоги с началом на горизонтальной оси

Если данные содержат значения, которые считаются промежуточными и итоговыми, например нетто Income, вы можете установить эти значения таким образом, чтобы они начинались с горизонтальной оси, а не &#;Плавающая&#;.

Дважды щелкните точку данных, чтобы открыть область задач Формат точки данных , и установите флажок установить как итог .

Примечание: Если щелкнуть столбец один раз, будет выбран ряд данных, а не точка данных.

Чтобы снова сделать столбец плавающим, снимите флажок Задать как итог.

Совет: Итоги также можно задать, щелкнув правой кнопкой мыши точку данных и выбрав в контекстном меню пункт Задать как итог.

Отображение и скрытие соединительных линий

Соединительные линии соединяют конец предыдущего столбца c началом следующего, помогая отобразить поток данных на диаграмме.

Чтобы скрыть соединительные линии, щелкните правой кнопкой мыши ряд данных, чтобы открыть область задач &#; Формат ряда данных &#;, и снимите флажок Показывать соединительные линии .

Чтобы снова отобразить эти линии, установите флажок Отображать соединительные линии.

Совет: В легенде диаграммы точки данных сгруппированы по типам: Увеличение, Уменьшение и Итог. Если щелкнуть легенду диаграммы, на диаграмме будут выделены все столбцы, соответствующие выбранной группе.

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

На вкладке Вставка на ленте щелкните (значок каскада) и выберите Каскад.

Примечание: На вкладках Конструктор и Формат можно настроить внешний вид диаграммы. Если эти вкладки не отображаются, щелкните в любом месте каскадной диаграммы, чтобы отобразить их на ленте.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: profexcel.ru

Диаграмма каскадная в excel

Войти через uID

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

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

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

Содержание:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Исходную ситуацию и результат достаточно внести только 1 раз на лист.
Все положительные и отрицательные эффекты следует внести дважды.
- Например, высота “Положительный эффект” составляет При этом следует указать высоту предыдущего столбца и самого эффекта отдельно.
- Высота столбца “Отрицательный эффект” также составляет Однако в данном случае следует вторым значением указать, на сколько понизился столбец (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, как показано на картинке выше. Нажмите ОК:

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

Выделите красные колонки, затем дважды щелкните на них и в появившемся окне установите отсутствие заливки и рамки фигуры:

Выделите положительный эффект, дважды кликните и установите зеленую заливку:

Выделите отрицательный эффект, дважды кликните и установите красную заливку:

Уберите все лишние подписи и данные, настройте ширину столбцов, добавьте подписи данных, настройте шкалы… Вот теперь диаграмма готова:

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

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

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

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

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

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

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

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

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

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

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

Источник: profexcel.ru

Как построить диаграмму &#;водопад&#; (waterfall)

Все чаще и чаще встречаю в отчетности разных компаний и слышу просьбы от слушателей на тренингах объяснить как строится каскадная диаграмма отклонений &#; она же &#;водопад&#;, она же &#;waterfall&#;, она же &#;мост&#;, она же &#;bridge&#; и т.д. Выглядит она примерно так:

Издали действительно похожа на каскад водопадов на горной реке или навесной мост &#; кто что видит 🙂

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

Мы наглядно видим начальное и конечное значение параметра (первый и последний столбцы).
Положительные изменения (рост) отображаются одним цветом (обычно зеленым ), а отрицательные (спад) &#; другим (обычно красным ).
Иногда в диаграмме могут присутствовать ещё и столбцы промежуточных итогов ( серые , приземленные на ось Х столбцы).

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

Наглядное отображение динамики какого-либо процесса во времени: потока наличности (cash-flow), инвестиций (вкладываем деньги в проект и получаем от него прибыль).
Визуализация выполнения плана (крайний левый столбик в диаграмме &#; факт, крайний правый &#; план, вся диаграмма отображает наш процесс движения к желаемому результату)
Когда нужно наглядно показать факторы, влияющие на наш параметр (факторный анализ прибыли &#; из чего она складывается).

Есть несколько способов построения такой диаграммы &#; всё зависит от вашей версии Microsoft Excel.

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

Если у вас Excel , или новее (или Office ), то построение такой диаграммы не составит труда &#; в этих версиях Excel такой тип уже встроен по умолчанию. Нужно будет лишь выделить таблицу с данными и выбрать на вкладке Вставка (Insert) команду Каскадная (Waterfall) :

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

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

Если нужно добавить в диаграмму столбцы с промежуточными итогами или финальный столбец-итог, то удобнее всего это сделать с помощью функций profexcel.ru (SUBTOTALS) или АГРЕГАТ (AGGREGATE) . Они посчитает накопленную с начала таблицы сумму, исключив при этом из нее выше расположенные аналогичные итоги:

В данном случае, первый аргумент (9) &#; это код математической операции суммирования, а второй (0) заставляет функцию не учитывать в результатах уже вычисленные итоги за предыдущие кварталы.

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

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

Вот, собственно, и всё &#; диаграмма-водопад готова:

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

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

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

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

Во-первых, нужно разделить наш исходный столбец, выделив положительные и отрицательные значения в разные колонки с помощью функции ЕСЛИ (IF) .
Во-вторых, нужно будет добавить перед сделанными столбцами колонку Пустышки, где первое значение будет 0, а начиная со второй ячейки формулой будет вычисляться высота тех самых прозрачных подпирающих столбцов.

После этого останется выделить всю таблицу кроме исходного столбца Поток и создать обычную гистограмму с накоплением через Вставка &#; Гистограмма (Insert &#; Column Chart) :

Если теперь выделить синие столбцы и сделать их невидимыми (по ним правой кнопкой мыши &#; Формат ряда &#; Заливка &#; Нет заливки), то мы как раз и получим то, что требуется.

В плюсах подобного способа &#; простота. В минусах &#; необходимость считать вспомогательные колонки.

Способ 3. Если уходим в минус &#; всё сложнее

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

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

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

Этот способ основан на использовании специального малоизвестного элемента плоских диаграмм (гистограмм и графиков) &#; Полос повышения-понижения (Up-Down Bars) . Эти полосы попарно соединяют точки двух графиков, чтобы наглядно показать какая из двух точек выше-ниже, что активно используется при визуализации план-факта:

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

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

Для создания &#;водопада&#; нужно выделить столбец с месяцами (для подписей по оси Х) и два дополнительных столбца График 1 и График 2 и построить для начала обычный график через Вставка &#; График (Insert &#; Line Сhart) :

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

В Excel и новее для этого необходимо выбрать на вкладке Конструктор команду Добавить элемент диаграммы&#; Полосы повышения-понижения (Design &#; Add Chart Element &#; Up-Down Bars)
В Excel &#; перейти на вкладку Макет &#; Полосы повышения-понижения (Layout &#; Up-Down Bars)

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

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

В последних версиях Microsoft Excel ширину полос можно изменить, щёлкнув по одному из прозрачных графиков (не по полосам!) правой кнопкой мыши и выбрав команду Формат ряда данных &#; Боковой зазор (Format series &#; Gap width) .

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

Выделите построенную диаграмму
Нажмите сочетание клавиш Alt + F11 , чтобы попасть в редактор Visual Basic
Нажмите сочтетание клавиш Ctrl + G , чтобы открыть панель прямого ввода команд и отладки Immediate (обычно она расположена внизу).
Скопируйте и вставьте туда вот такую команду: profexcel.ruroups(1).GapWidth = 30 и нажмите Enter :

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

Источник: profexcel.ru

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Диаграмма водопад (waterfall chart) в Excel

Водопад диаграмма (waterfall chart) является одной из форм визуализации данных, которая показывает совокупный эффект последовательно введенных положительных и отрицательных значений. Также иногда можно встретить название bridge chart, или «мост».

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

Выглядит она следующим образом:

Итак, посмотрим, как же можно построить диаграмму, похожую на водопад.

Подготовка данных

Изначально у вас имеются данные, примерно похожие на таблицу ниже. Чтобы построить водопад, вам предстоит привести их в должный вид.

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

Где имеются такие формулы

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

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

Тут все просто, ячейка G3 суммирует значения ячеек C3:E3, соответственно формула в ней будет =СУММ(C3:E3). Ячейка G4 копирует значение ячейки G3.

Создание диаграммы Водопад

Осталось самое простое – построить диаграмму. Выделяем ячейки A1:A6 (да, пустую ячейку тоже включаем), жмем клавишу Ctrl и выделяем ячейки C1:J6, таким образом у вас будет выделено две области.

Переходим по вкладке Вставка в группу Диаграммы, выбираем Вставить гистограмму -> Гистограмма с накоплением. У вас должен получиться вот такой график:

Меняем значения столбцов и строк местами. Для этого переходим по вкладке Работа с диаграммами -> Конструктор в группу Данные и щелкаем по иконке Строка/столбец. Наша диаграмма примет вид:

Щелкаем правой кнопкой по любому ряду данных, из всплывающего меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне, меняем ряды данных соответствующие коннекторам на График.

На диаграмме щелкаем правой кнопкой по ряду данных Конектор1, из всплывающего меню выбираем Формат ряда данных, в правой панели во вкладке Линия устанавливаем формат, чтобы получилась пунктирная линия черного цвета. Повторяем эти действия для всех рядов данных отвечающих за коннекторы.

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

В принципе, наша диаграмма водопад готова.

Осталось добавить кое-какие штрихи в форматирование. Убрать лишние элементы: легенду, линии сетки. Дать диаграмме внятное имя. Добавить подписи данных. В добавок, я уменьшил боковой зазор между колонками. Все наши злоключения должны были привести нас к следующему результату:

Источник: profexcel.ru

Пользовательская сортировка каскадной диаграммы (Waterfall) в Power BI

Недавно в чате о Power BI в Telegram был задан вопрос – возможно ли применить для графика Waterfall (каскадная диаграмма, она же «Водопад») нестандартную динамическую сортировку: положительные значения показываются по убыванию, а отрицательные наоборот, по возрастанию (то есть, сначала самые большие по модулю отрицательные значения, затем минуса поменьше и самые мелкие – в конце).

В итоге из такого графика:

Нужно получить вот такой:

Стандартными средствами мы можем сортировать Waterfall только по возрастанию или убыванию, по обычным правилам (настроить и проверить сортировку можно нажав на три точки в правом верхнем углу визуала):

Нам же нужно сделать так, чтобы:

Можно было применить нестандартную сортировку;
Она должна быть динамической, то есть реагировать на фильтры (например, по дате)

Решение этой задачи делится на две подзадачи:

Найти способ применить пользовательскую сортировку
Определить и реализовать алгоритм сортировки

Можем ли мы применить пользовательскую сортировку?

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

У Waterfall таких областей четыре:

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

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

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

Как задать правило сортировки?

Вспомним, что сортировка должна учитывать возможность применения пользовательских фильтров, поэтому нам не подойдет вычисляемый столбец в DAX или Power Query). То есть, нам нужна мера.

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

Вспоминаем вводные условия:

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

Самым распространенным способом создания динамической сортировки является использование функции RANKX в формуле меры.

Давайте посмотрим на исходные данные, отсортированные в обычном порядке:

Мера Simple Rank, использованная в таблице – это обычный RANKX, рассчитанный на основе меры [Amount] (в данном случае не важно, что именно считает эта мера, главное, что нам нужно сортировать ее значения).

Simple Rank =
IF (
ISINSCOPE ( ‘Margin Details'[SubArticle] ) ;
RANKX ( ALLSELECTED ( ‘Margin Details'[SubArticle] ) ; [Amount]; [Amount]; ASC )
)

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

Так как в задаче было описано условие, что отрицательные числа должны сортироваться по убыванию модуля, то первым порывом был расчёт модуля значений меры [Amount] при помощи функции ABS. Однако, если брать в расчёт не только отрицательные, но и положительные значения (а у нас именно такой случай), их модули перемешиваются, и мы получим кашу из + и – значений.

Еще одним вариантом решения было использование функции IF внутри функции RANKX. Но, перепробовав несколько вариантов, я окончательно запутался в контекстах вычисления, оставил эту затею и вернулся к переосмыслению задачи.

Давайте посмотрим на ряд чисел, отсортированный по убыванию:

Если мы присвоим им ранг по возрастанию значения (самое маленькое число имеет самый маленький по номиналу ранг), то мы получим вот такой ряд рангов:

Нам надо как-то сделать, чтобы самое маленькое отрицательное число имело самый большой ранг среди отрицательных чисел.

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

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

Половину проблемы мы решили. Осталось только «перевернуть» ранг отрицательных. Но это сделать уже очень просто. В DAX, как и в Excel, есть непримечательная функция SIGN (в русскоязычных версиях Excel она называется ЗНАК), которая возвращает 1, -1 или 0 в зависимости от знака числа, переданного ей в аргументе.

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

Что это значит с точки зрения математики?

Изначально самое большое отрицательное число (-2) имеет самый большой ранг (4). Самое маленькое отрицательное число () имеет самый маленький ранг (1)

После умножения ранга на сигнум самый большой ранг отрицательного числа (4) стал самым маленьким среди рангов отрицательных чисел (-4), а самый маленький ранг отрицательного числа (1) стал самым большим рангом среди рангов отрицательных числа (-1).

То есть, порядок ранга отрицательного числа «перевернулся», что мы и хотели получить.

Осталось только подправить нашу меру ранга:

CustomOrder =
IF (
ISINSCOPE ( ‘Margin Details'[SubArticle] ) ;
SIGN ( [Amount] )
* RANKX ( ALLSELECTED ( ‘Margin Details'[SubArticle] ) ; [Amount]; [Amount]; ASC )
)

И поместить эту меру в подсказки каскадной диаграммы,

Источник: profexcel.ru

Может быть интересно:

Добавление ряда данных в диаграмму | Изменение подписей осей на диаграмме в Office