Создание водопадной или мостовой диаграммы в Excel

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

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

• Создание водопадной диаграммы в Excel 2016 и более поздних версиях
• Создание водопадной диаграммы в Excel 2013 и более ранних версиях
• Создание горизонтальной водопадной диаграммы в Excel с помощью Kutools
• Скачать пример файла с водопадной диаграммой
• Видео: Создание водопадной диаграммы в Excel

Создание водопадной диаграммы в Excel 2016 и более поздних версиях

В Excel 2016 и более поздних версиях была представлена новая встроенная водопадная диаграмма. Таким образом, вы можете создать эту диаграмму быстро и легко, выполнив следующие шаги:

1. Подготовьте свои данные и рассчитайте окончательный чистый доход, как показано на скриншоте ниже:

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

3. И теперь диаграмма была вставлена в лист, см. скриншот:

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

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

6. Наконец, чтобы сделать вашу диаграмму более профессиональной:

• Переименуйте заголовок диаграммы по своему усмотрению;
• Удалите или отформатируйте легенду по своему усмотрению, в этом примере я удалю её.
• Присвойте разные цвета для отрицательных и положительных чисел, например, столбцы с отрицательными числами - оранжевым, а с положительными числами - зеленым. Для этого дважды щелкните одну точку данных, затем нажмите Формат > Заливка фигуры, затем выберите нужный цвет, см. скриншот:

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

Создание водопадной диаграммы в Excel 2013 и более ранних версиях

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

Создание вспомогательных столбцов для исходных данных:

1. Сначала вы должны переформатировать диапазон данных, вставив три столбца между двумя исходными столбцами, дав им заголовки как База, Вниз и Вверх. См. скриншот:

2. Затем, пожалуйста, введите формулу: =IF(E2<=0, -E2,0) в ячейку C2, и протяните маркер заполнения до ячейки C11, вы получите все отрицательные числа из столбца E, которые будут отображаться как положительные числа, а все положительные числа будут отображаться как 0. См. скриншот:

3. Продолжайте вводить эту формулу: =IF(E2>0, E2,0) в ячейку D2, затем протяните маркер заполнения до ячейки D11, теперь вы получите результат, если ячейка E2 больше 0, все положительные числа будут отображаться как положительные, а все отрицательные числа будут отображаться как 0. См. скриншот:

4. Затем введите эту формулу: =B2+D2-C3 в ячейку B3 столбца База, и протяните маркер заполнения до ячейки B12, см. скриншот:

Вставка стековой диаграммы и форматирование её как водопадной диаграммы

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

6. Затем нажмите Вставка > Вставить столбчатую или гистограммную диаграмму > Стековая гистограмма, и диаграмма будет вставлена, как показано на скриншотах ниже:

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

8. В открывшейся панели Формат ряда данных, под значком Заливка и линия, выберите Без заливки и Без линии из разделов Заливка и Граница соответственно, см. скриншот:

9. Теперь столбцы серии База становятся невидимыми, просто удалите Базу из легенды диаграммы, и вы получите диаграмму, как показано на скриншоте ниже:

10. Затем вы можете отформатировать столбцы серий Вниз и Вверх с помощью конкретных цветов заливки, которые вам нужны. Вам просто нужно щелкнуть на серии Вниз или Вверх, затем нажать Формат > Заливка фигуры и выбрать нужный цвет, см. скриншот:

11. На этом этапе вы должны отобразить последнюю точку данных Чистого дохода, дважды щелкните последнюю точку данных, затем нажмите Формат > Заливка фигуры, выберите нужный цвет для её отображения, см. скриншот:

12. Затем дважды щелкните любой из столбцов диаграммы, чтобы открыть панель Формат точки данных, под значком Параметры ряда измените ширину зазора на меньшую, например, 10%, см. скриншот:

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

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

1. Выберите серию, к которой вы хотите добавить метку, затем щелкните правой кнопкой мыши и выберите параметр Добавить метки данных, см. скриншот:

2. Повторите операцию для других серий и удалите ненужные нулевые значения из столбцов, и вы получите следующий результат:

3. Теперь вы должны изменить положительные значения на отрицательные для столбца серии Вниз, щелкните на серии Вниз, щелкните правой кнопкой мыши и выберите параметр Формат меток данных, см. скриншот:

4. В панели Формат меток данных, в разделе Число, введите -Общий в текстовое поле Код формата, затем нажмите кнопку Добавить, см. скриншоты:

5. Теперь вы получите диаграмму с метками данных, как показано на скриншоте ниже:

Создание горизонтальной водопадной диаграммы в Excel с помощью Kutools

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

После установки Kutools для Excel выполните следующие шаги:

1. Нажмите Kutools > Диаграммы > Сравнение различий > Горизонтальная водопадная диаграмма, см. скриншот:
   
2. В диалоговом окне Горизонтальная водопадная диаграмма выполните следующие действия:
   • (1.) Выберите Диаграмма в разделе Тип диаграммы;
   • (2.) Затем выберите Метки осей и Диапазон данных отдельно в текстовых полях;
   • (3.) Наконец, нажмите кнопку OK.
   • 
3. Теперь горизонтальная водопадная диаграмма успешно создана, см. скриншот:
   

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

Скачать пример файла с водопадной диаграммой

Видео: Создание водопадной диаграммы в Excel