Kaip sukurti krioklio diagramą

Vis dažniau susitinku įvairių įmonių ataskaitose ir išgirstu praktikantų prašymus paaiškinti, kaip kuriama kaskadinė nukrypimų diagrama – tai ir „krioklys“, tai ir „krioklys“, tai ir „tiltas“. “, tai taip pat yra „tiltas“ ir kt. Tai atrodo maždaug taip:

Iš tolo tikrai atrodo kaip krioklių kaskada kalnų upėje ar kabantis tiltas – kas ką mato 🙂

Tokios diagramos ypatumas yra tas, kad:

• Aiškiai matome pradinę ir galutinę parametro reikšmę (pirmasis ir paskutinis stulpeliai).
• Teigiami pokyčiai (augimas) rodomi viena spalva (dažniausiai žalias), o neigiamus (sumažėjimas) kitiems (dažniausiai). raudonas).
  
• Kartais diagramoje taip pat gali būti tarpinės sumos stulpelių (pilkasnusileido ant x ašies stulpelių).
  

Kasdieniame gyvenime tokios diagramos dažniausiai naudojamos šiais atvejais:

• regėjimo dinamikos ekranas bet koks procesas laike: pinigų srautas (cash-flow), investicijos (investuojame į projektą ir iš jo gauname pelną).
• Vizualizacija plano įgyvendinimas (kairiausias diagramos stulpelis yra faktas, dešiniausias stulpelis yra planas, visa diagrama atspindi mūsų judėjimą link norimo rezultato)
• Kai reikia vizualinio parodyti veiksniuskurie turi įtakos mūsų parametrui (pelno faktorinė analizė – iš ko jis susideda).
  

Yra keletas būdų, kaip sukurti tokią diagramą – viskas priklauso nuo jūsų „Microsoft Excel“ versijos.

1 būdas: lengviausias: integruotas tipas Excel 2016 ir naujesnėje versijoje

Jei turite „Excel 2016“, 2019 ar naujesnę versiją (arba „Office 365“), sukurti tokią diagramą nėra sunku – šiose „Excel“ versijose šis tipas jau yra integruotas pagal numatytuosius nustatymus. Tereikia pasirinkti lentelę su duomenimis ir pasirinkti skirtuke įsiuvas (Įdėti) Komanda pakopinių (Krioklys):

Dėl to gausime beveik paruoštą diagramą:

Galite iš karto nustatyti norimas teigiamų ir neigiamų stulpelių užpildymo spalvas. Lengviausias būdas tai padaryti yra pasirinkti tinkamas eilutes Padidinti и sumažinti tiesiai legendoje ir dešiniuoju pelės mygtuku spustelėdami juos pasirinkite komandą Pildyti (Užpildyti):

Jei prie diagramos reikia pridėti stulpelių su tarpinėmis sumomis arba galutinę stulpelio sumą, patogiausia tai padaryti naudojant funkcijas SUBSUMOS (SUBTOTALS) or VIENETAI (AGREGATAS). Jie apskaičiuos nuo lentelės pradžios sukauptą sumą, iš jos neįtraukdami panašių aukščiau esančių sumų:

Šiuo atveju pirmasis argumentas (9) yra matematinės sumavimo operacijos kodas, o antrasis (0) verčia funkciją rezultatuose nepaisyti jau apskaičiuotų ankstesnių ketvirčių sumų.

Pridėjus eilutes su sumomis, belieka pasirinkti diagramoje pasirodžiusius suminius stulpelius (padaryti du iš eilės pavienius stulpelio paspaudimus) ir dešiniuoju pelės klavišu spustelėjus komandą Nustatyti kaip bendrą (Nustatyti kaip bendrą):

Pasirinktas stulpelis nusileis ant x ašies ir automatiškai pakeis spalvą į pilką.

Tiesą sakant, tai ir viskas – krioklio diagrama paruošta:

2 metodas. Universalus: nematomi stulpeliai

Jei turite „Excel 2013“ ar senesnes versijas (2010, 2007 ir tt), aukščiau aprašytas metodas jums neveiks. Turėsite apeiti ir iškirpti trūkstamą krioklio diagramą iš įprastos sukrautos histogramos (sumuojant juosteles vieną ant kitos).

Apgaulė čia yra naudoti skaidrius atramos stulpelius, kad mūsų raudonos ir žalios duomenų eilutės būtų padidintos iki tinkamo aukščio:

Norėdami sukurti tokią diagramą, prie šaltinio duomenų turime pridėti dar keletą pagalbinių stulpelių su formulėmis:

• Pirmiausia turime padalyti pradinį stulpelį, atskirdami teigiamas ir neigiamas reikšmes į atskirus stulpelius naudodami funkciją IF (JEI).  
• Antra, prieš stulpelius turėsite pridėti stulpelį sauskelnės, kur pirmoji reikšmė bus 0, o pradedant nuo antrojo langelio, formulė apskaičiuos tų labai skaidrių atraminių stulpelių aukštį.
  

Po to belieka pasirinkti visą lentelę, išskyrus pradinį stulpelį Tėkmė ir sukurkite įprastą sukrautą histogramą skersai Įdėklas – histograma (Įterpti – stulpelių diagrama):

Jei dabar pasirinksite mėlynus stulpelius ir padarysite juos nematomus (dešiniuoju pelės mygtuku spustelėkite juos - Eilutės formatas – Užpildyti – Neužpildyti), tada gauname tai, ko mums reikia. 

Šio metodo pranašumas yra paprastumas. Minusuose – būtinybė skaičiuoti pagalbinius stulpelius.

3 būdas. Jei einame į minusą, viskas yra sunkiau

Deja, ankstesnis metodas tinka tik teigiamoms vertėms. Jei bent kurioje srityje mūsų krioklys patenka į neigiamą sritį, tada užduoties sudėtingumas žymiai padidėja. Tokiu atveju kiekvieną eilutę (manekeno, žalios ir raudonos) reikės atskirai apskaičiuoti neigiamoms ir teigiamoms dalims su formulėmis:

Kad daug nenukentėtumėte ir neišradinėtumėte dviračio iš naujo, šio straipsnio pavadinime galima atsisiųsti paruoštą šabloną tokiam atvejui.

4 būdas. Egzotika: juostos aukštyn žemyn

Šis metodas pagrįstas specialiu mažai žinomu plokščių diagramų (histogramų ir grafikų) elementu – Juostos aukštyn žemyn (juostos aukštyn žemyn). Šios juostos sujungia dviejų grafikų taškus poromis, kad aiškiai parodytų, kuris iš dviejų taškų yra aukščiau ar žemiau, o tai aktyviai naudojama vizualizuojant planą-faktą:

Nesunku suprasti, kad jei pašalinsime diagramų linijas ir diagramoje paliksime tik juostas aukštyn žemyn, gausime tą patį „krioklį“.

Tokiai konstrukcijai į savo lentelę turime įtraukti dar du papildomus stulpelius su paprastomis formulėmis, kurios apskaičiuos dviejų reikalingų nematomų grafikų padėtį:

Norėdami sukurti „krioklį“, turite pasirinkti stulpelį su mėnesiais (parašams išilgai X ašies) ir dviem papildomais stulpeliais Tvarkaraštis 1 и Tvarkaraštis 2 ir sukurti įprastą grafiką pradedantiesiems Įterpimas – grafikas (Įterpti – diagramos eilutė):

Dabar prie diagramos pridėkime juostas aukštyn žemyn:

• „Excel 2013“ ir naujesnėse versijose tai turi būti pasirinkta skirtuke Konstruktorius Komanda Pridėti diagramos elementą — Didėjimo-mažėjimo juostos (Dizainas – pridėti diagramos elementą – juostos aukštyn žemyn)
• „Excel 2007–2010“ – eikite į skirtuką Išdėstymas – Išankstinio mažinimo juostos (Išdėstymas – juostos aukštyn žemyn)

Tada diagrama atrodys maždaug taip:

Belieka pasirinkti grafikus ir padaryti juos skaidrius, paeiliui spustelėjus dešiniuoju pelės mygtuku ir pasirinkus komandą Duomenų serijos formatas (Serialo formatas). Panašiai galite pakeisti standartines, gana niūriai atrodančias juodos ir baltos juostelių spalvas į žalią ir raudoną, kad galų gale gautumėte gražesnį vaizdą:

Naujausiose Microsoft Excel versijose juostų plotį galima keisti dešiniuoju pelės mygtuku spustelėjus vieną iš skaidrių grafikų (ne juostų!) ir pasirinkus komandą Duomenų serijos formatas – šoninis atstumas (Formatas – tarpo plotis).

Senesnėse „Excel“ versijose turėjote naudoti „Visual Basic“ komandą, kad tai išspręstumėte:

1. Pažymėkite pastatytą diagramą
2. Paspauskite spartųjį klavišą Kitas+F11Norėdami patekti į Visual Basic redaktorių
3. Paspauskite klaviatūros nuorodą "Ctrl"+GNorėdami atidaryti tiesioginio komandų įvesties ir derinimo skydelį Tiesioginis (dažniausiai yra apačioje).

4. Nukopijuokite ir įklijuokite ten šią komandą: ActiveChart.ChartGroups(1).GapWidth = 30 ir paspauskite įeiti:

Žinoma, jei norite, galite žaisti su parametro verte. GapWidthnorint pasiekti norimą atstumą:

• Kaip sukurti ženklelių diagramą programoje „Excel“, kad būtų galima vizualizuoti KPI  
• Kas naujo „Excel 2013“ diagramose
• Kaip sukurti interaktyvią „tiesioginę“ diagramą „Excel“.