Excel Tips: Gegevens opsplitsen in meerdere werkbladen / werkboeken op basis van kolomwaarde

Bij het beheren van grote datasets in Excel kan het zeer voordelig zijn om gegevens op te splitsen in meerdere werkbladen op basis van specifieke kolomwaarden. Deze methode verbetert niet alleen de organisatie van gegevens, maar verhoogt ook de leesbaarheid en vergemakkelijkt de analyse van gegevens.

Stel dat je een groot overzicht van verkooprecords hebt met meerdere items zoals productnaam en de verkochte hoeveelheid van het eerste kwartaal. Het doel is om deze gegevens op te splitsen in aparte werkbladen op basis van elke productnaam, zodat individuele verkoopprestaties apart kunnen worden geanalyseerd.

Gegevens opsplitsen in meerdere werkbladen op basis van kolomwaarde

• Door gebruik te maken van VBA-code
• Door gebruik te maken van een krachtige functie - Kutools voor Excel

Gegevens opsplitsen in meerdere werkboeken op basis van kolomwaarde met VBA-code

Gegevens opsplitsen in meerdere werkbladen op basis van kolomwaarde

Normaal gesproken kun je eerst de gegevenslijst sorteren en vervolgens de gegevens één voor één kopiëren en plakken in nieuwe werkbladen. Maar dit vereist veel geduld om herhaaldelijk te kopiëren en plakken. In deze sectie introduceren we twee eenvoudige methoden om deze taak efficiënt aan te pakken in Excel, wat tijd bespaart en de kans op fouten vermindert.

Gegevens opsplitsen in meerdere werkbladen op basis van kolomwaarde met VBA-code

1. Houd de toetsen ALT + F11 ingedrukt om het venster Microsoft Visual Basic for Applications te openen.

2. Klik op Invoegen > Module en plak de volgende code in het Modulevenster.

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. Druk vervolgens op F5 om de code uit te voeren, en er verschijnt een pop-upvenster waarin je wordt gevraagd de titelrij te selecteren. Klik daarna op OK. Zie screenshot:

4. Selecteer in het tweede pop-upvenster de kolomgegevens waarop je wilt splitsen en klik vervolgens op OK. Zie screenshot:

5. Alle gegevens in het actieve werkblad worden verdeeld over meerdere werkbladen op basis van de kolomwaarden. De resulterende werkbladen worden genoemd naar de waarden in de gesplitste cellen en worden aan het einde van het werkboek geplaatst. Zie screenshot:

Gegevens opsplitsen in meerdere werkbladen op basis van kolomwaarde met Kutools voor Excel

Kutools voor Excel brengt een slimme functie – Split Data – rechtstreeks in je Excel-omgeving. Het opsplitsen van gegevens in meerdere werkbladen is geen uitdaging meer. Ons intuïtieve hulpmiddel verdeelt automatisch je dataset op basis van de gekozen kolomwaarde of rijentelling, zodat elke informatie precies daar terechtkomt waar je het nodig hebt. Zeg vaarwel tegen de tijdrovende taak van handmatig spreadsheets organiseren en omarm een snellere, foutloze manier om je gegevens te beheren.

Nadat je Kutools voor Excel hebt geïnstalleerd, selecteer je het gegevensbereik en klik je op Kutools Plus > Split Data om het dialoogvenster Split Data into multiple worksheets te openen.

1. Selecteer de optie Specificeer kolom in de sectie Opsplitsingsbasis en kies de kolomwaarde waarop je de gegevens wilt splitsen uit de keuzelijst.
2. Als je gegevens koppen bevatten en je deze in elk nieuw gesplitst werkblad wilt invoegen, vink dan de optie Mijn gegevens bevatten koppen aan. (Je kunt het aantal titelrijen specificeren op basis van je gegevens. Bijvoorbeeld, als je gegevens twee koppen bevatten, typ dan 2.)
3. Vervolgens kun je de namen van de gesplitste werkbladen specificeren. Onder de sectie Nieuwe werkbladnamen, kies je de regel voor de werkbladnamen uit de keuzelijst Regel. Je kunt ook een Voorvoegsel of Achtervoegsel toevoegen aan de bladnamen.
4. Klik op de OK knop. Zie screenshot:
   

Nu zijn de gegevens in het werkblad opgesplitst in meerdere werkbladen in een nieuw werkboek.

Gegevens opsplitsen in meerdere werkboeken op basis van kolomwaarde met VBA-code

Soms is het voordeliger om gegevens op te splitsen in afzonderlijke werkboeken in plaats van in meerdere werkbladen, gebaseerd op een sleutelkolom. Hier is een stap-voor-stap handleiding over hoe je VBA-code kunt gebruiken om het proces van het opsplitsen van gegevens in meerdere werkboeken te automatiseren op basis van een specifieke kolomwaarde.

1. Houd de toetsen ALT + F11 ingedrukt om het venster Microsoft Visual Basic for Applications te openen.

2. Klik op Invoegen > Module en plak de volgende code in het Modulevenster.

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. Druk vervolgens op F5 om de code uit te voeren, en er verschijnt een pop-upvenster waarin je wordt gevraagd de titelrij te selecteren. Klik daarna op OK. Zie screenshot:

4. Selecteer in het tweede pop-upvenster de kolomgegevens waarop je wilt splitsen en klik vervolgens op OK. Zie screenshot:

5. Na het splitsen zijn alle gegevens in het actieve werkblad verdeeld over meerdere werkboeken op basis van de kolomwaarden. Alle gesplitste werkboeken worden opgeslagen in de door jou gespecificeerde map. Zie screenshot:

Gerelateerde artikelen: