Calculadora: Tamaño muestra en universos grandes

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Al trabajar con estadística o en investigación nos será muy útil la calculadora: Tamaño muestra en universos grandes.

Calculadora: Tamaño muestra en universos grandes

– El Tamaño de la población o Universo, es el número total de posibles encuestados.

– El Margen de error, es el error que estamos dispuestos a aceptar de equivocarnos al seleccionar nuestra muestra.

– El Nivel de confianza, es el riesgo que aceptamos de equivocarnos al presentar nuestros resultados (también se puede denominar grado o nivel de seguridad).

– El Tamaño de la muestra, es el número de observaciones que la componen.

– El Valor Z, es el nivel de confianza.

  • ENCABEZADOS O RÓTULOS

Insertamos los encabezados o rótulos que definen la entrada de datos.

  1. Ir a: Celda D7 / Escribir: tamaño población. Define el valor de la celda E7.

  2. Ir a: Celda D9 / Escribir: Margen de error. Define el valor de la celda E9.

  3. Ir a: Celda D11 / Escribir: Nivel de confianza. Define el valor de la celda E11.

  4. Ir a: Celda D13 / Escribir: Tamaño de muestra. Define el valor de la celda E13.

  5. Ir a: Celda B16 / Escribir: Población / Error. Define las columnas y filas de la tabla.

  6. Ir a: Celda H4 / Escribir: Valor Z. Define los valores de la columna.

  7. Ir a: Celda I4 / Escribir: Varianza. Define los valores de la columna.

Hoja Excel con encabezados o rótulos
  • ENTRADA DE DATOS

Insertamos los valores y fórmula que definen el tamaño de la muestra.

  1. Ir a: Celda E7 / Escribir: 18000. O el valor deseado para el tamaño de la población.

  2. Ir a: Celda E9 / Escribir: 2%. O el margen de error deseado.

  3. Ir a: Celda E11 / Escribir: 98%. O el nivel de confianza deseado.

  4. Ir a: Celda E13 / Escribir el tamaño de la muestra:

    =(E7)/(1+((E9*E9)*(E7-1))/(DISTR.T.INV(1-E11;1000000)^2*0,5^2))
Hoja Excel con entrada de datos
  • VALOR Z

Insertamos los valores Z como los niveles de confianza, escribiéndolos o aplicando serie de relleno.

  1. Ir a: Celda H5 / Escribir: 90%.

  2. Ir a: Celda H6 / Escribir: 91%.

  3. Ir a: Celda H7 / Escribir: 92%.

  4. Ir a: Celda H8 / Escribir: 93%.

  5. Ir a: Celda H9 / Escribir: 94%.

  6. Ir a: Celda H10 / Escribir: 95%.

  7. Ir a: Celda H11 / Escribir: 96%.

  8. Ir a: Celda H12 / Escribir: 97%.

  9. Ir a: Celda H13 / Escribir: 98%.

  10. Ir a: Celda H14 / Escribir: 99%.

Hoja Excel con valor z
  • VARIANZA

Insertamos la fórmula de la varianza para el nivel de confianza.

  1. Ir a: Celda I5 / Escribir:

    =DISTR.T.INV(1-H5;1000000)
  2. Copiar la celda I5 / Pegar en el rango: I6:I14.

Hoja Excel con valor de varianza
  • TABLA: MARGEN DE ERROR

Insertamos los encabezados de las columnas para los margenes de error, escribiéndolos o aplicando serie de relleno.

  1. Ir a: Celda C16 / Escribir: 1,0%.

  2. Ir a: Celda D16 / Escribir: 1,5%.

  3. Ir a: Celda E16 / Escribir: 2,0%.

  4. Ir a: Celda F16 / Escribir: 2,5%.

  5. Ir a: Celda G16 / Escribir: 3,0%.

  6. Ir a: Celda H16 / Escribir: 3,5%.

  7. Ir a: Celda I16 / Escribir: 4,0%.

  8. Ir a: Celda J16 / Escribir: 4,5%.

  9. Ir a: Celda K16 / Escribir: 5,0%.

  10. Ir a: Celda L16 / Escribir: 5,5%.

  11. Ir a: Celda M16 / Escribir: 6,0%.

  12. Ir a: Celda N16 / Escribir: 6,5%.

  13. Ir a: Celda O16 / Escribir: 7,0%.

  14. Ir a: Celda P16 / Escribir: 7,5%.

  15. Ir a: Celda Q16 / Escribir: 8,0%.

  16. Ir a: Celda R16 / Escribir: 8,5%.

  17. Ir a: Celda S16 / Escribir: 9,0%.

  18. Ir a: Celda T16 / Escribir: 9,5%.

  19. Ir a: Celda U16 / Escribir: 10,0%.

Hoja Excel con margen de error
  • TABLA: TAMAÑO POBLACIÓN

Insertamos los encabezados de las filas para el tamaño de la población, escribiéndolos o aplicando serie de relleno.

  1. Ir a: Celda B17 / Escribir: 1000.

  2. Ir a: Celda B18 / Escribir: 2000.

  3. Ir a: Celda B19 / Escribir: 3000.

  4. Ir a: Celda B20 / Escribir: 4000.

  5. Ir a: Celda B21 / Escribir: 5000.

  6. Ir a: Celda B22 / Escribir: 6000.

  7. Ir a: Celda B23 / Escribir: 7000.

  8. Ir a: Celda B24 / Escribir: 8000.

  9. Ir a: Celda B25 / Escribir: 9000.

  10. Ir a: Celda B26 / Escribir: 10000.

  11. Ir a: Celda B27 / Escribir: 11000.

  12. Ir a: Celda B28 / Escribir: 12000.

  13. Ir a: Celda B29 / Escribir: 13000.

  14. Ir a: Celda B30 / Escribir: 14000.

  15. Ir a: Celda B31 / Escribir: 15000.

  16. Ir a: Celda B32 / Escribir: 16000.

  17. Ir a: Celda B33 / Escribir: 17000.

  18. Ir a: Celda B34 / Escribir: 18000.

  19. Ir a: Celda B35 / Escribir: 19000.

  20. Ir a: Celda B36 / Escribir: 20000.

  21. Ir a: Celda B37 / Escribir: 21000.

  22. Ir a: Celda B38 / Escribir: 22000.

  23. Ir a: Celda B39 / Escribir: 23000.

  24. Ir a: Celda B40 / Escribir: 24000.

  25. Ir a: Celda B41 / Escribir: 25000.

  26. Ir a: Celda B42 / Escribir: 26000.

  27. Ir a: Celda B43 / Escribir: 27000.

  28. Ir a: Celda B44 / Escribir: 28000.

  29. Ir a: Celda B45 / Escribir: 29000.

  30. Ir a: Celda B46 / Escribir: 30000.

Hoja Excel con tamaño de población
  • TABLA: TAMAÑO DE MUESTRA

Insertamos la fórmula que define el tamaño de la muestra según la población y el error.

  1. Ir a: Celda C17 / Escribir:

    =($B17)/(1+((C$16*C$16)*($B17-1))/(DISTR.T.INV(1-$E$11;1000000)^2*0,5^2))
  2. Copiar la celda C17 / Pegar en el rango: C17:U46.

  • TABLA: FORMATO CONDICIONAL MARGEN DE ERROR

Aplicamos un formato condicional que nos remarque el margen de error aplicado.

  1. Seleccionar el rango C16:U16. Rango para remarcar el margen de error.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva regla. Mostrará la ventana de nueva regla de formato.

  3. Ir a: Seleccionar un tipo de regla / Clic sobre Utilice una fórmula que determine las celdas para aplicar formato.

  4. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir:

    =$E$9=C16
  5. Pulsar el botón formato. Mostrará la ventana de formato de celdas.

  6. Aplicar el formato deseado a fuente, borde o relleno.

  7. Pulsar el botón Aceptar. Cierra la ventana formato de celdas y aplica los cambios.

  8. Pulsar el botón Aceptar. Cierra la ventana editar regla y aplica los cambios.

  • TABLA: FORMATO CONDICIONAL TAMAÑO POBLACIÓN

Aplicamos un formato condicional que nos remarque el tamaño de la población.

  1. Seleccionar el rango B17:B46. Rango para remarcar el margen de error.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva regla. Mostrará la ventana de nueva regla de formato.

  3. Ir a: Seleccionar un tipo de regla / Clic sobre Utilice una fórmula que determine las celdas para aplicar formato.

  4. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir:

    =B17=$E$7
  5. Pulsar el botón formato. Mostrará la ventana de formato de celdas.

  6. Aplicar el formato deseado a fuente, borde o relleno.

  7. Pulsar el botón Aceptar. Cierra la ventana formato de celdas y aplica los cambios.

  8. Pulsar el botón Aceptar. Cierra la ventana editar regla y aplica los cambios.

  • TABLA: FORMATO CONDICIONAL TAMAÑO DE MUESTRA

Aplicamos un formato condicional que nos remarque el tamaño de muestra para los valores deseados.

  1. Seleccionar el rango C17:U46. Rango para remarcar el margen de error.

  2. Ir a: Cinta de opciones / Pestaña: Inicio / Grupo: Estilos / Formato condicional / Nueva regla. Mostrará la ventana de nueva regla de formato.

  3. Ir a: Seleccionar un tipo de regla / Clic sobre Utilice una fórmula que determine las celdas para aplicar formato.

  4. Ir a: Dar formato a los valores donde esta fórmula sea verdadera / Escribir:

    =$E$13=C17
  5. Pulsar el botón formato. Mostrará la ventana de formato de celdas.

  6. Aplicar el formato deseado a fuente, borde o relleno.

  7. Pulsar el botón Aceptar. Cierra la ventana formato de celdas y aplica los cambios.

  8. Pulsar el botón Aceptar. Cierra la ventana editar regla y aplica los cambios.

Tabla Excel con tamaño de muestra
  • GRÁFICO TAMAÑO MUESTRA.

Podemos representar el gráfico con el tamaño de la muestra del resultado.

  1. Seleccionar los rangos B17:B34 y E17:E34. Rango con los resultados para representar.

  2. Ir a: Cinta de opciones / Pestaña: Insertar / Grupo: Gráficos / Dispersión / Dispersión con líneas rectas y marcadores. Insertará el gráfico del tamaño de la muestra.

Gráfico de tamaño muestra
  • GRÁFICO TAMAÑO POBLACIÓN.

Podemos representar el gráfico con el tamaño de la población del resultado.

  1. Seleccionar los rangos C16:E16 y C34:E34. Rango con los resultados para representar.

  2. Ir a: Cinta de opciones / Pestaña: Insertar / Grupo: Gráficos / Dispersión / Dispersión con líneas rectas y marcadores. Insertará el gráfico del tamaño según el margen de error.

Gráfico de tamaño población
  • FORMATOS. Aplicamos el formato deseado a la hoja.
  1. OCULTAR LÍNEAS DE CUADRÍCULA. Para dar apariencia de formulario.

  2. BORDES Y RELLENOS. Aplicamos bordes y rellenos a las celdas deseadas.

  3. FORMATO DE CELDA NÚMEROS. Aplicamos el formato tipo número deseado.

  4. PROTEGER HOJA. Desbloquear las celdas E7, E9 y E11 y proteger la hoja.

Una vez obtenido el tamaño de la muestra, se debe establecer los criterios para la selección, según la característica del universo:

– De manera aleatoria (dando la oportunidad a cualquier registro de ser elegido).

– De manera sistemática (dividiendo la población entre el tamaño de la muestra, obteniendo un valor que servirá para establecer un intervalo para recoger la muestra).

– Por bloques (seleccionando cierta cantidad de registros por meses y aplicando la metodología sistemática en cada bloque).

– Por otros criterios, tomando en cuenta registros materiales, etc.

  

Nivel de dificultad: Avanzado Excel Avanzado

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *