Beregning av konfidensintervall i Excel

Excel brukes til å utføre ulike statistiske oppgaver, en av dem er beregningen av konfidensintervallet, som brukes som den mest hensiktsmessige erstatningen for et punktestimat med en liten prøvestørrelse.

Vi vil med en gang merke oss at selve prosedyren for å beregne konfidensintervallet er ganske komplisert, men i Excel er det en rekke verktøy designet for å lette denne oppgaven. La oss ta en titt på dem.

Konfidensintervallberegning

Et konfidensintervall er nødvendig for å gi et intervallestimat til enkelte statiske data. Hovedformålet med denne operasjonen er å fjerne usikkerheten i punktestimatet.

Det er to metoder for å utføre denne oppgaven i Microsoft Excel:

• operatør TILLITSNORM – brukes i tilfeller der spredningen er kjent;
• operatør TRUST.STUDENTnår variansen er ukjent.

Nedenfor vil vi trinn for trinn analysere begge metodene i praksis.

Metode 1: TRUST.NORM-setning

Denne funksjonen ble først introdusert i arsenalet til programmet i Excel 2010-utgaven (før denne versjonen ble den erstattet av operatøren "TRISTET). Operatøren er inkludert i kategorien "statistisk".

Funksjonsformel TILLITSNORM ser slik ut:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Som vi kan se, har funksjonen tre argumenter:

• “Alpha” er en indikator på betydningsnivået som legges til grunn for beregningen. Konfidensnivået beregnes som følger:
  • 1-"Альфа". Dette uttrykket gjelder hvis verdien “Alpha” presentert som en koeffisient. For eksempel, 1-0,7 0,3 =, hvor 0,7=70%/100%.
  • (100-"Альфа")/100. Dette uttrykket vil bli brukt hvis vi vurderer konfidensnivået med verdien “Alpha” i prosent. For eksempel, (100-70) / 100 = 0,3.
• "Standardavvik" – henholdsvis standardavviket til den analyserte dataprøven.
• "Størrelse" er størrelsen på datautvalget.

OBS: For denne funksjonen er tilstedeværelsen av alle tre argumentene en forutsetning.

Operatør "TRISTET, som ble brukt i tidligere versjoner av programmet, inneholder de samme argumentene og utfører de samme funksjonene.

Funksjonsformel TILLITT som følger:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

Det er ingen forskjeller i selve formelen, bare navnet på operatøren er annerledes. I Excel 2010 og senere utgaver er denne operatøren i kompatibilitetskategorien. I eldre versjoner av programmet er det plassert i delen for statiske funksjoner.

Konfidensintervallgrensen bestemmes av følgende formel:

X+(-)ДОВЕРИТ.НОРМ

hvor Х er gjennomsnittsverdien over det angitte området.

La oss nå se hvordan du bruker disse formlene i praksis. Så vi har en tabell med ulike data fra 10 målinger som er tatt. I dette tilfellet er standardavviket til datasettet 8.

Vår oppgave er å oppnå verdien av konfidensintervallet med et 95 % konfidensnivå.

1. Først av alt, velg en celle for å vise resultatet. Så klikker vi på knappen "Sett inn funksjon" (til venstre for formellinjen).
2. Funksjonsveiviser-vinduet åpnes. Ved å klikke på gjeldende funksjonskategori utvider du listen og klikker på linjen i den "Statistisk".
3. I den foreslåtte listen klikker du på operatøren «TILLITSNORM», Og trykk deretter OK.
4. Vi vil se et vindu med innstillingene for funksjonsargumentene, fyll som vi trykker på knappen OK.
   • i felten “Alpha” angi betydningsnivået. Vår oppgave forutsetter et 95 % konfidensnivå. Ved å erstatte denne verdien i beregningsformelen, som vi vurderte ovenfor, får vi uttrykket: (100-95)/100. Vi skriver det i argumentfeltet (eller du kan umiddelbart skrive resultatet av beregningen lik 0,05).
   • i felten «std_off» i henhold til forholdene våre skriver vi tallet 8.
   • i «Størrelse»-feltet angir du antall elementer som skal undersøkes. I vårt tilfelle ble det tatt 10 målinger, så vi skriver tallet 10.
5. For å unngå å måtte rekonfigurere funksjonen når data endres, kan du automatisere den. Til dette bruker vi funksjonen "KRYSS AV". Sett pekeren i inndataområdet til argumentinformasjonen "Størrelse", klikk deretter på trekantikonet på venstre side av formellinjen og klikk på elementet "Flere funksjoner ...".
6. Som et resultat vil et annet vindu i funksjonsveiviseren åpnes. Ved å velge en kategori "Statistisk", klikk på funksjonen "KRYSS AV", deretter OK.
7. Skjermen vil vise et annet vindu med innstillingene for argumentene til funksjonen, som brukes til å bestemme antall celler i et gitt område som inneholder numeriske data.

   Funksjonsformel KRYSS AV det er skrevet slik: =СЧЁТ(Значение1;Значение2;...).

   Antall tilgjengelige argumenter for denne funksjonen kan være opptil 255. Her kan du skrive enten spesifikke tall, eller celleadresser, eller celleområder. Vi vil bruke det siste alternativet. For å gjøre dette, klikk på informasjonsinntastingsområdet for det første argumentet, og hold nede venstre museknapp, velg alle cellene i en av kolonnene i tabellen vår (ikke teller overskriften), og trykk deretter på knappen OK.

8. Som et resultat av handlingene som er utført, vil resultatet av beregninger for operatøren vises i den valgte cellen TILLITSNORM. I vårt problem viste det seg at verdien var lik 4,9583603.
9. Men dette er ennå ikke det endelige resultatet i vår oppgave. Deretter må du beregne gjennomsnittsverdien over et gitt intervall. For å gjøre dette, må du bruke funksjonen "HJERTE"A som utfører oppgaven med å beregne gjennomsnittet over et spesifisert dataområde.

   Operatorformelen er skrevet slik: =СРЗНАЧ(число1;число2;...).

   Velg cellen der vi planlegger å sette inn funksjonen og trykk på knappen "Sett inn funksjon".

10. I kategori "Statistisk" velg en kjedelig operatør "HJERTE" og klikk OK.
11. I funksjonsargumenter i argumentverdi "Antall" spesifiser området, som inkluderer alle celler med verdiene for alle målinger. Så klikker vi GREIT.
12. Som et resultat av handlingene som er utført, vil gjennomsnittsverdien automatisk beregnes og vises i cellen med den nylig innsatte funksjonen.
13. Nå må vi beregne CI-grensene (konfidensintervall). La oss starte med å beregne verdien av høyre kantlinje. Vi velger cellen der vi vil vise resultatet, og legger til resultatene som er oppnådd ved å bruke operatørene "HJERTE" og "TILLITSNORMER”. I vårt tilfelle ser formelen slik ut: A14+A16. Etter å ha skrevet det, trykk Enter.
14. Som et resultat vil beregningen utføres og resultatet vises umiddelbart i cellen med formelen.
15. Deretter, på lignende måte, utfører vi beregningen for å få verdien av venstre kant av CI. Bare i dette tilfellet verdien av resultatet "TILLITSNORMER” du trenger ikke å legge til, men trekke fra resultatet oppnådd ved hjelp av operatoren "HJERTE". I vårt tilfelle ser formelen slik ut: =A16-A14.
16. Etter å ha trykket på Enter, får vi resultatet i den gitte cellen med formelen.

OBS: I avsnittene ovenfor prøvde vi å beskrive så detaljert som mulig alle trinnene og hver funksjon som ble brukt. Imidlertid kan alle de foreskrevne formlene skrives sammen, som en del av en stor:

• For å bestemme den høyre grensen til CI, vil den generelle formelen se slik ut:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• På samme måte, for den venstre grensen, bare i stedet for et pluss, må du sette et minus:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Metode 2: TRUST.STUDENT-operatør

La oss nå bli kjent med den andre operatoren for å bestemme konfidensintervallet − TRUST.STUDENT. Denne funksjonen ble introdusert i programmet relativt nylig, med start fra versjonen av Excel 2010, og er rettet mot å bestemme CI for det valgte datasettet ved hjelp av studentens distribusjon, med en ukjent varians.

Funksjonsformel TRUST.STUDENT som følger:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

La oss analysere bruken av denne operatoren på eksemplet med samme tabell. Først nå vet vi ikke standardavviket i henhold til forholdene for problemet.

1. Velg først cellen der vi planlegger å vise resultatet. Klikk deretter på ikonet "Sett inn funksjon" (til venstre for formellinjen).
2. Det allerede velkjente funksjonsveiviservinduet åpnes. Velg en kategori "Statistisk", og klikk deretter på operatøren fra den foreslåtte listen over funksjoner «PÅLITERT STUDENT», deretter - OK.
3. I neste vindu må vi sette opp funksjonsargumentene:
   • på “Alpha” som i den første metoden, spesifiser verdien 0,05 (eller "100-95)/100").
   • La oss gå videre til argumentasjonen. «std_off». Fordi i henhold til betingelsene for problemet er verdien ukjent for oss, må vi gjøre de riktige beregningene, der operatøren "STDEV.B". Klikk på knappen legg til funksjon og deretter på elementet "Flere funksjoner ...".
   • I det neste vinduet i funksjonsveiviseren velger du operatøren "STDEV.B" i kategori "Statistisk" og klikk OK.
   • Vi kommer inn i innstillingsvinduet for funksjonsargumenter, hvis formel ser slik ut: =СТАНДОТКЛОН.В(число1;число2;...). Som det første argumentet spesifiserer vi et område som inkluderer alle cellene i «Verdi»-kolonnen (ikke teller overskriften).
   • Nå må du gå tilbake til vinduet med funksjonsargumentene "TRUST.STUDENT». For å gjøre dette, klikk på inskripsjonen med samme navn i formelinntastingsfeltet.
   • La oss nå gå videre til det siste argumentet "Størrelse". Som i den første metoden, her kan du enten spesifisere et celleområde, eller sette inn operatoren "KRYSS AV". Vi velger det siste alternativet.
   • Når alle argumentene er fylt ut, klikker du på knappen OK.
4. Den valgte cellen vil vise verdien av konfidensintervallet i henhold til parametrene vi spesifiserte.
5. Deretter må vi beregne verdiene til CI-grensene. Og for dette må du få gjennomsnittsverdien for det valgte området. For å gjøre dette bruker vi igjen funksjonen "HJERTE". Algoritmen for handlinger ligner den som er beskrevet i den første metoden.
6. Etter å ha mottatt verdien "HJERTE", kan du begynne å beregne CI-grensene. Formlene i seg selv er ikke forskjellige fra de som brukes med "TILLITSNORMER”:
   • Høyre kantlinje CI=GJENNOMSNITT+STUDENTTILLIT
   • Venstre bundet CI=GJENNOMSNITTLIG STUDENTTILLIT

konklusjonen

Excels arsenal av verktøy er utrolig stort, og sammen med vanlige funksjoner tilbyr programmet en lang rekke spesialfunksjoner som vil gjøre arbeidet med data mye enklere. Kanskje trinnene beskrevet ovenfor kan virke kompliserte for noen brukere ved første øyekast. Men etter en detaljert studie av problemet og handlingssekvensen, vil alt bli mye lettere.