Excel 2013 inkluderer 52 nye funktioner, hvoraf de fleste blev tilføjet for at være i overensstemmelse med standarderne for Open Document Spreadsheet.
Dette indlæg dækker Excel 2013 Gauss-funktionen.
I øjeblikket er Excel-hjælp lidt mangelfuld i deres beskrivelse af funktionen.
Syntaks: =GAUSS(x)- Returnerer 0,5 mindre end den normale normale kumulative fordeling.
Som en hurtig genopfriskning er standardnormalfordelingen et specielt tilfælde med et gennemsnit på 0 og en standardafvigelse på 1. Du genkender det som klokkekurven.
Excel har altid haft en måde at beregne sandsynligheder for den normale normale kurve. Først vil NORMSDIST og derefter i Excel 2010 NORM.S.DIST (z, True) beregne sandsynligheder. Argumentet "z" er antallet af standardafvigelser fra gennemsnittet.
Her er et trivielt eksempel på at bruge NORM.S.DIST til at beregne en sandsynlighed. Hvad er sandsynligheden for, at et tilfældigt medlem fra befolkningen bliver mindre end -0,5 standardafvigelser fra gennemsnittet? Dette er det område, der er skyggelagt i figur 2. Formlen er simpel =NORM.S.DIST(-0.5,True).
Simpelt nok, ikke? Hvis du kun var interesseret i små ting, ville denne formel være alt hvad du behøver. Forskere er dog ofte interesseret i andre områder end venstre side af kurven.
I figur 3 vil du vide sandsynligheden for, at et tilfældigt medlem falder mellem (gennemsnit-0,5 standardafvigelser) og (gennemsnit + 1 standardafvigelser). Der er ikke en NORM.S.DIST.RANGE-funktion, så du kan simpelthen bede om sandsynligheden mellem -0,5,1). I stedet skal du finde svaret i to underformler. Beregn sandsynligheden for at være mindre end +1 med, =NORM.S.DIST(1,True)og træk derefter sandsynligheden for at være mindre end -0,5 med =NORM.S.DIST(-.5,True). Du kan gøre dette i en enkelt formel som vist i figur 3.
Jeg er klar over, at dette er et langt indlæg, men billedet ovenfor er det vigtigste billede for at forstå den nye GAUSS-funktion. Genlæs dette afsnit for at sikre, at du forstår konceptet. For at få sandsynligheden for, at et medlem af befolkningen falder mellem to punkter på kurven, starter du med NORM.S.DIST for det rigtige punkt og trækker NORM.S.DIST fra det venstre punkt. Det er ikke raketvidenskab. Det er ikke engang så kompliceret som VLOOKUP. Funktionen returnerer altid sandsynligheden fra kurvens venstre kant (-infinity) til værdien af z.
Hvad hvis du er interesseret i sandsynligheden for at være større end en bestemt størrelse? For at finde chancen for at være større end (gennemsnit + 1 standardafvigelse) kan du starte med 100% og trække muligheden for at være mindre end (gennemsnit + 1 standardafvigelse). Dette ville være =100%-NORM.S.DIST(1,True). Da 100% er det samme som 1, kan du forkorte formlen til =1-NORM.S.DIST(1,True). Eller du kan indse, at kurven er symmetrisk og bede om NORM.S.DIST (-1, True) for at få det samme svar.
For de af jer, der er så OCD som jeg er, kan jeg forsikre jer om, at hvis =SUM(30.85,53.28,15.87)man ender med 100%. Jeg ved det, fordi jeg tjekkede det i regnearket.
Gå tilbage til figur 3 - du skal vide, hvordan du beregner sandsynligheden ud fra to punkter z1 og z2. Træk NORM.S.DIST (z2, True) -NORM.S.DIST (z1, True), så får du svaret. Lad os overveje det meget specielle tilfælde, hvor z1 er middelværdien. Du forsøger at finde ud af sandsynligheden for, at nogen er mellem middelværdien og +1,5 standardafvigelser fra gennemsnittet, som illustreret i figur 6.
Brug af det, du lærte fra figur 3, hvilke af disse ville finde sandsynligheden for området under kurven ovenfor?
-
=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) - Intet af det ovenstående
Hvordan gjorde du? Forudsat at du svarede A, B eller C, scorede du 100% på testen. Tillykke. Som jeg sagde, er det virkelig ikke raketvidenskab.
For de af jer, der elsker genveje, skal man huske, at der er 50% sandsynlighed for, at noget vil være mindre end eller lig med gennemsnittet. Når du ser = NORM.S.DIST (0, sandt), kan du straks tænke, "Åh - det er 50%!". Så svar B ovenfor kunne omskrives som
=NORM.S.DIST(1.5,True)-50%
Men hvis du elsker genveje, hader du at skrive 50% og vil forkorte det til .5:
=NORM.S.DIST(1.5,True)-.5
Kunne du bruge det symmetriske modsatte af området under kurven? Ja, = .5-NORM.S.DIST (-1.5, True) giver dig det samme resultat. Så quizzen ovenfor kan være:
-
=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) - Alt det ovenstående
Forudsat at du vælger et svar, giver jeg dig fuld kredit. Når alt kommer til alt er det Excel. Der er fem måder at gøre noget på, og jeg accepterer ethvert svar, der fungerer (godt, bortset fra hårdkodning = 0,433 i en celle).
For de af jer, der fik svaret på det sidste spørgsmål korrekt, skal du stoppe med at læse. Alle andre har brug for GAUSS:
Hvad med GAUSS-funktionen? Nå, GAUSS-funktionen giver os endnu en måde at løse det specifikke tilfælde, hvor området går fra middelværdien til et punkt over gennemsnittet. I stedet for at bruge svarene ovenfor kan du bruge =GAUSS(1.5).
Ja … de tilføjede en funktion til folk, der ikke kan trække 0,5 fra NORM.S.DIST!
Hvis du er som mig, spørger du: "Seriøst? De spildte ressourcer på at tilføje denne funktion?" Nå, tilbage i Excel 2007 tog Excel-teamet en beslutning om at tillade os at gemme dokumenter i .ODS-formatet. Dette er regnearkformatet Åbn dokument. Det er ikke et format, der kontrolleres af Microsoft. Da de tilbyder support til ODS, er Microsoft tvunget til at tilføje alle funktioner, som Open Document Spreadsheet understøtter. Tilsyneladende kunne et flertal af mennesker i Open Document Spreadsheet-konsortiet ikke finde ud af, at svaret på min første quiz var A, så de tilføjede en helt ny funktion.
Jeg gætter på, at Microsoft ikke var begejstret for at tilføje support til funktioner, der lignede andre funktioner, der allerede var i Excel. Jeg kan næsten forestille mig samtalen mellem den tekniske forfatter, der har til opgave at skrive om GAUSS i Excel Help og projektlederen i Excel-teamet:
Forfatter: "Så fortæl mig om GAUSS"
PM: "Det er vanvittigt. Tag =NORM.S.DISTog træk 0,5. Jeg kan ikke tro, at vi var nødt til at tilføje dette."
Forfatteren redigerede derefter redaktionelle kommentarer og tilbød dette hjælpemne:
Så - lad mig tilbyde dette alternative hjælpemne:
GAUSS (z) - Beregner sandsynligheden for, at et medlem af en normal normalpopulation falder mellem middelværdien og + z standardafvigelser fra gennemsnittet.
- z Påkrævet. Antallet af standardafvigelser over gennemsnittet. Generelt i området +0,01 til +3.
- Føjet til Excel 2013 for at støtte folk, der ikke kan trække to tal.
- Ikke særlig meningsfuld for negative værdier af Z. For at beregne sandsynligheden for, at noget falder i området -1,5 til middelværdien, skal du bruge
=GAUSS(1.5). - Fungerer ikke i Excel 2010 og tidligere. Brug i Excel 2010 og tidligere
=NORM.S.DIST(z,True)-0.5.
Der har du det … mere end du nogensinde har ønsket at vide om GAUSS. Det er bestemt mere, end jeg nogensinde har ønsket at vide. Forresten tilbyder mine Excel dybdebøger en komplet beskrivelse af alle de 452 funktioner i Excel. Tjek den forrige udgave, Excel 2010 i dybde eller den nye Excel 2013 i dybde, der frigives i november 2012.
