Công thức Excel: Ô bằng một trong nhiều thứ -

Mục lục

Công thức chung

=SUMPRODUCT(--(A1=things))>0

Tóm lược

Nếu bạn muốn kiểm tra một ô để xem nó có bằng một trong một số thứ hay không, bạn có thể làm như vậy bằng công thức sử dụng hàm SUMPRODUCT.

Bối cảnh

Giả sử bạn có một danh sách các chuỗi văn bản trong phạm vi B5: B11 và bạn muốn kiểm tra từng ô với một danh sách những thứ khác trong phạm vi E5: E9. Nói cách khác, đối với mỗi ô trong B5: B11, bạn muốn biết: ô này có bằng bất kỳ ô nào trong E5: E9 không?

Bạn có thể bắt đầu xây dựng một công thức lớn dựa trên các câu lệnh IF lồng nhau, nhưng một công thức mảng dựa trên SUMPRODUCT là một cách tiếp cận đơn giản hơn, rõ ràng hơn.

Giải pháp

Giải pháp là tạo một công thức sẽ kiểm tra nhiều giá trị và trả về danh sách các giá trị TRUE / FALSE. Khi chúng ta có điều đó, chúng ta có thể xử lý danh sách đó (thực tế là một mảng) với SUMPRODUCT.

Công thức chúng tôi đang sử dụng trông giống như sau:

=SUMPRODUCT(--(B5=things))>0

Giải trình

Điều quan trọng là đoạn mã này:

--(B5=things)

mà chỉ đơn giản là so sánh giá trị trong B5 với mọi giá trị trong phạm vi được đặt tên là "things". Vì chúng ta đang so sánh B5 với một mảng (tức là phạm vi được đặt tên "things", E5: E11) nên kết quả sẽ là một mảng các giá trị TRUE / FALSE như sau:

(TRUE; FALSE; FALSE; FALSE; FALSE)

Nếu chúng ta có chẵn một TRUE trong mảng, chúng ta biết rằng B5 bằng ít nhất một thứ trong danh sách, do đó, để buộc các giá trị TRUE / FALSE thành 1s và 0s, chúng ta sử dụng một âm kép (-, còn được gọi là một kép một ngôi). Sau vụ cưỡng chế này, chúng tôi có điều này:

(1; 0; 0; 0; 0)

Bây giờ chúng ta xử lý kết quả với SUMPRODUCT, sẽ thêm các phần tử trong mảng. Nếu chúng tôi nhận được bất kỳ kết quả nào khác 0, chúng tôi có ít nhất một kết quả phù hợp, vì vậy chúng tôi sử dụng> 1 để buộc kết quả cuối cùng là TRUE hoặc FALSE.

Với một danh sách được mã hóa cứng

Không bắt buộc bạn phải sử dụng một phạm vi cho danh sách những thứ của mình. Nếu bạn chỉ tìm kiếm một số lượng nhỏ những thứ, bạn có thể sử dụng danh sách ở định dạng mảng, được gọi là hằng số mảng. Ví dụ: nếu bạn chỉ đang tìm kiếm các màu đỏ, xanh lam và xanh lục, bạn có thể sử dụng ("đỏ", "xanh dương", "xanh lá cây") như sau:

--(B5=("red","blue","green"))

Xử lý các khoảng trống thừa

Nếu các ô bạn đang kiểm tra chứa thêm bất kỳ khoảng trống nào, chúng sẽ không khớp đúng. Để loại bỏ tất cả không gian thừa, bạn có thể sửa đổi công thức để sử dụng hàm TRIM như sau: