A bizalmi intervallum kiszámítása Excelben

Az Excel segítségével különféle statisztikai feladatokat hajtanak végre, ezek egyike a konfidencia intervallum kiszámítása, amely a kis mintaszámú pontbecslés legmegfelelőbb helyettesítőjeként szolgál.

Azonnal meg akarjuk jegyezni, hogy a konfidenciaintervallum kiszámításának folyamata meglehetősen bonyolult, azonban az Excelben számos eszköz található, amelyek megkönnyítik ezt a feladatot. Vessünk egy pillantást rájuk.

Konfidencia intervallum számítása

Konfidenciaintervallumra van szükség ahhoz, hogy bizonyos statikus adatok intervallumbecslését adhassuk. Ennek a műveletnek a fő célja a pontbecslés bizonytalanságának megszüntetése.

A feladat Microsoft Excel programban történő végrehajtására két módszer létezik:

• Operátor BIZALOM NORM – olyan esetekben használják, amikor a diszperzió ismert;
• Operátor BIZALOM.DIÁKamikor a szórás ismeretlen.

Az alábbiakban lépésről lépésre elemezzük mindkét módszert a gyakorlatban.

1. módszer: TRUST.NORM utasítás

Ezt a funkciót először az Excel 2010 kiadásban vezették be a program arzenáljába (a verzió előtt a " operátor váltotta felMEGBÍZHATÓ”). Az operátor a „statisztikai” kategóriába tartozik.

Funkció képlet BIZALOM NORM így néz ki:

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

Amint látjuk, a függvénynek három argumentuma van:

• „Alpha” a szignifikancia szintjének mutatója, amelyet a számítás alapjául vesznek. A megbízhatósági szintet a következőképpen számítják ki:
  • 1-"Альфа". Ez a kifejezés akkor alkalmazható, ha az érték „Alpha” együtthatóként mutatjuk be. Például, 1-0,7 0,3 =, ahol 0,7=70%/100%.
  • (100-"Альфа")/100. Ezt a kifejezést akkor alkalmazzuk, ha figyelembe vesszük a megbízhatósági szintet az értékkel „Alpha” százalékban. Például, (100-70) / 100 = 0,3.
• „Szabvány eltérés” – az elemzett adatminta szórása.
• "Méret" az adatminta mérete.

Jegyzet: Ehhez a függvényhez mindhárom argumentum megléte előfeltétel.

Operátor "MEGBÍZHATÓ”, amelyet a program korábbi verzióiban használtak, ugyanazokat az argumentumokat tartalmazza, és ugyanazokat a funkciókat látja el.

Funkció képlet BIZTOS az alábbiak szerint:

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

Magában a képletben nincs különbség, csak az operátor neve más. Az Excel 2010 és újabb kiadásaiban ez az operátor a kompatibilitás kategóriába tartozik. A program régebbi verzióiban a statikus függvények részben található.

A konfidenciaintervallum határát a következő képlet határozza meg:

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

ahol Х az átlagos érték a megadott tartományon belül.

Most nézzük meg, hogyan alkalmazzuk ezeket a képleteket a gyakorlatban. Tehát van egy táblázatunk 10 mérés különböző adataival. Ebben az esetben az adatsor szórása 8.

Feladatunk, hogy a konfidenciaintervallum értékét 95%-os megbízhatósági szinttel kapjuk meg.

1. Először is válasszon ki egy cellát az eredmény megjelenítéséhez. Ezután rákattintunk a gombra "Funkció beszúrása" (a képletsor bal oldalán).
2. Megnyílik a Funkcióvarázsló ablak. Az aktuális függvénykategóriára kattintva bontsa ki a listát, és kattintson a benne lévő sorra "Statisztikai".
3. A javasolt listában kattintson az operátorra „BIZALOM NORMÁJA”, Majd nyomja meg a gombot OK.
4. Megjelenik egy ablak a függvény argumentumainak beállításaival, amit kitöltve nyomjuk meg a gombot OK.
   • a területen „Alpha” jelezze a jelentőség szintjét. Feladatunk 95%-os megbízhatósági szintet feltételez. Ha ezt az értéket behelyettesítjük a fenti számítási képletbe, a következő kifejezést kapjuk: (100-95)/100. Beírjuk az argumentum mezőbe (vagy azonnal beírhatjuk a számítás eredményét 0,05-tel).
   • a területen "std_off" feltételeink szerint 8-as számot írunk.
   • a „Méret” mezőben adja meg a vizsgálandó elemek számát. Esetünkben 10 mérés történt, ezért a 10-es számot írjuk.
5. Annak érdekében, hogy ne kelljen újrakonfigurálnia a funkciót az adatok megváltozásakor, automatizálhatja azt. Ehhez a „függvényt használjukJELÖLJE BE". Helyezze a mutatót az argumentuminformáció beviteli területére "Méret", majd kattintson a képletsor bal oldalán található háromszög ikonra, és kattintson az elemre “További funkciók…”.
6. Ennek eredményeként megnyílik a Funkcióvarázsló másik ablaka. Kategória kiválasztásával "Statisztikai", kattintson a funkcióra "JELÖLJE BE", akkor jó.
7. A képernyőn egy másik ablak jelenik meg a függvény argumentumainak beállításaival, amely egy adott tartományban a numerikus adatokat tartalmazó cellák számának meghatározására szolgál.

   Funkció képlet JELÖLJE BE így van írva: =СЧЁТ(Значение1;Значение2;...).

   A függvényhez használható argumentumok száma legfeljebb 255 lehet. Ide írhat konkrét számokat, cellacímeket vagy cellatartományokat. Az utolsó lehetőséget fogjuk használni. Ehhez kattintson az első argumentum információbeviteli területére, majd a bal egérgombot lenyomva tartva jelölje ki táblázatunk egyik oszlopának összes celláját (a fejlécet nem számítva), majd nyomja meg a gombot. OK.

8. A végrehajtott műveletek eredményeként az operátor számítási eredménye megjelenik a kiválasztott cellában BIZALOM NORM. A mi feladatunkban az értéke egyenlőnek bizonyult 4,9583603.
9. De ez még nem a mi feladatunk végeredménye. Ezután ki kell számítania egy adott intervallum átlagértékét. Ehhez a " funkciót kell használniSZÍV"A, amely egy meghatározott adattartomány átlagának kiszámítását végzi.

   Az operátor képlet a következőképpen van írva: =СРЗНАЧ(число1;число2;...).

   Válassza ki azt a cellát, ahová a függvényt be kívánjuk illeszteni, és nyomja meg a gombot "Funkció beszúrása".

10. Kategóriában "Statisztikai" válasszon unalmas kezelőt "SZÍV" és kattintson OK.
11. A függvény argumentumában argumentumértékben "Szám" adja meg a tartományt, amely magában foglalja az összes cellát az összes mérés értékével. Aztán kattintunk OKÉ.
12. A végrehajtott műveletek eredményeként az átlagérték automatikusan kiszámításra kerül, és megjelenik az újonnan beillesztett függvénnyel rendelkező cellában.
13. Most ki kell számítanunk a CI (konfidenciaintervallum) határait. Kezdjük a jobb oldali szegély értékének kiszámításával. Kijelöljük azt a cellát, ahol az eredményt meg szeretnénk jeleníteni, és a kapott eredményeket az operátorok segítségével összeadjuk.SZÍV" és "BIZALMI NORMÁK”. Esetünkben a képlet így néz ki: A14+A16. A beírás után nyomja meg a gombot belép.
14. Ennek eredményeként a számítás végrehajtásra kerül, és az eredmény azonnal megjelenik a cellában a képlettel.
15. Ezután hasonló módon végezzük el a számítást, hogy megkapjuk a CI bal határának értékét. Csak ebben az esetben az eredmény értéke "BIZALMI NORMÁK” nem össze kell adni, hanem ki kell vonni az operátor segítségével kapott eredménytSZÍV". Esetünkben a képlet így néz ki: =A16-A14.
16. Az Enter megnyomása után az adott cellában kapjuk meg az eredményt a képlettel.

Jegyzet: A fenti bekezdésekben igyekeztünk a lehető legrészletesebben leírni az összes lépést és az egyes használt funkciókat. Azonban az összes előírt képlet összeírható egy nagy képlet részeként:

• A CI jobb határának meghatározásához az általános képlet így fog kinézni:

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

• Hasonlóképpen, a bal oldali szegélyhez csak plusz helyett mínuszt kell beírni:

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

2. módszer: TRUST.STUDENT operátor

Most ismerkedjünk meg a második operátorral a konfidenciaintervallum meghatározására − BIZALOM.DIÁK. Ezt a függvényt viszonylag nemrég, az Excel 2010 verziójától kezdve vezették be a programba, és a kiválasztott adatkészlet CI-jének a Student-féle eloszlás segítségével, ismeretlen szórással történő meghatározását célozza.

Funkció képlet BIZALOM.DIÁK az alábbiak szerint:

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

Elemezzük ennek az operátornak az alkalmazását ugyanezen táblázat példáján. Csak most nem ismerjük a szórást a probléma körülményei szerint.

1. Először válassza ki azt a cellát, ahol az eredményt szeretnénk megjeleníteni. Ezután kattintson az ikonra "Funkció beszúrása" (a képletsor bal oldalán).
2. Megnyílik a már jól ismert Funkcióvarázsló ablak. Válasszon kategóriát "Statisztikai", majd a javasolt függvénylistában kattintson az operátorra „MEGBÍZHATÓ DIÁK”, akkor - OK.
3. A következő ablakban be kell állítanunk a függvény argumentumait:
   • A „Alpha” az első módszerhez hasonlóan adja meg a 0,05 (vagy „100-95)/100” értéket.
   • Térjünk át az érvelésre. "std_off". Mivel a probléma körülményei szerint ennek értéke ismeretlen számunkra, el kell végeznünk a megfelelő számításokat, amelyekben az operátor „STDEV.B”. Kattintson a funkció hozzáadása gombra, majd az elemre “További funkciók…”.
   • A Funkcióvarázsló következő ablakában válassza ki a „STDEV.B” kategóriában "Statisztikai" és kattintson OK.
   • Bejutunk a függvényargumentumok beállítási ablakába, amelynek képlete így néz ki: =СТАНДОТКЛОН.В(число1;число2;...). Első argumentumként egy olyan tartományt adunk meg, amely az „Érték” oszlop összes celláját tartalmazza (a fejlécet nem számítva).
   • Most vissza kell mennie az ablakba a függvény argumentumával "BIZALOM.DIÁK”. Ehhez kattintson az azonos nevű feliratra a képlet beviteli mezőjében.
   • Most menjünk tovább az utolsó „Méret” argumentumhoz. Az első módszerhez hasonlóan itt is megadhat egy cellatartományt, vagy beillesztheti az operátort "JELÖLJE BE". Az utolsó lehetőséget választjuk.
   • Miután az összes argumentumot kitöltötte, kattintson a gombra OK.
4. A kiválasztott cellában az általunk megadott paramétereknek megfelelő konfidencia intervallum értéke jelenik meg.
5. Ezután ki kell számítanunk a CI-határok értékeit. És ehhez meg kell kapnia a kiválasztott tartomány átlagos értékét. Ehhez ismét alkalmazzuk a „SZÍV". A műveletek algoritmusa hasonló az első módszerben leírtakhoz.
6. Miután megkapta az értéket "SZÍV", elkezdheti a CI határok kiszámítását. Maguk a képletek nem különböznek a "BIZALMI NORMÁK”:
   • Jobb oldali szegély CI=ÁTLAG+DIÁK BIZALMA
   • Balra kötött CI=ÁTLAGOS DIÁK BIZALMA

Következtetés

Az Excel eszköztára hihetetlenül nagy, és a közös funkciók mellett a program számos speciális funkciót kínál, amelyek jelentősen megkönnyítik az adatokkal való munkát. Talán a fent leírt lépések néhány felhasználó számára első pillantásra bonyolultnak tűnhetnek. De a probléma és a műveletek sorrendjének részletes tanulmányozása után minden sokkal könnyebb lesz.