Tratamiento de datos estadísticos en Excel con un caso real

El tratamiento de datos estadísticos consiste en limpiar una lista de ventas y convertirla en medidas útiles: total, media, valores extremos, grupos por categoría y señales de error. En este tutorial usarás un libro llamado VentasPapeleria.xlsx, con una hoja Ventas, para analizar qué productos conviene mantener. Las herramientas mostradas están disponibles en Excel 2016, 2019, 2021, 2024 y Microsoft 365; algunas funciones requieren versiones posteriores. Los datos están comprobados en octubre de 2026.

Qué es el tratamiento de datos estadístico y cuándo conviene usarlo

El tratamiento de datos estadístico organiza información para responder preguntas concretas. En el libro VentasPapeleria.xlsx, la hoja Ventas reúne ventas de papelería. Al principio contiene encabezados inconsistentes, una categoría escrita como «Papeleria», una fila vacía y una venta repetida.

El trabajo comienza con limpiar la base. Después se calculan indicadores como el promedio, la mediana, el máximo, el mínimo y el segundo importe más alto. Una tabla dinámica permite comparar categorías y vendedores. La validación de datos evita nuevos importes fuera del rango permitido, mientras que el formato condicional resalta ventas altas, importes bajos y duplicados.

Estas técnicas sirven cuando necesitas comparar ventas, detectar anomalías y justificar qué productos mantener. No sustituyen una revisión de los datos: la validación de datos no corrige lo que ya está escrito ni lo que se pega encima. Los datos comprobados en octubre de 2026.

Las funciones que calculan el resumen estadístico

Las funciones estadísticas resuelven preguntas puntuales sobre un rango. Se usan cuando quieres un resultado rápido sin construir un informe completo. En la hoja Ventas, los importes están en B2:B8.

PROMEDIO: el valor medio de una serie

La media aritmética reparte el total entre todas las ventas. No distingue una venta normal de una exceptional.

=PROMEDIO(B2:B8)

MEDIANA: el valor central sin quedar influido por extremos

La mediana ordena los importes y toma el valor central. En esta serie, los seis valores menores suman 1.020,00 y el valor central es 180,00. La venta de 450,00 no desplaza tanto ese centro como lo hace la media.

=MEDIANA(B2:B8)

MAX y MIN: los extremos de los datos

MAX identifica la venta más alta y MIN, la más baja. Son útiles para localizar expenditures extremos y revisar si parecen errores.

=MAX(B2:B8)

=MIN(B2:B8)

K.ESIMO.MAYOR: encontrar un valor entre los más altos

K.ESIMO.MAYOR devuelve el k-ésimo mayor. Con k = 2, encuentra la segunda venta más alta sin ordenar toda la columna.

=K.ESIMO.MAYOR(B2:B8;2)

SUBTOTALES: recalcular con filas filtradas u ocultas

SUBTOTALES calcula un subtotal y puede ignorar las filas excluidas por un filtro. El código 9 suma ignorando lo filtrado. El código 109 también ignora las filas ocultadas manualmente. Esta diferencia importa en informes donde ocultar filas sustituye al filtrado.

=SUBTOTALES(109;B2:B8)

1. Escribe la fórmula en B9 de la hoja Ventas.
2. Selecciona B2:B8 y aplica un filtro con Ctrl+Mayús+L.
3. Elige Tecnología en el filtro de B1.
4. Pulsa Alt+F5 para recalcular la hoja.
5. Compara B9 con =SUBTOTALES(9;B2:B8).

Preparar una base de ventas limpia para analizarla

Una base limpia tiene encabezados claros, fechas reconocibles, categorías coherentes y una sola fila por venta. En la hoja Ventas, el rango inicial es A1:D8.

Revisar encabezados, fechas y valores duplicados

1. Revisa A1:D1 y escribe Fecha, Categoría, Vendedor e Importe con mayúscula inicial.
2. Selecciona B2:B8 y usa una tabla auxiliar de categorías para detectar «Papeleria».
3. Sustituye «Papeleria» por «Papelería».
4. Selecciona A2:D8 y usa Datos > Herramientas de datos > Duplicados para localizar repeticiones.
5. Revisa la fila duplicada y conserva una sola venta.

Convertir la base en una tabla de Excel

Ahora convertimos la base en una tabla para mantenerla ordenada y preparada para crecer.

1. Selecciona A1:D8 en la hoja Ventas.
2. Abre Insertar > Tablas > Tabla.
3. Confirma que la casilla La tabla tiene encabezados está marcada.
4. Pulsa Aceptar.
5. Escribe TablaVentas en el cuadro Nombre de la tabla.

Resumir las ventas por categoría y vendedor

La tabla dinámica permite comparar grupos sin construir una fórmula para cada combinación. En este libro, Categoría irá en Filas, Vendedor en Columnas e Importe en Valores.

Construir la tabla dinámica con totales por categoría

1. Selecciona cualquier celda de TablaVentas.
2. Abre Insertar > Tablas > Tabla dinámica.
3. Elige un destino nuevo en la hoja Resumen.
4. Arrastra Categoría al área Filas.
5. Arrastra Vendedor al área Columnas.
6. Arrastra Importe al área Valores.
7. Activa Suma de Importe en el panel de valores.

Filtrar la tabla para analizar un subconjunto

1. Pulsa la flecha del filtro de Vendedor en la tabla dinámica.
2. Desactiva Luis.
3. Pulsa Aceptar.
4. Observa cómo Papelería mantiene 600,00 y Tecnología queda en blanco.
5. Usa Alt+F5 para recalcular cuando sea necesario.

Con todos los registros visibles

En la hoja Ventas, calcula el total de la serie completa.

=SUBTOTALES(109;B2:B8)

Con una venta ocultada a mano

Ahora oculta la fila 6, correspondiente a la venta de Tecnología de 320,00, y compara los códigos.

=SUBTOTALES(9;B2:B8)

=SUBTOTALES(109;B2:B8)

Detectar los datos que no cumplen las reglas del análisis

La validación de datos protege las nuevas entradas, pero no sanea el contenido existente. Para el libro, se admitirán fechas de 2026 e importes entre 0 y 1.000.

Limitar fechas e importes con validación

1. Selecciona A2:A8 en la hoja Ventas.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Fecha.
4. En Datos, elige entre.
5. Escribe 01/01/2026 en Fecha inicial y 31/12/2026 en Fecha final.
6. Repite desde el paso 2 con D2:D8.
7. En Permitir, elige Decimal.
8. En Datos, elige entre.
9. Escribe 0 en Mínimo y 1000 en Máximo.
10. Pulsa Aceptar.

Rodear las ventas que ya incumplen la validación

La hoja contiene ahora dos incidencias: una fecha de 2025 y un importe de 1.500. La herramienta de datos no válidos permite localizarlas.

1. Selecciona A2:A8.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. Elige Rodear con un círculo datos no válidos.
4. Repite la operación para D2:D8.
5. Corrige las celdas señaladas y vuelve a comprobar la validación.

Resaltar ventas altas, importes bajos y duplicados

El formato condicional cambia la apariencia de las celdas según sus valores. En la hoja Ventas, se resaltarán importes superiores a 300 y registros repetidos.

Comparar cada importe con un umbral

1. Selecciona D2:D8.
2. Abre Inicio > Estilos > Formato condicional.
3. Elige Resaltar celdas > Mayor que.
4. Escribe 300 en el campo de valor.
5. Elige Relleno rojo claro con texto rojo oscuro.
6. Pulsa Aceptar.

Localizar registros duplicados

Para encontrar una venta repetida por vendedor y fecha, creamos una regla sobre dos columnas.

1. Selecciona A2:C8.
2. Abre Inicio > Estilos > Formato condicional.
3. Elige Resaltar reglas de celdas > Valores duplicados.
4. Elige un formato de relleno visible.
5. Pulsa Aceptar.

En esta base, la venta duplicada se crea en una fila adicional para que la detección sea visible.

Actualizar y automatizar el tratamiento de datos

La tabla dinámica no se actualiza sola al cambiar los datos. Al añadir una venta a TablaVentas, debes actualizar el informe.

Mantener la tabla dinámica al crecer los datos

1. Selecciona la última celda de TablaVentas.
2. Pulsa Ctrl+Entrar para añadir una fila, si el rango no se ha ampliado automáticamente.
3. Escribe 18/01/2026, Papelería, Luis y 150,00.
4. Ve a la hoja Resumen.
5. Abre Analizar tabla dinámica > Actualizar.
6. Comprueba que el total de Papelería sube de 600,00 a 750,00.

Atajo: Alt+F5.

Qué funciones y herramientas encajan con cada tarea

Cada herramienta tiene un alcance distinto. Las funcionesهات responden preguntas puntuales; las herramientas de hoja modifican, resumen o revisan la base.

Cuándo usar Power Query para limpiar la base

Power Query está integrado desde Excel 2016. En Excel 2010 y 2013 puede utilizarse como complemento. Excel para Mac y Excel para la web ofrecen menos conectores.

Para VentasPapeleria.xlsx, Power Query puede corregir encabezados, convertir fechas, unificar «Papeleria» como «Papelería» y eliminar filas vacías. Los pasos quedan guardados y se vuelven a aplicar al pulsar Actualizar.

Cuándo elegir una función en lugar de una tabla dinámica

Usa PROMEDIO cuando necesites la media de todos los importes. Usa MEDIANA cuando un valor alto no deba desplazar el centro. Usa K.ESIMO.MAYOR para localizar una venta concreta entre las mayores.

=PROMEDIO(TablaVentas[Importe])

=MEDIANA(TablaVentas[Importe])

=K.ESIMO.MAYOR(TablaVentas[Importe];2)

La tabla dinámica es mejor para comparar muchas combinaciones: categoría, vendedor, mes o producto. Una función da un resultado puntual; la tabla dinámica organiza un informe completo y permite filtrar grupos.

Lo que ya sabes hacer con datos estadísticos

• Limpiar encabezados, categorías, fechas y duplicados en una base de ventas.
• Convertir el rango en una tabla de Excel preparada para crecer.
• Calcular el promedio, la mediana, el máximo, el mínimo y el segundo valor mayor.
• Resumir ventas por categoría y vendedor con una tabla dinámica.
• Comparar subtotales con filas filtradas y ocultadas manualmente.
• Limitar nuevas fechas e importes mediante validación de datos.
• Localizar valores que incumplen las reglas y registros repetidos.
• Resaltar importes altos y duplicados mediante formato condicional.
• Actualizar la tabla dinámica cuando entra una venta nueva.

El siguiente paso es combinar la tabla dinámica con formato condicional para revisar rápidamente qué categorías y vendedores concentran las ventas más altas.