Công thức chung
(=MODE(IF(criteria,data)))
Tóm lược
Để tính toán chế độ có điều kiện với một hoặc nhiều tiêu chí, bạn có thể sử dụng công thức mảng dựa trên hàm IF và MODE. Trong ví dụ được hiển thị, công thức trong F5 là:
(=MODE(IF(group=E5,data)))
trong đó "nhóm" là phạm vi được đặt tên B5: B14 và "dữ liệu" là phạm vi được đặt tên C5: C14.
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
Chức năng MODE không có sẵn cách áp dụng tiêu chí. Cho một phạm vi, nó sẽ trả về số xuất hiện thường xuyên nhất trong phạm vi đó.
Để áp dụng tiêu chí, chúng tôi sử dụng hàm IF bên trong MODE để lọc các giá trị trong một phạm vi. Trong ví dụ này, hàm IF lọc các giá trị theo nhóm với biểu thức như sau:
IF(group=E5,data)
Điều này so sánh từng giá trị trong "nhóm" phạm vi được đặt tên với giá trị trong E5, là "A". Vì kiểm tra logic được áp dụng cho một mảng có nhiều giá trị, kết quả là một mảng các giá trị TRUE FALSE:
(TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE)
trong đó mỗi TRUE tương ứng với một hàng có nhóm là "A". Mảng này trở thành một bộ lọc. Đối với mỗi TRUE, IF trả về giá trị tương ứng trong phạm vi được đặt tên là "dữ liệu". Giá trị FALSE không thay đổi. Kết quả cuối cùng của IF là mảng này:
(3;FALSE;3;FALSE;5;FALSE;1;FALSE;2;FALSE)
Chỉ lưu ý các giá trị trong nhóm A đã tồn tại, các giá trị nhóm B bây giờ là FALSE. Mảng này được trả về hàm MODE, hàm này sẽ tự động bỏ qua các giá trị FALSE và trả về số xuất hiện thường xuyên nhất, là 3.
Lưu ý: khi IF được sử dụng theo cách này để lọc các giá trị bằng thao tác mảng, công thức phải được nhập bằng control + shift + enter.
Tiêu chí bổ sung
Để áp dụng nhiều hơn một tiêu chí, bạn có thể lồng một IF khác bên trong IF đầu tiên:
(=MODE(IF(criteria1,IF(criteria2,data))))
