Se você precisa manipular dados no Planilhas Google, a função QUERY pode ajudar! Ele traz uma pesquisa poderosa no estilo de banco de dados para sua planilha, para que você possa pesquisar e filtrar seus dados em qualquer formato que desejar. Orientaremos você sobre como usá-lo.
Índice
Usando a função QUERY
A função QUERY não é muito difícil de dominar se você já interagiu com um banco de dados usando SQL. O formato de uma função QUERY típica é semelhante ao SQL e traz o poder das pesquisas de banco de dados para o Planilhas Google.
O formato de uma fórmula que usa a função QUERY é =QUERY(data, query, headers). Você substitui “dados” por seu intervalo de células (por exemplo, “A2: D12” ou “A: D”) e “consulta” por sua consulta de pesquisa.
O argumento opcional “cabeçalhos” define o número de linhas de cabeçalho a serem incluídas no topo do seu intervalo de dados. Se você tem um cabeçalho que se espalha por duas células, como “Primeiro” em A1 e “Nome” em A2, isso especificaria que QUERY usaria o conteúdo das duas primeiras linhas como o cabeçalho combinado.
No exemplo abaixo, uma folha (chamada “Lista de funcionários”) de uma planilha do Google Sheets inclui uma lista de funcionários. Inclui seus nomes, números de identificação de funcionários, datas de nascimento e se eles participaram da sessão de treinamento obrigatória de funcionários.
Em uma segunda planilha, você pode usar uma fórmula QUERY para obter uma lista de todos os funcionários que não participaram da sessão de treinamento obrigatória. Essa lista incluirá os números de identificação dos funcionários, nomes, sobrenomes e se eles participaram da sessão de treinamento.
Para fazer isso com os dados mostrados acima, você pode digitar =QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'"). Isso consulta os dados do intervalo A2 a E12 na planilha “Lista de funcionários”.
Como uma consulta SQL típica, a função QUERY seleciona as colunas a serem exibidas (SELECT) e identifica os parâmetros para a pesquisa (WHERE). Ele retorna as colunas A, B, C e E, fornecendo uma lista de todas as linhas correspondentes nas quais o valor na coluna E (“Treinamento Assistido”) é uma string de texto contendo “No.”
Conforme mostrado acima, quatro funcionários da lista inicial não participaram de um treinamento. A função QUERY forneceu essas informações, bem como colunas correspondentes para mostrar seus nomes e números de ID de funcionário em uma lista separada.
Este exemplo usa um intervalo de dados muito específico. Você poderia alterar isso para consultar todos os dados nas colunas A a E. Isso permitiria que você continuasse a adicionar novos funcionários à lista. A fórmula QUERY que você usou também será atualizada automaticamente sempre que você adicionar novos funcionários ou quando alguém comparecer à sessão de treinamento.
A fórmula correta para isso é =QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'"). Esta fórmula ignora o título inicial “Funcionários” na célula A1.
Se você adicionar um 11º funcionário que não participou do treinamento à lista inicial, conforme mostrado abaixo (Christine Smith), a fórmula QUERY também é atualizada e exibe o novo funcionário.
Fórmulas QUERY avançadas
A função QUERY é versátil. Ele permite que você use outras operações lógicas (como AND e OR) ou funções do Google (como COUNT) como parte de sua pesquisa. Você também pode usar operadores de comparação (maior que, menor que e assim por diante) para encontrar valores entre duas figuras.
Usando operadores de comparação com QUERY
Você pode usar QUERY com operadores de comparação (como menor que, maior que ou igual a) para restringir e filtrar os dados. Para fazer isso, adicionaremos uma coluna adicional (F) à nossa planilha “Lista de funcionários” com o número de prêmios que cada funcionário ganhou.
Usando o QUERY, podemos pesquisar todos os funcionários que ganharam pelo menos um prêmio. O formato desta fórmula é =QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0").
Isso usa um operador de comparação maior que (>) para pesquisar valores acima de zero na coluna F.
O exemplo acima mostra que a função QUERY retornou uma lista de oito funcionários que ganharam um ou mais prêmios. De um total de 11 funcionários, três nunca ganharam um prêmio.
Usando AND e OR com QUERY
Funções de operador lógico aninhadas como AND e OR funcionam bem em uma fórmula QUERY maior para adicionar vários critérios de pesquisa à sua fórmula.
Uma boa maneira de testar E é pesquisar dados entre duas datas. Se usarmos nosso exemplo de lista de funcionários, poderíamos listar todos os funcionários nascidos de 1980 a 1989.
Isso também tira vantagem dos operadores de comparação, como maior ou igual a (> =) e menor ou igual a (<=).
O formato desta fórmula é =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'"). Isso também usa uma função DATE aninhada adicional para analisar os carimbos de data / hora corretamente e procura todos os aniversários iguais a 1º de janeiro de 1980 e 31 de dezembro de 1989.
Conforme mostrado acima, três funcionários nascidos em 1980, 1986 e 1983 atendem a esses requisitos.
Você também pode usar OR para produzir resultados semelhantes. Se usarmos os mesmos dados, mas trocarmos as datas e usarmos OR, podemos excluir todos os funcionários nascidos na década de 1980.
O formato dessa fórmula seria =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'").
Dos 10 funcionários originais, três nasceram na década de 1980. O exemplo acima mostra os sete restantes, que nasceram antes ou depois das datas que excluímos.
Usando COUNT com QUERY
Em vez de simplesmente pesquisar e retornar dados, você também pode misturar QUERY com outras funções, como COUNT, para manipular dados. Digamos que queremos eliminar um número de todos os funcionários de nossa lista que participaram e não participaram do treinamento obrigatório.
Para fazer isso, você pode combinar QUERY com COUNT assim =QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E").
Focando na coluna E (“Treinamento Assistido”), a função QUERY usou COUNT para contar o número de vezes que cada tipo de valor (um “Sim” ou uma sequência de texto “Não”) foi encontrado. Da nossa lista, seis funcionários concluíram o treinamento e quatro não.
Você pode facilmente alterar essa fórmula e usá-la com outros tipos de funções do Google, como SUM.
