Mẹo Excel: Chia dữ liệu thành nhiều bảng tính/sổ làm việc dựa trên giá trị cột
Khi quản lý các tập dữ liệu lớn trong Excel, việc chia dữ liệu thành nhiều trang tính dựa trên các giá trị cột cụ thể có thể rất có lợi. Phương pháp này không chỉ cải thiện việc tổ chức dữ liệu mà còn tăng cường khả năng đọc và tạo điều kiện cho việc phân tích dữ liệu dễ dàng hơn.
Giả sử bạn có một bản ghi bán hàng lớn chứa nhiều mục như tên sản phẩm, số lượng bán ra trong quý đầu tiên. Mục tiêu là chia dữ liệu này thành các bảng tính riêng biệt dựa trên từng tên sản phẩm để có thể phân tích hiệu suất bán hàng riêng lẻ.
Chia dữ liệu thành nhiều bảng tính dựa trên giá trị cột
Chia dữ liệu thành nhiều sổ làm việc dựa trên giá trị cột bằng mã VBA
Chia dữ liệu thành nhiều bảng tính dựa trên giá trị cột
Thông thường, bạn có thể sắp xếp danh sách dữ liệu trước, sau đó sao chép và dán từng dữ liệu vào các bảng tính mới khác. Nhưng việc này sẽ cần sự kiên nhẫn của bạn để sao chép và dán nhiều lần. Trong phần này, chúng tôi sẽ giới thiệu hai phương pháp đơn giản để giải quyết hiệu quả tác vụ này trong Excel, giúp bạn tiết kiệm thời gian và giảm khả năng xảy ra lỗi.
Chia dữ liệu thành nhiều bảng tính dựa trên giá trị cột bằng mã VBA
1. Nhấn giữ ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.
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. Sau đó, nhấn F5 để chạy mã và một hộp nhắc xuất hiện để nhắc bạn chọn hàng tiêu đề, sau đó bấm vào OK. Xem ảnh chụp màn hình:
4. Trong hộp nhắc thứ hai, vui lòng chọn dữ liệu cột mà bạn muốn phân chia dựa trên đó, sau đó nhấp vào OK. Xem ảnh chụp màn hình:
5. Tất cả dữ liệu trong bảng tính đang hoạt động được chia thành nhiều bảng tính dựa trên các giá trị cột. Các bảng tính kết quả được đặt tên theo các giá trị trong các ô được phân tách và được đặt ở cuối sổ làm việc. Xem ảnh chụp màn hình:
Chia dữ liệu thành nhiều bảng tính dựa trên giá trị cột với Kutools for Excel
Kutools cho Excel mang đến tính năng thông minh – Dữ liệu phân tách ngay vào môi trường Excel của bạn. Việc chia dữ liệu thành nhiều trang tính không còn là một thách thức nữa. Công cụ trực quan của chúng tôi tự động phân chia tập dữ liệu của bạn dựa trên giá trị cột hoặc số hàng đã chọn, đảm bảo rằng mỗi phần thông tin đều ở chính xác nơi bạn cần. Hãy tạm biệt công việc tẻ nhạt là sắp xếp bảng tính theo cách thủ công và áp dụng cách quản lý dữ liệu nhanh hơn, không có lỗi.
Sau khi cài đặt Kutools cho Excel, chọn phạm vi dữ liệu rồi bấm vào Kutools Plus > Dữ liệu phân tách để mở Tách dữ liệu thành nhiều bảng tính hộp thoại.
- Chọn Cột cụ thể tùy chọn trong Phân chia dựa trên và chọn giá trị cột mà bạn muốn phân tách dữ liệu dựa trên danh sách thả xuống.
- Nếu dữ liệu của bạn có tiêu đề và bạn muốn chèn chúng vào mỗi bảng tính phân chia mới, vui lòng kiểm tra Dữ liệu của tôi có tiêu đề lựa chọn. (Bạn có thể chỉ định số hàng tiêu đề dựa trên dữ liệu của mình. Ví dụ: nếu dữ liệu của bạn chứa hai tiêu đề, vui lòng nhập 2.)
- Sau đó, bạn có thể chỉ định các tên bảng tính đã tách, trong Tên trang tính mới phần, chỉ định quy tắc tên bảng tính từ danh sách thả xuống Quy tắc, bạn có thể thêm quy tắc Tiếp đầu ngữ or suffix cho cả tên trang tính.
- Nhấn vào OK cái nút. Xem ảnh chụp màn hình:
Bây giờ, dữ liệu trong bảng tính được chia thành nhiều bảng tính trong sổ làm việc mới.
Chia dữ liệu thành nhiều sổ làm việc dựa trên giá trị cột bằng mã VBA
Đôi khi, thay vì chia dữ liệu thành nhiều trang tính, việc chia dữ liệu thành các sổ làm việc riêng biệt dựa trên một cột khóa có thể sẽ có ích hơn. Dưới đây là hướng dẫn từng bước về cách sử dụng mã VBA để tự động hóa quá trình chia dữ liệu thành nhiều sổ làm việc dựa trên một giá trị cột cụ thể.
1. Nhấn giữ ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. nhấp chuột Chèn > Mô-đunvà dán mã sau vào Cửa sổ mô-đun.
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. Sau đó, nhấn F5 để chạy mã và một hộp nhắc xuất hiện để nhắc bạn chọn hàng tiêu đề, sau đó bấm vào OK. Xem ảnh chụp màn hình:
4. Trong hộp nhắc thứ hai, vui lòng chọn dữ liệu cột mà bạn muốn phân chia dựa trên đó, sau đó nhấp vào OK. Xem ảnh chụp màn hình:
5. Sau khi chia tách, tất cả dữ liệu trong bảng tính đang hoạt động sẽ được chia thành nhiều sổ làm việc dựa trên các giá trị cột. Tất cả các sổ làm việc được chia sẽ được lưu vào thư mục bạn đã chỉ định. Xem ảnh chụp màn hình:
Bài viết liên quan:
- Chia dữ liệu thành nhiều trang tính theo số hàng
- Việc phân chia hiệu quả một phạm vi dữ liệu lớn thành nhiều trang tính Excel dựa trên số hàng cụ thể có thể hợp lý hóa việc quản lý dữ liệu. Ví dụ: chia tập dữ liệu cứ 5 hàng thành nhiều trang có thể giúp tập dữ liệu dễ quản lý và có tổ chức hơn. Hướng dẫn này cung cấp hai phương pháp thực tế để hoàn thành nhiệm vụ này một cách nhanh chóng và dễ dàng.
- Hợp nhất hai hoặc nhiều bảng thành một dựa trên các cột chính
- Giả sử bạn có ba bảng trong một sổ làm việc, bây giờ, bạn muốn hợp nhất các bảng này thành một bảng dựa trên các cột chính tương ứng để nhận được kết quả như hình minh họa bên dưới. Đây có thể là một nhiệm vụ rắc rối đối với hầu hết chúng ta, nhưng, xin đừng lo lắng, bài viết này, tôi sẽ giới thiệu một số phương pháp để giải quyết vấn đề này.
- Tách chuỗi văn bản bằng dấu phân cách thành nhiều hàng
- Thông thường, bạn có thể sử dụng tính năng Văn bản thành Cột để chia nội dung ô thành nhiều cột bằng một dấu phân cách cụ thể, chẳng hạn như dấu phẩy, dấu chấm, dấu chấm phẩy, dấu gạch chéo, v.v. Tuy nhiên, đôi khi, bạn có thể cần chia nội dung ô được phân tách thành nhiều hàng và lặp lại dữ liệu từ các cột khác như ảnh chụp màn hình bên dưới. Bạn có cách nào tốt để xử lý tác vụ này trong Excel không? Hướng dẫn này sẽ giới thiệu một số phương pháp hiệu quả để hoàn thành công việc này trong Excel.
- Chia nội dung ô nhiều dòng thành các hàng/cột riêng biệt
- Giả sử bạn có nội dung ô nhiều dòng được phân tách bằng Alt + Enter và bây giờ bạn cần chia nội dung nhiều dòng thành các hàng hoặc cột được tách biệt, bạn có thể làm gì? Trong artical này, bạn sẽ học cách nhanh chóng chia nội dung ô nhiều dòng thành các hàng hoặc cột riêng biệt.
Công cụ năng suất văn phòng tốt nhất
Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian. Bấm vào đây để có được tính năng bạn cần nhất...
Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
