Hướng dẫn cách tạo danh sách phụ thuộc nhau trong Excel bằng VBA

Bạn làm như thế nào khi muốn trình bày các đối tượng dưới dạng danh sách trong Excel? Có nhiều cách để thực hiện việc này, một trong những cách phổ biến nhất là sử dụng chức năng Data Validation / List. Nhưng yêu cầu nâng cao hơn của việc tạo danh sách các phụ thuộc thì sao? Hãy cùng Học Excel Online tìm hiểu cách tạo danh sách phụ thuộc trong Excel bằng VBA.

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

Ở hình trên chúng ta có 2 danh sách: Danh sách các 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 danh sách Tỉnh / Thành phố được chọn tại ô F1, trong danh sách này tên các Tỉnh / Thành phố chỉ xuất hiện một lần. Tại ô F2 tạo danh sách chọn tên các Quận / Huyện tùy theo tỉnh / thành phố được chọn tại ô F1.

Bạn có thể tải file mẫu tại:

Tải game crack việt hoá tại: https://daominhha.com

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

Tạo danh sách chọn lọc các giá trị không khớp

Để có thể tạo danh sách chọn các tỉnh / thành phố không trùng nhau trong ô F1, chúng ta cần thực hiện như sau:

  • Lọc bỏ nội dung không phù hợp (chỉ xuất hiện một lần) trong cột A. Đưa các kết quả đã lọc vào cột C
  • Tạo danh sách chọn trong ô F1 với Xác thực Dữ liệu

Giới thiệu về cách lọc các giá trị không khớp khỏi danh sách, chúng tôi có nhiều cách để thực hiện: Sử dụng Bộ lọc nâng cao, Sử dụng Xóa các bản trùng lặp

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

>>> Xem thêm:  Xuất ra file PDF từ danh sách chọn trước bằng VBA

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 khớp với cột A. Nếu chưa rõ cách thực hiện, bạn có thể tham khảo link bài viết trên. Tiến hành như sau:

Trong cột A: áp dụng Bộ lọc nâng cao từ A1 đến A1000 (giả sử có tới 1000 hàng dữ liệu) trong sheet1 (là trang tính đang làm việc)

  • Hoạt động: Thực hiện hành động để sao chép kết quả sang vị trí khác
  • CopyToRange: Vị trí lấy kết quả là ô C1 trong sheet1 (là sheet đang làm việc)
  • Độc nhất: để nhận các giá trị không khớp, chỉ xuất hiện một lần, chọn True

Khi chạy lệnh này, chúng ta nhận được danh sách các Tỉnh / Thành phố như trong cột C

Để tạo danh sách chọn cho ô F1, chúng tôi sử dụng Xác thực dữ liệu như sau:

Trong ô F1, chọn thẻ Dữ liệu => Chọn chức năng Xác nhận dữ liệu. Trong cửa sổ Xác thực Dữ liệu, hãy làm như sau:

  • Cho phép: lựa chọn Danh sách để tạo một danh sách lựa chọn
  • Nguồn: Nguồn cho danh sách. Nguồn này sẽ dựa trên nội dung trong cột C. Do đó, hãy sử dụng hàm OFFSET, bắt đầu từ vị trí C2, kết thúc tại vị trí từ C2 đến tổng số đối tượng trong khu vực C2: C1000. Trong trường hợp này, chúng tôi có:

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

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

Đây là cách tạo danh sách động, trong đó chiều rộng của danh sách có thể thay đổi tùy thuộc vào số lượng đối tượng trong danh sách.

>>> Xem thêm:  Hàm IF nhiều điều kiện trong Excel

Tạo danh sách lựa chọn phụ thuộc

Sau khi tạo xong danh sách chọn Tỉnh / Thành phố tại ô F1, chúng ta có thể chọn bất kỳ tỉnh / thành phố nào.

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

  • Trong cột D sẽ tạo danh sách các Quận / Huyện tùy thuộc vào giá trị được chọn trong ô F1.
  • Tạo danh sách chọn trong ô F2 theo danh sách trong cột

Bước 1: Tạo danh sách Quận / Huyện tùy thuộc vào giá trị được chọn trong ô F1

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

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

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

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

Private Sub Worksheet_Change (ByVal Target As Range)

If Not Application.Intersect (Range (“F1”)), Range (Target.Address)) Không có gì thì

Gọi cho Ma_Huyen_GetData

EndIf

Kết thúc Sub

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

>>> Xem thêm:  CÁCH DÙNG HÀM LEN TRONG EXCEL ĐỂ ĐẾM SỐ KÝ TỰ TRONG MỘT Ô

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

Bước 2: Tạo danh sách lựa chọn trong ô F2

Trong ô F2, chọn thẻ Dữ liệu => Chọn chức năng Xác nhận dữ liệu. Trong cửa sổ Xác thực Dữ liệu, hãy làm như sau:

  • Cho phép: lựa chọn Danh sách để tạo một danh sách lựa chọn
  • Nguồn: Nguồn cho danh sách. Nguồn này sẽ dựa trên nội dung trong cột D. Do đó, sử dụng hàm OFFSET, bắt đầu từ vị trí D2, kết thúc tại vị trí từ D2 cho tổng số đối tượng trong khu vực D2: D1000. Trong trường hợp này, chúng tôi có:

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

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

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

Làm thế nào để tạo một Combobox với một danh sách các phụ thuộc?

Tạo danh sách thả xuống trong Excel

Làm thế nào để tạo một danh sách các tỉnh, huyện và xã phụ thuộc vào nhau?

Bạn có gặp khó khăn khi áp dụng VBA để làm 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 giúp ích rất nhiều trong công việc. Tham gia ngay khóa học VBA101 – Lập trình VBA cho Người mới bắt đầu với Học Excel Online. Khóa học này sẽ giúp bạn tiếp cận những kiến ​​thức về VBA trong Excel một cách dễ hiểu, có hệ thống, giúp bạn nắm bắt những kiến ​​thức này một cách nhanh nhất. Hiện tại hệ thống đang có nhiều ưu đãi cực hấp dẫn khi bạn đăng ký khóa học này. Xem chi tiết tại:

Nguồn : Blog hocexcel