Công thức chung
(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))
Tóm lược
Để liệt kê các số xuất hiện thường xuyên nhất trong một cột (tức là phổ biến nhất, phổ biến thứ hai, phổ biến thứ ba, v.v.), bạn có thể tạo công thức mảng dựa trên bốn hàm Excel: IF, MODE, MATCH và ISNUMBER. Trong ví dụ được hiển thị, công thức trong D5 là:
(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))
trong đó "dữ liệu" là dải ô được đặt tên là B5: B16. Sau đó, công thức được sao chép vào các hàng bên dưới D5 để xuất ra danh sách mong muốn các số thường gặp nhất.
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
Cốt lõi của công thức này là hàm MODE, trả về số xuất hiện thường xuyên nhất trong một phạm vi hoặc mảng. Phần còn lại của công thức chỉ tạo một mảng được lọc cho MODE để sử dụng trong mỗi hàng. Phạm vi mở rộng $ D $ 4: D4 hoạt động để loại trừ các số đã được xuất trong $ D $ 4: D4.
Làm việc từ trong ra ngoài:
- Hàm MATCH được sử dụng trước tiên hãy kiểm tra tất cả các số trong dải ô được đặt tên là "dữ liệu" so với các số hiện có trong dải ô mở rộng $ D $ 4: D4
- ISNUMBER chuyển đổi các giá trị phù hợp thành TRUE và các giá trị không khớp thành FALSE
- 1-NUMBER đảo ngược mảng và phép toán xuất ra các giá trị và số không
- IF sử dụng đầu ra mảng của # 3 ở trên để lọc danh sách giá trị ban đầu, loại trừ các số đã có trong $ D $ 4: D4
- Hàm MODE trả về số thường xuyên nhất trong đầu ra của mảng ở bước # 4
Trong ô D5, không có bộ lọc nào xảy ra và kết quả của mỗi bước ở trên trông giống như sau:
(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93
Trong ô D6, với 93 đã có trong D5, đầu ra trông giống như sau:
(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95
Xử lý lỗi
Chức năng MODE sẽ trả về lỗi # N / A khi không có chế độ. Khi bạn sao chép công thức xuống các hàng tiếp theo, bạn có thể gặp phải lỗi # N / A. Thay vào đó, để bẫy lỗi này và trả về một chuỗi trống (""), bạn có thể sử dụng IFERROR như sau:
=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")
