Hoe een grote tabel in meerdere kleine tabellen splitsen in Excel?

Als u een grote Excel-tabel heeft met meerdere kolommen en honderden of duizenden rijen, wilt u deze mogelijk opsplitsen in kleinere, beheersbaardere tabellen. U kunt bijvoorbeeld gegevens willen scheiden op basis van specifieke kolomwaarden of deze in stukken van een vast aantal rijen willen verdelen. Deze handleiding biedt stapsgewijze methoden om deze taak efficiënt uit te voeren.

Hoofdtabel		Tabel opsplitsen in meerdere tabellen op basis van kolomwaarde	Tabel opsplitsen in meerdere tabellen op basis van aantal rijen

Een grote tabel opsplitsen in meerdere tabellen op basis van kolomwaarde met VBA-code

Een grote tabel opsplitsen in meerdere tabellen op basis van een specifiek aantal rijen met VBA-code

Een grote tabel opsplitsen in meerdere tabellen op basis van kolomwaarde of aantal rijen met Kutools voor Excel

Een grote tabel opsplitsen in meerdere tabellen op basis van kolomwaarde met VBA-code

Om deze grote tabel op te splitsen in meerdere tabellen op basis van een specifieke kolomwaarde, kan de volgende VBA-code u helpen. Volg deze stappen:

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 "Module"-venster.

VBA-code: Een grote tabel opsplitsen in meerdere tabellen op basis van sleutelkolom:

Sub Splitdatabycol()
'by 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
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
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").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. Nadat u de code hebt geplakt, drukt u op de toets "F5" om de code uit te voeren, en er verschijnt een promptvenster waarin u de koptekstrij uit uw gegevens moet selecteren, zie schermafbeelding:

4. Klik vervolgens op "OK", en er verschijnt een ander dialoogvenster waarin u de kolomgegevens selecteert waarop u de tabel wilt splitsen, zie schermafbeelding:

5. Klik op "OK". De grote tabel is nu opgesplitst in meerdere werkbladen op basis van kolomwaarden, waarbij de nieuwe werkbladen zich na het hoofdwerkblad bevinden. De nieuwe werkbladen zijn genoemd naar de kolomwaarden. Zie schermafbeelding:

Een grote tabel opsplitsen in meerdere tabellen op basis van een specifiek aantal rijen met VBA-code

Als u de tabel in meerdere tabellen moet splitsen op basis van het aantal rijen, kan de volgende VBA-code u helpen.

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 "Module"-venster.

VBA-code: Een grote tabel opsplitsen in meerdere tabellen op basis van aantal rijen:

Sub Splitdatabyrows()
'Updated by Extendoffice 
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
Dim xTRg As Range
Dim xNTRg As Range
Dim xIER
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set xTRg = Application.InputBox("Please select the header row:", xTitleId, "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set WorkRng = Application.InputBox("Please select the data range(exclude the header row):", xTitleId, WorkRng.Address, Type:=8)
If TypeName(WorkRng) = "Nothing" Then Exit Sub
SplitRow = Application.InputBox("Split Row Num", xTitleId, Type:=1)
If SplitRow = 0 Then Exit Sub
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
xIER = WorkRng.Rows.Count
xIER = WorkRng.Row + xIER - 1
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (xIER - xRow.Row + 1) < SplitRow Then
        resizeCount = (xIER - xRow.Row + 1)
    End If
    xRow.Resize(resizeCount).Copy
    Set xWs = Application.Worksheets.Add(after:=Application.Worksheets(Application.Worksheets.Count))
    If xIER > (xRow.Row + SplitRow - 1) Then
        xWs.Name = xRow.Row & " - " & (xRow.Row + SplitRow - 1)
    ElseIf xIER = xRow.Row Then
        xWs.Name = xRow.Row
    Else
        xWs.Name = xRow.Row & " - " & xIER
    End If
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xNTRg = Application.ActiveSheet.Range("A1")
    xTRg.Copy
    xNTRg.Insert
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Druk vervolgens op de toets "F5", in het pop-upvenster selecteert u de koptekstrij, zie schermafbeelding:

4. Klik vervolgens op "OK", en in het tweede promptvenster selecteert u het gegevensbereik dat u wilt splitsen op basis van het aantal rijen, zie schermafbeelding:

5. Klik vervolgens op de knop "OK", in het derde promptvenster voert u het aantal rijen in waarop u wilt splitsen, zie schermafbeelding:

6. Klik vervolgens op de knop "OK", de hoofdtabel is nu opgesplitst in meerdere werkbladen op basis van het aantal rijen zoals in onderstaande schermafbeelding te zien is:

Een grote tabel opsplitsen in meerdere tabellen op basis van kolomwaarde of aantal rijen met Kutools voor Excel

De bovenstaande codes kunnen voor de meeste gebruikers lastig zijn, hier introduceer ik een geweldige functie - "Gegevens opsplitsen" van "Kutools voor Excel". Met dit hulpprogramma kunt u een grote tabel snel en gemakkelijk opsplitsen in meerdere tabellen op basis van een sleutelkolom of aantal rijen.

1. Selecteer het gegevensbereik dat u wilt splitsen, en klik vervolgens op "Kutools Plus" > "Gegevens opsplitsen", zie schermafbeelding:

2. Specificeer in het dialoogvenster "Gegevens opsplitsen naar meerdere werkbladen" de instellingen naar behoefte:

(1.) Selecteer "Specifieke kolom" of "Vaste waarde" in de sectie "Opsplitsingsbasis" zoals u nodig hebt;

(2.) Specificeer de naam van de nieuwe werkbladen uit de vervolgkeuzelijst "Regel", u kunt ook een "Voorvoegsel" of "Achtervoegsel" aan de bladnamen toevoegen.

3. Klik vervolgens op de knop "Ok", en nu is de grote tabel opgesplitst in meerdere kleine tabellen in een nieuw werkboek. Zie schermafbeeldingen:

Tabel opsplitsen in meerdere tabellen op basis van kolomwaarde	Tabel opsplitsen in meerdere tabellen op basis van aantal rijen

Meer gerelateerde artikelen: