Hướng dẫn cách tạo danh sách (list) có điều kiện phụ thuộc nhau trong Excel bằng VBA

Bạn sẽ làm thế nào khi muốn trình bày các đối tượng theo 1 danh sách trong Excel? Có rất nhiều cách để làm điều này, một trong những cách thường gặp nhất là sử dụng chức năng Data Validation/ List. 

Nhưng với yêu cầu nâng cao hơn là tạo danh sách phụ thuộc lẫn nhau thì thế nào? Hãy cùng Gitiho.com tìm hiểu cách tạo danh sách phụ thuộc nhau trong Excel bằng VBA nhé.

Để làm rõ nội dung này, chúng ta cùng tham khảo ví dụ sau:

Trong hình trên chúng ta có 2 danh sách: Danh sách tỉnh/ thành phố của Việt Nam. Tương ứng với mỗi tỉnh / thành phố là các Quận / huyện. Yêu cầu là chúng ta cần tạo ra danh sách chọn Tỉnh / TP vào vị trí ô F1, trong danh sách này tên các tỉnh/ TP chỉ xuất hiện 1 lần. Tại ô F2 tạo danh sách chọn tên các Quận / Huyện phụ thuộc theo tỉnh / TP được chọn tại ô F1.

Sau đây chúng ta sẽ lần lượt đi giải quyết cụ thể từng trường hợp.

Cách tạo danh sách chọn giá trị không trùng

Để có thể tạo ra 1 danh sách chọn các tỉnh/TP không trùng nhau tại ô F1, chúng ta cần thực hiện các công việc sau:

• Lọc các nội dung không trùng (chỉ xuất hiện 1 lần) tại cột A. Đưa kết quả lọc được ra cột C
• Tạo danh sách chọn tại ô F1 bằng Data Validation

Về việc lọc giá trị không trùng từ 1 danh sách, chúng ta có rất nhiều cách làm: Sử dụng Advanced Filter, Sử dụng Remove Duplicates

Trong trường hợp này, chúng ta sẽ sử dụng Advanced Filter trong VBA để lọc danh sách không trùng với cột A. Nếu bạn chưa rõ cách làm thì có thể tham khảo ở link bài viết phía trên. Cách thực hiện như sau:

Tại cột A: áp dụng Advanced Filter từ A1 tới A1000 (giả định tới 1000 dòng dữ liệu) trong sheet1 (là sheet đang làm việc)

• Action: Thực hiện hành động copy kết quả ra vị trí khác
• CopyToRange: Vị trí lấy kết quả là ô C1 trong sheet1 (là sheet đang làm việc)
• Unique: để lấy các giá trị không trùng, chỉ xuất hiện 1 lần thì chọn là True

Khi chạy câu lệnh này, chúng ta thu được kết quả danh sách Tỉnh/TP như ở cột C

Để tạo danh sách chọn cho ô F1, chúng ta sử dụng Data Validation như sau:

Tại ô F1 chọn thẻ Data => Chọn chức năng Data Validation. Trong cửa sổ Data Validation thực hiện tiếp các nội dung:

• Allow: chọn List để tạo danh sách chọn
• Source: Nguồn cho danh sách. Nguồn này sẽ căn cứ vào nội dung ở cột C. Do đó sử dụng hàm OFFSET, bắt đầu từ vị trí C2, kết thúc là vị trí tính từ C2 tới tổng số đối tượng trong vùng C2:C1000. Trong trường hợp này ta có:

COUNTA(C2:C1000) = 63 tương ứng với 63 tỉnh thành phố

OFFSET(C2,0,0,63) = vùng C2:C64

Đây là cách tạo 1 danh sách động, trong đó độ rộng của danh sách có thể thay đổi tùy theo số đối tượng có trong danh sách đó. Với VBA, bạn có thể làm nhiều hơn thế nữa để tự động hóa công việc, nâng cao hiệu suất và tiết kiệm thời gian làm việc. Đăng ký học VBA ngay tại Gitiho với các khóa sau:

Cách tạo danh sách chọn phụ thuộc

Sau khi đã hoàn thành việc tạo danh sách chọn cho Tỉnh/TP tại ô F1, chúng ta có thể chọn 1 tỉnh/TP bất kỳ.

Để có thể có danh sách phụ thuộc giá trị được chọn tại F1, chúng ta có thể hình dung các công việc phải làm là:

• Tại cột D sẽ tạo ra danh sách các Quận/ Huyện phụ thuộc theo giá trị chọn ở ô F1.
• Tạo danh sách chọn tại ô F2 theo danh sách trong cột D

Bước 1: Tạo ra danh sách các Quận/ Huyện phụ thuộc theo giá trị chọn ở ô F1

Bởi mỗi khi chọn giá trị tại ô F1 thì danh sách có thể thay đổi, do đó việc đầu tiên cần làm là làm mới lại nội dung trong cột D, xóa các nội dung trước đó trong danh sách.

Tiếp đó thực hiện vòng lặp để xét từng giá trị tại cột A với giá trị tại ô F1. Nếu vị trí nào giống nhau thì sẽ lấy giá trị tương ứng ở cột B lấy sang cột D. Kết quả đưa sang cột D sẽ theo dòng cuối có dữ liệu trong cột này.

Nội dung cụ thể của câu lệnh VBA như sau:

	Sub Ma_Huyen_GetData() 'Lọc danh sách Quận/Huyện theo giá trị tỉnh/TP được chọn
	Sheet1.Range("D2:D1000").ClearContents 'Làm mới danh sách
	'Thiet lap cac bien
	Dim vl01 As Integer 'Biến vòng lặp mã tỉnh/tp
	Dim MaTinh As String 'Vị trí ô mã tỉnh/tp được chọn
	MaTinh = Sheet1.Range("F1").Value
	Dim lr_Tinh As Long
	Dim lr_Huyen As Long
	lr_Tinh = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row 'Dòng cuối của danh sách tỉnh/tp. Đây là giới hạn cho vòng lặp
	lr_Huyen = Sheet1.Cells(Rows.Count, 4).End(xlUp).Row 'Dòng cuối của cột kết quả, là vị trí sẽ đưa kết quả vào
	'Thực hiện vòng lặp
	For vl01 = 2 To lr_Tinh
	If MaTinh = Sheet1.Cells(vl01, 1).Value Then 'Xét mã tỉnh tại ô F1 với từng giá trị tại cột A
	Sheet1.Cells(lr_Huyen + 1, 4).Value = Sheet1.Cells(vl01, 2).Value 'Mỗi kết quả đúng sẽ trả về giá trị tại cột D, vị trí dòng cuối + 1 là dòng trống tiếp theo phía dưới
	lr_Huyen = Sheet1.Cells(Rows.Count, 4).End(xlUp).Row 'Sau mỗi kết quả thu được sẽ tính lại dòng cuối để lấy thêm kết quả
	End If
	Next
	End Sub

view raw Ma_Huyen_GetData.bas hosted with ❤ by GitHub

View the code on Gist.

Do mỗi lần thay đổi giá trị tại ô F1 sẽ chạy lại câu lệnh trên, nên chúng ta sẽ gán câu lệnh này trong sự kiện thay đổi giá trị tại ô F1 của worksheet như sau:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range(“F1”), Range(Target.Address)) Is Nothing Then
Call Ma_Huyen_GetData
End If
End Sub

Xem thêm: Tìm hiểu về ý nghĩa và cách dùng các sự kiện trong VBA Excel

Kết quả thu được như sau:

Bước 2: Tạo danh sách chọn tại ô F2

Tại ô F2 chọn thẻ Data => Chọn chức năng Data Validation. Trong cửa sổ Data Validation thực hiện tiếp các nội dung:

• Allow: chọn List để tạo danh sách chọn
• Source: Nguồn cho danh sách. Nguồn này sẽ căn cứ vào nội dung ở cột D. Do đó sử dụng hàm OFFSET, bắt đầu từ vị trí D2, kết thúc là vị trí tính từ D2 tới tổng số đối tượng trong vùng D2:D1000. Trong trường hợp này ta có:

COUNTA(D2:D1000) = tổng số quận/huyện lấy được từ câu lệnh VBA ở trên

Kết quả cuối cùng như sau:

Ngoài ra các bạn có thể tham khảo thêm một số cách khác như sau:

Cách tạo Combobox có danh sách phụ thuộc nhau

Tạo một danh sách tùy chọn trong Excel

Cách tạo danh sách tỉnh huyện xã phụ thuộc nhau

Bạn có gặp khó khăn khi áp dụng VBA vào trong công việc trên Excel không? VBA thực sự là một kiến thức nâng cao trong Excel, khó học, nhưng nó lại giúp ích rất nhiều trong công việc. Hãy tham gia ngay khóa học VBA101 – Lập trình VBA cho người mới bắt đầu cùng Gitiho.com. Khóa học này sẽ giúp bạn tiếp cận các kiến thức về VBA trong Excel một cách dễ hiểu nhất, có hệ thống giúp bạn nắm bắt kiến thức này một cách nhanh nhất. Hiện nay hệ thống đang có nhiều ưu đãi rất hấp dẫn khi bạn đăng ký tham gia khóa học này. Chi tiết xem tại:

 

 

 

 

 

 

 

 

 

 

Đánh giá bài viết này