[Excel] Tìm giá trị được lặp lại nhiều nhất

Giả sử chúng ta có một cuộc bình chọn người đẹp nào là "tài sắc vẹn toàn" nhất trong nhóm. Qua bỏ phiếu kín, người kiểm phiếu đã điền tên các thí sinh lên một hàng trong Excel, vậy làm sao thống kê được ai là người được bình chọn nhiều nhất và đạt bao nhiêu phiếu?

Minh họa dữ liệu và kết quả cần có

Muốn biết kết quả người đẹp nào thắng cuộc, hãy chọn một ô, copy công thức sau vào Formular Bar và nhấn Ctrl + Shift + Enter
=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))  & " (" &MAX(COUNTIF(B2:K2,B2:K2)) & " lần)"

Mấu chốt của công thức này nằm ở COUNTIF(B2:K2,B2:K2), nó sẽ đếm xem mỗi ô được lặp lại bao nhiêu lần trong khoảng B2:K2. Nếu bạn copy =COUNTIF(B2:K2,B2:K2) vào ô nào đó rồi nhấn F2, sau đó nhấn tiếp F9 sẽ thấy Excel trả về một mảng {2,2,3,1,2,3,1,3,2,1}, tương ứng với số lần lặp lại của từng ô.

Kết quả của =COUNTIF(B2:K2, B2:K2)

Vậy MAX(COUNTIF(B2:K2,B2:K2)) sẽ cho ta kết quả là số lớn nhất trong mảng này, chính là 3.
Lúc này MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0) tương đương với MATCH(3, {2,2,3,1,2,3,1,3,2,1},0). Hàm này sẽ tìm vị trí của số 3 trong mảng {2,2,3,1,2,3,1,3,2,1}, nếu không tìm được thì trả về 0, ở đây sẽ trả về 3.

INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0)) lúc này đơn giản chỉ còn là INDEX(B2:K2, 3). Nó sẽ trả về ô thứ 3 trong khoảng B2:K2, tức là Trinh.

Còn đoạn & " (" &MAX(COUNTIF(B2:K2,B2:K2)) & " lần)" đơn giản chỉ là thêm số lần "Trinh" được lặp lại và chữ "lần" thôi

Xin chúc mừng em Trinh đã được nhiều anh ái mộ nhất

Thể loại: array,excel,formula,function