O Planilhas Google PESQUISA é 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.
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.
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.
=QUERY(A1:F15, ”Select A where F = ‘Yes'”,1)
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.
=QUERY(A1:F15, ”Select A where F ‘Yes'”,1)
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.
=QUERY(A1:F15, ”Select A, B, C where D > 7″,1)
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.
=QUERY(A1:F15, ”Select A, C, E where B = data ‘2016-12-25’ and E <= date = ‘2017-01-20′” ,1)
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.
=QUERY(A1:F15, ”Select A, E where E > date ‘” & TEXT(I1, ”yyyy-mm-dd”)&”‘”,1)
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 #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.
=QUERY(A1:F15, ”Select C, (B*12), (D+1)”,1)
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.
=QUERY(A1:F15, ”Select C, avg(B) group by C”, 1)
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á.
=QUERY(A1:F15, ”Select C, sum(D) group by C”, 1)
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.
=QUERY(A1:F15, ”Select C, sum(D) group by C order by sum(D) label sum(D) ‘Courses Taken'”, 1)
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.
=QUERY(A1:F15, ”Select C, count(D) group by C label count(D) ‘# Instances'”, 1)
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.
=QUERY(A1:F15, ”Select A, B order by B asc limit 10″, 1)
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.
=QUERY(A1:F15, ”Select C, sum(D) group by C pivot A”, 1)
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.
=QUERY(A1:F15, ”Select A, C, E where B = data ‘2016-12-25’ and E <= data = ‘2017-01-20’ ,1)
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.
=QUERY(A1:F16, ”Select A, B, C, D where F “No””, 2)
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!
=QUERY({Sheet4!A1:F7;Sheet3!A1:F9}, “Select Col1”)
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 PESQUISA função aqui.
Não deixe de conferir esta relevante postagem do blog sobre Como criar tabelas dinâmicas no Planilhas Google.
Nota do editor: Esta é uma versão revisada de uma postagem anterior que foi atualizada para maior precisão e abrangência.
English 
Español 
Português do Brasil 
Français