Hướng dẫn này sẽ trình bày cách tính giá trị trung bình đáp ứng các tiêu chí nhất định trong Excel và Google Trang tính.
Hàm MEDIAN
Hàm MEDIAN tính giá trị trung bình trong một chuỗi số.
= MEDIAN (C2: C10)
Tuy nhiên, không có Hàm “Giá trị trung bình” tích hợp để chỉ tính giá trị trung bình cho các số đáp ứng các tiêu chí nhất định.
Lưu ý: Bạn có thể quen thuộc với Hàm AVERAGEIFS sẽ tính giá trị trung bình cho các số đáp ứng các tiêu chí nhất định. Không có giải pháp thay thế trung gian.
Trung vị If - Công thức mảng
Vì vậy, để hoàn thành mục tiêu tính giá trị trung bình của các số đáp ứng các tiêu chí nhất định, chúng ta phải sử dụng Công thức mảng. Với công thức mảng của chúng tôi, chúng tôi sẽ sử dụng Hàm IF bên trong Hàm MEDIAN để chúng tôi chỉ lấy giá trị trung bình của các giá trị đáp ứng tiêu chí đã nhập trong Hàm IF.
Sử dụng tập dữ liệu trước đây của chúng tôi, hãy lấy giá trị trung bình cho Apple.
= MEDIAN (IF ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))
Excel 2022 trở về trước
Trong Office 365 và các phiên bản Excel sau năm 2022, bạn có thể chỉ cần nhập công thức trên như bình thường (bằng cách nhấn ĐI VÀO).
Tuy nhiên, đối với Excel 2022 trở về trước, bạn phải nhập công thức bằng cách nhấn CTRL + SHIFT + ENTER. Sau khi làm như vậy, bạn sẽ nhận thấy dấu ngoặc nhọn xuất hiện xung quanh công thức:
{= MEDIAN (IF ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))}
Quan trọng: không tự viết các dấu ngoặc nhọn này, chúng chỉ được nhập bằng CTRL + SHIFT + ENTER.
Công thức hoạt động như thế nào?
Hãy nhớ đây là công thức của chúng tôi:
{= MEDIAN (IF ($ A $ 2: $ A $ 10 = $ E2, $ C $ 2: $ C $ 10))}
Công thức hoạt động khi đánh giá tiêu chí của từng giá trị là TRUE hoặc FALSE.
= MEDIAN (IF ({FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE}, {1287; 1205; 1243; 1482; 1261; 1042; 1090; 1748; 1909}))
Tiếp theo, Hàm IF thay thế mỗi giá trị bằng FALSE nếu điều kiện của nó được đáp ứng.
= MEDIAN ({FALSE; 1205; FALSE; FALSE; 1261; FALSE; 1090; 1748; FALSE})
Bây giờ Hàm MEDIAN bỏ qua các giá trị FALSE và tính giá trị trung bình của các giá trị còn lại (1233 là giá trị trung bình của hai giá trị giữa: 1205 và 1261).
IF trung vị - Nhiều tiêu chí
Bạn cũng có thể tính giá trị trung bình dựa trên nhiều tiêu chí bằng cách sử dụng Boolean Logic.
Hãy xem tập dữ liệu mới với các cột Vị trí, Năm và Kho:
Bây giờ đây là công thức để tính giá trị trung bình dựa trên nhiều tiêu chí:
= MEDIAN (IF ((A2: A4 = "B") * (B2: B4 = 2008), C2: C4))
Lưu ý ở đây, chúng tôi nhân hai bộ tiêu chí với nhau:
(G8: G10 = "b") * (H8: H10 = 2008)
Nếu cả hai tiêu chí đều TRUE thì nó sẽ tính là TRUE, nhưng nếu một (hoặc nhiều) tiêu chí là FALSE thì nó sẽ tính là FALSE.
Sử dụng phương pháp này, bạn có thể thêm nhiều tiêu chí khác nhau.
Trung vị nếu Công thức trong Google Trang tính
Tất cả các ví dụ trên đều hoạt động hoàn toàn giống nhau trong Google Trang tính cũng như trong Excel.
