Programación para analítica de datos en Excel: automatiza tu trabajo

Programación para analítica de datos significa usar herramientas de Excel para limpiar, transformar, resumir y repetir procesos sin rehacer manualmente cada cálculo. En este tutorial trabajarás con un libro de ventas:power query leaveará los datos preparados, VBA generará un informe ordenado y Python exportará un resumen por categoría.

La ruta está pensada para Excel 2016 y posteriores, donde Power Query está integrado. VBA funciona en Excel de escritorio; Python necesita instalarse aparte y no es una función de Excel. Excel para la web no ejecuta macros VBA.

Qué es la programación para analítica de datos

Imagina que cada semana recibes una hoja con fechas, categorías, clientes e importes. El trabajo no consiste solo en sumar: también hay que corregir fechas, quitar registros repetidos, comprobar campos vacíos y preparar un informe para compartirlo.

La programación para analítica de datos automatiza esas tareas. No reemplaza al criterio del analista: evita repetir los mismos clics y permite concentrarse en interpretar resultados. Puede resolverse con herramientas dentro de Excel, como Power Query y VBA, o con un lenguaje externo como Python.

En este libro, la hoja Ventas será la fuente. La hoja Limpio mostrará qué registros conserva el proceso y la hoja Resultado recibirá el informe final.

VBA, Python y Power Query: qué resuelve cada uno

Las tres herramientas se combinan, pero no tienen el mismo alcance.

Power Query es útil cuando la fuente tiene formato de tabla y debes quitar duplicados, cambiar tipos o combinar información. VBA sirve para ordenar, dar formato y guardar copias desde Excel. Python puede leer el libro, agrupar información y escribir otro archivo sin abrir Excel.

Power Query transforma tablas y graba cada paso

Power Query no es una función de celda. Carga una tabla, aplica una secuencia de transformaciones y conserva esa secuencia para poder repetirla.

Desde Excel 2016 está integrado en Obtener y transformar datos. En Excel 2010 y 2013 se instala como complemento. Su lenguaje M pertenece a Power Query y no es una fórmula de Excel.

La ayuda oficial de Power Query explica sus pasos de transformación.

VBA automatiza acciones dentro del libro

VBA permite que Excel ordene una tabla, aplique formato y guarde una copia sin que tengas que ejecutar cada acción manualmente. Un libro con macros se guarda con la extensión .xlsm.

El siguiente ejemplo usa un libro que ya contiene una tabla llamada Ventas. Todavía no crea la tabla: esa parte se hará en Excel.

Sub MostrarMensaje()
    MsgBox "La macro se ejecutará sobre la tabla Ventas."
End Sub

La guía oficial sobre VBA en Excel explica cómo grabar y editar macros.

Python procesa datos fuera de Excel

Python es útil cuando el análisis continúa fuera del libro: por ejemplo, si después de limpiar ventas se deben generar varios archivos o combinar información de otras fuentes.

El siguiente código solo muestra el principio: lee Book.xlsx, imprime las filas y todavía no modifica el archivo.

import pandas as pd

datos = pd.read_excel("Book.xlsx", sheet_name="Ventas")
print(datos)

Se necesita instalar Python, pandas y openpyxl. La guía oficial de pandas para leer archivos de Excel explica las opciones de lectura y escritura.

Prepara los datos antes de automatizarlos

Una automatización fiable empieza con una fuente predecible. En la hoja Ventas, convierte los datos en una tabla y comprueba que cada columna contiene un tipo coherente.

No conviene eliminar valores a ciegas: primero revisa qué falta, qué está repetido y qué categorías existen. Después utiliza fórmulas auxiliares para detectar problemas antes de importar la tabla en Power Query.

Convierte tus ventas en una tabla de Excel

1. Abre el libro y selecciona A1:E7.
2. En la cinta, entra en:

1. Confirma que la opción La tabla tiene encabezados está marcada.
2. Escribe Ventas en el cuadro Nombre de la tabla.
3. Pulsa Aceptar.

La tablaimely cambia de aspecto, aparecen filtros y las referencias pueden crecer cuando añades filas.

Normaliza fechas, importes y categorías

En G1, escribe Revisión. En G2, usa una fórmula que marque las filas sin importe:

=SI(D2="";"Falta importe";"Correcto")

Copia G2 hasta G7 arrastrando el controlador de relleno.

Para comprobar que D2 es un número, escribe en H1 Tipo importe y en H2:

=SI(ESNUMERO(D2);"Número";"No número")

Copia H2 hasta H7. La hoja debe mostrar seis resultados y una advertencia en la fila 6.

Con SUMAR.SI.CONJUNTO resume las ventas por categoría

Para calcular el importe total de Papelería, escribe Papelería en G10 y la fórmula en H10:

=SUMAR.SI.CONJUNTO($D$2:$D$7;$B$2:$B$7;G10)

El rango absoluto mantiene las referencias fijas cuando copies la fórmula hacia abajo.

Automatiza una limpieza con Power Query

La tabla Ventas será el origen de la limpieza. Power Query quita la fila repetida y conserva los importes que sí están completos. El resultado se cargará en una hoja llamada Limpio.

Los pasos se guardan dentro del libro. Si después cambian los datos, puedes repetirlos con Actualizar todo.

Quita duplicados y valores vacíos

1. Selecciona una celda dentro de la tabla Ventas.
2. Abre:

1. Elige De tabla o rango.
2. En el editor de Power Query, abre Transformar datos.
3. Selecciona la columna Importe.
4. Elige Transformar > Eliminar filas vacías.
5. Selecciona todas las columnas y elige Transformar > Eliminar duplicados.
6. Cambia la consulta a Solo crear cargas.
7. En Cargar en, elige Tabla y selecciona una hoja nueva.
8. Escribe Limpio como nombre de la hoja y pulsa Cerrar y cargar.

La consulta conserva una sola fila para Ana y elimina la fila de Diego porque su importe está vacío.

Actualiza el análisis después de añadir ventas

Ahora añade una venta nueva a la tabla Ventas: 07/01/2026, categoría Tecnología, cliente Fede, importe 275,00, vendedor Sara.

Después:

1. Selecciona cualquier celda de la hoja Limpio.
2. Pulsa Datos > Actualizar todo.
3. Comprueba que la consulta incluya la venta de Fede.

Automatiza un informe repetido con VBA

VBA puede crear una hoja Resultado, ordenar las ventas por importe de mayor a menor y aplicar formato. La macro utiliza la tabla Ventas, por eso no necesita que escribas las referencias celda por celda.

El libro debe guardarse como .xlsm antes de ejecutar una macro que contenga código.

Graba la primera macro desde la cinta

La pestaña Programador puede estar oculta. Para mostrarla:

1. Pulsa Archivo > Opciones > Personalizar cinta de opciones.
2. En la lista de la derecha, busca Programador.
3. Activa la casilla.
4. Pulsa Aceptar.

Después, para grabar una prueba:

1. Selecciona la hoja Ventas.
2. En Programador > Macros, pulsa Grabar macro.
3. Escribe OrdenarVentas.
4. Acepta.
5. Selecciona Datos > Ordenar.
6. Ordena por Importe, de mayor a menor.
7. Pulsa Detener grabación.

Completa el código para repetir el informe

Abre el editor con Alt+F11 y sustituye el contenido del módulo por este código:

Option Explicit

Sub CrearInformeVentas()
    Dim origen As ListObject
    Dim destino As Worksheet

    Set origen = ThisWorkbook.Worksheets("Ventas").ListObjects("Ventas")

    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("Resultado").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    Set destino = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    destino.Name = "Resultado"

    origen.Range.Copy
    destino.Range("A1").PasteSpecial xlPasteValues
    destino.Range("A1:E1").Font.Bold = True
    destino.Rows.AutoFit

    destino.Sort.SortFields.Clear
    destino.Sort.SortFields.Add Key:=destino.Range("D2:D" & destino.Cells(destino.Rows.Count, 1).End(xlUp).Row), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    destino.Sort.SetRange destino.Range("A1:E" & destino.Cells(destino.Rows.Count, 1).End(xlUp).Row)
    destino.Sort.Header = xlYes
    destino.Sort.Apply

    ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\Analisis_ventas.xlsm"
    MsgBox "Informe creado correctamente."
End Sub

Para ejecutarla, vuelve a Excel con Alt+F11 y pulsa F5. El archivo se guardará en la misma carpeta del libro.

Exporta un análisis de Excel con Python

Python permite cerrar el flujo fuera de Excel: lee el libro preparado, agrupa los importes por categoría y escribe un archivo final. Así puedes conservar un informe independiente sin depender de una sesión abierta de Excel.

En los ejemplos, Book.xlsx representa el libro que contiene la hoja Ventas. El archivo final será Analisis.xlsx.

Carga y revisa el libro desde Python

Instala las bibliotecas con:

python -m pip install pandas openpyxl

Después ejecuta:

import pandas as pd

datos = pd.read_excel("Book.xlsx", sheet_name="Ventas")
datos["Fecha"] = pd.to_datetime(datos["Fecha"], errors="coerce")
datos["Importe"] = pd.to_numeric(datos["Importe"], errors="coerce")
datos = datos[datos["Importe"].notna()].drop_duplicates()

print(datos.to_string(index=False))

La salida debe mostrar cinco registros válidos: Ana una vez, Beto, Carla, Eva y Fede.

 Fecha Categoría Cliente Importe Vendedor
2026-01-03 Papelería Ana 120.0 Luis
2026-01-04 Tecnología Beto 350.0 Sara
2026-01-05 Papelería Carla 80.0 Luis
2026-01-06 Tecnología Eva 210.0 Sara
2026-01-07 Tecnología Fede 275.0 Sara

Guarda los resultados en un archivo de Excel

Este código agrupa por categoría y crea una hoja Resumen:

import pandas as pd

datos = pd.read_excel("Book.xlsx", sheet_name="Ventas")
datos["Fecha"] = pd.to_datetime(datos["Fecha"], errors="coerce")
datos["Importe"] = pd.to_numeric(datos["Importe"], errors="coerce")
datos = datos[datos["Importe"].notna()].drop_duplicates()

resumen = (
    datos.groupby("Categoría", as_index=False)["Importe"]
    .sum()
    .sort_values("Importe", ascending=False)
)

with pd.ExcelWriter("Analisis.xlsx", engine="openpyxl") as archivo:
    resumen.to_excel(archivo, sheet_name="Resumen", index=False)
    datos.to_excel(archivo, sheet_name="Ventas_limpias", index=False)

print(resumen.to_string(index=False))

Categoría Importe
Tecnología 835.0
Papelería 200.0

El total de Tecnología es 350 + 210 + 275 = 835. Papelería suma 120 + 80 = 200.

Comprueba que los automatismos no cambian los resultados

Un resultado automático no es correcto solo porque termina. Compara siempre la fuente, la salida y una fórmula de control. Esta revisión debe formar parte del proceso.

En el libro, SUMAR.SI.CONJUNTO calcula un total con la hoja actual; Power Query conserva los datos transformados; VBA copia valores; Python agrupa registros. Las cuatro rutas deberían llegar a la misma cifra cuando recebem la misma fuente limpia.

Controla que los datos de origen no estén duplicados

En J1 de Ventas, escribe Conteo de clientes únicos. En J2 escribe:

=CONTAR.SI.UNICOS(C2:C7)

El resultado es 4: Ana, Beto, Carla y Eva. La revisión siguiente debe hacerse después de limpiar duplicados; por eso la salida final conserva cinco ventas y cuatro clientes.

Revisa errores y resultados vacíos

Para localizar fórmulas con errores en un rango, selecciona una celda auxiliar, escribe ESERROR y ponle la referencia de la celda que quieres comprobar.

Por ejemplo, en L2:

=SI(ESERROR(H10);"Revisar fórmula";"Total correcto")

Si H10 devuelve un valor, L2 mostrará Total correcto. Si la fórmula no existe o está mal escrita, la revisiónossen will flag it para inspection. En una hoja grande, usa las herramientas de búsqueda para localizar errores y revisa también que no haya filas vacías inesperadas.

Qué programa cuando Excel llega a su límite

La herramienta depende del volumen de datos y de la tarea. Para una hoja pequeña, las fórmulas y una tabla dinámica suelen bastar. Power Query evita repetir limpiezas; VBA automatiza acciones de Excel; Python es más adecuado cuando el proceso continúa fuera del archivo.

Elige la herramienta según el tamaño y la tarea

• Usa fórmulas o una tabla dinámica para resumir una hoja pequeña.
• Usa Power Query cuando la limpieza requiera quitar duplicados, normalizar columnas o combinar archivos.
• Usa VBA cuando debas repetir una ordenación, un formato o un guardado dentro de Excel.
• Usa Python cuando haya procesos externos, archivos grandes, varias salidas o conexión con otras fuentes.

La elección no depende de aprender una sola herramienta. Consiste en elegir la que reduce el trabajo repetitivo sin ocultar el cálculo.

Revisa las versiones de las herramientas

Power Query está integrado desde Excel 2016; en 2010 y 2013 se instala como complemento. VBA requiere una versión de escritorio compatible y un archivo .xlsm. Python funciona fuera de Excel, pero necesita sus bibliotecas instaladas.

Las funciones nuevas también dependen de la versión. FILTRAR está disponible desde Excel 2021, mientras que AGRUPARPOR solo está en Microsoft 365 y Excel para la web. No confundas estas funciones de hoja con Power Query o con VBA.

Lo que ya sabes hacer al automatizar tu análisis

• Preparar una hoja de ventas como tabla de Excel.
• Detectar importes vacíos y comprobar que sean números.
• Resumir importes por categoría con SUMAR.SI.CONJUNTO.
• Limpiar duplicados y vacíos con Power Query.
• Actualizar el análisis después de añadir una venta.
• Grabar y completar una macro VBA para ordenar y guardar un informe.
• Leer, limpiar y agrupar datos con Python.
• Exportar los resultados a Analisis.xlsx.
• Comparar fórmulas, Power Query, VBA y Python antes de confiar en el resultado.

El siguiente paso es elegir una tarea repetida de tu propio libro y automatizar primero la limpieza, después el informe y, al final, la exportación.