Como usar consultas avançadas no Excel

Embora as ferramentas tecnológicas ofereçam uma ampla variedade de soluções de gerenciamento de dados, a consolidação de informações de diversas fontes continua sendo um desafio. Entender planilhas de dados díspares geralmente depende de esforço manual.

É aqui que a consulta avançada pode ajudar, reunindo dados de várias origens em uma visão integrada.

Como consultor de marketing, trabalho com várias equipes nos negócios de um cliente. Para ter uma ideia real do que está acontecendo com o funil de receita de uma empresa de SaaS, por exemplo, normalmente preciso de dados de marketing, vendas, sucesso do cliente e produto.

No entanto, os dados de que preciso normalmente são coletados em vários locais e em diferentes formatos. Juntar tudo significa colocar tudo em um só lugar, no mesmo formato e poder ser manipulado da maneira que for necessária.

Portanto, a consulta avançada tem sido muito útil para mim ao longo dos anos e não é tão complicada quanto você imagina.

Com a consulta avançada, você pode importar, limpar, transformar e mesclar conjuntos de dados de várias fontes. Depois de saber como usá-lo, coletar e interpretar dados de diversas fontes se tornará muito mais fácil.

O que é uma consulta avançada?

A consulta avançada é essencialmente uma tecnologia que você pode usar no Excel para conectar conjuntos de dados em uma planilha do Excel. Você pode usar a consulta avançada para extrair dados de diversas fontes, incluindo páginas da web, bancos de dados, outras planilhas e vários tipos de arquivo.

Depois que os dados forem importados, você também pode usar o editor de consulta avançada para limpar e transformar os dados. Finalmente, você pode importar os dados transformados para sua planilha existente.

As ações comuns que você pode usar no editor de consulta avançada após importar dados incluem filtragem, remoção de duplicatas, divisão de colunas, formatação de dados e mesclagem de dados.

O principal benefício é que tarefas que levariam horas para serem realizadas manualmente podem ser realizadas em minutos com consulta avançada. Além disso, você pode usar repetidamente as mesmas ações de limpeza ou transformação com o clique de um botão quando as fontes de dados forem atualizadas.

Com qualquer recurso avançado do Excel, há uma certa curva de aprendizado. Mas embora você possa precisar de algumas tentativas para se familiarizar com a consulta avançada, ela tem uma interface bastante amigável quando você sabe onde está tudo.

Como usar consultas avançadas no Excel

Ao aprender como usar algo como consulta avançada, o método mais útil é com um exemplo. As etapas abaixo podem ser aplicadas a qualquer tipo de conjunto de dados, e as funcionalidades destacadas podem ser substituídas por outras formas de utilização de consultas conforme necessário.

Neste exemplo, estou reunindo dados de vendas de duas regiões geográficas diferentes para análise. Os dados vêm dos EUA e do Reino Unido, com formatação e moedas escritas e formatadas de maneira diferente em duas planilhas.

Meu objetivo é combinar esses conjuntos de dados em uma planilha, com os dados limpos e facilmente interpretados e analisados ​​em um só lugar.

Passo 1. Abra o Excel e acesse a consulta avançada.

Primeiramente, abra a planilha principal na qual você trabalhará. Neste caso, abri minha planilha de vendas nos EUA, pois é aqui que desejo limpar e combinar os dados.

Na faixa principal, clique em “Dados”. Você verá “Obter dados (Power Query)” como o primeiro item na faixa de dados.

Passo 2. Importe seus dados.

Clique no menu suspenso para acessar suas opções de consulta avançada.

Nesta primeira etapa, queremos clicar em “Obter dados” para que possamos iniciar uma consulta usando os dados da planilha de vendas do Reino Unido.

Clicar nesta opção abrirá uma janela onde você poderá escolher uma fonte de dados. Como você verá, é possível extrair dados de uma ampla variedade de fontes, incluindo arquivos compartilhados do SharePoint.

Neste caso, estamos selecionando “Pasta de trabalho do Excel” para acessar o arquivo que está armazenado localmente no meu computador. Depois de fazer essa seleção, basta procurar a planilha correta em seus arquivos e clicar em “Obter dados”.

Em seguida, na tela seguinte, clique em “Avançar”. A partir daqui, você pode selecionar planilhas específicas que deseja importar. Você poderá visualizar os dados antes de clicar em “Carregar” para finalizar a importação.

Se você selecionar “Carregar” neste estágio, a consulta avançada importará os dados para uma nova guia em sua planilha existente.

A partir daí, você pode selecionar “Dados” e “Iniciar Editor de Consulta Power” para realizar limpeza ou atualização em massa dos dados importados.

Passo 3. Limpeza dos dados importados.

Se preferir limpar seus dados antes de importá-los, você pode fazer isso durante a fase de importação.

Em vez de selecionar “Carregar” durante o processo de importação, você pode selecionar “Transformar dados”, que abre o Editor do Power Query como na etapa anterior.

Por exemplo, a planilha de vendas do Reino Unido contém os mesmos dados que a planilha dos EUA, mas não está formatada corretamente. O formato da moeda não é aplicado à célula da moeda.

Portanto, no Power Query Editor, posso selecionar a guia “Transform” para editar os dados antes de importá-los (eu também poderia fazer isso se importasse os dados primeiro e depois abrisse o Power Query Editor).

Como você pode ver, há inúmeras opções disponíveis para você aqui. Neste caso, selecionarei a coluna Moeda e selecionarei “Tipo de dados”.

A partir daí, posso selecionar a opção Moeda e alterar o valor para USD.

Passo 4. Carregue seus dados transformados.

A etapa final é adicionar esses dados limpos e transformados à minha planilha existente. No Power Query Editor, navegue de volta para a guia “Home” e clique em “Fechar e carregar”.

O Excel agora carregará esta consulta em uma nova guia da sua planilha.

Este processo passo a passo simples mostra como começar a acessar e usar a consulta avançada.

Os casos de uso para consulta avançada, no entanto, são quase infinitos devido à grande variedade de dados que você pode importar e limpar e aos tipos de transformações e manipulações de dados que você pode realizar.

Então, vamos dar uma olhada em alguns exemplos do que você pode fazer com a consulta avançada.

Exemplos de consulta avançada

Consolidando dados de várias planilhas

Esteja você trabalhando com várias pastas de trabalho de sua própria criação ou recebendo arquivos enviados por outras pessoas, a necessidade de consolidar dados em uma planilha é um problema bastante comum.

A consulta avançada torna esta uma tarefa bastante simples.

Primeiro, certifique-se de que todas as pastas de trabalho que você deseja consolidar estejam salvas na mesma pasta. Sempre que possível, certifique-se de que todos correspondam em termos de terem os mesmos títulos de coluna.

Abra o Excel em uma pasta de trabalho em branco e acesse a consulta avançada com a opção “Obter dados”.

Selecione sua fonte de dados como “pasta de trabalho do Excel” e escolha seu primeiro arquivo. Se todas as suas planilhas estiverem formatadas da mesma forma, você não precisará fazer mais nada aqui. Basta clicar em “Carregar”.

A seguir, você importará suas outras planilhas. Mas, em vez de executar esta primeira etapa exatamente da mesma maneira, você anexará as outras planilhas a esta consulta existente que acabou de criar.

Na planilha, abra a consulta avançada novamente e selecione a segunda pasta de trabalho da sua lista ao importar um arquivo. Em vez de clicar em “Carregar”, selecione a opção “Transformar dados”.

Agora, você deve ver ambas as consultas disponíveis no Power Query Editor no painel esquerdo.

No lado direito da faixa de opções no Power Query Editor, você verá um botão “Combinar” com um menu suspenso contendo opções para mesclar ou anexar consultas.

No menu suspenso, selecione “Anexar consultas como novas”. Selecione as tabelas que deseja anexar uma ao final da outra e clique em “Ok”.

A consulta avançada anexará as duas tabelas em uma guia da sua planilha. Você pode realizar esta ação com duas planilhas ou várias planilhas, dependendo de suas necessidades.

Limpando dados antes de importar

Às vezes, você pode estar lidando com uma planilha confusa. A formatação e o estilo estão em todo lugar, e você quer ter certeza de que está o mais limpo e organizado possível antes de importar.

A consulta avançada torna isso uma parte fácil do processo à medida que você importa dados, para que você não precise perder tempo limpando manualmente as coisas depois.

Um exemplo simples é uma planilha onde parte do texto está em maiúscula e outra não. Portanto, o objetivo é converter tudo em maiúsculas e minúsculas antes de adicioná-lo à planilha existente.

Digamos que a equipa de vendas em França nos enviou o seu relatório de vendas. Parece ótimo, mas parte do texto na coluna ID do país está em letras maiúsculas e parte não. Além disso, surgiram espaços aleatórios no nome do país que também precisam ser corrigidos, ou teremos dificuldade para extrair adequadamente os dados para tabelas dinâmicas e outros relatórios.

Na sua planilha, abra a consulta avançada e importe o arquivo com os dados confusos. Clique em “Transformar dados” em vez de importá-los imediatamente.

Primeiro, encontre a coluna com letras maiúsculas misturadas e espaços extras. Clique com o botão direito na coluna e clique em “Transformar coluna”, depois em “Transformações de texto” e, por fim, em “Cortar”. Isso removerá qualquer espaçamento extra que possa interferir na manipulação dos dados.

Clique com o botão direito na coluna novamente e, desta vez, clique em “Capitalizar cada palavra” nas opções. Pode ser necessário primeiro selecionar “minúsculas” para corrigir quaisquer palavras em letras maiúsculas e, em seguida, usar a opção “Capitalizar cada palavra”.

Agora, você pode clicar em “Carregar” para que seus dados limpos sejam importados para sua planilha Excel.

Dados não dinâmicos/dinâmicos para melhorar os recursos de análise

Outro desafio comum do Excel é receber dados de uma forma que dificulta a manipulação e a análise, especificamente quando você deseja alternar colunas e linhas — ou “dinamizar” os dados.

Neste exemplo, temos dados de pesquisa em que as colunas são analisadas pelos respondentes. Idealmente, gostaríamos que fosse analisado com as colunas conforme as perguntas da pesquisa.

Primeiro, abra uma tabela do Excel em branco e importe sua planilha usando consulta avançada.

Como no exemplo anterior, em vez de clicar em “Carregar”, primeiro precisamos clicar em “Transformar dados”.

Agora que o Power Query Editor está aberto, navegue até a guia Transformar na faixa de opções. Selecione todas as colunas de perguntas da pesquisa juntas, clique em “Desdinamizar colunas” e selecione “Desdinamizar apenas colunas selecionadas”.

A consulta avançada pega as colunas selecionadas e analisa os dados pelos atributos e valores das perguntas. Navegue de volta para a guia “Página inicial” e clique em “Fechar e carregar” para usar os dados não dinâmicos em sua planilha.

Experimente o Power Query

No grande esquema de ferramentas, o Excel está entre os mais poderosos quando se trata de classificar, limpar e transformar o tipo de dados usados ​​nas operações comerciais diárias em vários departamentos e equipes.

Embora possa demorar um pouco para se familiarizar com todos os recursos e funcionalidades de algo como consulta avançada, vale a pena o esforço.

Mesmo com planilhas relativamente simples que contêm apenas muitos dados, a consulta avançada pode reduzir significativamente o tempo de limpeza e transformação, para que você possa entrar em análises importantes e tomar decisões com muito mais rapidez.