Cómo fusionar dos hojas de Google: buscar, comparar y actualizar registros

La entrada del blog de hoy presenta todas las formas de fusionar 2 hojas de Google. Utilizaremos VLOOKUP, INDEX/MATCH, QUERY y el complemento Merge Sheets para actualizar las celdas de una hoja a partir de los registros de otra, basándonos en las coincidencias de las columnas comunes.

• Cómo combinar hojas de Google mediante la función VLOOKUP
  • Sintaxis y uso
  • Ocultar los errores devueltos por VLOOKUP en Google Sheets – Función IFERROR
  • Coincidir y actualizar registros de toda la columna a la vez – ArrayFormula
• Combinar hojas de Google utilizando el dúo INDEX MATCH
  • INDEX MATCH en acción en Google Sheets
  • Manejar los errores devueltos por INDEX MATCH en Google Sheets
• Combinar hojas de Google y actualizar celdas con la función QUERY
• Combinación de tablas de diferentes hojas de cálculo de Google: función IMPORTRANGE
• Manejar los errores devueltos por la función INDEX MATCH en Google Sheets

Combinar hojas de Google con la función VLOOKUP

Lo primero a lo que puedes recurrir cuando necesites hacer coincidir y fusionar dos hojas de Google es la función VLOOKUP.

Sintaxis y uso

Esta función busca en una columna que especifiques un determinado valor clave y extrae uno de los registros relacionados de la misma fila en otra tabla u hoja.

Aunque el VLOOKUP de Google Sheets suele considerarse una de las funciones más difíciles, en realidad es bastante sencillo e incluso fácil una vez que se conoce.

Echemos un vistazo a sus componentes:

=VLOOKUP(clave_búsqueda, rango, índice, [is_sorted])

• search_key es el valor clave que está buscando. Puede ser cualquier cadena de texto, número o una referencia de celda.
• El rango es el grupo de celdas (o una tabla) donde buscarás la search_key y de donde sacarás los registros relacionados.
  Nota. VLOOKUP en Google Sheets siempre busca la clave de búsqueda en la primera columna del rango.
• El índice es el número de la columna dentro de ese rango de donde quieres sacar los datos.

  Por ejemplo, si tu rango a buscar es A2:E20 y es la columna E de la que necesitas obtener los datos, introduce 5. Pero si el rango es D2:E20, tendrás que introducir 2 para obtener los registros de la columna E.

• [is_sorted] es el único argumento que puede omitir. Se utiliza para decir si la columna con valores clave está ordenada (TRUE) o no (FALSE). Si es TRUE, la función trabajará con la coincidencia más cercana, si es FALSE – con una completa. Si se omite, se utiliza TRUE por defecto.

Consejo. Tenemos una guía detallada dedicada a VLOOKUP en Google Sheets. Por favor, consúltala para aprender más sobre la función, sus peculiaridades y límites, y obtener más ejemplos de fórmulas.

Teniendo en cuenta estos argumentos, vamos a utilizar VLOOKUP para combinar dos hojas de Google.

Supongamos que tengo una pequeña tabla con bayas y sus identificaciones en la Hoja2. Sin embargo, se desconoce la disponibilidad de las existencias:

Llamemos a esta tabla la principal ya que mi objetivo es rellenarla.

También hay otra tabla en la Hoja1 con todos los datos, incluyendo la disponibilidad de stock:

La llamaré la tabla de búsqueda ya que miraré en ella para obtener los datos.

Utilizaré la función VLOOKUP de Google Sheets para fusionar estas 2 hojas. La función hará coincidir las bayas en ambas tablas, y sacará la información correspondiente de la búsqueda en la tabla principal.

=VLOOKUP(B2,Hoja1!$B$2:$C$10,2,FALSE)

Así es como esta fórmula fusiona dos hojas de Google exactamente:

Utilizo referencias absolutas para el rango porque copio la fórmula hacia abajo en la columna y por lo tanto necesito que este rango permanezca igual en cada fila para que el resultado no se rompa.

Ocultar los errores devueltos por VLOOKUP en Google Sheets – IFERROR

¿Pero qué pasa con esos errores #N/A?

Los ves en esas filas donde las bayas no tienen coincidencias en otra hoja y no hay nada que devolver. Por suerte, hay una manera de mantener esas celdas vacías.

Sólo tienes que envolver tu VLOOKUP de Google Sheets en IFERROR

=IFERROR(VLOOKUP(B2,Hoja1!$B$2:$C$10,2,FALSE),N"N")

Consejo. Atrapa y corrige otros errores que tu VLOOKUP de Google Sheets pueda devolver utilizando las soluciones de esta guía.

Comparar y actualizar los registros de toda la columna a la vez – ArrayFormula

Una cosa más que me gustaría mencionar es cómo hacer coincidir y combinar los datos de Google Sheets para toda la columna a la vez.

No es nada del otro mundo, sólo una función más – ArrayFormula.

Simplemente reemplaza tu registro clave de una celda en el VLOOKUP de Google Sheets con toda la columna y pon esta fórmula completa dentro de ArrayFormula:

=ArrayFormula(IFERROR(VLOOKUP(B2:B10,Hoja1!$B$2:$C$10,2,FALSE),N"N"))

De esta manera, no tendrás que copiar la fórmula en la columna. ArrayFormula devolverá el resultado correcto a cada celda de inmediato.

Consejo. Encuentre más detalles sobre ArrayFormula en esta entrada del blog.

Aunque el VLOOKUP en las Hojas de cálculo de Google es perfecto para tareas tan simples, tiene algunos límites. Este es uno de los inconvenientes: no puede mirar a su izquierda. Cualquiera que sea el rango que indiques, siempre explora su primera columna.

Por lo tanto, si usted necesita para combinar 2 hojas de Google y tirar de los ID (datos de la primera columna) sobre la base de las bayas (segunda columna), VLOOKUP no ayudará. Simplemente no podrás construir una fórmula correcta.

En casos como este, INDEX MATCH para Google Sheets entra en el juego.

Combinar y fusionar hojas de Google usando el dúo INDEX MATCH

INDEX MATCH, o más bien INDEX & MATCH, son en realidad dos funciones diferentes de Google Sheets. Pero cuando se utilizan juntas, son como un VLOOKUP de nivel superior.

Sí, también fusionan las hojas de Google: actualizan las celdas de una tabla con registros de otra tabla basándose en registros clave comunes.

Pero lo hacen mucho mejor ya que ignoran todas esas limitaciones que tiene VLOOKUP.

No voy a cubrir todos los fundamentos hoy porque ya lo hice en esta entrada del blog. Pero te daré algunos ejemplos de fórmulas INDEX MATCH para que puedas ver cómo funcionan directamente en las hojas de cálculo de Google. Utilizaré las mismas tablas de ejemplo de arriba.

INDEX MATCH en acción en Google Sheets

En primer lugar, vamos a fusionar esas hojas de Google y actualizar la disponibilidad de las existencias de todas las bayas que coincidan:

=INDEX(Hoja1!$C$1:$C$10,MATCH(B2,Hoja1!$B$1:$B$10,0))

¿Cómo funcionan INDEX y MATCH cuando se usan juntos de esta manera?

Ahora vamos a probar INDEX MATCH con lo que el VLOOKUP de Google Sheets no puede hacer: fusionar hojas y actualizar la columna más a la izquierda con los IDs requeridos:

=INDEX(Hoja1!$A$2:$A$10,MATCH(B2,Hoja1!$B$2:$B$10,0))

Muy fácil 🙂

Manejar los errores devueltos por INDEX MATCH en Google Sheets

Vamos a ir más allá y a deshacernos de esos errores en las celdas sin coincidencias. IFERROR nos ayudará de nuevo. Simplemente pon tu INDEX MATCH de Google Sheets como primer argumento.

Ejemplo 1.

=IFERROR(INDEX(Hoja1!$C$1:$C$10,MATCH(B2,Hoja1!$B$1:$B$10,0)),N-"N-")

=IFERROR(INDEX(Hoja1!$A$2:$A$10,MATCH(B2,Hoja1!$B$2:$B$10,0)),N"N")

Ahora, ¿cómo se combinan esas hojas de Google utilizando INDEX MATCH y se actualizan todas las celdas de la columna completa a la vez?

Pues… no lo haces. Hay un pequeño problema: ArrayFormula no funciona con estas dos.

Tendrás que copiar la fórmula INDEX MATCH en la columna o utilizar la función QUERY de Google Sheets como alternativa.

Combinar hojas de Google y actualizar celdas usando QUERY

La función QUERY de Google Sheets es la más potente de las hojas de cálculo. Por ello, no es de extrañar que ofrezca una forma de fusionar tablas, es decir, de hacer coincidir y fusionar valores de diferentes hojas.

=QUERY(datos, consulta, [cabeceras])
Consejo. Si nunca has utilizado el QUERY de Google Sheets, este tutorial te permitirá conocer su peculiar lenguaje.

¿Cómo debería ser la fórmula de QUERY para actualizar la columna Stock con los datos reales?

=QUERY(Hoja1!$A$2:$C$10,N "select C where B=’N"&Hoja4!$B2:$B$10&"N"N")

• Google Sheets QUERY busca en mi hoja de búsqueda (Hoja1 con los registros que necesito tirar a mi tabla principal)
• y devuelve todas las celdas de la columna C en las que la columna B coincide con las bayas de mi tabla principal

Permíteme perder esos errores para las celdas sin coincidencias:

=IFERROR(QUERY(Hoja1!$A$2:$C$10,"select C where B='"&Hoja4!$B2:$B$10&"‘"),"")

Bueno, así está mejor 🙂

Combinar tablas de diferentes hojas de cálculo de Google – Función IMPORTRANGE

Hay una función más que me gustaría mencionar. Es importante porque te permite fusionar hojas que residen en diferentes hojas de cálculo de Google (archivos).

La función se llama IMPORTRANGE:

=IMPORTRANGE(N-"hoja de cálculo_url",N-"rango_cadena")

• el primero va el enlace a la hoja de cálculo en la que se extraen los datos de
• la segunda va la hoja y el rango que quieres tomar de esa hoja de cálculo

Nota. Te recomiendo encarecidamente que revises los documentos de Google sobre esta función para que no te pierdas ningún matiz importante de su funcionamiento.

Imagina que tu hoja de búsqueda (con los datos de referencia) está en la hoja de cálculo 2 (también conocida como hoja de cálculo de búsqueda). Tu hoja principal está en la Hoja de cálculo 1 (hoja de cálculo principal).

Nota. Para que IMPORTRANGE funcione, debes conectar ambos archivos. Y aunque la Hoja de cálculo de Google sugiere un botón para ello justo después de escribir la fórmula en una celda y pulsar Intro, para las fórmulas que aparecen a continuación es posible que tengas que hacerlo antes. Esta guía paso a paso te ayudará.

A continuación se muestran los ejemplos para combinar hojas de Google de diferentes archivos utilizando IMPORTRANGE con cada función que has aprendido hoy.

Ejemplo 1. IMPORTRANGE + VLOOKUP

Utiliza IMPORTRAGE como rango en VLOOKUP para fusionar 2 hojas de cálculo de Google separadas:

=ArrayFormula(IFERROR(VLOOKUP(B2:B10,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,"¡Hoja1!$B$2:$C$10″),2,FALSE),N-"N")

Ejemplo 2. IMPORTRANGE + INDEX MATCH

Como en el caso de INDEX MATCH & IMPORTRANGE, la fórmula se vuelve más voluminosa ya que hay que referenciar otra hoja de cálculo dos veces: como rango para INDEX y como rango para MATCH:

=IFERROR(INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,"Hoja1!$A$1:$A$10″),MATCH(B2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,"Hoja1!$B$2:$B$10″),0)),N-"N-".)

=IFERROR(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Sq…j7o/edit”,"¡Hoja1!$A$2:$C$10″),"select Col3 where Col2='"&QUERY!$B2:$B$10&"‘"),N"N")

¡Uf!

Eso es todo para las funciones y fórmulas.

Usted es libre de elegir cualquier función y construir su propia fórmula por los ejemplos anteriores …

o…

…¡prueba una herramienta especial que fusiona hojas de Google por ti! 😉

Manera libre de fórmulas para combinar datos – Complemento de fusión de hojas para Google Sheets

Si no tienes tiempo para construir o incluso aprender fórmulas, o si simplemente estás buscando la forma más fácil de unir datos basados en registros comunes, Merge Sheets será perfecto.

Todo lo que tendrás que hacer es marcar las casillas de verificación en 5 pasos fáciles de usar:

elija las columnas que desea actualizar:

There is even a possibility to save all the selected options into a scenario and reuse it whenever you need:

Watch this 3-minute demo video to see how it works:

{
"@context": "http://schema.org”,
"@type": "VideoObject",
"name": "Merge Sheets in Google Sheets",
"description": "Use Merge Sheets to keep your Google Sheets tables up-to-date. The add-on will scan two tables for matches and do a quick vlookup. It will update old records, add missing info, and pull new rows and columns to your main table. Learn more on our website: https://www.ablebits.com/google-sheets-add-ons/merge-sheets/index.php Get Merge Sheets at Google Sheets add-ons store: https://gsuite.google.com/marketplace/app/merge_sheets/850686067510”,
"thumbnailUrl": "https://i.ytimg.com/vi/V4DXNgqEdLc/default.jpg”,
"uploadDate": "2019-10-30T13:12:20Z",
"duration": "PT2M56S",
"embedUrl": "https://www.youtube.com/embed/V4DXNgqEdLc”,
"interactionCount": "10317",
"publisher": {
"@type": "Organization",
"name": "Ablebits.com",
"url": "https://www.ablebits.com”
},
"author": {
"@type": "Person",
"name": "Irina Pozniakova",
"url": "https://www.ablebits.com/office-addins-blog/author/irina-pozniakova/”
}
}
I encourage you to install your Merge Sheets from the Google Sheets store and follow these instructions to try and update your own table with the info from another sheet.