Excel-tips: Gegevens opsplitsen naar meerdere werkbladen / werkmappen op basis van kolomwaarde
Bij het beheren van grote datasets in Excel kan het zeer nuttig zijn om Gegevens opsplitsen naar meerdere werkbladen op basis van Specificeer kolom-waarden. Deze methode verbetert niet alleen de organisatie van gegevens, maar verhoogt ook de leesbaarheid en vergemakkelijkt Data-analyse.
Stel dat u een uitgebreid verkoopoverzicht heeft met meerdere vermeldingen, zoals productnaam en de verkochte hoeveelheid in het eerste kwartaal. Het doel is om deze gegevens te splitsen in afzonderlijke werkbladen per productnaam, zodat u de individuele verkoopprestaties gericht kunt analyseren.
Gegevens opsplitsen naar meerdere werkbladen op basis van kolomwaarde
Gegevens opsplitsen in meerdere werkmappen op basis van kolomwaarde met VBA-code
Gegevens opsplitsen naar meerdere werkbladen op basis van kolomwaarde
Normaal gesproken sorteert u eerst de gegevenslijst en kopieert en plakt u de gegevens vervolgens één voor één naar een nieuw werkblad. Dit vraagt echter veel geduld door het herhaaldelijk kopiëren en plakken. In deze sectie presenteren we twee eenvoudige methoden om deze taak efficiënt aan te pakken in Excel, zodat u tijd bespaart en fouten voorkomt.
Gegevens opsplitsen naar 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 Sub3. Druk vervolgens op de toets F5 om de code uit te voeren. Er verschijnt een dialoogvenster waarin u wordt gevraagd de koprij te selecteren; klik daarna op OK. Zie schermafbeelding:
4. Selecteer in het tweede dialoogvenster de kolomgegevens die u als opsplitsingsbasis wilt gebruiken en klik vervolgens op OK. Zie schermafbeelding:
5. Alle gegevens in het actieve werkblad worden verdeeld over meerdere werkbladen op basis van de kolomwaarden. De resulterende werkbladen krijgen de namen van de waarden in de te splitsen cellen en worden achteraan in de werkmap ingevoegd. Zie schermafbeelding:
Gegevens opsplitsen naar meerdere werkbladen op basis van kolomwaarde met Kutools voor Excel
Kutools voor Excel biedt een slimme functie – Gegevens opsplitsen – direct in uw Excel-omgeving. Het splitsen van gegevens over meerdere werkbladen is geen uitdaging meer! Onze intuïtieve tool verdeelt uw dataset automatisch op basis van de gekozen kolomwaarde of het aantal rijen, zodat elke gegevensset precies op de juiste plek terechtkomt. Zeg vaarwel tegen het tijdrovende handmatig organiseren van uw spreadsheets en kies voor een snellere, foutloze manier om uw gegevens te beheren.
Na installatie van Kutools voor Excel selecteert u het gegevensbereik en klikt u vervolgens op KUTOOLS PLUS > Gegevens opsplitsen om het dialoogvenster Gegevens opsplitsen naar meerdere werkbladen te openen.
- Selecteer de optie Specificeer kolom in het gedeelte Opsplitsingsbasis en kies in de keuzelijst de kolomwaarde waarmee u de gegevens wilt splitsen.
- Als uw gegevens koppen bevatten en u wilt dat deze koppen in elk nieuw gesplitst werkblad worden opgenomen, vink dan de optie Titels opnemen aan. (U kunt het aantal titelrijen instellen op basis van uw gegevens. Bevatten uw gegevens bijvoorbeeld twee rijen met koppen? Typ dan 2.)
- Vervolgens kunt u de splitsregel Werkbladnaam opgeven. Selecteer onder het gedeelte Naam van aangemaakte werkbladen de regel Werkbladnaam via de vervolgkeuzelijst Regels. U kunt ook een Voorvoegsel of Achtervoegseltoevoegen aan de bladnamen.
- Klik op de knop OK. Zie de schermafbeelding:
De gegevens in het werkblad zijn nu gesplitst in meerdere werkbladen in een Nieuw werkblad.
Gegevens opsplitsen in meerdere werkmappen op basis van kolomwaarde met VBA-code
Soms is het voordeliger om gegevens niet in meerdere werkbladen, maar in afzonderlijke werkmappen te splitsen op basis van een Sleutelkolom. Hier volgt een stapsgewijze handleiding voor het gebruik van VBA-code om het proces van het splitsen van gegevens in meerdere werkmappen op basis van een Specificeer kolom-waarde te automatiseren.
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 de toets F5 om de code uit te voeren. Er verschijnt een dialoogvenster waarin u wordt gevraagd de koprij te selecteren; klik daarna op OK. Zie schermafbeelding:
4. Selecteer in het tweede dialoogvenster de kolomgegevens die u als basis voor de opsplitsing wilt gebruiken en klik vervolgens op OK. Zie schermafbeelding:
5. Na het splitsen worden alle gegevens in het actieve werkblad verdeeld over meerdere werkmappen op basis van de kolomwaarden. Alle gesplitste werkmappen worden opgeslagen in de map die u hebt opgegeven. Zie schermafbeelding:
Gerelateerde artikelen:
- Gegevens opsplitsen naar meerdere werkbladen op rijenaantal
- Het efficiënt verdelen van een grote Gegevensbereik in meerdere Excel-werkmappen op basis van een specifiek aantal rijen kan gegevensbeheer stroomlijnen. Bijvoorbeeld: het splitsen van een dataset om de 5 rijen over meerdere bladen maakt de gegevens overzichtelijker en beter georganiseerd. Deze handleiding biedt twee praktische methoden om deze taak snel en eenvoudig uit te voeren.
- Twee of meer tabellen samenvoegen tot één op basis van Sleutelkolom
- Stel dat u drie tabellen hebt in een werkmap en u deze tabellen nu wilt samenvoegen tot één tabel op basis van de bijbehorende Sleutelkolom, zodat u het resultaat krijgt zoals in onderstaande schermafbeelding. Dit kan voor de meesten van ons een lastige taak zijn, maar maak u geen zorgen: in dit artikel introduceer ik enkele methoden om dit probleem op te lossen.
- Tekstreeksen splitsen op scheidingsteken in meerdere rijen
- Normaal gesproken kunt u de functie Tekst naar kolommen gebruiken om celinhoud te splitsen in meerdere kolommen met een specifiek scheidingsteken, zoals komma, punt, puntkomma, slash, enzovoort. Maar soms moet u de inhoud van een cel met scheidingstekens splitsen in meerdere rijen en tegelijkertijd de gegevens uit andere kolommen herhalen, zoals in de onderstaande schermafbeelding. Hebt u hiervoor goede oplossingen in Excel? In deze handleiding worden enkele effectieve methoden beschreven om deze taak in Excel uit te voeren.
- Inhoud van meerdere regels in cellen splitsen in afzonderlijke rijen/kolommen
- Stel dat u celinhoud met meerdere regels hebt, gescheiden door Alt + Enter, en u deze inhoud nu wilt splitsen in afzonderlijke rijen of kolommen. Wat kunt u dan doen? In dit artikel leert u hoe u snel inhoud met meerdere regels in cellen kunt splitsen in afzonderlijke rijen of kolommen.
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins