Dicas do Excel: divida os dados em várias planilhas/pastas de trabalho com base no valor da coluna
Ao gerenciar grandes conjuntos de dados no Excel, pode ser altamente benéfico dividir os dados em várias planilhas com base em valores de colunas específicas. Este método melhora não apenas a organização dos dados, mas também aumenta a legibilidade e facilita a análise dos dados.
Suponha que você tenha um grande registro de vendas contendo várias entradas, como o nome do produto e a quantidade vendida no primeiro trimestre. O objetivo é dividir esses dados em planilhas separadas com base no nome de cada produto, para que o desempenho de vendas individual possa ser analisado separadamente.
Divida os dados em várias planilhas com base no valor da coluna
Divida os dados em várias pastas de trabalho com base no valor da coluna com código VBA
Divida os dados em várias planilhas com base no valor da coluna
Normalmente, você pode classificar a lista de dados primeiro e depois copiá-los e colá-los um por um em outras novas planilhas. Mas isso exigirá paciência para copiar e colar repetidamente. Nesta seção, apresentaremos dois métodos simples para realizar essa tarefa com eficiência no Excel, economizando tempo e reduzindo o potencial de erros.
Divida os dados em várias planilhas com base no valor da coluna com código VBA
1. Mantenha pressionado o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. Clique inserção > Móduloe cole o código a seguir 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. Então aperte F5 para executar o código e uma caixa de prompt é exibida para lembrá-lo de selecionar a linha do cabeçalho e, em seguida, clique em OK. Veja a imagem:
4. Na segunda caixa de prompt, selecione os dados da coluna nos quais deseja dividir e clique em OK. Veja a imagem:
5. Todos os dados da planilha ativa são divididos em várias planilhas com base nos valores das colunas. As planilhas resultantes são nomeadas de acordo com os valores nas células divididas e colocadas no final da pasta de trabalho. Veja a captura de tela:
Divida os dados em várias planilhas com base no valor da coluna com o Kutools para Excel
Kutools for Excel traz recurso inteligente - Dividir Dados diretamente em seu ambiente Excel. Dividir dados em várias planilhas não é mais um desafio. Nossa ferramenta intuitiva divide automaticamente seu conjunto de dados com base no valor da coluna escolhida ou na contagem de linhas, garantindo que cada informação esteja exatamente onde você precisa. Diga adeus à tediosa tarefa de organizar manualmente suas planilhas e adote uma maneira mais rápida e sem erros de gerenciar seus dados.
Depois de instalar Kutools for Excel, selecione o intervalo de dados e clique em Kutools Plus > Dividir Dados para abrir o Divida os dados em várias planilhas caixa de diálogo.
- Selecionar Coluna específica opção no Dividir com base em seção e escolha o valor da coluna no qual deseja dividir os dados na lista suspensa.
- Se seus dados possuem cabeçalhos e você deseja inseri-los em cada nova planilha dividida, verifique Meus dados têm cabeçalhos opção. (Você pode especificar o número de linhas de cabeçalho com base em seus dados. Por exemplo, se seus dados contiverem dois cabeçalhos, digite 2.)
- Em seguida, você pode especificar os nomes da planilha dividida, no Nome da nova planilha seção, especifique a regra de nomes de planilhas na lista suspensa Regras, você pode adicionar o Prefixo or Sufixo para os nomes das folhas também.
- Clique na OK botão. Veja a imagem:
Agora, os dados da planilha são divididos em várias planilhas em uma nova pasta de trabalho.
Divida os dados em várias pastas de trabalho com base no valor da coluna com código VBA
Ocasionalmente, em vez de dividir os dados em várias planilhas, pode ser mais benéfico dividir os dados em pastas de trabalho separadas com base em uma coluna-chave. Aqui está um guia passo a passo sobre como usar o código VBA para automatizar o processo de divisão de dados em várias pastas de trabalho com base em um valor de coluna específico.
1. Mantenha pressionado o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. Clique inserção > Móduloe cole o seguinte código no Janela 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. Então aperte F5 para executar o código e uma caixa de prompt é exibida para lembrá-lo de selecionar a linha do cabeçalho e, em seguida, clique em OK. Veja a imagem:
4. Na segunda caixa de prompt, selecione os dados da coluna nos quais deseja dividir e clique em OK. Veja a imagem:
5. Após a divisão, todos os dados da planilha ativa são divididos em várias pastas de trabalho com base nos valores das colunas. Todas as pastas de trabalho divididas são salvas na pasta que você especificou. Veja a captura de tela:
Artigos relacionados:
- Divida os dados em várias planilhas por contagem de linhas
- A divisão eficiente de um grande intervalo de dados em várias planilhas do Excel com base em uma contagem de linhas específica pode agilizar o gerenciamento de dados. Por exemplo, dividir um conjunto de dados a cada 5 linhas em várias planilhas pode torná-lo mais gerenciável e organizado. Este guia oferece dois métodos práticos para realizar esta tarefa de forma rápida e fácil.
- Mesclar duas ou mais tabelas em uma com base nas colunas principais
- Supondo que você tenha três tabelas em uma pasta de trabalho, agora você deseja mesclar essas tabelas em uma tabela com base nas colunas-chave correspondentes para obter o resultado conforme mostrado na captura de tela abaixo. Esta pode ser uma tarefa problemática para a maioria de nós, mas, por favor, não se preocupe, neste artigo, irei apresentar alguns métodos para resolver este problema.
- Dividir strings de texto por delimitador em várias linhas
- Normalmente, você pode usar o recurso Texto para coluna para dividir o conteúdo da célula em várias colunas por um delimitador específico, como vírgula, ponto, ponto e vírgula, barra etc. Mas, às vezes, pode ser necessário dividir o conteúdo da célula delimitada em várias linhas e repita os dados de outras colunas conforme a captura de tela mostrada abaixo. Você tem alguma boa maneira de lidar com essa tarefa no Excel? Este tutorial apresentará alguns métodos eficazes para concluir este trabalho no Excel.
- Divida o conteúdo das células multilinhas em linhas/colunas separadas
- Supondo que você tenha um conteúdo de célula de várias linhas separado por Alt + Enter e agora precise dividir o conteúdo de várias linhas em linhas ou colunas separadas, o que você pode fazer? Neste artigo, você aprenderá como dividir rapidamente o conteúdo de uma célula de várias linhas em linhas ou colunas separadas.
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
