Si të ndërtoni një tabelë ujëvarash

Gjithnjë e më shpesh takohem në raportimet e kompanive të ndryshme dhe dëgjoj kërkesa nga të trajnuarit për të shpjeguar se si është ndërtuar një diagram kaskadë devijimesh - është gjithashtu një "ujëvarë", është gjithashtu një "ujëvarë", është gjithashtu një "urë". ”, është edhe “urë” etj. Duket diçka si kjo:

Nga një distancë, me të vërtetë duket si një kaskadë ujëvarash në një lumë malor ose një urë të varur - kush sheh çfarë 🙂

E veçanta e një diagrami të tillë është se:

• Ne shohim qartë vlerën fillestare dhe përfundimtare të parametrit (kolona e parë dhe e fundit).
• Ndryshimet pozitive (rritja) shfaqen në një ngjyrë (zakonisht e gjelbër), dhe ato negative (rënie) tek të tjerët (zakonisht i kuq).
  
• Ndonjëherë grafiku mund të përmbajë gjithashtu kolona nëntotale (griu ul në kolonat e boshtit x).
  

Në jetën e përditshme, diagrame të tilla zakonisht përdoren në rastet e mëposhtme:

• Vizual shfaqja e dinamikës çdo proces në kohë: cash flow (cash-flow), investime (ne investojmë në një projekt dhe marrim fitim prej tij).
• Vizualizimi zbatimin e planit (kolona më e majtë në diagram është një fakt, kolona më e djathtë është një plan, i gjithë diagrami pasqyron procesin tonë të lëvizjes drejt rezultatit të dëshiruar)
• Kur keni nevojë për vizuale tregojnë faktorëtqë ndikojnë në parametrin tonë (analiza faktoriale e fitimit – nga çfarë përbëhet).
  

Ka disa mënyra për të ndërtuar një grafik të tillë - gjithçka varet nga versioni juaj i Microsoft Excel.

Metoda 1: Më e lehtë: Lloji i integruar në Excel 2016 dhe më të reja

Nëse keni Excel 2016, 2019 ose më vonë (ose Office 365), atëherë ndërtimi i një grafiku të tillë nuk është i vështirë - këto versione të Excel tashmë e kanë këtë lloj të integruar si parazgjedhje. Do të jetë e nevojshme vetëm të zgjidhni një tabelë me të dhëna dhe të zgjidhni në skedën Fut (Fut) Komandë Cascading (Ujëvara):

Si rezultat, ne do të marrim një diagram pothuajse të gatshëm:

Ju mund të vendosni menjëherë ngjyrat e dëshiruara të mbushjes për kolonat pozitive dhe negative. Mënyra më e lehtë për ta bërë këtë është të zgjidhni rreshtat e duhur Rritje и ulje direkt në legjendë dhe duke klikuar me të djathtën mbi to, zgjidhni komandën Mbush (Plotëso):

Nëse keni nevojë të shtoni kolona me nëntotalë ose një total kolone përfundimtare në grafik, atëherë është më e përshtatshme ta bëni këtë duke përdorur funksionet NENTOTALE (NENTOTALE) or UNIT (AGREGATI). Ata do të llogarisin shumën e grumbulluar nga fillimi i tabelës, duke përjashtuar prej saj totalet e ngjashme të vendosura më sipër:

Në këtë rast, argumenti i parë (9) është kodi i operacionit të mbledhjes matematikore, dhe i dyti (0) bën që funksioni të injorojë totalet e llogaritura tashmë për tremujorët e mëparshëm në rezultate.

Pas shtimit të rreshtave me totale, mbetet të zgjidhni kolonat totale që janë shfaqur në diagram (bëni dy klikime të njëpasnjëshme të vetme në kolonë) dhe, duke klikuar me të djathtën mbi miun, zgjidhni komandën Cakto si total (Cakto si total):

Kolona e zgjedhur do të vendoset në boshtin x dhe do të ndryshojë automatikisht ngjyrën në gri.

Kjo, në fakt, është gjithçka - diagrami i ujëvarës është gati:

Metoda 2. Universale: kolona të padukshme

Nëse keni Excel 2013 ose versione më të vjetra (2010, 2007, etj.), atëherë metoda e përshkruar më sipër nuk do të funksionojë për ju. Ju do të duhet të shkoni përreth dhe të hiqni grafikun e ujëvarës që mungon nga një histogram i rregullt i grumbulluar (duke përmbledhur shufrat njëra mbi tjetrën).

Truku këtu është të përdorim kolona transparente mbështetëse për të ngritur rreshtat tona të kuqe dhe jeshile të të dhënave në lartësinë e duhur:

Për të ndërtuar një grafik të tillë, duhet të shtojmë disa kolona të tjera ndihmëse me formula në të dhënat burimore:

• Së pari, ne duhet të ndajmë kolonën tonë origjinale duke ndarë vlerat pozitive dhe negative në kolona të veçanta duke përdorur funksionin IF (NESE).  
• Së dyti, do t'ju duhet të shtoni një kolonë përpara kolonave biberona, ku vlera e parë do të jetë 0, dhe duke u nisur nga qeliza e dytë, formula do të llogarisë lartësinë e atyre kolonave mbështetëse shumë transparente.
  

Pas kësaj, mbetet për të zgjedhur të gjithë tabelën, përveç kolonës origjinale Rrjedhë dhe krijoni një histogram të rregullt të grumbulluar në të gjithë Inset - Histogram (Fut - Grafiku i kolonës):

Nëse tani zgjidhni kolonat blu dhe i bëni ato të padukshme (klikoni me të djathtën mbi to - Formati i rreshtit – Plotëso – Pa Plotësim), atëherë ne thjesht marrim atë që na nevojitet. 

Avantazhi i kësaj metode është thjeshtësia. Në minuset - nevoja për të numëruar kolonat ndihmëse.

Metoda 3. Nëse shkojmë në të kuqe, gjithçka është më e vështirë

Fatkeqësisht, metoda e mëparshme funksionon në mënyrë adekuate vetëm për vlera pozitive. Nëse të paktën në një zonë ujëvara jonë shkon në një zonë negative, atëherë kompleksiteti i detyrës rritet ndjeshëm. Në këtë rast, do të jetë e nevojshme të llogaritet çdo rresht (bedel, jeshil dhe i kuq) veçmas për pjesët negative dhe pozitive me formula:

Për të mos vuajtur shumë dhe për të mos rishpikur timonin, një shabllon i gatshëm për një rast të tillë mund të shkarkohet në titullin e këtij artikulli.

Metoda 4. Ekzotike: banda lart-poshtë

Kjo metodë bazohet në përdorimin e një elementi të veçantë pak të njohur të grafikëve të sheshtë (histogramë dhe grafikë) - Shiritat lart-poshtë (shiritat lart-poshtë). Këto breza lidhin pikat e dy grafikëve në çifte për të treguar qartë se cila nga dy pikat është më e lartë ose më e ulët, gjë që përdoret në mënyrë aktive kur vizualizohet fakti i planit:

Është e lehtë të kuptojmë se nëse heqim linjat e grafikëve dhe lëmë vetëm brezat lart-poshtë në tabelë, atëherë do të marrim të njëjtën "ujëvarë".

Për një ndërtim të tillë, ne duhet të shtojmë dy kolona të tjera shtesë në tabelën tonë me formula të thjeshta që do të llogarisin pozicionin e dy grafikëve të padukshëm të kërkuar:

Për të krijuar një "ujëvarë", duhet të zgjidhni një kolonë me muaj (për nënshkrimet përgjatë boshtit X) dhe dy kolona shtesë Orari 1 и Orari 2 dhe ndërtoni një grafik të rregullt për përdorim fillestar Insert – Grafiku (Fut - Grafiku i rreshtit):

Tani le të shtojmë grupet lart-poshtë në grafikun tonë:

• Në Excel 2013 dhe më të reja, kjo duhet të zgjidhet në skedën konstruktor Komandë Shto element grafiku — Brezet e rritjes-uljes (Dizajni - Shto element grafiku - shirita lart-poshtë)
• Në Excel 2007-2010 - shkoni te skeda Layout – Shirita Advance-Decrement (Struktura - Shirita lart-poshtë)

Më pas, grafiku do të duket diçka si kjo:

Mbetet për të zgjedhur grafikët dhe për t'i bërë ato transparente duke klikuar mbi ta me radhë me butonin e djathtë të miut dhe duke zgjedhur komandën Formati i serisë së të dhënave (Seriali i formatit). Në mënyrë të ngjashme, ju mund të ndryshoni ngjyrat standarde të shiritave bardh e zi me pamje të dobët në jeshile dhe të kuqe për të marrë një pamje më të bukur në fund:

Në versionet më të fundit të Microsoft Excel, gjerësia e shiritave mund të ndryshohet duke klikuar në një nga grafikët transparent (jo shiritat!) me butonin e djathtë të miut dhe duke zgjedhur komandën Formati i serisë së të dhënave – Pastrimi anësor (Seria e formatit - Gjerësia e boshllëkut).

Në versionet më të vjetra të Excel, ju duhej të përdorni komandën Visual Basic për të rregulluar këtë:

1. Theksoni diagramin e ndërtuar
2. Shtypni shkurtoren e tastierës Ndalet+F11për të hyrë në Redaktorin e Visual Basic
3. Shtypni shkurtore të tastierës Ctrl+Gpër të hapur panelin e hyrjes dhe korrigjimit të komandës direkte i menjëhershëm (zakonisht i vendosur në fund).

4. Kopjoni dhe ngjisni komandën e mëposhtme atje: ActiveChart.ChartGroups(1).GapWidth = 30 dhe shtypni Shkruani:

Ju, sigurisht, mund të luani me vlerën e parametrit nëse dëshironi. GapWidthpër të arritur pastrimin e dëshiruar:

• Si të ndërtoni një tabelë me pika në Excel për të vizualizuar KPI  
• Çfarë ka të re në grafikët në Excel 2013
• Si të krijoni një tabelë interaktive "live" në Excel