Excel-tips: Splits gegevens op in meerdere werkbladen/werkmappen op basis van de kolomwaarde
Bij het beheren van grote gegevenssets in Excel kan het zeer nuttig zijn om gegevens op te splitsen in meerdere werkbladen op basis van specifieke kolomwaarden. Deze methode verbetert niet alleen de organisatie van gegevens, maar verbetert ook de leesbaarheid en vergemakkelijkt een eenvoudigere gegevensanalyse.
Stel dat u een groot verkooprecord heeft met meerdere vermeldingen, zoals de productnaam en de verkochte hoeveelheid van het eerste kwartaal. Het doel is om deze gegevens op te splitsen in afzonderlijke werkbladen op basis van elke productnaam, zodat de individuele verkoopprestaties afzonderlijk kunnen worden geanalyseerd.
Gegevens opsplitsen in meerdere werkbladen op basis van de kolomwaarde
Splits gegevens in meerdere werkmappen op basis van kolomwaarde met VBA-code
Gegevens opsplitsen in meerdere werkbladen op basis van de kolomwaarde
Normaal gesproken kunt u de gegevenslijst eerst sorteren en deze vervolgens één voor één kopiëren en in andere nieuwe werkbladen plakken. Maar dit vereist uw geduld om herhaaldelijk te kopiëren en plakken. In dit gedeelte introduceren we twee eenvoudige methoden om deze taak efficiënt aan te pakken in Excel, waardoor u tijd bespaart en de kans op fouten wordt verkleind.
Splits gegevens op in meerdere werkbladen op basis van kolomwaarde met VBA-code
1. Houd de toets ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
2. klikken Invoegen > Moduleen 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 F5 toets om de code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren de koprij te selecteren en vervolgens te klikken OK. Zie screenshot:
4. Selecteer in het tweede promptvenster de kolomgegevens waarop u wilt splitsen en klik vervolgens op OK. Zie screenshot:
5. Alle gegevens in het actieve werkblad zijn verdeeld in meerdere werkbladen op basis van de kolomwaarden. De resulterende werkbladen krijgen een naam op basis van de waarden in de gesplitste cellen en worden aan het einde van de werkmap geplaatst. Zie schermafbeelding:
Splits gegevens op in meerdere werkbladen op basis van kolomwaarde met Kutools voor Excel
Kutools for Excel brengt slimme functie – Gegevens splitsen rechtstreeks in uw Excel-omgeving. Gegevens opsplitsen in meerdere werkbladen is niet langer een uitdaging. Onze intuïtieve tool verdeelt uw dataset automatisch op basis van de gekozen kolomwaarde of het aantal rijen, zodat elk stukje informatie precies daar is waar u het nodig heeft. Zeg vaarwel tegen de vervelende taak van het handmatig organiseren van uw spreadsheets en omarm een snellere, foutloze manier om uw gegevens te beheren.
Na het installeren van Kutools for Excel, selecteer het gegevensbereik en klik vervolgens op Koetools Plus > Gegevens splitsen om de te openen Gegevens opsplitsen in meerdere werkbladen dialoog venster.
- kies Specifieke kolom optie in het Splitsen op basis van sectie en kies de kolomwaarde waarvan u de gegevens wilt splitsen op basis van de vervolgkeuzelijst.
- Als uw gegevens kopteksten hebben en u deze in elk nieuw gesplitst werkblad wilt invoegen, vinkt u dit aan Mijn gegevens hebben kopteksten keuze. (U kunt het aantal koptekstrijen opgeven op basis van uw gegevens. Als uw gegevens bijvoorbeeld twee kopteksten bevatten, typt u 2.)
- Vervolgens kunt u de namen van het gesplitste werkblad specificeren onder de Nieuwe werkbladnaam sectie, specificeer de regel voor werkbladnamen in de vervolgkeuzelijst Regels, u kunt de Voorvoegsel or Toevoeging ook voor de bladnamen.
- Klik op de OK knop. Zie screenshot:
Nu worden de gegevens in het werkblad opgesplitst in meerdere werkbladen in een nieuwe werkmap.
Splits gegevens in meerdere werkmappen op basis van kolomwaarde met VBA-code
Soms kan het, in plaats van gegevens in meerdere werkbladen te verdelen, voordeliger zijn om de gegevens in afzonderlijke werkmappen te splitsen op basis van een sleutelkolom. Hier vindt u een stapsgewijze handleiding over het gebruik van VBA-code om het proces van het splitsen van gegevens in meerdere werkmappen te automatiseren op basis van een specifieke kolomwaarde.
1. Houd de toets ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
2. klikken Invoegen > Moduleen plak de volgende code in het Module Venster.
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 toets om de code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren de koprij te selecteren en vervolgens te klikken OK. Zie screenshot:
4. Selecteer in het tweede promptvenster de kolomgegevens waarop u wilt splitsen en klik vervolgens op OK. Zie screenshot:
5. Na het splitsen worden alle gegevens in het actieve werkblad verdeeld in meerdere werkmappen op basis van de kolomwaarden. Alle gesplitste werkmappen worden opgeslagen in de map die u hebt opgegeven. Zie schermafbeelding:
Gerelateerde artikelen:
- Splits gegevens in meerdere werkbladen door het aantal rijen
- Door een groot gegevensbereik efficiënt te verdelen in meerdere Excel-werkbladen op basis van een specifiek aantal rijen, kan het gegevensbeheer worden gestroomlijnd. Als u een gegevensset bijvoorbeeld elke vijf rijen in meerdere werkbladen opsplitst, kan deze beter beheersbaar en georganiseerd worden. Deze handleiding biedt twee praktische methoden om deze taak snel en gemakkelijk uit te voeren.
- Voeg twee of meer tabellen samen tot één op basis van sleutelkolommen
- Stel dat u drie tabellen in een werkmap heeft, nu wilt u deze tabellen samenvoegen tot één tabel op basis van de overeenkomstige sleutelkolommen om het resultaat te krijgen zoals onderstaand screenshot. Dit kan voor de meesten van ons een lastige taak zijn, maar maak je geen zorgen, in dit artikel zal ik enkele methoden introduceren om dit probleem op te lossen.
- Splits tekstreeksen door scheidingsteken in meerdere rijen
- Normaal gesproken kunt u de functie Tekst naar kolom gebruiken om de celinhoud op te splitsen in meerdere kolommen met een specifiek scheidingsteken, zoals een komma, punt, puntkomma, schuine streep, enz. Maar soms moet u de gescheiden celinhoud opsplitsen in meerdere rijen en herhaal de gegevens uit andere kolommen zoals onderstaand screenshot getoond. Heeft u goede manieren om met deze taak in Excel om te gaan? Deze zelfstudie introduceert enkele effectieve methoden om deze taak in Excel te voltooien.
- Splits de celinhoud van meerdere regels in gescheiden rijen/kolommen
- Stel dat u celinhoud met meerdere regels hebt die wordt gescheiden door Alt + Enter, en nu moet u de inhoud van meerdere regels splitsen in gescheiden rijen of kolommen, wat kunt u dan doen? In dit artikel leert u hoe u snel celinhoud van meerdere regels kunt splitsen in gescheiden rijen of kolommen.
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
