Công thức chung
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Tóm lược
Để sử dụng xác thực dữ liệu chỉ cho phép một danh sách các ký tự cụ thể, bạn có thể sử dụng một công thức mảng khá phức tạp dựa trên các hàm COUNT, MATCH và LEN. Trong ví dụ được hiển thị, xác thực dữ liệu được áp dụng với công thức này:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
trong đó "được phép" là dải ô được đặt tên D5: D11.
Giải trình
Làm việc từ trong ra ngoài, hàm MID được sử dụng để tạo một mảng từ văn bản được nhập trong B5 với đoạn mã này:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
giải thích chi tiết tại đây. Kết quả là một mảng như thế này:
("A";"A";"A";"-";"1";"1";"1")
sẽ chuyển thành MATCH dưới dạng giá trị tra cứu. Đối với mảng tra cứu, chúng tôi sử dụng phạm vi được đặt tên "được phép", được nối với một chuỗi trống (""):
allowed&""
Việc nối sẽ chuyển đổi bất kỳ số nào thành chuỗi, do đó chúng ta đang so khớp giữa táo với táo. Kết quả là một mảng như thế này:
("A";"B";"C";"1";"2";"3";"-")
Đối số cuối cùng trong MATCH, match_type được đặt thành 0 để bắt buộc đối sánh chính xác. Vì chúng tôi cung cấp MATCH nhiều giá trị tra cứu, chúng tôi nhận lại một mảng có nhiều kết quả:
(1;1;1;7;4;4;4)
Mỗi số trong mảng này đại diện cho một trận đấu. Trong trường hợp không tìm thấy ký tự khớp nào, mảng sẽ chứa lỗi # N / A.
Cuối cùng, hàm COUNT được sử dụng để đếm các số trong mảng kết quả, được so sánh với số lượng tất cả các ký tự trong ô được tính bằng hàm LEN. Khi MATCH tìm thấy một kết quả phù hợp cho tất cả các ký tự, số lượng là bằng nhau, công thức trả về TRUE và xác thực dữ liệu thành công. Nếu MATCH không tìm thấy một ký tự phù hợp nào, nó sẽ trả về # N / A thay vì một số. Trong trường hợp đó, số lượng không khớp và xác thực dữ liệu không thành công.
Lưu ý: công thức này dựa vào thói vũ phu để hoàn thành công việc. Nếu bạn có cách tiếp cận tốt hơn, hãy để lại bình luận bên dưới.
