Công thức chung
=SUMPRODUCT(--ISNUMBER(SEARCH(list,A1)))=0
Tóm lược
Để không cho phép đầu vào chứa một trong nhiều thứ, bạn có thể sử dụng quy tắc xác thực dữ liệu tùy chỉnh dựa trên hàm SEARCH.
Trong ví dụ được hiển thị, xác thực dữ liệu được áp dụng cho B5: B11 là:
=SUMPRODUCT(--ISNUMBER(SEARCH(list,B5)))=0
Giải trình
Quy tắc xác thực dữ liệu được kích hoạt khi người dùng thêm hoặc thay đổi giá trị ô.
Công thức này sử dụng hàm TÌM KIẾM để kiểm tra đầu vào của người dùng cho từng giá trị trong "danh sách" phạm vi được đặt tên. Logic tìm kiếm là "chứa" - khi giá trị từ "danh sách" được tìm thấy, TÌM KIẾM trả về vị trí của giá trị dưới dạng số. Nếu không tìm thấy, SEARCH trả về lỗi.
Sau đó, hàm ISNUMBER chuyển đổi các số thành TRUE và lỗi thành FALSE, và toán tử phủ định kép thay đổi các giá trị TRUE FALSE thành 1s và số không. Vì phạm vi được đặt tên "danh sách" chứa 5 giá trị, chúng tôi nhận lại 5 kết quả trong một mảng như sau:
(0; 0; 0; 0; 0)
Sau đó SUMPRODUCT tổng hợp các mục trong mảng và kết quả được kiểm tra bằng 0. Miễn là tất cả các mục bằng 0, hàm SUMPRODUCT trả về 0 và quá trình xác nhận thành công. Nếu hàm SUMPRODUCT trả về số khác (tức là khi một mục trong "danh sách" được tìm thấy) thì công thức trả về FALSE và việc xác nhận không thành công.
Lưu ý: Tham chiếu ô trong công thức xác thực dữ liệu có liên quan đến ô phía trên bên trái trong phạm vi được chọn khi quy tắc xác thực được xác định, trong trường hợp này là B5.
