Làm thế nào để khi lựa chọn Tỉnh/Thành phố thì các đơn vị trực thuộc Quận/Huyện tương ứng với Tỉnh/Thành phố đó được tự động lọc theo? Chỉ với việc sử dụng các hàm Excel cơ bản cùng Macro VBA chúng ta đã có thể giải quyết thắc mắc trên.

Tạo file Excel có 2 Sheets Data và Reference

Điền thông tin các Quận/Huyện trực thuộc Tỉnh/Thành phố bằng cách tìm kiếm trên Google với từ khóa “danh sách quận huyện hà nội

Lựa chọn bằng cách click phải chuột chọn “Mở liên kết trong tab mới

Sao chép” danh sách Quận/Huyện được tổ chức dạng bảng

Dán vào sheet phụ trên Excel và “Copy” cột chứa thông tin Quận/Huyện

Paste “Value” vào cột District trên sheet Reference

Điền thông tin Tỉnh/Thành phố rồi sao chép xuống phía dưới bằng cách click đúp chuột tại phần cuối của ô Cell “Hà Nội”

Lựa chọn thẻ “INSERT” để tạo bảng

Tạo bảng với tick “My table has headers” rồi click “Ok

Đổi tên bảng thành “tblDistrict

Tương tự với các Tỉnh/Thành phố và Quận/Huyện khác

Đặt tên bảng Tỉnh/Thành phố là “tblProvince” và Phường/Xã là “tblWards

Tại phần Provinces để sử dụng Data Validation tự động thêm/bớt khi dữ liệu trong phần Reference được thêm/bớt chúng ta cần xác định vùng dữ liệu theo bảng tblProvince thông qua “=tblProvince[Province]

Tại thẻ “FORMULAS” lựa chọn “Name Manager

Click “New…

Đặt tên hằng số là “Provice” với giá trị “Refers to” là “=tblProvince[Province]

Click “Close

Tại ô F2 của sheet Data lựa chọn thẻ “DATA” → “Data Validation

Lựa chọn “Settings” với Allow là “List

Nguồn (Source) được xác định từ hằng số “=Province” rồi click “Ok

Lúc này cột “Province” tự động sổ ra theo dữ liệu tại sheet Reference

Tại phần District để sử dụng Data Validation tự động thay đổi dữ liệu khi lựa chọn thông tin ở cột Province chúng ta cần xác định 2 hằng số ProvinceStart (“=tblDistrict[[#Headers],[Province]]“) và ProviceColumn

Bôi cả cột Province của bảng tblDistrict rồi lựa chọn “FORMULAS” → “Name Manager

Lựa chọn “New

Đặt tên hằng số là “ProviceColumn” với giá trị “Refers to” là “=tblDistrict[[All],[Province]]” rồi click “Ok

Click “Close

Lựa chọn “Settings” với Allow là “List” với nguồn (Source) được xác định từ công thức “=OFFSET(ProvinceStart,MATCH(F2,ProvinceColumn,0)-1,1,COUNTIF(ProvinceColumn,F2),1)” rồi click “Ok

Lúc này mỗi khi chúng ta lựa chọn Tỉnh/Thành phố (Province) giá trị bên cột Quận/Huyện (District) sẽ thay đổi

Để các dữ liệu tại cột District clear mỗi khi thay đổi Province chúng ta cần thêm một đoạn code VBA Macro

File nguồn: VietNam.xlsm