Tạo danh sách thả xuống phụ thuộc là thực tế phổ biến giữa những người dùng Excel. Tuy nhiên, việc tạo danh sách thả xuống phụ thuộc với khoảng trắng trong Excel yêu cầu xử lý khoảng trắng giữa các từ.
Giả sử chúng tôi có 3 Danh sách sản phẩm với tên hai từ dẫn đến khoảng trắng giữa các từ. Chúng tôi phải tạo một danh sách thả xuống cho phép khoảng trắng trong các tên phụ thuộc. Excel INDEX , TRẬN ĐẤU và INDIRECT các hàm tạo thành các công thức cho phép các khoảng trắng tồn tại trong các tên được tham chiếu.
Trong bài viết này, chúng tôi trình bày 2 cách dễ dàng để tạo danh sách thả xuống phụ thuộc vào Excel với khoảng trắng.
Tải xuống Sổ làm việc Excel
2 cách dễ dàng để tạo danh sách thả xuống phụ thuộc vào Excel với dấu cách
Trong Dữ liệu của Excel , Excel cung cấp tính năng Xác thực dữ liệu trong Công cụ dữ liệu tiết diện. Người dùng dễ dàng chèn danh sách thả xuống bằng cách sử dụng Xác thực dữ liệu tính năng. Nhưng trong khi tạo danh sách thả xuống phụ thuộc, các khoảng trắng có trong các mục nhập phụ thuộc sẽ gây ra lỗi trong quá trình này.
Để khắc phục điều này, chúng tôi sử dụng các phương pháp sau.
Phương pháp 1:Danh sách thả xuống phụ thuộc có dấu cách bằng cách sử dụng các hàm Excel kết hợp
INDEX được kết hợp và MATCH các hàm bỏ qua khoảng trắng trong tên hoặc tiêu đề phụ thuộc. Cú pháp của INDEX chức năng là
=INDEX (array, row_num, [col_num], [area_num]) mảng ; phạm vi ô hoặc mảng.
row_num ; vị trí hàng trong mảng.
col_num ; vị trí hàng trong mảng. [tùy chọn]
area_num ; phạm vi đã sử dụng trong mảng. [tùy chọn]
Một lần nữa, cú pháp của MATCH chức năng là
=MATCH (lookup_value, lookup_array, [match_type]) lookup_value ; giá trị khớp bắt buộc trong lookup_array.
lookup_array ; một mảng hoặc tham chiếu để khớp với lookup_value từ đó.
match_type ; theo mặc định, chính xác hoặc nhỏ nhất tiếp theo = 1 , đối sánh chính xác = 0 và chính xác hoặc lớn nhất tiếp theo = -1 . [tùy chọn]
🔁 Danh sách thả xuống
Bước 1: Đặt con trỏ vào bất kỳ ô trống nào (tức là F4 ) sau đó đi tới Dữ liệu > Chọn Xác thực dữ liệu (từ Công cụ dữ liệu phần).
Bước 2: Xác thực dữ liệu cửa sổ mở ra. Trong Xác thực dữ liệu Cài đặt phần,
Chọn Tiêu chí xác thực Cho phép dưới dạng Danh sách .
Chọn B4:D4 dưới dạng Nguồn .
Nhấp vào OK .
➤ Bây giờ, trong trang tính, hãy nhấp vào biểu tượng mũi tên xuống trong ô F4 . Bạn thấy tất cả các ô được chỉ định là Nguồn trong Xác thực dữ liệu cửa sổ xuất hiện dưới dạng danh sách thả xuống.
🔁 Danh sách thả xuống phụ thuộc
Bước 3: Lặp lại Bước 1 đến 2 cho ô F5 . Nhập công thức sau là Nguồn trong Xác thực dữ liệu cửa sổ.
=INDEX(B5:D13,,MATCH($F$4,$B$4:$D$4,0)) So sánh công thức với các cú pháp được mô tả trước đó, phần công thức MATCH ($ F $ 4, $ B $ 4:$ D $ 4,0) vượt qua col_num vào INDEX chức năng. B5:D13 = mảng của INDEX hàm số. Và không cần row_num .
TRẬN ĐẤU phần có F4 dưới dạng lookup_value , B4:D4 dưới dạng lookup_array và 0 đề cập đến đối sánh chính xác loại.
Nhấp vào OK .
➤ Nhấp vào OK đưa bạn trở lại trang tính. Trong trang tính, trong ô F5 nhấp vào biểu tượng mũi tên xuống và bạn thấy các không gian hiện có không thành vấn đề khi tạo danh sách thả xuống phụ thuộc.
Đọc thêm:Cách tạo danh sách thả xuống dựa trên công thức trong Excel (4 cách)
Bài đọc tương tự
- Danh sách thả xuống có điều kiện trong Excel (Tạo, Sắp xếp và Sử dụng)
- Cách liên kết giá trị ô với danh sách thả xuống trong Excel (5 cách)
- Danh sách thả xuống của Excel tùy thuộc vào lựa chọn
- Tạo Danh sách Thả xuống Có thể Tìm kiếm trong Excel (2 Phương pháp)
- Cách tạo danh sách thả xuống Excel với màu (2 cách)
Phương pháp 2:Sử dụng chức năng Xác định Tên và CHỈ ĐỊNH
Trước đó, chúng tôi đã chứng minh cách INDIRECT hàm không thể tạo danh sách thả xuống trong khi khoảng trắng có trong tên hoặc tham chiếu phụ thuộc. Để đảm bảo INDIRECT hàm cho phép khoảng trắng trong các tên phụ thuộc, chúng tôi phải sửa đổi một chút công thức bằng cách sử dụng SUBSTITUTE hàm số. Cú pháp của INDIRECT chức năng là
=INDIRECT (ref_text, [a1]) ref_text ; tham chiếu trong Văn bản định dạng.
a1 ; Dấu hiệu Boolean của A1. Theo mặc định, TRUE =A1 Phong cách. [Tùy chọn]
🔁 Xác định tên
Bước 1: Chọn tiêu đề cột (tức là B4:D4 ) sau đó đi tới Công thức > Chọn Xác định tên (từ Xác định tên phần).
Bước 2: Tên mới cửa sổ xuất hiện. Trong Tên mới cửa sổ,
Chỉ định một Tên cho các ô (tức là Danh sách ).
Excel tự động lấy tham chiếu ô cho Tham chiếu đến hộp.
Nhấp vào OK .
Bước 3: Lặp lại Bước 1 và 2 của phương pháp này, để gán tên và phạm vi của các danh sách khác nhau.
➤ Bạn có thể kiểm tra tất cả các tên được chỉ định từ Công thức > Trình quản lý tên (từ Xác định tên phần).
🔁 Danh sách thả xuống
Bước 4: Bây giờ, đặt con trỏ vào F4 chuyển đến ô Dữ liệu > Chọn Xác thực dữ liệu (từ Công cụ dữ liệu phần).
Bước 5: Xác thực dữ liệu hộp thoại xuất hiện. Trong hộp thoại,
Trong Tiêu chí xác thực chọn Danh sách trong Cho phép hộp.
Nhập =Danh sách ( Danh sách là tên được xác định cho tiêu đề cột) trong Nguồn hộp thoại.
Nhấp vào OK .
➤ Nếu bạn nhấp vào biểu tượng mũi tên xuống trong ô F4 , bạn sẽ thấy các tiêu đề cột dưới dạng danh sách thả xuống như trong hình bên dưới.
🔁 Danh sách thả xuống phụ thuộc
Bước 6: Thực hiện Bước 4 và 5 để hiển thị cửa sổ Xác thực Dữ liệu, Trong cửa sổ,
Dán công thức sau làm Nguồn .
=INDIRECT (SUBSTITUTE ($ F $ 4, ”“, ”_”))
Trong công thức, SUBSTITUTE hàm thay thế khoảng trắng trong F4 tham chiếu với dấu gạch dưới ( _ ). Bạn có thể sử dụng các ký tự khác để thay thế khoảng trắng trong các tên đã xác định và tương tự trong INDIRECT công thức. Do đó, INDIRECT chuyển đổi hàm F4 mục nhập tương tự như các tên được chỉ định. Tùy thuộc vào F4 giá trị, INDIRECT chức năng hiển thị danh sách sản phẩm.
➤ Sau khi nhấp vào OK dẫn đến một danh sách phụ thuộc để hiển thị như được mô tả trong ảnh chụp màn hình bên dưới.
Bạn có thể thay đổi các mục nhập tên phụ thuộc từ danh sách thả xuống đầu tiên, kết quả là các mục nhập trong danh sách thả xuống thứ hai sẽ tự động thay đổi.
Đọc thêm:Cách tạo danh sách thả xuống trong Excel (Độc lập và Phụ thuộc)
Kết luận
Trong bài viết này, chúng tôi trình bày các công thức để chèn danh sách thả xuống phụ thuộc Excel với khoảng trắng. INDEX được kết hợp và MATCH công thức được tạo sẵn có khoảng trắng trong tên hoặc tiêu đề phụ thuộc. Tuy nhiên, chúng tôi phải sửa đổi INDIRECT chức năng cho phép khoảng trắng trong các mục nhập phụ thuộc. Hy vọng những phương pháp nêu trên đáp ứng yêu cầu của bạn. Bình luận nếu bạn có thêm thắc mắc hoặc có điều gì cần bổ sung.
Các bài viết có liên quan
- VLOOKUP với Danh sách thả xuống trong Excel
- Cách chỉnh sửa danh sách thả xuống trong Excel (4 cách tiếp cận cơ bản)
- Tạo danh sách thả xuống từ một trang tính khác trong Excel (2 phương pháp)
- Cách Loại bỏ Danh sách Thả xuống trong Excel
- Cách sử dụng Câu lệnh IF để Tạo Danh sách Thả xuống trong Excel
- Danh sách thả xuống của Excel không hoạt động (8 vấn đề và giải pháp)