Khám phá cách sắp xếp dữ liệu trong Excel với VBA

Nếu bạn đang tìm kiếm giải pháp linh hoạt và mạnh mẽ cho việc sắp xếp dữ liệu theo yêu cầu cụ thể, VBA là lựa chọn đúng đắn. Hãy đồng hành cùng Mytour để khám phá chi tiết và ứng dụng ngay những kỹ thuật này trong công việc hàng ngày của bạn.

Học cách sắp xếp dữ liệu hiệu quả với VBA chỉ trong một vài bước đơn giản. Bài viết này sẽ giúp bạn nắm bắt kỹ thuật cần thiết để tạo ra những bảng dữ liệu hoàn hảo theo ý muốn.

Hiểu sâu về Range.Sort trong Excel VBA

Để thành công trong việc sắp xếp dữ liệu bằng VBA, bạn cần am hiểu về phương pháp Range.Sort. Đơn giản, 'Range' là nơi chứa dữ liệu bạn muốn sắp xếp. Chẳng hạn, nếu bạn muốn sắp xếp dữ liệu từ A1 đến A10, 'Range' sẽ là vùng đó ('A1:A10').

Hơn nữa, bạn có thể tạo một Range được đặt tên và sử dụng nó thay vì tham chiếu đến các ô cụ thể. Ví dụ, nếu tôi đặt tên cho vùng A1:A10 là 'DataRange', tôi có thể sử dụng Range('DataRange').

Trong phương pháp sắp xếp này, bạn cần cung cấp thêm thông tin qua các tham số. Dưới đây là một số tham số quan trọng bạn cần biết:

- Khóa - Ở đây, bạn cần xác định cột bạn muốn sắp xếp. Ví dụ, để sắp xếp cột A, sử dụng lệnh key:=Range('A1').

- Thứ tự - Chọn sắp xếp dữ liệu từ nhỏ đến lớn hoặc từ lớn đến nhỏ. Ví dụ, để sắp xếp từ nhỏ đến lớn, sử dụng lệnh Order:=xlAscending.

- Tiêu đề - Chọn tập hợp có tiêu đề hay không. Nếu có tiêu đề, sắp xếp bắt đầu từ dòng thứ hai, thay vì từ dòng đầu tiên. Đối với tiêu đề, sử dụng lệnh Header:=xlYes.

Các thông số trên thường đủ cho hầu hết các trường hợp, tuy nhiên, Mytour khuyên bạn nên tìm hiểu thêm về các thông số trong Excel.

Chúng ta sẽ thảo luận về cách sắp xếp dữ liệu trong Excel bằng VBA, áp dụng phương pháp Range.Sort!

Sắp xếp mỗi cột không có tiêu đề

Cho rằng bạn đang đối mặt với một cột không có tiêu đề (như hình minh họa).

Dùng đoạn mã sau để sắp xếp nó theo thứ tự từ nhỏ đến lớn.

Sub SapXepDuLieuKhongCoTieuDe()
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 hoặc xóa bớt các giá trị, bạn có thể sử dụng đoạn mã sau để chương trình tự động điều chỉnh theo các ô có dữ liệu trong tập hợp dữ liệu.
Sub SapXepDuLieuKhongCoTieuDe()
Range('A1', Range('A1').End(xlDown)).Sort Key1:=Range('A1'), Order1:=xlAscending, Header:=xlNo
End Sub

Chú ý rằng thay vì Range('A1:A12'), tôi đã sử dụng Range('A1', Range('A1').End(xlDown)).

Như vậy, chúng ta sẽ kiểm tra các ô có dữ liệu liên tục đến cuối cột và bao gồm chúng trong dữ liệu đã sắp xếp. Trong trường hợp có ô trống, chương trình sẽ chỉ xét dữ liệu cho đến ô trống đầu tiên.

Bên cạnh đó, 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 mã của bạn sẽ có dạng như sau:

Sub SapXepDuLieuKhongCoTieuDe()

Range('DataRange').Sort Key1:=Range('A1'), Order1:=xlAscending, Header:=xlNo

End Sub

Bây giờ, chúng ta sẽ diễn đạt ngắn gọn về các thông số được sử dụng trong ví dụ trên:

- Key1:=Range('A1') - A1 là địa chỉ cột bạn muốn sắp xếp dữ liệu.
- Order1:=xlAscending - chỉ sắp xếp theo thứ tự từ nhỏ đến lớn. Nếu muốn từ lớn đến nhỏ, sử dụng xlDescending.
- Header:= xlNo - chỉ định rằng dữ liệu không có tiêu đề. Giá trị mặc định khi bỏ trống. Dữ liệu sẽ được sắp xếp ngay cả khi không có tiêu đề.

Nếu bạn muốn biết nơi để đặt mã VBA này và cách chạy macro, hãy đọc thêm trong bài viết Hướng dẫn sắp xếp dữ liệu trong Excel sử dụng VBA.

Sắp xếp cột có tiêu đề

Trong ví dụ trước đó, tập hợp dữ liệu không có tiêu đề.

Với dữ liệu có tiêu đề, trong đoạn mã, bạn cần chỉ định rằng chương trình sắp xếp dữ liệu từ dòng thứ hai của tập hợp dữ liệu.

Hãy tưởng tượng bạn có bảng dữ liệu như sau:

Dưới đây là đoạn mã để sắp xếp dữ liệu giảm dần theo doanh thu của các cửa hàng.

Sub SortDataWithHeader()
Range('DataRange').Sort Key1:=Range('C1'), Order1:=xlDescending
End Sub

Chú ý rằng tôi vừa tạo một vùng có tên là 'DataRange' và sử dụng vùng này trong đoạn mã.

Sắp xếp nhiều cột với tiêu đề

Trong các phần trước của bài viết, chúng ta đã tìm hiểu về cách sắp xếp một cột (có và không có tiêu đề).

Dưới đây là các bước bạn cần thực hiện để sắp xếp dữ liệu theo nhiều cột.

Ví dụ, trong bộ dữ liệu dưới đây, trước hết tôi muốn sắp xếp theo mã bang và sau đó là theo cửa hàng.

Dưới đây là đoạn mã để sắp xếp đồng thời nhiều cột.

Sub SắpXếpNhiềuCột()
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 sẽ nhận được.

Trong ví dụ này, trước hết dữ liệu sẽ được sắp xếp theo mã bang (cột A). Tiếp theo, trong từng nhóm mã bang, dữ liệu sẽ 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 bởi mã bang bạn muốn thao tác.

Click đúp vào tiêu đề để sắp xếp dữ liệu

Nếu bạn đang tạo bảng điều khiển hoặc muốn tối ưu hóa việc trình bày dữ liệu báo cáo, bạn có thể viết mã VBA để tự động 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 mã giúp bạn thực hiện công việc 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 <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
Range('DataRange').Sort Key1:=KeyRange, Header:=xlYes
End If
End Sub

Hãy lưu ý rằng tôi đã tạo một vùng có tên ('DataRange') và sử dụng nó trong đoạn mã thay vì các tham chiếu ô thông thường.

Khi bạn click đúp vào bất kỳ tiêu đề nào, đoạn mã này sẽ vô hiệu hóa tính năng click đúp thông thường (chuyển sang chế độ chỉnh sửa) và sử dụng ô đó làm khóa để sắp xếp dữ liệu.

Hơn nữa, đoạn mã này chỉ sắp xếp các cột theo thứ tự từ bé đến lớn.

Đặt code vào đúng vị trí

Hãy dán đoạn mã này vào cửa sổ code trong bảng tính muốn kích hoạt tính năng sắp xếp dữ liệu khi click đúp.

Quá trình thực hiện thao tác:

- Chuột phải vào tab trong bảng tính.
- Chọn View Code.
- Dán đoạn mã vào cửa sổ code của bảng tính cần sắp xếp dữ liệu.

Giả sử bạn muốn sắp xếp hai cột đầu ('Sate' và 'Store') theo thứ tự tăng dần, còn cột 'Sales' theo thứ tự giảm dần.

Đoạn mã thực hiện 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 <= columncount=''>
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 mã trên, hãy kiểm tra xem ô được nhấp đúp có phải là tiêu đề Sales hay không. Nếu đúng, gán giá trị xlDescending cho biến SortOrder; nếu không, gán giá trị xlAscending.

Bây giờ, hãy theo dõi visual marker (mũi tên và ô có màu) trong tiêu đề khi dữ liệu được sắp xếp.

Như bạn thấy trong hình dưới đây:

Đã thêm một bảng tính mới và điều chỉnh 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).

- Đổ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: chọn 'Insert' và click vào 'Symbol').
- Copy và paste các tiêu đề từ tập hợp dữ liệu vào ô A3:C3 trong 'BackEnd'.
- Sử 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 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ã sau để 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 đã 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 <= columncount=''>
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 mã này thường được sử dụng để xây dựng dữ liệu và tập tin workbook. Nếu bạn thay đổi cấu trúc dữ liệu, hãy nhớ điều chỉnh đoạn mã này tương ứng.

Ở đây, chúng tôi đã hướng dẫn bạn cách sắp xếp dữ liệu trong Excel bằng VBA. Để củng cố kiến thức, bạn cũng có thể tham khảo 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!