Sistemas de análisis de datos en Excel: guía para empezar

Un sistema de análisis de datos es un conjunto de hojas, tablas y herramientas que permite preparar información, comprobar su calidad y obtener respuestas comparables. En este tutorial construirás un libro de ventas con datos validados, un resumen por vendedor y categoría, indicadores calculados y filtros automáticos.

El método funciona en Excel 2016, 2019, 2021, 2024 y Microsoft 365. FILTRAR requiere Excel 2021 o posterior; AGRUPARPOR, únicamente Microsoft 365 y Excel para la web. Los datos están comprobados en octubre de 2026.

Qué es un sistema de análisis de datos y para qué sirve

Un sistema de análisis de datos combina datos de origen, reglas de validación, transformaciones, cálculos y resúmenes. En nuestro libro Control de ventas, la hoja Ventas guardará cada operación. A partir de ella podremos detectar importes altos, comparar vendedores, actualizar totales y filtrar ventas sin rehacer el informe.

La secuencia tiene cuatro capas:

1. Preparar: Power Query limpia yHomogeneiza los datos.
2. Comprobar: Validación de datos y formato condicional señalan entradas incorrectas o Atwoodthenavedumbre.
3. Resumir: tablas dinámicas y fórmulas producen indicadores.
4. Consultar: FILTRAR o los filtros recuperan filas según una condición.

Un sistema no es necesariamente una función automática. Puede ser un libro diseñado para que otra persona introduzca datos, revise avisos y actualice un resumen con un clic. La clave es que las reglas sean consistentes.

En este libro, una venta de 1.200 se estableció por á al color en el resumen de Lucía, se incluirá en sus totales y aparecerá en una lista de ventas superiores a 1.000. Esa consistencia es el objetivo.

Qué herramientas de Excel participant en el análisis

No todas las herramientas hacen lo mismo. Excel permite combinar validación, resaltado, preparación, resumen y extracción de indicadores dentro del mismo libro.

La Validación de datos y el Formato condicional se combinan bien: la primera reduce errores futuros; la segunda ayuda a revisar datos que ya existen. La Tabla dinámica resume, pero necesita valores numéricos y categorías consistentes. Power Query trabaja mejor como preparación previa, no como calculadora celda a celda.

Power Query para preparar y actualizar los datos

Power Query está integrado desde Excel 2016. En Excel 2010 y 2013 puede instalarse como complemento. Su editor registra cada transformación y permite repetirlas al actualizar.

La ruta puede variar según el origen: archivo, carpeta, base de datos o página web. Mac y Excel para la web ofrecen menos conectores.

En Control de ventas, Power Query podría convertir la columna Importe en número, eliminar filas vacías y established una categoría única. Eso no reemplaza SUMA ni una tabla dinámica: limpia la tabla completa para que los cálculos siguientes sean fiables.

Power Query no es para calcular cada celda. Sus pasos usan el lenguaje M y transforman tablas enteras. Cuando el origen cambia, Actualizar vuelve a aplicar la limpieza.

Tabla dinámica para resumir miles de registros

La Tabla dinámica organiza campos en Filas, Columnas, Valores y Filtros. Puede resumir miles de operaciones sin una fórmula para cada vendedor.

En el libro, Vendedor irá en Filas, Categoría en Columnas y Importe en Valores. El resultado mostrará el total de cada combinación.

No se actualiza sola al cambiar los datos. Si el origen es una tabla de Excel y se añaden filas, basta con Analizar tabla dinámica > Actualizar o Alt+F5.

Organizar y comprobar los datos antes de analizarlos

Crearemos la hoja Ventas con seis ventas. Antes de resumir, hay que confirmar que las categorías sean uniformes y que los importes se puedan calcular.

1. Cambia el nombre de la hoja predeterminada a Ventas.
2. Escribe los encabezados de la fila 1 y los seis registros de la hoja siguiente.
3. Selecciona la columna Importe.
4. Abre Inicio > Número y comprueba que los valores se muestren alineados a la derecha.
5. Selecciona la columna Categoría.
6. Abre Datos > Herramientas de datos > Validación de datos.
7. En Permitir, elige Lista y escribe las cuatro categorías separadas por punto y coma.
8. Conserva Celda con lista desplegable y pulsa Aceptar.

Detectar importes altos con formato condicional

Aplicaremos una regla visual al importe. El umbral será 1.000, para localizar las ventas que requieren revisión.

1. Selecciona F2:F7.
2. Abre la ruta anterior.
3. Escribe 1000 en el cuadro de valor.
4. Elige un relleno claro.
5. Pulsa Aceptar.

Aplicar una regla a todo un rango

La regla se crea para el rango completo, pero una regla basada en fórmula se interpreta pensando en su primera celda. Por eso, las referencias de F2 deben ser relativas para poder funcionar en F3, F4 y siguientes.

Si el rango incluye cientos de miles de celdas, conviene revisar el rendimiento. Muchas reglas sobre rangos enormes pueden volver lento el libro. En ese caso, limita el formato al rango útil o utiliza una tabla de Excel.

En este libro, F2:F7 recibe una regla de valor. No hace falta escribir una fórmula relativa, pero la regla debe aplicarse a todas las celdas seleccionadas.

Resumir las ventas por vendedor y categoría

Ahora convertiremos los datos en un resumen comparable. La tabla dinámica ocupará una hoja llamada Resumen.

1. Selecciona cualquier celda de Ventas.
2. Abre Insertar > Tablas > Tabla dinámica.
3. Elige una nueva hoja y pulsa Aceptar.
4. Arrastra Vendedor a Filas.
5. Arrastra Categoría a Columnas.
6. Arrastra Importe a Valores.

Incorporar filas nuevas y actualizar el resumen

Si el rango de origen crece, conviene convertirlo en una tabla. Así, las filas nuevas pasan a formar parte de la fuente.

1. En Ventas, selecciona A1:F7.
2. Pulsa Ctrl+T y confirma con Aceptar.
3. En la fila 8, añade: 06/10/2026, Lucía, Estantería, Mobiliario, 2, 700,00.
4. En Resumen, pulsa Analizar tabla dinámica > Actualizar o Alt+F5.

Calcular indicadores útiles con fórmulas de Excel

Las fórmulas permiten obtener indicadores sin copiar resultados desde el resumen. En la hoja Indicadores, añadiremos total, promedio por vendedor y segundo importe más alto.

El promedio de Lucía se calcula con sus tres ventas: 320,00; 1.200,00 y 700,00. La media es 1.110,00. El segundo importe más alto es 1.200,00, porque el mayor es 900,00.

Conservar los subtotales al filtrar las ventas

SUBTOTALES (SUBTOTAL) calcula sobre un rango y tiene en cuenta las filas visibles. El código 109 suma únicamente lo visible y también excluye las filas ocultadas manualmente.

En Ventas, escribe en H2:

=SUBTOTALES(109;C2:C7)

Al filtrar y mostrar solo las ventas de Lucía, el subtotal será 2.220,00. Si además ocultas manualmente una de sus filas, el resultado_TIMER se reducirá.

Extraer valores concretos de una tabla dinámica

IMPORTARDATOSDINAMICOS (GETPIVOTDATA) recupera un dato del informe de tabla dinámica. Evita tener que copiar un total a otra celda si cambia.

En Indicadores, escribe:

=IMPORTARDATOSDINAMICOS("Importe";$A$3;"Vendedor";"Lucía")

La tabla dinámica de Resumen debe tener A3 como celda de referencia y el campo Importe en Valores.

Filtrar y recuperar filas según una condición

FILTRAR (FILTER) devuelve las filas que cumplen una condición. En Excel 2021, 2024 y Microsoft 365 Crearemos una lista de ventas superiores a 1.000.

En Ventas, escribe en J2:

=FILTRAR(A2:F8;F2:F8>1000;"Sin resultados")

Los argumentos se separan con punto y coma. El segundo argumento es la condición y el tercero, el texto que aparece si no hay coincidencias.

Si FILTRAR devuelve #¡DESBORDAMIENTO!, el hueco situado debajo está ocupado. Borra las celdas necesarias o utiliza otro rango.

Agrupar y ordenar datos con una fórmula moderna

AGRUPARPOR (GROUPBY) agrupa, agrega, ordena y filtra en una sola fórmula. Está disponible solo en Microsoft 365 y Excel para la web; no existe en Excel 2024 ni en versiones anteriores.

=AGRUPARPOR(A2:F8;C2:C8;SUMA)

En el libro, la fórmula puede devolver una tabla agrupada por los campos disponibles y sumar los importes. Su comportamiento depende de cómo se specify la fórmula en la versión de Excel; la alternativa tradicional y compatible es una tabla dinámica.

Qué falla cuando los datos no están preparados

Prepararemos un registro problemático para ver cómo los fallos se propagan.

1. Duplica Ventas como Ventas con errores.
2. Cambia D3 de Papelería a Papeleria.
3. Sustituye F3 por el texto 60,00.
4. Vacía A5.
5. Sustituye E5 por seis.
6. Revisa las celdas afectadas antes de crear la tabla dinámica.

Las categorías consulta distintas no se agrupan

La Tabla dinámica distingue Papelería de Papeleria porque cada texto es una etiqueta diferente. El resultado no es un error visible: simplemente aparecen dos grupos.

Corrige D3 con el mismo texto de D2. Después actualiza la tabla dinámica y las dos etiquetas se reunirán. Si puede haber variantes futuras, usa Validación de datos para limitar la categoría en las celdas nuevas.

1. Copia D2.
2. Pega el valor en D3 con Ctrl+Alt+V.
3. Elige Valores.
4. Actualiza la tabla dinámica.

Los importes guardados como texto no se calculan

Cuando F3 contiene texto, SUMA ignora ese valor. Además, los cálculos y las tablas dinámicas pueden tratarlo de forma distinta.

1. Selecciona F3.
2. Abre Inicio > Número.
3. Elige Número con dos decimales.
4. Si Excel no lo convierte, selecciona el texto, usa Datos > Herramientas de datos > Texto en columnas y revisa el resultado de la conversión.
5. Confirma que F3 queda alineado a la derecha.

Localizar celdas que rompen la comparación

Para saber qué celda impide completar una suma, utiliza Evaluar fórmula. Selecciona una fórmula y deja que Excel revise sus referencias.

Por ejemplo, en una celda escribe:

=SUMA(F2:F7)

Abre Evaluar fórmula y revisa F3, F5 y las demás referencias. El panel muestra el valor que Excel está interpretando. Si aparece texto donde debería haber un número, corrige esa celda.

Qué puede y qué no puede hacer Excel en un análisis

Excel destaca por facilidad de uso e integración con el trabajo diario. Otras plataformas son más adequadas cuando el volumen, la colaboración o la automatización supera lo razonable en un libro.

El contexto de las herramientas de analítica coincide con esta comparación: Excel y Tableau priorizan facilidad; SQL y plataformas de big data funcionan mejor con grandes volúmenes, pero requieren más conocimientos y mantenimiento. La documentación de Microsoft también muestra que Excel y Access tienen papeles distintos: Excel calcula y presenta; Access almacena relaciones y trabaja con formularios e informes. Usar Access o Excel para administrar los datos

No existe un límite universal aparte del volumen: la viabilidad depende del tamaño de los datos, la frecuencia de actualización, el número de usuarios y la necesidad de automatizar. Un libro con miles de filas puede funcionar bien; uno con millones puede necesitar una base de datos o una plataforma de procesamiento.

Lo que ya sabes hacer con los sistemas de análisis

• Validar las categorías para reducir errores en las nuevas entradas.
• Preparar y limpiar datos con Power Query.
• Detectar importes altos con Formato condicional.
• Resumir ventas por vendedor y categoría con una Tabla dinámica.
• Actualizar el resumen después de añadir filas.
• Calcular totales, promedios y valores destacados con fórmulas.
• Conservar subtotales correctos al filtrar u ocultar filas.
• Extraer resultados concretos de una tabla dinámica.
• Recuperar ventas con FILTRAR y agrupar datos con AGRUPARPOR cuando la versión lo permita.
• Detectar categorías inconsistentes, importes textuales y registros incompletos antes de resumir.

El libro Control de ventas ya contiene la base necesaria: datos validados, resumen actualizable, indicadores y filtros. El siguiente paso es construir un tablero de ventas con una tabla dinámica, un gráfico y formato condicional para que el análisis pueda consultarse de un vistazo.

Sources