Dividindo uma tabela em planilhas

O Microsoft Excel possui muitas ferramentas para coletar dados de várias tabelas (de diferentes planilhas ou de diferentes arquivos): links diretos, funções INDIRETO (INDIRETO), Power Query e complementos Power Pivot, etc. Deste lado da barricada, tudo parece bem.

Mas se você se deparar com um problema inverso – espalhar dados de uma tabela para planilhas diferentes – tudo ficará muito mais triste. No momento, não há ferramentas internas civilizadas para essa separação de dados no arsenal do Excel, infelizmente. Então você terá que usar uma macro no Visual Basic, ou usar a combinação gravador de macro + Power Query com um pouco de “refinamento de arquivo” depois.

Vamos dar uma olhada em como isso pode ser implementado.

Formulação do problema

Temos como dados iniciais uma tabela com tamanho superior a 5000 linhas para vendas:

Tarefa: distribuir os dados desta tabela por cidade em folhas separadas deste livro. Aqueles. na saída, você precisa obter em cada planilha apenas as linhas da tabela em que a venda foi na cidade correspondente:

Preparar

Para não complicar o código da macro e torná-lo o mais fácil de entender possível, vamos realizar algumas etapas preparatórias.

Em primeiro lugar, o criar uma tabela de pesquisa separada, onde uma única coluna listará todas as cidades para as quais você deseja criar planilhas separadas. É claro que esse diretório pode não conter todas as cidades presentes nos dados de origem, mas apenas aquelas para as quais precisamos de relatórios. A maneira mais fácil de criar essa tabela é usar o comando Dados - Remover duplicatas (Dados — Remover duplicatas) para cópia de coluna Cidades ou função ÚNICO (ÚNICO) – se você tiver a versão mais recente do Excel 365.

Como as novas planilhas no Excel são criadas por padrão antes (à esquerda) da atual (anterior), também faz sentido classificar as cidades neste diretório em ordem decrescente (de Z a A) – depois da criação, a cidade as folhas serão organizadas em ordem alfabética.

Em segundo lugar, o пconverter ambas as tabelas para dinâmicas (“inteligente”) para facilitar o trabalho com eles. Usamos o comando Home – Formatar como uma tabela (Início — Formatar como Tabela) ou atalho de teclado Ctrl+T. Na aba que aparece Construtor (Desenhar) vamos chamá-los tablProdaji и TabelaCidade, respectivamente:

Método 1. Macro para divisão por folhas

Na guia Avançado revelador (Desenvolvedor) clique no botão Visual Basic ou use o atalho de teclado outro+F11. Na janela do editor de macro que se abre, insira um novo módulo vazio através do menu Inserir - Módulo e copie o seguinte código lá:

Sub Splitter() para cada célula em Range("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible).Copy Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Próxima célula Worksheets("Данные").ShowAllData End Sub	  

Aqui com um laço Para cada… Próximo implementou a passagem pelas células do diretório TabelaCidade, onde para cada cidade é filtrada (método AutoFiltro) na tabela de vendas original e, em seguida, copiando os resultados para a planilha recém-criada. Ao longo do caminho, a folha criada é renomeada para o mesmo nome da cidade e o ajuste automático da largura das colunas para beleza é ativado nela.

Você pode executar a macro criada no Excel na guia revelador botão Macros (Desenvolvedor — Macros) ou atalho de teclado outro+F8.

Método 2. Criar várias consultas no Power Query

O método anterior, por toda a sua compacidade e simplicidade, tem uma desvantagem significativa – as planilhas criadas pela macro não são atualizadas quando são feitas alterações na tabela de vendas original. Se a atualização em tempo real for necessária, você terá que usar o pacote VBA + Power Query, ou melhor, criar usando uma macro não apenas planilhas com dados estáticos, mas consultas atualizadas do Power Query.

A macro neste caso é parcialmente semelhante à anterior (também possui um ciclo Para cada… Próximo para iterar sobre cidades no diretório), mas dentro do loop não haverá mais filtragem e cópia, mas sim criar uma consulta do Power Query e carregar seus resultados em uma nova planilha:

Sub Splitter2() para cada célula no intervalo("tabela de cidades") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Categoria"", digite texto}, {""Nome"", digite texto}, {""Cidade"", digite texto}, {""Gerente"", digite texto}, {""Negócio date "", digite datetime}, {""Custo"", digite number}})," & Chr(13) & "" & Chr(10) & " #""Linhas com filtro aplicado"" = Table.Se " & _ "lectRows(#""Tipo alterado"", each ([Cidade] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Linhas com filtro aplicado""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Falso End With ActiveSheet.Name = cell.Value Próxima célula End Sub  

Após seu lançamento, veremos as mesmas planilhas por cidade, mas as consultas do Power Query já criadas as formarão:

Com qualquer alteração nos dados de origem, bastará atualizar a tabela correspondente com o botão direito do mouse – o comando Atualizar e salvar (Atualizar) ou atualize todas as cidades de uma vez em massa usando o botão Atualizar tudo aba Data (Dados — Atualizar tudo).

• O que são macros, como criá-las e usá-las
• Salvando planilhas da pasta de trabalho como arquivos separados
• Coletando dados de todas as folhas do livro em uma tabela