Како да се изгради табела за водопади

Се почесто се среќавам во известувањето на различни компании и слушам барања од специјализанти да објаснат како се гради каскаден дијаграм на отстапувања – тоа е и „водопад“, исто така е „водопад“, исто така е „мост“. “, тоа е и „мост“ итн. Изгледа нешто вака:

Од далечина, навистина изгледа како каскада од водопади на планинска река или висечки мост - кој што гледа 🙂

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

• Јасно ја гледаме почетната и конечната вредност на параметарот (првата и последната колона).
• Позитивните промени (раст) се прикажуваат во една боја (обично зелена), а негативните (опаѓаат) на другите (обично црвено).
  
• Понекогаш графиконот може да содржи и потвкупни колони (сиваслета на столбовите на оската x).
  

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

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

Постојат неколку начини да се изгради таков графикон - сето тоа зависи од вашата верзија на 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. Притиснете ја кратенката на тастатурата Alt+F11за да влезете во уредникот на Visual Basic
3. Притиснете кратенка на тастатурата Ctrl+Gза да го отворите панелот за директно внесување команди и отстранување грешки непосредна (обично се наоѓа на дното).

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

Се разбира, можете да си играте со вредноста на параметарот ако сакате. GapWidthза да го постигнете саканиот клиренс:

• Како да се изгради табела со точки во Excel за да се визуелизира KPI  
• Што е ново во графиконите во Excel 2013 година
• Како да креирате интерактивен графикон „во живо“ во Excel