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

В этом руководстве будет показано, как создать диаграмму водопада во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

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

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

Этот тип диаграмм широко используется профессионалами во всех основных отраслях промышленности для проведения количественного анализа - от расшифровки финансовых отчетов до мониторинга запасов в магазинах.

Графики-мосты были представлены как встроенный тип диаграммы только в Excel 2016, а это означает, что у пользователей более ранних версий Excel нет другого выбора, кроме как создать диаграмму с нуля.

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

Начиная

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

Имея это в виду, рассмотрим следующие данные:

Чтобы помочь вам настроить диаграмму в соответствии с вашими потребностями, давайте поговорим о таблице более подробно:

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

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

Как создать диаграмму водопада в Excel 2016+

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

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

Однако все это связано с ограниченными возможностями настройки и потенциальными проблемами совместимости; старые версии Excel просто не могут обрабатывать более свежие собственные типы диаграмм.

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

Шаг № 1: Постройте диаграмму водопада.

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

1. Выделите все данные (A1: B13).
2. Перейти к Вставлять таб.
3. Выберите «Вставить водопад, воронку, биржу, поверхность или радарную диаграмму" кнопка.
4. Выбирать "Водопад."

Мгновенно появится ваша таблица летающих кирпичей:

Шаг № 2: Задайте столбцы промежуточных итогов и итогов.

Наш следующий шаг - отделить начальные и конечные значения от «летающих кирпичей».

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

Технически вы можете остановиться прямо здесь и оставить график как есть. Но этот каскадный график ничего не говорит нам о нанесенных на него данных.

Зачем соглашаться на меньшее? Вместо этого вы можете существенно улучшить свою недавно созданную диаграмму Марио, изменив кое-что здесь и там.

Шаг № 3: Измените цветовую схему.

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

1. Дважды щелкните, а затем щелкните правой кнопкой мыши элемент легенды, представляющий Серия «Снижение».
2. Выберите «Наполнять" кнопка.
3. Выбирать красный из цветовой палитры.

Таким же образом перекрасим Серия «Повышение» к зеленому и Серия «Итоги» к синему. Там ваша каскадная диаграмма должна выглядеть так:

Шаг №4: Настройте диапазоны вертикальной оси в соответствии с вашими фактическими данными.

Чтобы увеличить плавающие столбцы для получения более подробной информации, измените масштаб вертикальной оси. Щелкните правой кнопкой мыши основную вертикальную ось и выберите «Ось формата.”

в Ось формата панель задач, выполните следующие простые шаги:

1. Переключитесь на Параметры оси таб.
2. Установить Минимальные границы к "50,000.”
3. Установить Максимальные границы к "110,000.”

Шаг № 5: Настройте детали.

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

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

Наконец, измените заголовок диаграммы, и все готово!

Как создать диаграмму водопада в Excel 2007, 2010 и 2013

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

По этой причине вы можете захотеть вручную построить диаграмму с нуля, даже если вы используете Excel 2016 или 2022.

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

К концу этого раздела вы узнаете, как создать эту настраиваемую динамическую мостовую диаграмму, совместимую со всеми версиями Excel:

Без лишних слов, давайте приступим к делу.

Шаг № 1: Подготовьте данные диаграммы.

Честно говоря, это самый тяжелый шаг.

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

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

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

Начнем с добавления трех вспомогательных столбцов с именем «Невидимый” (столбец B), “Увеличивать” (столбец C), а также "Снижаться” (столбец D), которые будут содержать фиктивные значения для размещения ваших фактических данных на будущей диаграмме.

И если столбцы Увеличить а также Снижаться говорят сами за себя, в чем дело столбец Невидимый? По сути, эти значения будут формировать невидимую основу будущей столбчатой ​​диаграммы с накоплением, чтобы поддерживать фактические значения до соответствующей высоты.

Давай разберемся с столбец Увеличение сначала: введите следующую формулу в ячейку C2 и перетащите его вниз, используя маркер заполнения, чтобы выполнить его для оставшихся ячеек (C3: C13):

1	= ЕСЛИ (E2> 0, E2, 0)

Теперь введите формулу зеркала в первую ячейку столбец Уменьшение (столбец D) и также скопируйте его:

1	= ЕСЛИ (E2 <= 0, -E2, 0)

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

1	= B2 + C2-D3

ПРИМЕЧАНИЕ. Совершенно необходимо оставить первую и последнюю ячейки (Би 2 а также B13) в столбце пусто!

Не забудьте отформатировать все данные диаграммы как валюту. (На главную> Группа номеров> Валюта).

На этом этапе ваша развернутая таблица должна выглядеть так:

В качестве последней корректировки настройте еще один столбец с именем «Этикетки», Который будет содержать причудливые пользовательские метки данных.

После настройки этого столбца введите эту формулу во вторую ячейку Ярлыки столбцов (F3) и скопируйте его, оставив первую ячейку (F2) и последняя ячейка (F13) пустой:

1	= ЕСЛИ (E3 <= 0, ТЕКСТ (-E3, "$ #, ##") & СИМВОЛ (10) & ТЕКСТ (-E3 / СУММ (B3: D3), "(#% ↓)"), ТЕКСТ (E3, "$ #, ##") & CHAR (10) & TEXT (E3 / SUM (B3: D3), "(#% ↑)"))

Тем, кто работает с другими числовыми форматами, следует вместо этого применить эту формулу:

1	= ЕСЛИ (E3 <= 0, ТЕКСТ (-E3, "#, ##") & СИМВОЛ (10) & ТЕКСТ (-E3 / СУММ (B3: D3), "(#% ↓)"), ТЕКСТ (E3, " #, ## ") & CHAR (10) & TEXT (E3 / SUM (B3: D3)," (#% ↑) "))

Шаг № 2: Создайте столбчатую диаграмму с накоплением.

Мы преодолели горку, так что, наконец, пришло время построить нашу диаграмму водопадов.

1. Выбрать все данные в столбцах Период, Невидимый, Увеличение, а также Снижаться (A1: D13).
2. Перейти к Вставлять таб.
3. Нажмите «Вставить столбец или гистограмму.”
4. Выбирать "Столбец с накоплением.”

Excel построит этот простой график, который в конечном итоге превратится в потрясающую диаграмму водопада:

Шаг № 3: Скрыть серию «Невидимый».

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

Оказавшись там, сделайте невидимыми вспомогательные столбцы:

1. Перейти к Заливка и линия вкладка Наполнять вкладка в старых версиях Excel.
2. Под "Наполнять," Выбрать "Без заливки.”
3. Под "Граница,"Нажмите"Нет линии.”

Шаг №4: Настройте цветовую схему.

В то же самое Форматировать ряд данных на панели задач перекрасите каждую серию данных, выполнив те же шаги, что и выше (Варианты серий > Наполнять > Сплошная заливка > Цвет), чтобы его можно было интуитивно понять с первого взгляда:

1. Для Серия «Снижение» выбирать красный.
2. Для Серия «Повышение» выбирать зеленый.
3. Кроме того, дважды щелкните столбцы «Переходящий остаток» а также "Текущий баланс" и перекрасить их синий.

Вот что у вас должно получиться на этом этапе:

Шаг № 5: Измените ширину зазора на «20%».

Теперь увеличьте ширину столбцов для лучшего визуального восприятия.

1. Открыть Форматировать ряд данных панель задач (щелкните правой кнопкой мыши одну из серий данных и выберите Форматировать ряд данных) и переключитесь на Варианты серий таб.
2. Изменять "Ширина зазора" к "20%.”

Шаг № 6: Отрегулируйте диапазоны вертикальной оси.

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

1. Щелкните правой кнопкой мыши вертикальную ось и выберите «Ось формата.”
2. На панели задач откройте Параметры оси таб.
3. Под "Границы," изменение "Минимум" к "50,000" а также "Максимум" к "110,000.”

Шаг № 7: Добавьте и разместите пользовательские метки данных.

Эти персонализированные ярлыки данных слишком долго ждали, так что давайте, наконец, применим их. Щелкните правой кнопкой мыши любой столбец и выберите «Добавьте метки данных.”

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

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

1. Дважды щелкните метку данных, которую хотите изменить.
2. Входить "=”В Панель формул.
3. Выделите соответствующую ячейку в Ярлыки столбцов.

Замените оставшиеся метки данных, повторив тот же процесс для каждого плавающего столбца.

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

После того, как вы успешно применили метки, ваша диаграмма Марио должна трансформироваться во что-то вроде этого:

Шаг № 8: Очистите область диаграммы.

Наконец, удалите легенду диаграммы и линии сетки, которые ничего не приносят в таблицу (Щелкните правой кнопкой мыши > Удалить). Измените название диаграммы, и вот у вас есть диаграмма водопада!