Guarda il video - Come creare un grafico di Pareto in Excel
Il grafico di Pareto si basa sul principio di Pareto (noto anche come regola 80/20), che è un concetto ben noto nella gestione dei progetti.
Secondo questo principio, ~80% dei problemi può essere attribuito a circa ~20% dei problemi (o ~80% dei tuoi risultati potrebbe essere un risultato diretto di ~20% dei tuoi sforzi, e così via…).
Il valore percentuale 80/20 può variare, ma l'idea è quella di tutti i problemi/sforzi, ce ne sono alcuni che producono il massimo impatto.
Questo è un concetto ampiamente utilizzato nella gestione dei progetti per dare priorità al lavoro.
Creazione di un grafico di Pareto in Excel
In questo tutorial, ti mostrerò come realizzare un:
- Grafico di Pareto semplice (statico) in Excel.
- Grafico di Pareto dinamico (interattivo) in Excel.
Creare un grafico di Pareto in Excel è molto semplice.
Tutto il trucco è nascosto nel modo in cui organizzi i dati nel backend.
Prendiamo un esempio di un Hotel per il quale i dati dei reclami potrebbero essere simili a quelli mostrati di seguito:
NOTA: per creare un grafico di Pareto in Excel, è necessario disporre i dati in ordine decrescente.
Creazione di un grafico di Pareto semplice (statico) in Excel
Ecco i passaggi per creare un grafico di Pareto in Excel:
- Imposta i tuoi dati come mostrato di seguito.
- Calcola la % cumulativa nella colonna C. Utilizza la seguente formula: =SUM($B$2:B2)/SUM($B$2:$B$1)
- Seleziona l'intero set di dati (A1: C10), vai a Inserisci -> Grafici -> Colonna 2D -> Colonna raggruppata. Viene inserito un istogramma con 2 serie di dati (n. di reclami e percentuale cumulativa).
- Fare clic con il pulsante destro del mouse su una delle barre e selezionare Modifica tipo di grafico serie.
- Nella finestra di dialogo Modifica tipo di grafico, seleziona Combinazione nel riquadro di sinistra.
- Apportare le seguenti modifiche:
- N. di reclami: colonna raggruppata.
- % cumulativa: linea (selezionare anche la casella di controllo Asse secondario).
[Se stai utilizzando Excel 2010 o 2007, sarà una procedura in due passaggi processi. Innanzitutto, cambia il tipo di grafico in un grafico a linee. Quindi fare clic con il pulsante destro del mouse sul grafico a linee e selezionare Formato serie di dati e selezionare Asse secondario in Opzioni serie]
- Il tuo grafico di Pareto in Excel è pronto. Regola i valori dell'asse verticale e il titolo del grafico.
Come interpretare questo grafico di Pareto in Excel
Questo grafico di Pareto evidenzia i principali problemi su cui l'hotel dovrebbe concentrarsi per ordinare il numero massimo di reclami. Ad esempio, il targeting per i primi 3 problemi si occuperebbe automaticamente di circa l'80% dei reclami.
Ad esempio, il targeting dei primi 3 problemi si occuperebbe automaticamente di circa l'80% dei reclami.
Creazione di un grafico di Pareto dinamico (interattivo) in Excel
Ora che abbiamo un grafico di Pareto statico/semplice in Excel, facciamo un ulteriore passo avanti e rendiamolo un po' interattivo.
Qualcosa come mostrato di seguito:
In questo caso, un utente può specificare la % di reclami che devono essere affrontati (utilizzando la barra di scorrimento di excel) e il grafico evidenzierà automaticamente i problemi che dovrebbero essere esaminati.
L'idea qui è di avere 2 barre diverse.
Quello rosso è evidenziato quando il valore percentuale cumulativo è vicino al valore target.
Ecco i passaggi per creare questo grafico di Pareto interattivo in Excel:
- Nella cella B14, ho il valore target collegato alla barra di scorrimento (il cui valore varia da 0 a 100).
- Nella cella B12, ho usato la formula = B14/100. Poiché non è possibile specificare un valore percentuale per una barra di scorrimento, dividiamo semplicemente il valore della barra di scorrimento (in B14) con 100 per ottenere il valore percentuale.
- Nella cella B13, inserisci la seguente combinazione di funzioni INDICE, CONFRONTA e SE ERRORE:
=SEERRORE(INDICE($C$2:$C$10;SEERRORE(CONFRONTA($B$12;$C$2:$C$10,1),0)+1),1)
Questa formula restituisce il valore cumulativo che coprirebbe il valore di destinazione. Ad esempio, se il valore target è 70%, restituirà 77%, indicando che dovresti provare a risolvere i primi tre problemi.
- Nella cella D2, inserisci la seguente formula (e trascina o copia per tutte le celle - D2: D10):
=SE($B$13>=C2,B2,NA()) - Nella cella E2 inserisci la seguente formula (e trascina o copia per tutte le celle - E2: E10):
=SE($B$13<> - Seleziona i dati nella colonna A, C, D ed E (premi Ctrl e seleziona usando il mouse).
- Vai a Inserisci -> Grafici -> Colonna 2D -> Colonna raggruppata. Questo inserirà un istogramma con 3 serie di dati (percentuale cumulativa, le barre da evidenziare per raggiungere l'obiettivo e tutte le altre barre rimanenti)
- Fare clic con il pulsante destro del mouse su una delle barre e selezionare Modifica tipo di grafico serie.
- Nella finestra di dialogo Modifica tipo di grafico, seleziona Combinazione nel riquadro di sinistra e apporta le seguenti modifiche:
- % cumulativa: Linea (selezionare anche la casella di controllo Asse secondario).
- Barre evidenziate: colonna raggruppata.
- Barre rimanenti: colonna raggruppata.
- Fare clic con il pulsante destro del mouse su una delle barre evidenziate e cambiare il colore in Rosso.
Questo è tutto!
Hai creato un grafico di Pareto interattivo in Excel.
Ora, quando cambi l'obiettivo usando la barra di scorrimento, il grafico di Pareto si aggiornerà di conseguenza.
Usi il grafico di Pareto in Excel?
Mi piacerebbe sentire i tuoi pensieri su questa tecnica e su come l'hai usata. Lascia le tue impronte nella sezione commenti 🙂
- Analisi dei reclami dei ristoranti utilizzando il grafico di Pareto.
- Creazione di un diagramma di Gantt in Excel.
- Creazione di un grafico Milestone in Excel.
- Creazione di un istogramma in Excel.
- Modello di calcolatrice foglio presenze di Excel.
- Modello di monitoraggio delle ferie dei dipendenti.
- Calcolo della media ponderata in Excel.
- Creazione di una curva a campana in Excel.
- Grafici Excel avanzati
- Come aggiungere un asse secondario nei grafici di Excel
