Todo coacher que se precie nos conmina a "pensar fuera de la caja".Cuando de Excel se trata hay pensar fuera y dentro de la caja al mismo tiempo. O mejor dicho, en Excel pensar "fuera de la caja" es también ver qué hay "dentro de la caja", la caja de herramientas de Excel que se va enriqueciendo de versión en versión.
Pensar "fuera de la caja" quiere decir romper nuestros hábitos de pensamiento, recurrir siempre a lo que sabemos y no preguntarnos si habrá alguna otra forma de resolver el problema. Y aquí entra la cuestión de ver que hay dentro de la caja de herramientas de Excel. Power Query y PowerPivot son las herramientas más notables que Microsoft ha incorporado en los últimos años.
BUSCARV (VLOOKUP en inglés) es probablemente una de las funciones más usadas en Excel; en particular por usuarios de nivel básico o intermedio. Sin embargo, BUSCARV tiene varias limitaciones:
- la búsqueda se realiza de izquierda a derecha, lo que nos en muchos casos a reorganizar el orden de las columnas en nuestra tabla de búsqueda o a utilizar una combinación de INDICE y COINCIDIR;
- la búsqueda debe realizar en base a un único parámetro o utilizar alguna de las técnicas que muestro en el post "BUSCARV con dos parámetros" (si la dan un vistazo a la complejidad de las fórmulas, traten de imaginar una búsqueda en base a tres o más parámetros);
- si hay valores repetidos en la columna de búsqueda, BUSCARV siempre dará el primer valor encontrado ignorando los restantes.
Todas estas limitaciones pueden ser superadas con facilidad usando Power Query en lugar de BUSCARV.
Una pequeña introducción al uso de Power Query para aquellos lectores que aún no estén familiarizados con esta herramienta.(quienes esté interesados en ampliar conocimientos recomiendo
este curso).
Para usar Power Query, incorporado orgánicamente a Excel desde la versión 2013 o como complemento en Excel 2010, creamos una conexión a la fuente de datos. Estos datos pueden encontrarse en el mismo cuaderno como tabla o en cualquier fuente remota (cuaderno Excel, Access, página WEB, archivo de texto, etc.). Una vez creada la conexión podemos volcar los datos en una hoja de Excel o dejarlos como "solo conexión". Esta última opción nos permite superar la limitación de 1.48 millón filas por hoja.
Vamos ahora a ver el primer caso que menciono más arriba usando tablas extraídas de la base de datos Northwind. Los dos casos restantes los veremos en los próximos posts.
Aclaración: en un caso real sólo crearíamos conexiones a la base de datos sin necesidad de cargar los datos en hojas de Excel. A los efectos del ejemplo, vamos a suponer que los datos están en tablas en hojas de Excel.
Caso 1 - Búsqueda de derecha a izquierda.
Supongamos esta tabla de productos. El identificador de categoría (Category ID) está en la columna C.
En esta tabla de categorías el identficador está en la columna B (la segunda de la tabla) y la descripción en la columna A.
Queremos agregar la descripción de la categoría a la tabla de productos. Para hacerlo con BUSCARV tendríamos que reordenar la tabla de categorías o usar una combinación de INDICE y COINCIDIR.
Con Power Query hacemos lo siguiente:
a. Creamos una conexión a la tabla de productos
En la ventana del editor de Power Query seleccionamos la opción "Guardar como..." y "Sólo conexión"
b. Hacemos lo mismo con la tabla de categorías. En el panel de conexiones veremos ambas conexiones que acabamos de crear
c. Usamos la opción Datos-Obtener Datos-Combinar consultas
En el cuadro que se abre seleccionamos las consultas a combinar (Products y Categories), señalamos las columnas coincidentes de ambas tablas (CategoryID) y seleccionamos el tipo de combinación a "Extrema izquierda" (sin connotaciones políticas... esto quiere decir, traer todos los registros de la primer tablas y sólo los coincidentes de la segunda).
Apretamos "Aceptar" y veremos que se crea una nueva consulta con la tabla Productos a los que se le agregado una nueva columna, "Categories". En realidad esta columna contiene toda la tabla Categories y la doble flecha en el encabezamiento nos permite expandirla y elegir la o las columnas de Categories que queremos traer
Al expandir la columna, como dijimos, vemos todas la columnas de la tabla. En nuestro caso sólo elegiremos "Category Name"
Apretamos "Aceptar" y aquí tenemos el resultado
Todo los que nos queda por hacer es descargar esta nueva consulta a una hoja del cuaderno
Para quien no esté familiarizado con Power Query este procedimiento puede parecer complicado. ¿Qué más simple que pegar una fórmula con BUSCARV?. Sin embargo, el uso de Power Query tiene varias ventajas:
- no hay necesidad de hacer cambios en la tabla de búsqueda;
- en nuestro ejemplo hay sólo 77 filas. En casos con miles o decenas de miles de filas, el uso de BUSCARV hace que el cuaderno responda lentamente con cada cambio que conlleve recalcularlo. Por ejemplo, en una hoja con 100 mil filas (caso real de un colega), cada aplicación de Autofiltro puede llevar varios minutos.
En el
próximo post veremos los dos restantes casos. usar BUSCARV con más de un criterio de búsqueda y BUSCARV con valores repetidos en la tabla de búsqueda.
