提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何根據Excel中的列將數據拆分為多個工作表?

假設你有一個包含大量數據的工作表,現在,你需要根據數據拆分成多個工作表 名稱 列(請參見以下屏幕快照),並且名稱是隨機輸入的。 也許你可以先對它們進行排序,然後將它們逐個複制並粘貼到其他新的工作表中。 但這需要你耐心的重複複製和粘貼。 今天,我會談談一些快速的技巧來解決這個任務。

doc按列1分割數據

使用VBA代碼將數據分割成多個基於列的工作表

使用Kutools for Excel將數據分割成多個基於列的工作表


根據工作表中的特定列或行數將數據拆分為多個工作表:

如果您想根據特定列數據或行數將大工作表分成多個工作表, Kutools for Excel's 拆分數據 功能可以幫助您快速輕鬆地解決此任務。

doc按列6分割數據

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


使用VBA代碼將數據分割成多個基於列的工作表


如果您想快速自動地根據列值拆分數據,以下VBA代碼是一個不錯的選擇。 請這樣做:

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,並將以下代碼粘貼到模塊窗口中。

Sub Splitdatabycol()
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 鍵運行代碼,彈出提示框提醒您選擇標題行,見截圖:

doc按列7分割數據

4。 然後點擊 OK 按鈕,在第二個提示框中,請選擇要拆分的列數據,請參見屏幕截圖:

doc按列8分割數據

5。 然後,點擊 OK,並且活動工作表中的所有數據都按列值拆分為多個工作表。 拆分工作表以拆分單元名稱命名。 看截圖:

doc按列2分割數據

注意:拆分工作表放置在主工作表所在工作簿的末尾。


使用Kutools for Excel將數據分割成多個基於列的工作表

作為一名Excel初學者,這個長的VBA代碼對我們來說有些困難,而且我們大多數人甚至不知道如何根據需要修改代碼。

在這裡,我會介紹一個多功能工具 - Kutools for Excel,其 拆分數據 實用程序不僅可以幫助您根據列將數據拆分為多個工作表,還可以按行數拆分數據。

Kutools for Excel : 與超過300方便的Excel加載項,在60天免費試用沒有限制.

如果你已經安裝 Kutools for Excel請按照以下步驟進行:

1。 選擇要分割的數據范圍。

2。 點擊 Kutools Plus > 工作表 > 拆分數據,看截圖:

doc按列3分割數據

3。 在 將數據拆分為多個工作表 對話框中,您需要:

1)。 選擇 專欄 在選項 基於分割 部分,然後從下拉列表中選擇要分割數據的列值。 (如果您的數據包含標題,並且您希望將它們插入到每個新的拆分工作表中,請檢查 我的數據有標題 選項。)

2)。 然後,您可以指定拆分工作表名稱 新的工作表名稱 部分,從中指定工作表名稱規則 規則 下拉列表中,您可以添加 字首 or 後綴 也是表格名稱。

3)。 點擊 OK 按鈕。 看截圖:

doc按列4分割數據

4。 現在,數據在新的工作簿中被分成多個工作表。

doc按列5分割數據

點擊下載Kutools for Excel和免費試用版吧!


使用Kutools for Excel將數據分割成多個基於列的工作表

Kutools for Excel 包含了比300更方便的Excel工具。 免費試用60天無限制。 立即下載免費試用!


相關文章:

如何按行數將數據拆分為多個工作表?



推薦的Excel生產力工具

Kutools for Excel幫助您提前完成工作,並從人群中脫穎而出

  • 超過300強大的高級功能,專為1500工作場景設計,通過70%提高生產力,讓您有更多時間照顧家庭和享受生活。
  • 不再需要記憶公式和VBA代碼,從現在起讓你的大腦休息一下。
  • 成為3分鐘的Excel專家,複雜和重複的操作可以在幾秒鐘內完成,
  • 每天減少成千上萬的鍵盤和鼠標操作,現在告別職業病。
  • 110,000高效人才和300 +世界知名公司的選擇。
  • 60-day full功能免費試用。 60天退款保證。 2多年的免費升級和支持。

將選項卡式瀏覽和編輯帶到Microsoft Office,遠比瀏覽器的選項卡強大

  • Office選項卡專為Word,Excel,PowerPoint和其他Office應用程序設計:Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Bernhard · 24 days ago
    Thank you, worked excellently!
  • To post as a guest, your comment is unpublished.
    Degardt · 25 days ago
    This code is brilliant. It does exactly what I need. Only, I don't know how to change the code so that the data in the rows, all starting with the same letter, should go on the same worksheet, instead of each getting its own worksheet. I have 1000 rows of data, some starting with "L", or "K"or "F" and then a number. It sorts the data each to its own sheet, I want all the "L" cells' data on one sheet and all the"K" data on one sheet. Can someone please help me. I don't underwstand coding that good
    • To post as a guest, your comment is unpublished.
      MP · 7 days ago
      Add a column and pull the first character from the cell with L+ the number or K+ the number. Assume your key is in cell A2, use the formula =left(A2,1) to pull the first character into your new column. Use this column to separate data to its own sheet.
  • To post as a guest, your comment is unpublished.
    Aaron · 1 months ago
    Why does the VBA create new sheets with columns all the way to XFD when my main sheet only goes to AK?
  • To post as a guest, your comment is unpublished.
    ShiroKuro · 2 months ago
    how do i make this work on around 150k rows count. Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 1 months ago
      Hello, ShiroKuro,
      If there are large data in your worksheet, I recommend Kutools for Excel's Split Data feature for you, you can download it and free trial 60-day!
      Please try, thank you!
  • To post as a guest, your comment is unpublished.
    Aditya Bhatnagar · 3 months ago
    Great script! Could someone please help as I need to just add "Class-C" at the end of each new worksheet's name that is created after running this. For Eg. Lucy-Class C; Emily Class C; and so on. Would really appreciate your help here.
    • To post as a guest, your comment is unpublished.
      Me · 1 months ago
      just add a column and concatenate the Name and the "-Class C" field and use that as the column to split on instead of the Name column, then you can hide the column if you want
    • To post as a guest, your comment is unpublished.
      skyyang · 2 months ago
      Hello, Aditya,
      Here, I recommend Kutools for Excel's Split Data feature for you, you can add the prefix or suffix text easily in the dialog box as you need.
      You can download the tool and free trial 60-day.
      Please try, thank you!