Power Query es un complemento de Microsoft Excel que inicialmente fué creado para importar datos, pero ha crecido hasta ofrecer algo parecido a una base de datos dentro del propio Excel, que incluso cuenta con su propio lenguaje de consultas.
En este caso muestro usando un Microsoft Excel 2016 una de las tareas más sencillas que se pueden hacer con Power Query, como es el cruce de dos listados.
Os dejo el paso a paso completo en mi canal de Youtube en el vídeo https://youtu.be/jSgcCNf9xRE
El primer paso, es convertir ambos listados en tablas, ya sea desde al menú correspondiente o con la combinación de teclas CTRL + T (esta combinación selecciona por defecto todo el rango continuo de celdas)
Luego, dependiendo de la versión de Excel, desde la pestaña Datos o desde la pestaña PowerQuery, seleccionamos la opción "desde tabla".
Esto abre la ventana de power query.
Solo tenemos que guardar e indicar crear solo conexión.
Repetimos este paso con la segunda tabla.
Nuevamente entramos a Power Query, indicando que deseamos combinar consultas.
Seleccionamos las dos tablas y el único campo de cada una de ellas y cerramos.
Luego solo queda indicar a Power query donde queremos que se muestre el resultado.
Este método es incluso más rápido que el tercero que mostraba la próxima semana usando una búsqueda binaria para resultados exactos mezclando BUSCARV y SI.
Como ventaja sobre los métodos que mostré la semana pasada, recordar que no necesita que las tablas estén ordenadas.
Uno de los usos más habituales de Microsoft Excel es cruzar listados para saber si los datos de un listado existen en otro.
El enfoque más habitual es usar la función BUSCARV indicando que se desea devolver la columna 1, de esta forma si el dato existe en la otra lista, se devuelve el propio dato, y en caso contrario un error #N/A.
Si tenemos el listado en que buscar en la columna A y el de los datos a comprobar en la columna C la fórmula sería algo así.
=BUSCARV(C1;A:A;1;FALSO)
En este ejemplo asumimos que la lista en la que vamos a buscar los datos no están ordenados.
El último parámetro de la función BUSCARV se denomina ordenado, y realmente sirve para indicar el tipo de búsqueda a realizar.
Indicando que este último parámetro es FALSO le decimos a Excel que no podemos asegurar que la lista en la que buscamos está ordenada, pero también le indicamos que queremos realizar una búsqueda exacta. De forma que si el dato no se encuentra se devuelve un error #N/A.
Para realizar esto Microsoft Excel necesita buscar por toda la lista hasta encontrar el dato o llegar al final de la lista. Esto se denomina búsqueda secuencial.
Esto hace que este tipo de búsquedas sean muy lentas.
Imaginemos dos listados de 20 datos, si ambos listados contienen los mismos datos habrá una búsqueda que encuentre un resultado en cada posición, uno en la primera, otro en la segunda, otro en la tercera posición y así sucesivamente.
Eso implica que de media por cada dato se hacen 10 búsquedas, lo que representa un total de 200 búsquedas.
Si en el listado de datos a buscar hubiese datos que no apareciesen en el listado en el que se busca eso implicaría tantas búsquedas adicionales como datos en el listado multiplicado por el tamaño del listado en el que buscamos.
Si en el listado anterior hubiese cinco datos más, que no apareciesen en el listado de búsqueda, implicaría 100 búsquedas adicionales.
Esta es la razón por la que BUSCARV se puede volver tan lento al aumentar el tamaño de los listados.
En este vídeo muestro como un cruce de dos listados de 10.000 registros mediante una búsqueda secuencial con BUSCARV tarda 9 segundos.
Como alternativa se puede usar COINCIDIR que es un poco más rápido.
COINCIDIR devuelve la posición del dato buscado en la lista.
En el tercer parámetro le indicamos si queremos que devuelva el valor exacto, o si en caso de no existir devuelve el valor menor más cercano o el mayor más cercano.
Si deseamos saber si el valor existe, seleccionaremos el valor exacto, se representa con un cero.
=COINCIDIR(C1;A:A;0)
Con esta configuración si aparece el dato, devuelve un número que representa la fila en que se encuentra, si no aparece el dato devuelve un error #N/A.
Al devolver solo la posición del dato y no el propio dato COINCIDIR es un poco más rápido que BUSCARV.
En este vídeo muestro como un cruce de dos listados de 10.000 registros mediante una búsqueda secuencial con COINCIDIR tarda 6 segundos.
Como alternativa se puede realizar una búsqueda binaria.
Este tipo de búsquedas, exige que el listado en el que se va a buscar esté ordenado, y se puede aplicar cuando es posible ordenar el listado en el que se va a buscar.
y comienza por el centro del listado para descartar la mitad.
Supongamos que tenemos un listado de los 20 primeros números del 1 al 20 ordenados de forma ascendente.
Buscamos el número 20.
En el primer paso de la búsqueda se compara con el valor central de la lista, el número 10.
La primera comparación, será entre el 20 y el 10, sabemos que todos los valores de la primera parte del listado serán menores que el 10, por lo que podemos descartarlos.
La segunda búsqueda será en la mitad superior del listado.
La segunda comparación es con el 15, la siguiente con el 17, y la siguiente con el 19 y finalmente con el 20.
Esto implica que hemos localizado el último dato de la lista en solo cinco pasos, en lugar de los veinte necesarios en una búsqueda secuencial.
Podemos hacer una búsqueda binaria usando BUSCARV indicando VERDADERO en el último parámetro.
Existe un truco para simular la búsqueda exacta usando BUSCARV y la función SI.
El truco consiste en realizar una búsqueda secuencial devolviendo el valor encontrado en la primera columna y compararlo con el valor a buscar.
Si el valor devuelto es igual al buscado es que el dato existe, en caso contrario indicaremos que el dato no existe en el listado.
De esta forma reduciremos drásticamente las comparaciones necesarias para hacer el cruce de datos acelerando las búsquedas considerablemente.
En este vídeo muestro como un cruce de dos listados de 10.000 registros mediante una búsqueda binaria con BUSCARV es casi inmediata, en contra de los 9 segundos que llevaba la búsqueda secuencial.
Ignorando el coste que para una empresa pueda representar, el dejar de usar licencias pagadas de por vida y sustituirlas por licencias de pago por uso. Lo cual ya será un impedimento para la adopción de Buscarv.
Otra gran dificultad con la que se encontrará es la propia complejidad de la función.
Buscarv tiene 4 parámetros y dos formas de funcionamiento, una búsqueda exacta en un listado que puede ser desordenado (la más comúnmente usada) y una búsqueda aproximada (casi nunca usada) que exige un listado ordenado.
La gran mayoría de los usuarios no entiende que la fórmula pueda devolver el resultado correspondiente a un valor distinto al buscado.
Buscarx tiene dos parámetros más, y varias formas de funcionamiento. Devolver el menor o el mayor,basumir que la lista está ordenada de menos a mayor o viceversa, etc.
Mi experiencia como formador, es que la mayoría de la gente, no apreciará ninguna ventaja, y si verá un incremento en la dificultad de uso.
Esta posiblemente sea la mayor traba a su adopción.
Sólo hay que recordar el revuelo que se produjo con el cambio de nombre que experimentó la función en el Office 2010. Cuando fué renombrada como CONSULTAV, para recuperar nuevamente su nombre en el service pack uno de office 2010.
Mucha gente comienza a hablar de la nueva función de Excel buscarx como un sustituto de buscarv.
Mi opinión es justo la contraria.
Buscarv está para quedarse.
Creo que sólo una minoría de usuarios medio avanzados los que comenzarán a usarla.
Cierto es que la función buscarx es más versátil que buscarv, pero tiene mucho en contra para conseguir sustituir a buscarv.
La mayor dificultad está en la no retrocompatibilidad. Buscarx solo estará disponible para versiones de Microsoft Excel 2019 y superiores (Office 365 incluido).
Esto hace que si se crea una hoja de cálculo con la función Buscarx, solo se pueda compartir con usuarios con esas versiones de Microsoft Excel. Cualquiera que tenga una versión anterior no podrá usar la hoja de cálculo.
La solución a este problema sería que todos los equipos de la empresa se actializasen.
Sin embargo, aunque eso fuese posible, seguiría existiendo el problema de compartir esa hoja de cálculo con usuarios de fuera de la organización.
Microsoft Excel es una gran herramienta, pero a pesar de todo, puede jugarnos malas pasadas.
Uno de los formatos de datos que más habitualmente se usan para importar datos a Excel desde Bases de Datos o programas ERP de gestión, es el formato csv.
Este formato usa un carácter como separador, para indicar donde se separan los campos.
Por defecto este separador, en la versión española del programa es el punto y coma.
Esto hace que si el formato csv está vinculado a Excel, y se abre el fichero csv con doble click; Microsoft Excel asumirá que los campos están separados por punto y coma.
Esto hace que si el separador que estamos usando es distinto, los campos se separen incorrectamente.
No suele ser un problema en listados pequeños en los que esto se detecta visualmente, pero en un listado de mil líneas o más puede pasar desapercibido y provocar pérdida de datos.
En el ejemplo tenemos un listado de libros, separado por Barras verticales, también conocidas como pipes.
Es muy usual indicar los nombres de los autores, con apellidos coma nombre, y si se trata de varios autores, separar estos por punto y coma.
En este caso si se abre el fichero directamente los datos de los libros que tengan más de un autor, serán divididos por Excel en varias columnas.
Aunque en el ejemplo se aprecia el problema en las primeras líneas, existe la posibilidad de que la división se produzca en filas que no se aprecian en pantalla, por ejemplo la fila 300, pudiendo provocar pérdida de datos.
Una solución sería abrir un fichero de Excel vacío e importar desde allí el fichero.
Otra solución es la que os muestro, símplemente renombrando el fichero, cambiando la extensión por txt. De esta forma Microsoft Excel siempre importará todos los datos en la misma columna,
Para exportar una hoja de cálculo a formato CSV en Microsoft Excel, solo es necesario Guardar el fichero como csv.
Sin embargo no existe ninguna posibilidad de elegir las opciones de exportación.
Es importante entender que el separador de campos usado, es el separador de listas configurado en el sistema operativo.
En el caso de Microsoft Windows esto se configura en el apartado configuración regional del Panel de Control.
NOTA:
A la hora de exportar a csv, en mi caso prefiero utilizar Libreoffice Calc, esta hoja de cálculo permite seleccionar cualquier separador a la hora de guardar el fichero en formato CSV.
