AVERAGEIFS
Hàm AVERAGEIFS trả về trung bình (giá trị trung bình cộng) của các ô trong nhóm cho trước, trong đó một hoặc nhiều nhóm đáp ứng một hoặc nhiều điều kiện có liên quan.
AVERAGEIFS(giá trị trung bình; giá trị kiểm tra; điều kiện; giá trị kiểm tra…;điều kiện…)
giá trị trung bình: Một tập hợp chứa các giá trị sẽ được xem xét để tính giá trị trung bình. giá trị trung bình là một tham chiếu đến một tập hợp ô duy nhất, có thể chứa giá trị bất kỳ.
giá trị kiểm tra: Một tập hợp chứa các giá trị sẽ được kiểm tra. giá trị kiểm tra có thể chứa bất kỳ giá trị nào.
điều kiện: Một biểu thức so sánh hoặc kiểm tra các giá trị và kết quả trong giá trị lô-gic TRUE hoặc FALSE. điều kiện có thể bao gồm các toán tử so sánh, hằng số, toán tử ghép nối và, tham chiếu và ký tự đại diện. Bạn có thể sử dụng ký tự đại diện để khớp với bất kỳ ký tự duy nhất nào hoặc nhiều ký tự trong biểu thức. Bạn có thể sử dụng dấu ? (dấu hỏi chấm) để biểu thị một ký tự, dấu * (dấu hoa thị) để biểu thị nhiều ký tự, và dấu ~ (dấu ngã) để biểu thị rằng ký tự tiếp sau đó cần phải khớp, chứ không phải được dùng làm ký tự đại diện. điều kiện cũng có thể chứa một hàm REGEX thay vì ký tự đại diện.
giá trị kiểm tra…: Tùy ý bao gồm một hoặc nhiều tập hợp chứa các giá trị sẽ được kiểm tra. Mỗi tập hợp giá trị kiểm tra phải được theo sau bởi biểu thức điều kiện. Mẫu giá trị kiểm tra, điều kiện này có thể được lặp lại nhiều lần nếu cần.
điều kiện…: Nếu tập hợp tùy chọn của giá trị kiểm tra được bao gồm, điều kiện… là một biểu thức bổ sung dẫn đến giá trị lô-gic TRUE hoặc FALSE. Phải có một biểu thức điều kiện theo sau mỗi tập hợp giá trị kiểm tra; do đó, số lượng các đối số của hàm này sẽ luôn là số lẻ.
Ghi chú
Đối với mỗi cặp giá trị kiểm tra và điều kiện, giá trị tương ứng (cùng vị trí trong dãy) được so sánh với kiểm tra điều kiện. Nếu tất cả các kiểm tra điều kiện đều được đáp ứng, giá trị tương ứng trong giá trị trung bình được tính trong giá trị trung bình.
giá trị trung bình và tất cả các tập hợp giá trị kiểm tra phải có cùng kích thước.
Ví dụ |
|---|
Cho bảng sau: |
A | B | C | D | |
|---|---|---|---|---|
1 | Tuổi | Giới tính | Tình trạng | Lương |
2 | 35 | M | M | 71.000 |
3 | 27 | F | M | 81.000 |
4 | 42 | M | M | 86.000 |
5 | 51 | M | S | 66.000 |
6 | 28 | M | S | 52.000 |
7 | 49 | F | S | 62.000 |
8 | 63 | F | M | 89.000 |
9 | 22 | M | M | 34.000 |
10 | 29 | F | S | 42.000 |
11 | 35 | F | M | 56.000 |
12 | 33 | M | S | 62.000 |
13 | 61 | M | M | 91.000 |
=AVERAGEIFS(D2:D13; A2:A13; "<40"; B2:B13; "=M") trả về 54.750, lương trung bình của nam giới (được biểu thị bằng "M" trong cột B) dưới 40 tuổi. =AVERAGEIFS(D2:D13; A2:A13; "<40"; B2:B13; "=M"; C2:C13; "=S") trả về 57.000, lương trung bình của nam giới độc thân (được biểu thị bằng "S" trong cột C) dưới 40 tuổi. =AVERAGEIFS(D2:D13; A2:A13; "<40"; B2:B13; "=M"; C2:C13; "=M") trả về 52.500, lương trung bình của nam giới đã kết hôn (được biểu thị bằng "M" trong cột C) dưới 40 tuổi. =AVERAGEIFS(D2:D13; A2:A13; ">=40"; B2:B13; "=M"; C2:C13; "=M") trả về 88.500, lương trung bình của nam giới đã kết hôn (được biểu thị bằng "M" trong cột C) và ít nhất 40 tuổi. Bạn cũng có thể sử dụng toán tử ≥. =AVERAGEIFS(D2:D13; A2:A13; "<40"; B2:B13; "=F") trả về khoảng 59.667, lương trung bình của nữ giới (biểu thị bằng "F" trong cột B) dưới 40 tuổi. =AVERAGEIFS(D2:D13; A2:A13; "<>35"; C2:C13; "=S") trả về 56.800, lương trung bình của người không phải 35 tuổi và độc thân. Bạn cũng có thể sử dụng toán tử ≠. |
Ví dụ sử dụng REGEX |
|---|
Cho bảng sau: |
A | B | |
|---|---|---|
1 | 45 | marina@example.com |
2 | 41 | Aaron |
3 | 29 | michael@example.com |
4 | 64 | katrina@example.com |
5 | 12 | Sarah |
=AVERAGEIFS(A1:A5; B1:B5; REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})"); A1:A5; ">10") trả về 46, là giá trị trung bình của tất cả các ô trong A1:A5 lớn hơn 10 và trong đó ô tương ứng trong B1:B5 chứa một địa chỉ email. |