Як пабудаваць дыяграму вадаспаду

Усё часцей я сустракаю ў справаздачах розных кампаній і чую просьбы ад практыкантаў растлумачыць, як будуецца каскадная дыяграма адхіленняў – гэта таксама «вадаспад», гэта таксама «вадаспад», гэта яшчэ і «мост». », гэта таксама «мост» і г.д. Гэта выглядае прыкладна так:

Здалёк гэта сапраўды падобна на каскад вадаспадаў на горнай рэчцы або падвесны мост – хто што бачыць 🙂

Асаблівасцю такой дыяграмы з'яўляецца тое, што:

• Наглядна бачым пачатковае і канчатковае значэнне параметру (першы і апошні слупкі).
• Станоўчыя змены (рост) адлюстроўваюцца адным колерам (звычайна зялёны), а адмоўныя (адхіляюцца) да іншых (звычайна чырвоны).
  
• Часам дыяграма можа таксама ўтрымліваць слупкі прамежкавых вынікаў (шэрыпрызямліўся на слупкі восі х).
  

У побыце такія дыяграмы звычайна выкарыстоўваюцца ў наступных выпадках:

• візуальны дысплей дынамікі любы працэс у часе: грашовы паток (cash-flow), інвестыцыі (інвестуем у праект і атрымліваем ад гэтага прыбытак).
• Візуалізацыя выкананне плана (самы левы слупок на дыяграме - гэта факт, крайні правы слупок - план, уся дыяграма адлюстроўвае наш працэс руху да жаданага выніку)
• Калі патрэбна візуальная інфармацыя паказаць фактарыякія ўплываюць на наш параметр (факторный аналіз прыбытку - з чаго ён складаецца).
  

Ёсць некалькі спосабаў пабудовы такой дыяграмы - усё залежыць ад вашай версіі Microsoft Excel.

Спосаб 1: самы просты: убудаваны тып у Excel 2016 і навей

Калі ў вас Excel 2016, 2019 або больш позняй версіі (або Office 365), то пабудаваць такую ​​дыяграму не складзе працы - у гэтых версіях Excel гэты тып ужо ўбудаваны па змаўчанні. Трэба будзе толькі выбраць табліцу з дадзенымі і выбраць на ўкладцы Уставіць (Уставіць) Каманда каскадныя (Вадаспад):

У выніку мы атрымаем практычна гатовую схему:

Вы можаце адразу ўсталяваць жаданыя колеры залівання для станоўчых і адмоўных слупкоў. Прасцей за ўсё гэта зрабіць, выбраўшы адпаведныя радкі Павышэнне и паменшыць непасрэдна ў легендзе і пстрыкнуўшы па іх правай кнопкай мышы, абярыце каманду Fill (Запоўніць):

Калі вам трэба дадаць у дыяграму слупкі з прамежкавымі вынікамі або канчатковы слупок-вынік, то зручней за ўсё гэта зрабіць з дапамогай функцый Прамежкавыя вынікі (Прамежкавыя вынікі) or БЛОК (СУМНА). Яны палічаць суму, назапашаную з пачатку табліцы, пры гэтым выключаючы з яе аналагічныя сумы, размешчаныя вышэй:

У гэтым выпадку першы аргумент (9) з'яўляецца кодам матэматычнай аперацыі сумавання, а другі (0) прымушае функцыю ігнараваць у выніках ужо разлічаныя вынікі за папярэднія кварталы.

Пасля дадання радкоў з вынікамі застаецца вылучыць выніковыя слупкі, якія з'явіліся на дыяграме (зрабіце два паслядоўных аднаразовых пстрычкі па слупку) і пстрычкай правай кнопкі мышы выбраць каманду Усталяваць як агульную суму (Усталяваць як агульную суму):

Выбраны слупок прызямліцца на восі х і аўтаматычна зменіць колер на шэры.

Вось, уласна, і ўсё - схема вадаспаду гатовая:

Спосаб 2. Універсальны: слупкі-невідзімкі

Калі ў вас Excel 2013 або больш старыя версіі (2010, 2007 і г.д.), то апісаны вышэй спосаб вам не падыйдзе. Вам трэба будзе пайсці вакол і выразаць адсутную дыяграму вадаспаду са звычайнай складзенай гістаграмы (сумуючы палоскі адна на адну).

Хітрасць тут у тым, каб выкарыстоўваць празрыстыя слупкі рэквізітаў, каб падняць нашы чырвоныя і зялёныя радкі дадзеных на правільную вышыню:

Каб пабудаваць такую ​​дыяграму, да зыходных дадзеных трэба дадаць яшчэ некалькі дапаможных слупкоў з формуламі:

• Спачатку нам трэба падзяліць наш зыходны слупок, падзяліўшы дадатныя і адмоўныя значэнні на асобныя слупкі з дапамогай функцыі IF (КАЛІ).  
• Па-другое, вам трэба будзе дадаць слупок перад слупкамі пустышкі, дзе першае значэнне будзе 0, і, пачынаючы з другой ячэйкі, формула вылічыць вышыню гэтых вельмі празрыстых апорных слупкоў.
  

Пасля гэтага застаецца вылучыць усю табліцу за выключэннем зыходнага слупка Паток і стварыць звычайную складзеную папярок гістаграму Урэзка — Гістаграма (Устаўка — Слупковая дыяграма):

Калі зараз вы вылучыце сінія слупкі і зробіце іх нябачнымі (пстрыкніце іх правай кнопкай мышы – Фармат радка – Запаўненне – Без запаўнення), тады мы атрымліваем толькі тое, што нам трэба. 

Перавага гэтага спосабу - прастата. У мінусах – неабходнасць падліку дапаможных слупкоў.

Спосаб 3. Калі сыходзім у мінус, усё складаней

На жаль, папярэдні метад адэкватна працуе толькі для станоўчых значэнняў. Калі хаця б на нейкім участку наш вадаспад сыходзіць у адмоўную вобласць, то складанасць задачы істотна ўзрастае. У гэтым выпадку трэба будзе разлічыць кожную радок (фіктыўную, зялёную і чырвоную) асобна для адмоўнай і станоўчай частак па формулах:

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

Спосаб 4. Экзотыка: паласы ўверх-уніз

Гэты метад заснаваны на выкарыстанні асаблівага малавядомага элемента плоскіх дыяграм (гістаграм і графікаў) – Паласы ўверх-уніз (Палосы ўверх-уніз). Гэтыя паласы злучаюць кропкі двух графікаў парамі, каб выразна паказаць, якая з двух кропак вышэй або ніжэй, што актыўна выкарыстоўваецца пры візуалізацыі плана-факта:

Няцяжка зразумець, што калі прыбраць лініі з графікаў і пакінуць на графіцы толькі паласы ўверх-уніз, то атрымаецца той жа «вадаспад».

Для такой канструкцыі нам трэба дадаць у нашу табліцу яшчэ два дадатковых слупка з простымі формуламі, якія вылічаць становішча двух неабходных нябачных графікаў:

Для стварэння «вадаспаду» трэба выбраць слупок з месяцамі (для подпісаў па восі X) і два дадатковых слупка Расклад 1 и Расклад 2 і пабудуйце рэгулярны графік для пачаткоўцаў Устаўка – графік (Устаўка — лінейная дыяграма):

А цяпер дадамо паласы ўверх-уніз да нашай дыяграмы:

• У Excel 2013 і навейшых версіях гэта неабходна выбраць на ўкладцы Канструктар Каманда Дадаць элемент дыяграмы — Паласы павелічэння-памяншэння (Дызайн — Дадаць элемент дыяграмы — Слупкі ўверх-уніз)
• У Excel 2007-2010 - перайдзіце на ўкладку Разметка – панэлі з памяншэннем (Макет — панэлі ўверх-уніз)

Тады дыяграма будзе выглядаць прыкладна так:

Засталося вылучыць графікі і зрабіць іх празрыстымі, клікнуўшы па іх па чарзе правай кнопкай мышкі і выбраўшы каманду Фармат серыі даных (Сэрыя ў фармаце). Сапраўды гэтак жа вы можаце змяніць стандартныя, даволі пацёртыя колеры чорна-белых палос на зялёныя і чырвоныя, каб у выніку атрымаць больш прыгожы малюнак:

У апошніх версіях Microsoft Excel шырыню слупкоў можна змяніць, пстрыкнуўшы на адным з празрыстых графікаў (не на слупках!) правай кнопкай мышы і выбраўшы каманду Фармат серыі даных – зазор збоку (Серый фармату — Шырыня прабелу).

У старых версіях Excel вам трэба было выкарыстоўваць каманду Visual Basic, каб выправіць гэта:

1. Вылучыце пабудаваную схему
2. Націсніце спалучэнне клавіш Alt+F11каб увайсці ў рэдактар ​​Visual Basic
3. Націсніце спалучэнне клавіш Ctrl+Gкаб адкрыць панэль прамога ўводу каманд і адладкі Неадкладны (звычайна размешчаны ўнізе).

4. Скапіруйце і ўстаўце туды наступную каманду: ActiveChart.ChartGroups(1).GapWidth = 30 і націсніце Уводзіць:

Вы, вядома, можаце пагуляць са значэннем параметру, калі хочаце. GapWidthкаб дасягнуць жаданага зазору:

• Як пабудаваць дыяграму ў Excel для візуалізацыі KPI  
• Што новага ў дыяграмах у Excel 2013
• Як стварыць інтэрактыўную «жывую» дыяграму ў Excel