Cum să construiți o diagramă în cascadă

Din ce în ce mai des mă întâlnesc în raportarea diferitelor companii și aud solicitări de la cursanți pentru a explica modul în care se construiește o diagramă în cascadă a abaterilor – este și „cascada”, este și „cascada”, este și „pod”. ”, este, de asemenea, un „pod”, etc. Arata cam asa:

De la distanță, chiar arată ca o cascadă de cascade pe un râu de munte sau un pod suspendat – cine vede ce 🙂

Particularitatea unei astfel de diagrame este că:

• Vedem clar valoarea inițială și finală a parametrului (prima și ultima coloană).
• Modificările pozitive (creșterea) sunt afișate într-o singură culoare (de obicei verde), și cele negative (declin) față de ceilalți (de obicei roșu).
  
• Uneori, graficul poate conține și coloane subtotale (griaterizat pe coloanele axei x).
  

În viața de zi cu zi, astfel de diagrame sunt utilizate de obicei în următoarele cazuri:

• Vizual afișarea dinamicii orice proces în timp: flux de numerar (cash-flow), investiții (investim într-un proiect și obținem profit din acesta).
• Vizualizare implementarea planului (coloana din stânga din diagramă este un fapt, coloana din dreapta este un plan, întreaga diagramă reflectă procesul nostru de a ne îndrepta către rezultatul dorit)
• Când ai nevoie de vizual arată factoriicare ne afectează parametrul (analiza factorială a profitului – în ce constă).
  

Există mai multe moduri de a construi o astfel de diagramă - totul depinde de versiunea dvs. de Microsoft Excel.

Metoda 1: Cel mai ușor: tip încorporat în Excel 2016 și mai nou

Dacă aveți Excel 2016, 2019 sau o versiune ulterioară (sau Office 365), atunci construirea unei astfel de diagrame nu este dificilă - aceste versiuni de Excel au deja acest tip încorporat în mod implicit. Va fi necesar doar să selectați un tabel cu date și să selectați pe filă Insera (Introduce) Comandă cascadă (Cascadă):

Ca rezultat, vom obține o diagramă aproape gata făcută:

Puteți seta imediat culorile de umplere dorite pentru coloanele pozitive și negative. Cel mai simplu mod de a face acest lucru este să selectați rândurile corespunzătoare Crește и scădea direct în legendă și făcând clic dreapta pe ele, selectați comanda Umple (Completati):

Dacă trebuie să adăugați coloane cu subtotaluri sau un total de coloană final la diagramă, atunci este cel mai convenabil să faceți acest lucru folosind funcții SUBTOTALURI (SUBTOTALURI) or UNITATE (AGREGAT). Ei vor calcula suma acumulată de la începutul tabelului, excluzând în același timp din acesta totalurile similare situate mai sus:

În acest caz, primul argument (9) este codul operației de însumare matematică, iar al doilea (0) face ca funcția să ignore totalurile deja calculate pentru trimestrele anterioare în rezultate.

După adăugarea rândurilor cu totaluri, rămâne să selectați totalul coloanelor care au apărut pe diagramă (efectuați două clicuri consecutive simple pe coloană) și, făcând clic dreapta pe mouse, selectați comanda Setați ca total (Setat ca total):

Coloana selectată va ateriza pe axa x și va schimba automat culoarea în gri.

Asta, de fapt, este tot – diagrama cascadei este gata:

Metoda 2. Universal: coloane invizibile

Dacă aveți Excel 2013 sau versiuni mai vechi (2010, 2007 etc.), atunci metoda descrisă mai sus nu va funcționa pentru dvs. Va trebui să ocoliți și să decupați diagrama cascadă lipsă dintr-o histogramă obișnuită stivuită (însumând barele una peste alta).

Trucul aici este să folosiți coloane transparente pentru a ridica rândurile noastre de date roșii și verzi la înălțimea corectă:

Pentru a construi o astfel de diagramă, trebuie să mai adăugăm câteva coloane auxiliare cu formule la datele sursă:

• În primul rând, trebuie să împărțim coloana noastră originală, separând valorile pozitive și negative în coloane separate folosind funcția IF (DACĂ).  
• În al doilea rând, va trebui să adăugați o coloană în fața coloanelor suzeta, unde prima valoare va fi 0, iar pornind de la a doua celula, formula va calcula inaltimea acelor coloane de sustinere foarte transparente.
  

După aceea, rămâne să selectați întregul tabel, cu excepția coloanei originale Debit și creați o histogramă stivuită obișnuită Inset — Histogramă (Inserați — Diagramă coloane):

Dacă acum selectați coloanele albastre și le faceți invizibile (faceți clic dreapta pe ele - Format rând – Umplere – Fără umplere), atunci obținem doar ceea ce ne trebuie. 

Avantajul acestei metode este simplitatea. În minusuri – necesitatea numărării coloanelor auxiliare.

Metoda 3. Dacă intrăm în roșu, totul este mai dificil

Din păcate, metoda anterioară funcționează adecvat doar pentru valori pozitive. Dacă cel puțin într-o zonă cascada noastră intră într-o zonă negativă, atunci complexitatea sarcinii crește semnificativ. În acest caz, va fi necesar să se calculeze fiecare rând (manchin, verde și roșu) separat pentru părțile negative și pozitive cu formule:

Pentru a nu suferi mult și a nu reinventa roata, un șablon gata făcut pentru un astfel de caz poate fi descărcat în titlul acestui articol.

Metoda 4. Exotic: benzi sus-jos

Această metodă se bazează pe utilizarea unui element special puțin cunoscut de diagrame plate (histograme și grafice) - Benzi sus-jos (Barele sus-jos). Aceste benzi conectează punctele a două grafice în perechi pentru a arăta clar care dintre cele două puncte este mai mare sau mai jos, care este utilizat în mod activ la vizualizarea planului-fapt:

Este ușor să ne dăm seama că dacă eliminăm liniile diagramelor și lăsăm doar benzile sus-jos pe diagramă, atunci vom obține aceeași „cascada”.

Pentru o astfel de construcție, trebuie să mai adăugăm două coloane suplimentare la tabelul nostru cu formule simple care vor calcula poziția celor două grafice invizibile necesare:

Pentru a crea o „cascada”, trebuie să selectați o coloană cu luni (pentru semnături de-a lungul axei X) și două coloane suplimentare Programați 1 и Programați 2 și construiți un grafic obișnuit pentru începători Inserare – Grafic (Inserați — Diagramă de linii):

Acum să adăugăm benzi sus-jos la graficul nostru:

• În Excel 2013 și mai nou, acesta trebuie selectat în filă Constructor Comandă Adăugați un element grafic — Benzi de creștere-scădere (Design — Adăugați element de diagramă — Bare sus-jos)
• În Excel 2007-2010 – accesați fila Layout – Advance-Decrement Bars (Aspect – Bare sus-jos)

Graficul va arăta apoi cam așa:

Rămâne să selectați graficele și să le faceți transparente făcând clic pe ele pe rând cu butonul drept al mouse-ului și selectând comanda Format de serie de date (Format seria). În mod similar, puteți schimba culorile standard ale dungilor alb-negru, cu aspect destul de ponosit, în verde și roșu, pentru a obține o imagine mai frumoasă în final:

În cele mai recente versiuni de Microsoft Excel, lățimea barelor poate fi modificată făcând clic pe unul dintre graficele transparente (nu pe barele!) cu butonul drept al mouse-ului și selectând comanda Format serie de date – spațiu liber lateral (Format serie — Lățimea golului).

În versiunile mai vechi de Excel, a trebuit să utilizați comanda Visual Basic pentru a remedia acest lucru:

1. Evidențiați diagrama construită
2. Apăsați comanda rapidă de la tastatură Alt+F11pentru a intra în Editorul Visual Basic
3. Apăsați tasta rapidă de la tastatură Ctrl+Gpentru a deschide panoul de intrare directă a comenzii și depanare imediat (situat de obicei în partea de jos).

4. Copiați și inserați următoarea comandă acolo: ActiveChart.ChartGroups(1).GapWidth = 30 și apăsați Intrați:

Puteți, desigur, să vă jucați cu valoarea parametrului dacă doriți. GapWidthpentru a obține spațiul liber dorit:

• Cum să construiți o diagramă cu marcatori în Excel pentru a vizualiza KPI  
• Ce este nou în diagrame în Excel 2013
• Cum să creați o diagramă interactivă „în direct” în Excel