Tạo danh sách thả xuống phụ thuộc (dependent drop down list) là thao tác phổ biến trong Excel. Tuy nhiên, tạo dependent dropdown list có khoảng trắng giữa các từ cần xử lý đặc biệt cho các khoảng trắng đó.
Giả sử có 3 Danh sách sản phẩm (Product Lists) với tên 2 từ dẫn đến khoảng trắng. Cần tạo dropdown list cho phép khoảng trắng trong tên phụ thuộc. Các hàm INDEX, MATCH, và INDIRECT của Excel tạo công thức cho phép khoảng trắng tồn tại trong tên tham chiếu.
Bài viết demo 2 cách dễ dàng tạo Excel dependent drop down list có khoảng trắng.
Tải Excel Workbook
2 Cách dễ dàng tạo Excel Dependent Drop Down List có khoảng trắng
Trong tab Data của Excel, tính năng Data Validation nằm trong mục Data Tools. Người dùng dễ dàng chèn dropdown list bằng Data Validation. Nhưng khi tạo dependent dropdown list, khoảng trắng trong các mục phụ thuộc gây lỗi.
Để khắc phục, dùng các phương pháp sau.
Phương pháp 1: Dependent Drop Down List có khoảng trắng dùng kết hợp hàm Excel
Kết hợp hàm INDEX và MATCH bỏ qua khoảng trắng trong tên/thuộc tính phụ thuộc. Cú pháp hàm INDEX:
=INDEX (array, row_num, [col_num], [area_num])array: phạm vi ô hoặc mảng.
row_num: vị trí hàng trong mảng.
col_num: vị trí cột trong mảng. [tùy chọn]
area_num: phạm vi dùng trong mảng. [tùy chọn]
Cú pháp hàm MATCH:
=MATCH (lookup_value, lookup_array, [match_type])lookup_value: giá trị cần tìm trong lookup_array.
lookup_array: mảng hoặc tham chiếu để tìm lookup_value.
match_type: mặc định, chính xác hoặc nhỏ hơn gần nhất=1, chính xác=0, chính xác hoặc lớn hơn gần nhất=-1. [tùy chọn]
🔁 Drop Down List (Danh sách thả xuống chính)
Bước 1: Đặt con trỏ tại ô trống bất kỳ (ví dụ F4) rồi vào Data > Chọn Data Validation (từ Data Tools).
Bước 2: Cửa sổ Data Validation mở ra. Tại phần Setting:
- Chọn Validation Criteria Allow là List.
- Chọn B4:D4 làm Source.
- Nhấn OK.
➤ Bây giờ, trong worksheet, click mũi tên xuống ở ô F4. Tất cả ô được gán làm Source trong Data Validation xuất hiện jako dropdown list.
🔁 Dependent Drop Down List (Danh sách thả xuống phụ thuộc)
Bước 3: Lặp lại Bước 1 và 2 cho ô F5. Nhập công thức sau làm Source trong cửa sổ Data Validation.
=INDEX(B5:D13,,MATCH($F$4,$B$4:$D$4,0))So với cú pháp trên, phần MATCH($F$4,$B$4:$D$4,0) truyền col_num cho hàm INDEX. B5:D13 = array của INDEX. Không lấy row_num.
Phần MATCH lấy F4 làm lookup_value, B4:D4 làm lookup_array, 0 là exact match (khớp chính xác).
Nhấn OK.
➤ Click OK quay lại worksheet. Tại ô F5 click mũi tên xuống, khoảng trắng không còn gây vấn đề cho dependent dropdown list.
Đọc thêm: Cách tạo Drop-Down List dựa trên công thức trong Excel (4 cách)
Đọc tương tự
- Conditional Drop Down List trong Excel (Tạo, Sắp xếp và Sử dụng)
- Cách liên kết giá trị ô với Drop Down List trong Excel (5 cách)
- Excel Drop Down List Depending on Selection
- Tạo Searchable Drop Down List trong Excel (2 phương pháp)
- Cách tạo Excel Drop Down List có màu (2 cách)
Phương pháp 2: Dùng Define Name (Định danh) và hàm INDIRECT
Trước đây đã thấy hàm INDIRECT không tạo được dropdown list khi tên phụ thuộc có khoảng trắng. Để INDIRECT cho phép khoảng trắng, cần sửa công thức dùng hàm SUBSTITUTE. Cú pháp INDIRECT:
=INDIRECT (ref_text, [a1])ref_text: tham chiếu dạng Text.
a1: chỉ báo Boolean kiểu A1. Mặc định TRUE = A1 style. [Tùy chọn]
🔁 Định danh (Define Names)
Bước 1: Chọn tiêu đề cột (ví dụ B4:D4) rồi vào Formulas > Chọn Define Names (từ mục Define Names).
Bước 2: Cửa sổ New Name hiện ra. Tại đó:
- Đặt Name cho ô (ví dụ List).
- Excel tự lấy tham chiếu ô cho ô Refers to.
- Nhấn OK.
Bước 3: Lặp lại Bước 1 và 2 của phương pháp này để đặt tên và phạm vi cho các danh sách khác.
➤ Kiểm tra tất cả tên đã đặt tại Formulas > Name Manager (từ mục Define Name).
🔁 Drop Down List
Bước 4: Đặt con trỏ tại ô F4 vào Data > Chọn Data Validation (từ Data Tools).
Bước 5: Hộp thoại Data Validation hiện ra. Tại hộp thoại:
- Dưới Validation Criteria chọn List trong Allow.
- Nhập =List (List là tên định danh cho tiêu đề cột) vào ô Source.
- Nhấn OK.
➤ Click mũi tên xuống ô F4, tiêu đề cột hiện ra làm dropdown list như hình.
🔁 Dependent Drop Down List
Bước 6: Thực hiện Bước 4 và 5 để hiện cửa sổ Data Validation. Tại cửa sổ:
- Dán công thức sau làm Source.
=INDIRECT(SUBSTITUTE($F$4," ","_"))
Trong công thức, hàm SUBSTITUTE thay thế khoảng trắng trong tham chiếu F4 bằng dấu gạch dưới (_). Có thể dùng ký tự khác để thay thế khoảng trắng trong tên định danh và tương tự trong công thức INDIRECT. Do đó INDIRECT chuyển đổi giá trị F4 giống tên đã định danh. Tùy giá trị F4, INDIRECT hiển thị danh sách sản phẩm.
➤ Sau khi nhấn OK ra dependent list như ảnh dưới.
Có thể thay đổi mục phụ thuộc từ dropdown list đầu, danh sách dropdown thứ 2 sẽ tự động thay đổi.
Đọc thêm: Cách tạo Drop Down List trong Excel (Độc lập và Phụ thuộc)
Kết luận
Bài viết demo công thức chèn Excel dependent drop down list có khoảng trắng. Công thức kết hợp INDEX và MATCH tự động xử lý khoảng trắng trong tên/thuộc tính phụ thuộc. Còn INDIRECT cần sửa đổi để cho phép khoảng trắng. Hy vọng các phương pháp trên đáp ứng nhu cầu. Comment nếu có thắc mắc hoặc bổ sung.
Các bài viết liên quan
- VLOOKUP với Drop Down List trong Excel
- Cách sửa Drop-Down List trong Excel (4 cách cơ bản)
- Tạo Drop Down List từ Sheet khác trong Excel (2 phương pháp)
- Cách xóa Drop-Down List trong Excel
- Cách dùng IF Statement tạo Drop-Down List trong Excel
- Excel Drop Down List không hoạt động (8 vấn đề và giải pháp)