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

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 dato 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 forma di campana, 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 tu stesso. Ecco perché abbiamo sviluppato il componente aggiuntivo Chart Creator, uno strumento che ti permette 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, è necessario conoscere 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 misure. Questo definisce la diffusione dei dati nella distribuzione normale-o in parole povere, quanto dovrebbe essere ampia 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ù bassa è la 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 dati verrà posizionato all’interno di una SD lontana dalla media, il 95% entro due SD e il 99,7% entro tre SD.

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

Introduttiva

Per illustrare, si supponga di avere i punteggi dei test di 200 studenti e desidera il grado di una curva,” significato degli studenti classi sarà basato sulla loro performance relativa al resto della classe:

Passo #1: Trovare la media.

In genere, ti vengono dati i valori medi e SD fin dall’inizio, 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, è possibile trovare la misurazione standard utilizzando la funzione MEDIA.

Digitare 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: più spesso di quanto non, potrebbe essere necessario per completare la formula di uscita. Per fare questo, basta avvolgerlo in funzione ROUND come segue:

Passo #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:

Ancora una volta, la formula raccoglie tutti i valori dall’intervallo di celle specificato (B2:B201) e calcola la sua deviazione standard, ma non dimenticare di arrotondare anche l’output.

Passo #3: Impostare i valori dell’asse x per la curva.

Fondamentalmente, il grafico costituisce un numero enorme di intervalli (pensali come passi) 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, è possibile includere tutti gli intervalli che si desidera-è possibile cancellare facilmente i dati ridondanti in seguito modificando la scala dell’asse orizzontale. Basta fare in modo di scegliere un intervallo che incorporerà le tre deviazioni standard.

Iniziamo un conteggio a uno (poiché non c’è modo che uno studente possa ottenere un punteggio negativo all’esame) e andare fino a 150—non importa se è 150 o 1500—per impostare un’altra tabella di supporto.

1. Selezionare una cella vuota sotto i dati del grafico (ad esempio E4) e digitare “1”, il valore che definisce il primo intervallo.
2. Passare alla scheda Home.
3. Nel gruppo di modifica, scegliere ” Riempi.”
4. Sotto” Serie in, “selezionare” Colonna.”
5. Per” Valore passo”, digitare ” 1.”Questo valore determina gli incrementi che verranno automaticamente sommati fino a quando Excel raggiunge l’ultimo intervallo.
6. Per “Valore di arresto”, digitare “150”, il valore che rappresenta l’ultimo intervallo e fare clic su “OK.”

Miracolosamente, 149 celle nella colonna E (E5:E153) sono state riempite con i valori che vanno da 2 a 150.

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

Passo # 4: Calcola i valori di distribuzione normali per ogni valore dell’asse X.

Ora, trova i valori di distribuzione normali – la probabilità che uno studente ottenga un determinato punteggio d’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 NORMA.Funzione DIST.

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

Ecco la versione decodificata per aiutarti a regolare di conseguenza:

Si bloccano i valori medi e SD in modo da poter eseguire senza sforzo la formula per gli intervalli rimanenti (E5:E153).

Ora, fare doppio clic sull’handle di riempimento per copiare la formula nel resto delle celle (F5:F153).

Passo # 5: Creare un grafico a dispersione con linee morbide.

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

1. Selezionare qualsiasi valore nella tabella helper contenente i valori degli assi x e Y (E4:F153).
2. Vai alla scheda Inserisci.
3. Fare clic sul pulsante “Inserisci Scatter (X, Y) o Bubble Chart”.
4. Scegli ” Scatter con linee morbide.”

Passo #6: Impostare la tabella delle etichette.

Tecnicamente, hai la tua curva a campana. Ma sarebbe difficile da leggere in quanto manca qualsiasi dato che lo descriva.

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 un’altra tabella di supporto come segue:

Innanzitutto, copiare il valore medio (F1) accanto alla cella corrispondente nella colonna X-Value (I5).

a quel punto, calcolare la deviazione standard dei valori al di sotto della media inserendo questa semplice formula nella cella I4:

messo Semplicemente, la formula sottrae la somma dei precedenti valori di deviazione standard dalla media. Ora, trascinare la maniglia di riempimento verso l’alto per copiare la formula nelle restanti due celle (I2:I3).

Ripetere lo stesso processo per le deviazioni standard sopra la media utilizzando la formula specchio:

allo stesso modo, eseguire la formula per le altre due cellule (I7:I8).

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

Passo # 7: Inserire i dati dell’etichetta nel grafico.

Ora, aggiungi tutti i dati che hai preparato. Fare clic con il tasto destro sul grafico e scegliere ” Seleziona dati.”

Nella finestra di dialogo che si apre, selezionare “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.”

Passo #8: Modificare il tipo di grafico della serie di etichette.

Il nostro prossimo passo è cambiare il tipo di grafico della serie appena aggiunta per far apparire i marcatori di dati come punti. Per fare ciò, fare clic destro sul grafico e selezionare ” Cambia tipo di grafico.”

Quindi, progettare un grafico combinato:

1. Passare alla scheda Combo.
2. Per la serie “Series2”, cambia “Tipo grafico” in ” Scatter.”
   • Nota: Assicurarsi che “Series1″ rimane come ” Scatter con linee morbide.”A volte Excel cambierà quando si effettua una Combo Anche assicurarsi che “Series1” non viene spinto verso l’asse secondario—la casella di controllo accanto al tipo di grafico non deve essere contrassegnato.
3. Fare clic su ” OK.”

Passo # 9: Modificare la scala dell’asse orizzontale.

Centrare il grafico sulla curva a campana regolando la scala dell’asse orizzontale. Fare clic destro sull’asse orizzontale e selezionare “Formato asse” dal menu.

Una volta visualizzato il riquadro attività, procedere come segue:

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

Puoi modificare l’intervallo di scala dell’asse come meglio credi, ma poiché conosci gli intervalli di deviazione standard, imposta i valori dei limiti un po ‘ lontano da ciascuna delle tue terze deviazioni standard per mostrare la “coda” della curva.

Passo #10: Inserire e posizionare le etichette dei dati personalizzati.

Come si lucidare il grafico, assicurarsi di aggiungere le etichette di dati personalizzati. Innanzitutto, fare clic destro su qualsiasi punto che rappresenta la serie ” Series2 “e selezionare” Aggiungi etichette dati.”

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

1. Tasto destro del mouse su qualsiasi etichetta dati Serie “Series2”.
2. Selezionare ” Formatta etichette dati.”
3. Nel riquadro attività, passare alla scheda Opzioni etichetta.
4. Seleziona la casella “Valore X”.
5. Deseleziona la casella “Valore Y”.
6. Sotto “Posizione etichetta”, scegliere ” Sopra.”

Inoltre, è ora possibile rimuovere le griglie (fare clic destro su di esse > Elimina).

Passo #11: Ricolorare i marcatori di dati (opzionale).

Infine, ricolorare i punti per aiutarli a inserirsi nel vostro stile grafico.

1. Tasto destro del mouse su qualsiasi etichetta dati Serie “Series2”.
2. Fare clic sul pulsante “Riempi”.
3. Scegli il tuo colore dalla tavolozza che appare.

Inoltre, rimuovere i bordi attorno ai punti:

1. Fare nuovamente clic con il tasto destro sullo stesso indicatore di dati e selezionare ” Outline.”
2. Scegliere ” Nessun contorno.”

Passo #12: Aggiungere linee verticali (opzionale).

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

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

Tenere premuto il tasto “SHIFT” mentre si trascina il mouse per disegnare linee perfettamente verticali da ogni punto a dove 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!