Cálculo del intervalo de confianza en Excel

Excel se utiliza para realizar diversas tareas estadísticas, una de las cuales es el cálculo del intervalo de confianza, que se utiliza como el reemplazo más adecuado para una estimación puntual con un tamaño de muestra pequeño.

Queremos señalar de inmediato que el procedimiento mismo para calcular el intervalo de confianza es bastante complicado, sin embargo, en Excel hay una serie de herramientas diseñadas para facilitar esta tarea. Echemos un vistazo a ellos.

Cálculo del intervalo de confianza

Se necesita un intervalo de confianza para dar una estimación de intervalo a algunos datos estáticos. El objetivo principal de esta operación es eliminar las incertidumbres de la estimación puntual.

Hay dos métodos para realizar esta tarea en Microsoft Excel:

• Operador NORMA DE CONFIANZA – se utiliza en los casos en que se conoce la dispersión;
• Operador ESTUDIANTE DE CONFIANZAcuando la varianza es desconocida.

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

Método 1: instrucción TRUST.NORM

Esta función se introdujo por primera vez en el arsenal del programa en la edición de Excel 2010 (antes de esta versión, fue reemplazada por el operador “DE CONFIANZA”). El operador está incluido en la categoría “estadística”.

Fórmula de función NORMA DE CONFIANZA tiene este aspecto:

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

Como podemos ver, la función tiene tres argumentos:

• "Alpha" es un indicador del nivel de significación, que se toma como base para el cálculo. El nivel de confianza se calcula de la siguiente manera:
  • 1-"Альфа". Esta expresión es aplicable si el valor "Alpha" presentado como un coeficiente. Por ejemplo, 1-0,7 0,3 =, donde 0,7=70%/100%.
  • (100-"Альфа")/100. Esta expresión se aplicará si consideramos el nivel de confianza con el valor "Alpha" en porcentajes Por ejemplo, (100-70) / 100 = 0,3.
• "Desviación Estándar" – respectivamente, la desviación estándar de la muestra de datos analizada.
• "Tamaño" es el tamaño de la muestra de datos.

Nota: Para esta función, la presencia de los tres argumentos es un requisito previo.

Operador “DE CONFIANZA”, que se utilizó en versiones anteriores del programa, contiene los mismos argumentos y realiza las mismas funciones.

Fórmula de función CONFIANZA como sigue:

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

No hay diferencias en la fórmula en sí, solo el nombre del operador es diferente. En Excel 2010 y ediciones posteriores, este operador se encuentra en la categoría Compatibilidad. En versiones anteriores del programa, se encuentra en la sección de funciones estáticas.

El límite del intervalo de confianza está determinado por la siguiente fórmula:

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

donde Х es el valor promedio sobre el rango especificado.

Ahora veamos cómo aplicar estas fórmulas en la práctica. Entonces, tenemos una tabla con varios datos de 10 medidas tomadas. En este caso, la desviación estándar del conjunto de datos es 8.

Nuestra tarea es obtener el valor del intervalo de confianza con un nivel de confianza del 95%.

1. En primer lugar, seleccione una celda para mostrar el resultado. Luego hacemos clic en el botón "Función de inserción" (a la izquierda de la barra de fórmulas).
2. Se abre la ventana Asistente de funciones. Al hacer clic en la categoría actual de funciones, expanda la lista y haga clic en la línea en ella "Estadístico".
3. En la lista propuesta, haga clic en el operador “NORMA DE CONFIANZA”, entonces presione OK.
4. Veremos una ventana con la configuración de los argumentos de la función, llenando la cual presionamos el botón OK.
   • en el campo "Alpha" indicar el nivel de significación. Nuestra tarea asume un nivel de confianza del 95%. Sustituyendo este valor en la fórmula de cálculo, que consideramos anteriormente, obtenemos la expresión: (100-95)/100. Lo escribimos en el campo de argumento (o puede escribir inmediatamente el resultado del cálculo igual a 0,05).
   • en el campo "std_off" según nuestras condiciones, escribimos el número 8.
   • en el campo "Tamaño", especifique el número de elementos a examinar. En nuestro caso se tomaron 10 medidas, por lo que escribimos el número 10.
5. Para evitar tener que reconfigurar la función cuando cambian los datos, puede automatizarla. Para ello utilizamos la función “CONTROLAR". Coloque el puntero en el área de entrada de la información del argumento. "Tamaño", luego haga clic en el ícono del triángulo en el lado izquierdo de la barra de fórmulas y haga clic en el elemento "Más características…".
6. Como resultado, se abrirá otra ventana del Asistente de funciones. Al elegir una categoría "Estadístico", haga clic en la función “CONTROLAR", entonces OK.
7. La pantalla mostrará otra ventana con la configuración de los argumentos de la función, que se utiliza para determinar el número de celdas en un rango dado que contienen datos numéricos.

   Fórmula de función CHEQUE esta escrito asi: =СЧЁТ(Значение1;Значение2;...).

   El número de argumentos disponibles para esta función puede ser de hasta 255. Aquí puede escribir números específicos, direcciones de celdas o rangos de celdas. Usaremos la última opción. Para hacer esto, haga clic en el área de entrada de información para el primer argumento, luego, manteniendo presionado el botón izquierdo del mouse, seleccione todas las celdas de una de las columnas de nuestra tabla (sin contar el encabezado) y luego presione el botón OK.

8. Como resultado de las acciones realizadas, el resultado de los cálculos para el operador se mostrará en la celda seleccionada NORMA DE CONFIANZA. En nuestro problema, su valor resultó ser igual a 4,9583603.
9. Pero este no es todavía el resultado final de nuestra tarea. A continuación, debe calcular el valor promedio durante un intervalo determinado. Para hacer esto, necesita usar la función “CORAZÓN"Objeto que realiza la tarea de calcular el promedio sobre un rango específico de datos.

   La fórmula del operador se escribe así: =СРЗНАЧ(число1;число2;...).

   Seleccione la celda donde planeamos insertar la función y presione el botón "Función de inserción".

10. En categoria "Estadístico" elegir un operador aburrido "CORAZÓN" y haga clic OK.
11. En argumentos de función en valor de argumento "Número" especifique el rango, que incluye todas las celdas con los valores de todas las medidas. Luego hacemos clic BUENO.
12. Como resultado de las acciones realizadas, el valor promedio se calculará automáticamente y se mostrará en la celda con la función recién insertada.
13. Ahora necesitamos calcular los límites del IC (intervalo de confianza). Empecemos por calcular el valor del borde derecho. Seleccionamos la celda donde queremos visualizar el resultado, y realizamos la suma de los resultados obtenidos mediante los operadores “CORAZÓN" y "NORMAS DE CONFIANZA”. En nuestro caso, la fórmula se ve así: A14+A16. Después de escribirlo, presione Participar.
14. Como resultado, se realizará el cálculo y el resultado se mostrará inmediatamente en la celda con la fórmula.
15. Luego, de manera similar, realizamos el cálculo para obtener el valor del borde izquierdo del IC. Solo en este caso el valor del resultado”NORMAS DE CONFIANZA” no necesita sumar, sino restar del resultado obtenido usando el operador “CORAZÓN". En nuestro caso, la fórmula se ve así: =A16-A14.
16. Después de presionar Enter, obtendremos el resultado en la celda dada con la fórmula.

Nota: En los párrafos anteriores, tratamos de describir con el mayor detalle posible todos los pasos y cada función utilizada. Sin embargo, todas las fórmulas prescritas se pueden escribir juntas, como parte de una grande:

• Para determinar el borde derecho del CI, la fórmula general se verá así:

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

• Del mismo modo, para el borde izquierdo, solo que en lugar de un signo más, debe poner un signo menos:

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

Método 2: Operador TRUST.STUDENT

Ahora, familiaricémonos con el segundo operador para determinar el intervalo de confianza: ESTUDIANTE DE CONFIANZA. Esta función se introdujo en el programa hace relativamente poco tiempo, a partir de la versión de Excel 2010, y tiene como objetivo determinar el IC del conjunto de datos seleccionado utilizando la distribución de Student, con una varianza desconocida.

Fórmula de función ESTUDIANTE DE CONFIANZA como sigue:

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

Analicemos la aplicación de este operador en el ejemplo de la misma tabla. Solo que ahora no conocemos la desviación estándar según las condiciones del problema.

1. Primero, seleccione la celda donde planeamos mostrar el resultado. Luego haga clic en el icono "Función de inserción" (a la izquierda de la barra de fórmulas).
2. Se abrirá la ya conocida ventana Asistente de funciones. Elige una categoría "Estadístico", luego de la lista propuesta de funciones, haga clic en el operador “ESTUDIANTE DE CONFIANZA”, después - OK.
3. En la siguiente ventana, necesitamos configurar los argumentos de la función:
   • En "Alpha" como en el primer método, especifique el valor 0,05 (o “100-95)/100”).
   • Pasemos al argumento. "std_off". Debido a que de acuerdo a las condiciones del problema, su valor nos es desconocido, necesitamos hacer los cálculos correspondientes, en los que el operador “DESVEST.B”. Haga clic en el botón Agregar función y luego en el elemento "Más características…".
   • En la siguiente ventana del asistente de funciones, seleccione el operador "DESVEST.B” en categoría "Estadístico" y haga clic OK.
   • Entramos en la ventana de configuración de argumentos de función, cuya fórmula se ve así: =СТАНДОТКЛОН.В(число1;число2;...). Como primer argumento, especificamos un rango que incluye todas las celdas en la columna "Valor" (sin contar el encabezado).
   • Ahora necesita volver a la ventana con los argumentos de la función "ESTUDIANTE DE CONFIANZA”. Para hacer esto, haga clic en la inscripción del mismo nombre en el campo de entrada de fórmula.
   • Ahora pasemos al último argumento "Tamaño". Como en el primer método, aquí puede simplemente especificar un rango de celdas o insertar el operador "CHEQUE". Elegimos la última opción.
   • Una vez completados todos los argumentos, haga clic en el botón OK.
4. La celda seleccionada mostrará el valor del intervalo de confianza según los parámetros que especificamos.
5. A continuación, necesitamos calcular los valores de los límites de CI. Y para esto necesita obtener el valor promedio para el rango seleccionado. Para ello, aplicamos de nuevo la función “CORAZÓN". El algoritmo de acciones es similar al descrito en el primer método.
6. Habiendo recibido el valor “CORAZÓN", puede comenzar a calcular los límites de CI. Las fórmulas en sí no son diferentes de las que se utilizan con el “NORMAS DE CONFIANZA”:
   • Borde derecho IC=PROMEDIO+CONFIANZA DEL ESTUDIANTE
   • Límite izquierdo IC=PROMEDIO-CONFIANZA DEL ESTUDIANTE

Conclusión

El arsenal de herramientas de Excel es increíblemente grande y, junto con las funciones comunes, el programa ofrece una amplia variedad de funciones especiales que harán que trabajar con datos sea mucho más fácil. Quizás los pasos descritos anteriormente puedan parecer complicados para algunos usuarios a primera vista. Pero después de un estudio detallado del problema y la secuencia de acciones, todo será mucho más fácil.