如何在Excel中將一個大表拆分為多個小表？

網友投稿 571 2025-03-31

如何在Excel中將一個大表拆分為多個小表？

現在，如果您有一個包含多列和成百上千行數據的大型工作表，則希望根據列值或行數將該大型表拆分為多個小型表，以獲得以下結果。您如何在Excel中處理此任務？

主表 按列值將表拆分為多個表 按行數將表拆分為多個表

使用VBA代碼根據列值將大表拆分為多個表

使用VBA代碼根據特定的行數將一個大表拆分為多個表

基于列值或行數將大型表拆分為多個表，具有驚人的功能

使用VBA代碼根據列值將大表拆分為多個表

若要根據特定的列值將此大表拆分為多個表，請使用以下VBA代碼。請這樣做：

1。按住 ALT + F11 鍵打開 Microsoft Visual Basic應用程序 窗口。

2。點擊插頁 > 模塊，然后將以下代碼粘貼到模塊窗口。

VBA代碼：按關鍵字列將一個大表拆分為多個表：

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。粘貼代碼后，按 F5 鍵運行此代碼，并彈出提示框，請從數據中選擇標題行，請參見屏幕截圖：

4。然后，點擊 OK，然后會彈出另一個對話框，請選擇要用于拆分表的列數據，請參見屏幕截圖：

5。點擊 OK，此大表已通過位于主表之后的列值拆分為多個工作表。并且新工作表使用列值命名。看截圖：

使用VBA代碼根據特定的行數將一個大表拆分為多個表

如果需要根據行數將表拆分為多個表，則以下VBA代碼可以為您提供幫助。

1。按住 ALT + F11 鍵打開 Microsoft Visual Basic應用程序 窗口。

2。點擊插頁 > 模塊，然后將以下代碼粘貼到模塊窗口。

VBA代碼：按行數將一個大表拆分為多個表：

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

Copy

3。然后按 F5 鍵，在彈出的對話框中，選擇標題行，請參見屏幕截圖：