Càlcul de l'interval de confiança en Excel

Excel s'utilitza per realitzar diverses tasques estadístiques, una de les quals és el càlcul de l'interval de confiança, que s'utilitza com a substitució més adequada d'una estimació puntual amb una mida de mostra petita.

Volem assenyalar de seguida que el mateix procediment per calcular l'interval de confiança és bastant complicat, però a Excel hi ha una sèrie d'eines dissenyades per facilitar aquesta tasca. Fem-los una ullada.

Càlcul de l'interval de confiança

Es necessita un interval de confiança per donar una estimació d'interval a algunes dades estàtiques. L'objectiu principal d'aquesta operació és eliminar les incerteses de l'estimació puntual.

Hi ha dos mètodes per realitzar aquesta tasca a Microsoft Excel:

• Operador NORMA DE CONFIANÇA – s'utilitza en els casos en què la dispersió és coneguda;
• Operador CONFIANÇA.ESTUDIANTquan es desconeix la variància.

A continuació analitzarem pas a pas ambdós mètodes a la pràctica.

Mètode 1: declaració TRUST.NORM

Aquesta funció es va introduir per primera vegada a l'arsenal del programa a l'edició d'Excel 2010 (abans d'aquesta versió, es va substituir per l'operador "CONFIABLE"). L'operador està inclòs a la categoria "estadística".

Fórmula de la funció NORMA DE CONFIANÇA es veu així:

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

Com podem veure, la funció té tres arguments:

• "Alfa" és un indicador del nivell de significació, que es pren com a base per al càlcul. El nivell de confiança es calcula de la següent manera:
  • 1-"Альфа". Aquesta expressió és aplicable si el valor "Alfa" presentat com a coeficient. Per exemple, 1-0,7 0,3 =, on 0,7=70%/100%.
  • (100-"Альфа")/100. Aquesta expressió s'aplicarà si tenim en compte el nivell de confiança amb el valor "Alfa" en percentatges. Per exemple, (100-70) / 100 = 0,3.
• "Desviació estàndar" – respectivament, la desviació estàndard de la mostra de dades analitzada.
• "Mida" és la mida de la mostra de dades.

Nota: Per a aquesta funció, la presència dels tres arguments és un requisit previ.

Operador "CONFIABLE", que s'utilitzava en versions anteriors del programa, conté els mateixos arguments i realitza les mateixes funcions.

Fórmula de la funció DE CONFIANÇA com segueix:

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

No hi ha diferències en la fórmula en si, només el nom de l'operador és diferent. A Excel 2010 i edicions posteriors, aquest operador es troba a la categoria Compatibilitat. En versions anteriors del programa, es troba a la secció de funcions estàtiques.

El límit de l'interval de confiança es determina mitjançant la fórmula següent:

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

where Х és el valor mitjà de l'interval especificat.

Ara vegem com aplicar aquestes fórmules a la pràctica. Així doncs, tenim una taula amb diverses dades de 10 mesures preses. En aquest cas, la desviació estàndard del conjunt de dades és 8.

La nostra tasca és obtenir el valor de l'interval de confiança amb un nivell de confiança del 95%.

1. En primer lloc, seleccioneu una cel·la per mostrar el resultat. Després fem clic al botó "Funció d'inserció" (a l'esquerra de la barra de fórmules).
2. S'obre la finestra de l'assistent de funcions. En fer clic a la categoria actual de funcions, amplieu la llista i feu clic a la línia que hi ha "Estadística".
3. A la llista proposada, feu clic a l'operador “NORMA DE CONFIANÇA”, Premeu OK.
4. Veurem una finestra amb la configuració dels arguments de la funció, omplint la qual premem el botó OK.
   • en el camp "Alfa" indica el nivell de significació. La nostra tasca suposa un nivell de confiança del 95%. Substituint aquest valor a la fórmula de càlcul, que hem considerat anteriorment, obtenim l'expressió: (100-95)/100. Ho escrivim al camp d'argument (o podeu escriure immediatament el resultat del càlcul igual a 0,05).
   • en el camp "std_off" segons les nostres condicions, escrivim el número 8.
   • al camp "Mida", especifiqueu el nombre d'elements a examinar. En el nostre cas, s'han fet 10 mesures, així que escrivim el número 10.
5. Per evitar haver de reconfigurar la funció quan canvien les dades, podeu automatitzar-la. Per a això fem servir la funció “VERIFICAR”. Posa el punter a l'àrea d'entrada de la informació de l'argument "Mida", feu clic a la icona del triangle a la part esquerra de la barra de fórmules i feu clic a l'element "Més funcions...".
6. Com a resultat, s'obrirà una altra finestra de l'assistent de funcions. Escollint una categoria "Estadística", feu clic a la funció "VERIFICAR”, llavors d'acord.
7. La pantalla mostrarà una altra finestra amb la configuració dels arguments de la funció, que s'utilitza per determinar el nombre de cel·les d'un interval determinat que contenen dades numèriques.

   Fórmula de la funció VERIFICACIÓ està escrit així: =СЧЁТ(Значение1;Значение2;...).

   El nombre d'arguments disponibles per a aquesta funció pot ser de fins a 255. Aquí podeu escriure nombres específics, adreces de cel·les o rangs de cel·les. Farem servir l'última opció. Per fer-ho, feu clic a l'àrea d'entrada d'informació del primer argument, després, mantenint premut el botó esquerre del ratolí, seleccioneu totes les cel·les d'una de les columnes de la nostra taula (sense comptar la capçalera) i després premeu el botó OK.

8. Com a resultat de les accions realitzades, el resultat dels càlculs per a l'operador es mostrarà a la cel·la seleccionada NORMA DE CONFIANÇA. En el nostre problema, el seu valor va resultar ser igual a 4,9583603.
9. Però aquest encara no és el resultat final de la nostra tasca. A continuació, heu de calcular el valor mitjà durant un interval determinat. Per fer-ho, heu d'utilitzar la funció "COR”A que realitza la tasca de calcular la mitjana en un interval de dades especificat.

   La fórmula de l'operador s'escriu així: =СРЗНАЧ(число1;число2;...).

   Seleccioneu la cel·la on volem inserir la funció i premeu el botó "Funció d'inserció".

10. En categoria "Estadística" tria un operador avorrit “CO” and clic OK.
11. En arguments de funció en valor d'argument "Número" especifiqueu l'interval, que inclou totes les cel·les amb els valors de totes les mesures. Llavors fem clic BÉ.
12. Com a resultat de les accions realitzades, el valor mitjà es calcularà automàticament i es mostrarà a la cel·la amb la nova funció inserida.
13. Ara hem de calcular els límits CI (interval de confiança). Comencem calculant el valor de la vora dreta. Seleccionem la cel·la on volem mostrar el resultat, i realitzem la suma dels resultats obtinguts mitjançant els operadors “COR” i "NORMES DE CONFIANÇA”. En el nostre cas, la fórmula és així: A14+A16. Després d'escriure-ho, premeu Enter.
14. Com a resultat, es realitzarà el càlcul i el resultat es mostrarà immediatament a la cel·la amb la fórmula.
15. Després, de manera semblant, realitzem el càlcul per obtenir el valor de la vora esquerra de l'IC. Només en aquest cas el valor del resultat "NORMES DE CONFIANÇA” no cal sumar, sinó restar del resultat obtingut mitjançant l'operador "COR”. En el nostre cas, la fórmula és així: =A16-A14.
16. Després de prémer Intro, obtindrem el resultat a la cel·la donada amb la fórmula.

Nota: En els paràgrafs anteriors, hem intentat descriure amb el màxim de detall possible tots els passos i cada funció utilitzada. Tanmateix, totes les fórmules prescrites es poden escriure juntes, com a part d'una de gran:

• Per determinar la vora dreta de l'IC, la fórmula general serà així:

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

• De la mateixa manera, per a la vora esquerra, només en lloc d'un plus, cal posar un menys:

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

Mètode 2: operador TRUST.STUDENT

Ara, familiaritzem-nos amb el segon operador per determinar l'interval de confiança − CONFIANÇA.ESTUDIANT. Aquesta funció s'ha introduït al programa fa relativament poc temps, a partir de la versió d'Excel 2010, i té com a objectiu determinar el CI del conjunt de dades seleccionat mitjançant la distribució de Student, amb una variància desconeguda.

Fórmula de la funció CONFIANÇA.ESTUDIANT com segueix:

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

Analitzem l'aplicació d'aquest operador a l'exemple de la mateixa taula. Només ara no coneixem la desviació estàndard segons les condicions del problema.

1. Primer, seleccioneu la cel·la on volem mostrar el resultat. A continuació, feu clic a la icona "Funció d'inserció" (a l'esquerra de la barra de fórmules).
2. S'obrirà la ja coneguda finestra de l'assistent de funcions. Trieu una categoria "Estadística", després de la llista de funcions proposada, feu clic a l'operador “ESTUDIANT DE CONFIANÇA”, llavors - OK.
3. A la finestra següent, hem de configurar els arguments de la funció:
   • A la "Alfa" com en el primer mètode, especifiqueu el valor 0,05 (o “100-95)/100”).
   • Passem a l'argument. "std_off". Com que segons les condicions del problema, el seu valor és desconegut per a nosaltres, hem de fer els càlculs adequats, en què l'operador "STDEV.B”. Feu clic al botó d'afegir funció i després a l'element "Més funcions...".
   • A la següent finestra de l'assistent de funcions, seleccioneu l'operador "STDEV.B” en categoria "Estadística" and clic OK.
   • Entrem a la finestra de configuració dels arguments de la funció, la fórmula de la qual és així: =СТАНДОТКЛОН.В(число1;число2;...). Com a primer argument, especifiquem un interval que inclou totes les cel·les de la columna "Valor" (sense comptar la capçalera).
   • Ara heu de tornar a la finestra amb els arguments de la funció "CONFIANÇA.ESTUDIANT”. Per fer-ho, feu clic a la inscripció del mateix nom al camp d'entrada de la fórmula.
   • Ara passem a l'últim argument "Mida". Com en el primer mètode, aquí podeu simplement especificar un rang de cel·les o inserir l'operador “COMPROVA”. Triem l'última opció.
   • Un cop emplenats tots els arguments, feu clic al botó OK.
4. La cel·la seleccionada mostrarà el valor de l'interval de confiança segons els paràmetres que hem especificat.
5. A continuació, hem de calcular els valors dels límits CI. I per a això cal obtenir el valor mitjà de l'interval seleccionat. Per fer-ho, tornem a aplicar la funció “COR”. L'algorisme d'accions és similar al descrit en el primer mètode.
6. Havent rebut el valor "COR”, podeu començar a calcular els límits CI. Les fórmules en si no són diferents de les utilitzades amb el "NORMES DE CONFIANÇA”:
   • Vora dreta CI=MITJANA+CONFIANÇA DE L'ALUMNE
   • Límit esquerre CI=CONFIANÇA MITJANA DE L'ESTUDIANT

Conclusió

L'arsenal d'eines d'Excel és increïblement gran i, juntament amb les funcions comunes, el programa ofereix una gran varietat de funcions especials que facilitaran molt el treball amb dades. Potser els passos descrits anteriorment poden semblar complicats per a alguns usuaris a primera vista. Però després d'un estudi detallat del problema i de la seqüència d'accions, tot serà molt més fàcil.