Encuestas fáciles de analizar en una hoja de cálculo: 200 respuestas en una hoja, dos hojas auxiliares y cuatro fórmulas bastan para sacar tablas, porcentajes y gráficos sin tocar el papel.
En este tutorial vas a montar una hoja realista de respuestas (sexo, edad, satisfacción y provincia), a validar las celdas, a contar con CONTAR.SI (COUNTIF) y CONTAR.SI.CONJUNTO (COUNTIFS), a construir una tabla dinámica que cruce preguntas y a presentar los resultados en gráficos. Sirve para cualquier encuesta con varias preguntas de opción múltiple o numéricas. Funciona en Excel 2010 en adelante, en Excel para la web y en Excel para Mac. Cuando veas ; entre argumentos, recuerda que en Excel configurado con punto decimal (México, Estados Unidos) el separador es la coma.
Qué es analizar una encuesta y por qué se hace en una hoja
Analizar una encuesta es pasar de un listado de respuestas en bruto a tablas con frecuencias y porcentajes que respondan a las preguntas del cuestionario: cuántos hombres y cuántas mujeres, qué nota media de satisfacción, cómo varía la respuesta por tramo de edad. En una hoja de Excel se hace en minutos: cada respuesta es una celda y las cuentas se obtienen con fórmulas o con una tabla dinámica.
Frente al papel o un PDF, la hoja permite filtrar al instante, cruzar dos preguntas (mujeres con satisfacción Alta) y rehacer los totales en cuanto llega una respuesta más. Para ello solo hace falta una hoja bien pensada: una fila por encuestado, una columna por pregunta y las respuestas válidas escritas en otra hoja aparte que sirva de diccionario.
A partir de aquí trabajarás con un libro de tres hojas: «Respuestas», «Categorías» y «Resumen». Lo construyes con datos pequeños de una encuesta ficticia de satisfacción de un comercio: 8 encuestados, sexo, edad, nivel de satisfacción y provincia.
La estructura de una hoja pensada para encuestas
Una hoja «Respuestas» con 8 filas y 5 columnas (Id, Sexo, Edad, Satisfacción, Provincia) es el patrón básico. Las preguntas quedan en columnas y cada encuestado ocupa una fila: así se pueden contar, filtrar y cruzar sin reorganizar nada. La hoja «Categorías» guarda los valores válidos de cada pregunta y se convierte en tabla para crecer sola cuando se añadan opciones nuevas.
La columna A identifica al encuestado (1, 2, 3…), la B el sexo (Hombre o Mujer), la C la edad (un entero entre 18 y 99), la D el nivel de satisfacción (Bajo, Medio o Alto) y la E la provincia. La fila 1 lleva los encabezados en negrita y los datos empiezan en la fila 2.
| Pregunta | Tipo de dato | Valores válidos |
|---|---|---|
| Sexo | Categoría (texto) | Hombre, Mujer |
| Edad | Entero | 18 a 99 |
| Satisfacción | Categoría (texto) | Bajo, Medio, Alto |
| Provincia | Categoría (texto) | Norte, Centro, Sur |
Una fila por persona, una columna por pregunta
La hoja «Respuestas» con los ocho encuestados del ejemplo.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Id | Sexo | Edad | Satisfaccion | Provincia |
| 2 | 1 | Hombre | 34 | Alto | Norte |
| 3 | 2 | Mujer | 28 | Medio | Centro |
| 4 | 3 | Mujer | 41 | Alto | Sur |
| 5 | 4 | Hombre | 52 | Bajo | Norte |
| 6 | 5 | Mujer | 36 | Alto | Centro |
| 7 | 6 | Hombre | 45 | Medio | Sur |
| 8 | 7 | Mujer | 29 | Bajo | Norte |
| 9 | 8 | Hombre | 60 | Alto | Centro |
El diccionario de valores válidos en otra hoja
La hoja «Categorías» lista los valores de cada pregunta, primero en encabezados y después en vertical. Al convertirla en tabla, basta con escribir una opción nueva en la fila siguiente para que aparezca en las listas desplegables y en el resumen.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sexo | Edad | Satisfaccion | Provincia | |
| 2 | Hombre | 18 | Bajo | Norte | |
| 3 | Mujer | 99 | Medio | Centro | |
| 4 | Alto | Sur | |||
| 5 |
Para crearla:1. Escribe los encabezados en B1:E1 y los valores debajo siguiendo la tabla.
- Selecciona
A1:E5y abreInsertar > Tablas > Tabla. - Marca "La tabla tiene encabezados" y acepta; Excel la llama "Tabla1".
B2:B3 y escribir "Otro" debajo, la lista desplegable de la hoja «Respuestas» incorpora la nueva opción al pulsar Tabla1 en el nombre.Validar las celdas para que solo entren respuestas válidas
La Validación de datos impide que entren respuestas mal escritas (Borg en vez de Hombre, 12 años, satisfecho en vez de Alto). Con ella, una lista desplegable en B y D, y un número entero entre 18 y 99 en C, los datos quedan limpios sin revisar uno por uno. Está disponible desde Excel 2007.
Lista desplegable sacada del diccionario
Para que las celdas de la columna B muestren una flecha con los valores del diccionario:
- Selecciona
B2:B200en la hoja «Respuestas». - Abre
Datos > Herramientas de datos > Validacion de datos. - En Permitir elige Lista; en Origen escribe
=Tabla1[Sexo]y marca "Celda con lista desplegable". - En Mensaje de entrada escribe "Selecciona un valor" y en Mensaje de error deja Estilo Detener con el texto "Valor no válido".
- Acepta.
Para la columna D, repite el proceso con Origen =Tabla1[Satisfaccion].
Edad entre 18 y 99 años
La columna C admite un número entero limitado a un rango razonable para una encuesta de adultos.
Contar respuestas con CONTAR.SI y CONTAR.SI.CONJUNTO
Con los datos limpios, llega el momento de contar. CONTAR.SI (COUNTIF) cuenta cuántas celdas de un rango cumplen una condición; CONTAR.SI.CONJUNTO (COUNTIFS) aplica varias a la vez, así sirve para cruzar preguntas. En una hoja aparte, «Resumen», se construye una tabla pregunta a pregunta y al lado otra con los porcentajes sobre el total.
Cuántos de cada sexo y de cada nivel de satisfacción
La tabla «Resumen» empieza con dos columnas: la opción a contar y el número de veces que aparece. Para cada fila se arrastra la fórmula hacia abajo.
=CONTAR.SI('Respuestas'!B:B;"Mujer")
=CONTAR.SI('Respuestas'!D:D;"Alto")En la hoja «Resumen» con los datos del ejemplo, el reparto por sexo y por satisfacción queda así:
| A | B | |
|---|---|---|
| 1 | Opcion | Cantidad |
| 2 | Mujer | 4 |
| 3 | Hombre | 4 |
| 4 | Total | 8 |
| 5 | Satisfaccion Alto | 4 |
| 6 | Satisfaccion Medio | 2 |
| 7 | Satisfaccion Bajo | 2 |
Cruzar dos variables a la vez
Para responder "¿cuántas mujeres tienen una satisfacción Alta?" se usa CONTAR.SI.CONJUNTO con un par de criterios, uno por cada columna:
=CONTAR.SI.CONJUNTO('Respuestas'!B:B;"Mujer";'Respuestas'!D:D;"Alto")Con los datos del ejemplo, el resultado es 3. La fórmula se arrastra cambiando los criterios para sacar Hombre-Alto (1), Mujer-Medio (1), Hombre-Medio (1), Mujer-Bajo (1) y Hombre-Bajo (1).
Pasar el conteo a porcentaje sobre el total
La columna de al lado convierte cada cantidad en porcentaje del total de respuestas con la celda absoluta del total:
=B2/$B$4| | A | B | C | | 1 | Opcion | Cantidad | Porcentaje | | 2 | Mujer | 4 | 50,00% | | 3 | Hombre | 4 | 50,00% | | 4 | Total | 8 | 100,00% |
Aplica formato de porcentaje a C2:C4 con Ctrl+1 > Número > Porcentaje con dos decimales. El $ en $B$4 fija la referencia al copiar la fórmula hacia abajo.
Tablas dinámicas para cruzar preguntas sin escribir fórmulas
La tabla dinámica hace lo mismo que CONTAR.SI.CONJUNTO sin escribir fórmulas: arrastras los campos a zonas y Excel resume los datos. Está disponible desde Excel 2007 y en Excel para la web.
Poner las preguntas en Filas, Columnas y Valores
- Haz clic en una celda de «Respuestas» y abre
Insertar > Tablas > Tabla dinamica. - Elige "De tabla o rango" y deja la tabla
Tabla1; acepta en una hoja nueva. - En el panel de campos, arrastra Sexo a Filas, Satisfacción a Columnas y Satisfacción otra vez a Valores.
- Clic sobre el campo de valor > Configuración de campo de valor > Cuenta, y acepta.
| Sexo | Alto | Bajo | Medio | Total general |
|---|---|---|---|---|
| Hombre | 1 | 1 | 1 | 4 |
| Mujer | 3 | 1 | 1 | 4 |
| Total general | 4 | 2 | 2 | 8 |
CONTAR.SI.CONJUNTO del apartado anterior.Filtrar por Edad o por cualquier columna
La lista de campos del panel sirve también para filtrar: arrastra Edad a Filtros y aparece una lista desplegable encima de la tabla. Para mostrar solo mayores de 40:
- Clic en la flecha del filtro Edad > Filtros de número > Mayor que.
- Escribe 40 y acepta.
También puedes hacer clic derecho sobre cualquier valor de la tabla y elegir "Mostrar valor como" > "% del total de fila" para ver proporciones por fila sin escribir una división.
Actualizar la tabla dinámica al meter respuestas nuevas
Cada vez que lleguen respuestas nuevas a la hoja «Respuestas», pulsa clic en la tabla dinámica y abre:
Atajo: Alt+F5 actualiza todas las dinámicas del libro. Si «Respuestas» está convertida en tabla, el origen se amplía solo y basta con refrescar; si no es tabla, cambia el origen desde Analisis de tabla dinamica > Cambiar origen de datos para que apunte al rango completo.
Gráficos que muestran los resultados de un vistazo
Un gráfico bien elegido cuenta la historia sin obligar a leer la tabla. Para una encuesta con pocas categorías se usan dos: uno de barras agrupadas para comparar cantidades y uno circular para ver proporciones. Los gráficos dinámicos respetan los filtros aplicados a la tabla dinámica.
Barras para comparar categorías
Para sacar un gráfico de barras con los niveles de satisfacción a partir de la tabla «Resumen»:
- Selecciona
A5:B7(opción y cantidad de satisfacción). - Abre
Insertar > Graficos > Barras agrupadas. - Clic derecho sobre una barra > Agregar etiquetas de datos > Fin.
- En Formato de etiquetas de datos marca "Porcentaje" y "Posición: Exterior extremo".
Circular para ver proporciones
Para el reparto por sexo:
- Selecciona
A2:B3en «Resumen» (Mujer, Hombre con sus cantidades). - Abre
Insertar > Graficos > Circular. - Clic derecho sobre el círculo > Agregar etiquetas de datos > Fin.
- En Opciones de etiqueta marca "Valor" y "Porcentaje" y desmarca "Nombre de la categoría" si no quieres ruido.
Errores que aparecen al analizar encuestas
Trabajar con encuestas reales siempre deja algún error típico: categorías mal escritas, datos importados con dos preguntas juntas o porcentajes que dan 0 en lugar de quedar en blanco. Estos cinco casos son los más repetidos.
#¡DIV/0! en una celda de porcentaje
La fórmula =B2/B6 devuelve #¡DIV/0! cuando la celda del total está vacía o contiene texto. Para evitarlo se envuelve en SI:
=SI(B6=0;"";B2/B6)Si el denominador es una celda esperada a 0 por error, sustitúyelo por la celda del total calculada con SUMA.
«0» donde debería haber un guion
Cuando una opción no tiene respuestas, CONTAR.SI devuelve 0 y el porcentaje muestra "0,00%"; en una tabla pulida se prefiere un guion. Selecciona las celdas de la columna de porcentaje y aplica con Ctrl+1 el formato personalizado:
Este formato deja los positivos con porcentaje, los negativos con menos y un guion literal cuando el valor es 0.
La tabla dinámica repite los mismos datos al actualizar
Sucede cuando el origen no es una tabla de Excel sino un rango con un número de filas fijo. Al añadir respuestas más allá de ese rango, la dinámica no las ve. La solución: convertir el rango en tabla con Insertar > Tablas > Tabla y volver a crear la dinámica; desde el panel marca "Agregar estos datos al modelo de datos" si trabajas con tablas grandes.
Validación que deja entrar valores nuevos
A veces la lista desplegable muestra solo cuatro valores pero la columna Origen apunta a un rango pequeño que se ha quedado antiguo. Revisa Datos > Validacion de datos > Origen y cámbialo por =Tabla1[Sexo] (o por la columna completa de la tabla de categorías). Si aun así entran valores en blanco, desmarca "Omitir blancos" en la pestaña Configuración.
Las respuestas están en una columna con dos preguntas juntas
En encuestas exportadas mal es común que la columna D traiga "Alto;30" donde "Alto" es la satisfacción y "30" es la edad. Para separarlas:
- Inserta dos columnas a la derecha de D (queda D, E, F).
- Selecciona
D2:D200, abreDatos > Herramientas de datos > Texto en columnas. - Elige Delimitados > Otro y escribe
;. - Acepta y comprueba que la satisfacción queda en D y la edad en E; después aplica la validación numérica a E.
Límites del análisis en Excel y qué usar cuando se queda corto
Una hoja de Excel admite hasta 1.048.576 filas y 16.384 columnas (hasta XFD), una cifra enorme para una encuesta puntual pero insuficiente para encuestas anuales con miles de respuestas mensuales o cuando hay que calcular intervalos de confianza, chi-cuadrado o regresiones. En ese punto, lo mejor es Power Query, integrado desde Excel 2016, para unir varios archivos de encuestas en uno solo; y, si hace falta análisis estadístico serio, exportar la tabla limpia a R, Python o Power BI con archivos .xlsx o .csv.
Lo que ya sabes hacer con una encuesta en Excel
- Estructurar la hoja de respuestas con una fila por encuestado y una columna por pregunta.
- Crear un diccionario de categorías en otra hoja y convertirlo en tabla.
- Validar celdas con lista desplegable y número entero entre 18 y 99.
- Contar respuestas con
CONTAR.SIy cruzar dos preguntas conCONTAR.SI.CONJUNTO. - Pasar conteos a porcentajes con formato de porcentaje y referencias absolutas.
- Construir una tabla dinámica con filtros y actualizarla al añadir respuestas.
- Presentar resultados con gráficos de barras y circular y formato personalizado para blancos.
El siguiente paso es abrir Datos > Obtener y transformar datos > Desde archivo > Desde carpeta para unir las hojas de distintos meses en una sola y analizarlas en un informe común.
Dudas al analizar encuestas en Excel
1. ¿Cómo puedo analizar los resultados de una encuesta en Excel con tablas dinámicas?
Carga tus datos en una hoja con una fila por encuestado y una columna por pregunta. Selecciona todo el rango, ve a Insertar > Tabla dinámica y elige dónde colocarla. Luego arrastra cada pregunta al área Filas y las opciones de respuesta al área Valores, contando respuestas con la función Recuento para ver los porcentajes al instante.
2. ¿Cómo sé qué porcentaje marcó cada opción en una encuesta usando Excel?
Crea una tabla dinámica con las respuestas en Filas y Recuento de respuestas en Valores. Haz clic en cualquier valor, elige Mostrar valores como > % del total de columnas y verás qué opción fue la más y la menos elegida. El gráfico dinámico asociado se actualiza solo cuando cambias la encuesta.
3. ¿Puedo hacer gráficos de barras o circulares a partir de una encuesta en Excel?
Sí: con la tabla dinámica lista, sitúate en ella y ve a Insertar > Gráfico dinámico. Elige barras agrupadas para comparar opciones o circular para ver proporciones, y aparecerá en la misma hoja. Cualquier cambio en los datos de origen se refleja al actualizar con clic derecho > Actualizar.
4. ¿Cómo actualizo mi análisis de encuesta cuando llegan nuevas respuestas?
Añade las respuestas nuevas debajo de la tabla original manteniendo los mismos encabezados. Haz clic derecho sobre la tabla dinámica y elige Actualizar: los conteos y porcentajes se recalculan al instante. Si creaste el gráfico desde esa tabla, se actualizará junto con ella sin tocar nada más.
Fuentes
- MicrosoftExcel 2013 — documentación oficialconsultado el 12/10/2026