Contenuti
Sempre più spesso mi incontro nelle segnalazioni di diverse aziende e sento richieste da parte di tirocinanti per spiegare come si costruisce un diagramma a cascata di deviazioni – è anche una “cascata”, è anche una “cascata”, è anche un “ponte ”, è anche un “ponte”, ecc. . Sembra qualcosa del genere:
Da lontano sembra davvero una cascata di cascate su un fiume di montagna o un ponte sospeso – chissà cosa 🙂
La particolarità di un tale diagramma è che:
- Vediamo chiaramente il valore iniziale e finale del parametro (la prima e l'ultima colonna).
- I cambiamenti positivi (crescita) sono visualizzati in un colore (di solito green) e quelli negativi (declino) ad altri (di solito rosso).
- A volte il grafico può contenere anche colonne di subtotali (grigioatterrato sulle colonne dell'asse x).
Nella vita di tutti i giorni, tali diagrammi vengono solitamente utilizzati nei seguenti casi:
- Visivo visualizzazione dinamica qualsiasi processo nel tempo: cash flow (cash-flow), investimenti (si investe in un progetto e ne traiamo profitto).
- Visualizzazione attuazione del piano (la colonna più a sinistra nel diagramma è un dato di fatto, la colonna più a destra è un piano, l'intero diagramma riflette il nostro processo di spostamento verso il risultato desiderato)
- Quando hai bisogno di visuale mostra i fattoriche influenzano il nostro parametro (analisi fattoriale del profitto – in cosa consiste).
Esistono diversi modi per creare un grafico del genere: tutto dipende dalla versione di Microsoft Excel in uso.
Metodo 1: Più semplice: tipo integrato in Excel 2016 e versioni successive
Se hai Excel 2016, 2019 o versioni successive (o Office 365), la creazione di un grafico di questo tipo non è difficile: queste versioni di Excel hanno già questo tipo integrato per impostazione predefinita. Sarà solo necessario selezionare una tabella con dati e selezionare nella scheda inserire (Inserire) Comando cascata (Cascata):
Di conseguenza, otterremo un diagramma quasi pronto:
È possibile impostare immediatamente i colori di riempimento desiderati per le colonne positive e negative. Il modo più semplice per farlo è selezionare le righe appropriate Aumento и diminuire direttamente nella legenda e cliccandoci sopra con il tasto destro selezionare il comando Riempire (Riempire):
Se devi aggiungere colonne con totali parziali o un totale di colonne finale al grafico, è più conveniente farlo usando le funzioni SUBTOTALI (SOTTOTOTALI) or UNITA ' (AGGREGATO). Calcoleranno l'importo accumulato dall'inizio della tabella, escludendo da essa i totali simili sopra riportati:
In questo caso, il primo argomento (9) è il codice dell'operazione di somma matematica e il secondo (0) fa sì che la funzione ignori nei risultati i totali già calcolati per i trimestri precedenti.
Dopo aver aggiunto le righe con i totali, resta da selezionare le colonne totali che sono apparse sul diagramma (fare due clic consecutivi sulla colonna) e, facendo clic con il tasto destro del mouse, selezionare il comando Imposta come totale (Imposta come totale):
La colonna selezionata atterrerà sull'asse x e cambierà automaticamente il colore in grigio.
Questo, in effetti, è tutto: il diagramma a cascata è pronto:
Metodo 2. Universale: colonne invisibili
Se hai Excel 2013 o versioni precedenti (2010, 2007, ecc.), il metodo sopra descritto non funzionerà per te. Dovrai andare in giro e ritagliare il grafico a cascata mancante da un normale istogramma impilato (sommando le barre una sopra l'altra).
Il trucco qui è usare colonne prop trasparenti per aumentare le nostre righe di dati rosse e verdi all'altezza corretta:
Per costruire un tale grafico, dobbiamo aggiungere alcune altre colonne ausiliarie con formule ai dati di origine:
- Innanzitutto, dobbiamo dividere la nostra colonna originale separando i valori positivi e negativi in colonne separate usando la funzione IF (SE).
- In secondo luogo, dovrai aggiungere una colonna davanti alle colonne ciucci, dove il primo valore sarà 0, e partendo dalla seconda cella, la formula calcolerà l'altezza di quelle colonne portanti molto trasparenti.
Successivamente, resta da selezionare l'intera tabella ad eccezione della colonna originale Flow e creare un istogramma impilato regolare Riquadro — Istogramma (Inserisci — Grafico a colonne):
Se ora selezioni le colonne blu e le rendi invisibili (fai clic con il tasto destro su di esse - Formato riga – Riempi – Nessun riempimento), quindi otteniamo solo ciò di cui abbiamo bisogno.
Il vantaggio di questo metodo è la semplicità. Tra gli svantaggi: la necessità di contare le colonne ausiliarie.
Metodo 3. Se andiamo in rosso, tutto è più difficile
Sfortunatamente, il metodo precedente funziona adeguatamente solo per valori positivi. Se almeno in qualche area la nostra cascata va in un'area negativa, la complessità del compito aumenta in modo significativo. In questo caso bisognerà calcolare ogni riga (fittizia, verde e rossa) separatamente per la parte negativa e quella positiva con le formule:
Per non soffrire molto e non reinventare la ruota, nel titolo di questo articolo è possibile scaricare un modello già pronto per un caso del genere.
Metodo 4. Esotico: bande up-down
Questo metodo si basa sull'uso di uno speciale elemento poco noto di grafici piatti (istogrammi e grafici) – Fasce up-down (Barre su-giù). Queste bande collegano i punti di due grafici a coppie per mostrare chiaramente quale dei due punti è più alto o più basso, cosa che viene utilizzata attivamente durante la visualizzazione del fatto in pianta:
È facile capire che se rimuoviamo le linee dei grafici e lasciamo solo le bande up-down sul grafico, otterremo la stessa "cascata".
Per una tale costruzione, dobbiamo aggiungere altre due colonne aggiuntive alla nostra tabella con semplici formule che calcoleranno la posizione dei due grafici invisibili richiesti:
Per creare una "cascata", è necessario selezionare una colonna con i mesi (per le firme lungo l'asse X) e due colonne aggiuntive Programma 1 и Programma 2 e costruisci un grafico regolare per cominciare usando Inserisci – Grafico (Inserire — Riga Сhart):
Ora aggiungiamo le bande up-down al nostro grafico:
- In Excel 2013 e versioni successive, questo deve essere selezionato nella scheda Costruttore Comando Aggiungi elemento grafico — Fasce di aumento-diminuzione (Design — Aggiungi elemento grafico — Barre su-giù)
- In Excel 2007-2010: vai alla scheda Layout – Barre di decremento avanzato (Layout — Barre su-giù)
Il grafico sarà quindi simile a questo:
Resta da selezionare i grafici e renderli trasparenti cliccandoci sopra a turno con il tasto destro del mouse e selezionando il comando Formato serie di dati (Formato serie). Allo stesso modo, puoi cambiare i colori delle strisce bianche e nere standard, piuttosto squallidi, in verde e rosso per ottenere un'immagine più bella alla fine:
Nelle ultime versioni di Microsoft Excel è possibile modificare la larghezza delle barre cliccando su uno dei grafici trasparenti (non le barre!) con il tasto destro del mouse e selezionando il comando Formato serie di dati - Spazio libero laterale (Serie di formati — Larghezza dello spazio vuoto).
Nelle versioni precedenti di Excel, è stato necessario utilizzare il comando Visual Basic per risolvere questo problema:
- Evidenzia il diagramma costruito
- Premi la scorciatoia da tastiera altro+F11per accedere all'editor di Visual Basic
- Premi la scorciatoia da tastiera Ctrl+Gper aprire l'input del comando diretto e il pannello di debug immediato (di solito situato in basso).
- Copia e incolla lì il seguente comando: ActiveChart.ChartGroups(1).GapWidth = 30 e premere entrare:
Ovviamente puoi giocare con il valore del parametro se lo desideri. Larghezza spazioper ottenere la distanza desiderata:
- Come creare un grafico a punti in Excel per visualizzare KPI
- Novità nei grafici in Excel 2013
- Come creare un grafico interattivo "live" in Excel