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

Dương Mạnh Quân
Dương Mạnh Quân
Aug 01 2018

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:

Ví dụ cách tạo danh sách có điều kiện phụ thuộc nhau trong Excel

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

Tham khảo: Tự động lọc danh sách không trùng với Advanced Filter VBA cực kỳ đơn giản

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:

Cách tạo danh sách phụ thuộc nhau trong Excel

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:

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

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:

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:

Tạo ra danh sách các Quận/ Huyện phụ thuộc theo giá trị chọn

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:

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

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

0/5 - (0 bình chọn)

0/5 - (0 bình chọn)

Bài viết liên quan

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn về một số hàm DAX căn bản trong Power Pivot (Phần 2)

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách phân biệt Data Table với Lookup Table trong Power Pivot

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách mở thẻ Power Pivot trên thanh Menu của Excel

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách phân biệt giữa Power Pivot và Pivot Table

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách kết nối các bảng trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

Hướng dẫn cách đổi tên bảng và tên cột trong Power Pivot

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội