Kā izveidot ūdenskrituma diagrammu

Arvien biežāk tiekos dažādu uzņēmumu atskaitēs un dzirdu praktikantu lūgumus paskaidrot, kā tiek veidota noviržu kaskādes diagramma – tas arī ir “ūdenskritums”, tas ir arī “ūdenskritums”, tas ir arī “tilts”. ”, tas ir arī “tilts” utt. Tas izskatās apmēram šādi:

No attāluma tas tiešām izskatās pēc ūdenskritumu kaskādes kalnu upē vai piekārta tilta – kurš ko redz 🙂

Šādas diagrammas īpatnība ir tāda, ka:

• Mēs skaidri redzam parametra sākotnējo un galīgo vērtību (pirmā un pēdējā kolonna).
• Pozitīvās izmaiņas (izaugsme) tiek parādītas vienā krāsā (parasti zaļš), un negatīvos (noraidīšana) citiem (parasti). sarkans).
  
• Dažreiz diagrammā var būt arī starpsummas kolonnas (pelēksnosēdās uz x ass kolonnām).
  

Ikdienas dzīvē šādas diagrammas parasti izmanto šādos gadījumos:

• redzes dinamikas displejs jebkurš process laikā: naudas plūsma (cash-flow), investīcijas (ieguldām projektā un gūstam no tā peļņu).
• Vizualizācija plāna īstenošana (diagrammas galējā kreisā kolonna ir fakts, galējā labā kolonna ir plāns, visa diagramma atspoguļo mūsu procesu virzībā uz vēlamo rezultātu)
• Kad nepieciešams vizuālais parādīt faktoruskas ietekmē mūsu parametru (peļņas faktoriālā analīze – no kā tā sastāv).
  

Ir vairāki veidi, kā izveidot šādu diagrammu — tas viss ir atkarīgs no jūsu Microsoft Excel versijas.

1. metode: vienkāršākā: iebūvētais tips programmā Excel 2016 un jaunākās versijās

Ja jums ir Excel 2016, 2019 vai jaunāka versija (vai Office 365), tad šādas diagrammas izveide nav grūta – šajās Excel versijās šis tips jau ir iebūvēts pēc noklusējuma. Būs tikai jāizvēlas tabula ar datiem un jāatlasa cilnē ielaidums (Ievietot) Komanda kaskādes (Ūdenskritums):

Rezultātā mēs iegūsim gandrīz gatavu diagrammu:

Jūs varat nekavējoties iestatīt vajadzīgās aizpildījuma krāsas pozitīvajām un negatīvajām kolonnām. Vienkāršākais veids, kā to izdarīt, ir atlasīt atbilstošās rindas Palielināt и samazināt tieši leģendā un, ar peles labo pogu noklikšķinot uz tiem, atlasiet komandu Aizpildīt (Aizpildīt):

Ja diagrammai jāpievieno kolonnas ar starpsummas vai galīgo kolonnu kopsummu, visērtāk to izdarīt, izmantojot funkcijas STARPKOPĀS (SUBTOTALS) or UNIT (APKOPOJUMS). Viņi aprēķinās no tabulas sākuma uzkrāto summu, vienlaikus izslēdzot no tās līdzīgās kopsummas, kas atrodas iepriekš:

Šajā gadījumā pirmais arguments (9) ir matemātiskās summēšanas operācijas kods, bet otrais (0) liek funkcijai ignorēt rezultātos jau aprēķinātās kopsummas par iepriekšējiem ceturkšņiem.

Pēc rindu ar summām pievienošanas atliek atlasīt diagrammā parādījušās kopējās kolonnas (uz kolonnas veiciet divus secīgus vienreizējus klikšķus) un, ar peles labo pogu noklikšķinot, atlasiet komandu Iestatīt kā kopējo (Iestatīt kopā):

Atlasītā kolonna nonāks uz x ass un automātiski mainīs krāsu uz pelēku.

Tas arī viss – ūdenskrituma diagramma ir gatava:

2. metode. Universāls: neredzamas kolonnas

Ja jums ir Excel 2013 vai vecāka versija (2010, 2007 utt.), tad iepriekš aprakstītā metode jums nederēs. Jums būs jāapiet un jāizgriež trūkstošā ūdenskrituma diagramma no parastas stacked histogrammas (summējot joslas vienu virs otras).

Šis triks ir izmantot caurspīdīgas atbalsta kolonnas, lai mūsu sarkanās un zaļās datu rindas paaugstinātu līdz pareizajam augstumam.

Lai izveidotu šādu diagrammu, avota datiem jāpievieno vēl dažas papildu kolonnas ar formulām:

• Pirmkārt, mums ir jāsadala sākotnējā kolonna, atdalot pozitīvās un negatīvās vērtības atsevišķās kolonnās, izmantojot funkciju IF (JA).  
• Otrkārt, kolonnu priekšā būs jāpievieno kolonna mānekļi, kur pirmā vērtība būs 0 un, sākot no otrās šūnas, formula aprēķinās šo ļoti caurspīdīgo atbalsta kolonnu augstumu.
  

Pēc tam atliek atlasīt visu tabulu, izņemot sākotnējo kolonnu Plūsma un izveidojiet regulāru grupētu histogrammu šķērsām Ielaidums — histogramma (Ievietot — kolonnu diagramma):

Ja tagad atlasāt zilās kolonnas un padarīsit tās neredzamas (ar peles labo pogu noklikšķiniet uz tām - Rindas formāts – Aizpildīt – Neaizpildīt), tad mēs vienkārši iegūstam to, kas mums nepieciešams. 

Šīs metodes priekšrocība ir vienkāršība. Mīnusos – nepieciešamība skaitīt palīgailes.

3. metode. Ja mēs ieejam mīnusā, viss ir grūtāk

Diemžēl iepriekšējā metode adekvāti darbojas tikai pozitīvām vērtībām. Ja vismaz kādā apgabalā mūsu ūdenskritums nonāk negatīvā zonā, tad uzdevuma sarežģītība ievērojami palielinās. Šajā gadījumā katra rinda (fiktīva, zaļa un sarkana) būs jāaprēķina atsevišķi negatīvajām un pozitīvajām daļām ar formulām:

Lai daudz neciestu un neizgudrotu riteni no jauna, šī raksta nosaukumā var lejupielādēt gatavu veidni šādam gadījumam.

4. metode. Eksotiskais: augšup-leju joslas

Šīs metodes pamatā ir īpaša mazpazīstama plakano diagrammu elementa (histogrammu un grafiku) izmantošana – Augšup-leju joslas (augšup-lejup joslas). Šīs joslas savieno divu grafiku punktus pa pāriem, lai skaidri parādītu, kurš no diviem punktiem ir augstāks vai zemāks, ko aktīvi izmanto, vizualizējot plānu-faktu:

Ir viegli saprast, ka, ja mēs noņemsim diagrammu līnijas un diagrammā atstājam tikai augšup-lejup joslas, mēs iegūsim to pašu “ūdenskritumu”.

Šādai konstrukcijai mūsu tabulai jāpievieno vēl divas papildu kolonnas ar vienkāršām formulām, kas aprēķinās divu nepieciešamo neredzamo grafiku atrašanās vietu:

Lai izveidotu “ūdenskritumu”, ir jāatlasa kolonna ar mēnešiem (parakstiem gar X asi) un divas papildu kolonnas Grafiks 1 и Grafiks 2 un izveidojiet regulāru grafiku iesācējiem Ievietot - grafiks (Ievietot — diagrammas rinda):

Tagad pievienosim diagrammai augšupvērstas joslas:

• Programmā Excel 2013 un jaunākās versijās tas ir jāatlasa cilnē Konstruktors Komanda Pievienojiet diagrammas elementu — Palielinājuma-samazinājuma joslas (Dizains — pievienot diagrammas elementu — augšupvērstas joslas)
• Programmā Excel 2007–2010 — dodieties uz cilni Izkārtojums — Advance-Decrement Bars (Izkārtojums — augšup-lejup joslas)

Pēc tam diagramma izskatīsies apmēram šādi:

Atliek atlasīt grafikus un padarīt tos caurspīdīgus, pēc kārtas noklikšķinot uz tiem ar peles labo pogu un izvēloties komandu Datu sērijas formāts (Sērija formāta). Līdzīgi varat mainīt standarta, diezgan nobružātā izskata melnbaltās svītru krāsas uz zaļu un sarkanu, lai beigās iegūtu jaukāku attēlu:

Jaunākajās Microsoft Excel versijās joslu platumu var mainīt, ar peles labo pogu noklikšķinot uz kāda no caurspīdīgajiem grafikiem (nevis uz joslām!) un izvēloties komandu Datu sērijas formāts — sānu klīrenss (Formātu sērija — atstarpes platums).

Vecākajās Excel versijās jums bija jāizmanto komanda Visual Basic, lai to labotu:

1. Iezīmējiet izveidoto diagrammu
2. Nospiediet īsinājumtaustiņu cits+F11lai iekļūtu Visual Basic redaktorā
3. Nospiediet īsinājumtaustiņu Ctrl+Glai atvērtu tiešo komandu ievades un atkļūdošanas paneli tūlītējs (parasti atrodas apakšā).

4. Kopējiet un ielīmējiet tur šādu komandu: ActiveChart.ChartGroups(1).GapWidth = 30 un nospiediet ienākt:

Ja vēlaties, protams, varat paspēlēties ar parametra vērtību. GapWidthlai sasniegtu vēlamo klīrensu:

• Kā programmā Excel izveidot aizzīmju diagrammu, lai vizualizētu KPI  
• Kas jauns programmā Excel 2013 diagrammās
• Kā programmā Excel izveidot interaktīvu "dzīvu" diagrammu