gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 246 гостей онлайн
Excel 2013. Как сделать диаграмму «Водопад» в Excel.

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

      Диаграммы в Excel можно построить по разным данным. Можно в диаграмме показать текущие данные, а можно показать только разницу данных по периодам. Диаграмма водопад в Excel поможет отобразить только изменение данных по периодам, как положительных, так и отрицательных. Поможет провести анализ в Excel любой деятельности.
      Для того, чтобы увидеть и сравнить рост и снижение данных на графике, строим диаграмму «Водопад».
Есть несколько вариантов, как построить диаграмму Водопад.  
Как работать с диаграммой, графиком, где расположены функции, смотрите в статье «Как сделать диаграмму в Excel».
      Первый способ.
      Как сделать диаграмму водопад в Excel, когда все данные в таблице положительные.
Например, нам нужно посмотреть, как движется прибыль по месяцам. Построим таблицу.
В столбце A – наименование (товар, прибыль, период, т.д.), любые данные, изменение по которым хотим увидеть на графике.
В столбце C пишем цифры роста (прибыли, товара, т.д.) В столбце D пишем цифры снижения.
В столбце B пишем в верхней ячейке число, от которого будем отталкиваться. Например, остаток на начало месяца, если хотим увидеть разницу по месяцам, года – если хотим посмотреть разницу по годам, т.д.  
В ячейке B3 пишем формулу, которая будет считать остаток на начало следующего периода (месяца, года).  =В2+С3-D3  Пояснение формулы – остаток на январь  плюс приход за февраль, минус расход за февраль, получается остаток на февраль и на начало марта. Копируем формулу по столбцу. У нас получилась такая таблица.
Теперь выделяем всю таблицу с шапкой. На закладке «Вставка» в разделе «Диаграммы» нажимаем на кнопку «Гистограмма». Выбираем  - «Гистограмма с накоплением».
Получился такой график.
Теперь нужно изменить цвета столбцов диаграммы. Синий цвет убрать, сделать эти столбцы прозрачными. Нажимаем на синий столбец на диаграмме. Столбцы выделились. Нажимаем на синий столбец правой мышкой, выбираем - «Формат ряда данных» -> "Параметры ряда" -> «Заливка» -> «Нет заливки».
      Как работать с диаграммой, на каких закладках найти функции, как изменить цвет, ширину столбца диаграммы, т.д., читайте в статье «Как сделать график в Excel».
Теперь установим зеленый цвет для данных прихода (повышения)и красный цвет для данных расхода (снижения). Получилось так.Можно сделать столбцы шире. Нажимаем правой мышкой на столбец диаграммы и выбираем из контекстного меню функцию «Формат ряда данных». В разделе «Параметры ряда» уменьшаем «Зазор». Мы поставили 20%.
Получилось так.
Как построить диаграмму водопад в Excel.      Второй способ.
      Как сделать диаграмму водопад в Excel, когда данные в таблице положительные и отрицательные.
У нас такая таблица по движению финансовых средств по месяцам.
Нам нужно построить диаграмму так, чтобы положительные данные на диаграмме отложились выше нулевой оси, а отрицательные числа - ниже нулевой оси. Для этого нужно рассчитать несколько дополнительных данных. Формулами это сделать быстро. Вся таблица получится такая.
В столбце C посчитали данные нарастающим итогом – к данным предыдущего месяца прибавили данные текущего месяца. Например, к значению за январь прибавили значение за февраль.
В ячейке С4 напишем такую формулу.  =C3+B4  Получилось накопление нарастающим итогом за февраль. (3 + (-2,5)= 0,5). В феврале произошло снижение, но итоговое значение осталось положительным. Эти данные отложатся на диаграмме над нулевой осью. А в апреле итоговое значение отрицательное. Оно отложится под нулевой линией.
      Для этих целей разобьем все данные по отдельным колонкам, чтобы диаграмма построилась правильно.
Итак, формулы такие. Все формулы пишем в строке 4, затем копируем вниз по столбцу.
      В ячейке E4 формула будет такая.  =ЕСЛИ(И(C4<0;C3<0);МАКС(C3:C4);0)
      В ячейке F4.  =ЕСЛИ(И(B4>0;C4>0);МИН(B4;C4);0)
      В ячейке G4.  =ЕСЛИ(И(B4>0;C3<0);МАКС(-B4;C3);0)
      В ячейке H4.  =ЕСЛИ(И(C4>0;C3>0);МИН(C3:C4);0)
      В ячейке I4.   =ЕСЛИ(И(B4<0;C4<0);МАКС(B4;C4);0)
      В ячейке J4.  =ЕСЛИ(И(B4<0;C3>0);МИН(-B4;C3);0)
В таблице название столбцов окрашено тем же цветом, что и будем раскрашивать столбцы в диаграмме. Не окрашенные столбцы в диаграмме сделаем прозрачными.
      Строим диаграмму Водопад в Excel.
Выделяем столбец A с названием столбца. Нажимаем клавишу «Ctrl», удерживая её нажатой, выделяем столбцы с D до J. Получается, мы не выделили только два столбца – В и С.
      На закладке «Вставка» в разделе «Диаграммы» нажимаем на кнопку «Гистограмма». Выбираем  - «Гистограмма с накоплением». Получилась такая диаграмма.
Нажимаем на диаграмму. На закладке «Конструктор» в разделе «Данные» нажимаем на кнопку функции «Строка/Столбец». Меняем местами в диаграмме строки и столбцы. Легенду на диаграмме расположили справа. Получилось так.Теперь осталось разобраться с цветами столбцов. Нажимаем на столбец в диаграмме правой мышкой. Из контекстного меню выбираем функцию «Формат ряда данных». Устанавливаем нужный цвет.
Столбец января окрасили в серый цвет – это точка отсчета.
Дополнительные столбцы – ставим – «Нет заливки», они станут прозрачными.
Столбцы прихода окрашиваем в зеленый цвет. Столбцы расхода окрашиваем в красный цвет. Получилось так.Как сделать диаграмму водопад в Excel.По этому графику видно, что только в июне вышли в плюс, хоть рост был и в мае.
      Третий способ.
Еще один способ построения диаграммы «Водопад» в Excel с помощью графика. У нас такая таблиц
В ячейке C3 стоит такая формула.   =D2    В ячейке D3 стоит такая формула.  =D2+B3
Формулы копируем вниз по столбцам. Выделяем столбцы A,C, D (кроме столбца В)с помощью клавиши «Ctrl» (смотрите второй способ).На закладке «Вставка» нажимаем кнопку функции «График». Получился такой график.Теперь нужно добавить полосы повышения и понижения. В Excel 2013 нажимаем на плюсик справа от диаграммы. Или на закладке «Конструктор» нажимаем на кнопку функции «Добавить элемент диаграммы», Выбираем функцию «Полосы повышения и понижения». В Excel 2007 на закладке «Макет» в разделе «Анализ» нажимаем на кнопку «Полосы повышения/понижения». Получилось так.Осталось изменить цвет столбцов в диаграмме, убрать цвет у линий графика. Получилось так.Как составить диаграмму в Excel.      В Excel можно выделить повторяющиеся данные цветом, но и словами, числами, знаками, формулой, т.д. Смотрите о таких приемах в статье "Как найти повторяющиеся значения в Excel".