Công thức chung
(=MEDIAN(IF(criteria,range)))
Tóm lược
Để tính giá trị trung vị có điều kiện dựa trên một hoặc nhiều tiêu chí, bạn có thể sử dụng công thức mảng sử dụng hàm MEDIAN và IF cùng nhau. Trong ví dụ được hiển thị, công thức trong F5 là:
=MEDIAN(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
Hàm MEDIAN không có sẵn cách áp dụng tiêu chí. Cho một phạm vi, nó sẽ trả về số MEDIAN (giữa) trong phạm vi đó.
Để áp dụng tiêu chí, chúng tôi sử dụng hàm IF bên trong MEDIAN để "lọc" các giá trị. Trong ví dụ này, hàm IF lọc theo nhóm như sau:
IF(group=E5,data)
Biểu thức 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 ("A"). Vì tiêu chí đượ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 như sau:
(TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE)
Trong mảng này, mỗi TRUE tương ứng với một giá trị trong nhóm A. Hàm IF đánh giá các kết quả này và trả về giá trị tương ứng từ phạm vi được đặt tên là "dữ liệu". Kết quả cuối cùng từ IF là
(1;2;3;3;5;FALSE;FALSE;FALSE;FALSE;FALSE)
Chỉ lưu ý các giá trị trong nhóm A còn tồn tại và các giá trị nhóm B bây giờ là FALSE. Mảng này được trả về hàm MEDIAN, hàm này sẽ tự động bỏ qua các giá trị FALSE và trả về giá trị trung vị, 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:
(=MEDIAN(IF(criteria1,IF(criteria2,data))))
Để tránh lồng thêm, bạn cũng có thể sử dụng logic boolean trong tiêu chí.
