Excel 技巧：依欄位值將分割數據至多個工作表／活頁簿分割

作者Xiaoyang修改日期2024-08-01

在 Excel 中管理大型資料集時，依指定列的值將數據分割至多個工作表，不僅大幅提升資料的組織性與可讀性，更讓後續的數據分析更加輕鬆高效。

假設您擁有一份龐大的銷售記錄，其中包含多筆項目，例如產品名稱與第一季的銷售數量。目標是根據各產品名稱，將這份資料分割成獨立的工作表，以便針對每個產品的銷售表現進行個別分析。

分割數據至多個工作表（依欄位值）

使用 VBA 程式碼
使用強大功能－Kutools for Excel

使用 VBA 程式碼將分割數據依欄位值分割成多個活頁簿

根據欄位值將資料分割至多個工作表

分割數據至多個工作表（依欄位值）

通常，您可以先將資料清單排序，再逐一複製貼上到其他新工作表。然而，這種方法需反覆操作，相當耗費耐心。本節將介紹兩種更簡便的方法，助您在 Excel 中高效完成此任務，不僅節省時間，還能降低出錯風險。

使用 VBA 程式碼將分割數據至多個工作表依欄位值分割

1. 按住 ALT + F11 鍵，即可立即開啟 Microsoft Visual Basic for Applications 視窗！

2. 按一下插入＞模組，並將下列程式碼貼到模組視窗中。

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. 接著按下 F5 鍵執行程式碼，系統將彈出提示框，提醒您選取標題列，請按一下確定。請參閱下方截圖：
使用 VBA 程式碼分割資料至工作表以選取標題列

4. 在第二個提示框中，請選擇您要作為分割依據的欄位資料，然後點選確定。請參閱下方截圖：
使用 VBA 程式碼分割資料至工作表以選取資料範圍

5. 作用中工作表的所有資料已根據欄位值分割為多個工作表。新產生的工作表將以分割儲存格中的值命名，並置於活頁簿的末尾。請參閱截圖：
使用 VBA 程式碼分割資料至工作表以取得結果

使用 Kutools for Excel 將分割數據至多個工作表依欄位值分割

Kutools for Excel 為您的 Excel 環境注入智慧功能－分割數據！將資料快速分割成多個工作表，從此不再是難題。我們直覺化的工具能根據您選取的欄位值或列數，自動精準分割資料集，確保每筆資訊都正確無誤。立即告別手動整理試算表的繁瑣流程，擁抱更快速、零錯誤的高效資料管理方式！

注意：若要套用此 分割數據，首先應下載 Kutools for Excel，即可快速輕鬆地使用此功能。

安裝 Kutools for Excel 後，選取資料區域，然後點擊 KUTOOLS PLUS＞ 分割數據，即可開啟分割數據至多個工作表對話方塊。

在分割依據區段中，選取指定列選項，並從下拉列表選擇您要用來分割資料的欄位值。
若您的資料包含標題列，並希望將其插入至每個新分割的工作表中，請勾選包含標題選項。（您可依資料指定標題列的數量；例如，若資料包含兩列標題，請輸入 2。）
接著，您可在創建工作表的名稱區段指定分割後的工作表名稱：從規則下拉式清單中選取工作表名稱規則，並可為工作表名稱新增前置字元或後置字元。
點擊確定按鈕。請參閱下方截圖：

現在，工作表中的資料已根據新工作簿分割為多個工作表。
使用 Kutools 分割資料至工作表以取得結果

使用 VBA 程式碼將分割數據依欄位值分割成多個活頁簿

有時，與其將資料分散在多個工作表中，不如依據關鍵欄位的值，將資料自動分割成多個獨立活頁簿來得更實用。以下逐步說明如何運用 VBA 程式碼，依指定欄位的值自動將資料拆分至不同活頁簿。

1. 按住 ALT + F11 鍵以開啟 Microsoft Visual Basic for Applications 視窗。

2. 按一下插入＞模組，並將下列程式碼貼到模組視窗中。

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

注意：在上述程式碼中，您應將檔案路徑修改為您自己的路徑，以在此指令碼中儲存分割工作簿：savePath = "C:\Users\AddinsVM001\Desktop\multiple files\"。

3. 接著按下 F5 鍵執行程式碼，系統將彈出提示框，提醒您選取標題列，請按一下確定。詳情請參閱下方截圖：
使用 VBA 程式碼分割資料至活頁簿以選取標題列

4. 在第二個提示框中，請選擇您要作為分割依據的欄位資料，然後按一下確定。請參閱截圖：
使用 VBA 程式碼分割資料至活頁簿以選取資料範圍

5. 分割完成後，作用中工作表的所有資料已根據欄位值拆分為多個活頁簿，並全部儲存至您指定的資料夾中。請參閱截圖：
使用 VBA 程式碼分割資料至活頁簿以取得結果

分割數據至多個工作表（依列數）
將大型數據區域依特定列數有效分割成多個 Excel 工作表，有助於簡化資料管理。例如，每 5 列將資料集分割至多個工作表，不僅讓資料更易管理，也更加井然有序。本指南提供兩種實用方法，助您快速輕鬆完成此任務。

根據關鍵列合併兩個或多個表格為一個
假設您的活頁簿中包含三個表格，現在希望根據對應的關鍵欄位將它們合併成單一表格，以獲得如下方截圖所示的結果。這對多數人來說或許頗具挑戰性，但別擔心！本文將為您介紹幾種實用的方法，輕鬆搞定這項任務。

依分隔符號將文字字串分割成多列
一般情況下，您可以使用「文字分列」功能，透過特定分隔符號（例如逗號、句點、分號、斜線等）將儲存格內容分割成多欄。但有時您可能需要將含分隔符號的儲存格內容拆分成多列，同時重複其他欄位的資料（如下方截圖所示）。您是否正在尋找在 Excel 中高效處理此任務的方法？本教學將為您介紹幾種實用技巧，助您輕鬆完成這項工作。

將多行儲存格內容分割成獨立的列或欄
假設您的儲存格內容包含以 Alt + Enter 分隔的多行文字，該如何將這些內容快速分割成獨立的列或欄？本文將教您輕鬆完成這項操作！

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多