Consolidar múltiples hojas en una sola desde Google Sheets

Consolidar tablas en Google Sheets

En este artículo vemos como combinar hojas en Google Sheets, consolidando todos nuestras tablas en una sola pestaña.

Aquí pueden ver el tutorial en video:

¿Para que consolidar?

Muchas veces, tenemos los datos repartidos en distintas bases, bien sea porque así se hizo desde el principio o porque lo decidimos así, separándolo por años, o por países, o por categorías, o lo que sea.

Pero si resulta que después queremos hacer un reporte o gráfica o tabla dinámica de todos mis datos, para esto necesito juntar o consolidar todas esas tablas que tenemos separadas, en una sola.

La manera manual

La manera más fácil es copiar y pegar, una por una.

Esto está bien si solo lo necesitamos una vez, pero si es un informe que queremos hacer cada semana o cada mes, o si es un informe en tiempo real que queremos realizar, entonces debemos encontrar alguna manera de poder juntar estos datos más fácilmente.

Uniendo con arreglos

Con la ayuda de los símbolos {} (conocidos como Array literals) podemos unir fácilmente diferentes rangos.

Por ahora solo debemos tener en cuenta que para separar filas usamos el punto y coma (;) y para separar columnas usamos la barra inversa (\).

Nota: Para Estados Unidos, Inglaterra y México, el separador de columnas es “,”

Entonces en un caso muy sencillo, como el siguiente:

Simplemente abro mis corchetes, selección el primer rango, ingreso el separador, en este caso “;” y después escojo el siguiente rango.

Y ya está. Ya combinamos nuestros primeros rangos.

Lo más importante a saber por ahora es que cuando juntemos filas, el número de columnas de todos los rangos debe ser igual.

Varias hojas

Siguiendo la misma lógica, vamos a abrir nuestro corchete, pero vamos a escoger rangos de distintas pestañas como en el siguiente ejemplo:

={Colombia!A1:J112;Ecuador!A1:J128;Peru!A1:J85}

Si bajamos a la fila donde empieza Ecuador nos encontramos con un error

Y es que está trayendo todos los encabezados. Esto lo solucionamos empezando los arreglos de Ecuador y Perú desde la fila 2. Quedaría así:

={Colombia!A1:J112;Ecuador!A2:J128;Peru!A2:J85}

Rangos abiertos

Esto esta muy bien para cuando los rangos son cerrados o fijos.

Por ejemplo, en el caso de arriba, el rango en la pestaña “Colombia” siempre va a ir hasta la fila 112, ni una más ni una menos. Lo mismo para las demás.

Pero como esto no siempre es así, tenemos que prever que nuestras bases pueden cambiar y no queremos estar siempre teniendo que cambiar manualmente los rangos

Para esto, vamos a dejar los rangos abiertos, es decir sin fila final. Nuestra fórmula cambiaria a:

={Colombia!A1:J;Ecuador!A2:J;Peru!A2:J}

En principio nada cambia, pero si bajamos un poco, nos damos cuenta de un error

La formula tal como está nos trae no solo los datos, sino las filas vacías.

Hay varias formas de solucionar esto:

Ordenar

Si ordenamos de manera sencilla el arreglo que acabamos de construir, nos va a dejar los vacíos de último. La fórmula sería:

=SORT({Colombia!A1:J;Ecuador!A1:J;Peru!A1:J})

En este caso vamos a encontrar un error similar a lo que ya nos había sucedido con los encabezados: Como dejamos formulado el encabezado de Colombia, al ordenarlo, lo va a bajar a la última fila

Para solucionar esto, también vamos a quitarle el encabezado a Colombia. pegar como valores el encabezado, es decir la primera fila, y empezamos nuestra fórmula desde la fila 2 y no desde la 1 como antes.

=SORT({Colombia!A2:J;Ecuador!A2:J;Peru!A2:J})

Y listo!

Filtrar con un Query

Otra forma de filtrar sin necesidad de ordenar es usando la función QUERY donde solo mostramos las filas que en alguna columna especifica no tengan valores.

Como QUERY es una función tan potente, pero intimidante sobre todo si no la conocemos antes, les dejo todo un tutorial de introducción a QUERY, e inclusive un curso completo de QUERY

Es importante escoger una columna que estemos seguros de que siempre va a tener un valor.

En nuestro caso vamos a escoger la B de “Producto”. Pero como le estamos “dando” a QUERY un arreglo creado, la columna no la vamos a llamar B, sino Col2.

El QUERY quedaría así:

=QUERY({Colombia!A2:J;Ecuador!A2:J;Peru!A2:J};"SELECT * WHERE Col2<>''")

Otra manera alternativa de hacerlo es usar el comando IS NOT NULL que traduce “no es nulo”. Quedaría así:

=QUERY({Colombia!A2:J;Ecuador!A2:J;Peru!A2:J};"SELECT * WHERE Col2 IS NOT NULL")

Hacer un arreglo de QUERIES

Otra forma de hacerlo, es primero filtrar cada una de nuestras bases y después, hacemos el arreglo.

En otras palabras es invertir el orden de la fórmula anterior.

Primero hagamos un QUERY para Colombia donde quitemos los vacíos:

=QUERY(Colombia!A2:J;"SELECT * WHERE B<>''")

Lo mismo que el anterior, pero la columna ahora si es B y no es Col2

Hacemos lo mismo para Perú y Ecuador

=QUERY(Peru!A2:J;"SELECT * WHERE B<>''")
=QUERY(Ecuador!A2:J;"SELECT * WHERE B<>''")

Por ultimo juntamos estos 3 QUERIES con un arreglo

={QUERY(Colombia!A2:J;"SELECT * WHERE B<>''");QUERY(Ecuador!A2:J;"SELECT * WHERE B<>''");QUERY(Peru!A2:J;"SELECT * WHERE B<>''")}

Hacer un arreglo de FILTERS

Es lo mismo que el caso anterior pero lo podemos hacer con funciones FILTER

La función FILTER para Colombia sería

=FILTER(Colombia!A2:J;Colombia!B2:B<>"")

Las de Ecuador y Perú quedarían así:

FILTER(Ecuador!A2:J;Ecuador!B2:B<>"")
FILTER(Peru!A2:J;Peru!B2:B<>"")

Y el arreglo final quedaría así:

={FILTER(Colombia!A2:J;Colombia!B2:B<>"");FILTER(Ecuador!A2:J;Ecuador!B2:B<>"");FILTER(Peru!A2:J;Peru!B2:B<>"")}

La ventaja de usar QUERY en vez de FILTER es que dentro del mismo QUERY podemos después ordenar y hacer cosas más avanzadas

Nuevos métodos

Hay unas nuevas funciones como VSTACK que hacen esta labor más fácil. Por favor dejenme saber en los comentarios si les hago un par de ejemplos con esta función

Conclusión

Como ven, hay varias maneras de combinar pestañas para consolidar nuestros datos.

Todo depende de detalles específicos de sus bases, y de su objetivo final.

Cuéntenme casos específicos donde esto no funcione y cualquier otra sugerencia.

29 comentarios en “Consolidar múltiples hojas en una sola desde Google Sheets”

  1. Hola Juan!, tus videos tutoriales me han ayudado mucho con varios trabajos que realizo con planillas excel en google sheets!, pero ahora tengo problemas utilizando la función query en este ejemplo, ya que en mi caso tengo en 1 sola planilla de google sheets con varias hojas que contienen tablas con cantidad de filas diferentes y columnas diferentes pero tienen 1 columna en común que es como un ID, el tema está en que intento combinar/consolidar 2 tablas con nombres diferentes de columnas y estas coincidan con los datos que se extraen, por ejemplo:
    Tengo tabla 1 con RUT como primera columna y varios datos personales en las siguientes columnas, luego tengo en tabla 2, la misma primera columna con RUT que en tabla 1 también están pero con otros datos y quiero traer los RUT de la tabla 2 y que las siguientes columnas sean tanto de la tabla 1 como 2 para combinar la información entre ellas, pero probando con si anidados, o la función esblanco anidada, entre otras funciones y me arroja siempre el mismo error de “Cantidad de Array faltantes por filas vacias” o me corta la tabla en dos mas abajo con sus filas vacías, existe alguna forma en que pueda realizar esa tarea con la función query o un filter? porque encontré una solución utilizando el buscarv que me traer el dato en especifico utilizando un dato común, pero la idea es poder automatizar la hoja para solo ir cambiando el nombre de la hoja a la hora de hacer la consulta query o bien los datos que quiero traerme con su columna RUT y coincidir los datos de las otras columnas según mi columna RUT. no lo hago desde SQL ya que tengo recursos limitados y tampoco son una cantidad gigantesca de datos como para utilizar esa opción. desde ya muchas gracias por todo el contenido compartido!!

    1. Hola Gonzalo
      Ahora hay un par de nuevas funciones en Sheets que permiten hacer eso más fácil
      una es CHOOSECOLS que te permite crear una tabla solo con las columnas que necesitas y en el orden que quieras.
      La otra es VSTACK que te permite unir tablas de manera rapida
      Saludos!

  2. Hola!
    Qué pasa si demo unificar 2 bases de datos, pero una BD tiene más columnas que la otra, ¿Cómo debo usar la fórmula para que queden los datos en las columnas que son?

  3. Hola Juan! Necesito poder ordenar por alguna columna en especial en la planilla donde Query me compila todos los datos. Usando tu ejemplo de Ecuador, Colombia y Perú, supongamos que quiero ordenarlo por País Importador (en mi caso, fechas que esta en la octava columna). Cómo se haría? Explico que si le pongo la opción de filtros en los encabezados, me trae filas vacías al comienzo pero nunca me ordena por fecha, intenta pero vuelve a la original)

  4. Buenos días,
    tengo unas 144 hojas, con 40000 filas cada una. El límite creo que está en un millón y algo de filas. Todas las hojas tienen las mismas columnas. Son hoja por mes de los últimos 12 años. Unificarlas no se puede por el límite de filas. ¿hay alguna forma de hacer una tabla dinámica para poder consultar los datos de todas al mismo tiempo? o una base de datos alternativa donde poner toda la información y hacer la consulta desde google sheets??
    Muchas gracias

  5. Estoy consolidando dos consultas (Query), pero cuando un de los dos no tiene datos que extraer no me salen los datos de la otra consulta. ¿Como puedo hacer para que de como respuesta la consulta que si tiene datos?

  6. Juan buenas tardes,

    ¿Cómo se pueden poner dos bases de datos distintas (tienen datos distintos como es la columna FECHA) en una única tabla dinámica?

    La única opción que he encontrado añadiendo un campo calculado a la variable valores de una tabla dinámica inicial.

    El problema es que a hora de aplicarle un control de filtros, solo me lo aplica de los datos de la tabla dinamica original y no del campo calculado añadido.

    No sé si me explico.

    Muchas gracias Juan

  7. Hola, estoy utilizando la formula de query, ={QUERY(‘1.ESP’!A6:A;”SELECT * WHERE A””);QUERY(‘2.ESP’!A5:A;”SELECT * WHERE A””);Query(‘3.ESP’!A5:A;”SELECT * WHERE A””);QUERY(‘4.ESP’!A5:A;”SELECT * WHERE A””);QUERY(‘5.ESP’!A5:A;”SELECT * WHERE A””);QUERY(‘6.ESP’!A5:A;”SELECT * WHERE A””);QUERY(‘1.CVN’!A5:A;”SELECT * WHERE A””);QUERY(‘2.CVN’!A5:A;”SELECT * WHERE A””);QUERY(‘3.CVN’!A5:A;”SELECT * WHERE A””);QUERY(‘4.CVN’!A5:A;”SELECT * WHERE A””);QUERY(‘1.TIT’!A5:A;”SELECT * WHERE A””);QUERY(‘2.TIT’!A5:A;”SELECT * WHERE A””);QUERY(‘3.TIT’!A5:A;”SELECT * WHERE A””);QUERY(‘4.TIT’!A5:A;”SELECT * WHERE A””);QUERY(‘5.TIT’!A5:A;”SELECT * WHERE A””);QUERY(‘6.TIT’!A5:A;”SELECT * WHERE A””);QUERY(‘7.TIT’!A5:A;”SELECT * WHERE A””);QUERY(‘1.ACH’!A5:A;”SELECT * WHERE A””);QUERY(‘2.ACH’!A5:A;”SELECT * WHERE A””);QUERY(‘3.ACH’!A5:A;”SELECT * WHERE A””);QUERY(‘4.ACH’!A5:A;”SELECT * WHERE A””);QUERY(‘5.ACH’!A5:A;”SELECT * WHERE A””);QUERY(‘6.ACH’!A5:A;”SELECT * WHERE A””);QUERY(‘1.GEPE’!A5:A;”SELECT * WHERE A””);QUERY(‘2.GEPE’!A5:A;”SELECT * WHERE A””);QUERY(‘3.GEPE’!A5:A;”SELECT * WHERE A””);QUERY(‘4.GEPE’!A5:A;”SELECT * WHERE A””);QUERY(‘6.GEPE’!A5:A;”SELECT * WHERE A””);QUERY(‘7.GEPE’!A5:A;”SELECT * WHERE A””);QUERY(‘8.GEPE’!A5:A;”SELECT * WHERE A””);QUERY(‘9.GEPE’!A5:A;”SELECT * WHERE A””)}

    Pero me aparece el siguiente error: “#N/D”, como hago para que me quede la casilla en blanco?

  8. Buen dia.

    Esta genial este tutorial, muy bien explicado y todos las opciones me funcionaron, ahora tengo una duda:
    en el escenario en que los datos estan en diferentes libros? es decir no en el mismo documento supongo se usuario la instrucción IMPORTRANGE, pero como serian los argumentos?

  9. Hola Juan, qué útil y qué facilidad para explicarlo todo tan bien, tanto aquí como en el vídeo de Youtube., así que enhorabuena y gracias por compartirlo.

    Yo he puesto la fórmula, pero me sigue devolviendo #¡valor! (No se puede analizar la cadena de la consulta para En la función QUERY, el parámetro 2: NO_COLUMN: col12)

    Las hojas tienen nombres muy largos, no sé si quizás es por eso. Te pongo aquí mi fórmula por si me puedes ayudar por favor.

    =query({‘1. Encuesta antes de empezar el curso 05 – Paellas y Arroces Gourmet Caldosos, S…’!A2:L;’1. Encuesta antes de empezar el curso (12 Claves de la pasta fresca y rellena)’!A2:L;’1. Encuesta antes de empezar el curso (17 – Cocina japonesa las claves del sushi…’!A2:L;’1. Encuesta antes de empezar el curso (106 – Gin Tonics Perfectos)’!A2:L};”select * where col12””)

    Si pongo la fórmula sin la última parte (a partir del select) sí que me devuelve los datos, pero con las filas en blanco de cada hoja.

    Gracias de antemano

  10. Nohelia Andrea Galindo

    Estimado Juan, solo voy a decir una frase:

    “Eres de lo mejor!

    Gracias por compartir tus conocimientos, la verdad es que super genial tu explicación, me ahorraste horas de “Carpintería! y me regalaste horas de vida.🌟🖐

    Un abrazo!

  11. Buenas espero puedas contestar esta consulta.

    Hay varias pestañas que hacen la misma función siempre, pero hay pestañas que en su contenido pues como no ha llegado la fecha están vacías. Sin embargo deseo a través de un Query hacer sumas de los elementos comunes y es necesario considerar aun cuando todo este vacío, es posible eso?

    He hecho otras funciones usando el si.error pero ese es otro camino, usando query no doy con la solucion.

    Ejemplo la Semana 1 se vendio:

    A B
    boligrafos 5
    Lapiz 2

    Semana 2

    A B
    Bolígrafos 1

    Semana 3

    (no hay información)

      1. QUE TAL DE NUEVO EXPLICAME COMO COLOCAR LA SINTAXIS PARA QUE NO ME DIGA EL ASUNTO DE #VALOR!

        =query({‘SEMANA 0′!H:J;’SEMANA 1′!H:J;’SEMANA 2′!H:J;’SEMANA 3’!H:J};”Select Col1 WHERE Col1 IS NOT NULL, SUM(Col2) group by (Col1)”)

        RECUERDA QUE PARTE DE LA SEGUNDA MATRIZ Y LA TERCERA NO TIENEN INFORMACION.

  12. Hola buenos días, la verdad es que se agradece tu facilidad para explicar las cosa, estoy intentado consolidar hojas pero al momento de aplicar la formula solo me vacía los datos de la primera hoja, ojalá me pudiera ayudar a resolver.
    Saludos

  13. Hola, aprendi como consolidar hojas simples por tus videos muy útiles pero cuando quiero poner filtro y ordenar de mayor a menor o alfabeticamente la tabla consolidada no lo realiza, evidentemente surge una especie de error
    aplico esta formula simple pero los filtros no ordenan el sonsolidado. gracias espero tu ayuda ={Filter(Ventas!AA:AD,ESTEXTO(Ventas!AA:AA));Filter(Cobros!AA2:AD,ESTEXTO(Cobros!AA2:AA))}

      1. Hola! Muchas gracias.. ya aprendí a consolidar pero, ya lo hice con QUERY y con Filter y aun así no me funciona el filtro para ordenarlo cronológicamente! ¿Sabes si sí se puede ordenar cronológicamente y de ser así, me puedes enseñar cómo hacerlo?
        Mil gracias

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Pin It on Pinterest

Share This