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.
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)
Usa la clausula ORDER BY Col1 DESC
Saludos!
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
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!
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?
Aquí te dejo un video que te puede ayudar
https://youtu.be/YVf1WuF06jA
Saludos!
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
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?
Hola Alberto
Veo que tienes WHERE A»» pero veo el igual
Por otro lado, no entiendo tu pregunta al final
Saludos!
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?
Hola Gerardo
Aquí te dejo un video que te puedo ayudar
https://youtu.be/Ck-DRfZlwpM
Un gran saludo!
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
Hola Lorena
Mil gracias por tus palabras!
cambiale col12 a Col12
Me cuentas si te sirve
Un gran saludo!
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!
Mil gracias por tus amables palabras!
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)
Hola Sam
Podrias usar la condicion WHERE Col1 IS NOT NULL
Me cuentas si te funciona
Saludos!
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.
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
Hola Fabio
Mil gracias!
Asegúrate que ordenas los datos para que no tengamos celdas vacías entre hoja y hoja
Un gran saludo!
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))}
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!
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
Hola Andrea
Dejame ver como tienes la formula
Cual es la columna donde esta la fecha
Saludos!