Consolidar múltiples hojas en una sola desde Google Sheets

Consolidar tablas en Google Sheets
— by

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 {} 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 el slash hacia adelante (\).

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 un 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

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

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.

Suscríbete para acceder al curso «Descubriendo el poder de Google Sheets» y empezar tu camino a la maestría de Sheets, Forms y Apps Script

Boletín

Nuestras últimas noticias en tu correo electrónico

Respuestas

  1. Avatar de Gonzalo
    Gonzalo

    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)

    1. Avatar de jsguzmanb
      jsguzmanb

      Usa la clausula ORDER BY Col1 DESC

      Saludos!

  2. Avatar de Javier
    Javier

    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

    1. Avatar de jsguzmanb
      jsguzmanb

      Disculpame por metido, pero la verdad con esa cantidad de datos, deberías migrar a bases SQL o similares
      Y como tu bien dices despues las puedes consultar con Excel o Sheets
      Saludos!

  3. Avatar de Alberto
    Alberto

    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?

    1. Avatar de jsguzmanb
      jsguzmanb

      Aquí te dejo un video que te puede ayudar
      https://youtu.be/YVf1WuF06jA
      Saludos!

  4. Avatar de Jaime
    Jaime

    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

  5. Avatar de Alberto
    Alberto

    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?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Alberto

      Veo que tienes WHERE A»» pero veo el igual

      Por otro lado, no entiendo tu pregunta al final

      Saludos!

  6. Avatar de Gerardo Castro
    Gerardo Castro

    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?

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Gerardo

      Aquí te dejo un video que te puedo ayudar

      https://youtu.be/Ck-DRfZlwpM

      Un gran saludo!

  7. Avatar de Lorena
    Lorena

    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

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Lorena

      Mil gracias por tus palabras!

      cambiale col12 a Col12

      Me cuentas si te sirve

      Un gran saludo!

  8. Avatar de Nohelia Andrea Galindo
    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!

    1. Avatar de jsguzmanb
      jsguzmanb

      Mil gracias por tus amables palabras!

  9. Avatar de Sam
    Sam

    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. Avatar de jsguzmanb
      jsguzmanb

      Hola Sam

      Podrias usar la condicion WHERE Col1 IS NOT NULL

      Me cuentas si te funciona

      Saludos!

      1. Avatar de Sam
        Sam

        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.

  10. Avatar de Fabio
    Fabio

    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

    1. Avatar de jsguzmanb
      jsguzmanb

      Hola Fabio

      Mil gracias!

      Asegúrate que ordenas los datos para que no tengamos celdas vacías entre hoja y hoja

      Un gran saludo!

  11. Avatar de German
    German

    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. Avatar de jsguzmanb
      jsguzmanb

      Hola!

      Para poder filtrar, te sugiero que uses un QUERY desde el encabezado, con eso al filtrar la formula no se te mueve. No se si me explico

      Un gran saludo!

      1. Avatar de Andrea Acuña
        Andrea Acuña

        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

        1. Avatar de jsguzmanb
          jsguzmanb

          Hola Andrea

          Dejame ver como tienes la formula

          Cual es la columna donde esta la fecha

          Saludos!

Deja una respuesta

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