Come creare una curva a campana di distribuzione normale in Excel

Questo tutorial dimostrerà come creare una normale curva a campana di distribuzione in tutte le versioni di Excel: 2007, 2010, 2013, 2016 e 2022.

In statistica, una curva a campana (nota anche come distribuzione normale standard o curva gaussiana) è un grafico simmetrico che illustra la tendenza dei dati a raggrupparsi attorno a un valore centrale, o media, in un determinato set di dati.

L'asse y rappresenta la probabilità relativa che un dato valore si verifichi nel set di dati mentre l'asse x traccia i valori stessi sul grafico per creare una curva a campana, da cui il nome.

Il grafico ci aiuta ad analizzare se un determinato valore fa parte della variazione attesa o è statisticamente significativo e, quindi, deve essere esaminato più da vicino.

Poiché Excel non ha soluzioni integrate da offrire, dovrai tracciarlo tu stesso. Ecco perché abbiamo sviluppato il componente aggiuntivo Chart Creator, uno strumento che ti consente di creare grafici Excel avanzati in pochi clic.

In questo tutorial passo passo, imparerai come creare una normale curva a campana di distribuzione in Excel da zero:

Per tracciare una curva gaussiana, devi sapere due cose:

• il mezzo (noto anche come misura standard). Questo determina il centro della curva, che a sua volta caratterizza la posizione della curva.
• La deviazione standard (DS) delle misurazioni. Questo definisce la diffusione dei tuoi dati nella distribuzione normale o, in parole povere, quanto dovrebbe essere larga la curva. Ad esempio, nella curva a campana mostrata sopra, una deviazione standard della media rappresenta l'intervallo tra i punteggi degli esami di 53 e 85.

Più basso è il SD, più alta è la curva e meno i tuoi dati saranno distribuiti, e viceversa.

Vale la pena menzionare la regola 68-95-99,7 che può essere applicata a qualsiasi curva di distribuzione normale, il che significa che circa il 68% dei tuoi dati verrà posizionato all'interno di una SD di distanza dalla media, il 95% entro due SD e il 99,7% all'interno tre SD.

Ora che conosci l'essenziale, passiamo dalla teoria alla pratica.

Iniziare

A scopo illustrativo, supponiamo che tu abbia i punteggi del test di 200 studenti e desideri valutarli "su una curva", il che significa che i voti degli studenti saranno basati sulla loro prestazione relativa al resto della classe:

Passaggio 1: trova la media.

In genere, vengono forniti i valori medi e SD dall'inizio, ma in caso contrario, è possibile calcolare facilmente questi valori in pochi semplici passaggi. Affrontiamo prima la media.

Poiché la media indica il valore medio di un campione o di una popolazione di dati, puoi trovare la tua misurazione standard utilizzando la funzione MEDIA.

Digita la seguente formula in una cella vuota (F1 in questo esempio) accanto ai dati effettivi (colonne A e B) per calcolare la media dei punteggi dell'esame nel dataset:

1	=MEDIA(B2:B201)

Una breve nota: il più delle volte, potrebbe essere necessario arrotondare l'output della formula. Per farlo, avvolgilo semplicemente nella funzione ROUND come segue:

1	=ROUND(MEDIA(B2:B201),0)

Passaggio 2: trova la deviazione standard.

Uno giù, uno per andare. Fortunatamente, Excel ha una funzione speciale per fare tutto il lavoro sporco di trovare la deviazione standard per te:

1	=DEV.ST.P(B2:B201)

Anche in questo caso, la formula preleva tutti i valori dall'intervallo di celle specificato (B2: B201) e calcola la sua deviazione standard, ma non dimenticare di arrotondare anche l'output.

1	=ARROTO(DEV.ST.P(B2:B201),0)

Passaggio 3: imposta i valori dell'asse x per la curva.

Fondamentalmente, il grafico costituisce un numero enorme di intervalli (pensali come passaggi) uniti insieme a una linea per creare una curva liscia.

Nel nostro caso, i valori dell'asse x verranno utilizzati per illustrare un particolare punteggio dell'esame mentre i valori dell'asse y ci diranno la probabilità che uno studente ottenga quel punteggio all'esame.

Tecnicamente, puoi includere tutti gli intervalli che desideri: puoi cancellare facilmente i dati ridondanti in un secondo momento modificando la scala dell'asse orizzontale. Assicurati solo di scegliere un intervallo che incorpori le tre deviazioni standard.

Iniziamo un conteggio da uno (poiché non c'è modo in cui uno studente possa ottenere un punteggio negativo all'esame) e arriviamo fino a 150 - non importa se è 150 o 1500 - per impostare un'altra tabella di supporto.

1. Scegli una cella vuota sotto i dati del grafico (come E4) e digita “1,” il valore che definisce il primo intervallo.
2. Vai a Casa scheda.
3. Nel La modifica gruppo, scegli "Riempire.”
4. Sotto "Serie in," Selezionare "Colonna.”
5. Per "Valore del passo," genere “1.” Questo valore determina gli incrementi che verranno aggiunti automaticamente fino a quando Excel non raggiunge l'ultimo intervallo.
6. Per "valore di arresto," genere "150,” il valore che rappresenta l'ultimo intervallo e fare clic su "OK.”

Miracolosamente, 149 celle nella colonna E (MI5: MI153) sono stati riempiti con i valori che vanno da 2 a 150.

NOTA: non nascondere le celle di dati originali come mostrato negli screenshot. Altrimenti, la tecnica non funzionerà.

Passaggio 4: calcolare i valori di distribuzione normale per ogni valore dell'asse x.

Ora, trova i valori della distribuzione normale, la probabilità che uno studente ottenga un determinato punteggio dell'esame rappresentato da un particolare valore dell'asse x, per ciascuno degli intervalli. Fortunatamente per te, Excel ha il cavallo di battaglia per fare tutti questi calcoli per te: la funzione DISTRIB.NORM.ST.

Digita la seguente formula nella cella a destra (F4) del tuo primo intervallo (E4):

1	=DISTRIB.NORM.(MI4,$FA$1,$FA$2,FALSO)

Ecco la versione decodificata per aiutarti a regolare di conseguenza:

1	= DISTRIB.NORM.([il primo intervallo],[la media (riferimento assoluto)],[la deviazione standard (riferimento assoluto),FALSO)

Si bloccano i valori media e SD in modo da poter eseguire senza sforzo la formula per gli intervalli rimanenti (MI5: MI153).

Ora, fai doppio clic sulla maniglia di riempimento per copiare la formula nel resto delle celle (F5: F153).

Passaggio n. 5: creare un grafico a dispersione con linee morbide.

Finalmente è arrivato il momento di costruire la curva a campana:

1. Seleziona qualsiasi valore nella tabella di supporto contenente i valori degli assi x e y (E4:F153).
2. Vai al Inserire scheda.
3. Clicca il "Inserisci grafico a dispersione (X, Y) o a bollepulsante ".
4. Scegliere "Dispersione con linee morbide.”

Passaggio n. 6: imposta la tabella delle etichette.

Tecnicamente, hai la tua curva a campana. Ma sarebbe difficile da leggere in quanto mancano tutti i dati che lo descrivono.

Rendiamo più informativa la distribuzione normale aggiungendo le etichette che illustrano tutti i valori di deviazione standard sotto e sopra la media (puoi anche usarle per mostrare gli z-score).

Per questo, imposta un'altra tabella di supporto come segue:

Per prima cosa, copia il valore medio (F1) accanto alla cella corrispondente nella colonna X-Value (I5).

Quindi, calcola i valori della deviazione standard al di sotto della media inserendo questa semplice formula in cella I4:

1

=I5-$F$2

In poche parole, la formula sottrae la somma dei precedenti valori di deviazione standard dalla media. Ora trascina il quadratino di riempimento verso l'alto per copiare la formula nelle due celle rimanenti (I2:I3).

Ripeti lo stesso processo per le deviazioni standard sopra la media usando la formula speculare:

1

=I5+$F$2

Allo stesso modo, esegui la formula per le altre due celle (I7:I8).

Infine, riempi i valori dell'etichetta dell'asse y (J2:J8) con zeri come si desidera che i marcatori di dati siano posizionati sull'asse orizzontale.

Passaggio 7: inserire i dati dell'etichetta nel grafico.

Ora aggiungi tutti i dati che hai preparato. Fare clic con il pulsante destro del mouse sul grafico e scegliere "Seleziona Dati.”

Nella finestra di dialogo che si apre, seleziona "Aggiungere.”

Evidenzia i rispettivi intervalli di celle dalla tabella di supporto-I2:I8 per "Valori della serie X" e J2:J8 per "Valori serie Y”-e fare clic su "OK.”

Passaggio n. 8: modificare il tipo di grafico della serie di etichette.

Il nostro prossimo passo è cambiare il tipo di grafico delle serie appena aggiunte per far apparire i marcatori di dati come punti. Per farlo, fai clic con il pulsante destro del mouse sul grafico e seleziona "Cambia tipo di grafico.”

Quindi, progetta un grafico combinato:

1. Vai a Combo scheda.
2. Per Serie "Serie2", modificare "Tipo di grafico" a "Dispersione.”
   • Nota: assicurati che "Serie1” rimane come “Dispersione con linee morbide.” A volte Excel lo cambierà quando fai un Combo Assicurati anche "Serie1” non viene inserito nell'asse secondario: la casella di controllo accanto al tipo di grafico non deve essere contrassegnata.
3. Fare clic su "ok.”

Passaggio 9: modifica la scala dell'asse orizzontale.

Centrare il grafico sulla curva a campana regolando la scala dell'asse orizzontale. Fare clic con il pulsante destro del mouse sull'asse orizzontale e selezionare "Asse formato” dal menù.

Una volta visualizzato il riquadro attività, effettuare le seguenti operazioni:

• Vai al Opzioni dell'asse scheda.
• Impostare il Limiti minimi valore a "15.”
• Impostare il Limiti massimi valore a "125.”

Puoi modificare l'intervallo della scala degli assi come ritieni opportuno, ma poiché conosci gli intervalli di deviazione standard, imposta i valori Limiti un po' lontano da ciascuna delle tue terze deviazioni standard per mostrare la "coda" della curva.

Passaggio n. 10: inserire e posizionare le etichette dati personalizzate.

Mentre rifinisci il tuo grafico, assicurati di aggiungere le etichette dati personalizzate. Innanzitutto, fai clic con il pulsante destro del mouse su qualsiasi punto che rappresenta Serie “Serie2” e seleziona "Aggiungi etichette dati.”

Quindi, sostituisci le etichette predefinite con quelle precedentemente impostate e posizionale sopra i marcatori di dati.

1. Fare clic con il tasto destro su qualsiasi Serie “Serie2” etichetta dati.
2. Selezionare "Formatta etichette dati.”
3. Nel riquadro attività, passare a Opzioni etichetta scheda.
4. Controlla il "Valore X" scatola.
5. Deseleziona il "Valore Y" scatola.
6. Sotto "Posizione etichetta," scegliere "Al di sopra.”

Inoltre, ora puoi rimuovere le linee della griglia (fai clic con il pulsante destro del mouse su di esse> Elimina).

Passaggio 11: ricolora i marcatori di dati (opzionale).

Infine, ricolora i punti per aiutarli ad adattarsi allo stile del tuo grafico.

1. Fare clic con il tasto destro su qualsiasi Serie “Serie2” etichetta dati.
2. Clicca il "Riempirepulsante ".
3. Scegli il tuo colore dalla tavolozza che appare.

Inoltre, rimuovi i bordi attorno ai punti:

1. Fare nuovamente clic con il pulsante destro del mouse sullo stesso indicatore di dati e selezionare "Contorno.”
2. Scegliere "Nessun contorno.”

Passaggio 12: aggiungi linee verticali (opzionale).

Come regolazione finale, puoi aggiungere linee verticali al grafico per enfatizzare i valori SD.

• Seleziona il grafico (in questo modo le linee verranno inserite direttamente nel grafico).
• Vai al Inserire scheda.
• Clicca il "Formepulsante ".
• Scegliere "Linea.”

Tieni premuto il "SPOSTARE" mentre si trascina il mouse per tracciare linee perfettamente verticali da ciascun punto fino al punto in cui ogni linea incontra la curva a campana.

Cambia il titolo del grafico e la tua curva a campana migliorata sarà pronta a mostrare i tuoi preziosi dati di distribuzione.

Ed è così che lo fai. Ora puoi scegliere qualsiasi set di dati e creare una normale curva a campana di distribuzione seguendo questi semplici passaggi!