Dicas do Excel: Dividir Dados em Múltiplas Planilhas / livros com base no valor da coluna
Ao gerir grandes conjuntos de dados no Excel, dividir os dados em múltiplas folhas com base nos valores de uma coluna específica pode revelar-se extremamente vantajoso. Este método não só melhora a organização dos dados, como também potencia a legibilidade e facilita a análise.
Imagine que tem um registo detalhado de vendas com múltiplas entradas, incluindo o nome do produto e a quantidade vendida no primeiro trimestre. O objetivo é dividir esses dados em folhas separadas por nome de produto, permitindo uma análise individual do desempenho das vendas.
Dividir Dados em Múltiplas Planilhas com base no valor da coluna
Dividir Dados em vários livros com base no valor da coluna utilizando código VBA
Dividir Dados em Múltiplas Planilhas com base no valor da coluna
Normalmente, pode ordenar primeiro a lista de dados e depois copiá-los e colá-los um a um noutra nova planilha. Contudo, este processo exige paciência, já que implica repetir continuamente as operações de cópia e colagem. Nesta secção, apresentamos dois métodos simples para resolver esta tarefa de forma eficaz no Excel, poupando tempo e reduzindo potenciais erros.
Dividir Dados em Múltiplas Planilhas com base no valor da coluna utilizando código VBA
1. Mantenha premidas as teclas ALT + F11 para abrir a janela Microsoft Visual Basic for Applications.
2. Clique em Inserir > Módulo e cole o seguinte código na Janela do Módulo.
Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub3. Em seguida, prima a tecla F5 para executar o código. Aparecerá uma caixa de diálogo a solicitar que selecione a linha de cabeçalho; clique depois em OK. Veja a imagem:
4. Na segunda caixa de diálogo, selecione os dados da coluna que pretende utilizar como critério de divisão e, em seguida, clique em OK. Veja a imagem:
5. Todos os dados da folha ativa são divididos em várias folhas com base nos valores de uma coluna. As folhas resultantes recebem nomes correspondentes aos valores em «Dividir Células» e são inseridas no final do livro. Veja a imagem:
Dividir Dados em Múltiplas Planilhas com base no valor da coluna utilizando Kutools para Excel
Kutools para Excel oferece uma funcionalidade inteligente – Dividir Dados diretamente no seu ambiente do Excel. Dividir dados em várias folhas já não é um desafio! A nossa ferramenta intuitiva divide automaticamente o seu conjunto de dados com base no valor da coluna escolhida ou na contagem de linhas, garantindo que cada informação está exatamente onde precisa de estar. Diga adeus à tarefa tediosa de organizar manualmente as suas folhas de cálculo e adote uma forma mais rápida e isenta de erros de gerir os seus dados.
Após instalar o Kutools para Excel, selecione o intervalo de dados e, em seguida, clique em KUTOOLS PLUS > Dividir Dados para abrir a caixa de diálogo Dividir Dados em Múltiplas Planilhas.
- Selecione a opção Especificar coluna na secção Critério de Divisão e escolha, na lista suspensa, o valor da coluna com base no qual pretende dividir os dados.
- Se os seus dados tiverem cabeçalhos e quiser incluí-los em cada nova folha dividida, marque a opção Incluir títulos (pode especificar o número de linhas de título de acordo com os seus dados; por exemplo, se os seus dados contiverem dois cabeçalhos, introduza 2).
- Depois, pode especificar o nome da folha de cálculo a dividir. Na secção Nome das planilhas criadas, defina a regra para o nome da folha a partir da lista pendente Regras e, ainda, adicione um Prefixo ou um Sufixo aos nomes das folhas.
- Clique no botão OK. Veja a imagem:
Agora, os dados da folha foram divididos em várias folhas num novo livro.
Dividir Dados em vários livros com base no valor da coluna utilizando código VBA
Por vezes, em vez de dividir os dados em várias folhas, poderá ser mais vantajoso separá-los em livros distintos com base num Coluna Chave. Eis um guia passo a passo sobre como utilizar código VBA para automatizar o processo de divisão de dados em vários livros com base num valor de Especificar coluna.
1. Mantenha premidas as teclas ALT + F11para abrir a janela Microsoft Visual Basic for Applications.
2. Clique em Inserir > Módulo e cole o seguinte código na Janela do Módulo.
Sub SplitDataByColToWorkbooks()
' Updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWS As Workbook
Dim savePath As String
' Set the directory to save new workbooks
savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
Application.DisplayAlerts = False
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.Address(False, False)
titlerow = xTRg.Row
ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
For i = 2 To UBound(myarr)
Set xWS = Workbooks.Add
ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"
xWS.Close SaveChanges:=False
Next i
ws.AutoFilterMode = False
Application.DisplayAlerts = True
ws.Activate
End Sub
3. Em seguida, prima a tecla F5 para executar o código. Aparecerá uma caixa de diálogo a solicitar que selecione a linha de cabeçalho; clique depois em OK. Veja a imagem:
4. Na segunda caixa de diálogo, selecione os dados da coluna que pretende utilizar como critério de divisão e, em seguida, clique em OK. Veja a imagem:
5. Após a divisão, todos os dados da folha ativa são distribuídos por vários livros, com base nos valores da coluna selecionada. Todos os livros separados são guardados na pasta que especificou. Veja a imagem:
Artigos Relacionados:
- Dividir Dados em Múltiplas Planilhas por contagem de linhas
- Dividir eficientemente um grande intervalo de dados em várias folhas do Excel com base num número específico de linhas pode tornar a gestão de dados muito mais simples. Por exemplo, ao dividir um conjunto de dados a cada 5 linhas por várias folhas, torna-se mais organizado e fácil de gerir. Este guia apresenta dois métodos práticos para realizar esta tarefa de forma rápida e eficaz.
- Combinar duas ou mais tabelas numa só com base em Coluna Chave
- Imagine que tem três tabelas num livro e pretende combiná-las numa única tabela com base na respetiva Coluna Chave, obtendo o resultado apresentado na imagem seguinte. Pode parecer uma tarefa complicada para a maioria de nós — mas não se preocupe! Neste artigo, revelo alguns métodos eficazes para resolver este desafio com facilidade.
- Dividir cadeias de texto por delimitador em várias linhas
- Normalmente, pode utilizar a funcionalidade Texto em Colunas para dividir o conteúdo das células em várias colunas com base num delimitador específico, como vírgula, ponto, ponto e vírgula, barra, entre outros. Contudo, por vezes, poderá precisar de dividir esses conteúdos delimitados em várias linhas, repetindo os dados das restantes colunas — tal como ilustrado na imagem seguinte. Conhece uma boa solução para realizar esta tarefa no Excel? Este tutorial apresenta métodos eficazes para concluir este trabalho no Excel.
- Dividir conteúdos de células multilinha em linhas/colunas separadas
- Imagine que tem conteúdo multilinha numa célula, separado por Alt + Enter, e precisa de dividir esses conteúdos em linhas ou colunas separadas. O que pode fazer? Neste artigo, aprenderá como dividir rapidamente conteúdos multilinha de células em linhas ou colunas separadas.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos