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

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

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

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

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

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

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

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

3. Диаграмма теперь вставлена на лист. См. скриншот:

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

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

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

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

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

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

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

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

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.) Наконец, нажмите кнопку ОК.
   • 
3. Теперь Горизонтальная водопадная диаграмма успешно создан. См. скриншот:
   

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

Скачать образец файла диаграммы водопада

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