Công thức Excel: Văn bản thường gặp nhất với tiêu chí -

Mục lục

Công thức chung

=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))

Tóm lược

Để tìm văn bản xuất hiện thường xuyên nhất trong một phạm vi, dựa trên tiêu chí bạn cung cấp, bạn có thể sử dụng công thức mảng dựa trên một số hàm Excel INDEX, MATCH, MODE và IF. Trong ví dụ được hiển thị, công thức trong G5 là:

=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))

trong đó "nhà cung cấp" là dải ô được đặt tên C5: C15 và "khách hàng" là dải ô được đặt tên B5: B15.

Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.

Giải trình

Làm việc từ trong ra ngoài, chúng tôi sử dụng hàm MATCH để đối sánh phạm vi văn bản với chính nó, bằng cách cung cấp cho MATCH cùng một phạm vi cho giá trị tra cứu và mảng tra cứu, bằng 0 cho loại đối sánh:

MATCH(supplier,supplier,0)

Vì giá trị tra cứu là một mảng có 10 giá trị, MATCH trả về một mảng 10 kết quả:

(1;1;3;3;5;1;7;3;1;5;5)

Mỗi mặt hàng trong mảng này đại diện cho vị trí đầu tiên mà tại đó tên nhà cung cấp xuất hiện trong dữ liệu. Mảng này được đưa vào hàm IF, được sử dụng để lọc kết quả chỉ cho Ứng dụng khách A:

IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))

IF trả về mảng đã lọc cho hàm MODE:

(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)

Chỉ lưu ý các vị trí được liên kết với Máy khách A vẫn còn trong mảng. MODE bỏ qua các giá trị FALSE và trả về số xuất hiện thường xuyên nhất cho hàm INDEX dưới dạng số hàng: