Với VBA, bạn có thể tạo một macro để thực hiện nhanh các tác vụ chỉ với một cú click, nhờ thế tiết kiệm được khá nhiều thời gian và công sức mỗi khi cần xử lý dữ liệu Excel. Ngoài ra, nếu tạo dasboard trong Excel, bạn sẽ nâng khả năng sắp xếp dữ liệu Excel lên một tầm cao mới khi click đúp vào tiêu đề như trong hình dưới đây.
Trong bài viết này, Taimienphi.vn sẽ hướng dẫn các bạn Cách sắp xếp dữ liệu trong Excel sử dụng VBA.
Nắm được phương pháp Range.Sort trong Excel VBA
Khi sắp xếp dữ liệu qua VBA, trước tiên bạn cần hiểu được phương pháp Range.Sort. "Range" là dữ liệu bạn cần sắp xếp. Ví dụ, nếu muốn sắp xếp dữ liệu vào các ô A1:A10, thì "Range" chính là vùng đó ("A1:A10").
Bên cạnh đó, bạn có thể tạo một Range có tên và dùng thay cho các tham chiếu ô. Ví dụ, nếu tôi tạo một vùng có tên "DataRange" cho các ô A1:A10, như vậy, tôi có thể sử dụng Range ("DataRange").
Với phương pháp sắp xếp dữ liệu này, bạn cần cung cấp thêm các thông tin qua các tham số. Dưới đây là một số thông số chính bạn cần biết:
- Key - ở đây bạn cần xác định cột muốn sắp xếp. Ví dụ, nếu muốn sắp xếp cột A, bạn cần sử dụng lệnh key:=Range("A1").
- Order - Ở đây, bạn chọn sắp xếp dữ liệu theo thứ tự chiều nhỏ đến lớn hoặc theo thứ tự từ chiều lớn đến nhỏ. Ví dụ, nếu muốn phân loại theo thứ tự từ nhỏ đến lớn, bạn sử dụng lệnh Order:=xlAscending.
- Header - Ở đây, bạn chọn tập hợp dữ liệu có tiêu đề hoặc không có tiêu đề. Nếu có tiêu đề, việc sắp xếp dữ liệu sẽ bắt đầu từ dòng thứ hai của tập hợp dữ liệu, thay vì từ dòng thứ nhất. Đối với dữ liệu có tiêu đề, bạn sử dụng lệnh Header:=xlYes.
Ba thông số trên được áp dụng cho hầu hết các trường hợp, tuy nhiên, Taimienphi.vn gợi ý bạn vẫn nên tham khảo thêm về các thông số trong Excel trong bài viết này.
Bây giờ, chúng ta sẽ xem cách sắp xếp dữ liệu trong Excel sử dụng VBA theo phương pháp Range.Sort nhé!
Sắp xếp từng cột không có tiêu đề
Giả sử bạn có một cột không có tiêu đề (như trong hình minh họa).
Bạn có thể sử dụng đoạn code dưới đây để sắp xếp nó theo thứ tự từ nhỏ đến lớn.
Sub SortDataWithoutHeader()
Range("A1:A12").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub
Ở đây, tôi chọn vùng dữ liệu là Range("A1:A12").
Trong trường hợp dữ liệu có thể được thay đổi và bổ sung hay xóa bớt các giá trị, bạn có thể sử dụng đoạn code sau để chương trình tự động điều chỉnh theo các ô có điền dữ liệu trong tập hợp dữ liệu.
Sub SortDataWithoutHeader()
Range("A1", Range("A1").End(xlDown)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub
Lưu ý rằng thay bằng Range("A1:A12"), tôi vừa sử dụng Range("A1", Range("A1").End(xlDown)).
Như vậy, ta sẽ kiểm tra được các ô có điền dữ liệu liên tục đến cuối cột và bao hàm nó trong dữ liệu được sắp xếp. Trong trường hợp có các ô trống, chương trình sẽ chỉ xét dữ liệu cho đến ô trống đầu tiên.
Ngoài ra, bạn có thể tạo vùng theo tên và sử dụng vùng tên đó thay cho các tham chiếu ô. Ví dụ, nếu vùng được đặt tên là DataSet, đoạn code của bạn sẽ có được viết như sau:
Sub SortDataWithoutHeader()
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo
End Sub
Bây giờ, chúng ta sẽ giải thích nhanh các thông số được sử dụng trong ví dụ trên:
- Key1:=Range("A1") - A1 là mã cho biết cột đang sắp xếp dữ liệu.
- Order1:=xlAscending - chỉ thứ tự hiện tại là xlAscending. Nếu muốn sắp xếp dữ liệu theo thứ tự từ lớn đến nhỏ, sử dụng lệnh xlDescending.
- Header:= xlNo - chỉ dữ liệu hiện tại không có tiêu đề. Đây là giá trị mặc định. Vì vậy kể cả khi bạn bỏ đi giá trị này, dữ liệu sẽ được sắp xếp theo theo trường hợp không có tiêu đề.
Bạn thắc mắc không biết nên đặt mã VBA này vào đâu và cách chạy macro như thế nào? Tiếp tục tham khảo bài viết Cách sắp xếp dữ liệu trong Excel sử dụng VBA nhé!
Phân loại cột đơn có tiêu đề
Trong ví dụ trước, tập hợp dữ liệu không có tiêu đề.
Đối với dữ liệu có tiêu đề, bạn cần chỉ ra trong đoạn code, để chương trình sắp xếp dữ liệu bắt đầu từu dòng thứ hai của tập hợp dữ liệu.
Giả sử bạn có tập hợp dữ liệu như sau:
Dưới đây là đoạn code sắp xếp dữ liệu theo thứ tự từ lớn đến nhỏ dựa trên doanh thu của các cửa hàng.
Sub SortDataWithHeader()
Range("DataRange").Sort Key1:=Range("C1"), Order1:=xlDescending
End Sub
Lưu ý rằng tôi vừa tạo một vùng có tên - 'DataRange', và sử dụng vùng có tên này trong đoạn code.
Sắp xếp nhiều cột có tiêu đề
Trong các phần trước của bài viết này, chúng ta đã nghiên cứu Cách sắp xếp cột đơn (có và không có tiêu đề).
Bây giờ là các thao tác bạn cần thực hiện để sắp xếp dữ liệu theo nhiều cột.
Ví dụ, trong tập hợp dữ liệu dưới đây, đầu tiên tôi cần sắp xếp dữ liệu theo mã bang, và tiếp đó là theo cửa hàng.
Đây là đoạn code sắp xếp đồng thời nhiều cột.
Sub SortMultipleColumns()
With ActiveSheet.Sort
.SortFields.Add Key:=Range("A1"), Order:=xlAscending
.SortFields.Add Key:=Range("B1"), Order:=xlAscending
.SetRange Range("A1:C13")
.Header = xlYes
.Apply
End With
End Sub
Dưới đây là kết quả bạn thu được.
Trong ví dụ trên, đầu tiên dữ liệu được sắp xếp theo mã bang (cột A). Sau đó, trong dữ liệu mã bang, dữ liệu tiếp tục được sắp xếp theo tên Cửa hàng (cột B). Thứ tự này được xác định theo mã mà bạn muốn đề cập.
Click đúp vào tiêu đề để sắp xếp dữ liệu
Nếu đang tạo một dashboard hay muốn dễ khai thác dữ liệu báo cáo hơn, bạn có thể viết code VBA để sắp xếp dữ liệu khi click đúp vào các tiêu đề.
Như trong bảng dưới đây:
Dưới đây là đoạn code giúp bạn thực hiện tác vụ này:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("DataRange").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <=>
Cancel = True
Set KeyRange = Range(Target.Address)
Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes
End If
End Sub
Lưu ý rằng tôi vừa tạo một vùng có tên ("DataRange") và dùng trong đoạn code trên thay cho các tham chiếu ô.
Khi bạn click đúp vào một tiêu đề bất kỳ, đoạn code sẽ tắt tính năng click đúp thông thường (chuyển sang chế độ biên tập) và sử dụng ô đó làm key trong quá trình sắp xếp dữ liệu.
Ngoài ra, đoạn code này chỉ sắp xếp các cột theo thứ tự từ nhỏ đến lớn.
Vị trí đặt code
Bạn cần dán đoạn code này vào cửa sổ code trong bảng tính muốn thực hiện tính năng sắp xếp dữ liệu click đúp.
Thao tác thực hiện:
- Click chuột phải vào tab trong bảng tính.
- Click vào View Code.
- Dán đoạn code trong cửa sổ code của bảng tính cần sắp xếp dữ liệu.
Giả sử bây giờ bạn muốn sắp xếp hai cột đầu tiên ('Sate' và 'Store') theo thứ tự từ nhỏ đến lớn, với cột 'Sales' theo thứ tự từ lớn đến nhỏ.
Đoạn code sẽ như sau:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("DataRange").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <=>
Cancel = True
Set KeyRange = Range(Target.Address)
If Target.Value = "Sales" Then
SortOrder = xlDescending
Else
SortOrder = xlAscending
End If
Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes, Order1:=SortOrder
End If
End Sub
Trong đoạn code trên, bạn cần kiểm tra xem ô được click đúp có đúng là tiêu đề Sales hay không. Nếu đúng, ô này sẽ gán giá trị xlDescending cho biến SortOrder, nếu không, nó sẽ gán cho xlAscending.
Bây giờ, chúng ta sẽ quan sát công cụ visual Marker (mũi tên và ô có màu) trong tiêu đề khi sắp xếp dữ liệu.
Như trong hình dưới đây:
Như vậy, tôi vừa thêm một bảng tính mới và thay đổi một số nội dung trong bảng này (bạn có thể tải file ví dụ và thực hành theo):
- Đổi tên bảng tính mới thành 'BackEnd'.
- Trong ô B2, nhập biểu tượng mũi tên (cách làm: đi đến mục Insert và click vào tùy chọn 'Symbol').
- Copy và paste các tiêu đề từ tập hợp dữ liệu vào ô A3:C3 trong 'BackEnd'.
- Dùng hàm sau trong ô A4:AC4:
=IF(A3=$C$1,A3&" "&$B$1,A3)
- Các ô còn lại sẽ tự động điền dữ liệu theo mã VBA khi bạn click đúp vào các tiêu đề cần sắp xếp cột.
Bảng backend sẽ có dạng như sau:
Bây giờ, bạn có thể sử dụng đoạn mã dưới đây để sắp xếp dữ liệu khi click đúp vào các tiêu đề. Khi bạn click đúp vào một tiêu đề, nó sẽ tự động xuất hiện mũi tên trong văn bản tiêu đề. Lưu ý rằng tôi vừa sử dụng định dạng có điều kiện để tô sáng ô.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("DataRange").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <=>
Cancel = True
Worksheets("Backend").Range("C1") = Target.Value
Set KeyRange = Range(Target.Address)
Range("DataRange").Sort Key1:=KeyRange, Header:=xlYes
Worksheets("BackEnd").Range("A1") = Target.Column
For i = 1 To ColumnCount
Range("DataRange").Cells(1, i).Value = Worksheets("Backend").Range("A4").Offset(0, i - 1).Value
Next i
End If
End Sub
Lưu ý rằng đoạn code này thường được áp dụng để xây dựng dữ liệu và tập tin workbook. Nếu thay đổi cấu trúc dữ liệu, bạn sẽ phải thay đổi cả đoạn code trên.
Trên đây, chúng tôi đã giúp các bạn sắp xếp dữ liệu trong Excel sử dụng VBA, để củng cố thêm kiến thức, các bạn tham khảo thêm cách truy cập dữ liệu trong bảng tính Excel từ VBA tại đây. Chúc các bạn thành công!