Python en Excel (2024, canal Insider)

Python en Excel (2024, canal Insider)

Pasos iniciales y ejemplos prácticos de cómo sacarle todo el provecho a Python en Excel

Una de las novedades más importantes que ha llegado a Excel en los últimos años es la posibilidad de ejecutar Python directamente desde la hoja de cálculo. En esta sesión, disponible por ahora solo en el canal Insider y en fase Beta, repasamos qué es Python en Excel, cómo se prepara la información con la librería pandas y dos aplicaciones de ciencia de datos: clustering y árboles de decisión. La premisa es sencilla: no hace falta memorizar el código, sino entender qué hace cada paso, porque hay muchísima documentación disponible y los asistentes de IA ofrecen respuestas muy buenas para cualquiera de estas operaciones.

Qué es Python en Excel y cómo funciona

Python es un lenguaje de programación muy versátil, especialmente popular en ciencia de datos. La versión que llega a Excel está muy acotada: no incluye la parte de automatización, manejo de ficheros locales o creación de webs (para eso Excel ya tiene Visual Basic y Office Script). Lo que sí ofrece es la parte de transformación de información (ETL) y, sobre todo, análisis de datos avanzado.

La clave de su arquitectura es que Python no corre en tu ordenador, sino en un servidor Azure de Microsoft. Excel le envía los datos, el servidor hace los cálculos y devuelve el resultado. Por eso Python no puede acceder a tus archivos locales: la información debe llegarle desde rangos, celdas, tablas o conexiones de Power Query. Para enviársela se usa la función específica de Excel XL(), que también aparece automáticamente al marcar un rango. El límite actual ronda los 500.000 registros.

Trabajar la información con pandas

Para empezar se inserta una celda de Python (menú Insertar Python o escribiendo =PY y tabulador). Al activarse, la barra de fórmulas se vuelve verde. Excel precarga varias librerías: pandas, numpy, matplotlib, statsmodels y otras. pandas es la que permite trabajar con tablas, llamadas DataFrames.

Para crear un DataFrame a partir de una conexión de Power Query:

DF_ventas = pd.DataFrame(xl("Conexion"))

Se valida con Ctrl + Intro (el Intro normal baja de línea dentro de la celda). Cada resultado es un objeto Python; con la opción valor de Excel se puede esparcir su contenido por las celdas, aunque algunos objetos no tienen versión visualizable.

Operaciones básicas que recuerdan a Power Query:

  • Información general: DF_ventas.info() y DF_ventas.describe() muestran columnas, nulos, tipos, promedios, mínimos y máximos (con Mostrar diagnóstico).
  • Añadir columna de texto: DF_ventas["nueva"] = "Hola", o extraer un carácter con .str[0].
  • Añadir columna numérica: DF_ventas["edad2"] = DF_ventas["edad"] * 2.
  • Categorizar por rangos (como un BUSCARX aproximado): pd.cut() con los parámetros bins (los rangos) y labels (las categorías). Para ello conviene convertir antes los rangos y etiquetas en listas con [0].tolist().

Es importante recordar que todo el libro es un único programa: el código se lee en zigzag (de izquierda a derecha y de arriba abajo, también entre pestañas). Una variable solo existe si se ha definido antes del punto donde se usa; si no, da error. Python distingue mayúsculas y minúsculas, igual que el código M.

Dinamizar, filtrar y eliminar

  • Tablas dinámicas: pd.pivot_table() con index, columns y values (por ejemplo, promedio del puntaje de crédito). Para varios campos se pasan entre corchetes como lista.
  • Despivotar: la función melt() convierte columnas en filas, indicando las variables que se conservan y las que pasan a valores.
  • Filtrar: DF_ventas[DF_ventas["producto"] == "Producto C"]. La igualdad se escribe con doble ==. Se combinan condiciones con & (y) y | (o), cada una entre paréntesis.
  • Eliminar columna: DF_ventas.drop("cliente_id", axis=1). El axis=1 indica que se borra una columna, no una fila.

Clustering con K-Means

La primera técnica de análisis es el clustering con el algoritmo K-Means, que agrupa elementos parecidos (clientes, facturas, pedidos...) sin que tú definas previamente los grupos. El algoritmo coloca centroides aleatorios, reparte los puntos según el más cercano, recalcula el centro de cada grupo y repite hasta que las zonas se estabilizan.

Pasos en Excel (con buena práctica de listar cada paso en orden):

  1. Importar librerías: StandardScaler (igualar escalas), LabelEncoder (pasar texto a número) y KMeans.
  2. Seleccionar las variables (en el ejemplo, edad e ingresos anuales: dos variables para que sea visual).
  3. Escalar los datos con StandardScaler para que las magnitudes muy distintas (miles frente a unidades) sean comparables.
  4. Aplicar KMeans indicando el número de clusters y un random_state.
  5. Visualizar con un scatter plot coloreado por cluster.

Cambiando el número de grupos (incluso enlazándolo a una celda con xl()) se obtienen distintas segmentaciones. K-Means no explica por qué agrupa así: esa interpretación corresponde al analista.

Árboles de decisión (Machine Learning)

La última parte entra en lo predictivo con un árbol de decisión. Tras codificar las variables de texto con LabelEncoder, se separan los datos en la variable objetivo (qué producto compran) y el resto. Después se divide la muestra con train_test_split (en el ejemplo, 70% para entrenar y 30% para testear) y se entrena el clasificador.

El modelo alcanzó un 86% de acierto sobre los datos no usados en el aprendizaje. Para evaluarlo se usan dos herramientas visuales:

  • La matriz de confusión: un mapa de calor donde una diagonal marcada indica un buen modelo (lo previsto coincide con lo real).
  • La vista de árbol: muestra qué variables pesan más y cómo se ramifican las decisiones (por ejemplo, edad menor de 40,5 años como primer corte). Limitando la profundidad del árbol se obtiene una vista más simple o más detallada.

Conclusión

Python en Excel abre un enorme abanico de posibilidades para preparar datos y aplicar análisis estadístico avanzado sin salir de la hoja de cálculo. No es una herramienta para todo el mundo ni para todas las tareas (Excel y Power Query siguen siendo más cómodos en muchos casos), pero merece la pena conocerla. Como recuerda el ponente, gran parte del trabajo es copiar, pegar y, sobre todo, entender lo que se hace en cada paso.

Más contenido de Excel en InflueXcel