Dicas do Excel: Dividir 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 específicos de colunas. Esse método não apenas melhora a organização dos dados, mas também aumenta a legibilidade e facilita a análise de 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 em cada nome de produto para que o desempenho de vendas individual possa ser analisado separadamente.

Dividir dados em várias planilhas com base no valor da coluna

• Usando código VBA
• Usando um recurso poderoso - Kutools para Excel

Dividir dados em várias pastas de trabalho com base no valor da coluna com código VBA

Dividir 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á sua paciência para copiar e colar repetidamente. Nesta seção, apresentaremos dois métodos diretos para lidar com essa tarefa de forma eficiente no Excel, economizando tempo e reduzindo o potencial de erros.

Dividir dados em várias planilhas com base no valor da coluna com código VBA

1. Mantenha pressionadas 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 Sub

3. Em seguida, pressione F5 para executar o código, e uma caixa de prompt será exibida para lembrá-lo de selecionar a linha de cabeçalho; depois, clique em OK. Veja a captura de tela:

4. No segundo prompt, selecione os dados da coluna que deseja dividir com base neles, depois clique em OK. Veja a captura de tela:

5. Todos os dados na 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:

Dividir dados em várias planilhas com base no valor da coluna com Kutools para Excel

Kutools para Excel traz um recurso inteligente – Dividir Dados diretamente para o seu ambiente do 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 parte da informação esteja exatamente onde você precisa. Diga adeus à tarefa tediosa de organizar manualmente suas planilhas e adote uma maneira mais rápida e livre de erros de gerenciar 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 várias planilhas.

1. Selecione a opção Coluna específica na seção Dividir com base em e escolha o valor da coluna que deseja usar para dividir os dados a partir da lista suspensa.
2. Se seus dados contêm cabeçalhos e você deseja inseri-los em cada nova planilha dividida, marque a opção Os dados contêm cabeçalhos. (Você pode especificar o número de linhas de cabeçalho com base nos seus dados. Por exemplo, se seus dados contêm dois cabeçalhos, digite 2.)
3. Em seguida, você pode especificar os nomes das planilhas divididas, na seção Nome das novas planilhas, especifique a regra de nomenclatura das planilhas a partir da lista suspensa Regras. Você também pode adicionar Prefixo ou Sufixo aos nomes das planilhas.
4. Clique no botão OK . Veja a captura de tela:
   

Agora, os dados na planilha são divididos em várias planilhas em uma nova pasta de trabalho.

Dividir dados em várias pastas de trabalho com base no valor da coluna com código VBA

Ocasionalmente, em vez de dividir 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 específico de coluna.

1. Mantenha pressionadas 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 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, pressione F5 para executar o código, e uma caixa de prompt será exibida para lembrá-lo de selecionar a linha de cabeçalho; depois, clique em OK. Veja a captura de tela:

4. No segundo prompt, selecione os dados da coluna que deseja dividir com base neles, depois clique em OK. Veja a captura de tela:

5. Após a divisão, todos os dados na 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: