Excel 2013 inkluderer 52 nye funksjoner, hvorav de fleste ble lagt til for å være i samsvar med standardene for Open Document Spreadsheet.
Dette innlegget vil dekke Excel 2013 Gauss-funksjonen.
For øyeblikket er Excel-hjelp litt mangelfull i beskrivelsen av funksjonen.
Syntaks: =GAUSS(x)- Returnerer 0,5 mindre enn standard normal kumulativ fordeling.
Som en rask oppfriskning er standard normalfordeling et spesialtilfelle med et gjennomsnitt på 0 og et standardavvik på 1. Du vil gjenkjenne det som bjellekurven.
Excel har alltid hatt en måte å beregne sannsynligheter for standard normalkurve. Først NORMSDIST og deretter i Excel 2010 vil NORM.S.DIST (z, True) beregne sannsynligheter. Argumentet "z" er antall standardavvik fra gjennomsnittet.
Her er et trivielt eksempel på å bruke NORM.S.DIST til å beregne en sannsynlighet. Hva er sannsynligheten for at et tilfeldig medlem fra befolkningen blir mindre enn -0,5 standardavvik fra gjennomsnittet? Dette er området som er skyggelagt i figur 2. Formelen er ganske enkelt =NORM.S.DIST(-0.5,True).
Enkelt nok, ikke sant? Hvis du bare var interessert i små ting, ville denne formelen være alt du trenger. Imidlertid er forskere ofte interessert i andre områder enn venstre side av kurven.
I figur 3 vil du vite sannsynligheten for at et tilfeldig medlem faller mellom (gjennomsnitt-0,5 standardavvik) og (gjennomsnitt + 1 standardavvik). Det er ikke en NORM.S.DIST.RANGE-funksjon, så du kan bare be om sannsynligheten mellom -0,5,1). I stedet må du finne svaret i to underformler. Beregn sannsynligheten for å være mindre enn +1 med =NORM.S.DIST(1,True)og trekk deretter sannsynligheten for å være mindre enn -0,5 med =NORM.S.DIST(-.5,True). Du kan gjøre dette i en enkelt formel som vist i figur 3.
Jeg skjønner at dette er et langt innlegg, men bildet over er det viktigste bildet for å forstå den nye GAUSS-funksjonen. Les det avsnittet på nytt for å sikre at du forstår konseptet. For å få sannsynligheten for at et medlem av befolkningen faller mellom to punkter på kurven, begynner du med NORM.S.DIST på høyre punkt og trekker NORM.S.DIST av venstre punkt. Det er ikke rakettvitenskap. Det er ikke engang så komplisert som VLOOKUP. Funksjonen returnerer alltid sannsynligheten fra venstre kant av kurven (-infinity) til verdien av z.
Hva om du er interessert i sannsynligheten for å være større enn en viss størrelse? For å finne sjansen for å bli større enn (gjennomsnitt + 1 standardavvik), kan du starte med 100% og trekke muligheten for å være mindre enn (gjennomsnitt + 1 standardavvik). Dette ville være =100%-NORM.S.DIST(1,True). Siden 100% er det samme som 1, kan du forkorte formelen til =1-NORM.S.DIST(1,True). Eller du kan innse at kurven er symmetrisk og be om NORM.S.DIST (-1, True) for å få det samme svaret.
For deg som er så OCD som jeg er, kan jeg forsikre deg om at hvis =SUM(30.85,53.28,15.87)du ender med 100%. Jeg vet det fordi jeg sjekket det i regnearket.
Gå tilbake til figur 3 - du bør vite hvordan du beregner sannsynligheten fra to punkter z1 og z2. Trekk fra NORM.S.DIST (z2, True) -NORM.S.DIST (z1, True), så får du svaret. La oss vurdere det helt spesielle tilfellet der z1 er gjennomsnittet. Du prøver å finne ut sannsynligheten for at noen er mellom gjennomsnittet og +1,5 standardavvik fra gjennomsnittet, som illustrert i figur 6.
Hvilke av disse vil bruke sannsynligheten for området under kurven over ved hjelp av det du lærte fra figur 3?
-
=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) - Ingen av de ovennevnte
Hvordan gikk det? Forutsatt at du svarte A, B eller C, fikk du 100% på testen. Gratulerer. Det er som sagt ikke rakettvitenskap.
For de av dere som elsker snarveier, husk at det er 50% sannsynlighet for at noe vil være mindre enn eller lik gjennomsnittet. Når du ser = NORM.S.DIST (0, True), kan du øyeblikkelig tenke, "Åh - det er 50%!". Så svar B ovenfor kan skrives om som
=NORM.S.DIST(1.5,True)-50%
Men hvis du elsker snarveier, hater du å skrive 50% og vil forkorte den til .5:
=NORM.S.DIST(1.5,True)-.5
Kan du bruke det symmetriske motsatte av området under kurven? Ja, = .5-NORM.S.DIST (-1.5, True) vil gi deg det samme resultatet. Så quizen 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 ovennevnte
Forutsatt at du velger et svar, vil jeg gi deg full kreditt. Tross alt er det Excel. Det er fem måter å gjøre noe på, og jeg godtar ethvert svar som fungerer (vel, annet enn hardkoding = 0,433 i en celle).
For de av dere som fikk svaret på det siste spørsmålet riktig, slutte å lese. Alle andre trenger GAUSS:
Hva med GAUSS-funksjonen? GAUSS-funksjonen gir oss enda en måte å løse det spesifikke tilfellet der området går fra gjennomsnittet til et punkt over gjennomsnittet. I stedet for å bruke svarene ovenfor, kan du bruke =GAUSS(1.5).
Ja … de la til en funksjon for folk som ikke kan trekke 0,5 fra NORM.S.DIST!
Hvis du er som meg, spør du: "Seriøst? De kastet bort ressurser på å legge til denne funksjonen?" Vel, tilbake i Excel 2007, tok Excel-teamet en beslutning om å la oss lagre dokumenter i .ODS-format. Dette er regnearkformatet Open Document. Det er ikke et format som kontrolleres av Microsoft. Siden de tilbyr støtte for ODS, er Microsoft tvunget til å legge til alle funksjonene som Open Document Spreadsheet støtter. Tilsynelatende kunne ikke et flertall mennesker i Open Document Spreadsheet-konsortiet finne ut at svaret på min første quiz var A, så de la til en helt ny funksjon.
Jeg antar at Microsoft ikke var begeistret for å legge til støtte for funksjoner som lignet på andre funksjoner som allerede var i Excel. Jeg kan nesten forestille meg samtalen mellom den tekniske forfatteren som har til oppgave å skrive om GAUSS i Excel Help og prosjektlederen på Excel-teamet:
Forfatter: "Så, fortell meg om GAUSS"
PM: "Det er vanvittig. Ta =NORM.S.DISTog trekk fra 0,5. Jeg kan ikke tro at vi måtte legge til dette."
Forfatteren redigerte deretter redaksjonelle kommentarer og tilbød dette hjelpemnet:
Så - la meg tilby dette alternative hjelpemnet:
GAUSS (z) - Beregner sannsynligheten for at et medlem av en standard normalpopulasjon vil falle mellom gjennomsnittet og + z standardavvik fra gjennomsnittet.
- z Påkrevd. Antall standardavvik over gjennomsnittet. Vanligvis i området +0,01 til +3.
- Lagt til i Excel 2013 for å støtte folk som ikke kan trekke fra to tall.
- Ikke spesielt meningsfullt for negative verdier av Z. For å beregne sannsynligheten for at noe faller i området -1,5 til gjennomsnittet, bruk
=GAUSS(1.5). - Fungerer ikke i Excel 2010 og tidligere. Bruk i Excel 2010 og tidligere
=NORM.S.DIST(z,True)-0.5.
Der har du det … mer enn du noen gang ønsket å vite om GAUSS. Det er absolutt mer enn jeg noen gang ønsket å vite. For øvrig tilbyr mine Excel In Depth-bøker en fullstendig beskrivelse av alle de 452 funksjonene i Excel. Ta en titt på forrige utgave, Excel 2010 In Depth eller den nye Excel 2013 In Depth som skal utgis i november 2012.
