Conteúdo
Formulação do problema
Vejamos uma bela solução para uma das situações muito comuns que a maioria dos usuários do Excel enfrenta mais cedo ou mais tarde: você precisa coletar dados de forma rápida e automática de um grande número de arquivos em uma tabela final.
Suponha que tenhamos a seguinte pasta, que contém vários arquivos com dados das cidades filiais:
O número de arquivos não importa e pode mudar no futuro. Cada arquivo tem uma planilha chamada Vendasonde a tabela de dados está localizada:
O número de linhas (ordens) nas tabelas, é claro, é diferente, mas o conjunto de colunas é padrão em todos os lugares.
Tarefa: coletar dados de todos os arquivos em um livro com atualização automática subsequente ao adicionar ou excluir arquivos de cidades ou linhas em tabelas. De acordo com a tabela consolidada final, então será possível construir quaisquer relatórios, tabelas dinâmicas, dados de classificação de filtros, etc. O principal é poder coletar.
Nós selecionamos armas
Para a solução, precisamos da versão mais recente do Excel 2016 (a funcionalidade necessária já está incorporada por padrão) ou versões anteriores do Excel 2010-2013 com o suplemento gratuito instalado Consulta de energia da Microsoft (faça o download aqui). O Power Query é uma ferramenta super flexível e super poderosa para carregar dados do mundo exterior para o Excel, depois removê-los e processá-los. O Power Query suporta quase todas as fontes de dados existentes – de arquivos de texto a SQL e até Facebook 🙂
Se você não tiver o Excel 2013 ou 2016, não poderá ler mais (brincadeira). Em versões mais antigas do Excel, tal tarefa só pode ser realizada programando uma macro em Visual Basic (o que é muito difícil para iniciantes) ou por cópia manual monótona (que leva muito tempo e gera erros).
Etapa 1. Importar um arquivo como amostra
Primeiro, vamos importar dados de uma pasta de trabalho como exemplo, para que o Excel “pegue a ideia”. Para fazer isso, crie uma nova pasta de trabalho em branco e…
- se você tiver o Excel 2016, abra a guia Data e depois Criar Consulta – Do Arquivo – Do Livro (Dados - Nova Consulta - Do arquivo - Do Excel)
- se você tiver o Excel 2010-2013 com o suplemento Power Query instalado, abra a guia Consulta de energia e selecione nele Do arquivo – Do livro (Do arquivo — Do Excel)
Em seguida, na janela que se abre, vá para a nossa pasta com relatórios e selecione qualquer um dos arquivos da cidade (não importa qual, pois todos são típicos). Após alguns segundos, a janela Navegador deve aparecer, onde você precisa selecionar a planilha que precisamos (Vendas) no lado esquerdo e seu conteúdo será exibido no lado direito:
Se você clicar no botão no canto inferior direito desta janela Baixar (Carga), a tabela será importada imediatamente para a planilha em sua forma original. Para um único arquivo, isso é bom, mas precisamos carregar muitos desses arquivos, então faremos um pouco diferente e clicaremos no botão Correção (Editar). Depois disso, o editor de consultas do Power Query deve ser exibido em uma janela separada com nossos dados do livro:
Esta é uma ferramenta muito poderosa que permite “terminar” a tabela para a visualização que precisamos. Mesmo uma descrição superficial de todas as suas funções levaria cerca de cem páginas, mas, se muito brevemente, usando esta janela você pode:
- filtrar dados desnecessários, linhas vazias, linhas com erros
- classificar dados por uma ou mais colunas
- livrar-se da repetição
- divida o texto fixo por colunas (por delimitadores, número de caracteres, etc.)
- colocar o texto em ordem (remover espaços extras, corrigir maiúsculas, etc.)
- converter tipos de dados de todas as maneiras possíveis (transformar números como texto em números normais e vice-versa)
- transpor (girar) tabelas e expandir tabelas cruzadas bidimensionais em tabelas planas
- adicione colunas adicionais à tabela e use fórmulas e funções nelas usando a linguagem M incorporada ao Power Query.
- ...
Por exemplo, vamos adicionar uma coluna com o nome de texto do mês à nossa tabela, para que posteriormente seja mais fácil criar relatórios de tabela dinâmica. Para fazer isso, clique com o botão direito do mouse no cabeçalho da coluna dadose selecione o comando Coluna duplicada (Coluna Duplicada)e, em seguida, clique com o botão direito do mouse no cabeçalho da coluna duplicada que aparece e selecione Comandos Transformar - Mês - Nome do Mês:
Uma nova coluna deve ser formada com os nomes de texto do mês para cada linha. Ao clicar duas vezes no cabeçalho de uma coluna, você pode renomeá-lo de Copiar Data para um mais confortável Mês, por exemplo.
Se em algumas colunas o programa não reconheceu corretamente o tipo de dados, você pode ajudá-lo clicando no ícone de formato no lado esquerdo de cada coluna:
Você pode excluir linhas com erros ou linhas vazias, bem como gerentes ou clientes desnecessários, usando um filtro simples:
Além disso, todas as transformações realizadas são fixadas no painel direito, onde sempre podem ser revertidas (cruzadas) ou alteradas seus parâmetros (engrenagem):
Leve e elegante, não é?
Etapa 2. Vamos transformar nossa solicitação em uma função
Para repetir posteriormente todas as transformações de dados feitas para cada livro importado, precisamos converter nossa solicitação criada em uma função, que será aplicada, por sua vez, a todos os nossos arquivos. Para fazer isso é realmente muito simples.
No Editor de consultas, vá para a guia Exibir e clique no botão Editor Avançado (Visualizar — Editor Avançado). Uma janela deve abrir onde todas as nossas ações anteriores serão escritas na forma de código na linguagem M. Observe que o caminho para o arquivo que importamos para o exemplo está codificado no código:
Agora vamos fazer alguns ajustes:
Seu significado é simples: a primeira linha (caminho do arquivo)=> transforma nosso procedimento em uma função com um argumento caminho de arquivo, e abaixo alteramos o caminho fixo para o valor dessa variável.
Tudo. Clique em Acabamento e deve ver isso:
Não tenha medo de que os dados tenham desaparecido - na verdade, está tudo bem, tudo deve ficar assim 🙂 Criamos com sucesso nossa função personalizada, onde todo o algoritmo de importação e processamento de dados é lembrado sem estar vinculado a um arquivo específico . Resta dar-lhe um nome mais compreensível (por exemplo Obter dados) no painel à direita no campo Nome e você pode colher Home — Fechar e baixar (Início - Fechar e Carregar). Observe que o caminho para o arquivo que importamos para o exemplo está codificado no código. Você retornará à janela principal do Microsoft Excel, mas um painel com a conexão criada para nossa função deve aparecer à direita:
Etapa 3. Coletando todos os arquivos
Toda a parte mais difícil ficou para trás, a parte agradável e fácil fica. Vá para a aba Dados – Criar Consulta – Do Arquivo – Da Pasta (Dados — Nova Consulta — Do arquivo — Da pasta) ou, se você tiver o Excel 2010-2013, de forma semelhante à guia Consulta de energia. Na janela que aparece, especifique a pasta onde estão localizados todos os nossos arquivos da cidade de origem e clique em OK. O próximo passo deve abrir uma janela onde serão listados todos os arquivos do Excel encontrados nesta pasta (e suas subpastas) e os detalhes de cada um deles:
Clique Mudar (Editar) e novamente entramos na janela familiar do editor de consultas.
Agora precisamos adicionar outra coluna à nossa tabela com nossa função criada, que irá “puxar” os dados de cada arquivo. Para fazer isso, vá para a guia Adicionar coluna – coluna personalizada (Adicionar coluna — Adicionar coluna personalizada) e na janela que aparece, digite nossa função Obter dados, especificando para ele como argumento o caminho completo para cada arquivo:
Depois de clicar em OK a coluna criada deve ser adicionada à nossa tabela à direita.
Agora vamos excluir todas as colunas desnecessárias (como no Excel, usando o botão direito do mouse – Eliminar), deixando apenas a coluna adicionada e a coluna com o nome do arquivo, pois esse nome (mais precisamente, a cidade) será útil ter no total de dados de cada linha.
E agora o “momento uau” – clique no ícone com suas próprias setas no canto superior direito da coluna adicionada com nossa função:
… desmarque Use o nome da coluna original como prefixo (Use o nome da coluna original como prefixo)e clique OK. E nossa função irá carregar e processar os dados de cada arquivo, seguindo o algoritmo gravado e coletando tudo em uma tabela comum:
Para uma beleza completa, você também pode remover as extensões .xlsx da primeira coluna com nomes de arquivos – por substituição padrão por “nada” (clique com o botão direito do mouse no cabeçalho da coluna – Substituto) e renomeie esta coluna para Cidades. E também corrija o formato dos dados na coluna com a data.
Tudo! Clique em Início - Fechar e carregar (Início - Fechar e carregar). Todos os dados coletados pela consulta para todas as cidades serão carregados na planilha Excel atual no formato “tabela inteligente”:
A conexão criada e nossa função de montagem não precisam ser salvas separadamente de forma alguma - elas são salvas junto com o arquivo atual da maneira usual.
No futuro, com qualquer alteração na pasta (adicionando ou removendo cidades) ou nos arquivos (alterando o número de linhas), bastará clicar com o botão direito do mouse diretamente na tabela ou na consulta do painel direito e selecionar o comando Atualizar e salvar (Atualizar) – O Power Query “reconstruirá” todos os dados novamente em alguns segundos.
PS
Alteração. Após as atualizações de janeiro de 2017, o Power Query aprendeu a coletar pastas de trabalho do Excel por si só, ou seja, não é mais necessário fazer uma função separada – isso acontece automaticamente. Assim, a segunda etapa deste artigo não é mais necessária e todo o processo se torna visivelmente mais simples:
- Escolha Criar Solicitação – Do Arquivo – Da Pasta – Selecionar Pasta – OK
- Depois que a lista de arquivos aparecer, pressione Mudar
- Na janela Query Editor, expanda a coluna Binary com uma seta dupla e selecione o nome da planilha a ser obtida de cada arquivo
E isso é tudo! Canção!
- Redesenho do crosstab em um plano adequado para a construção de tabelas dinâmicas
- Criando um gráfico de bolhas animado no Power View
- Macro para montar planilhas de diferentes arquivos do Excel em um