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

El análisis de datos experimentales consiste en ordenar las observaciones de un experimento, calcular indicadores y comparar sus resultados sin perder de vista las condiciones de cada prueba. En este tutorial trabajarás con las ventas de dos productos de prueba en un libro de Excel: prepararás los datos, resumirás los resultados y crearás un gráfico. El método se aplica en Excel 2007 o posterior, Microsoft 365, Excel para la web, Mac y móviles; algunas funciones de Excel para la web pueden tener disponibilidad limitada.

Los datos del tutorial están comprobados en octubre de 2026.

Qué es el análisis de datos experimentales

El análisis experimental busca responder una pregunta concreta a partir de observaciones registradas. Para este libro, la pregunta será: «¿Qué producto de prueba generó más importe y cómo evolucionaron sus ventas entre enero y marzo?».

Cada fila de la tabla representará una observación. Las columnas registrarán las variables del experimento: grupo, producto, fecha, unidades vendidas e importe. Después, Excel permitirá calcular el total, el promedio y los valores máximos; comparar los grupos mediante una tabla dinámica; y presentar la diferencia con un gráfico.

Este tutorial se centra en un análisis descriptivo: muestra qué ocurrió en los datos. No sustituye un diseño experimental controlado, una prueba de hipótesis ni un modelo estadístico avanzado. Para una prueba de productos, conviene además documentar qué grupo recibió cada condición y mantener esa asignación durante todo el registro.

El flujo de trabajo para analizar un experimento

Un flujo útil evita calcular sobre datos que todavía cambian. El orden será:

1. Definir la pregunta: ¿qué comparación u objetivo debe responder el análisis?
2. Preparar los datos: eliminar duplicados, completar faltantes y normalizar fechas y categorías.
3. Calcular indicadores: obtener totales, promedios y valores destacados.
4. Comparar grupos: resumir los registros con una tabla dinámica.
5. Visualizar: crear un gráfico que haga evidente la diferencia.
6. Comunicar: conservar el libro, las fórmulas y la explicación de cada indicador.

En este libro, cada paso conserva la información anterior. La tabla dinámica y el gráfico se construyen a partir de la misma tabla de resultados, de modo que cualquier cambio pueda revisarse y actualizar.

Definir la pregunta antes de abrir Excel

La pregunta debe ser medible y refieren qué observación representa el resultado. En vez de «¿funcionó mejor el producto?», usaremos «¿Qué grupo produjo el mayor importe total entre enero y marzo?».

Las variables quedan así:

El importe es una variable calculada: unidades vendidas por precio del producto. La pregunta y la definición de variables deben fijarse antes de modificar los datos; cambiar la pregunta durante el análisis puede dar una interpretación inconsistente.

Preparar los datos antes de calcular

Antes de usar SUMA, una tabla dinámica o un gráfico, comprueba que la hoja sea consistente:

• Una fila representa una observación y una columna representa una variable.
• El grupo y el producto se escriben siempre con la misma etiqueta.
• Las fechas son fechas reales, no texto.
• El importe coincide con las unidades y el precio.
• No hay observaciones repetidas por error.

Cuando el libro crezca, Power Query puede automatizar la importación y la limpieza. Está integrado desde Excel 2016; en Excel 2010 y 2013 se utiliza como complemento. Consulta la documentación oficial de Power Query.

Organizar los resultados en una tabla de Excel

Crearemos una tabla con seis observaciones. El precio se incorpora como campo auxiliar para verificar el importe, pero la comparación principal se hará con el resultado registrado.

1. Cambia el nombre de la hoja a Resultados.
2. En A1:E1, escribe los encabezados: Grupo, Producto, Fecha, Unidades vendidas e Importe.
3. Introduce las seis filas de la hoja siguiente.
4. Selecciona A1:E7.
5. En la cinta, abre Insertar > Tablas > Insertar tabla.
6. Conserva La tabla tiene encabezados y pulsa Aceptar.

El precio del producto A es 3,00 y el del producto B es 4,00. Así, 12 unidades por 3,00 producen 36,00 y 18 por 4,00 producen 72,00.

Separar las variables y los resultados medidos

Una tabla de Excel permite filtrar y trabajar con cada observación sin copiar fórmulas a rangos separados. Las variables explicativas ocupan las columnas de grupo, producto y fecha; el resultado medido se registra en unidades vendidas. El importe es un resultado derivado.

En las versiones modernas, esta tabla puede alimentar directamente una tabla dinámica y expandirse al añadir filas. La expansión automática es una ventaja de las tablas de Excel; no debe confundirse con la actualización automática de una tabla dinámica, que sí requiere pulsar Actualizar.

Revisar duplicados y valores vacíos

Para revisar la calidad de los datos, utiliza dos herramientas de Excel:

• Duplicados: selecciona A1:E7, abre Datos > Herramientas de datos > Quitar duplicados y revisa el aviso. No pulses Aceptar hasta comprobar qué registros corresponden.
• Celdas vacías: usa Ctrl+G, escribe A1:E7, pulsa Entrar y después F5 > Ir a especial > Especial > Celdas vacías. Excel seleccionará las que no tienen información.

Si una fecha está vacía, no la inventes sin revisar el cuaderno de observaciones. Si falta una unidad, corrige el dato fuente. En una tabla dinámica, una celda vacía seresume como “en blanco”, pero ese valor no representa por sí mismo una condición experimental.

Para localizar visualmente incidencias, Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Celdas vacías puede ayudarlo. El formato condicional no modifica los datos: solo los resalta.

Calcular indicadores del experimento con funciones de Excel

Añadiremos cuatro indicadores bajo la tabla: total, promedio, mayor importe y segundo mayor importe. Estarán en A10:A13; los resultados, en B10:B13.

1. Selecciona B10 y escribe =SUMA(E2:E7).
2. En B11, escribe =PROMEDIO(E2:E7).
3. En B12, escribe =MAX(E2:E7).
4. En B13, escribe =K.ESIMO.MAYOR(E2:E7;2).

=SUMA(E2:E7)
=PROMEDIO(E2:E7)
=MAX(E2:E7)
=K.ESIMO.MAYOR(E2:E7;2)

La suma es 363,00: 36 + 45 + 72 + 80 + 30 + 100. El promedio es 363 / 6 = 60,50. El valor mayor es 100,00 y el segundo mayor es 80,00.

Usar SUBTOTALES para comparar solo las filas visibles

SUBTOTALES (SUBTOTAL) calcula un indicador sobre los registros visibles de una lista o tabla. Su sintaxis es =SUBTOTALES(núm_función; ref1; [ref2]; ...). El código 109 suma las filas visibles y también excluye las ocultadas manualmente; el código 9 solo tiene en cuenta el filtrado.

Para crear el subtotal:

1. Selecciona A1:E7.
2. Pulsa Ctrl+Mayús+L para activar el filtro.
3. En E1, filtra para mostrar solo Prueba.
4. Selecciona A9 y escribe Total visible.
5. En B9, escribe =SUBTOTALES(109;E2:E7).

=SUBTOTALES(109;E2:E7)

El total de Prueba es 72 + 80 + 100 = 252,00. Al quitar el filtro, B9 volverá a 363,00.

Obtener el valor máximo y el segundo máximo

K.ESIMO.MAYOR (LARGE) devuelve el valor que ocupa una posición determined en un conjunto ordenado de mayor a menor: =K.ESIMO.MAYOR(matriz;k). Con k=2, obtiene el segundo mayor importe.

En este libro, el primer resultado es 100,00, correspondiente a 25 unidades de Producto B. El segundo es 80,00, correspondiente a 20 unidades del mismo producto. La función identifica el valor; no explica qué condición experimental lo produjo. Para eso se necesitarás una tabla dinámica.

Una ventaja de K.ESIMO.MAYOR es que evita ordenar el rango. Si el conjunto contiene celdas vacías, k debe corresponder al número de valores disponibles. Si Excel muestra #¿NOMBRE?, la función no está disponible en esa versión o el nombre local no es el esperado.

Comparar grupos con una tabla dinámica

La tabla dinámica resume el libro por categorías sin escribir fórmulas por grupo. En este experimento permitirá contrastar el importe de Control y Prueba y observar los meses.

1. Selecciona A1:E7.
2. Abre Insertar > Tablas > Tabla dinámica.
3. Elige Desde una tabla o intervalo.
4. Confirma que el rango es Resultados!$A$1:$E$7.
5. Elige Hoja de trabajo nueva y pulsa Aceptar.

Colocar grupo, fecha e importe en la tabla dinámica

En el panel de campos:

1. Arrastra Grupo a Filas.
2. Arrastra Fecha a Columnas.
3. Arrastra Importe a Valores.
4. Si Excel crea subtotales por fecha, desactívalos desde Analizar tabla dinámica > Diseñar > Subtotales.

El resumen resultante es:

En enero, Control suma 36 + 45 = 81,00 y Prueba suma 72 + 80 = 152,00. En febrero, los resultados son 30,00 y 100,00. La tabla permite ver que la diferencia entre grupos no se mantiene igual en ambos periodos; por eso conviene revisar cada condición antes de atribuir causalidad al resultado.

La ayuda oficial para crear una tabla dinámica explica el flujo de campos y actualización.

Actualizar el resumen cuando cambien los datos

Las tablas dinámicas no se actualizan solas al cambiar los datos. Añadiremos otra observación y refrescaremos el resumen.

1. En Resultados, añade una fila: Prueba, Producto B, 05/03/2026, 10, 40,00.
2. Vuelve a Tabla dinámica1.
3. En la cinta, abre Analizar tabla dinámica > Actualizar.
4. Revisa el nuevo mes y el total.

Tras actualizar, marzo mostrará 40,00 para Prueba y el total general será 403,00. El atajo Alt+F5 también actualiza todas las tablas dinámicas del libro.

Visualizar el experimento con un gráfico de Excel

Ahora transformaremos el resumen en un gráfico de columnas. La hoja Tabla dinámica1 debe tener los totales por grupo en B2:B3 y C2:C3.

1. Selecciona A2:C3 en Tabla dinámica1.
2. Abre Insertar > Gráficos > Insertar gráfico de columnas o barras.
3. Elige Columna agrupada.
4. Cambia el título a Importe total por grupo y mes.
5. En Elementos de gráfico > Leyenda, conserva la leyenda.
6. En Elementos de gráfico > Ejes, añade el título Importe al eje vertical.

El gráfico tendrá dos series —enero y febrero— y dos categorías: Control y Prueba. Su propósito no es Sua decoración, sino permitir comparar la altura de las columnas dentro de cada grupo.

Elegir el gráfico según la pregunta

Un gráfico de columnas funciona mejor para comparar categorías: Control frente a Prueba. Un gráfico de líneas funciona mejor para observar cambios a lo largo del tiempo: enero, febrero y marzo. En ambos casos, las unidades del eje deben ser las mismas y los periodos deben estar en orden cronológico.

No uses un gráfico de dispersión para estas seis observaciones: su propósito es mostrar la relación entre dos variables numéricas. Tampoco conviertas el gráfico en sustituto de las cifras: conserva el resumen y las fórmulas que lo alimentan.

Cuándo Excel no es suficiente para el análisis experimental

Excel funciona bien para tablas de tamaño intermedio, controles de calidad, cálculos descriptivos, tablas dinámicas y gráficos. Si las observaciones crecen a cientos de miles o millones, recalcular archivos grandes puede resultar lento.

• Python con pandas: automatiza la limpieza, une archivos y procesa grandes volúmenes.
• R: es adecuado para visualizar, modelos lineales y gráficos reproducibles.
• SQL: permite consultar y agregar registros directamente en bases de datos.
• Power Query: repeating transformaciones de tablas cuando sus fuentes cambian con frecuencia.
• Power Pivot: ayuda cuando la etapa de calculations exceeds lo cómodo en un libro conventional.

Para inferencia, pruebas de hipótesis, regresión o diseño experimental aleatorizado, Excel puede preparar los datos, pero un paquete estadístico ofrece métodos y diagnósticos más adecuados. Un flujo abierto y reproducible, como el descrito en este artículo sobre análisis experimental con Python, resulta útil cuando el procedimiento debe documentarse y repetirse.

Qué hacer cuando los datos se desordenan o no coinciden

Detecta los problemas antes de resumir. En el libro, una fecha como 05/01/26 puede parecer fecha, pero si la columna está formateada como Texto no se ordenará cronológicamente. Del mismo modo, A, a y Grupo A serán categorías diferentes para una tabla dinámica, aunque representen la misma condición.

También debes contrastar cada importe con las unidades y el precio. Un resultado de 45,00 con 15 unidades de Producto A sí coincide; un importe de 45,00 con 15 unidades de Producto B no. El cuaderno de observaciones es la fuente para decidir qué dato corregir.

Corregir fechas que Excel no reconoce como fechas

Si las fechas están a la izquierda de la celda, probablemente son texto.

1. Selecciona las celdas de fecha.
2. Pulsa F2 para entrar en edición.
3. Vuelve a escribir cada fecha como 05/01/2026.
4. Pulsa Entrar y aplica el formato dd/mm/aaaa.
5. Selecciona la columna y usa Datos > Ordenar y filtrar > Ordenar de mayor a menor.

Unificar categorías antes de comparar resultados

Para sustituir etiquetas sin tocar cada fila, utiliza Ctrl+L o Inicio > Edición > Buscar y reemplazar. Reemplaza a por Grupo A y Grupo A por A, respetando la secuencia para no crear sustituciones circulares. Repite el proceso con Grupo B y B.

Después:

1. Actualiza la tabla dinámica con Analizar tabla dinámica > Actualizar.
2. Comprueba que las categoríasJL aparezcan una sola vez.
3. Revisa de nuevo los totales.

Lo que ya sabes hacer con datos experimentales en Excel

• Preparar una tabla donde cada fila sea una observación y cada columna, una variable.
• Revisar duplicados, faltantes, fechas, categorías y cálculos antes de resumir.
• Calcular total, promedio, máximo y segundo máximo con fórmulas en español.
• Usar SUBTOTALES (SUBTOTAL) para trabajar con las filas visibles.
• Comparar grupos y periodos con una tabla dinámica.
• Actualizar el resumen con Analizar tabla dinámica > Actualizar o Alt+F5.
• Crear un gráfico de columnas o líneas y relacionarlo con una pregunta medible.

El siguiente paso es convertir tu preparación en un flujo repetible con Power Query o llevar los datos a Python, R o SQL cuando el volumen, la automatización o el modelo estadístico excedan lo que conviene mantener en Excel.