Análisis estadístico en Excel: funciones clave con ejemplos

Imagina una hoja con las notas de un grupo de alumnos: veinte filas, tres columnas y la sensación de que detrás de esa lista hay un mensaje escondido. El análisis estadístico en Excel es, justamente, el camino para sacar ese mensaje: convierte una tabla en números que se pueden comparar (media, mediana, desviación) y en gráficos que se pueden mirar de un vistazo. Al terminar este tutorial sabrás resumir un conjunto de datos, detectar diferencias entre dos grupos y comprobar si dos variables se mueven juntas, todo con funciones y herramientas que ya vienen en Excel 2007 en adelante. Lo que se explica aquí funciona en Microsoft 365, Excel 2024, 2021, 2019, 2016 y 2013; las funciones más nuevas (matrices dinámicas, PROMEDIO.SI.CONJUNTO) las aviso aparte.

Qué hace Excel por ti cuando trabajas con datos

Una hoja de notas con veinte filas es un buen ejemplo para entenderlo. La hoja Notas tiene, en la columna A, el nombre de cada alumno; en la B, la nota del examen (un número del 0 al 10); y en la C, el grupo al que pertenece, A o B. A primera vista solo ves una lista. El análisis estadístico hace cuatro cosas con esa lista: encuentra un valor que represente al grupo (la media, la mediana), mide cuánto se separan las notas de ese representante (la desviación), compara los dos grupos para ver si la diferencia es grande o pequeña, y dibuja una gráfica que cuente la historia de un vistazo. En este tutorial la hoja Notas crece paso a paso: empezamos con los nombres y las notas, añadimos una columna de horas de estudio, calculamos las medidas sobre todos los datos y solo sobre el grupo A, comparamos A y B en un gráfico de columnas y, por último, vemos si estudiar más horas se traduce en nota más alta con una correlación y una nube de puntos. Todo con el mismo libro, del primer al último paso.

Las funciones estadísticas que de verdad usarás

Nueve funciones cubren el 90% del trabajo diario. PROMEDIO, MEDIANA y MODA localizan el centro de los datos; MAX y MIN localizan los extremos; CONTAR y CONTARA cuentan cuántos datos hay; DESVEST.M (STDEV.S, en inglés) y VAR.S (VARIANCE.S) miden la dispersión. La tabla siguiente resume qué hace cada una y cuándo elegirla.

PROMEDIO, MEDIANA y MODA: el centro de los datos

Con la lista 1, 2, 2, 3, 9 las tres funciones dan respuestas distintas y eso es justamente lo que importa entender. PROMEDIO devuelve 3,4 (la suma, 17, partido por 5); MEDIANA devuelve 2 (el valor central al ordenar); MODA devuelve 2 (el valor que más se repite). La media se va hasta 3,4 porque el 9 tira de ella hacia arriba; la mediana y la moda ni se enteran. Por eso, cuando sospechas que hay un valor atípico, mira la mediana antes que la media.

=PROMEDIO(1;2;2;3;9)
=MEDIANA(1;2;2;3;9)
=MODA(1;2;2;3;9)

DESVEST.M y VAR.S: cuánto se separan los datos de la media

Con la misma lista, DESVEST.M devuelve 3,13 y VAR.S devuelve 9,8. La desviación típica es, en términos intuitivos, la distancia media que tienen los datos respecto a la media: si la media es 3,4 y la desviación es 3,13, las notas se mueven en un rango amplio. VAR.S es el cuadrado de la desviación, y se usa menos en informes porque sus unidades también van al cuadrado. Usa DESVEST.M cuando la lista es una muestra; usa DESVEST.P (STDEV.P) cuando la lista es toda la población.

=DESVEST.M(1;2;2;3;9)
=VAR.S(1;2;2;3;9)

Preparar la hoja antes de calcular

Antes de pedirle a Excel que calcule nada, conviene saber con cuántos datos cuentas y si hay duplicados. En la hoja Notas, A22 va a contar los nombres y B22 los números, y después quitaremos los nombres repetidos. La celda activa al terminar es B2, y la zona de medidas quedará en E1:F8.

Una columna limpia con CONTARA y CONTAR

En A22 escribe =CONTARA(A2:A21) y en B22 =CONTAR(B2:B21). La primera cuenta celdas no vacías (nombres) y la segunda solo números (notas). Si los dos números no coinciden con 20, ya sabes que hay algo raro: un alumno sin nota, una nota escrita como texto, una fila vacía.

Quitar duplicados antes de promediar

1. Selecciona A1:A21 (encabezado incluido).
2. Abre Datos > Herramientas de datos > Quitar duplicados.
3. Marca «Mis datos tienen encabezados» y deja la columna Nombre activa.
4. Pulsa Aceptar.

Excel quita los nombres que aparecen más de una vez y deja la primera ocurrencia. Si un alumno estaba duplicado porque se copió dos veces, desaparece; si el nombre aparece una sola vez, se queda. En la hoja Notas no debería quitar nada, pero si lo hace ya sabes que un alumno estaba contado dos veces y eso te habría falseado la media.

Calcular las medidas principales sobre la hoja Notas

Ahora llega la zona de resultados. En E1:F8, rótulo a la izquierda y fórmula a la derecha, calculamos la media, la mediana, la moda, la desviación y la varianza de los 20 datos. Después repetimos solo para el grupo A.

Media, mediana y moda de las 20 notas

En E2, E3 y E4 escribimos los rótulos «Media», «Mediana» y «Moda»; en F2, F3 y F4 las fórmulas. La celda activa es B2, pero los resultados viven en F2:F4.

=PROMEDIO(B2:B21)
=MEDIANA(B2:B21)
=MODA(B2:B21)

Dispersión con DESVEST.M y VAR.S

En E5 escribimos «Desviación» y en F5 la fórmula. La idea: 1,95 puntos es la distancia media a la que se mueve una nota respecto a la media de 6,20; casi todas las notas caen en el rango 4,25 a 8,15.

=DESVEST.M(B2:B21)
=VAR.S(B2:B21)

Las mismas medidas solo para el grupo A

Para los filtros condicionales, en Excel 2021 y Microsoft 365 PROMEDIO.SI (AVERAGEIF) y DESVEST.M con matrices dinámicas cubren el caso en una sola línea. En versiones anteriores, el camino es sumar y contar por separado y luego dividir.

=PROMEDIO.SI(C2:C21;"A";B2:B21)
=DESVEST.M(SI(C2:C21="A";B2:B21))

En Excel 2019 o 2016, la segunda fórmula es matricial y se confirma con Ctrl+Mayús+Entrar. La alternativa clásica, que sí funciona en todas las versiones, es:

=SUMAR.SI.CONJUNTO(B2:B21;C2:C21;"A")/CONTAR.SI.CONJUNTO(C2:C21;"A")

Comparar dos grupos de un vistazo

Un número de media por grupo dice algo, pero un gráfico lo cuenta en un segundo. Vamos a poner la media del grupo A y la del grupo B en una tabla pequeña y a graficarla con un gráfico de columnas agrupadas.

Una tabla con las medias por grupo para graficar

En E11 y E12 escribimos los rótulos «Grupo A» y «Grupo B». En F11 y F12, las medias calculadas con PROMEDIO.SI.

=PROMEDIO.SI(C2:C21;E11;B2:B21)
=PROMEDIO.SI(C2:C21;E12;B2:B21)

El gráfico con las dos barras

1. Selecciona E11:F12.
2. Abre Insertar > Gráficos > Columna agrupada.
3. Pulsa Aceptar.

El gráfico muestra dos barras: el grupo A con su media y el grupo B con la suya. Si la barra del B es visiblemente más alta, el grupo B rinde más; si están casi a la misma altura, la diferencia es pequeña y conviene confirmarla con una prueba estadística.

Cuando los datos se parecen pero no sabes cuánto: COEF.DE.CORREL y COEFICIENTE.R2

Ahora añadimos una columna D con las horas de estudio de cada alumno (valores plausibles entre 0 y 8). La pregunta es: ¿estudiar más horas se traduce en nota más alta? La covarianza y la correlación de Pearson dan la respuesta numérica, y la nube de puntos la respuesta visual.

La covarianza y la correlación de Pearson

En E14 escribimos «Covarianza» y en F14 la fórmula; en E15 «Correlación» y en F15 la correlación. El valor de la correlación cae siempre entre -1 y 1: cerca de 1, las dos variables suben juntas; cerca de -1, una sube cuando la otra baja; cerca de 0, no hay relación lineal.

=COVARIANZA.M(B2:B21;D2:D21)
=COEF.DE.CORREL(B2:B21;D2:D21)

Con los datos de ejemplo, la correlación anda cerca de 0,85, una relación fuerte y positiva: más horas, más nota.

Dibujar la nube de puntos y la línea de tendencia

1. Selecciona B1:B21 y, manteniendo Ctrl, D1:D21.
2. Abre Insertar > Gráficos > Dispersión > Dispersión solo con marcadores.
3. Clic derecho sobre un punto del gráfico y elige Agregar línea de tendencia > Lineal.
4. En Formato de línea de tendencia marca «Presentar ecuación en el gráfico» y «Presentar valor R cuadrado en el gráfico».

Sobre la misma hoja aparece la recta y, junto a ella, un número: R². Si R² vale 0,72, eso significa que el 72% de la variación de la nota se explica por las horas de estudio.

Por qué tus fórmulas estadísticas fallan

Tres tropiezos típicos con la hoja Notas y cómo arreglarlos.

Texto en la columna de notas y #¡DIV/0! en PROMEDIO

Si alguien escribió «NP» (No Presentado) en B5, PROMEDIO devuelve #¡DIV/0! porque CONTAR ignora el texto y queda un divisor de cero. El arreglo rápido es envolver la fórmula:

=SI.ERROR(PROMEDIO(B2:B21);"Revisa la columna B")

La solución de fondo es impedir que entre texto donde toca un número: Datos > Herramientas de datos > Validación de datos, Permitir Decimal, entre 0 y 10. Así Excel rechaza la entrada antes de que arruine el cálculo.

#¡VALOR! al mezclar DESVEST.M con una celda vacía

Una celda en blanco dentro del rango B2:B21 hace que DESVEST.M devuelva un resultado raro, y si en esa celda alguien pegó un texto, devuelve #¡VALOR!. Pasa el rango completo a la función y, si el problema es de versión, recuerda: DESVEST.M (muestral) y DESVEST.P (poblacional) son las dos variantes modernas; DESVEST a secas se conserva por compatibilidad y trata los datos como muestra.

Gráfico con una sola barra o con los nombres en el eje equivocado

Si al insertar el gráfico Excel leyó los nombres de los alumnos en vez de los grupos, el problema está en la selección. Hay que seleccionar SOLO E11:F12 antes de insertar el gráfico, no toda la tabla. Si aun así el eje horizontal no queda bien, abre Diseño de gráficos > Seleccionar datos y mueve «Grupo A» y «Grupo B» a la lista de Categorías.

Lo que no cabe en una fórmula: prueba de hipótesis y regresión

Las funciones PROMEDIO, DESVEST.M y COEF.DE.CORREL cubren el análisis descriptivo, pero para ir más allá (saber si la diferencia entre dos medias es estadísticamente significativa, o predecir una variable a partir de varias) Excel necesita un complemento.

Buscar objetivo para resolver una ecuación a mano

Datos > Previsión > Análisis de hipótesis > Buscar objetivo despeja una variable en una fórmula. Por ejemplo,sabes qué nota necesitas para que tu media suba a 7: introduces la celda de la media, valor 7 y la celda de la nota a cambiar. Sirve para tanteos, no para pruebas estadísticas.

El complemento Análisis de datos para muestras y regresiones

En Archivo > Opciones > Complementos > Complementos de Excel, marca Herramientas para análisis y pulsa Aceptar. La cinta Datos gana un grupo Análisis con un botón Análisis de datos que abre un cuadro con Histograma, Estadística descriptiva, Regresión, Prueba t para dos muestras, ANOVA y más. Ahí sí se hacen las pruebas que las funciones sueltas no cubren.

Power Pivot y Power Query cuando los datos no caben en una hoja

Si la hoja Notas creciera hasta un millón de filas, o si tuvieras que cruzarla con otra tabla (por ejemplo, las notas de varios colegios), las fórmulas se quedan cortas. Power Query, en Datos > Obtener y transformar datos, importa y limpia grandes volúmenes; Power Pivot, en su pestaña propia, crea un modelo de datos con relaciones entre tablas. Para análisis serios (modelos lineales generalizados, machine learning, validación cruzada), Excel se queda corto y toca pasar a un programa estadístico dedicado como R o Python.

Lo que ya sabes hacer con el análisis estadístico en Excel

• Calcular la media, mediana y moda de un conjunto de datos con PROMEDIO, MEDIANA y MODA.
• Medir la dispersión con DESVEST.M y VAR.S y entender la diferencia entre muestra y población.
• Contar datos limpios con CONTAR y CONTARA para detectar textos y celdas vacías.
• Filtrar las medidas por grupo con PROMEDIO.SI, SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO.
• Comparar dos grupos visualmente con un gráfico de columnas agrupadas.
• Comprobar la relación entre dos variables con COEF.DE.CORREL, la covarianza y una nube de puntos con su R².

El siguiente paso natural es activar el complemento Herramientas para análisis y practicar con la Prueba t y la Regresión, las dos herramientas que llevan del análisis descriptivo al análisis inferencial sin salir de Excel.