Betrouwbaarheidsinterval berekenen in Excel

Excel wordt gebruikt om verschillende statistische taken uit te voeren, waaronder de berekening van het betrouwbaarheidsinterval, dat wordt gebruikt als de meest geschikte vervanging voor een puntschatting met een kleine steekproefomvang.

We willen meteen opmerken dat de procedure voor het berekenen van het betrouwbaarheidsinterval nogal ingewikkeld is, maar in Excel zijn er een aantal hulpmiddelen die zijn ontworpen om deze taak te vergemakkelijken. Laten we ze eens bekijken.

Berekening van het betrouwbaarheidsinterval

Een betrouwbaarheidsinterval is nodig om een ​​intervalschatting te kunnen geven aan sommige statische gegevens. Het belangrijkste doel van deze bewerking is om de onzekerheden van de puntschatting weg te nemen.

Er zijn twee methoden om deze taak in Microsoft Excel uit te voeren:

• Operator VERTROUWEN NORM – gebruikt in gevallen waar de verspreiding bekend is;
• Operator VERTROUWEN.STUDENTwanneer de variantie niet bekend is.

Hieronder zullen we stap voor stap beide methoden in de praktijk analyseren.

Methode 1: TRUST.NORM-instructie

Deze functie werd voor het eerst geïntroduceerd in het arsenaal van het programma in de Excel 2010-editie (vóór deze versie werd deze vervangen door de operator "VERTROUWD”). De operator is opgenomen in de categorie "statistisch".

Functie Formule VERTROUWEN NORM ziet eruit als:

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

Zoals we kunnen zien, heeft de functie drie argumenten:

• "Alpha" is een indicator van het significantieniveau, dat als basis voor de berekening wordt genomen. Het betrouwbaarheidsniveau wordt als volgt berekend:
  • 1-"Альфа". Deze uitdrukking is van toepassing als de waarde "Alpha" gepresenteerd als een coëfficiënt. Bijvoorbeeld, 1-0,7 0,3 =, waarbij 0,7=70%/100%.
  • (100-"Альфа")/100. Deze uitdrukking wordt toegepast als we kijken naar het betrouwbaarheidsniveau met de waarde "Alpha" in procenten. Bijvoorbeeld, (100-70) / 100 = 0,3.
• "Standaardafwijking" – respectievelijk de standaarddeviatie van het geanalyseerde gegevensmonster.
• "Maat" is de grootte van de gegevenssteekproef.

Opmerking: Voor deze functie is de aanwezigheid van alle drie de argumenten een vereiste.

bediener “VERTROUWD”, die in eerdere versies van het programma werd gebruikt, bevat dezelfde argumenten en voert dezelfde functies uit.

Functie Formule VERTROUWD als volgt:

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

Er zijn geen verschillen in de formule zelf, alleen de naam van de operator is anders. In Excel 2010 en latere edities bevindt deze operator zich in de categorie Compatibiliteit. In oudere versies van het programma bevindt het zich in de sectie statische functies.

De grens van het betrouwbaarheidsinterval wordt bepaald door de volgende formule:

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

WAAR Х is de gemiddelde waarde over het opgegeven bereik.

Laten we nu eens kijken hoe we deze formules in de praktijk kunnen toepassen. We hebben dus een tabel met verschillende gegevens van 10 uitgevoerde metingen. In dit geval is de standaarddeviatie van de dataset 8.

Het is onze taak om de waarde van het betrouwbaarheidsinterval te verkrijgen met een betrouwbaarheidsniveau van 95%.

1. Selecteer eerst een cel om het resultaat weer te geven. Dan klikken we op de knop “Functie invoegen” (links van de formulebalk).
2. Het venster Functiewizard wordt geopend. Door op de huidige categorie functies te klikken, vouwt u de lijst uit en klikt u op de regel erin “Statistisch”.
3. Klik in de voorgestelde lijst op de operator “VERTROUWENNORM”, Druk dan op OK.
4. We zullen een venster zien met de instellingen van de functieargumenten, waarin we op de knop drukken OK.
   • in het veld "Alpha" het significantieniveau aangeven. Onze taak gaat uit van een betrouwbaarheidsniveau van 95%. Door deze waarde in te vullen in de berekeningsformule, die we hierboven hebben overwogen, krijgen we de uitdrukking: (100-95)/100. We schrijven het in het argumentveld (of je kunt het resultaat van de berekening meteen gelijk aan 0,05) schrijven.
   • in het veld "std_off" volgens onze voorwaarden schrijven we het getal 8.
   • specificeer in het veld "Grootte" het aantal elementen dat moet worden onderzocht. In ons geval zijn er 10 metingen gedaan, dus schrijven we het getal 10.
5. Om te voorkomen dat u de functie opnieuw moet configureren wanneer gegevens veranderen, kunt u deze automatiseren. Hiervoor gebruiken we de functie “CONTROLEREN". Plaats de aanwijzer in het invoergebied van de argumentinformatie "Maat", klik vervolgens op het driehoekje aan de linkerkant van de formulebalk en klik op het item "Meer eigenschappen…".
6. Als gevolg hiervan wordt een ander venster van de Functiewizard geopend. Door een categorie te kiezen “Statistisch”, klik op de functie “CONTROLEREN", dan ok.
7. Het scherm toont een ander venster met de instellingen van de argumenten van de functie, die wordt gebruikt om het aantal cellen in een bepaald bereik te bepalen dat numerieke gegevens bevat.

   Functie Formule BEKIJK het is zo geschreven: =СЧЁТ(Значение1;Значение2;...).

   Het aantal beschikbare argumenten voor deze functie kan tot 255 zijn. Hier kunt u specifieke getallen, celadressen of celbereiken schrijven. We zullen de laatste optie gebruiken. Om dit te doen, klikt u op het informatie-invoergebied voor het eerste argument, houdt u de linkermuisknop ingedrukt, selecteert u alle cellen van een van de kolommen van onze tabel (de kop niet meegerekend) en drukt u vervolgens op de knop OK.

8. Als resultaat van de ondernomen acties wordt het resultaat van berekeningen voor de operator weergegeven in de geselecteerde cel VERTROUWEN NORM. In ons probleem bleek de waarde gelijk te zijn aan 4,9583603.
9. Maar dit is nog niet het eindresultaat van onze taak. Vervolgens moet u de gemiddelde waarde over een bepaald interval berekenen. Om dit te doen, moet u de functie "HART"A die de taak uitvoert om het gemiddelde over een gespecificeerd gegevensbereik te berekenen.

   De operatorformule is als volgt geschreven: =СРЗНАЧ(число1;число2;...).

   Selecteer de cel waarin we de functie willen invoegen en druk op de knop “Functie invoegen”.

10. In categorie “Statistisch” kies een saaie operator "HART" en klik op OK.
11. In functieargumenten in argumentwaarde "Nummer" specificeer het bereik, dat alle cellen omvat met de waarden van alle metingen. Dan klikken we OKE.
12. Als resultaat van de ondernomen acties wordt de gemiddelde waarde automatisch berekend en weergegeven in de cel met de nieuw ingevoegde functie.
13. Nu moeten we de CI-grenzen (betrouwbaarheidsinterval) berekenen. Laten we beginnen met het berekenen van de waarde van de rechterrand. We selecteren de cel waar we het resultaat willen weergeven en voeren de optelling van de verkregen resultaten uit met behulp van de operatoren "HART" en "VERTROUWENNORMEN”. In ons geval ziet de formule er als volgt uit: A14+A16. Druk na het typen op Enter.
14. Als resultaat wordt de berekening uitgevoerd en wordt het resultaat onmiddellijk weergegeven in de cel met de formule.
15. Vervolgens voeren we op een vergelijkbare manier de berekening uit om de waarde van de linkerrand van de CI te verkrijgen. Alleen in dit geval is de waarde van het resultaat “VERTROUWENNORMEN” u hoeft niet op te tellen, maar af te trekken van het resultaat verkregen met de operator “HART". In ons geval ziet de formule er als volgt uit: =A16-A14.
16. Nadat we op Enter hebben gedrukt, krijgen we het resultaat in de gegeven cel met de formule.

Opmerking: In de bovenstaande paragrafen hebben we geprobeerd om alle stappen en elke gebruikte functie zo gedetailleerd mogelijk te beschrijven. Alle voorgeschreven formules kunnen echter samen worden geschreven, als onderdeel van één grote:

• Om de rechterrand van de CI te bepalen, ziet de algemene formule er als volgt uit:

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

• Evenzo moet u voor de linkerrand, alleen in plaats van een plus, een min plaatsen:

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

Methode 2: TRUST.STUDENT-operator

Laten we nu vertrouwd raken met de tweede operator voor het bepalen van het betrouwbaarheidsinterval − VERTROUWEN.STUDENT. Deze functie is relatief recent in het programma geïntroduceerd, vanaf de versie van Excel 2010, en is gericht op het bepalen van het CI van de geselecteerde dataset met behulp van de Student-distributie, met een onbekende variantie.

Functie Formule VERTROUWEN.STUDENT als volgt:

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

Laten we de toepassing van deze operator analyseren op het voorbeeld van dezelfde tabel. Alleen weten we nu niet de standaarddeviatie volgens de omstandigheden van het probleem.

1. Selecteer eerst de cel waarin we het resultaat willen weergeven. Klik dan op het icoon “Functie invoegen” (links van de formulebalk).
2. Het al bekende Function Wizard-venster wordt geopend. Kies een categorie “Statistisch”, klik vervolgens in de voorgestelde lijst met functies op de operator “VERTROUWDE STUDENT”, dan - OK.
3. In het volgende venster moeten we de functieargumenten instellen:
   • In het "Alpha" zoals bij de eerste methode, specificeer de waarde 0,05 (of "100-95)/100").
   • Laten we verder gaan met het argument. "std_off". Omdat volgens de omstandigheden van het probleem de waarde ons onbekend is, moeten we de juiste berekeningen maken, waarbij de operator "STDEV.B”. Klik op de knop functie toevoegen en vervolgens op het item "Meer eigenschappen…".
   • Selecteer in het volgende venster van de Functiewizard de operator “STDEV.B” in categorie “Statistisch” en klik op OK.
   • We komen in het instellingenvenster voor functieargumenten, waarvan de formule er als volgt uitziet: =СТАНДОТКЛОН.В(число1;число2;...). Als eerste argument specificeren we een bereik dat alle cellen in de kolom "Waarde" omvat (de kop niet meegerekend).
   • Nu moet je teruggaan naar het venster met de functieargumenten “VERTROUWEN.STUDENT”. Klik hiervoor op het gelijknamige opschrift in het invoerveld van de formule.
   • Laten we nu verder gaan met het laatste argument "Grootte". Net als bij de eerste methode, kunt u hier eenvoudig een celbereik opgeven of de operator invoegen "REKENING". Wij kiezen voor de laatste optie.
   • Zodra alle argumenten zijn ingevuld, klikt u op de knop OK.
4. De geselecteerde cel geeft de waarde van het betrouwbaarheidsinterval weer volgens de parameters die we hebben gespecificeerd.
5. Vervolgens moeten we de waarden van de CI-grenzen berekenen. En hiervoor moet u de gemiddelde waarde voor het geselecteerde bereik krijgen. Hiervoor passen we opnieuw de functie “HART". Het algoritme van acties is vergelijkbaar met dat beschreven in de eerste methode.
6. Na ontvangst van de waarde “HART", kunt u beginnen met het berekenen van de CI-grenzen. De formules zelf verschillen niet van die gebruikt met de "VERTROUWENNORMEN”:
   • Rechterrand CI = GEMIDDELDE + STUDENTVERTROUWEN
   • Links gebonden CI = GEMIDDELDE STUDENTVERTROUWEN

Conclusie

Het arsenaal aan tools van Excel is ongelooflijk groot, en samen met gemeenschappelijke functies biedt het programma een breed scala aan speciale functies die het werken met gegevens veel gemakkelijker maken. Misschien lijken de hierboven beschreven stappen voor sommige gebruikers op het eerste gezicht ingewikkeld. Maar na een gedetailleerde studie van het probleem en de volgorde van acties, zal alles veel gemakkelijker worden.