Mishki-tomsk.ru

Мода и стиль
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Диаграмма Водопад в EXCEL

Диаграмма Водопад в EXCEL

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

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

Диаграмму Водопад построим в EXCEL 2010 с использованием стандартной диаграммой типа Гистограмма с накоплением .

Примечание : Начиная с версии 2016 года в EXCEL имеется стандартная каскадная диаграмма. Подробнее о ее построении можно прочитать в статье на сайте Microsoft .

Диаграмма водопад. Динамика показателя (+)

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

Сначала вычислим изменения за период. Увеличения и уменьшения разнесем по разным столбцам. Это нам позволит выделить цветом разнонаправленные изменения.

Также нам потребуется служебный столбец, который будет служить невидимой основой для столбцов-изменений (см. файл примера Лист Больше0 ).

Будем использовать Гистограмму с накоплением . В качестве рядов данных используем созданные выше столбцы (C, D, E).

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

Диаграмма водопад. Динамика показателя (+/-)

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

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

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

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

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

Анализ влияния факторов

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

Пусть в начале года было задано плановое значение для прибыли = 140. В конце года было получено значение прибыли = 171. При этом известен вклад каждого из факторов (столбец С).

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

Эта диаграмма состоит из 3-х рядов данных. Первый ряд данных включает начальное и конечное значение прибыли, а также невидимые служебные столбцы. Для построения такого ряда проще всего сначала установить для столбцов диаграммы значение Нет заливки , а затем для крайнего правого и левого значений вручную установить нужный цвет (например, синий). Для этого нужно выделить на диаграмме столбцы ряда, через 1 сек выделить левый столбик, изменить его заливку. Затем тоже сделать для последнего столбика. Подробнее см. статью Гистограмма в MS EXCEL с накоплением .

Читайте так же:
Торт тюбетейка домовая кухня

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

Бридж диаграмма что это такое

Войти через uID

Minitab

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

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

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

Содержание:

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, как показано на картинке выше. Нажмите ОК:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Таблица каскадной диаграммы think-cell

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

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

    Каскадная диаграмма think-cell со значением «равно» (e) в первом столбце

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

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

    Каскадная диаграмма think-cell со столбцом, содержащим положительные и отрицательные числа

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

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

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

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

    Каскадная диаграмма think-cell без соединителей

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

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

    Каскадная диаграмма think-cell с соединителями

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

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

    Таблица каскадной диаграммы think-cell с итоговыми значениями слева

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

    Каскадная диаграмма think-cell с итоговыми значениями слева

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

    Каскадная диаграмма think-cell с повторно соединенным столбцом со знаком «равно»

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

    image

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

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

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

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

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

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

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

    Стрелки разницы уровней с различными параметрами содержимого метки

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

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

    Бридж диаграмма что это такое

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

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

    Изображение каскадной диаграммы в Office 2016 для Windows

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

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

    Изображение ленты с командами для вставки каскадной диаграммы

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

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

    Вкладки "Конструктор" и "Формат" в разделе "Работа с диаграммами"

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

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

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

    Область задач "Формат точки данных" с установленным флажком "Задать как итог" в Office 2016 для Windows

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

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

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

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

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

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

    Область задач "Формат точки данных" со снятым флажком "Отображать соединительные линии" в Office 2016

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

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

    Вот как можно создать каскадную диаграмму в Excel для Mac:

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

    Каскадная диаграмма на ленте

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

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector