Beräkna konfidensintervall i Excel

Excel används för att utföra olika statistiska uppgifter, varav en är beräkningen av konfidensintervallet, som används som den lämpligaste ersättningen för en punktuppskattning med en liten urvalsstorlek.

Vi vill genast notera att själva proceduren för att beräkna konfidensintervallet är ganska komplicerad, men i Excel finns det ett antal verktyg som är utformade för att underlätta denna uppgift. Låt oss ta en titt på dem.

Beräkning av konfidensintervall

Ett konfidensintervall behövs för att ge en intervalluppskattning för vissa statiska data. Huvudsyftet med denna operation är att ta bort osäkerheterna i punktuppskattningen.

Det finns två metoder för att utföra den här uppgiften i Microsoft Excel:

• Operatör FÖRTROENDE NORM – används i fall där spridningen är känd;
• Operatör TRUST.STUDENTnär variansen är okänd.

Nedan kommer vi steg för steg att analysera båda metoderna i praktiken.

Metod 1: TRUST.NORM-sats

Denna funktion introducerades först i programmets arsenal i Excel 2010-utgåvan (före denna version ersattes den av operatören "BETRODD"). Operatören ingår i kategorin "statistisk".

Funktionsformel FÖRTROENDE NORM ser ut så här:

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

Som vi kan se har funktionen tre argument:

• "Alfa" är en indikator på signifikansnivån som ligger till grund för beräkningen. Konfidensnivån beräknas enligt följande:
  • 1-"Альфа". Detta uttryck är tillämpligt om värdet "Alfa" presenteras som en koefficient. Till exempel, 1-0,7 0,3 =där 0,7=70%/100%.
  • (100-"Альфа")/100. Detta uttryck kommer att tillämpas om vi tar hänsyn till konfidensnivån med värdet "Alfa" i procent. Till exempel, (100-70) / 100 = 0,3.
• "Standardavvikelse" – respektive standardavvikelsen för det analyserade dataprovet.
• "Storlek" är storleken på dataprovet.

Notera: För denna funktion är närvaron av alla tre argument en förutsättning.

Operatör "BETRODD", som användes i tidigare versioner av programmet, innehåller samma argument och utför samma funktioner.

Funktionsformel BETRODD enligt följande:

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

Det finns inga skillnader i själva formeln, bara namnet på operatorn är annorlunda. I Excel 2010 och senare utgåvor är denna operatör i kategorin Kompatibilitet. I äldre versioner av programmet finns det i avsnittet om statiska funktioner.

Konfidensintervallsgränsen bestäms av följande formel:

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

var Х är medelvärdet över det angivna intervallet.

Låt oss nu se hur man tillämpar dessa formler i praktiken. Så vi har en tabell med olika data från 10 gjorda mätningar. I det här fallet är standardavvikelsen för datamängden 8.

Vår uppgift är att få fram värdet av konfidensintervallet med en 95% konfidensnivå.

1. Först av allt, välj en cell för att visa resultatet. Sedan klickar vi på knappen "Infoga funktion" (till vänster om formelfältet).
2. Fönstret Funktionsguiden öppnas. Genom att klicka på den aktuella kategorin av funktioner, expandera listan och klicka på raden i den "Statistisk".
3. Klicka på operatören i den föreslagna listan "FÖRTTROENDES NORM", Tryck sedan på OK.
4. Vi kommer att se ett fönster med inställningarna för funktionsargumenten, fylla som vi trycker på knappen OK.
   • på fältet "Alfa" ange signifikansnivån. Vår uppgift förutsätter en 95% konfidensnivå. Genom att ersätta detta värde i beräkningsformeln, som vi övervägde ovan, får vi uttrycket: (100-95)/100. Vi skriver det i argumentfältet (eller så kan du genast skriva resultatet av beräkningen lika med 0,05).
   • på fältet "std_off" enligt våra förutsättningar skriver vi siffran 8.
   • i fältet "Storlek" anger du antalet element som ska undersökas. I vårt fall gjordes 10 mätningar, så vi skriver siffran 10.
5. För att undvika att behöva konfigurera om funktionen när data ändras kan du automatisera den. För detta använder vi funktionen "KOLLA UPP". Sätt pekaren i inmatningsområdet för argumentinformationen "Storlek", klicka sedan på triangelikonen till vänster i formelfältet och klicka på objektet "Flera funktioner…".
6. Som ett resultat kommer ett annat fönster i funktionsguiden att öppnas. Genom att välja en kategori "Statistisk", klicka på funktionen "KOLLA UPP", då OK.
7. Skärmen kommer att visa ett annat fönster med inställningarna för funktionens argument, som används för att bestämma antalet celler i ett givet intervall som innehåller numeriska data.

   Funktionsformel CHECK det är skrivet så här: =СЧЁТ(Значение1;Значение2;...).

   Antalet tillgängliga argument för denna funktion kan vara upp till 255. Här kan du skriva antingen specifika siffror eller celladresser eller cellintervall. Vi kommer att använda det sista alternativet. För att göra detta, klicka på informationsinmatningsområdet för det första argumentet, håll sedan ned vänster musknapp, välj alla celler i en av kolumnerna i vår tabell (räknas inte rubriken) och tryck sedan på knappen OK.

8. Som ett resultat av de åtgärder som vidtagits kommer resultatet av beräkningar för operatören att visas i den valda cellen FÖRTROENDE NORM. I vårt problem visade sig dess värde vara lika med 4,9583603.
9. Men detta är ännu inte slutresultatet i vår uppgift. Därefter måste du beräkna medelvärdet över ett givet intervall. För att göra detta måste du använda funktionen "HJÄRTA"A som utför uppgiften att beräkna medelvärdet över ett specificerat dataintervall.

   Operatorformeln är skriven så här: =СРЗНАЧ(число1;число2;...).

   Välj cellen där vi planerar att infoga funktionen och tryck på knappen "Infoga funktion".

10. I kategorin "Statistisk" välj en tråkig operatör "HJÄRTA" och klicka OK.
11. I funktionsargument i argumentvärde "Siffra" ange intervallet, som inkluderar alla celler med värdena för alla mätningar. Sedan klickar vi OKEJ.
12. Som ett resultat av de åtgärder som vidtagits kommer medelvärdet automatiskt att beräknas och visas i cellen med den nyligen infogade funktionen.
13. Nu måste vi beräkna CI-gränserna (konfidensintervall). Låt oss börja med att beräkna värdet på den högra kanten. Vi markerar cellen där vi vill visa resultatet och lägger till resultaten med hjälp av operatorerna "HJÄRTA" och "FÖRTROENDES NORMER”. I vårt fall ser formeln ut så här: A14+A16. När du har skrivit det, tryck ange.
14. Som ett resultat kommer beräkningen att utföras och resultatet kommer omedelbart att visas i cellen med formeln.
15. Sedan, på ett liknande sätt, utför vi beräkningen för att erhålla värdet på den vänstra gränsen för CI. Endast i detta fall värdet av resultatet "FÖRTROENDES NORMER” du behöver inte lägga till, utan subtrahera från resultatet som erhålls med operatorn "HJÄRTA". I vårt fall ser formeln ut så här: =A16-A14.
16. Efter att ha tryckt på Enter får vi resultatet i den givna cellen med formeln.

Notera: I styckena ovan försökte vi så detaljerat som möjligt beskriva alla steg och varje funktion som användes. Men alla de föreskrivna formlerna kan skrivas tillsammans, som en del av en stor:

• För att bestämma den högra gränsen för CI kommer den allmänna formeln att se ut så här:

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

• På samma sätt, för den vänstra gränsen, bara istället för ett plus, måste du sätta ett minus:

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

Metod 2: TRUST.STUDENT operator

Låt oss nu bekanta oss med den andra operatorn för att bestämma konfidensintervallet − TRUST.STUDENT. Den här funktionen introducerades i programmet relativt nyligen, från och med versionen av Excel 2010, och syftar till att bestämma CI för den valda datamängden med hjälp av studentens distribution, med en okänd varians.

Funktionsformel TRUST.STUDENT enligt följande:

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

Låt oss analysera tillämpningen av denna operator på exemplet i samma tabell. Först nu känner vi inte till standardavvikelsen enligt villkoren för problemet.

1. Välj först cellen där vi planerar att visa resultatet. Klicka sedan på ikonen "Infoga funktion" (till vänster om formelfältet).
2. Det redan välkända fönstret Funktionsguide öppnas. Välj en kategori "Statistisk", klicka sedan på operatorn från den föreslagna listan med funktioner "BETROLIG STUDENT", sedan – OK.
3. I nästa fönster måste vi ställa in funktionsargumenten:
   • I "Alfa" som i den första metoden, ange värdet 0,05 (eller "100-95)/100").
   • Låt oss gå vidare till argumentet. "std_off". Eftersom enligt villkoren för problemet är dess värde okänt för oss, måste vi göra lämpliga beräkningar, där operatören "STDEV.B”. Klicka på knappen Lägg till funktion och sedan på objektet "Flera funktioner…".
   • I nästa fönster i funktionsguiden väljer du operatören "STDEV.B” i kategori "Statistisk" och klicka OK.
   • Vi kommer in i inställningsfönstret för funktionsargument, vars formel ser ut så här: =СТАНДОТКЛОН.В(число1;число2;...). Som det första argumentet anger vi ett intervall som inkluderar alla celler i kolumnen "Värde" (räknas inte rubriken).
   • Nu måste du gå tillbaka till fönstret med funktionsargumenten "TRUST.STUDENT”. För att göra detta, klicka på inskriptionen med samma namn i formelinmatningsfältet.
   • Låt oss nu gå vidare till det sista argumentet "Storlek". Som i den första metoden kan du här antingen helt enkelt ange ett cellområde eller infoga operatorn "KONTROLLERA". Vi väljer det sista alternativet.
   • När alla argument är ifyllda klickar du på knappen OK.
4. Den valda cellen kommer att visa värdet för konfidensintervallet enligt de parametrar vi angav.
5. Därefter måste vi beräkna värdena för CI-gränserna. Och för detta måste du få medelvärdet för det valda intervallet. För att göra detta använder vi återigen funktionen "HJÄRTA". Algoritmen för åtgärder liknar den som beskrivs i den första metoden.
6. Efter att ha fått värdet "HJÄRTA", kan du börja beräkna CI-gränserna. Formlerna i sig skiljer sig inte från de som används med "FÖRTROENDES NORMER”:
   • Höger kant CI=MEDEL+ELEVENS FÖRTROENDE
   • Vänster bunden CI=MEDELSTUDENT FÖRTROENDE

Slutsats

Excels arsenal av verktyg är otroligt stor, och tillsammans med vanliga funktioner erbjuder programmet en mängd olika specialfunktioner som kommer att göra arbetet med data mycket enklare. Kanske kan stegen som beskrivs ovan verka komplicerade för vissa användare vid första anblicken. Men efter en detaljerad studie av frågan och sekvensen av åtgärder kommer allt att bli mycket lättare.