Análisis de datos en Excel con un proyecto práctico de Udemy

La búsqueda «udemy analisis de datos» suele referirse a cursos y ejercicios de Udemy basados en Excel. En este tutorial tomaré ese sentido: aprenderás a analizar una tabla de ventas con las herramientas del programa, sin depender de un curso concreto. Usaremos tablas dinámicas, SUBTOTALES, IMPORTARDATOSDINAMICOS y formato condicional, además de conocer el papel de Power Query.

El proyecto funciona en Excel 2007 y posteriores, con algunas funciones y comportamientos distintos según la versión. Al terminar tendrás un informe de ventas por categoría y mes, señales visuales sobre los importes y un método para mantener los resultados al llegar nuevas ventas. Los datos están comprobados en octubre de 2026.

Qué es el análisis de datos en Excel

El análisis de datos consiste en convertir una tabla de operaciones en información que permita decidir. En nuestro proyecto, una tienda registra ventas de cuatro categorías durante tres meses. Cada fila contiene una venta; cada columna, un atributo de la venta.

La hoja Ventas será la fuente. La hoja Resumenongiàrm will hold el informe. No modificaremos los registros originales para resumirlos: la tabla dinámica calculará los totales a partir del origen.

Cómo preparar los datos antes de analizarlos

La calidad del resumen depende de la estructura del origen. Cada fila debe representar una venta y cada columna un campo con un solo significado. En nuestro libro, las columnas serán Fecha, Categoría, Vendedor e Importe.

Los nombres deben ser coherentes: usaremos “Papelería” en todas las filas, no “Papeleria” unas veces y “Papelería” otras. Las fechas deben ser fechas reales, no textos, y los importes deben contener solamente números.

1. Selecciona A1:D13.
2. En Inicio > Estilos, elige un estilo de tabla que destaque los encabezados.
3. En la pestaña Datos, comprueba que los encabezados no tengan espacios al principio o al final.
4. Selecciona una celda de la columna Importe y usa el formato de moneda o número con dos decimales.

Las fórmulas auxiliares sirven para crear una columna de apoyo, por ejemplo, el mes extraído de la fecha. Se evitan cuando la estructura ya permite resumir directamente, porque cada columna adicional debe tener un propósito.

Resumir ventas con una tabla dinámica

La tabla dinámicaegencia los totales sin escribir una suma para cada categoría y mes. El origen debe tener encabezados únicos y datos coherentes.

Primero creamos una tabla dinámica desde Ventas:

En el cuadro de diálogo, la hoja de destino será Resumen y la ubicación será una celda nueva.

La tabla ofrece cuatro zonas:

• Filas: categorías que forman las filas del resumen.
• Columnas: meses que forman las columnas.
• Valores: el campo numérico que se agrega, en este caso Importe.
• Filtros: criterios para reducir temporalmente el informe.

Crear el resumen por categoría y mes

1. Arrastra Categoría a Filas.
2. Arrastra Fecha a Columnas.
3. Arrastra Importe a Valores.
4. Cambia el resumen de Suma a Suma, que ya es la operación predeterminada para importes.
5. Si aparecen meses como enero, febrero y marzo, guárdalos como etiquetas legibles en el informe.

El total general es 7.025,00. Si movemos Categoría a Columnas y Fecha a Filas, cambia la orientación, pero se mantiene el mismo total.

Actualizar el resumen cuando lleguen más ventas

La tabla dinámica no se actualiza sola cuando cambia el origen. En nuestro libro añadiremos una venta de tecnología:

1. Selecciona cualquier celda de la tabla dinámica.
2. Abre Analizar tabla dinámica > Actualizar.
3. Revisa que el nuevo importe aparezca y que el total general sea ahora 8.425,00.

También puedes pulsar Alt+F5.

Con datos que crecen dentro de una tabla

Si el origen ya es una tabla de Excel, las filas añadidas debajo entran en el rango de la tabla dinámica. Para comprobarlo:

1. Selecciona la celda directamente debajo de la última fila de TablaVentas.
2. Escribe la nueva venta.
3. Presiona Tab para convertirla en una fila de la tabla.
4. Actualiza la tabla dinámica con Alt+F5.

Así no hay que ampliar manualmente el origen.

Con datos añadidos fuera de la tabla

Una fila pegada debajo de la tabla, pero fuera de su rango, no se incorpora automáticamente. Para evitarlo, convierte el origen en una tabla desde Insertar > Tablas > Tabla, o revisa el origen de la tabla dinámica.

Si el resumen no cambia, comprueba primero que la fila nueva seESISó realmente dentro del rango y después actualiza la tabla dinámica.

Calcular totales visibles con SUBTOTALES

Una tabla dinámica resume las ventas. SUBTOTALES (SUBTOTAL) calcula un subtotal de una lista y responde a los filtros. La función 109 suma las filas visibles y también ignora las que fueron ocultadas manualmente.

En la hoja Ventas, añade en F1 el encabezado Subtotal visible y en F2 esta fórmula:

=SUBTOTALES(109;D2:D100)

Después copia F2 hasta F13.

1. Selecciona A1:D13.
2. Abre Datos > Filtrar.
3. En Categoría, desactiva Papelería.
4. Observa cómo F2:F13 cambia a 4.765,00, que es el total de las categorías visibles.

Identificar el segundo importe más alto

K.ESIMO.MAYOR (LARGE) devuelve el k-ésimo valor más alto de un conjunto. Para obtener el segundo importe más alto de D2:D13, coloca el resultado en H2:

=K.ESIMO.MAYOR(D2:D13;2)

El valor es 1.250,00. El primero es 1.100,00 y el tercero es 780,00.

Resaltar resultados importantes con formato condicional

El formato condicional (conditional formatting) aplica formato según el valor o una fórmula. En nuestro informe señalarVentas altas, detectar categorías repetidas y destacar importes que superen un objetivo.

Para ventas altas, seleccionamos D2:D13 y elegimos:

Marcar las ventas que superan el objetivo

1. Selecciona D2:D13.
2. Abre Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Mayor que.
3. Escribe 1000 en el cuadro.
4. Elige Relleno verde y Texto oscuro rojo.
5. Pulsa Aceptar.

La regla se aplica por valor, no por posición. Si más adelante cambias 1.250,00 por 950,00, el resaltado desaparecerá de esa celda.

Detectar categorías repetidas

1. Selecciona B2:B13.
2. Abre Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Valores duplicados.
3. Elige un formato de relleno y pulsa Aceptar.

Todas las categorías aparecen repetidas porque el informe contiene varias ventas de Papelería, Tecnología y Librería. Para analizar únicamente una categoría duplicada en concreto, usa una fórmula con CONTAR.SI, pero la regla de duplicados es la opción directa para este ejercicio.

Cómo leer un informe de tabla dinámica con IMPORTARDATOSDINAMICOS

IMPORTARDATOSDINAMICOS (GETPIVOTDATA) recupera un dato almacenado en una tabla dinámica. Puede devolver un total por categoría, mes u otro campo.

La sintaxis es:

=IMPORTARDATOSDINAMICOS(campo_de_datos;tabla_dinámica;[campo1;elemento1;campo2;elemento2];...)

Los pares campo; elemento deben coincidir con los nombres exactos del informe. Si una categoría se escribe “Papelería” en la fórmula y “Papeleria” en la tabla, la función no encontrará la coincidencia.

Extraer un total concreto de la tabla dinámica

En la hoja Resumen, coloca:

• H1: Categoría seleccionada
• H2: Papelería
• I1: Mes seleccionado
• I2: feb
• J1: Importe
• J2: fórmula de consulta.

=IMPORTARDATOSDINAMICOS("Importe";TablaDinámica1;"Categoría";H2;"Fecha";I2)

El resultado será 1.000,00. Si cambias H2 a Tecnología y I2 a feb, obtainá 1.250,00.

Elegir el dato que devuelve la fórmula

Si colocas el cursor dentro de la fórmula y pulsas Mayús+F3, Excel abre el cuadro de argumentos. Allí puedes seleccionar el campo de datos, la tabla dinámica y los pares de campo y elemento.

El primer argumento indica qué medida recuperar. Los pares siguientes.navigate el resumen. Si Excel devuelve #REF!, revisa el nombre de la tabla dinámica y las referencias de las celdas de selección.

Por qué el análisis cambia con datos filtrados

Filtrar no borra las ventas: solo cambia qué filas muestra la hoja. SUMA continúa sumando todas las celdas del rango, mientras SUBTOTALES puede limitar el resultado a las filas visibles. Por eso un informe filtrado puede mostrar una suma total que no coincide con lo que el usuario ve.

Usaremos estas dos fórmulas:

=SUBTOTALES(9;C2:C100)
=SUBTOTALES(109;C2:C100)

La función 9 ignora las filas ocultadas por un filtro. La 109 ignora también las ocultadas manualmente.

Verificar que las filas ocultas no están sumando

En F2, coloca =SUBTOTALES(9;C2:C100) y en G2, =SUBTOTALES(109;C2:C100). Después oculta la fila 5 mediante Inicio > Celdas > Formato > Ocultar filas.

La suma de la columna C de las filas 2, 3 y 4 es 2.260,00. Como la fila 5 está oculta manualmente, ambas fórmulas devuelven 2.260,00. Si ocultas la fila 4 mediante un filtro, la fórmula 9 puede seguir incluyendo el valor de esa fila, mientras la 109 lo excluye.

Detectar un resumen desactualizado

Las tablas dinámicas y las fórmulas tienen formas distintas de actualizarse. Para detectar un resumen atrasado:

1. Cambia el importe de D2 de 320,00 a 700,00.
2. Mira el Total general de la tabla dinámica: seguirá mostrando 7.025,00 hasta actualizar.
3. Selecciona la tabla dinámica.
4. Elige Analizar tabla dinámica > Actualizar o pulsa Alt+F5.
5. El Total general pasa a 7.405,00.

Después, F9 fuerza un recálculo de las fórmulas del libro. La tabla dinámica debe actualizarse con su propio comando.

Cuándo usar tabla dinámica, fórmulas o Power Query

La elección depende del trabajo que necesitas repetir:

Power Query está integrado desde Excel 2016 y sirve para importar, limpiar y combinar datos. Sus fórmulas usan lenguaje M y sus pasos se actualizan con Actualizar. No sustituye los cálculos celda a celda: prepara el origen.

Para el proyecto, la tabla dinámica resume, las fórmulas calculan y Power Query puede limpiar un archivo de ventas antes de conectarlo al libro.

Mantener Power Query actualizado en Excel 2010 y 2013

Si utilizas Excel 2010 o 2013, instala o habilita el complemento de Power Query desde las opciones de complementos de Excel. En Excel 2016 o posterior, la herramienta aparece integrada en Datos > Obtener y transformar datos > Obtener datos.

Al conectarlo, conserva el origen de la consulta y actualiza con Datos > Consultas y conexiones > Actualizar todo. Verifica también que los datos de destino estén dentro del rango que espera el informe.

Qué puedes analizar después de completar el proyecto

• Preparaste una fuente con encabezados únicos, categorías coherentes, fechas reales e importes numéricos.
• Resumiste las ventas por categoría y mes con una tabla dinámica.
• Actualizaste el informe después de añadir una venta y aprendiste a mantener una tabla de Excel como origen.
• Calculaste totales visibles con SUBTOTALES y comparaste las funciones 9 y 109.
• Identificaste el segundo importe más alto con K.ESIMO.MAYOR.
• Resaltaste ventas superiores a 1.000 y detectaste categorías repetidas.
• Extrajiste un total concreto con IMPORTARDATOSDINAMICOS.

Como siguiente paso, añade un segundo informe junto a Resumen: coloca SUBTOTALES en la columna Importe y crea un filtro por vendedor o categoría para comparar rápidamente los importes visibles.