Excel 2013 include 52 nuove funzioni, la maggior parte delle quali sono state aggiunte per essere conformi agli standard Open Document Spreadsheet.
Questo post tratterà la funzione Gauss di Excel 2013.
Attualmente, la guida di Excel è un po 'poco brillante nella descrizione della funzione.
Sintassi: =GAUSS(x)- Restituisce 0,5 in meno rispetto alla distribuzione cumulativa normale standard.
Come rapido aggiornamento, la distribuzione normale standard è un caso speciale con una media di 0 e una deviazione standard di 1. La riconoscerai come curva a campana.
Excel ha sempre avuto un modo per calcolare le probabilità per la curva normale standard. Prima DISTRIB.NORMSD e poi in Excel 2010 DISTRIB.NORM.S (z, True) calcola le probabilità. L'argomento "z" è il numero di deviazioni standard dalla media.
Ecco un esempio banale dell'utilizzo di DISTRIB.NORM.S per calcolare una probabilità. Qual è la probabilità che un membro casuale della popolazione sia inferiore a -0,5 deviazioni standard dalla media? Questa è l'area ombreggiata nella Figura 2. La formula è semplice =NORM.S.DIST(-0.5,True).
Abbastanza semplice, vero? Se fossi interessato solo alle piccole cose, questa formula sarebbe tutto ciò di cui hai bisogno. Tuttavia, i ricercatori sono spesso interessati ad altri intervalli oltre al lato sinistro della curva.
Nella Figura 3, si desidera conoscere la probabilità che un membro casuale cada tra (deviazione standard media-0,5) e (deviazione standard media + 1). Non esiste una funzione NORM.S.DIST.RANGE, quindi puoi semplicemente chiedere la probabilità tra -0.5,1). Invece, devi trovare la risposta in due sottoformule. Calcola la probabilità di essere minore di +1 con =NORM.S.DIST(1,True)e poi sottrai la probabilità di essere minore di -0,5 con =NORM.S.DIST(-.5,True). Puoi farlo in una singola formula, come mostrato nella Figura 3.
Mi rendo conto che questo è un post lungo, ma l'immagine sopra è l'immagine più importante per comprendere la nuova funzione GAUSS. Rileggi quel paragrafo per assicurarti di aver compreso il concetto. Per ottenere la probabilità che un membro della popolazione cada tra due punti sulla curva, iniziare con DISTRIB.NORM.S del punto destro e sottrarre DISTR.NORM.S. del punto sinistro. Non è scienza missilistica. Non è nemmeno complicato come CERCA.VERT. La funzione restituisce sempre la probabilità dal bordo sinistro della curva (-infinito) al valore di z.
E se fossi interessato alla probabilità di essere più grande di una certa taglia? Per trovare la possibilità di essere maggiore di (media + 1 deviazione standard), puoi iniziare con 100% e sottrarre la possibilità di essere inferiore a (media + 1 deviazione standard). Questo sarebbe =100%-NORM.S.DIST(1,True). Poiché 100% è uguale a 1, puoi abbreviare la formula a =1-NORM.S.DIST(1,True). Oppure puoi renderti conto che la curva è simmetrica e chiedere il NORM.S.DIST (-1, True) per ottenere la stessa risposta.
Per quelli di voi come DOC come me, posso assicurarvi che se =SUM(30.85,53.28,15.87)finirete con il 100%. Lo so perché l'ho controllato nel foglio di lavoro.
Tornando alla Figura 3, dovresti sapere come calcolare la probabilità da due punti qualsiasi z1 e z2. Sottrai NORM.S.DIST (z2, True) -NORM.S.DIST (z1, True) e avrai la risposta. Consideriamo il caso molto speciale in cui z1 è la media. Stai cercando di capire la probabilità che qualcuno si trovi tra la media e +1,5 deviazioni standard dalla media, come illustrato nella Figura 6.
Utilizzando quanto appreso dalla Figura 3, quale di questi troverebbe la probabilità dell'area sotto la curva sopra?
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) - Nessuno dei precedenti
Come hai fatto? A condizione che tu abbia risposto A, B o C hai ottenuto il 100% nel test. Congratulazioni. Come ho detto, non è davvero scienza missilistica.
Per quelli di voi che amano le scorciatoie, ricordate che c'è una probabilità del 50% che qualcosa sia inferiore o uguale alla media. Quando vedi = DISTRIB.NORM.S (0, Vero), puoi immediatamente pensare, "Oh, questo è il 50%!". Quindi, la risposta B sopra potrebbe essere riscritta come
=NORM.S.DIST(1.5,True)-50%
Ma se ami le scorciatoie, odi digitare il 50% e lo ridurresti a .5:
=NORM.S.DIST(1.5,True)-.5
Potresti usare l'opposto simmetrico dell'area sotto la curva? Sì, = .5-DISTRIB.NORM.S (-1.5, True) ti darà lo stesso risultato. Quindi, il quiz sopra potrebbe essere:
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-.5 -
=.5-NORM.S.DIST(-1.5,True) - Tutto quanto sopra
A condizione che tu scelga una risposta, ti darò tutto il merito. Dopo tutto, è Excel. Ci sono cinque modi per fare qualsiasi cosa e accetterò qualsiasi risposta che funzioni (beh, a parte l'hardcoding = 0,433 in una cella).
Per quelli di voi che hanno ottenuto la risposta all'ultima domanda corretta, smettete di leggere. Tutti gli altri avranno bisogno di GAUSS:
E la funzione GAUSS? Bene, la funzione GAUSS ci offre ancora un altro modo per risolvere il caso specifico in cui l'intervallo va dalla media a un punto sopra la media. Invece di usare le risposte sopra, potresti usare =GAUSS(1.5).
Sì … hanno aggiunto una funzione per le persone che non possono sottrarre 0,5 da DISTRIB.NORM.S!
Se sei come me, ti stai chiedendo: "Sul serio? Hanno sprecato risorse per aggiungere questa funzione?" Bene, in Excel 2007, il team di Excel ha deciso di consentirci di salvare i documenti nel formato .ODS. Questo è il formato Open Document Spreadsheet. Non è un formato controllato da Microsoft. Poiché offrono supporto per ODS, Microsoft è costretta ad aggiungere tutte le funzioni supportate da Open Document Spreadsheet. Apparentemente, la maggioranza delle persone del consorzio Open Document Spreadsheet non è riuscita a capire che la risposta al mio primo quiz era A, quindi hanno aggiunto una funzione completamente nuova.
Immagino che Microsoft non fosse entusiasta di aggiungere il supporto per funzioni che erano simili ad altre funzioni già in Excel. Posso quasi immaginare la conversazione tra lo scrittore tecnico incaricato di scrivere su GAUSS nella Guida di Excel e il project manager del team di Excel:
Scrittore: "Allora, parlami di GAUSS"
PM: "È inutile. Prendi =NORM.S.DISTe sottrai 0,5. Non posso credere che abbiamo dovuto aggiungere questo."
L'autore ha quindi modificato i commenti editoriali e ha offerto questo argomento della Guida:
Quindi, permettimi di offrire questo argomento della guida alternativo:
GAUSS (z) - Calcola la probabilità che un membro di una popolazione normale standard cada tra la media e le deviazioni standard + z dalla media.
- z Obbligatorio. Il numero di deviazioni standard sopra la media. Generalmente nell'intervallo da +0,01 a +3.
- Aggiunto a Excel 2013 per supportare le persone che non possono sottrarre due numeri.
- Non particolarmente significativo per valori negativi di Z. Per calcolare la probabilità che qualcosa rientri nell'intervallo di -1,5 rispetto alla media, utilizzare
=GAUSS(1.5). - Non funzionerà in Excel 2010 e versioni precedenti. In Excel 2010 e versioni precedenti, utilizzare
=NORM.S.DIST(z,True)-0.5.
Ecco qua … più di quanto avresti mai voluto sapere su GAUSS. È certamente più di quanto avrei mai voluto sapere. A proposito, i miei libri Excel In Depth offrono una descrizione completa di tutte le 452 funzioni in Excel. Dai un'occhiata all'edizione precedente, Excel 2010 In Depth o il nuovo Excel 2013 In Depth che verrà rilasciato a novembre 2012.
