5 formas de fusionar hojas de cálculo de Google, agregar columnas con datos relacionados e insertar filas que no coinciden
¿Sabía que cuando fusiona 2 Hojas de cálculo de Google, no solo puede actualizar registros en una sola columna, sino también extraer columnas relacionadas completas e incluso filas que no coinciden? Hoy le mostraré cómo hacerlo con VLOOKUP, INDEX/MATCH, QUERY y el complemento Merge Sheets.
La última vez que hablamos sobre la fusión de 2 Hojas de cálculo de Google, compartimos formas de hacer coincidir y actualizar los datos. Esta vez, seguiremos actualizando las celdas, pero también extraeremos otras columnas y filas relacionadas que no coincidan.
Aquí está mi tabla de búsqueda. Tomaré todos los datos necesarios de él hoy:
Esta vez se ha vuelto más grande: tiene dos columnas adicionales con los nombres de los proveedores y sus calificaciones. Actualizaré la columna Stock con esta información en otra tabla y también buscaré proveedores. Bueno, tal vez las calificaciones también 🙂
Como de costumbre, usaré algunas funciones y un complemento especial para el trabajo.
Combine Google Sheets y agregue columnas relacionadas usando VSEARCH
¿Recuerdas BUSCARV de Hojas de cálculo de Google? Lo usé en mi artículo anterior para hacer coincidir datos y actualizar algunas celdas.
Si esta característica aún te asusta, es hora de enfrentarla y aprenderla de una vez por todas porque la usaré hoy 🙂
Consejo. Si está buscando una solución rápida que le ahorre tiempo, ve a aprender sobre Merge Sheets inmediato.
Recapitulemos brevemente la sintaxis de la fórmula:
=BUSCAR(búsqueda_clave, rango, índice, [is_sorted])
- clave de búsqueda es lo que buscas
- rango es el lugar que estás buscando.
- índice es el número de columna desde el que devuelve el valor.
- [is_sorted] es completamente opcional e indica si la columna clave está ordenada.
Consejo. Hay un tutorial completo dedicado a VERT SEARCH para hojas de cálculo de Google en nuestro blog, no dudes en echarle un vistazo.
Cuando fusioné dos Hojas de cálculo de Google y simplemente actualicé los datos en la columna Stock, utilicé esta fórmula de BÚSQUEDA DE VOL:
=ArrayFormula(IFERROR(VLOOKUP($B$2:$B$10,Sheet1!$B$2:$D$10,2,FALSE),""))
IFERROR se aseguró de que no hubiera errores en las celdas sin coincidencias y ARRAYFORMULA procesó toda la columna a la vez.
Entonces, ¿qué cambios debo hacer para extraer los proveedores como una nueva columna en la tabla de búsqueda?
Bueno, ya que es índice que le dice a Google Sheets BUSCARV de qué columna debe tomar los datos, seguramente será la que debe ajustarse.
La forma más fácil sería copiar la fórmula a la columna vecina y aumentarla índice con uno (reemplazar 2 con 3):
=ArrayFormula(IFERROR(VLOOKUP($B$2:$B$10,Sheet1!$B$2:$D$10,3,FALSE),""))
Sin embargo, deberá insertar la misma fórmula con un índice diferente tantas veces como columnas adicionales desee obtener.
Afortunadamente, hay una alternativa mejor. Se trata de crear matrices. Las matrices le permiten combinar todas las columnas que desea extraer en un solo índice.
Al crear una matriz en Hojas de cálculo de Google, enumere los valores o las referencias de celda/rango entre paréntesis, p. = {1, 2, 3} o ={1; 2; 3}
La disposición de estos registros en una hoja depende del delimitador:
- Si usa punto y coma, los números ocuparán filas diferentes en una columna:
- Si usa una coma, esos números aparecerán en columnas separadas en una fila:
Esto último es exactamente lo que debe hacer en el argumento de índice BUSCARV de Hojas de cálculo de Google.
Como estoy combinando Google Sheets, actualizando la segunda columna y arrastrando la tercera, necesito crear una matriz con estas columnas: {2.3}:
=ArrayFormula(IFERROR(VLOOKUP($B$2:$B$10,Sheet1!$B$2:$D$10,{2,3},FALSE),""))
De esta manera, una fórmula de BUSCARV de Hojas de cálculo de Google coincide con nombres, actualiza la información bursátil y agrega proveedores relacionados en una columna adyacente vacía.
Haga coincidir y combine hojas y agregue columnas con INDEX MATCH
El siguiente es ÍNDICE DE COINCIDENCIA. Estas dos funciones compiten con BUSCARV porque solucionan sus limitaciones al fusionar Hojas de cálculo de Google.
Consejo. Conozca INDEX MATCH para Hojas de cálculo de Google en este tutorial.
Permítanme comenzar recordándoles la fórmula que simplemente une una columna en función de las coincidencias:
=IFERROR(INDEX(Sheet1!$C$1:$C$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
En esta fórmula, Hoja1!$C$1:$C$10 es una columna con los valores que necesitas en cada momento Hoja1!$B$1:$B$10 cumple el mismo valor que en B2 en la tabla actual.
Teniendo en cuenta estos puntos, es Hoja1!$C$1:$C$10 que debe modificar no solo para fusionar tablas y actualizar celdas, sino también para agregar columnas.
A diferencia de Google Sheets VLOOKUP, aquí no hay nada especial. Simplemente ingrese el rango con todas esas columnas necesarias: la que desea actualizar y otras para agregar. en mi caso sera Hoja1!$C$1:$D$10:
=IFERROR(INDEX(Sheet1!$C$1:$D$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
O puedo extender el rango a E10 para agregar 2 columnas, no solo una:
=IFERROR(INDEX(Sheet1!$C$1:$E$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
Nota. Esos registros adicionales siempre caen en columnas vecinas. Si esas columnas tienen otros valores, la fórmula no los sobrescribirá. Le dará un error #REF con una pista correspondiente:
Una vez que elimine esas celdas o agregue nuevas columnas a su izquierda, aparecerán los resultados de la fórmula.
Combine Hojas de cálculo de Google, actualice celdas y agregue columnas relacionadas, todo usando QUERY
QUERY es una de las funciones más potentes de Google Sheets. Así que no sorprende que lo use hoy para fusionar algunas Hojas de cálculo de Google, actualizar celdas y agregar columnas adicionales al mismo tiempo.
Esta función se diferencia de otras porque uno de sus argumentos utiliza un lenguaje de comandos.
Consejo. Si se pregunta cómo usar la función de CONSULTA de Hojas de cálculo de Google, visite esta publicación de blog.
Recordemos primero la fórmula que actualiza las celdas:
=IFERROR(QUERY(Sheet1!$A$2:$C$10,"select C where B='"&QUERY!$B2:$B$10&"'"),"")
Aquí QUERY mira la tabla con los datos requeridos de la Hoja 1, hace coincidir las celdas de la columna B con mi nueva tabla actual y combina estas hojas: extrae los datos de la columna C para cada coincidencia. IFERROR mantiene el resultado libre de errores.
Para agregar columnas adicionales para esas coincidencias, debe realizar 2 pequeños cambios en esta fórmula:
- enumere todas las columnas requeridas para Seleccionar dominio:
…select C,D,E… - expanda el rango para buscar en consecuencia:
…QUERY(Sheet1!$A$2:$E$10,…
Aquí hay una fórmula completa:
=IFERROR(QUERY(Sheet1!$A$2:$E$10,"select C,D,E where B='"&Sheet4!$B2:$B$10&"'"),"")
Actualiza la columna de stock y extrae 2 columnas adicionales de la tabla de búsqueda en esta tabla principal.
Cómo agregar filas que no coinciden usando FILTRO + BUSCARV
Imagínese esto: combine 2 Hojas de cálculo de Google, actualice la información anterior con la nueva y obtenga nuevas columnas con valores relacionados adicionales.
¿Qué más podría hacer para tener una imagen completa de sus registros al alcance de su mano?
¿Quizás agregar filas que no coincidan al final de la tabla? De esa forma, tendrás todos los valores en un solo lugar: no solo coinciden con la información actualizada relacionada, sino también las discrepancias para que cuenten.
Me sorprendió gratamente que Google Sheets VLOOKUP sepa cómo hacer esto. Cuando se usa con la función FILTRO, fusiona Hojas de cálculo de Google y también agrega filas que no coinciden.
Consejo. Al finalTambién mostraré cómo un complemento hace lo mismo con una sola casilla de verificación.
Los argumentos del FILTRO de Hojas de cálculo de Google son bastante claros:
=FILTRO(rango, condición1, [condition2, …])
- rango son los datos que desea filtrar.
- condición 1 es una columna o fila con un criterio de filtro.
- criterio 2, criterio 3, etc. son completamente opcionales. Úselos cuando necesite usar múltiples criterios.
Consejo. Aprenderá más sobre la función FILTRO de Hojas de cálculo de Google en esta publicación de blog.
Entonces, ¿cómo se entienden estas dos funciones y cómo se fusionan Google Sheets? Bueno, FILTER devuelve datos según los criterios de filtro creados por BUSCARV.
Mira esta fórmula:
=FILTER(Sheet1!$A$2:$E$10,ISERROR(VLOOKUP(Sheet1!$B$2:$B$10,$B$2:$C$10,2,FALSE)=1))
Escanee 2 tablas de Google en busca de coincidencias y arrastre las filas que no coincidan de una tabla a la otra:
Déjame explicarte cómo funciona:
- FILTRO va a la hoja de búsqueda (una tabla con todos los datos – Hoja1!$A$2:$E$10) y use BUSCARV para obtener las filas correctas.
- BUSCARV toma los nombres de los elementos en la columna B en esa hoja de búsqueda y los compara con los nombres en mi tabla actual. Si no hay coincidencias, BUSCARV dice que hay un error.
- ESERROR marca cada uno de estos errores con 1, indicando a FILTRO que mueva esta fila a otra hoja.
Como resultado, la fórmula dibuja 3 filas adicionales para aquellas bayas que no aparecen en mi tabla principal.
No es tan complicado una vez que juegas un poco con este método 🙂
Pero si no quiere pasar tiempo con él, hay una manera mejor y más rápida, sin una sola función y fórmula.
La forma sin fórmulas de hacer coincidir y fusionar datos: el complemento Merge Sheets
Suplemento de hojas combinadas incluye las 3 posibilidades al fusionar Hojas de cálculo de Google:
- actualiza las celdas relacionadas en función de las coincidencias
- agregar nuevas columnas para esas coincidencias
- insertar filas con registros que no coinciden
Para evitar cualquier confusión, el proceso se divide en 5 sencillos pasos:
- Los primeros dos dónde estás selecciona tus comidas incluso si están en diferentes hojas de cálculo.
- En el 3ddebe elige las columnas clave que debe comprobarse en busca de coincidencias.
- El 4to paso te deja establecer columnas para actualizar con nuevos récords o agregar de una hoja a otra:
- Finalmente, el 5to paso tiene esa casilla de verificación que hacer que aparezcan todas las filas que no coinciden al final de la tabla actual:
Pasaron unos segundos antes de que pudiera ver el resultado:
Instalar hojas de combinación desde la tienda de Hojas de cálculo de Google y verá que procesa comidas más grandes con la misma rapidez. Gracias a Merge Sheets, tendrás más tiempo para asuntos importantes.
También dejaré este video de demostración de 3 minutos para ayudarlo a decidir 🙂
Hoja de cálculo con ejemplos de fórmulas.
Combinar Hojas de cálculo de Google, agregar columnas relacionadas y filas que no coinciden: ejemplos de fórmulas (haga una copia de esta hoja de cálculo)
