Cálculo do intervalo de confianza en Excel

Excel utilízase para realizar diversas tarefas estatísticas, unha delas é o cálculo do intervalo de confianza, que se utiliza como substituto máis axeitado para unha estimación puntual cun pequeno tamaño de mostra.

Queremos notar de inmediato que o propio procedemento para calcular o intervalo de confianza é bastante complicado, con todo, en Excel hai unha serie de ferramentas deseñadas para facilitar esta tarefa. Botámoslles unha ollada.

Cálculo do intervalo de confianza

Precísase un intervalo de confianza para dar unha estimación do intervalo a algúns datos estáticos. O obxectivo principal desta operación é eliminar as incertezas da estimación puntual.

Existen dous métodos para realizar esta tarefa en Microsoft Excel:

• Operador NORMA DE CONFIANZA – úsase nos casos en que se coñece a dispersión;
• Operador CONFIANZA.ESTUDANTEcando se descoñece a varianza.

A continuación analizaremos paso a paso ambos métodos na práctica.

Método 1: declaración TRUST.NORM

Esta función introduciuse por primeira vez no arsenal do programa na edición de Excel 2010 (antes desta versión, foi substituída polo operador "DE CONFIANZA”). O operador está incluído na categoría "estatística".

Fórmula da función NORMA DE CONFIANZA parece así:

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

Como podemos ver, a función ten tres argumentos:

• "Alfa" é un indicador do nivel de significación, que se toma como base para o cálculo. O nivel de confianza calcúlase do seguinte xeito:
  • 1-"Альфа". Esta expresión é aplicable se o valor "Alfa" presentado como un coeficiente. Por exemplo, 1-0,7 0,3 =, onde 0,7=70%/100%.
  • (100-"Альфа")/100. Esta expresión aplicarase se temos en conta o nivel de confianza co valor "Alfa" en porcentaxes. Por exemplo, (100-70) / 100 = 0,3.
• "Desviación estándar" – respectivamente, a desviación típica da mostra de datos analizados.
• "Tamaño" é o tamaño da mostra de datos.

Nota: Para esta función, a presenza dos tres argumentos é un requisito previo.

Operador "DE CONFIANZA”, que se usaba en versións anteriores do programa, contén os mesmos argumentos e realiza as mesmas funcións.

Fórmula da función DE CONFIANZA como segue:

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

Non hai diferenzas na propia fórmula, só o nome do operador é diferente. En Excel 2010 e edicións posteriores, este operador está na categoría Compatibilidade. Nas versións máis antigas do programa, atópase na sección de funcións estáticas.

O límite do intervalo de confianza determínase pola seguinte fórmula:

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

onde Х é o valor medio no intervalo especificado.

Agora vexamos como aplicar estas fórmulas na práctica. Así, temos unha táboa con varios datos de 10 medicións realizadas. Neste caso, a desviación estándar do conxunto de datos é 8.

A nosa tarefa é obter o valor do intervalo de confianza cun nivel de confianza do 95%.

1. En primeiro lugar, seleccione unha cela para mostrar o resultado. Despois prememos no botón "Inserir función" (á esquerda da barra de fórmulas).
2. Ábrese a xanela do asistente de funcións. Ao facer clic na categoría actual de funcións, expande a lista e fai clic na liña da mesma "Estatística".
3. Na lista proposta, fai clic no operador "NORMA DE CONFIANZA", a continuación, prema OK.
4. Veremos unha ventá coa configuración dos argumentos da función, cubrindo a cal prememos o botón OK.
   • no campo "Alfa" indica o nivel de significación. A nosa tarefa supón un nivel de confianza do 95%. Substituíndo este valor na fórmula de cálculo, que consideramos anteriormente, obtemos a expresión: (100-95)/100. Escribimos no campo de argumentos (ou pode escribir inmediatamente o resultado do cálculo igual a 0,05).
   • no campo "std_off" segundo as nosas condicións, escribimos o número 8.
   • no campo "Tamaño", especifique o número de elementos a examinar. No noso caso realizáronse 10 medidas, polo que escribimos o número 10.
5. Para evitar ter que reconfigurar a función cando cambien os datos, pode automatila. Para iso usamos a función "COMPROBAR”. Coloque o punteiro na área de entrada da información do argumento "Tamaño", a continuación, fai clic na icona do triángulo do lado esquerdo da barra de fórmulas e fai clic no elemento "Máis funcións...".
6. Como resultado, abrirase outra xanela do Asistente de funcións. Ao elixir unha categoría "Estatística", fai clic na función "COMPROBAR”, entón OK.
7. A pantalla mostrará outra xanela coa configuración dos argumentos da función, que se usa para determinar o número de celas dun determinado intervalo que conteñen datos numéricos.

   Fórmula da función vERIFICACIÓN está escrito así: =СЧЁТ(Значение1;Значение2;...).

   O número de argumentos dispoñibles para esta función pode ser de ata 255. Aquí pode escribir números específicos, enderezos de celas ou intervalos de celas. Usaremos a última opción. Para iso, fai clic na área de entrada de información para o primeiro argumento, despois, mantendo premido o botón esquerdo do rato, selecciona todas as celas dunha das columnas da nosa táboa (sen contar a cabeceira) e despois preme o botón OK.

8. Como resultado das accións realizadas, o resultado dos cálculos para o operador mostrarase na cela seleccionada NORMA DE CONFIANZA. No noso problema, o seu valor resultou ser igual a 4,9583603.
9. Pero este aínda non é o resultado final da nosa tarefa. A continuación, cómpre calcular o valor medio nun intervalo determinado. Para iso, cómpre empregar a función "CORAZÓN”A que realiza a tarefa de calcular a media nun intervalo de datos especificado.

   A fórmula do operador escríbese así: =СРЗНАЧ(число1;число2;...).

   Seleccione a cela onde pensamos inserir a función e prema o botón "Inserir función".

10. En categoría "Estatística" elixe un operador aburrido "Corazón" e prema OK.
11. En argumentos de función en valor de argumento "Número" especifique o intervalo, que inclúe todas as celas cos valores de todas as medicións. Despois prememos OK.
12. Como resultado das accións realizadas, o valor medio calcularase automaticamente e mostrarase na cela coa función recentemente inserida.
13. Agora necesitamos calcular os límites CI (intervalo de confianza). Comecemos calculando o valor do bordo dereito. Seleccionamos a cela onde queremos mostrar o resultado, e realizamos a suma dos resultados obtidos mediante os operadores “CORAZÓN” e "NORMAS DE CONFIANZA”. No noso caso, a fórmula é así: A14+A16. Despois de tecleo, prema Entrar.
14. Como resultado, o cálculo realizarase e o resultado mostrarase inmediatamente na cela coa fórmula.
15. Despois, de xeito similar, realizamos o cálculo para obter o valor do bordo esquerdo do CI. Só neste caso o valor do resultado "NORMAS DE CONFIANZA” non é necesario sumar, senón restar do resultado obtido mediante o operador "CORAZÓN”. No noso caso, a fórmula é así: =A16-A14.
16. Despois de premer Intro, obteremos o resultado na cela indicada coa fórmula.

Nota: Nos parágrafos anteriores, tentamos describir co máximo de detalle posible todos os pasos e cada función utilizada. Non obstante, todas as fórmulas prescritas pódense escribir xuntas, como parte dunha grande:

• Para determinar o bordo dereito do CI, a fórmula xeral terá o seguinte aspecto:

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

• Do mesmo xeito, para o bordo esquerdo, só en lugar dun plus, cómpre poñer un menos:

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

Método 2: operador TRUST.ESTUDENT

Agora, imos familiarizarnos co segundo operador para determinar o intervalo de confianza − CONFIANZA.ESTUDANTE. Esta función introduciuse no programa hai relativamente pouco tempo, a partir da versión de Excel 2010, e ten como obxectivo determinar o CI do conxunto de datos seleccionado mediante a distribución de Student, cunha varianza descoñecida.

Fórmula da función CONFIANZA.ESTUDANTE como segue:

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

Analicemos a aplicación deste operador no exemplo da mesma táboa. Só agora non coñecemos a desviación estándar segundo as condicións do problema.

1. En primeiro lugar, seleccione a cela onde pretendemos mostrar o resultado. A continuación, fai clic na icona "Inserir función" (á esquerda da barra de fórmulas).
2. Abrirase a xa coñecida xanela do asistente de funcións. Escolle unha categoría "Estatística", a continuación, desde a lista de funcións proposta, prema no operador "ESTUDANTE DE CONFIANZA", entón - OK.
3. Na seguinte xanela, necesitamos configurar os argumentos da función:
   • No "Alfa" como no primeiro método, especifique o valor 0,05 (ou "100-95)/100").
   • Pasemos ao argumento. "std_off". Porque segundo as condicións do problema, o seu valor é descoñecido para nós, necesitamos facer os cálculos axeitados, nos que o operador "STDEV.B”. Fai clic no botón engadir función e despois no elemento "Máis funcións...".
   • Na seguinte xanela do asistente de funcións, seleccione o operador "STDEV.B” na categoría "Estatística" e prema OK.
   • Entramos na xanela de configuración dos argumentos da función, cuxa fórmula é así: =СТАНДОТКЛОН.В(число1;число2;...). Como primeiro argumento, especificamos un intervalo que inclúe todas as celas da columna "Valor" (sen contar a cabeceira).
   • Agora cómpre volver á xanela cos argumentos da función "CONFIANZA.ESTUDANTE”. Para iso, fai clic na inscrición do mesmo nome no campo de entrada da fórmula.
   • Agora imos pasar ao último argumento "Tamaño". Como no primeiro método, aquí pode simplemente especificar un rango de celas ou inserir o operador "COMPROBAR". Escollemos a última opción.
   • Unha vez cubertos todos os argumentos, fai clic no botón OK.
4. A cela seleccionada mostrará o valor do intervalo de confianza segundo os parámetros que especificamos.
5. A continuación, necesitamos calcular os valores dos límites CI. E para iso cómpre obter o valor medio para o rango seleccionado. Para iso, aplicamos de novo a función "CORAZÓN”. O algoritmo de accións é similar ao descrito no primeiro método.
6. Recibindo o valor "CORAZÓN”, pode comezar a calcular os límites CI. As fórmulas en si non son diferentes das utilizadas co "NORMAS DE CONFIANZA”:
   • Borde dereito CI=PROMEDIO+CONFIANZA DO ESTUDANTE
   • Límite á esquerda CI=CONFIANZA MEDIA DO ESTUDANTE

Conclusión

O arsenal de ferramentas de Excel é incriblemente grande e, xunto coas funcións comúns, o programa ofrece unha gran variedade de funcións especiais que facilitarán moito o traballo con datos. Quizais os pasos descritos anteriormente poidan parecer complicados a algúns usuarios a primeira vista. Pero despois dun estudo detallado do problema e da secuencia de accións, todo será moito máis sinxelo.