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.
últimas postagens
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 (dados, consulta, cabeçalhos). 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ê tiver 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 (‘Lista de funcionários’! A2: E12, “SELECIONE A, B, C, E ONDE E = ‘Não’”). 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 da 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 (‘Lista de funcionários’! A2: E, “Selecione A, B, C, E ONDE E = ‘Não’”). 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 QUERY, podemos pesquisar todos os funcionários que ganharam pelo menos um prêmio. O formato desta fórmula é = QUERY (‘Lista de Staff’! A2: F12, “SELECIONE 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
As funções de operador lógico aninhado como AND e OR funcionam bem dentro de 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 de operadores de comparação, como maior ou igual a (> =) e menor ou igual a (
O formato desta fórmula é = QUERY (‘Lista de funcionários’! A2: E12, “SELECIONE A, B, C, D, E ONDE D> = DATA ‘1980-1-1’ e D
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 OU, podemos excluir todos os funcionários que nasceram na década de 1980.
O formato para esta fórmula seria = QUERY (‘Lista de funcionários’! A2: E12, “SELECIONE A, B, C, D, E ONDE D> = DATA ‘1989-12-31’ ou D
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 da sessão de treinamento obrigatória.
Para fazer isso, você pode combinar QUERY com COUNT assim = QUERY (‘Lista de funcionários’! 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.