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 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 đó.

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 rawMa_Huyen_GetData.bas hosted with ❤ by GitHub

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

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: