Curso
Google Sheets intermedio
4 h
57.7K
A veces, la ciencia de datos no va de deep learning, big data o sistemas de recomendación. En ocasiones, sobre todo si trabajas en sectores como finanzas o en una empresa que ha dependido mucho de Excel, es muy probable que tengas que trabajar con hojas de cálculo de Excel a menudo. Me incluyo en este último caso: mi empresa dependía mucho de Excel y, por tanto, tuve que aprender a fondo muchas de sus funciones para cumplir con mis responsabilidades.
Aunque R y Python tienen ventajas claras frente a Excel en muchos aspectos, Excel sigue siendo una herramienta potente para tareas concretas como crear informes de negocio. En este tutorial me voy a centrar en una de sus funciones integradas que facilita mucho el trabajo cuando manejas varias tablas a la vez.
Me refiero a VLOOKUP. VLOOKUP te permite relacionar distintas columnas dentro de la misma hoja o entre hojas diferentes del mismo libro. En esencia, te permite buscar un valor en una columna y devolver su correspondiente en otra columna. Esta función es muy útil cuando tienes tablas grandes que no están ordenadas y necesitas relacionarlas por alguna columna (idealmente con una clave única).
Voy a ilustrar cómo funciona VLOOKUP entre columnas de una misma hoja con una tabla de ejemplo:
En la captura de pantalla tienes una expresión típica de VLOOKUP tal como la escribirías para un informe. Sus componentes son:
- Función IFERROR: Se usa para sustituir un error por un guion (-) si la clave está vacía o no aparece en la tabla. Es una buena práctica de gestión de errores que deja la hoja más limpia, pero no es necesaria para que VLOOKUP funcione.
- Argumentos de VLOOKUP:
- E2 es el valor de búsqueda (en el ejemplo usamos KOG, pero podría ser cualquier clave). Este valor debe estar en la columna más a la izquierda del rango de tabla que vas a consultar.
- B1:C18 es el rango/array de tabla donde buscar. La columna más a la izquierda tiene índice 1, como ves. Esta columna debe contener también los valores de búsqueda para que la función funcione.
- 2 es el número de índice de columna del rango seleccionado del que quieres recuperar el valor. En este ejemplo queremos la descripción, así que elegimos la columna 2 del array.
- FALSE indica que quieres una coincidencia exacta del valor de búsqueda. Aquí elegimos FALSE porque queremos que la clave KOG coincida exactamente. La otra opción es TRUE, que busca una coincidencia aproximada y es la opción por defecto si no especificas este argumento. En general, no conviene usar TRUE cuando comparas claves o cadenas porque puede dar resultados extraños.
Ahora, tras escribir esa fórmula VLOOKUP, si pulsas ENTER en Excel, obtendrás lo siguiente:
Como ves, VLOOKUP devuelve exactamente lo esperado: "King of Gondor", que es la descripción asociada a la clave KOG. Esto sirve para cualquier clave que introduzcas ahora en la celda E2. Por ejemplo, veamos qué pasa si ponemos la clave KIN.
¡El Rey en el Norte! Exacto: si introducimos la clave KIN, eso es lo que esperamos obtener, ya que es la descripción de nuestro querido Jon (Snow).
Sin embargo, VLOOKUP sirve para mucho más que recuperar un único valor a partir de una clave. Imagina que dividimos esa tabla en dos, cada una en una hoja distinta del mismo libro.
Unir tablas
 |
 |
Ahora los datos están en dos hojas (S1 y S2) y las claves de la tabla de la segunda hoja (S2) están en otro orden. Por suerte, la columna de la clave es común en ambas hojas, así que podemos unir los datos en S1 usando VLOOKUP (piénsalo como un left join de una sola columna). La sintaxis sería la siguiente:
Esta vez, el primer argumento, el valor de búsqueda, pasa a ser B2 porque queremos hacer coincidir la clave B2 con la tabla de S2. El rango de tabla ahora es toda la tabla de S2, con la primera columna conteniendo las claves comunes. Verás que el rango de tabla lleva signos de dólar. Esto se hace pulsando F4 tras seleccionar el rango y sirve para fijarlo, de forma que, cuando arrastres la fórmula hacia abajo en S1, VLOOKUP siga consultando el rango original sin desplazarse. El número de índice de columna sigue siendo 2 porque queremos la descripción, que es la segunda columna en S2. Y el último argumento es FALSE, ya que queremos una coincidencia exacta de las claves.
El último paso es simplemente arrastrar la fórmula hasta la última fila de la tabla. La tabla resultante en S1 queda así:
¡Voilà! Así de fácil, hemos vuelto a juntar los datos con VLOOKUP. Unir tablas de esta forma es muy habitual cuando trabajas en un entorno de negocio y SQL, R o Python no son las herramientas a las que están acostumbrados los directivos. Por ejemplo, puedes tener dos archivos con detalles distintos sobre oportunidades de venta cualificadas que se relacionan mediante un ID único de oportunidad para un informe mensual de ventas en Excel (el caso más típico en mi experiencia).
Usar coincidencias aproximadas
Hasta ahora habrás visto que no hemos usado TRUE como último argumento (la coincidencia aproximada) en ninguna de las fórmulas. Esto se debe a que nos hemos centrado en igualar claves únicas de forma exacta. No obstante, puedes usar TRUE con seguridad en VLOOKUP cuando trabajas con datos numéricos.
Por ejemplo, imaginemos que vendemos pescado y ofrecemos los siguientes descuentos cuando los clientes compran ciertas cantidades (si alguien pregunta por qué pescado, es fácil: ¡el pescado mola!).
La función VLOOKUP en la celda F2 esta vez es:
Una vez tengas la fórmula, el descuento aplicado será el más cercano por debajo de los umbrales de la tabla. Por ejemplo, si compramos 25 piezas, el descuento será del 5%; si compramos 70, será del 10%; y si compramos 120, será del 15%. Aquí tienes los ejemplos:
 |
 |
 |
Introducción a INDEX-MATCH
VLOOKUP es una función potente para relacionar columnas, pero existe una alternativa: la combinación INDEX-MATCH. A diferencia de VLOOKUP, que es una sola función, INDEX-MATCH combina dos y tiene varias ventajas frente a VLOOKUP, aunque sea un poco más compleja de escribir.
En este tutorial veremos un ejemplo sencillo en el que usamos INDEX-MATCH para unir dos tablas basándonos en una clave común, igual que hicimos arriba con VLOOKUP, y explicaremos su sintaxis (ver abajo).
Los componentes de INDEX-MATCH son los siguientes:
- Argumentos de INDEX:
- M2:M18: Es el rango de la columna cuyos valores quieres recuperar; en este caso, la columna de descripción, así que seleccionamos ese rango. Fíjate en que hemos usado F4 tras seleccionar el rango para fijarlo y que no se mueva al arrastrar la fórmula.
- Argumentos de MATCH:
- B2: Es la clave que queremos buscar. Equivale al valor de búsqueda en VLOOKUP.
- L2:L18: Es el rango de la columna que contiene las claves donde debe localizarse el valor de búsqueda. Aquí también hemos usado F4.
- 0: Equivale a FALSE en VLOOKUP. Indica coincidencia exacta.
Solo con ver la sintaxis ya se aprecia una de las grandes ventajas de INDEX-MATCH frente a VLOOKUP: la inmunidad a cambios de columnas. Si ahora decidiéramos añadir una columna entre Key y Description en la tabla de la derecha, no pasaría nada: la fórmula seguiría referenciando la columna Description. Puedes verlo aquí:
Con VLOOKUP esto no es posible sin editar la fórmula, ya que utiliza una referencia de columna estática. Es decir, en el ejemplo anterior, al principio habrías puesto 2 como número de índice de columna para recuperar Description. Sin embargo, tras añadir la columna Fish, VLOOKUP devolvería ahora el valor de Fish porque pasa a ser la columna 2 del rango de tabla. Tendrías que entrar manualmente en la fórmula y cambiar el número de índice de columna a 3 para que volviera a funcionar bien.
Visto esto, unir la descripción con el nombre en la tabla de la izquierda usando INDEX-MATCH se hace igual que con VLOOKUP. Simplemente arrastramos la fórmula hacia abajo, como se muestra:
Para unir tablas entre distintas hojas del mismo libro, el procedimiento es el mismo. La única diferencia es que tendrás que seleccionar el rango de la columna con las claves comunes y el rango de la columna cuyos valores quieres recuperar en la segunda hoja.
Conclusión
Espero que te haya gustado el tutorial. Estas funciones, VLOOKUP e INDEX-MATCH, son prácticamente imprescindibles para cualquiera que trabaje con Excel en un entorno empresarial. Te animo a profundizar en INDEX-MATCH más allá de lo visto aquí: tiene posibilidades muy interesantes, como coincidencias que distinguen mayúsculas de minúsculas (sí, por defecto INDEX-MATCH y VLOOKUP no son sensibles a mayúsculas) o coincidencias basadas en múltiples columnas. Sigue aprendiendo; ¡el límite lo pones tú!
Si quieres aprender más sobre VLOOKUP, haz el curso Data Analysis with Spreadsheets de DataCamp.
