Creazione di un grafico di Pareto in Excel (statico e dinamico)

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.

/ Il valore percentuale del 20 può variare, ma lidea è 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 creare un:

• Grafico di Pareto semplice (statico) in Excel.
• Grafico di Pareto dinamico (interattivo) in Excel.

Creare un grafico di Pareto in Excel è molto semplice.

Tutti i trucchi sono nascosti in come organizzi i dati nel back-end.

Facciamo un esempio di un hotel per il quale i dati sui reclami potrebbero apparire come mostrato di seguito:

NOTA: per creare un grafico di Pareto in Excel, è necessario disporre i dati in ordine decrescente.

Scarica il modello di grafico di Pareto di Excel

Creazione di un grafico di Pareto semplice (statico) in Excel

Ecco i passaggi per creare un grafico di Pareto in Excel:

1. Imposta i tuoi dati come mostrato di seguito.
2. Calcola% cumulativa nella colonna C. Utilizza la seguente formula: = SUM ($ B $ 2: B2) / SUM ($ B $ 2: $ B $ 1 )
3. Seleziona lintero set di dati (A1: C10), vai a Inserisci – > Grafici – > Colonna 2-D – > Colonna raggruppata. Viene inserito un grafico a colonne con 2 serie di dati (numero di reclami e percentuale cumulativa).
4. Fai clic con il pulsante destro del mouse su una delle barre e seleziona Modifica Tipo di grafico in serie.
5. Nella finestra di dialogo Cambia tipo di grafico, seleziona Combinato nel riquadro di sinistra.
6. Apporta le seguenti modifiche:
   • N. di reclami: colonna raggruppata.
   • % cumulativa: linea (seleziona anche la casella di controllo Asse secondario).
7. Il tuo grafico di Pareto in Excel è pronto. Regola i valori dellasse verticale e il titolo del grafico.

Come interpretare questo grafico di Pareto in Excel

Questo Pareto il grafico evidenzia i principali problemi su cui lhotel dovrebbe concentrarsi per ordinare il numero massimo di reclami. Ad esempio, il targeting dei primi 3 problemi si occuperebbe automaticamente di circa l80% dei reclami.

Ad esempio, il targeting dei primi 3 problemi si occuperebbe automaticamente di circa l80% 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 lo scorrimento di Excel bar) e il grafico evidenzierà automaticamente i problemi che dovrebbero essere esaminati.

Lidea qui è di avere 2 diverse barre.

Quella rossa viene evidenziata quando la percentuale cumulativa il valore è vicino al valore di destinazione.

Ecco i passaggi per creare questo grafico di Pareto interattivo in Excel:

1. Nella cella B14, ho il valore di destinazione che è collegato alla barra di scorrimento (il cui valore varia da 0 a 100).
2. Nella cella B12, ho utilizzato 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) per 100 per ottenere il valore percentuale.
3. Nella cella B13, immettere la seguente combinazione di funzioni INDICE, CONFRONTA e SE.ERRORE:
   = SE.ERRORE (INDICE ($ C $ 2: $ C $ 10, SE.ERRORE (CONFRONTA ($ B $ 12, $ C $ 2: $ C $ 10,1 ), 0) +1), 1)
   Questa formula restituisce il valore cumulativo che coprirebbe il valore target. Ad esempio, se il valore target è 70%, restituirà 77%, a indicare che dovresti provare a risolvere i primi tre problemi.

1. Nella cella D2, inserisci la seguente formula (e trascina o copia per tutta la cella – D2: D10):
   = IF ($ B $ 13 > = C2, B2, NA ())
2. Nella cella E2 inserisci la seguente formula (e trascina o copia per tutte le celle – E2: E10):
   = IF ( $ B $ 13 < C2, B2, NA ())
3. Seleziona i dati nella colonna A, C, D & E (premi Control e seleziona utilizzando il mouse).
4. Vai a Inserisci – > Grafici – > Colonna 2D – > Colonna raggruppata.In questo modo verrà inserito un grafico a colonne con 3 serie di dati (percentuale cumulativa, le barre da evidenziare per soddisfare lobiettivo e tutte le altre barre rimanenti)
5. Fare clic con il pulsante destro del mouse su una delle barre e selezionare Cambia grafico serie Tipo.
6. Nella finestra di dialogo Cambia tipo di grafico, seleziona Combo nel riquadro di sinistra e apporta le seguenti modifiche:
   • % cumulativa: linea (seleziona anche la casella di controllo Asse secondario).
   • Barre evidenziate: colonna raggruppata.
   • Barre rimanenti: colonna raggruppata.
7. Fare clic con il pulsante destro del mouse su una delle barre evidenziate e cambia il colore in Rosso.

Ecco!

Hai creato un grafico di Pareto interattivo in Excel.

Ora, quando cambi target utilizzando la barra di scorrimento, il grafico di Pareto si aggiorna di conseguenza.

Scarica il modello di grafico di Pareto di Excel

Usi il diagramma di Pareto in Excel?

Mi piacerebbe sentire i tuoi pensieri su questa tecnica e su come hai lho usato. Lascia le tue impronte nella sezione dei commenti 🙂

Tutorial sulla gestione dei progetti e sui grafici correlati:

• Analisi dei reclami dei ristoranti utilizzando il diagramma di Pareto.
• Creazione di un Gantt Grafico in Excel.
• Creazione di un grafico cardine in Excel.
• Creazione di un istogramma in Excel.
• Modello di calcolatrice scheda attività Excel.
• Modello di monitoraggio delle uscite dei dipendenti.
• Calcolo della media ponderata in Excel.
• Creazione di una curva a campana in Excel.
• Grafici Excel avanzati
• Come per aggiungere un asse secondario nei grafici di Excel.