Beregning af konfidensinterval i Excel

Excel bruges til at udføre forskellige statistiske opgaver, hvoraf en er beregningen af ​​konfidensintervallet, som bruges som den mest passende erstatning for et punktestimat med en lille stikprøvestørrelse.

Vi vil med det samme bemærke, at selve proceduren til beregning af konfidensintervallet er ret kompliceret, men i Excel er der en række værktøjer designet til at lette denne opgave. Lad os tage et kig på dem.

Konfidensintervalberegning

Et konfidensinterval er nødvendigt for at give et intervalestimat til nogle statiske data. Hovedformålet med denne operation er at fjerne usikkerheden ved punktestimatet.

Der er to metoder til at udføre denne opgave i Microsoft Excel:

• Operatør TILLIDSNORM – anvendes i tilfælde, hvor spredningen er kendt;
• Operatør TRUST.STUDENTnår variansen er ukendt.

Nedenfor vil vi trin for trin analysere begge metoder i praksis.

Metode 1: TRUST.NORM-sætning

Denne funktion blev først introduceret i programmets arsenal i Excel 2010-udgaven (før denne version blev den erstattet af operatøren "TILTROLIGT”). Operatøren er inkluderet i kategorien "statistisk".

Funktionsformel TILLIDSNORM ser sådan ud:

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

Som vi kan se, har funktionen tre argumenter:

• ”Alpha” er en indikator for signifikansniveauet, som lægges til grund for beregningen. Konfidensniveauet beregnes som følger:
  • 1-"Альфа". Dette udtryk er anvendeligt, hvis værdien ”Alpha” præsenteret som en koefficient. For eksempel, 1-0,7 0,3 =, hvor 0,7=70%/100%.
  • (100-"Альфа")/100. Dette udtryk vil blive anvendt, hvis vi betragter konfidensniveauet med værdien ”Alpha” i procenter. For eksempel, (100-70) / 100 = 0,3.
• "Standardafvigelse" – henholdsvis standardafvigelsen for den analyserede dataprøve.
• "Størrelse" er størrelsen af ​​dataprøven.

Bemærk: For denne funktion er tilstedeværelsen af ​​alle tre argumenter en forudsætning.

Operatør "TILTROLIGT”, som blev brugt i tidligere versioner af programmet, indeholder de samme argumenter og udfører de samme funktioner.

Funktionsformel TILLID som følger:

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

Der er ingen forskelle i selve formlen, kun navnet på operatøren er anderledes. I Excel 2010 og senere udgaver er denne operatør i kompatibilitetskategorien. I ældre versioner af programmet er det placeret i afsnittet om statiske funktioner.

Konfidensintervalgrænsen bestemmes af følgende formel:

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

hvor Х er gennemsnitsværdien over det angivne interval.

Lad os nu se, hvordan man anvender disse formler i praksis. Så vi har en tabel med forskellige data fra 10 målinger. I dette tilfælde er standardafvigelsen for datasættet 8.

Vores opgave er at opnå værdien af ​​konfidensintervallet med et 95 % konfidensniveau.

1. Først og fremmest skal du vælge en celle for at vise resultatet. Så klikker vi på knappen "Indsæt funktion" (til venstre for formellinjen).
2. Vinduet Funktionsguide åbnes. Ved at klikke på den aktuelle kategori af funktioner, udvid listen og klik på linjen i den "Statistisk".
3. Klik på operatøren i den foreslåede liste "TILLIDS NORM", Og tryk derefter på OK.
4. Vi vil se et vindue med indstillingerne af funktionsargumenterne, udfylde som vi trykker på knappen OK.
   • i marken ”Alpha” angive betydningsniveauet. Vores opgave forudsætter et 95% konfidensniveau. Ved at erstatte denne værdi i beregningsformlen, som vi overvejede ovenfor, får vi udtrykket: (100-95)/100. Vi skriver det i argumentfeltet (eller du kan straks skrive resultatet af beregningen lig med 0,05).
   • i marken "std_off" i henhold til vores forhold skriver vi tallet 8.
   • i feltet "Størrelse" skal du angive antallet af elementer, der skal undersøges. I vores tilfælde blev der taget 10 målinger, så vi skriver tallet 10.
5. For at undgå at skulle omkonfigurere funktionen, når data ændres, kan du automatisere den. Til dette bruger vi funktionen "KONTROLLERE". Sæt markøren i inputområdet for argumentinformationen "Størrelse", klik derefter på trekantikonet i venstre side af formellinjen, og klik på elementet "Flere funktioner...".
6. Som følge heraf åbnes et andet vindue i funktionsguiden. Ved at vælge en kategori "Statistisk", klik på funktionen "KONTROLLERE", derefter OK.
7. Skærmen vil vise et andet vindue med indstillingerne for funktionens argumenter, som bruges til at bestemme antallet af celler i et givet område, der indeholder numeriske data.

   Funktionsformel KONTROLLERE det er skrevet sådan: =СЧЁТ(Значение1;Значение2;...).

   Antallet af tilgængelige argumenter for denne funktion kan være op til 255. Her kan du skrive enten specifikke tal eller celleadresser eller celleområder. Vi vil bruge den sidste mulighed. For at gøre dette skal du klikke på informationsindtastningsområdet for det første argument, og derefter, hold venstre museknap nede, vælg alle cellerne i en af ​​kolonnerne i vores tabel (ikke tæller overskriften), og tryk derefter på knappen OK.

8. Som et resultat af de udførte handlinger vil resultatet af beregninger for operatøren blive vist i den valgte celle TILLIDSNORM. I vores problem viste dens værdi sig at være lig med 4,9583603.
9. Men det er endnu ikke det endelige resultat i vores opgave. Dernæst skal du beregne gennemsnitsværdien over et givet interval. For at gøre dette skal du bruge funktionen "HJERTE"A, der udfører opgaven med at beregne gennemsnittet over et specificeret datainterval.

   Operatorformlen er skrevet sådan: =СРЗНАЧ(число1;число2;...).

   Vælg den celle, hvor vi planlægger at indsætte funktionen, og tryk på knappen "Indsæt funktion".

10. I kategori "Statistisk" vælg en kedelig operatør "HJERTE" og klik OK.
11. I funktionsargumenter i argumentværdi "Nummer" angiv området, som omfatter alle celler med værdierne for alle målinger. Så klikker vi OKAY.
12. Som et resultat af de udførte handlinger vil gennemsnitsværdien automatisk blive beregnet og vist i cellen med den nyligt indsatte funktion.
13. Nu skal vi beregne CI-grænserne (konfidensinterval). Lad os starte med at beregne værdien af ​​den højre kant. Vi vælger den celle, hvor vi vil vise resultatet, og udfører tilføjelsen af ​​de opnåede resultater ved hjælp af operatorerne "HJERTE" og "TILLIDSNORMER”. I vores tilfælde ser formlen sådan ud: A14+A16. Når du har skrevet det, skal du trykke på Indtast.
14. Som et resultat vil beregningen blive udført, og resultatet vil straks blive vist i cellen med formlen.
15. Derefter udfører vi på lignende måde beregningen for at opnå værdien af ​​venstre kant af CI. Kun i dette tilfælde værdien af ​​resultatet "TILLIDSNORMER” du behøver ikke at tilføje, men trække fra resultatet opnået ved hjælp af operatoren "HJERTE". I vores tilfælde ser formlen sådan ud: =A16-A14.
16. Efter at have trykket på Enter, får vi resultatet i den givne celle med formlen.

Bemærk: I ovenstående afsnit forsøgte vi at beskrive så detaljeret som muligt alle trinene og hver funktion, der blev brugt. Alle de foreskrevne formler kan dog skrives sammen som en del af én stor:

• For at bestemme den højre kant af CI vil den generelle formel se sådan ud:

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

• På samme måde, for den venstre grænse, skal du kun i stedet for et plus sætte et minus:

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

Metode 2: TRUST.STUDENT operator

Lad os nu blive bekendt med den anden operator til at bestemme konfidensintervallet − TRUST.STUDENT. Denne funktion blev introduceret i programmet relativt for nylig, startende fra versionen af ​​Excel 2010, og har til formål at bestemme CI for det valgte datasæt ved hjælp af Elevens fordeling, med en ukendt varians.

Funktionsformel TRUST.STUDENT som følger:

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

Lad os analysere anvendelsen af ​​denne operator på eksemplet med den samme tabel. Først nu kender vi ikke standardafvigelsen i henhold til problemets betingelser.

1. Først skal du vælge den celle, hvor vi planlægger at vise resultatet. Klik derefter på ikonet "Indsæt funktion" (til venstre for formellinjen).
2. Det allerede velkendte funktionsguidevindue åbnes. Vælg en kategori "Statistisk", og klik derefter på operatoren fra den foreslåede liste over funktioner "PÅTROLIG STUDENT", derefter - OK.
3. I det næste vindue skal vi opsætte funktionsargumenterne:
   • I ”Alpha” som i den første metode, angiv værdien 0,05 (eller "100-95)/100").
   • Lad os gå videre til argumentet. "std_off". Fordi i henhold til betingelserne for problemet er dets værdi ukendt for os, skal vi foretage de passende beregninger, hvor operatøren "STDEV.B". Klik på knappen Tilføj funktion og derefter på elementet "Flere funktioner...".
   • I det næste vindue i funktionsguiden skal du vælge operatøren "STDEV.B" i kategori "Statistisk" og klik OK.
   • Vi kommer ind i indstillingsvinduet for funktionsargumenter, hvis formel ser sådan ud: =СТАНДОТКЛОН.В(число1;число2;...). Som det første argument angiver vi et område, der inkluderer alle celler i kolonnen "Værdi" (overskriften tæller ikke med).
   • Nu skal du gå tilbage til vinduet med funktionsargumenterne "TRUST.STUDENT”. For at gøre dette skal du klikke på indskriften med samme navn i formelindtastningsfeltet.
   • Lad os nu gå videre til det sidste argument "Størrelse". Som i den første metode kan du her enten blot angive et celleområde eller indsætte operatoren "KONTROLLERE". Vi vælger den sidste mulighed.
   • Når alle argumenterne er udfyldt, skal du klikke på knappen OK.
4. Den valgte celle vil vise værdien af ​​konfidensintervallet i henhold til de parametre, vi har angivet.
5. Dernæst skal vi beregne værdierne af CI-grænserne. Og for dette skal du få den gennemsnitlige værdi for det valgte område. For at gøre dette anvender vi igen funktionen "HJERTE". Algoritmen for handlinger ligner den, der er beskrevet i den første metode.
6. Efter at have modtaget værdien "HJERTE", kan du begynde at beregne CI-grænserne. Selve formlerne er ikke forskellige fra dem, der bruges med "TILLIDSNORMER”:
   • Højre kant CI=GENNEMSNIT+STUDENTTILLID
   • Venstre bundet CI=Gennemsnitlig elevtillid

Konklusion

Excels arsenal af værktøjer er utroligt stort, og sammen med almindelige funktioner tilbyder programmet en lang række specialfunktioner, der vil gøre arbejdet med data meget lettere. Måske kan de ovenfor beskrevne trin virke komplicerede for nogle brugere ved første øjekast. Men efter en detaljeret undersøgelse af spørgsmålet og rækkefølgen af ​​handlinger bliver alt meget lettere.