INTERVALOS DE CONFIANZA EN EXCEL

Un dos métodos para resolver problemas estatísticos é o cálculo do intervalo de confianza. Utilízase como estimación de punto alternativa preferida cun tamaño de mostra pequeno. Debe notarse que o proceso de cálculo do intervalo de confianza en si é bastante complicado. Pero as ferramentas do programa Excel fan algo máis fácil. Descubrimos como se fai isto na práctica.

Vexa tamén: Funcións estatísticas en Excel

Procedemento de cálculo

Este método úsase para a estimación de intervalos de varias cantidades estatísticas. A principal tarefa deste cálculo é eliminar as incertezas da estimación puntual.

En Excel, hai dúas opcións principais para realizar cálculos usando este método: cando se coñece a varianza e cando se descoñece. No primeiro caso, a función utilízase para os cálculos. TRUST.NORM, e no segundo - TRUST.STUDENT.

Método 1: Función CONFIDENCIA.NORM

Operador TRUST.NORMrelacionado co grupo estatístico de funcións, apareceu por primeira vez en Excel 2010. En versións anteriores deste programa, o seu análogo é usado CONFIANZA. A tarefa deste operador é calcular o intervalo de confianza cunha distribución normal para a poboación media.

A súa sintaxe é a seguinte:

= CONFIANZA. NORM (alfa; estándar; tamaño)

"Alfa" - un argumento que indica o nivel de importancia que se usa para calcular o nivel de confianza. O nivel de confianza é a seguinte expresión:

(1- "Alpha") * 100

"Desviación estándar" - Este é un argumento cuxa esencia está clara no nome. Esta é a desviación estándar da mostra proposta.

"Tamaño" - O argumento que determina o tamaño da mostra.

Requírense todos os argumentos deste operador.

Función CONFIANZA Ten exactamente os mesmos argumentos e posibilidades que o anterior. A súa sintaxe é:

= CONFIANZA (alfa; estándar; tamaño)

Como podes ver, as diferenzas son só en nome do operador. A función especificada queda para compatibilidade con Excel 2010 e versións máis recentes nunha categoría especial. "Compatibilidade". Nas versións de Excel 2007 e versións anteriores, está presente no grupo principal de operadores estatísticos.

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

X + (-) CONFIANZA. NORM

Onde X - é o valor medio da mostra, que se atopa no medio do intervalo seleccionado.

Agora vexamos como calcular o intervalo de confianza nun exemplo específico. Realizáronse 12 probas, resultado do cal obtivéronse varios resultados listados na táboa. Esta é a nosa totalidade. A desviación estándar é 8. Necesitamos calcular un intervalo de confianza nun nivel de confianza do 97%.

Seleccione a cela onde se amosará o resultado do procesamento de datos. Fai clic no botón "Inserir función".

Aparece Asistente de funcións. Ir á categoría "Estatística" e selecciona o nome DOVERT.NORM. Despois faremos clic no botón. "OK".

Abrirase a ventá de argumento. Os seus campos corresponden naturalmente aos nomes dos argumentos.
Estableza o cursor no primeiro campo - "Alfa". Aquí debemos indicar o nivel de importancia. Como recordamos, o noso nivel de confianza é do 97%. Ao mesmo tempo, dixemos que se calcula do seguinte xeito:
(1- "Alpha") * 100
Entón, para calcular o nivel de importancia, é dicir, para determinar o valor "Alfa" Debe aplicarse a seguinte fórmula:
(1 nivel de confianza) / 100
É dicir, substituíndo o valor, obtemos:
(1-97)/100
Por cálculos sinxelos, descubrimos o argumento "Alfa" igual 0,03. Introduza este valor no campo.
Como sabes, a condición da desviación estándar é 8. Polo tanto, no campo "Desviación estándar" basta con escribir este número.
No campo "Tamaño" ten que introducir o número de elementos das probas. Como os recordamos 12. Pero para automatizar a fórmula e non editalo cada vez que se realiza unha nova proba, fixemos este valor non cun número ordinario, senón coa axuda dun operador CUENTA. Polo tanto, configure o cursor no campo "Tamaño"e logo fai clic no triángulo situado á esquerda da barra de fórmulas.
Aparece unha lista das funcións usadas recentemente. Se é operador CUENTA usado recentemente por vostede, debería estar nesta lista. Neste caso, só tes que facer clic no seu nome. No caso contrario, se non o atopas, pasa polo ítem "Outras funcións ...".

Aparece xa familiar para nós Asistente de funcións. Mova ao grupo de novo "Estatística". Escolla o nome "ACCOUNT". Facemos clic no botón "OK".

Aparece a ventá de argumento da afirmación anterior. Esta función pretende calcular o número de celas no intervalo especificado que conteñen valores numéricos. A súa sintaxe é a seguinte:
= COUNT (valor1; valor2; ...)
Grupo de argumentos "Valores" é unha referencia ao intervalo no que cómpre calcular o número de celas cubertas con datos numéricos. En total pode haber ata 255 tales argumentos, pero no noso caso só é necesario.
Sitúe o cursor no campo "Valor1" e, mantendo o botón esquerdo do rato, selecciona o intervalo que contén o noso conxunto na folla. Entón mostrará o seu enderezo no campo. Facemos clic no botón "OK".

Despois diso, a aplicación realizará o cálculo e mostrará o resultado na cela onde se atopa. No noso caso particular, a fórmula resultou ser da seguinte forma:
= CONFIANZA. NORM (0,03; 8; CUENTA (B2: B13))
O resultado global dos cálculos foi 5,011609.

Pero iso non é todo. Como recordamos, o límite do intervalo de confianza calcúlase engadindo e subtraindo do valor medio da mostra o resultado do cálculo TRUST.NORM. Deste xeito calcúlanse os límites dereito e esquerdo do intervalo de confianza respectivamente. O valor medio da mostra pode calcularse mediante o operador MEDIA.
Este operador está deseñado para calcular a media aritmética do intervalo de números seleccionado. Ten a seguinte sintaxe bastante sinxela:
= MEDIA (número1; número2; ...)
Argumento "Número" pode ser un valor numérico separado ou unha referencia a celas ou incluso a intervalos enteiros que os conteñen.
Así, selecciona a cela na que se amosará o cálculo do valor medio e fai clic no botón "Inserir función".

Abre Asistente de funcións. Volve á categoría "Estatística" e elixe entre a lista o nome "SRZNACH". Como sempre, facemos clic no botón "OK".

Iníciase a fiestra do argumento. Sitúe o cursor no campo "Número1" e premendo o botón esquerdo do rato, selecciona todo o rango de valores. Despois de que as coordenadas aparecen no campo, faga clic no botón "OK".

Despois diso MEDIA mostra o resultado do cálculo no elemento de folla.

Calculamos o límite dereito do intervalo de confianza. Para facelo, selecciona unha cela separada, pon o signo "=" e engade o contido dos elementos da folla, no que se atopan os resultados dos cálculos das funcións MEDIA e TRUST.NORM. Para realizar o cálculo, prema a tecla Intro. No noso caso, obtivemos a seguinte fórmula:
= F2 + A16
O resultado do cálculo: 6,953276

Do mesmo xeito, calculamos o límite esquerdo do intervalo de confianza, só que esta vez do resultado do cálculo MEDIA restar o resultado do cálculo do operador TRUST.NORM. Resulta a fórmula do noso exemplo do seguinte tipo:
= F2-A16
O resultado do cálculo: -3,06994

Tentamos describir detalladamente todos os pasos para calcular o intervalo de confianza, así que describimos cada fórmula detalladamente. Pero todas as accións pódense combinar nunha fórmula. O cálculo do límite dereito do intervalo de confianza pode escribirse como:
= MEDIOS (B2: B13) + TRUST.NORM (0,03; 8; COUNT (B2: B13))

Un cálculo similar do bordo esquerdo terá o seguinte aspecto:
= MEDIA (B2: B13) - CONFIANZA NORM (0,03; 8; COUNT (B2: B13))

Método 2: Función FESTUDENTE DE CONFIANZA

Ademais, en Excel hai outra función asociada ao cálculo do intervalo de confianza - TRUST.STUDENT. Apareceu só a partir de Excel 2010. Este operador realiza o cálculo do intervalo de confianza da poboación total usando a distribución do estudante. É moi cómodo usalo no caso de que se descoñece a desviación e, en consecuencia, a desviación estándar. A sintaxe do operador é:

= PROBA DE CONFIANZA (alfa; estándar; tamaño)

Como podes ver, os nomes dos operadores neste caso permaneceron inalterados.

Vexamos como calcular os límites do intervalo de confianza cunha desviación estándar descoñecida usando o exemplo da mesma totalidade que consideramos no método anterior. O nivel de confianza, como a última vez, toma o 97%.

Seleccione a cela na que se fará o cálculo. Facemos clic no botón "Inserir función".

No aberto Asistente de funcións ir á categoría "Estatística". Escolla un nome "DOVERT.STUUDENT". Facemos clic no botón "OK".

Iníciase a xanela de argumentos do operador especificado.
No campo "Alfa", tendo en conta que o nivel de confianza é do 97%, anotamos o número 0,03. A segunda vez nos principios do cálculo deste parámetro non se deterá.
Despois colocar o cursor no campo "Desviación estándar". Neste momento, esta cifra é descoñecida para nós e é preciso calcular. Isto faise usando unha función especial - STANDOWCLON.V. Para chamar á ventá deste operador, fai clic no triángulo situado á esquerda da barra de fórmulas. Se na lista aberta non atopamos o nome desexado, pasa polo elemento "Outras funcións ...".

Inicia Asistente de funcións. Mover á categoría "Estatística" e anote o nome nel "STANDOTKLON.V". A continuación, faga clic no botón. "OK".

Abrirase a ventá de argumento. Tarefa de operador STANDOWCLON.V é a determinación da desviación estándar ao facer a mostraxe. A súa sintaxe é:
= STDEV.V (número 1; número2; ...)
Non é difícil adiviñar ese argumento "Número" é o enderezo do elemento de selección. Se a mostra colócase nunha única matriz, entón, usando só un argumento, pode facer unha referencia a este intervalo.
Sitúe o cursor no campo "Número1" e, como sempre, mantendo o botón esquerdo do rato, seleccione o conxunto. Despois de que as coordenadas cheguen ao campo, non se apresuraron a premer o botón "OK", xa que o resultado será incorrecto. Primeiro necesitamos volver á ventá de argumento do operador TRUST.STUDENTpara facer o argumento final. Para facelo, fai clic no nome correspondente na barra de fórmulas.

A ventá de argumentos da función familiar ábrese de novo. Sitúe o cursor no campo "Tamaño". De novo, fai clic no triángulo xa familiar para ir á elección dos operadores. Como entendes, necesitamos un nome. "ACCOUNT". Xa que usamos esta función nos cálculos do método anterior, está presente nesta lista, polo que basta con premer nela. Se non o atopa, proceda segundo o algoritmo descrito no primeiro método.

Bater a ventá de argumento CUENTApoñer o cursor no campo "Número1" e co botón do rato presionado, seleccione o conxunto. A continuación, faga clic no botón. "OK".

Despois diso, o programa calcula e mostra o valor do intervalo de confianza.

Para determinar os límites, necesitaremos de novo calcular o valor medio da mostra. Pero, dado que o algoritmo de cálculo usa a fórmula MEDIA o mesmo que no método anterior, e aínda que o resultado non cambiou, non o imos a detalle detalladamente por segunda vez.

Engadindo os resultados do cálculo MEDIA e TRUST.STUDENT, obtemos o límite dereito do intervalo de confianza.

Retirar os resultados do cálculo do operador MEDIA resultado de cálculo TRUST.STUDENT, temos o límite esquerdo do intervalo de confianza.

Se o cálculo está escrito nunha fórmula, entón o cálculo do bordo dereito no noso caso será semellante ao seguinte:
= MEDIA (B2: B13) + PRUEBA DE CONFIANZA (0,03; CLONO ESTÁNDAR. B (B2: B13); CUENTA (B2: B13))

En consecuencia, a fórmula para calcular o bordo esquerdo terá o seguinte aspecto:
= MEDIA (B2: B13) -DVERIT.TUDENT (0,03; CLON STANDARD B (B2: B13); CUENTA (B2: B13))

Como podes ver, as ferramentas de Excel permiten simplificar significativamente o cálculo do intervalo de confianza e os seus límites. Para estes efectos, empréganse operadores separados para mostras nas que se coñece e descoñécese a varianza.