Pasitikėjimo intervalo skaičiavimas programoje „Excel“.

„Excel“ naudojama įvairioms statistinėms užduotims atlikti, iš kurių viena yra pasikliautinojo intervalo apskaičiavimas, kuris naudojamas kaip tinkamiausias taško įverčio pakaitalas esant mažam imties dydžiui.

Iš karto norime pažymėti, kad pati pasitikėjimo intervalo skaičiavimo procedūra yra gana sudėtinga, tačiau „Excel“ yra daugybė įrankių, skirtų šiai užduočiai palengvinti. Pažvelkime į juos.

Pasitikėjimo intervalo skaičiavimas

Pasikliautinasis intervalas reikalingas tam, kad būtų galima įvertinti kai kurių statinių duomenų intervalą. Pagrindinis šios operacijos tikslas – pašalinti taškinio įvertinimo neapibrėžtumus.

Yra du būdai, kaip atlikti šią užduotį „Microsoft Excel“:

• operatorius PASITIKĖJIMO NORMA – naudojamas tais atvejais, kai dispersija yra žinoma;
• operatorius PATIKĖKITE.STUDENTASkai dispersija nežinoma.

Žemiau mes žingsnis po žingsnio analizuosime abu metodus praktikoje.

1 metodas: TRUST.NORM teiginys

Ši funkcija pirmą kartą buvo įtraukta į programos arsenalą „Excel 2010“ leidime (prieš šią versiją ji buvo pakeista operatoriumi „PATIKIMA“). Operatorius įtrauktas į „statistikos“ kategoriją.

Funkcijų formulė PASITIKĖJIMO NORMA atrodo taip:

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

Kaip matome, funkcija turi tris argumentus:

• "Alfa" yra reikšmingumo lygio rodiklis, kuris yra apskaičiavimo pagrindas. Pasitikėjimo lygis apskaičiuojamas taip:
  • 1-"Альфа". Ši išraiška taikoma, jei reikšmė "Alfa" pateiktas kaip koeficientas. Pavyzdžiui, 1-0,7 0,3 =, kur 0,7=70%/100%.
  • (100-"Альфа")/100. Ši išraiška bus taikoma, jei atsižvelgsime į patikimumo lygį su verte "Alfa" procentais. Pavyzdžiui, (100-70) / 100 = 0,3.
• „Standartinis nuokrypis“ – atitinkamai analizuojamų duomenų imties standartinis nuokrypis.
• "Dydis" yra duomenų imties dydis.

Pastaba: Šiai funkcijai visų trijų argumentų buvimas yra būtina sąlyga.

Operatorius "PATIKIMA“, kuris buvo naudojamas ankstesnėse programos versijose, turi tuos pačius argumentus ir atlieka tas pačias funkcijas.

Funkcijų formulė PATIKIMAI keičiamas taip:

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

Pačioje formulėje skirtumų nėra, skiriasi tik operatoriaus pavadinimas. „Excel 2010“ ir vėlesnėse versijose šis operatorius yra suderinamumo kategorijoje. Senesnėse programos versijose ji yra statinių funkcijų skyriuje.

Pasitikėjimo intervalo riba nustatoma pagal šią formulę:

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

kur Х yra vidutinė vertė nurodytame diapazone.

Dabar pažiūrėkime, kaip šias formules pritaikyti praktikoje. Taigi, turime lentelę su įvairiais duomenimis iš 10 atliktų matavimų. Šiuo atveju duomenų rinkinio standartinis nuokrypis yra 8.

Mūsų užduotis yra gauti pasikliautinojo intervalo vertę su 95% patikimumo lygiu.

1. Pirmiausia pasirinkite langelį, kad būtų rodomas rezultatas. Tada paspaudžiame mygtuką „Įterpimo funkcija“ (kairėje nuo formulės juostos).
2. Atsidaro funkcijų vedlio langas. Spustelėdami esamą funkcijų kategoriją, išplėskite sąrašą ir spustelėkite jame esančią eilutę "Statistinė".
3. Siūlomame sąraše spustelėkite operatorių „PASITIKINKIMO NORMA“, Tada paspauskite OK.
4. Pamatysime langą su funkcijos argumentų nustatymais, kurį užpildę paspaudžiame mygtuką OK.
   • srityje "Alfa" nurodyti reikšmingumo lygį. Mūsų užduotis apima 95% patikimumo lygį. Pakeitę šią vertę į skaičiavimo formulę, kurią mes svarstėme aukščiau, gauname išraišką: (100-95)/100. Įrašome jį argumentų laukelyje (arba iš karto galite parašyti skaičiavimo rezultatą, lygų 0,05).
   • srityje „std_off“ pagal mūsų sąlygas rašome skaičių 8.
   • laukelyje „Dydis“ nurodykite tiriamų elementų skaičių. Mūsų atveju buvo atlikta 10 matavimų, todėl rašome skaičių 10.
5. Kad nereikėtų iš naujo konfigūruoti funkcijos pasikeitus duomenims, galite ją automatizuoti. Tam naudojame funkciją "PATIKRINTI". Įveskite žymeklį į argumentų informacijos įvesties sritį "Dydis", tada spustelėkite trikampio piktogramą kairėje formulės juostos pusėje ir spustelėkite elementą „Daugiau funkcijų…“.
6. Dėl to atsidarys kitas funkcijų vedlio langas. Pasirinkus kategoriją "Statistinė", spustelėkite funkciją "PATIKRINTI", tada gerai.
7. Ekrane bus rodomas kitas langas su funkcijos argumentų nustatymais, kuris naudojamas norint nustatyti langelių skaičių tam tikrame diapazone, kuriame yra skaitmeninių duomenų.

   Funkcijų formulė TIKRINTI parašyta taip: =СЧЁТ(Значение1;Значение2;...).

   Galimų šios funkcijos argumentų skaičius gali būti iki 255. Čia galite įrašyti konkrečius skaičius arba langelių adresus arba langelių diapazonus. Mes naudosime paskutinį variantą. Norėdami tai padaryti, spustelėkite pirmojo argumento informacijos įvesties sritį, tada, laikydami nuspaudę kairįjį pelės mygtuką, pažymėkite visus vieno iš mūsų lentelės stulpelių langelius (neskaičiuojant antraštės), tada paspauskite mygtuką OK.

8. Dėl atliktų veiksmų operatoriaus skaičiavimų rezultatas bus rodomas pasirinktoje langelyje PASITIKĖJIMO NORMA. Mūsų uždavinyje jo vertė pasirodė lygi 4,9583603.
9. Bet tai dar nėra galutinis mūsų užduoties rezultatas. Tada turite apskaičiuoti vidutinę vertę per tam tikrą intervalą. Norėdami tai padaryti, turite naudoti funkciją "ŠIRDIS“A, kuri atlieka tam tikro duomenų diapazono vidurkio apskaičiavimo užduotį.

   Operatoriaus formulė parašyta taip: =СРЗНАЧ(число1;число2;...).

   Pasirinkite langelį, kuriame planuojame įterpti funkciją, ir paspauskite mygtuką „Įterpimo funkcija“.

10. Kategorijoje "Statistinė" pasirinkti nuobodų operatorių "ŠIRDIS" ir spauskite OK.
11. Funkcijos argumentuose argumento reikšmėje "Skaičius" nurodykite diapazoną, į kurį įeina visi langeliai su visų matavimų reikšmėmis. Tada paspaudžiame GERAI.
12. Dėl atliktų veiksmų vidutinė vertė bus automatiškai apskaičiuojama ir rodoma langelyje su naujai įterpta funkcija.
13. Dabar turime apskaičiuoti CI (pasitikėjimo intervalo) ribas. Pradėkime nuo tinkamos kraštinės vertės apskaičiavimo. Mes pasirenkame langelį, kuriame norime rodyti rezultatą, ir pridedame gautus rezultatus naudodami operatorius “ŠIRDIS“ ir "PASITIKĖJIMO NORMOS“. Mūsų atveju formulė atrodo taip: A14+A16. Įvedę jį paspauskite įeiti.
14. Dėl to bus atliktas skaičiavimas, o rezultatas iškart bus rodomas langelyje su formule.
15. Tada panašiu būdu atliekame skaičiavimą, kad gautume kairiosios CI kraštinės reikšmę. Tik šiuo atveju rezultato reikšmė “PASITIKĖJIMO NORMOS“ nereikia pridėti, o atimti iš rezultato, gauto naudojant operatorių “ŠIRDIS“. Mūsų atveju formulė atrodo taip: =A16-A14.
16. Paspaudę Enter gausime rezultatą duotame langelyje su formule.

Pastaba: Aukščiau pateiktose pastraipose stengėmės kuo išsamiau aprašyti visus veiksmus ir kiekvieną naudojamą funkciją. Tačiau visas nustatytas formules galima parašyti kartu kaip vienos didelės formulės dalį:

• Norint nustatyti dešinę CI kraštą, bendra formulė atrodys taip:

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

• Panašiai kairėje kraštinėje, tik vietoj pliuso, reikia įdėti minusą:

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

2 metodas: TRUST.STUDENT operatorius

Dabar susipažinkime su antruoju pasikliautinojo intervalo nustatymo operatoriumi − PATIKĖKITE.STUDENTAS. Ši funkcija buvo įdiegta į programą palyginti neseniai, pradedant nuo Excel 2010 versijos, ir yra skirta nustatyti pasirinkto duomenų rinkinio CI naudojant Studento paskirstymą su nežinoma dispersija.

Funkcijų formulė PATIKĖKITE.STUDENTAS keičiamas taip:

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

Išanalizuokime šio operatoriaus taikymą tos pačios lentelės pavyzdyje. Tik dabar mes nežinome standartinio nuokrypio pagal problemos sąlygas.

1. Pirmiausia pasirinkite langelį, kuriame planuojame rodyti rezultatą. Tada spustelėkite piktogramą „Įterpimo funkcija“ (kairėje nuo formulės juostos).
2. Atsidarys jau gerai žinomas funkcijų vedlio langas. Pasirinkite kategoriją "Statistinė", tada iš siūlomo funkcijų sąrašo spustelėkite operatorių „PATIKIMAS STUDENTAS“, tada - OK.
3. Kitame lange turime nustatyti funkcijos argumentus:
   • Lauke "Alfa" kaip ir pirmuoju būdu, nurodykite reikšmę 0,05 (arba „100-95)/100“).
   • Pereikime prie argumento. „std_off“. Kadangi pagal problemos sąlygas jos reikšmė mums nežinoma, reikia atlikti atitinkamus skaičiavimus, kuriuose operatorius „STDEV.B“. Spustelėkite pridėti funkcijos mygtuką, tada – elementą „Daugiau funkcijų…“.
   • Kitame funkcijų vedlio lange pasirinkite operatorių “STDEV.B“ kategorijoje "Statistinė" ir spauskite OK.
   • Mes patenkame į funkcijų argumentų nustatymų langą, kurio formulė atrodo taip: =СТАНДОТКЛОН.В(число1;число2;...). Kaip pirmąjį argumentą nurodome diapazoną, apimantį visus langelius stulpelyje „Vertė“ (neskaičiuojant antraštės).
   • Dabar reikia grįžti į langą su funkcijos argumentais "PASITIKĖJIMAS.STUDENTAS“. Norėdami tai padaryti, formulės įvesties lauke spustelėkite to paties pavadinimo užrašą.
   • Dabar pereikime prie paskutinio argumento „Dydis“. Kaip ir pirmuoju metodu, čia galite tiesiog nurodyti langelių diapazoną arba įterpti operatorių "PATIKRINTI". Mes pasirenkame paskutinį variantą.
   • Kai visi argumentai bus užpildyti, spustelėkite mygtuką OK.
4. Pasirinktame langelyje bus rodoma pasikliautinojo intervalo reikšmė pagal mūsų nurodytus parametrus.
5. Toliau turime apskaičiuoti CI ribų reikšmes. Ir tam reikia gauti vidutinę pasirinkto diapazono vertę. Norėdami tai padaryti, vėl taikome funkciją "ŠIRDIS“. Veiksmų algoritmas yra panašus į aprašytą pirmame metode.
6. Gavęs vertę “ŠIRDIS“, galite pradėti skaičiuoti CI ribas. Pačios formulės niekuo nesiskiria nuo naudojamų su "PASITIKĖJIMO NORMOS“:
   • Dešinė kraštinė CI=VIDUTINIS+STUDENTŲ PASITIKĖJIMAS
   • Left Bound CI = VIDUTINIS STUDENTŲ PASITIKĖJIMAS

Išvada

„Excel“ įrankių arsenalas yra neįtikėtinai didelis, o kartu su įprastomis funkcijomis programa siūlo daugybę specialių funkcijų, kurios labai palengvins darbą su duomenimis. Galbūt kai kuriems vartotojams iš pirmo žvilgsnio aukščiau aprašyti veiksmai gali pasirodyti sudėtingi. Tačiau išsamiai išnagrinėjus problemą ir veiksmų seką, viskas taps daug lengviau.