Combinar QUERY com IMPORTRANGE no Planilhas Google

A combinação de QUERY com IMPORTRANGE permite extrair dados de diferentes planilhas e realizar consultas avançadas sobre eles. Se estiver gerenciando o progresso do projeto, dados financeiros ou esforços de equipe, o domínio dessas funções pode aumentar sua produtividade e o gerenciamento de dados.

IMPORTRANGE é uma das funções mais úteis do Google Sheets. Como o nome sugere, ela importa um intervalo de células de uma planilha para outra.

Por outro lado, QUERY é uma fórmula incrivelmente versátil, que essencialmente permite usar a planilha como um banco de dados. Com uma linguagem de consulta semelhante à do SQL, você pode procurar, somar, contar, calcular a média, filtrar e classificar dados.

A seguir, você encontra a sintaxe, cinco exemplos e uma configuração passo a passo em dois arquivos reais, incluindo como permitir o acesso e como agrupar e ordenar os dados importados.

Como usar QUERY com IMPORTRANGE no Planilhas Google

Essa combinação aplica o QUERY diretamente aos dados transferidos com o IMPORTRANGE para filtrar colunas, linhas e intervalos específicos.

É uma verdadeira economia de tempo ao trabalhar com várias planilhas, pois você extrai apenas os dados exatos de que precisa. Ele também permite que você formate os dados dentro da consulta em vez de depois, o que economiza uma etapa adicional.

Sintaxe

• URL da planilha - o link para a planilha de onde os dados são importados.
• string_de_intervalo - intervalo de células a ser importado. Isso pode ser dividido em dois componentes: 
  • nome_da_planilha - o nome da planilha com os dados desejados 
  • cell_range - o intervalo de células dentro dessa planilha
• consulta - critérios de quais dados devem ser transferidos. A consulta lida com funções de linguagem (Selecionar, Agrupar por, etc.) e funções de manipulação de dados (Média, Soma, Contagem e Multiplicação).

Como essa combinação funciona?

Sempre que você combina funções, as coisas podem ficar um pouco confusas. Para evitar isso, vamos dar uma olhada no que está acontecendo nessa sintaxe.

Primeiro, vamos nos concentrar na QUERY. Essa função pode manipular dados de várias maneiras, mas sempre precisa começar com uma fonte de dados.

Em muitos casos, são dados de outra guia na mesma planilha. Mas, como você sabe, este artigo trata da importação de dados de outra planilha. Como resultado, usaremos a função IMPORTRANGE para alimentar os dados no QUERY.

IMPORTRANGE é uma função simples: basta inserir o link da planilha e o intervalo que você quer importar. Na primeira vez que você conecta dois arquivos, ela mostra um erro #REF! até que você permita o acesso. A configuração passo a passo mais abaixo mostra onde clicar.

Esses dados importados podem ser inseridos na função QUERY. Depois disso, tudo o que você precisa inserir são os parâmetros de consulta, que determinam como os dados serão manipulados e exibidos. Os exemplos abaixo ilustram isso.

Lembre-se de que a estrutura da sintaxe permanece semelhante, independentemente da função que você aninhar dentro da função QUERY do Planilhas Google.

Exemplo 1: Importação e consulta

Nos exemplos a seguir, usarei esses dados geográficos como dados de origem:

Primeiro, importo dados de 186 países para uma planilha comercial. Mesclo itens de um conjunto de dados separado com base em determinados critérios.

Digamos que eu queira importar apenas os dados dos países das Américas. Para exibir apenas essas células específicas, basta aplicar a função Selecione e Onde dentro da função QUERY.

Nessa fórmula, o QUERY pesquisa dados de um intervalo/matriz predefinido de acordo com critérios específicos. Ao mesmo tempo, o IMPORTRANGE importa os dados para a planilha de destino.

Colocar QUERY antes de IMPORTRANGE permite que você pesquise (consulte) informações específicas dos dados que são importados com IMPORTRANGE. Dessa forma, você importa apenas os dados que deseja ver em sua planilha.

Depois de IMPORTRANGE, você precisa adicionar critérios de pesquisa. Para importar dados de países das Américas, usamos esta consulta: Selecionar * onde Col1=’Americas’. Em linguagem simples, isso se traduz em “selecionar todos os dados que tenham “Américas” na coluna 1”.

Essa fórmula é especialmente útil em situações em que você deseja mesclar dados. Se você trabalha com marketing on-line, por exemplo, talvez queira reunir o tráfego de várias fontes em uma única planilha. Ela lhe dá uma visão geral melhor de várias fontes de dados, como Google Analytics, Ahrefs, SEMrush ou Google Search Console.

Exemplo 2: Importar dados em um intervalo de datas

Neste próximo exemplo, adicionei critérios de pesquisa após IMPORTRANGE. Nesse caso, where Col3 > date ‘2019-07-01’ and Col3 < date ‘2019-12-30’ importa os mesmos dados, mas apenas para os itens da lista que foram publicados entre essas datas.

Observe que, ao inserir uma data em sua consulta, você precisa digitar a palavra date antes dela. Além disso, não se esqueça de colocar a data entre aspas. É assim que a função sabe que você está se referindo a uma data.

Você pode aplicar essa fórmula em logística ou até mesmo em uma caixa registradora de uma pequena empresa. Você pode somar linhas, colunas e intervalos de dados com alterações nos dados ao longo do tempo. Se você estiver interessado em mudanças nas linhas de produtos, vendas e outros KPIs ao longo do ano, essa fórmula pode realizar análises comparativas mensais ou trimestrais.

Exemplo 3: Importar e combinar várias planilhas com critérios específicos

Se quiser extrair determinadas células ou um intervalo de várias planilhas, use a seguinte fórmula. Veja como mesclar dados de várias planilhas em uma só:

Você precisa usar IMPORTRANGE para cada planilha que deseja mesclar. Na primeira fórmula IMPORTRANGE, adicionei os cabeçalhos em A1:P.

As outras planilhas também contêm os cabeçalhos. Entretanto, eles não devem ser importados várias vezes. Para evitar a importação do cabeçalho três vezes, alterei o intervalo para A2:P na segunda e terceira planilhas.

Por fim, a cláusula Where (where Col1’’) simplesmente declara que todas as células que não estiverem em branco devem ser importadas.

Aqui estão algumas dicas para a importação de várias planilhas:

• A lista de importações precisa ser colocada entre colchetes e um ponto e vírgula deve separar cada IMPORTRANGE.
• Todas as planilhas importadas devem ter o mesmo layout. Por exemplo, se a primeira coluna de todas as planilhas não fosse Country Name, os dados não se combinariam adequadamente.
• Não importe os cabeçalhos de todas as planilhas - você só precisa de um.
• Lembre-se de permitir o acesso a todas as planilhas das quais deseja importar.

Essa fórmula é muito útil para pesquisas de negócios (quando você coleta várias planilhas com o mesmo formato). Da mesma forma, para fins educacionais. Quando você distribui exercícios semelhantes aos alunos ou quando coleta notas de vários professores.

Exemplo 4: Importar e consultar valores médios

Com essa fórmula, importo a população média de cada continente. Como você pode ver, adiciono “avg” após a cláusula ‘Select’ e coloco a coluna entre parênteses. Em seguida, adicione a consulta (os dados que você está procurando) após a cláusula Where. Nesse caso, a consulta é ‘Europe’.

Observe como no restante dessa planilha eu uso várias funções QUERY/IMPORTRANGE para obter populações médias e PIBs totais para diferentes regiões. Como a QUERY é altamente personalizável, você pode obter todos os tipos de resultados diferentes, dependendo do seu objetivo!

A função QUERY permite que você extraia todas as funções aritméticas. Essa é uma ferramenta muito útil para comerciantes e analistas financeiros. Com ela, é possível consultar valores de várias planilhas de investimento e realizar cálculos como Valor Presente =PV, Valor Futuro =FV e funções de taxa de juros como =IRR e =RATE.

Além disso: é uma versão mais intuitiva e em linguagem natural do complexo INDEX-MATCH fórmulas no Excel.

Exemplo 5: Importar e consultar valores de soma

Neste exemplo final, a fórmula filtra por continente, soma o PIB de cada país e importa dados de uma planilha separada.

Isso é especialmente útil para criar painéis e relatórios de todos os tipos.

Como configurar QUERY com IMPORTRANGE entre dois arquivos

Os exemplos acima usam um marcador de posição no lugar do URL da planilha. Aqui está a configuração completa em um par de arquivos reais, com o URL e o intervalo em células próprias para que a fórmula fique curta.

O exemplo abaixo se baseia em dois arquivos. O arquivo de origem contém dados de vendas de todos os vendedores e produtos. O arquivo de destino é onde quero importar e analisar os dados com base em condições específicas.

Copie o URL de origem e o intervalo de células

1. Abra os arquivos de origem e de destino no Google Sheets.

2. Copie o URL do arquivo de origem.

3. Cole o URL no arquivo de destino.

4. Volte ao arquivo de origem e selecione o intervalo de células. Você pode copiar a referência do intervalo no canto superior esquerdo, como mostrado abaixo.

5. Cole a referência do intervalo de células no arquivo de destino. Não preciso especificar a página porque ela está em "Sheet1".

Adicione a função QUERY

Vou usar uma consulta simples com as cláusulas 'select' e 'where' para importar as linhas em que "sales_team" é 'Southteam'.

1. Comece a digitar a fórmula de QUERY.

2. No argumento 'data', adicione IMPORTRANGE e faça referência às células que contêm os argumentos dela, B1 e B2.

3. No argumento 'query', vou usar "Select * Where Col5='Southteam'". 'Col5' é a coluna "sales_team", que deve corresponder a "Southteam".

4. Pressione Enter para ver os resultados.

5. Você pode adicionar facilmente outra condição com o operador 'OR'. Por exemplo, posso importar os resultados de duas equipes diferentes ajustando a consulta como mostrado abaixo.

6. Como você pode ver na captura de tela abaixo, os resultados agora também incluem os dados de 'Westteam'.

Permita o acesso entre os arquivos

Se você estiver trabalhando com arquivos que nunca foram conectados, verá uma mensagem de erro em vez do valor ao pressionar Enter. Para ver o valor real, você precisará aprovar o acesso entre as planilhas. Clique na célula com o erro #REF! e depois em Permitir acesso. Você só precisa fazer isso uma vez para cada par de arquivos.

Como agrupar e ordenar dados importados com QUERY

Para simplificar, vou usar os dados da seção anterior. Na minha análise, quero ver o total de vendas por equipe e também o total de vendas por vendedor.

Agrupar por equipe de vendas

Para agrupar os resultados, preciso selecionar a coluna 'sales_team' (5) e a soma da coluna 'sales_amount' e, depois, agrupar por 'sales_team'.

A consulta fica assim: "Select Col5,sum(Col2) Group by Col5".

Pressione "Enter" para ver os resultados.

Agrupar por vendedor

Como o nome do vendedor está na coluna 3, preciso modificar a consulta e substituir as referências a 'Col5' por 'Col3'.

Pressione "Enter" para ver os resultados.

Ordenar por valor de vendas

Como você pode ver na captura de tela anterior, a função ordena automaticamente a coluna em ordem alfabética pelo nome do vendedor. No entanto, quero ver os resultados por valor de vendas, em ordem decrescente.

Posso modificar a consulta anterior adicionando "Order by sum(Col2) DESC" no final.

Pressione Enter para ver os resultados ordenados por valor de vendas.

Obtenha os mesmos resultados com as conexões Sheetgo

O Sheetgo oferece uma alternativa intuitiva e poderosa à combinação de QUERY e IMPORTRANGE no Google Sheets. Com o Sheetgo, você pode importar e processar dados automaticamente, para que os dados das suas planilhas fiquem prontos para relatórios, dashboards e IA.

Coincidentemente, um dos principais recursos de processamento de dados do Sheetgo oferece o filtro QUERY. Esse recurso permite que você realize consultas avançadas de dados semelhantes à função QUERY do Google Sheets. Você pode filtrar, classificar e manipular dados à medida que eles fluem entre as planilhas, tudo isso sem a necessidade de fórmulas complexas.

Por que usar o Sheetgo em vez do IMPORTRANGE? Aqui estão apenas três motivos:

• Gerenciamento de dados aprimorado: Ao contrário do IMPORTRANGE, o Sheetgo oferece um painel centralizado no qual você pode gerenciar todas as suas conexões de dados.
• Escalabilidade: O Sheetgo pode lidar com grandes conjuntos de dados e várias conexões sem os problemas de desempenho que o IMPORTRANGE pode encontrar. Isso o torna ideal para empresas com requisitos de dados complexos.
• Filtragem avançada: Com o filtro QUERY do Sheetgo, você pode realizar manipulações sofisticadas de dados diretamente em seus fluxos de trabalho. Isso permite recursos de processamento de dados mais avançados em comparação com o IMPORTRANGE.

Descubra todos os motivos Por que o Sheetgo é a melhor alternativa ao IMPORTRANGE.

Conclusão

Agora você já sabe como usar QUERY com IMPORTRANGE, desde permitir o acesso entre arquivos até agrupar e ordenar os dados importados. Agora você pode unir o poder dessas duas funções. Para outra ótima combinação, confira Como usar VLOOKUP e IMPORTRANGE.

Se o seu conjunto de dados for grande demais para QUERY+IMPORTRANGE, veja: Alternativa ao IMPORTRANGE sem fórmula — ela mostra como filtrar e conectar planilhas do Google Sheets em grande escala sem travar.