Kuidas koostada juga diagrammi

Üha sagedamini kohtun erinevate ettevõtete aruandluses ja kuulen praktikantidelt palveid selgitada, kuidas koostatakse kõrvalekallete kaskaaddiagrammi – see on ka “kosk”, see on ka “kosk”, see on ka “sild”. ”, see on ka „sild” jne. See näeb välja umbes selline:

Eemalt vaadates tundub see tõesti nagu koskede kaskaad mägijõel või rippsild – kes mida näeb 🙂

Sellise diagrammi eripära on see, et:

• Näeme selgelt parameetri alg- ja lõppväärtust (esimene ja viimane veerg).
• Positiivsed muutused (kasv) kuvatakse ühe värviga (tavaliselt roheline) ja negatiivseid (langus) teistele (tavaliselt). punane).
  
• Mõnikord võib diagramm sisaldada ka vahesumma veerge (hallmaandus x-telje veergudele).
  

Igapäevaelus kasutatakse selliseid diagramme tavaliselt järgmistel juhtudel:

• Visuaalne dünaamika kuva mis tahes protsess ajas: rahavoog (cash-flow), investeeringud (me investeerime projekti ja saame sellest kasumit).
• Visualiseerimine plaani elluviimine (diagrammi vasakpoolseim veerg on fakt, parempoolseim veerg on plaan, kogu diagramm kajastab meie protsessi soovitud tulemuse poole liikumisel)
• Kui vajate visuaalset näidata tegureidmis mõjutavad meie parameetrit (kasumi faktorianalüüs – millest see koosneb).
  

Sellise diagrammi koostamiseks on mitu võimalust – kõik sõltub teie Microsoft Exceli versioonist.

1. meetod. Lihtsaim: Excel 2016 ja uuemates versioonides sisseehitatud tüüp

Kui teil on Excel 2016, 2019 või uuem (või Office 365), siis pole sellise diagrammi koostamine keeruline – nendel Exceli versioonidel on see tüüp juba vaikimisi sisse ehitatud. Tuleb vaid valida andmetega tabel ja valida vahekaardil Sisesta (Sisesta) käsk CSS (Juga):

Selle tulemusena saame peaaegu valmis diagrammi:

Saate kohe määrata positiivsete ja negatiivsete veergude soovitud täitevärvid. Lihtsaim viis seda teha on valida sobivad read Suurendama и vähenema otse legendis ja paremklõpsates nendel, valige käsk Täitma (Täida):

Kui peate diagrammile lisama vahesummadega veerge või lõpliku veeru kogusumma, on seda kõige mugavam teha funktsioonide abil VAHEKOMMENDID (VÄLIKUMMA) or UNIT (KOGU). Nad arvutavad tabeli algusest kogunenud summa, jättes sellest välja ülaltoodud sarnased kogusummad:

Sel juhul on esimene argument (9) matemaatilise liitmistehte kood ja teine ​​(0) paneb funktsiooni ignoreerima tulemustes juba arvutatud summasid eelmiste kvartalite kohta.

Pärast summaarsete ridade lisamist jääb üle valida diagrammil ilmunud veerud kokku (teha kaks järjestikust ühekordset klõpsu veerul) ja hiire paremklõpsuga valida käsk Määra kogusummaks (Määratud kogusummaks):

Valitud veerg maandub x-teljel ja muudab värvi automaatselt halliks.

See on tegelikult kõik – juga diagramm on valmis:

2. meetod. Universaalne: nähtamatud veerud

Kui teil on Excel 2013 või vanemad versioonid (2010, 2007 jne), siis ülalkirjeldatud meetod teie jaoks ei tööta. Peate minema ringi ja lõikama tavalisest virnastatud histogrammist välja puuduva juga diagrammi (summerdades üksteise peal olevad tulbad).

Siin on nipp kasutada läbipaistvaid tugiveerge, et tõsta meie punased ja rohelised andmeridad õigele kõrgusele:

Sellise diagrammi koostamiseks peame lähteandmetele lisama veel mõned valemitega abiveergud:

• Esiteks peame oma algse veeru jagama, eraldades funktsiooni abil positiivsed ja negatiivsed väärtused eraldi veergudeks IF (KUI).  
• Teiseks peate veergude ette lisama veeru lutid, kus esimene väärtus on 0 ja alates teisest lahtrist arvutab valem nende väga läbipaistvate tugisammaste kõrgused.
  

Pärast seda jääb üle valida kogu tabel, välja arvatud algne veerg voolama ja looge tavaline virnastatud histogramm Inset — histogramm (Sisesta – veergdiagramm):

Kui valite nüüd sinised veerud ja muudate need nähtamatuks (paremklõpsake neil - Rea vorming – Täida – täitmata), siis saame just selle, mida vajame. 

Selle meetodi eeliseks on lihtsus. Miinustest – abiveergude loendamise vajadus.

Meetod 3. Kui läheme miinusesse, on kõik keerulisem

Kahjuks töötab eelmine meetod adekvaatselt ainult positiivsete väärtuste korral. Kui vähemalt mõnes piirkonnas läheb meie juga negatiivsesse piirkonda, siis ülesande keerukus suureneb oluliselt. Sel juhul tuleb negatiivsete ja positiivsete osade jaoks arvutada iga rida (näiv, roheline ja punane) eraldi valemitega:

Et mitte palju kannatada ja jalgratast mitte uuesti leiutada, saab selle artikli pealkirjas alla laadida sellise juhtumi jaoks valmis malli.

4. meetod. Eksootiline: üles-alla ribad

See meetod põhineb lamediagrammide (histogrammide ja graafikute) spetsiaalse vähetuntud elemendi kasutamisel – Üles-alla ribad (üles-alla ribad). Need ribad ühendavad kahe graafiku punkte paarikaupa, et selgelt näidata, kumb kahest punktist on kõrgem või madalam, mida kasutatakse aktiivselt plaani-fakti visualiseerimisel:

Lihtne on aru saada, et kui eemaldada graafikute jooned ja jätta graafikule ainult üles-alla ribad, siis saamegi samasuguse “kose”.

Sellise konstruktsiooni jaoks peame oma tabelisse lisama veel kaks täiendavat veergu lihtsate valemitega, mis arvutavad kahe vajaliku nähtamatu graafiku asukoha:

"Juga" loomiseks peate valima kuude veeru (allkirjade jaoks piki X-telge) ja kahe täiendava veeru Ajakava 1 и Ajakava 2 ja koostage algajatele tavaline graafik Sisesta – graafik (Sisesta – joon С):

Nüüd lisame oma diagrammi üles-alla ribad:

• Excel 2013 ja uuemates versioonides tuleb see vahekaardil valida Ehitaja käsk Lisa diagrammi element — Suurenemis-languse ribad (Disain – diagrammi elemendi lisamine – üles-alla ribad)
• Excelis 2007–2010 – minge vahekaardile Paigutus – Advance-Decrement ribad (Paigutus – üles-alla ribad)

Seejärel näeb diagramm välja umbes selline:

Jääb üle valida graafikud ja muuta need läbipaistvaks, klõpsates neil kordamööda hiire parema nupuga ja valides käsu Andmeseeria formaat (Seeria vormindamine). Samamoodi saate muuta tavalised, üsna räbala välimusega mustvalged triibuvärvid roheliseks ja punaseks, et lõpuks ilusam pilt saada:

Microsoft Exceli uusimates versioonides saab tulpade laiust muuta, klõpsates hiire parema nupuga ühel läbipaistval graafikul (mitte tulbadel!) ja valides käsu Andmeseeria vorming – külgmine vahemaa (Vorminguseeria – vahe laius).

Exceli vanemates versioonides pidite selle parandamiseks kasutama käsku Visual Basic:

1. Tõstke esile ehitatud diagramm
2. Vajutage klaviatuuri otseteed muu+F11Visual Basicu redaktorisse pääsemiseks
3. Vajutage kiirklahvi Ctrl+Gkäsu otsesisendi ja silumispaneeli avamiseks Vahetu (asub tavaliselt allosas).

4. Kopeerige ja kleepige sinna järgmine käsk: ActiveChart.ChartGroups(1).GapWidth = 30 ja vajutage sisene:

Loomulikult võite soovi korral parameetri väärtusega mängida. GapWidthsoovitud kliirensi saavutamiseks:

• Kuidas koostada Excelis KPI visualiseerimiseks täppdiagrammi  
• Mis on uut Excel 2013 diagrammides?
• Kuidas luua Excelis interaktiivset "reaalajas" diagrammi