Calcularea intervalului de încredere în Excel

Excel este folosit pentru a efectua diverse sarcini statistice, dintre care una este calcularea intervalului de încredere, care este folosit ca înlocuitor cel mai potrivit pentru o estimare punctuală cu o dimensiune mică a eșantionului.

Vrem să observăm imediat că procedura de calcul a intervalului de încredere este destul de complicată, totuși, în Excel există o serie de instrumente concepute pentru a facilita această sarcină. Să aruncăm o privire la ele.

Calcul intervalului de încredere

Este necesar un interval de încredere pentru a oferi o estimare a intervalului unor date statice. Scopul principal al acestei operațiuni este de a elimina incertitudinile estimării punctuale.

Există două metode pentru a efectua această sarcină în Microsoft Excel:

• Operator NORMA DE ÎNCREDERE – utilizat în cazurile în care dispersia este cunoscută;
• Operator ÎNCREDERE.STUDENTcând varianța este necunoscută.

Mai jos vom analiza pas cu pas ambele metode în practică.

Metoda 1: instrucțiunea TRUST.NORM

Această funcție a fost introdusă pentru prima dată în arsenalul programului în ediția Excel 2010 (înainte de această versiune, a fost înlocuită de operatorul „DE ÎNCREDERE”). Operatorul este inclus în categoria „statistică”.

Formula funcției NORMA DE ÎNCREDERE arată așa:

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

După cum putem vedea, funcția are trei argumente:

• "Alpha" este un indicator al nivelului de semnificație, care este luat ca bază pentru calcul. Nivelul de încredere se calculează după cum urmează:
  • 1-"Альфа". Această expresie este aplicabilă dacă valoarea "Alpha" prezentat ca un coeficient. De exemplu, 1-0,7 0,3 =, unde 0,7=70%/100%.
  • (100-"Альфа")/100. Această expresie va fi aplicată dacă luăm în considerare nivelul de încredere cu valoarea "Alpha" în procente. De exemplu, (100-70) / 100 = 0,3.
• "Deviație standard" – respectiv, abaterea standard a probei de date analizate.
• "Mărimea" este dimensiunea eșantionului de date.

Notă: Pentru această funcție, prezența tuturor celor trei argumente este o condiție prealabilă.

Operator „DE ÎNCREDERE”, care a fost folosit în versiunile anterioare ale programului, conține aceleași argumente și îndeplinește aceleași funcții.

Formula funcției DE ÎNCREDERE după cum urmează:

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

Nu există diferențe în formula în sine, doar numele operatorului este diferit. În Excel 2010 și edițiile ulterioare, acest operator se află în categoria Compatibilitate. În versiunile mai vechi ale programului, acesta se află în secțiunea de funcții statice.

Limita intervalului de încredere este determinată de următoarea formulă:

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

Unde Х este valoarea medie în intervalul specificat.

Acum să vedem cum să aplicăm aceste formule în practică. Deci, avem un tabel cu diverse date din 10 măsurători efectuate. În acest caz, abaterea standard a setului de date este 8.

Sarcina noastră este să obținem valoarea intervalului de încredere cu un nivel de încredere de 95%.

1. În primul rând, selectați o celulă pentru a afișa rezultatul. Apoi facem clic pe butonul „Funcția de inserare” (în stânga barei de formule).
2. Se deschide fereastra Function Wizard. Făcând clic pe categoria curentă de funcții, extindeți lista și faceți clic pe linia din ea "Statistic".
3. În lista propusă, faceți clic pe operator „NORMA DE ÎNCREDERE”, Apoi apăsați OK.
4. Vom vedea o fereastră cu setările argumentelor funcției, completând pe care apăsăm butonul OK.
   • în câmp "Alpha" indica nivelul de semnificație. Sarcina noastră presupune un nivel de încredere de 95%. Înlocuind această valoare în formula de calcul, pe care am considerat-o mai sus, obținem expresia: (100-95)/100. O scriem în câmpul de argument (sau puteți scrie imediat rezultatul calculului egal cu 0,05).
   • în câmp „std_off” conform condițiilor noastre, scriem numărul 8.
   • în câmpul „Mărime”, specificați numărul de elemente de examinat. În cazul nostru s-au făcut 10 măsurători, așa că scriem numărul 10.
5. Pentru a evita reconfigurarea funcției atunci când datele se modifică, o puteți automatiza. Pentru aceasta folosim funcția „VERIFICA". Puneți indicatorul în zona de intrare a informațiilor despre argument "Mărimea", apoi faceți clic pe pictograma triunghi din partea stângă a barei de formule și faceți clic pe articol "Mai multe trăsături…".
6. Ca rezultat, se va deschide o altă fereastră a Expertului funcții. Prin alegerea unei categorii "Statistic", faceți clic pe funcția „VERIFICA", atunci bine.
7. Ecranul va afișa o altă fereastră cu setările argumentelor funcției, care este folosită pentru a determina numărul de celule dintr-un interval dat care conțin date numerice.

   Formula funcției VERIFICA este scris asa: =СЧЁТ(Значение1;Значение2;...).

   Numărul de argumente disponibile pentru această funcție poate fi de până la 255. Aici puteți scrie fie numere specifice, fie adrese de celule, fie intervale de celule. Vom folosi ultima opțiune. Pentru a face acest lucru, faceți clic pe zona de introducere a informațiilor pentru primul argument, apoi, ținând apăsat butonul stâng al mouse-ului, selectați toate celulele uneia dintre coloanele tabelului nostru (fără numărarea antetului), apoi apăsați butonul OK.

8. Ca urmare a acțiunilor întreprinse, rezultatul calculelor pentru operator va fi afișat în celula selectată NORMA DE ÎNCREDERE. În problema noastră, valoarea sa s-a dovedit a fi egală cu 4,9583603.
9. Dar acesta nu este încă rezultatul final al sarcinii noastre. Apoi, trebuie să calculați valoarea medie pe un interval dat. Pentru a face acest lucru, trebuie să utilizați funcția „INIMA”A care îndeplinește sarcina de a calcula media pentru un interval specificat de date.

   Formula operatorului este scrisă astfel: =СРЗНАЧ(число1;число2;...).

   Selectați celula în care intenționăm să inserăm funcția și apăsați butonul „Funcția de inserare”.

10. În categorie "Statistic" alege un operator plictisitor „INIMĂ” și faceți clic OK.
11. În argumente de funcție în valoare de argument "Număr" specificați intervalul, care include toate celulele cu valorile tuturor măsurătorilor. Apoi facem clic BINE.
12. Ca urmare a acțiunilor întreprinse, valoarea medie va fi automat calculată și afișată în celula cu funcția nou introdusă.
13. Acum trebuie să calculăm limitele CI (intervalul de încredere). Să începem prin a calcula valoarea chenarului drept. Selectăm celula în care dorim să afișăm rezultatul și efectuăm adăugarea rezultatelor obținute folosind operatorii „INIMA” și "NORME DE ÎNCREDERE”. În cazul nostru, formula arată astfel: A14+A16. După ce l-ați tastat, apăsați Intrați.
14. Ca urmare, calculul va fi efectuat și rezultatul va fi afișat imediat în celula cu formula.
15. Apoi, într-un mod similar, efectuăm calculul pentru a obține valoarea marginii din stânga a CI. Numai în acest caz valoarea rezultatului „NORME DE ÎNCREDERE” nu trebuie să adăugați, ci să scădeți din rezultatul obținut folosind operatorul „INIMA”. În cazul nostru, formula arată astfel: =A16-A14.
16. După apăsarea Enter, vom obține rezultatul în celula dată cu formula.

Notă: În paragrafele de mai sus, am încercat să descriem cât mai detaliat toți pașii și fiecare funcție folosită. Cu toate acestea, toate formulele prescrise pot fi scrise împreună, ca parte a uneia mari:

• Pentru a determina marginea dreaptă a CI, formula generală va arăta astfel:

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

• În mod similar, pentru marginea din stânga, doar în loc de un plus, trebuie să puneți un minus:

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

Metoda 2: operator TRUST.STUDENT

Acum, să ne familiarizăm cu al doilea operator pentru determinarea intervalului de încredere − ÎNCREDERE.STUDENT. Această funcție a fost introdusă în program relativ recent, începând cu versiunea de Excel 2010, și are ca scop determinarea CI al setului de date selectat folosind distribuția Student, cu o varianță necunoscută.

Formula funcției ÎNCREDERE.STUDENT după cum urmează:

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

Să analizăm aplicarea acestui operator pe exemplul aceluiași tabel. Abia acum nu cunoaștem abaterea standard în funcție de condițiile problemei.

1. Mai întâi, selectați celula în care intenționăm să afișam rezultatul. Apoi faceți clic pe pictogramă „Funcția de inserare” (în stânga barei de formule).
2. Se va deschide fereastra deja binecunoscută Function Wizard. Alege o categorie "Statistic", apoi din lista de funcții propusă, faceți clic pe operator „ELEV DE ÎNCREDERE”, apoi - OK.
3. În fereastra următoare, trebuie să setăm argumentele funcției:
   • În "Alpha" ca și în prima metodă, specificați valoarea 0,05 (sau „100-95)/100”).
   • Să trecem la argument. „std_off”. Deoarece, în funcție de condițiile problemei, valoarea acesteia ne este necunoscută, trebuie să facem calculele adecvate, în care operatorul „STDEV.B”. Faceți clic pe butonul de adăugare a funcției și apoi pe element "Mai multe trăsături…".
   • În fereastra următoare a Expertului funcții, selectați operatorul „STDEV.B” în categoria "Statistic" și faceți clic OK.
   • Intrăm în fereastra de setări a argumentelor funcției, a cărei formulă arată astfel: =СТАНДОТКЛОН.В(число1;число2;...). Ca prim argument, specificăm un interval care include toate celulele din coloana „Valoare” (fără numărarea antetului).
   • Acum trebuie să reveniți la fereastra cu argumentele funcției „ÎNCREDERE.STUDENT”. Pentru a face acest lucru, faceți clic pe inscripția cu același nume în câmpul de introducere a formulei.
   • Acum să trecem la ultimul argument „Dimensiune”. Ca și în prima metodă, aici puteți fie să specificați pur și simplu un interval de celule, fie să introduceți operatorul "VERIFICA". Alegem ultima varianta.
   • Odată ce toate argumentele sunt completate, faceți clic pe butonul OK.
4. Celula selectată va afișa valoarea intervalului de încredere în funcție de parametrii pe care i-am specificat.
5. În continuare, trebuie să calculăm valorile limitelor CI. Și pentru aceasta trebuie să obțineți valoarea medie pentru intervalul selectat. Pentru a face acest lucru, aplicăm din nou funcția „INIMA”. Algoritmul acțiunilor este similar cu cel descris în prima metodă.
6. După ce a primit valoarea „INIMA”, puteți începe să calculați limitele CI. Formulele în sine nu diferă de cele folosite cu „NORME DE ÎNCREDERE”:
   • Bordul din dreapta CI=MEDIE+INCREDEREA STUDENTLOR
   • Left Bound CI=INCREDEREA MEDIE A STUDENTULUI

Concluzie

Arsenalul de instrumente Excel este incredibil de mare și, împreună cu funcțiile comune, programul oferă o mare varietate de funcții speciale care vor face lucrul cu date mult mai ușor. Poate că pașii descriși mai sus pot părea complicați pentru unii utilizatori la prima vedere. Dar după un studiu detaliat al problemei și al succesiunii acțiunilor, totul va deveni mult mai ușor.