Como usar a função QUERY do Google Sheets

O Planilhas Google QUERY é uma ferramenta muito poderosa e versátil. Tanto que você pode chamá-la de "one-stop-shop" para todos os seus requisitos lógicos, de pesquisa, de soma, de contagem, de média, de filtragem e de classificação. A função busca informações específicas de um conjunto de dados por meio de uma instrução de consulta, da mesma forma que busca conjuntos de resultados de um banco de dados usando consultas. A sintaxe da função linguagem de consulta usado nessa função é semelhante ao SQL.

Sintaxe

• dados - é a referência ao intervalo de células sobre o qual queremos fazer a consulta.
• consulta - é o texto com o qual a função QUERY produz as informações que estamos procurando no conjunto de dados. Como se espera que seja uma cadeia de caracteres, ela deve ser colocada entre aspas. Ou também pode ser uma referência a uma célula, onde o texto da consulta está armazenado.
• cabeçalhos - é um parâmetro opcional que indica o número de linhas de cabeçalho na parte superior dos dados. Se não for informado, o Planilhas Google adivinha o valor com base no conteúdo do campo dados.

Como usar a QUERY no Google Sheets

Para começar a usar a função QUERY, vamos partir de uma consulta simples que seleciona todas as colunas do conjunto de dados. Neste exemplo, vou escrever a função na planilha que contém os dados. Mas você também pode consultar com a mesma facilidade uma página de outro arquivo de planilha. Para saber mais, leia este artigo sobre QUERY com IMPORTRANGE no Google Sheets.

1. Dê um nome ao intervalo que contém os seus dados para fazer referência a ele com mais facilidade.

2. Digite a função QUERY na célula onde você quer os resultados.

3. Selecione o intervalo nomeado ou as células com os dados como o parâmetro 'data' (dados). Neste caso, vou simplesmente digitar o nome do intervalo.

4. Digite a consulta que você quer usar. Neste caso, vou selecionar todas as colunas com "SELECT *". Como estou digitando diretamente na fórmula, preciso colocar a consulta entre aspas.

5. Como os cabeçalhos ocupam só uma linha, vou pular o último parâmetro. Feche o parêntese e pressione 'Enter'.

Cláusulas e operadores

A linguagem de consulta usada na função QUERY do Google Sheets é uma linguagem baseada em texto semelhante ao SQL. A consulta usa cláusulas para executar ações. Abaixo está uma lista das cláusulas possíveis que você pode usar para executar uma ampla variedade de ações em seus dados.

Se você usa muito a função QUERY, verá que também faz uso de vários operadores lógicos para definir condições dentro da função. Abaixo está uma lista de operadores lógicos que você pode usar.

As palavras-chave das cláusulas não diferenciam maiúsculas de minúsculas, mas as letras das colunas precisam estar em maiúsculas. Quando uma consulta usa várias cláusulas, elas precisam aparecer nesta ordem: SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT, OPTIONS.

Funções básicas de QUERY

Em primeiro lugar, para entender como a função é usada, considere os seguintes dados de amostra. Ele consiste em informações correspondentes a uma lista de alunos que se inscreveram em vários cursos em uma universidade. Em todos os próximos exemplos, inserirei a função na célula H1. Ela também será exibida na barra de funções nos snapshots.

Exemplo #1: Selecionar colunas com base em uma condição

Começarei com uma demonstração bem básica e aumentarei a complexidade ao longo do artigo para ajudá-lo a entender a função passo a passo. Para começar, usarei a função QUERY para buscar os nomes dos alunos que estão residindo no campus.

Antes de prosseguir, examinarei alguns dos conceitos básicos do QUERY neste primeiro exemplo. Para começar, observe que os dados selecionados incluem os cabeçalhos - eles serão usados como parte do resultado da função. Você pode ver que a função retornou não apenas os nomes dos alunos que residem no campus, mas também o cabeçalho Name, que é muito útil para acompanhar o significado dos resultados!

Depois de selecionar os dados, a parte principal da consulta está entre aspas como um comando de cadeia de texto. Usei duas das cláusulas listadas no gráfico acima: Select e Where. O comando ‘select’ é usado para retornar colunas - dizer à função para “Select A” leva a uma saída da coluna A inteira.

A cláusula ‘where’ permite que você adicione uma condição. Aqui, adicionei a condição de que a coluna F deve ser igual a “Yes” (Sim). Para as linhas em que a coluna F é “No” (Não), a função ignora esses dados e não os inclui na saída.

Combinando as duas cláusulas em uma única consulta, digitei “Select A where F = 'Yes'". Essencialmente, isso está dizendo à função para retornar todos os nomes (da coluna A) dos alunos que moram no campus (os dados na coluna F são 'Sim').

Dica: se estiver selecionando todas as colunas do conjunto de dados, você pode digitar Select * para fazer isso rapidamente, em vez de listar cada coluna!

Exemplo #2: Selecionar colunas com base em uma condição

Agora vou mostrar o mesmo exemplo, mas encontrando os alunos que NÃO estão no campus.

A única diferença aqui é que eu mudei o operador lógico na cláusula ‘where’ para <>, que significa “não igual”. Portanto, estou pedindo que a função me forneça a lista de nomes de alunos que NÃO têm um ‘Sim’ na coluna F.

Exemplo #3: Selecionar várias colunas

Agora vou buscar os nomes, idades e departamentos dos alunos que fizeram mais de 7 cursos.

Novamente, começo com a cláusula Select, mas, desta vez, seleciono várias colunas listando-as separadas por vírgulas. Escolhi as colunas A, B e C para serem selecionadas. Também alterei a cláusula Where para filtrar os dados dos alunos que têm menos de 7 cursos.

Exemplo #4: Múltiplas condições WHERE

Agora vou dar um passo adiante. Mostrarei os nomes, os departamentos e as datas de ingresso dos alunos com 25 anos ou menos que ingressaram na universidade entre 25 de dezembro de 2016 e 20 de janeiro de 2017. Observe que, no texto da consulta, as datas sempre devem estar no formato aaaa-mm-dd, entre aspas simples.

Como você pode ver, é possível adicionar várias condições Where simplesmente separando-as com “and”. Isso permite que você filtre os dados de qualquer maneira que possa imaginar!

Exemplo #5: Células de referência

E se você precisar fazer referência à data de uma célula? Não há problema! Você pode fazer isso com a ajuda de operadores de concatenação e uma função de texto. Ao fazer isso no exemplo abaixo, você obterá os nomes e as datas de ingresso dos alunos que ingressaram após 1º de janeiro de 2017.

Um aspecto importante a ser observado ao usar datas em sua consulta é que as datas só podem estar no formato aaaa-mm-dd e devem ser colocadas entre aspas simples e precedidas pela string “date”. Portanto, para indicar 1º de janeiro de 2020 em sua consulta, você precisaria digitar: date ‘2020-01-01’.

Funções aritméticas e de agregação

Além das várias cláusulas listadas acima, você pode usar funções aritméticas regulares em suas consultas. Operações como adição, subtração, multiplicação e divisão, bem como as funções de agregação soma, média, contagem, máximo e mínimo, são todas válidas.

Exemplo nº 6: multiplicação e adição

Vou começar demonstrando algumas funções aritméticas simples: multiplicação e adição. Neste exemplo, quero que a idade seja em meses em vez de anos (portanto, multiplicarei por 12) e quero adicionar um crédito para cada aluno.

Como você pode ver, tudo o que você precisa fazer é executar a operação na coluna de sua escolha. O resultado não é muito bonito, mas discutiremos como alterar os cabeçalhos e a formatação mais adiante neste guia.

Exemplo #7: Média

Agora mostrarei uma operação de agregação - a função média. A média de uma coluna é acessada com a abreviação ‘avg’. Você também precisa informar à função quais itens devem ser agregados à média, ou seja, como você deseja agrupar os dados. É por isso que eu também disse à função para agrupar os dados pela coluna C (dept), usando a cláusula Group by.

Você já viu a função QUERY usar funções aritméticas básicas, como multiplicação e adição, bem como operações de agregação, como a média.

Funções complexas de QUERY

Agora que você já viu alguns exemplos básicos usando as cláusulas Select e Where para filtrar um conjunto de dados, bem como algumas funções aritméticas, mostrarei alguns exemplos mais complexos usando uma variedade de cláusulas e operações.

Exemplo #8: Selecionar, somar e agrupar por

Você pode usar uma combinação de Select, Sum e Group By para listar todos os departamentos e exibir o número de cursos feitos em cada departamento. A cláusula Group By é usada junto com funções de agregação (como Sum) para informar à função como os dados devem ser agrupados e somados - caso contrário, a função de agregação não funcionará.

Exemplo #9: Rotular e classificar

No exemplo anterior, você notará que a função QUERY retornou a segunda coluna com o cabeçalho “sum Courses”. Honestamente, isso é um pouco estranho - felizmente, você pode corrigir isso renomeando-a. Além disso, também usarei a segunda coluna (agora renomeada para ‘Cursos realizados’) para classificar em ordem crescente. Veja como fazer isso.

Exemplo #10: Contagem

Você pode exibir o número de instâncias em que o aluno está fazendo cursos de cada departamento? Claro que sim! A função QUERY também oferece essa opção - você pode usar o operador de contagem para fazer isso.

Em vez de usar o operador Sum (soma) para somar todos os cursos realizados, estou usando o operador Count (contagem) para contar o número de ocorrências de um aluno que cursou qualquer número de créditos em cada departamento. Também estou usando novamente as cláusulas Select, Group by e Label para obter o resultado desejado.

Exemplo #11: Limite e pedido por

A cláusula Limit limita os resultados a um número específico. Por exemplo, na captura de tela abaixo, limito a saída a 10 linhas. Essa cláusula é frequentemente usada em conjunto com a cláusula Order by, que coloca os dados em ordem ascendente ou descendente.

Aqui, usei a cláusula Order by para classificar os dados da idade mais baixa para a mais alta e, em seguida, usei a cláusula Limit para limitar o resultado a 10, o que me deu os 10 alunos mais jovens.

Exemplo #12: Pivô

A cláusula Pivot permite essencialmente que você crie suas próprias tabelas dinâmicas usando a função QUERY. No exemplo abaixo, peguei um exemplo anterior e o dinamizei pela coluna A. O resultado é que os nomes dos alunos agora são os cabeçalhos, com os dados que selecionei nas linhas abaixo de cada nome.

O uso da cláusula Pivot é um uso um pouco mais avançado das consultas, mas se você brincar com ela, descobrirá os muitos usos que ela tem!

Uso de ‘OR’ em consultas

Até agora, você viu muitas consultas que usam ‘and’ para acrescentar critérios adicionais à consulta. Mas você também pode usar ‘or’, assim como em outras funções do Planilhas Google, como as instruções IF.

Exemplo #13: Uso de OR em uma consulta

Este exemplo é uma réplica de um exemplo anterior, mas, em vez de especificar que a idade precisa ser 25 anos ou menos E a data de união deve estar entre as datas especificadas, estou especificando que qualquer ponto de dados que atenda a QUALQUER um desses critérios passará pelo filtro e aparecerá no resultado.

Cabeçalhos

Até agora, todos os exemplos foram feitos com uma única linha de cabeçalhos nos dados e um ‘1’ no parâmetro de cabeçalho dentro da função QUERY. Agora, mostrarei o que fazer se você tiver cabeçalhos que abrangem várias linhas.

Exemplo #14: Várias linhas de cabeçalhos

O cabeçalho é uma entrada opcional que é útil quando os cabeçalhos se estendem por várias linhas. Nesses casos, esse parâmetro o ajuda a combinar facilmente os cabeçalhos em uma única linha, como mostrado abaixo.

Tudo o que você precisa fazer é colocar um ‘2’ como o cabeçalho após a consulta! Independentemente do número de linhas que seus cabeçalhos abrangem, basta colocar esse número no parâmetro cabeçalho parâmetro - é tão simples quanto isso!

Consultar várias guias ou planilhas

O que acontece quando seus dados estão espalhados em várias guias da planilha ou até mesmo em uma planilha diferente? Você ainda pode usar a função QUERY nessas várias guias e planilhas!

Exemplo #15: Consulta em guias ou planilhas

Neste exemplo, você pode ver como usar a função QUERY em várias guias. A chave é ter os dados no mesmo formato em todas as planilhas - portanto, neste exemplo, as idades precisam estar na segunda coluna em ambas as planilhas. Em seguida, o formato muda um pouco - em vez de se referir às colunas pela letra (coluna A, B, etc.), você precisará se referir a elas pelo número (Col1, Col2, etc.).

Por fim, você também precisará colocar os intervalos de dados entre chaves {} e separá-los com um ponto e vírgula. As diferentes guias ou planilhas são identificadas com o nome da planilha seguido de um ponto de exclamação e, em seguida, o intervalo de células dentro da planilha. Tudo isso faz mais sentido quando você dá uma olhada no exemplo abaixo!

Exemplos de QUERY no Google Sheets com um conjunto de dados de vendas

Para ficar mais claro, todos os exemplos abaixo usam o mesmo conjunto de dados. Como algumas cláusulas são bem longas, vou digitá-las em células para poder usar as referências de célula dentro da função. Isso facilita as coisas, porque você não precisa se preocupar com as aspas, e ajuda a evitar erros.

Para ver mais casos reais, leia nosso guia para dominar a fórmula QUERY.

Use SELECT para escolher colunas específicas

Neste exemplo, quero que a QUERY retorne colunas específicas em vez de todas. Depois da palavra-chave SELECT, digite em maiúsculas as letras das colunas que você quer, separadas por vírgulas.

1. Em uma célula vazia, digite a consulta que seleciona as colunas com o nome, o sobrenome e o endereço de e-mail.

2. Na célula onde você quer os resultados, digite a função QUERY e adicione o intervalo como primeiro parâmetro.

3. Selecione a célula com a consulta como segundo parâmetro.

4. Pule o terceiro parâmetro e feche o parêntese.

5. Pressione "Enter" para ver os resultados.

Use a cláusula WHERE para filtrar por valor

Neste exemplo, vou selecionar o sobrenome do cliente, o valor e o agente de vendas. Mas só quero os registros do Agente 1, então vou precisar usar SELECT e WHERE. Se você só precisa filtrar linhas, há outra alternativa: Função FILTER , uma opção mais simples.

1. Em uma célula vazia, digite as cláusulas SELECT e WHERE para indicar a condição que precisa ser atendida. Neste caso, quero filtrar os resultados para ver as transações do Agente 1.

2. Na célula onde você quer os resultados, digite a função QUERY e adicione o intervalo como primeiro parâmetro.

3. Selecione a célula com a consulta como segundo parâmetro.

4. Pule o terceiro parâmetro e feche o parêntese.

5. Pressione "Enter" para ver os resultados.

Use a cláusula GROUP BY para agregar resultados

Neste exemplo, quero ver o total de vendas de cada agente. Ou seja, quero agrupar as transações por agente e obter a soma de cada um. Para isso, preciso das cláusulas SELECT e GROUP BY, além da função SUM.

1. Primeiro, digite a consulta para selecionar com SELECT as colunas F e G. Mas, na verdade, quero a soma de F, então vou envolver a coluna F com a função SUM. Adicione a cláusula GROUP BY no final para que os valores sejam somados por agente de vendas.

2. Na célula onde você quer os resultados, digite a função QUERY e adicione o intervalo como primeiro parâmetro.

3. Selecione a célula com a consulta como segundo parâmetro.

4. Pule o terceiro parâmetro e feche o parêntese.

5. Pressione "Enter" para ver os resultados.

Use a cláusula ORDER BY para ordenar os resultados

Vamos adicionar uma cláusula ORDER BY à consulta anterior para ordenar os resultados pela soma em ordem decrescente. Para ordenar um intervalo sem escrever uma consulta, há outra opção: Função SORT.

1. Copie a cláusula da consulta do exemplo anterior e adicione a cláusula ORDER BY como mostrado abaixo.

2. Na célula onde você quer os resultados, digite a função QUERY e adicione o intervalo como primeiro parâmetro.

3. Selecione a célula com a consulta como segundo parâmetro. Pule o terceiro parâmetro e feche o parêntese.

4. Pressione "Enter" para ver os resultados.

Use a cláusula LIMIT para restringir o número de resultados

Para limitar o número de resultados retornados, você pode usar a cláusula LIMIT seguida do número de resultados que quer. Por exemplo, vamos adicionar uma cláusula à consulta anterior para limitar os resultados aos 2 melhores agentes.

1. Copie a consulta da seção anterior e adicione a cláusula para limitar os resultados a 2.

2. Na célula onde você quer os resultados, digite a função QUERY e adicione o intervalo como primeiro parâmetro.

3. Selecione a célula com a consulta como segundo parâmetro. Pule o terceiro parâmetro e feche o parêntese.

4. Pressione "Enter" para ver os resultados.

Use a cláusula LABEL para renomear colunas

Por fim, vou adicionar à consulta outra cláusula que renomeia as colunas retornadas: LABEL.

1. Copie a fórmula da seção anterior e adicione a cláusula para renomear as colunas "sum amount" e "sales_agent" como 'Total Sales' e 'Sales Agent'.

2. Na célula onde você quer os resultados, digite a função QUERY e adicione o intervalo como primeiro parâmetro.

3. Selecione a célula com a consulta como segundo parâmetro. Pule o terceiro parâmetro e feche o parêntese.

4. Pressione "Enter" para ver os resultados.

Sem dúvida, essa é uma das funções mais complexas a serem dominadas no Planilhas Google. Pode valer a pena dedicar seu tempo para dominar bem essa função, pois ela é, sem dúvida, uma das ferramentas mais potentes do Planilhas Google. Incentivamos você a explorar mais a QUERY função aqui.

Não deixe de conferir esta relevante postagem do blog sobre Como criar tabelas dinâmicas no Planilhas Google.