Come creare una curva a campana di distribuzione normale in Excel

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

Nelle statistiche, 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 dato set di dati.

Lasse y rappresenta la probabilità relativa di un dato valore che si verifica nel set di dati mentre lasse x traccia i valori stessi sul grafico per creare una campana curva, da cui il nome.

Il grafico ci aiuta ad analizzare se un particolare 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 da solo. Ecco perché abbiamo sviluppato il componente aggiuntivo Chart Creator, uno strumento che ti consente di creare grafici Excel avanzati in pochi clic.

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

A tracciare una curva gaussiana, devi sapere due cose:

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

Minore è la DS, più alta è la curva e minore 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 entro una DS dalla media, 95% entro due DS e 99,7% entro tre DS.

Ora che tu conosciamo gli elementi essenziali, passiamo dalla teoria alla pratica.

Guida introduttiva

A scopo illustrativo, supponiamo che tu abbia i punteggi dei test di 200 studenti e desideri valutarli “su una curva , “Significa che i voti degli studenti saranno basati sul loro rendimento relativo rispetto al resto della classe:

Passaggio 1: trova la media.

In genere, ti viene data la media e i valori SD dallinizio, ma se non è così, puoi facilmente calcolare 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 degli esami nel set di dati:

Una breve nota : il più delle volte, potrebbe essere necessario arrotondare loutput della formula. Per farlo, inseriscilo nella funzione ROUND come segue:

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:

Anche in questo caso, la formula seleziona tutti i valori dallintervallo di celle specificato (B2: B201) e calcola la sua deviazione standard, ma non dimenticare di arrotondare per eccesso anche loutput.

Passaggio 3: imposta i valori dellasse x per la curva.

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

Nel nostro caso, i valori dellasse x verranno utilizzati per illustrare un particolare punteggio di esame mentre i valori dellasse y diranno noi la probabilità che uno studente ottenga quel punteggio allesame.

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

Iniziamo un conteggio da uno (poiché non è possibile che uno studente possa ottenere un punteggio negativo allesame) e andiamo fino a 150: non importa se è 150 o 1500, per impostare unaltra tabella di supporto.

1. Seleziona una cella vuota sotto i dati del grafico (come E4) e digita “1” il valore che definisce il primo intervallo.
2. Passa alla scheda Home.
3. Nel gruppo Modifica, scegli “Riempi”.
4. In “Serie in, “Seleziona” Colonna “.
5. Per” Valore passo “, digita” 1 “. Questo valore determina gli incrementi che verranno aggiunti automaticamente fino a quando Excel non raggiunge lultimo intervallo.
6. Per “Valore di arresto”, digitare “150”, il valore che rappresenta lultimo intervallo e fare clic su “OK. “

Miracolosamente, 149 celle nella colonna E (E5: E153) sono stati riempiti con i valori compresi tra 2 e 150.

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

Passaggio 4: calcola i valori di distribuzione normale per ogni valore dellasse x.

Ora, trova i valori di distribuzione normale: la probabilità che uno studente ottenga un determinato punteggio dellesame rappresentato da un particolare valore dellasse x —Per ciascuno degli intervalli. Fortunatamente per te, Excel ha il cavallo di battaglia per eseguire tutti questi calcoli: la funzione DISTRIB.NORM.

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

Ecco la versione decodificata per aiutarti a regolare di conseguenza:

Blocca i valori di media e SD in modo da poter eseguire facilmente la formula per gli intervalli rimanenti (E5: E153).

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

Passaggio 5: crea un grafico a dispersione con linee morbide.

Infine, è arrivato il momento di costruire la curva a campana:

1. Seleziona un valore qualsiasi nella tabella helper contenente i valori degli assi x e y (E4: F153).
2. Vai alla scheda Inserisci.
3. Fai clic sul pulsante “Inserisci grafico a dispersione (X, Y) o a bolle”.
4. Scegli “Dispersione con linee morbide”.

Passaggio 6: imposta la tabella delle etichette.

Tecnicamente, tu avere la tua curva a campana. Ma sarebbe difficile da leggere in quanto manca di dati che la descrivano.

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

Per questo, imposta unaltra tabella di supporto come segue:

Innanzitutto, copia il valore medio (F1) accanto alla cella corrispondente nella colonna Valore X (I5).

Successivamente, calcola i valori della deviazione standard sotto la media inserendo questa semplice formula nella cella I4:

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

Ripeti la stessa procedura per le deviazioni standard sopra la media utilizzando la formula speculare:

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

Infine, riempi i valori delletichetta dellasse y (J2: J8) con zeri come desideri indicatori di dati posizionati sullasse orizzontale.

Passaggio 7: inserisci i dati delletichetta nel grafico.

Ora aggiungi tutti i dati che hai preparato. Fai clic con il pulsante destro del mouse sul grafico e scegli “Seleziona dati”.

Nella finestra di dialogo che si apre, seleziona ” Aggiungi. “

Evidenzia i rispettivi intervalli di celle dalla tabella helper: I2: I8 per” Valori serie X “e J2 : J8 per “Valori serie Y” e fai clic su “OK”.

Passaggio 8: cambia il tipo di grafico di la serie di etichette.

Il passaggio successivo consiste nel modificare il tipo di grafico della serie appena aggiunta per fare in modo che gli indicatori di dati appaiano come punti. Per farlo, fai clic con il pulsante destro del mouse sul grafico e seleziona “Modifica Tipo di grafico. “

Successivamente, progetta un grafico combinato:

1. Vai al Scheda combinata.
2. Per la serie “Serie2”, cambia “Tipo di grafico” in “Scatter”.
   • Nota. Assicurati che “Serie1” rimanga come “Scatter with Smooth Lines”. A volte Excel lo cambierà quando crei una combinazione. Assicurati inoltre che “Serie1” non sia inserito nellasse secondario: la casella di controllo accanto al tipo di grafico non deve essere contrassegnata.
3. Fai clic su “OK”.

Passaggio 9: modifica la scala dellasse orizzontale.

Centra il grafico sulla curva a campana regolando la scala dellasse orizzontale. Fai clic con il pulsante destro del mouse sullasse orizzontale e seleziona “Formato asse” dal menu.

Quando viene visualizzato il riquadro delle attività, procedi come segue:

• Vai alla scheda Opzioni asse.
• Imposta il valore Limiti minimi su “15.”
• Imposta il valore Limiti massimi su “125”

Puoi modificare lintervallo di scala dellasse come meglio credi, ma poiché conosci gli intervalli di deviazione standard, imposta i valori dei limiti un po lontani da ciascuna delle tue terze deviazioni standard per mostrare la “coda” della curva .

Passaggio 10: inserisci e posiziona le etichette dati personalizzate.

Mentre ripulisci il grafico, assicurati per aggiungere le etichette dati personalizzate. Innanzitutto, fai clic con il pulsante destro del mouse su qualsiasi punto che rappresenta la serie “Serie2” e seleziona “Aggiungi etichette dati”.

Quindi, sostituisci le etichette predefinite con quelle che hai impostato in precedenza e posizionale sopra gli indicatori di dati.

1. Fai clic con il pulsante destro del mouse su qualsiasi etichetta di dati “Serie2” della serie.
2. Seleziona “Formato Etichette dati. “
3. Nel riquadro delle attività, passa alla scheda Opzioni etichetta.
4. Seleziona la casella” Valore X “.
5. Deseleziona il” Valore Y “Casella.
6. In” Posizione etichetta “, scegli” 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 adattarli allo stile del tuo grafico.

1. Fai clic con il pulsante destro del mouse su qualsiasi etichetta di dati “Serie2” della serie.
2. Fai clic su il pulsante “Riempi”.
3. Scegli il tuo colore dalla tavolozza visualizzata.

Inoltre, r smuovi i bordi attorno ai punti:

1. Fai di nuovo clic con il pulsante destro del mouse sullo stesso indicatore di dati e seleziona “Contorno”.
2. Scegli “Nessun contorno”.

Passaggio 12: aggiungi linee verticali (facoltativo).

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

• Seleziona il grafico del grafico (in questo modo, le linee verranno inserite direttamente nel grafico).
• Vai alla scheda Inserisci.
• Fai clic sul pulsante “Forme”.
• Scegli “Linea”.

Tieni premuto il tasto “MAIUSC” mentre trascinando il mouse per disegnare linee perfettamente verticali da ogni punto al punto in cui ogni linea incontra la curva a campana.

Cambia il titolo del grafico e la tua curva a campana migliorata è pronta, mostrando 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!