Jimmy a Huntsville vuole tracciare una curva a campana che mostri i punteggi medi di diverse persone. Quando Jimmy ha posto la domanda durante il mio seminario su Power Excel, ho ripensato a uno dei miei video più popolari su YouTube.
In Podcast 1665 - Crea una curva a campana in Excel, spiego che per creare una curva a campana, devi calcolare la media e la deviazione standard. Genero quindi 30 punti lungo l'asse x che coprono un'ipotetica popolazione di persone. In quel video, ho generato un valore compreso tra -3 deviazioni standard e + 3 deviazioni standard intorno a una media.
Ad esempio, se hai una media di 50 e una deviazione standard di 10, creerei un asse x che va da 70 a 130. L'altezza di ogni punto viene calcolata usando =NORM.DIST(x,mean,standard deviation,False).
Nell'immagine sopra, i numeri in A10: A40 sono essenzialmente "punti dati falsi". Genero 31 numeri per creare una bella curva liscia. Se avessi usato solo 7 punti dati, la curva sarebbe simile a questa:
Per il set di dati di Jimmy, i punteggi medi effettivi dei suoi dipendenti sono essenzialmente punti lungo un asse x. Per adattarli a una curva a campana, è necessario calcolare l'altezza o il valore Y per ciascun dipendente.
Segui questi passi:
-
Ordina i dati in modo che i punteggi appaiano dal più basso al più alto.
Ordina i dati - Calcola una media utilizzando la funzione MEDIA.
- Calcola una deviazione standard utilizzando la funzione DEV.ST.
-
Calcola il valore Y a destra dei punteggi utilizzando
=NORM.DIST(L2,$H$2,$H$3,FALSE). Il valore Y genererà l'altezza del punto di ogni persona lungo la curva a campana. La funzione DISTRIB.NORM si occuperà di tracciare le persone vicino alla media in una posizione più alta rispetto alle persone vicino alla parte superiore o inferiore.
Genera una serie di valori Y. - Seleziona i tuoi dati in L1: M15
-
Un bug strano ha iniziato a comparire di recente in Excel, quindi per garantire il successo, scegli Tutti i grafici nella scheda Inserisci.
Il programma di avvio della finestra di dialogo ti porta a tutti i tipi di grafico Nella finestra di dialogo Inserisci grafico, fai clic sulla scheda Tutti i grafici. Fare clic su XY (dispersione) lungo la sinistra. Scegli la seconda icona in alto. Scegli l'anteprima a destra.
Quattro clic per scegliere il grafico La curva della campana iniziale sarà simile a questa:
La curva a campana
Per pulire la curva a campana, segui questi passaggi:
- Fare clic sul titolo e premere il tasto Canc.
- Fare doppio clic su qualsiasi numero lungo l'asse Y nella parte inferiore del grafico. Apparirà il pannello Format Axis.
-
Digita nuovi valori per Minimo e Massimo. L'intervallo qui dovrebbe essere abbastanza ampio da mostrare a tutti sul grafico. Ho usato da 50 a 90.
Cambia il minimo e il massimo - Allarga il grafico trascinando il bordo del grafico.
- Fare clic sull'icona + a destra del grafico e selezionare Etichette dati. Non preoccuparti che le etichette non abbiano ancora senso.
- Fare doppio clic su un'etichetta per aprire il pannello Formato etichette.
- Ci sono quattro icone nella parte superiore del pannello. Scegli l'icona che mostra un grafico a colonne.
- Fare clic sulla freccia accanto a Opzioni etichetta per espandere quella parte del pannello.
- Scegli Valore dalle celle. Apparirà una finestra di dialogo che chiederà la posizione delle etichette. Scegli i nomi in K2: K15.
-
Sempre nel pannello Formato etichetta dati, deseleziona i valori Y. È importante completare il passaggio 15 prima di eseguire il passaggio 16, altrimenti le etichette verranno rimosse inavvertitamente.
Ottieni le etichette dalle celle contenenti i nomi.
Nota
La possibilità di ottenere etichette dalle celle è stata aggiunta in Excel 2013. Se utilizzi Excel 2010 o versioni precedenti, scarica il componente aggiuntivo XY Chart Labeler da Rob Bovey. (Google per trovarlo).
A questo punto, controlla se hai etichette del grafico che si bloccano l'una con l'altra. Per risolverli, segui attentamente questi passaggi.
- Fare clic una volta su un'etichetta del grafico. Questo seleziona tutte le etichette.
- Fare clic una volta su una delle etichette che si trova sopra un'altra etichetta per selezionare solo quell'etichetta.
- Passa il mouse su varie parti dell'etichetta finché non vedi una freccia a quattro punte. Fare clic e trascinare l'etichetta in una nuova posizione.
-
Dopo aver selezionato una sola etichetta, puoi fare clic una volta su qualsiasi altra etichetta per selezionarla. Ripeti per tutte le altre etichette che devono essere spostate.
Il grafico finale
Guarda un video
Trascrizione del video
Impara Excel da Podcast, episodio 2217: Posiziona le persone su una curva a campana.
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi, di Jimmy nel mio seminario a Huntsville, in Alabama. Jimmy ha dei dati, vuole riassumere questi dati e poi tracciare i risultati su una curva a campana.
Tutto a posto? Ora, uno dei miei video più popolari su YouTube è questo: numero 1663, Crea una curva a campana in Excel. E data una media e una deviazione standard, ho calcolato il minimo, che è 3 volte la deviazione standard inferiore alla media, e il massimo - 3 volte la deviazione standard più della media - dove si trova il divario - e una serie di valori X qui, e per calcolare l'altezza, usa questa funzione: = NORM.DIST del valore X, la media e la deviazione standard, virgola false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FALSO)).
E se ci pensi, questo video utilizza solo una serie di falsi valori X qui per ottenere una bella curva. E useremo lo stesso concetto qui, ma invece di falsi valori X, in realtà avremo le persone quaggiù e quindi l'altezza sarà esattamente la stessa formula. Tutto a posto.
Quindi, ora, Jimmy voleva creare una tabella pivot. Quindi inseriremo, tabella pivot, lo metteremo qui su questo foglio, fare clic su OK. Persone in basso a sinistra e poi il loro punteggio medio. Va bene, quindi inizia con la somma del punteggio, farò doppio clic lì e lo cambierò in una media. Grande. Ora, in fondo, non voglio un totale generale - fare clic con il pulsante destro del mouse e Rimuovi totale generale - e vogliamo disporre queste Persone dall'alto verso il basso e questo è facile da fare in una tabella pivot. Dati, dalla A alla Z … eccellente. Tutto a posto. Ora, faremo la stessa identica cosa che abbiamo fatto nel Podcast 1663, ovvero calcolare una media e una deviazione standard. Quindi la media è una media di questi punteggi e quindi è uguale alla deviazione standard di quei punteggi. Tutto a posto. Ora che lo so, sono in grado di creare il mio valore y.
Va bene, quindi un paio di cose che faremo qui. Prima di tutto, non puoi creare una tabella pivot, un grafico a dispersione, da una tabella pivot. Quindi copierò tutti questi dati e lo farò con = D2. Si noti che sto attento a non usare il mouse o i tasti freccia per puntare a quelli. E quindi abbiamo i nostri valori qui. Questi diventeranno valori X, il valore Y diventerà = NORM.DIST, ecco il valore x, virgola, per la media, quel numero, premerò F4 per bloccarlo; per la deviazione standard è questo numero, ancora una volta, premi F4 per bloccarlo e cumulativo FALSO. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) E faremo doppio clic per copiarlo. Tutto a posto. E poi, non scegliere le etichette,basta scegliere XY e inseriremo un grafico a dispersione con linee: puoi scegliere quello con linee curve o un po 'rette. Ecco, vado con linee curve come questa. E ora abbiamo tutte le nostre persone posizionate su una curva a campana.
Tutto a posto. Ora, alcune cose, alcune cose sul tipo di formattazione, le faremo qui: prima di tutto, fai doppio clic qui in basso lungo la scala e sembra che il nostro numero più basso sia probabilmente intorno a 50, quindi farò imposta un minimo di 50 e il nostro numero più grande, il nostro numero più grande, è 88, quindi imposterò un massimo di 90. Va bene. E ora dobbiamo etichettare questi punti. Se sei in Excel 2013 o più recente, è facile; ma se sei in una versione precedente di Excel, dovrai tornare indietro e utilizzare il componente aggiuntivo Etichettatrice grafico di Rob Bovey per fare in modo che queste etichette dei punti provengano da un punto che non è nel grafico. Va bene, quindi iniziamo qui. Aggiungeremo le etichette dati e aggiungeranno numeri e avranno un aspetto orribile. Vengo qui e dico che voglio più opzioni, opzioni etichetta,e voglio ottenere il valore dalle celle - Valore dalle celle. Tutto a posto? Quindi l'intervallo di celle è proprio lì, fai clic su OK. È molto importante utilizzare Value dalle celle prima di deselezionare il valore Y. Inizia ad avere un bell'aspetto. Mi libererò di questo. Ora, la chiave qui, perché ci sono alcune persone che si sovrascrivono a vicenda, è cercare di rendere il grafico il più grande possibile. Non abbiamo bisogno di una voce lassù. Perché? Basta cancellarlo. E vedo ancora, tipo, Kelly e Lou, Andy e Flo sono quasi nello stesso posto; Jared e … Va bene. Quindi ora, questo sarà frustrante, questi che si sovrappongono. Ma quando facciamo clic su un'etichetta, selezioniamo tutte le etichette, quindi facciamo nuovamente clic su un'etichetta e selezioniamo solo una singola etichetta. Tutto a posto? Così ora. molto attentamente. prova a fare clic su Andy e trascina Andy in alto a sinistra.Sembra che Jared e Ike stiano insieme, quindi ora che sono in modalità di selezione etichetta singola, è più facile. E poi Kelly e Lou, trascinali in quel modo. Forse c'è un posto migliore che non sovrasta Lou, o anche, tipo, qui posso trascinarlo su entrambi i lati. Va bene, quindi, cosa abbiamo? Abbiamo iniziato con una serie di dati, creato una tabella pivot, calcolato la media e la deviazione standard, che ci consente solo di calcolare l'altezza: la posizione Y per ciascuno di quei punteggi e l'altezza di quelli, si spera, faremo entrare le persone in una bella curva a campana a forma di parabola, come quella.Va bene, quindi, cosa abbiamo? Abbiamo iniziato con una serie di dati, creato una tabella pivot, calcolato la media e la deviazione standard, che ci consente solo di calcolare l'altezza: la posizione Y per ciascuno di quei punteggi e l'altezza di quelli, si spera, faremo entrare le persone in una bella curva a campana a forma di parabola, come quella.Va bene, quindi, cosa abbiamo? Abbiamo iniziato con una serie di dati, creato una tabella pivot, calcolato la media e la deviazione standard, che ci consente solo di calcolare l'altezza: la posizione Y per ciascuno di quei punteggi e l'altezza di quelli, si spera, faremo entrare le persone in una bella curva a campana a forma di parabola, come quella.
Adoro questa domanda di Jimmy, questa domanda non è in questo libro, ma sarà la prossima volta che scriverò questo libro. Devo aggiungere questo: è una richiesta interessante e un piccolo trucco interessante. Le curve a campana sono molto popolari in Excel.
Ma dai un'occhiata al mio libro, LIVe, The 54 Greatest Excel Tips of All Time.
Va bene, conclusione di questo episodio: Jimmy di Huntsville, vuole organizzare le persone su una curva a campana. Quindi usiamo una tabella pivot per calcolare il punteggio medio, ordinare le tabelle pivot in base ai punteggi - disposti dal più alto al più basso - sbarazzarci del totale generale in basso - questi saranno essenzialmente i valori X-- e poi di lato, calcola la media e la deviazione standard di quei punteggi e usa le formule per copiare i dati dalla tabella pivot a un nuovo intervallo, perché non puoi avere un grafico XY che si interseca con una tabella pivot. Calcola un valore y per ogni persona con = NORM.DIST del loro valore x, la media, la deviazione standard, virgola FALSE; crea un grafico a dispersione XY con linee morbide: se sei un Excel 2010 o versioni precedenti, utilizzerai il componente aggiuntivo per l'etichettatura dei grafici di Ron Bovey. Ti farò cercare su Google perché,nel caso in cui Rob cambi il suo URL, non voglio qui l'URL sbagliato. In Excel 2013, aveva Etichette dati, Dalle celle, specificare i nomi e quindi alcune regolazioni: modificare la scala lungo il fondo, cambiarle in e Max e quindi spostare le etichette che si sovrappongono a vicenda.
Per scaricare la cartella di lavoro dal video di oggi, utilizza l'URL nella descrizione di YouTube. Voglio ringraziare Jimmy per questa fantastica domanda a Huntsville e voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Scarica il file Excel
Per scaricare il file excel: place-people-on-bell-curve.xlsx
Grazie a Jimmy a Huntsville per la domanda di oggi!
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Se hai impostato Excel in modalità di ricalcolo manuale nell'ultimo mese, è il momento di Power Pivot (non avrai mai più bisogno della modalità manuale)"
Rob Collie
