Giả sử có một bảng dữ liệu trong Excel và giờ ta muốn tách dữ liệu trong bảng đó thành nhiều bảng theo một giá trị nào đó như dưới đây:
Để thực hiện tách bảng như trên, thì ta có thể sử dụng các cách sau
- Cách 1: Sử dụng công thức hàm FILTER để tiến hành tách.
- Cách 2: Sử dụng mã VBA để tự động tách hàm.
Sau đây mình sẽ hướng dẫn chi tiết từng cách để tách bảng thành nhiều bảng theo dữ liệu trong cột:
Cách 1: Sử dụng công thức với hàm FILTER
Ví dụ ta có một bảng dữ liệu dài như này với số liệu rất khó để theo dõi:
Giờ ta muốn tách bảng dữ liệu trên thành dữ liệu theo từng năm, thì ta sẽ thực hiện như sau:
Bước 1: Vì trên bảng dữ liệu trên chưa có sẵn giá trị cột theo năm, nên ta sẽ tạo thêm cột phụ “Year” để tách lấy năm từ dữ liệu cột Date (ở cột A) với hàm Year với công thức như sau: =YEAR(A2)
Sau đó ta sẽ được bảng như sau:
Bước 2: Giờ ta sẽ cần phải lọc lấy giá trị duy nhất trong cột Years vừa tạo bằng hàm UNIQUE. Mục đích việc này để tạo ra giá trị để lát dùng trong hàm FILTER sử dụng để tách bảng lúc sau:
Sau đây ta sẽ xử lý một chút để phân thành các khu vực để lát ta sẽ tách bảng theo các giá trị năm.
Bước 3: Giờ ta sẽ sử dụng hàm FILTER để tiến hành tách bảng dữ liệu trên thành nhiểu bảng dữ liệu theo từng năm như sau:
- Công thức chung hàm FILTER:
=FILTER(array;include;[if_emty]) - Áp dụng cho ví dụ trên, ví dụ ta cần lấy dữ liệu trong mảng array là
A2:C168với include là trong giá trị trong cộtD2:D168="giá trị năm". Lưu ý các mảng dữ liệu trong công thức ta sẽ khóa để tránh lệch khi kéo công thức. Lúc này ta sẽ sử dụng hàm FILTER để tách như sau:=FILTER($A$2:$C$168;$D$2:$D$168=G1)trong đó G1 là chứa số năm 2014.
Kết quả ta sẽ tách được bảng dữ liệu gốc ban đầu thành 1 bảng dữ liệu dành riêng cho năm 2014 như sau:
Giờ ta sẽ kéo hàm sang tương tự cho các năm khác:
Như vậy là ta đã tách hoàn toàn nhiều bảng theo giá trị năm từ một bảng dữ liệu gốc.
Bạn có thể xem Video dưới đây để theo dõi cách làm chi tiết:
Cách 2: Sử dụng mã VBA để tách bảng thành nhiều bảng tự động
Cách 1 sử dụng hàm để tách bảng dữ liệu phải làm thủ công và nhiều thời gian, để tự động ta có thể sử dụng mã VBA như sau:
Ta thực hiện với bảng dữ liệu về danh sách nhân viên như sau:
Giờ ta muốn tách bảng dữ liệu theo cột Employee ID để lấy dữ liệu theo từng nhân viên, thì làm như sau:
Bước 1: Ta cần biến bảng dữ liệu trên thành bảng bằng cách bôi đen toàn bộ dữ liệu bảng gốc => rồi nhấn tổ hợp phím Ctrl + T
Bước 2: Sau đó ta sẽ nhấp chuột phải vào tên Sheet => rồi chọn View Code để mở cửa sổ Microsoft Visual Basic For Applications cho sheet => sau đó dán đoạn mã sau vào:
Sub SplitTable()
Dim wss As Worksheet
Dim wst As Worksheet
Dim tbs As ListObject
Dim newTable As ListObject
Dim ids As Collection
Dim cel As Range
Dim rt As Long
Dim id As Variant
Application.ScreenUpdating = False
Set wss = ActiveSheet
Set tbs = wss.Range("A1").ListObject
Set ids = New Collection
On Error Resume Next
For Each cel In tbs.ListColumns("Employee ID").DataBodyRange
ids.Add Key:=CStr(cel.Value), Item:=cel.Value
Next cel
On Error GoTo 0
Set wst = Worksheets.Add(After:=wss)
wst.Range("Z1").Value = "Employee ID"
rt = 1
For Each id In ids
tbs.HeaderRowRange.Copy Destination:=wst.Range("A" & rt)
wst.Range("Z2").Value = id
tbs.Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=wst.Range("Z1:Z2"), _
CopyToRange:=wst.Range("A" & rt + 1)
wst.Range("A" & rt).CurrentRegion.Rows(1).Delete Shift:=xlShiftUp
' Add the table and get a reference to it
Set newTable = wst.ListObjects.Add(Source:=wst.Range("A" & rt).CurrentRegion)
' Enable total row for the new table
newTable.ShowTotals = True
rt = rt + wst.Range("A" & rt).CurrentRegion.Rows.Count + 1
Next id
wst.Range("Z1:Z2").Clear
wst.UsedRange.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
Sau khi dán mã xong, ta nhấp chuột vào tên cột chứa giá trị cần tách bảng (ở đây là Employee ID), để đoạn mã sẽ được tự động cập nhật tách giá trị theo cột nhấp vào:
Bước 3: Sau đó ta nhấn F5 để chạy mã VBA. Sau khi mã chạy xong nó sẽ tạo một Sheet mới và dữ liệu bảng gốc được tách theo từng giá trị trong cột Employee ID.
Trên đây là hướng dẫn cách chia bảng dữ liệu trong Excel thành nhiều bảng theo giá trị trong cột. Mong rằng bài viết này hữu ích và giúp các bạn trong quá trình làm việc!
