Análisis cuantitativo de datos en Excel: guía para empezar

Cuando se trabaja con cientos o miles de ventas, de notas o de movimientos, hace falta resumirlos, compararlos y anticipar lo que va a pasar. Eso es el análisis cuantitativo: convertir una hoja llena de números en respuestas concretas. En este tutorial aprenderás a hacerlo dentro de Excel, sin salir de la cinta de opciones, con un libro de ejemplo que crece contigo: Ventas 2026, una hoja con doce filas de pedidos repartidos por zonas y meses, a la que después añadiremos cierres mensuales y reglas de limpieza.

Nota sobre el separador: las fórmulas de este artículo usan punto y coma entre argumentos (=PROMEDIO(A1:A10)), como en España. En Excel configurado con punto decimal (México, Estados Unidos) el separador es la coma.

Las funciones y herramientas que verás están en Excel para Microsoft 365, Excel 2021, 2024, 2019 y 2016, y en Excel para la web. Cuando algo dependa de una versión concreta, lo decimos.

Qué es el análisis cuantitativo y por qué lo haces en Excel

Imagina una hoja llamada Ventas 2026 con doscientas filas: fecha, zona e importe de cada pedido. Si solo la miras, no ves nada. Si la recorres fila a fila, tardas demasiado y se te escapan cosas. El análisis cuantitativo es justo el paso intermedio: usa números que ya tienes (media, máximo, porcentaje, correlación) para resumir, comparar y anticipar.

Excel es el taller donde se hace ese trabajo por dos razones: las cifras ya están en filas y columnas, y las herramientas para tratarlas están en la misma cinta. La barra de estado, debajo a la derecha, ya te enseña la suma, el promedio y el recuento de lo que tienes seleccionado, sin escribir nada. Y a partir de ahí, cada fórmula resume más, cada gráfico muestra más y cada filtro concreta más.

En este tutorial construirás cinco capacidades: resumir una columna, comparar grupos, limpiar la hoja antes de contar, filtrar lo que importa y anticipar el siguiente paso. Todas sobre el mismo libro, para que cada cosa nueva se apoye en la anterior.

Las cifras que resumen una columna en segundos

La primera pregunta siempre es la misma: cuánto vendí, cuál fue el pedido más alto, cuántos registros tengo. Para responderla, Excel trae cinco funciones que viven en cualquier versión: PROMEDIO (AVERAGE), MAX, MIN, CONTAR (COUNT) y CONTARA (COUNTA). La diferencia entre las dos últimas es importante: CONTAR cuenta solo celdas con números y CONTARA cuenta todas las celdas que no están vacías, incluidas las de texto.

Mira la hoja Ventas 2026 con doce pedidos de enero a abril repartidos en cuatro zonas.

Para sacar las cinco cifras de una vez, escribe en la columna F a partir de F2:

=PROMEDIO(C2:C13)
=MAX(C2:C13)
=MIN(C2:C13)
=CONTAR(C2:C13)
=CONTARA(A2:A13)

Los resultados son: promedio 350,83, máximo 620, mínimo 180, 12 importes numéricos y 12 fechas con dato. La barra de estado, debajo a la derecha, te da la suma, el promedio y el recuento sin escribir nada: solo tienes que seleccionar el rango y mirar.

Distribuciones, posiciones y comparaciones entre grupos

El promedio esconde mucho: un valor muy alto y otro muy bajo dan la misma media que muchos valores en el centro. Para ver la forma real de los números hacen falta medidas de posición y de dispersión. MEDIANA (MEDIAN) te dice el valor que está en el centro; MODA (MODE) el que más se repite; PERCENTIL (PERCENTILE) deja ver un corte concreto, por ejemplo el 25 % más bajo.

La dispersión se mide con DESVEST.P (STDEV.P) cuando los datos son toda la población que te interesa, y con DESVEST.M (STDEV.S) cuando son una muestra. En la hoja de ejemplo, si Ventas 2026 es todo lo que vendió la empresa en el trimestre, usa DESVEST.P; si es solo una muestra de un periodo mayor, usa DESVEST.M.

Añade estas fórmulas debajo de las anteriores:

=MEDIANA(C2:C13)
=MODA(C2:C13)
=PERCENTIL(C2:C13;0,25)
=DESVEST.P(C2:C13)
=DESVEST.M(C2:C13)

Con los doce importes, la mediana es 330, la moda es 180 (no se repite, pero Excel lo señala como el primero más frecuente), el percentil 25 es 236,25, la desviación población 134,38 y la muestral 139,97.

Cruzar variables: tablas dinámicas y formato condicional

Con una columna sola ya resumimos; con dos columnas podemos comparar. La herramienta para esto es la tabla dinámica: arrastras campos y ella cuenta, suma y agrupa. Y el formato condicional se encarga de que, sin mirar números, veas dónde están los valores grandes.

Para crear la tabla dinámica:

Al aceptar, Excel crea una hoja nueva con el panel Campos de tabla dinámica a la derecha. Arrastra Zona a Filas, Mes (agrupando la fecha por meses) a Columnas y Importe a Valores con la suma. La cuadrícula que aparece muestra el total por zona y mes de un vistazo, sin escribir una sola fórmula.

Para que los números grandes salten a la vista, vuelve a la hoja Ventas 2026, selecciona C2:C13 y abre:

Elige Escalas de color para ver los importes altos en rojo y los bajos en verde, y Barras de datos para que cada celda muestre una barra proporcional a su valor. La celda ya no esconde el dato: lo dibuja.

El formato condicional con fórmula para casos a medida

Las reglas prefabricadas se quedan cortas cuando el criterio depende de una fórmula. Por ejemplo: «pinta en rojo los importes mayores que 500». Para eso, en Formato condicional > Nueva regla > Usar una fórmula, escribes una fórmula pensando en la primera celda del rango, que es C2. Si escribes la fórmula con $ delante de la columna y la fila, la regla no se mueve al copiar.

1. Selecciona C2:C13.
2. Abre Inicio > Estilos > Formato condicional > Nueva regla.
3. Elige Usar una fórmula que determine las celdas para aplicar formato.
4. Escribe =C2>500 y pulsa Formato para elegir el relleno rojo.
5. Acepta dos veces.

Limpiar la hoja antes de contar: Validación de datos y Pegado especial

Una hoja mal escrita da resultados mal contados. Antes de resumir, conviene obligar a que se escriba como toca. La herramienta es Validación de datos, en la pestaña Datos, y limita lo que se puede teclear en una celda: números, fechas, listas desplegables o longitudes de texto.

Sobre la columna A, queremos que solo se escriban fechas de 2026. Y sobre la columna B, que la zona sea una de cuatro: Norte, Sur, Este, Oeste.

1. Selecciona A2:A200 (un rango mayor para que la regla cubra las filas futuras).
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Fecha; en Datos, entre; en Fecha de inicio, 01/01/2026; en Fecha de fin, 31/12/2026.
4. En la pestaña Mensaje de error, escribe «Solo fechas de 2026».
5. Acepta.

Para la columna B, repite con Permitir: Lista y Origen: Norte,Sur,Este,Oeste. A partir de ahora, al hacer clic en una celda de la columna B aparecerá una lista desplegable con las cuatro zonas.

Quitar duplicados y homogeneizar texto antes de resumir

La hoja puede tener el mismo (Fecha, Zona) escrito dos veces por error, y zonas en mayúsculas distintas (norte, Norte, NORTE). Antes de contar, conviene limpiar.

1. Selecciona A1:C13.
2. Abre Datos > Herramientas de datos > Quitar duplicados.
3. Marca las tres columnas y acepta. Excel deja una fila por combinación.

Para homogeneizar la columna B, usa Pegado especial sobre la columna auxiliar, sin arrastrar fórmulas:

1. Copia la columna B con Ctrl+C.
2. En una columna libre, escribe =MAYUSC(B2) y arrastra hacia abajo.
3. Selecciona los resultados, Ctrl+C, y sobre B2 usa Ctrl+Alt+V (Pegado especial) eligiendo Valores.
4. Borra la columna auxiliar.

Filtrar y segmentar sin tocar la hoja original

La tabla dinámica que creaste antes tiene un botón extra muy útil: los segmentadores. Son botones grandes que filtran la tabla sin tocar la hoja de origen. Para añadirlos, haz clic dentro de la tabla dinámica y abre Analizar tabla dinámica > Filtrar > Insertar segmentador de datos. Marca Mes y Zona. Verás dos paneles con sus valores, y al pulsar uno, todo se filtra: por ejemplo, Trimestre 1 + Norte deja en pantalla solo las cuatro ventas de la zona Norte en enero, febrero y marzo, y los totales se recalculan al vuelo.

Cuando el filtro se queda corto: FILTRAR como fórmula

Los segmentadores filtran la tabla dinámica. Si lo que quieres es volcar el resultado filtrado a otra hoja como una lista nueva, usa la función FILTRAR (FILTER). Está en Excel 2021, 2024 y Microsoft 365.

Supón que quieres en la hoja Ventas grandes todas las filas de la hoja original con importe mayor que 300. En la celda A1 de la nueva hoja escribe:

=FILTRAR(Ventas!A2:C13;Ventas!C2:C13>300;"Sin resultados")

Excel devuelve, desbordadas hacia abajo y a la derecha, solo las filas cuyo importe supera 300. Si el rango destino está ocupado, aparece el error #¡DESBORDAMIENTO!; basta con dejar hueco.

Pronóstico y correlación: lo que las cifras insinúan

Resumir y comparar miran al pasado. Para mirar al presente y al futuro, Excel tiene COEFICIENTE.CORRELACION (CORREL) y PRONOSTICO.LINEAL (FORECAST.LINEAR). Crea una hoja nueva, Cierre mensual 2026, con el total de cada mes.

Para saber si el mes siguiente hereda el crecimiento del anterior, la correlación entre cada mes y el siguiente se calcula con:

=COEFICIENTE.CORRELACION(B2:B12;B3:B13)

Con los datos de ejemplo da 0,99, una correlación casi perfecta: si el mes anterior sube, el siguiente también. Para dibujarlo, selecciona A1:B13 y abre:

Sobre la serie, clic derecho y Agregar línea de tendencia > Lineal muestra la dirección; con Mostrar ecuación en el gráfico ves la recta y puedes extrapolar el siguiente valor a mano.

Escenarios a mano con Buscar objetivo

A veces la pregunta es al revés: «si quiero llegar a 12.000 en el trimestre, cuánto tengo que vender en abril». Para eso sirve Buscar objetivo: cambias una celda hasta que otra llega al valor que le pides.

1. En una celda nueva, B14, escribe =SUMA(B2:B4)+B5, donde B5 será abril.
2. Abre Datos > Previsión > Análisis de hipótesis > Buscar objetivo.
3. Definir la celda: B14. Con el valor: 12000. Cambiando la celda: B5.
4. Acepta.

Excel ajusta B5 hasta que B14 vale 12.000. Si la solución es imposible, muestra un mensaje y deja el valor anterior.

Errores que aparecen al analizar y cómo arreglarlos

Cuando las cifras se mezclan con textos, celdas vacías o referencias mal escritas, aparecen errores. Los más comunes al analizar son cinco:

• #¡DIV/0! al dividir. La celda del divisor está vacía o vale cero. Se evita con =SI.ERROR(A2/B2;0), que devuelve 0 en vez del error.
• #N/A en BUSCARV. La clave que buscas no está en la primera columna del rango. Revisa que el dato exista y que el rango empiece donde crees.
• Tabla dinámica con totales viejos. La tabla no se actualiza sola al cambiar los datos. Pulsa Alt+F5 o Analizar tabla dinámica > Actualizar.
• Formato condicional que pinta la fila de arriba. La fórmula de la regla se escribió pensando en la celda equivocada. Hay que escribirla siempre para la primera celda del rango, con referencias relativas.
• División entre cero al cambiar una celda a texto. Una columna que era Número se convirtió en Texto al pegar valores. Vuelve a darle formato Número con Ctrl+1 > Número y vuelve a introducir la fórmula.

La herramienta de análisis que no aparece en la cinta

Solver y el paquete Herramientas para análisis (que incluye Estadística descriptiva, Regresión, Histograma y otras) son complementos: no están en la cinta hasta que los activas. Para activarlos:

En la lista, marca Solver y Herramientas para análisis y acepta. Aparecerán en Datos > Análisis y Datos > Análisis de datos respectivamente.

Lo que tu hoja puede y lo que todavía no

Una fórmula cubre decenas de miles de filas sin despeinarse. La función SUBTOTALES (SUBTOTAL), con el código 9 o 109, suma solo lo que está visible después de un filtro, así que al filtrar la tabla el total se actualiza sin tocar la fórmula. =SUBTOTALES(9;C2:C200) lo demuestra en la hoja Ventas 2026.

Para resumir con fórmula, Microsoft 365 trae AGRUPARPOR (GROUPBY), que devuelve un mini resumen por grupo sin tabla dinámica. Es cómodo, pero solo está en Microsoft 365 y Excel para la web; en Excel 2024 y anteriores hay que usar tabla dinámica o Power Query.

Cuando el libro pasa del millón de filas, las hojas se quedan cortas. Ahí entra Power Query (Datos > Obtener y transformar datos), que mueve la limpieza y el resumen a un paso reproducible, fuera de la cuadrícula. No es para hacer cuentas celda a celda: es para transformar tablas enteras, grabando los pasos en un lenguaje distinto (M) y volviendo a aplicarlos con un clic en Actualizar.

Lo que ya sabes hacer con análisis cuantitativo en Excel

• Resumir una columna con PROMEDIO, MAX, MIN, CONTAR y CONTARA, y leer la barra de estado sin escribir nada.
• Comparar distribuciones con MEDIANA, MODA, PERCENTIL y DESVEST.P o DESVEST.M, y cruzar variables con una tabla dinámica.
• Limpiar la hoja antes de contar con Validación de datos, Quitar duplicados y Pegado especial, y pintarla con formato condicional de escala o de barras.
• Filtrar y segmentar con la tabla dinámica y, si hace falta, volcar el resultado a otra hoja con FILTRAR.
• Anticipar el siguiente paso con COEFICIENTE.CORRELACION, gráficos de línea con tendencia y Buscar objetivo.

El siguiente paso natural es una regresión lineal sobre Cierre mensual 2026: introduce los doce meses en PRONOSTICO.LINEAL, obtén la pendiente y el punto de corte, y comprueba en el gráfico que la línea de tendencia coincide con la recta de la fórmula. Con eso, predecir el cierre del mes que viene pasa de intuición a número.

Dudas al hacer análisis cuantitativo en Excel