Conteúdo
Formulação do problema
Como dados de entrada, temos um arquivo Excel, onde uma das planilhas contém várias tabelas com dados de vendas da seguinte forma:
Observe que:
- Tabelas de diferentes tamanhos e com diferentes conjuntos de produtos e regiões em linhas e colunas sem qualquer classificação.
- Linhas em branco podem ser inseridas entre as tabelas.
- O número de tabelas pode ser qualquer.
Duas premissas importantes. É assumido que:
- Acima de cada tabela, na primeira coluna, está o nome do gerente cujas vendas a tabela ilustra (Ivanov, Petrov, Sidorov, etc.)
- Os nomes de mercadorias e regiões em todas as tabelas são escritos da mesma maneira – com precisão de maiúsculas e minúsculas.
O objetivo final é coletar dados de todas as tabelas em uma tabela normalizada plana, conveniente para análise posterior e construção de um resumo, ou seja, nesta:
Etapa 1. Conecte-se ao arquivo
Vamos criar um novo arquivo Excel vazio e selecioná-lo na guia Data Command Obter dados – do arquivo – do livro (Dados — Do arquivo — Da pasta de trabalho). Especifique a localização do arquivo de origem com os dados de vendas e, em seguida, na janela do navegador, selecione a planilha que precisamos e clique no botão Converter dados (Transformar dados):
Como resultado, todos os dados dele devem ser carregados no editor do Power Query:
Etapa 2. Limpe o lixo
Excluir etapas geradas automaticamente tipo modificado (Tipo Alterado) и Cabeçalhos elevados (Cabeçalhos Promovidos) e se livrar de linhas vazias e linhas com totais usando um filtro nulo и TOTAL pela primeira coluna. Como resultado, obtemos a seguinte imagem:
Etapa 3. Adicionando gerentes
Para entender posteriormente onde estão as vendas, é necessário adicionar uma coluna à nossa tabela, onde em cada linha haverá um sobrenome correspondente. Por esta:
1. Vamos adicionar uma coluna auxiliar com números de linha usando o comando Adicionar Coluna - Coluna de Índice - De 0 (Adicionar coluna — coluna de índice — de 0).
2. Adicione uma coluna com uma fórmula com o comando Adicionando uma Coluna – Coluna Personalizada (Adicionar coluna — coluna personalizada) e introduza a seguinte construção lá:
A lógica desta fórmula é simples - se o valor da próxima célula na primeira coluna for "Produto", isso significa que nos deparamos com o início de uma nova tabela, então exibimos o valor da célula anterior com o nome do gerente. Caso contrário, não exibimos nada, ou seja, null.
Para obter a célula pai com o sobrenome, primeiro nos referimos à tabela da etapa anterior #”Índice adicionado”, e especifique o nome da coluna que precisamos [Coluna1] entre colchetes e o número da célula nessa coluna entre colchetes. O número da célula será um a menos que o atual, que tiramos da coluna Índice, Respectivamente.
3. Resta preencher as células vazias com nulo nomes de células superiores com o comando Transformar – Preencher – Abaixo (Transformar - Preencher - Baixo) e exclua a coluna não mais necessária com índices e linhas com sobrenomes na primeira coluna. Como resultado, obtemos:
Etapa 4. Agrupamento em tabelas separadas por gerentes
A próxima etapa é agrupar as linhas de cada gerente em tabelas separadas. Para isso, na aba Transformação, utilize o comando Agrupar por (Transformar – Agrupar por) e na janela que se abre, selecione a coluna Gerenciador e a operação Todas as linhas (Todas as linhas) para simplesmente coletar dados sem aplicar nenhuma função de agregação a (soma, média, etc.). P.):
Como resultado, obtemos tabelas separadas para cada gerente:
Etapa 5: transformar tabelas aninhadas
Agora damos as tabelas que estão em cada célula da coluna resultante Todos os dados em forma decente.
Primeiro, exclua uma coluna que não seja mais necessária em cada tabela Novos Negócios. Usamos novamente Coluna personalizada aba Transformação (Transformar — coluna personalizada) e a seguinte fórmula:
Então, com outra coluna calculada, elevamos a primeira linha de cada tabela aos títulos:
E, finalmente, realizamos a transformação principal - desdobrando cada tabela usando a função M Tabela.UnpivotOtherColumns:
Os nomes das regiões do cabeçalho irão para uma nova coluna e obteremos uma tabela mais estreita, mas ao mesmo tempo, uma tabela normalizada mais longa. Células vazias com nulo são ignorados.
Livrando-se de colunas intermediárias desnecessárias, temos:
Etapa 6 Expandir tabelas aninhadas
Resta expandir todas as tabelas aninhadas normalizadas em uma única lista usando o botão com setas duplas no cabeçalho da coluna:
… e finalmente conseguimos o que queríamos:
Você pode exportar a tabela resultante de volta para o Excel usando o comando Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para…).
- Crie tabelas com diferentes cabeçalhos de vários livros
- Coletando dados de todos os arquivos em uma determinada pasta
- Coletando dados de todas as folhas do livro em uma tabela