Как да изградим водопадна диаграма

Все по-често срещам в отчетите на различни фирми и чувам молби от стажанти да обяснят как се изгражда каскадна диаграма на отклоненията – също е „водопад“, също е „водопад“, също е „мост“ ”, също е „мост” и т.н. Изглежда нещо подобно:

Отдалеч наистина изглежда като каскада от водопади на планинска река или висящ мост – кой каквото вижда 🙂

Особеността на такава диаграма е, че:

• Ясно виждаме началната и крайната стойност на параметъра (първата и последната колона).
• Положителните промени (растеж) се показват в един цвят (обикновено зелен), и отрицателни (намаляване) към други (обикновено червен).
  
• Понякога диаграмата може също да съдържа колони с междинни суми (сивкацнал върху колоните по оста x).
  

В ежедневието такива диаграми обикновено се използват в следните случаи:

• зрителен динамичен дисплей всеки процес във времето: паричен поток (паричен поток), инвестиции (ние инвестираме в проект и получаваме печалба от него).
• Визуализация изпълнение на плана (най-лявата колона в диаграмата е факт, най-дясната колона е план, цялата диаграма отразява нашия процес на придвижване към желания резултат)
• Когато имате нужда от визуални показват факторикоито влияят на нашия параметър (факторен анализ на печалбата – от какво се състои).
  

Има няколко начина за изграждане на такава диаграма – всичко зависи от вашата версия на Microsoft Excel.

Метод 1: Най-лесният: Вграден тип в Excel 2016 и по-нова версия

Ако имате Excel 2016, 2019 или по-нова версия (или Office 365), тогава изграждането на такава диаграма не е трудно – тези версии на Excel вече имат този тип вграден по подразбиране. Ще бъде необходимо само да изберете таблица с данни и да изберете в раздела Поставете (вмъкване) Команда каскаден (Водопад):

В резултат на това ще получим почти готова диаграма:

Можете веднага да зададете желаните цветове за запълване на положителните и отрицателните колони. Най-лесният начин да направите това е да изберете подходящите редове Нараства и намаление директно в легендата и като щракнете с десния бутон върху тях, изберете командата Напълнете (Попълване):

Ако трябва да добавите колони с междинни суми или крайна колона-сума към диаграмата, най-удобно е да направите това с помощта на функции МЕЖДИННИ СУМИ (МЕЖДИННИ СУМИ) or МЕРНА ЕДИНИЦА (ОБЩО). Те ще изчислят сумата, натрупана от началото на таблицата, като изключат от нея подобни суми, разположени по-горе:

В този случай първият аргумент (9) е кодът на операцията за математическо сумиране, а вторият (0) кара функцията да игнорира вече изчислените суми за предходни тримесечия в резултатите.

След добавяне на редове с суми, остава да изберете колоните с суми, които са се появили на диаграмата (направете два последователни единични клика върху колоната) и с десен клик на мишката изберете командата Задайте като общо (Задайте като общо):

Избраната колона ще се приземи на оста x и автоматично ще промени цвета си на сив.

Това всъщност е всичко - диаграмата на водопада е готова:

Метод 2. Универсален: невидими колони

Ако имате Excel 2013 или по-стари версии (2010, 2007 и т.н.), тогава описаният по-горе метод няма да работи за вас. Ще трябва да обиколите и да изрежете липсващата водопадна диаграма от обикновена подредена хистограма (сумирайки лентите една върху друга).

Номерът тук е да използваме прозрачни опорни колони, за да повдигнем нашите червени и зелени редове с данни до правилната височина:

За да изградим такава диаграма, трябва да добавим още няколко помощни колони с формули към изходните данни:

• Първо, трябва да разделим нашата оригинална колона, като разделим положителните и отрицателните стойности в отделни колони с помощта на функцията IF (АКО).  
• Второ, ще трябва да добавите колона пред колоните залъгалки, където първата стойност ще бъде 0 и започвайки от втората клетка, формулата ще изчисли височината на тези много прозрачни поддържащи колони.
  

След това остава да изберете цялата таблица с изключение на оригиналната колона Състояние на Поток и създайте правилна подредена хистограма напречно Вмъкване — Хистограма (Вмъкване — колонна диаграма):

Ако сега изберете сините колони и ги направите невидими (щракнете с десния бутон върху тях – Формат на ред – Попълване – Без попълване), тогава просто получаваме това, от което се нуждаем. 

Предимството на този метод е простотата. В минусите - необходимостта от преброяване на спомагателни колони.

Метод 3. Ако отидем на червено, всичко е по-трудно

За съжаление предишният метод работи адекватно само за положителни стойности. Ако поне в някаква област нашият водопад премине в отрицателна зона, тогава сложността на задачата се увеличава значително. В този случай ще е необходимо да се изчисли всеки ред (фиктивен, зелен и червен) отделно за отрицателните и положителните части с формули:

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

Метод 4. Екзотика: ленти нагоре-надолу

Този метод се основава на използването на специален малко известен елемент от плоски диаграми (хистограми и графики) - Ленти нагоре-надолу (ленти нагоре-надолу). Тези ленти свързват точките на две графики по двойки, за да покажат ясно коя от двете точки е по-висока или по-ниска, което се използва активно при визуализиране на план-факт:

Лесно е да разберем, че ако премахнем линиите на диаграмите и оставим само лентите нагоре-надолу на диаграмата, тогава ще получим същия „водопад“.

За такава конструкция трябва да добавим още две допълнителни колони към нашата таблица с прости формули, които ще изчислят позицията на двете необходими невидими графики:

За да създадете „водопад“, трябва да изберете колона с месеци (за подписи по оста X) и две допълнителни колони График 1 и График 2 и изградете редовна графика за начинаещи Вмъкване – графика (Вмъкване — линейна диаграма):

Сега нека добавим ленти нагоре-надолу към нашата диаграма:

• В Excel 2013 и по-нова версия това трябва да бъде избрано в раздела конструктор Команда Добавете елемент на диаграма — Диапазон на увеличение-намаляване (Дизайн — Добавяне на елемент от диаграма — Ленти нагоре-надолу)
• В Excel 2007-2010 – отидете на раздела Оформление – ленти с напредване и намаляване (Оформление — Ленти нагоре-надолу)

Тогава диаграмата ще изглежда така:

Остава да изберете графиките и да ги направите прозрачни, като щракнете върху тях на свой ред с десния бутон на мишката и изберете командата Формат на серията данни (серия формат). По същия начин можете да промените стандартните, доста изтъркани цветове на черни и бели ивици на зелени и червени, за да получите по-хубава картина в крайна сметка:

В най-новите версии на Microsoft Excel ширината на лентите може да се промени, като щракнете върху една от прозрачните графики (не лентите!) с десния бутон на мишката и изберете командата Формат на серията данни – странично разстояние (Поредица от формати — Ширина на интервала).

В по-старите версии на Excel трябваше да използвате командата Visual Basic, за да коригирате това:

1. Маркирайте построената диаграма
2. Натиснете клавишната комбинация Друг+F11за да влезете в редактора на Visual Basic
3. Натиснете клавишната комбинация Ctrl+Gза да отворите панела за директно въвеждане на команди и отстраняване на грешки Незабавен (обикновено се намира в долната част).

4. Копирайте и поставете следната команда там: ActiveChart.ChartGroups(1).GapWidth = 30 и натиснете Въведете:

Можете, разбира се, да си поиграете със стойността на параметъра, ако желаете. GapWidthза постигане на желания клирънс:

• Как да изградите диаграма с водещи точки в Excel, за да визуализирате KPI  
• Какво е новото в диаграмите в Excel 2013
• Как да създадете интерактивна „жива“ диаграма в Excel