En el mundo de la gestión y análisis de datos, Google Sheets se ha convertido en una herramienta fundamental para muchos profesionales y estudiantes. Sin embargo, a medida que nuestras hojas de cálculo se vuelven más complejas, puede resultar difícil fusionar datos de múltiples hojas, agregar columnas con información relacionada o insertar filas que no coinciden. ¡Pero no te preocupes! En este artículo, te mostraremos cinco formas útiles de hacer todas estas tareas de manera sencilla y eficiente. Así que prepárate para descubrir cómo optimizar tu trabajo con Google Sheets y aprovechar al máximo esta poderosa herramienta.
¿Sabías que cuando combinas dos hojas de Google no solo puedes actualizar registros en una columna sino también extraer columnas completas relacionadas e incluso filas que no coinciden? Hoy les mostraré cómo se hace con las funciones BUSCARV, ÍNDICE/COINCIDIR, CONSULTA y el complemento Combinar hojas.
La última vez que hablé sobre fusionar dos hojas de Google, compartí formas de unir y actualizar datos. Esta vez, seguiremos actualizando las celdas, pero también extraeremos otras columnas relacionadas y filas que no coincidan.
Aquí está mi tabla de búsqueda. Voy a tomar todos los datos necesarios hoy:
Esta vez es 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 seleccionaré proveedores. Bueno, tal vez las calificaciones también 🙂
Como siempre, usaré algunas funciones y un complemento especial para el trabajo.
Fusione hojas de Google y agregue columnas relacionadas usando BUSCARV
¿Recuerdas la BUSCARV de Google Sheets? Lo usé en mi artículo anterior para hacer coincidir datos y actualizar algunas celdas.
Si esta función todavía te asusta, ya es hora de afrontarla y aprenderla de una vez por todas porque hoy también la voy a usar 🙂
Consejo. Si está buscando una solución rápida para ahorrar tiempo, ve a conocer las hojas de combinación de inmediato.
Hagamos un resumen rápido de la sintaxis de la fórmula:
=BUSCARV(clave_búsqueda, rango, índice, [is_sorted])
- clave de búsqueda es lo que estás buscando.
- rango es donde estás buscando.
- índice es el número de la columna de la que devolver el valor.
- [is_sorted] es completamente opcional e indica si la columna clave está ordenada.
Consejo. Hay un tutorial completo dedicado a BUSCARV de Google Sheets en nuestro blog, no dudes en echarle un vistazo.
Cuando fusioné dos hojas de Google y simplemente actualicé los datos en la columna Stock, utilicé esta fórmula BUSCARV:
=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 proveedores como una nueva columna de la tabla de búsqueda también?
Bueno, ya que es el índice que le dice a Google Sheets BUSCARV de qué columna debe tomar los datos, es seguro decir que es la que necesita ajustes.
La forma más sencilla sería simplemente copiar la fórmula en la columna vecina y aumentar su índice por 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, existe una alternativa mejor. Implica crear matrices. Las matrices le permiten combinar todas las columnas que desea incluir en un índice.
Cuando crea una matriz en Google Sheets, enumera valores o referencias de celdas/rango entre paréntesis, por ejemplo ={1, 2, 3} o ={1; 2; 3}
La disposición de estos registros en una hoja depende del delimitador:
- Si usa un punto y coma, los números ocuparán diferentes filas dentro de 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 del índice BUSCARV de Google Sheets.
Dado que fusiono hojas de Google, actualizo la segunda columna y extraigo 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 BUSCARV de Google Sheets coincide con nombres, actualiza la información de stock y agrega proveedores relacionados en una columna adyacente vacía.
Combine y combine hojas y agregue columnas con INDEX MATCH
El siguiente es el PARTIDO ÍNDICE. Estas dos funciones juntas compiten con BUSCARV ya que evitan sus limitaciones al fusionar hojas de Google.
Consejo. Conozca INDEX MATCH para Google Sheets en este tutorial.
Permítanme comenzar recordándoles la fórmula que simplemente fusiona una columna según 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 siempre Hoja1!$B$1:$B$10 cumple con el mismo valor que en B2 en la tabla actual.
Teniendo en cuenta estos puntos, es Hoja1!$C$1:$C$10 que necesita cambiar para no solo fusionar tablas y actualizar celdas, sino también agregar columnas.
A diferencia de BUSCARV de Google Sheets, aquí no hay nada especial. Simplemente ingresas al rango con todas esas columnas requeridas: la que actualizar y otras que agregar. En mi caso será Hoja1!$C$1:$D$10:
=IFERROR(INDEX(Sheet1!$C$1:$D$10,MATCH(B2,Sheet1!$B$1:$B$10,0)),"")
O puedo ampliar 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 las columnas vecinas. Si esas columnas tendrán otros valores, la fórmula no los sobrescribirá. Le dará un error #REF con la pista correspondiente:
Una vez que borre esas celdas o agregue nuevas columnas a la izquierda de ellas, aparecerán los resultados de la fórmula.
Fusione hojas de Google, actualice celdas y agregue columnas relacionadas, todo usando QUERY
QUERY es una de las funciones más potentes de las hojas de cálculo de Google. Así que no sorprende que lo use hoy para fusionar algunas hojas 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 comando.
Consejo. Si se pregunta cómo utilizar la función CONSULTA de Google Sheets, 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í CONSULTA mira la tabla con los datos requeridos en la Hoja1, hace coincidir las celdas de la columna B con mi nueva tabla actual y combina estas hojas: extrae 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:
- enumerar todas las columnas imprescindibles para el seleccionar dominio:
…select C,D,E… - amplíe el rango para verse 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 acciones y extrae 2 columnas adicionales de la tabla de búsqueda a esta tabla principal.
Cómo agregar filas que no coinciden usando FILTRO + BUSCARV
Imagínese esto: combina dos hojas de Google, actualiza la información antigua con la nueva y obtiene nuevas columnas con valores relacionados adicionales.
¿Qué más podrías hacer para tener a mano una imagen completa de los registros?
¿Quizás agregar filas que no coinciden al final de su tabla? De esta manera, tendrá todos los valores en un solo lugar: no solo las coincidencias con la información relacionada actualizada sino también las no coincidencias para que cuenten.
Me sorprendió gratamente que Google Sheets VLOOKUP sepa cómo hacer eso. Cuando se usa junto con la función FILTRO, fusiona hojas 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 Google Sheets son bastante claros:
=FILTRO(rango, condición1, [condition2, …])
- rango son los datos que desea filtrar.
- condición1 es una columna o una fila con un criterio de filtrado.
- criterio2, criterio3, etc. son completamente opcionales. Úselos cuando necesite utilizar varios criterios.
Consejo. Aprenderá más sobre la función FILTRO de Google Sheets en esta publicación de blog.
Entonces, ¿cómo se combinan estas dos funciones y fusionan las hojas de Google? Bueno, FILTER devuelve los datos según los criterios de filtrado 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))
Escanea 2 tablas de Google en busca de coincidencias y extrae las filas que no coinciden 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 utiliza BUSCARV para obtener las filas correctas.
- BUSCARV toma los nombres de los elementos de la columna B en esa hoja de búsqueda y los relaciona con los nombres de mi tabla actual. Si no hay ninguna coincidencia, BUSCARV dice que hay un error.
- ISERROR marca cada uno de estos errores con 1, indicando a FILTRO que lleve esta fila a otra hoja.
Como resultado, la fórmula extrae 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 perder su tiempo en esto, existe una manera mejor y más rápida, sin una sola función ni fórmula.
Manera sin fórmulas de unir y fusionar datos: complemento Merge Sheets
Complemento Combinar hojas abarca las 3 posibilidades al fusionar hojas de Google:
- actualiza las celdas relacionadas según las coincidencias
- agrega nuevas columnas para esas coincidencias
- inserta filas con registros que no coinciden
Para evitar cualquier confusión, el proceso se divide en 5 sencillos pasos:
- Los primeros dos estas donde tu selecciona tus mesas incluso si están en hojas de cálculo diferentes.
- En el 3dusted debe elegir columna(s) clave eso debe ser revisado para ver si hay coincidencias.
- El 4to paso te deja configurar las columnas para actualizar con nuevos registros 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 su tabla actual:
Pasaron unos segundos hasta que pude ver el resultado:
Instalar hojas de combinación desde la tienda de Google Sheets y verás que procesa tablas más grandes con la misma rapidez. Gracias a Merge Sheets, tendrás más tiempo para asuntos importantes.
También te dejaré este vídeo demostrativo de 3 minutos para ayudarte a decidirte 🙂
Hoja de cálculo con ejemplos de fórmulas.
Fusionar hojas de Google, agregar columnas relacionadas y filas que no coincidan: ejemplos de fórmulas (haga una copia de esta hoja de cálculo)
Usted también podría estar interesado en
Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded
5 formas de fusionar hojas de Google, agregar columnas con datos relacionados e insertar filas que no coinciden
Las hojas de Google son una herramienta poderosa para organizar y analizar datos. Pero, ¿sabías que hay formas de fusionar hojas, agregar columnas con datos relacionados e insertar filas que no coinciden? En este artículo, exploraremos cinco métodos para hacer estas tareas de manera eficiente.
1. Usando la función IMPORTRANGE
La función IMPORTRANGE te permite importar datos de una hoja a otra en Google Sheets. Puedes fusionar hojas de diferentes archivos creando una conexión entre ellas. Sigue estos pasos:
- Abre una hoja de Google donde quieras fusionar los datos.
- En una celda vacía de la hoja, utiliza la fórmula
=IMPORTRANGE("URL de la hoja de origen"; "Hoja1!Rango de datos"). - Sigue las instrucciones para autorizar el acceso a la hoja de origen y seleccionar el rango de datos que deseas importar.
- Presiona Enter y verás que los datos se fusionan en la hoja de destino.
Este método es útil para combinar datos de múltiples fuentes en una hoja centralizada.
2. Utilizando la función QUERY
La función QUERY te permite filtrar y organizar datos de manera personalizada. Puedes agregar columnas con datos relacionados utilizando esta función. Sigue estos pasos:
- Selecciona una celda donde desees que aparezca el resultado de la consulta.
- Usa la fórmula
=QUERY(Rango de datos; "consulta"; Número de encabezados). - Reemplaza “Rango de datos” por el rango que deseas consultar y “consulta” con los criterios de filtrado y organización que necesitas. También especifica el número de encabezados que tiene tu rango de datos.
- Pulsa Enter y los datos filtrados y organizados según tu consulta se mostrarán en la celda seleccionada.
Puedes utilizar esta función para agregar datos relacionados a tu hoja sin necesidad de mover o copiar columnas manualmente.
3. Utilizando la función VLOOKUP
La función VLOOKUP te permite buscar y recuperar valores de una columna en base a un valor de referencia. Puedes agregar columnas con datos relacionados utilizando esta función. Sigue estos pasos:
- Selecciona una celda donde desees que aparezca el resultado de la búsqueda.
- Usa la fórmula
=VLOOKUP(Valor de búsqueda; Rango de datos; Número de columna; [Coincidencia exacta]). - Reemplaza “Valor de búsqueda” con el valor que estás buscando, “Rango de datos” con el rango donde está la columna que contiene el valor buscado, “Número de columna” con el número de columna del valor que deseas recuperar y [Coincidencia exacta] con “VERDADERO” o “FALSO” dependiendo de si deseas una coincidencia exacta o aproximada.
- Pulsa Enter y el valor buscado se mostrará en la celda seleccionada.
Esta función es útil cuando deseas agregar datos relacionados en base a un identificador común en tus hojas.
4. Utilizando la función CONCATENAR
La función CONCATENAR te permite combinar el contenido de múltiples celdas en una sola celda. Puedes utilizar esta función para insertar filas que no coinciden en tus hojas. Sigue estos pasos:
- Selecciona una celda donde desees que aparezca la fila combinada.
- Usa la fórmula
=CONCATENAR(Celda1; Celda2; Celda3; ...). - Reemplaza “Celda1”, “Celda2”, “Celda3”, etc. con las celdas que deseas combinar.
- Pulsa Enter y las celdas se combinarán en una fila en la celda seleccionada.
Esta función es útil cuando deseas insertar filas que no coinciden y necesitas combinar información de diferentes celdas.
5. Utilizando la función FILTER
La función FILTER te permite filtrar un rango de datos en base a criterios específicos. Puedes utilizar esta función para mostrar solo las filas que no coinciden en tus hojas. Sigue estos pasos:
- Selecciona una nueva hoja donde desees que aparezcan las filas filtradas.
- En la primera celda de la nueva hoja, utiliza la fórmula
=FILTER(Rango de datos; Condición1; Condición2; ...). - Reemplaza “Rango de datos” con el rango que deseas filtrar y “Condición1”, “Condición2”, etc. con los criterios de filtrado que necesitas.
- Pulsa Enter y las filas que cumplan con las condiciones de filtrado se mostrarán en la nueva hoja.
Utiliza esta función cuando deseas mostrar únicamente las filas que no coinciden con ciertos criterios en tus hojas.
Con estas cinco formas de fusionar hojas de Google, agregar columnas con datos relacionados e insertar filas que no coinciden, puedes aprovechar al máximo las funcionalidades de Google Sheets para organizar tus datos de manera eficiente.
Para obtener más información sobre las funciones y características de Google Sheets, visita la página de Ayuda de Google Sheets.