=AVERAGEIF(A2:A7,"North",C2:C7) tính trung bình doanh số trong C2:C7 ở những hàng có cột A là North. Hàm này hoạt động giống SUMIF, chỉ khác là nó chia tổng cho số hàng khớp điều kiện.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Average | |
| 2 | North | Apple | 120 | North | 90 | |
| 3 | South | Pear | 45 | North, Apple | 80 | |
| 4 | North | Pear | 110 | Over 50 | 120 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 195 | |||
| 7 | North | Apple | 40 |
F2 tính trung bình ba hàng North, 120, 110 và 40, và hiện 90. F3 cần hai điều kiện, North và Apple, nên dùng AVERAGEIFS: (120 + 40) / 2 = 80. F4 không có vùng tính trung bình riêng, nên nó tính trung bình chính những giá trị doanh số khớp điều kiện.
Cú pháp AVERAGEIF và AVERAGEIFS
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Thứ tự đối số là cái bẫy quen thuộc giống SUMIF và SUMIFS: AVERAGEIF đặt vùng cần tính trung bình ở cuối (và cho phép bỏ trống), còn AVERAGEIFS đặt nó ở đầu. Điều kiện được viết giống nhau ở cả hai hàm: "North", ">50", "<>0", "*apple*", hoặc một toán tử nối với một ô, ">"&F5. Ô trống và văn bản trong vùng tính trung bình đều bị bỏ qua.
Tính trung bình và bỏ qua số 0
AVERAGE tính số 0 là một giá trị, nên hai học sinh vắng mặt bị 0 điểm sẽ kéo điểm trung bình của lớp xuống. Ô trống thì khác: AVERAGE bỏ qua chúng. =AVERAGEIF(B2:B7,"<>0") bỏ qua luôn cả số 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Method | Result | |
| 2 | Ana | 80 | AVERAGE | 48 | |
| 3 | Ben | 0 | Ignore zeros | 80 | |
| 4 | Cara | 90 | Count of zeros | 2 | |
| 5 | Dan | Count of numbers | 5 | ||
| 6 | Eva | 70 | |||
| 7 | Finn | 0 |
AVERAGE lấy 240 chia cho 5, vì ô trống của Dan bị bỏ qua nhưng hai số 0 vẫn được tính, nên hiện 48. AVERAGEIF với "<>0" lấy 240 chia cho 3 và hiện 80. Gõ 60 vào B5 thì cả hai đều thay đổi; gõ 0 vào B5 thì chỉ AVERAGE thay đổi. Muốn bỏ qua cả số 0 lẫn số âm, hãy dùng ">0".
Vì sao AVERAGEIF trả về #DIV/0!
Khi không có gì khớp điều kiện, AVERAGEIF không có gì để chia và trả về #DIV/0!. Bọc nó trong IFERROR để hiện một dấu gạch, một thông báo hoặc một ô trống thay thế.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | West average | #DIV/0! | |
| 3 | South | Pear | 45 | With IFERROR | No sales | |
| 4 | North | Pear | 110 | North max | 120 | |
| 5 | East | Apple | 55 | North min | 40 | |
| 6 | South | Apple | 195 | Apple max | 195 | |
| 7 | North | Apple | 40 |
#DIV/0! Công thức chia cho 0 hoặc cho một ô trống.Không có hàng West nào, nên F2 hiện #DIV/0! và F3 hiện thông báo. Đổi A3 thành West thì cả hai đều hiện 45.
MAXIFS và MINIFS
Các ô từ F4 đến F6 trong trang tính trên tìm giá trị lớn nhất và nhỏ nhất theo điều kiện. Chúng dùng thứ tự đối số giống AVERAGEIFS, vùng cần tìm đặt đầu tiên: =MAXIFS(C2:C7,A2:A7,"North") trả về 120 và =MINIFS(C2:C7,A2:A7,"North") trả về 40. Khác với AVERAGEIF, chúng trả về 0 khi không có gì khớp, chứ không báo lỗi.
MAXIFS và MINIFS cần Excel 2019 trở lên, hoặc Microsoft 365. Trong Excel 2016 trở về trước, =MAX(IF(A2:A7="North",C2:C7)) cho kết quả tương tự; ở các phiên bản đó, nhấn Ctrl+Shift+Enter (Cmd+Shift+Enter trên Mac) để nhập công thức.
Luyện tập: trung bình với hai điều kiện
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Class | Score | Condition | Average | |
| 2 | Ana | A | 80 | Class A, no zeros | ||
| 3 | Ben | B | 75 | |||
| 4 | Cara | A | 0 | |||
| 5 | Dan | B | 60 | |||
| 6 | Eva | A | 90 | |||
| 7 | Finn | B | 0 | |||
| 8 | Gus | A | 70 |
Đến lượt bạn: Tính trung bình điểm của lớp A, bỏ qua các số 0 (học sinh vắng mặt). Viết công thức vào F2.
Trung bình của các trung bình: một lỗi thường gặp
Lấy trung bình của các giá trị trung bình từ những nhóm có kích thước khác nhau sẽ cho ra trung bình chung sai. North có ba hàng còn South có hai, nên trong trung bình của hai giá trị trung bình, mỗi hàng South chiếm tỷ trọng lớn hơn mức đáng có.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Formula | Result | |
| 2 | North | 120 | North | 90 | |
| 3 | South | 45 | South | 120 | |
| 4 | North | 110 | Average of the two | 105 | |
| 5 | South | 195 | All rows | 102 | |
| 6 | North | 40 |
E4 hiện 105, còn E5 là trung bình thật của năm hàng, 102. Khi các nhóm có kích thước khác nhau, hãy tính trung bình trực tiếp trên các hàng bằng một hàm AVERAGEIFS, hoặc chia SUMIFS cho COUNTIFS với cùng các điều kiện:
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
Điểm số có trọng số theo tín chỉ hay theo số lượng lại là một phép tính khác: đó là trung bình có trọng số.
Câu hỏi thường gặp
AVERAGEIF và AVERAGEIFS khác nhau thế nào?
AVERAGEIF nhận một điều kiện và đặt vùng cần tính trung bình ở cuối: =AVERAGEIF(A2:A7,"North",C2:C7). AVERAGEIFS nhận nhiều điều kiện và đặt vùng cần tính trung bình ở đầu: =AVERAGEIFS(C2:C7,A2:A7,"North",B2:B7,"Apple").
Làm sao để tính trung bình trong Excel mà bỏ qua số 0?
Dùng =AVERAGEIF(B2:B7,"<>0"). Công thức này chỉ tính trung bình các ô khác 0. Ô trống vốn đã bị AVERAGE và AVERAGEIF bỏ qua, nên chỉ những số 0 thật sự mới cần điều kiện này.
Vì sao AVERAGEIF trả về #DIV/0!?
Không có ô nào khớp điều kiện, nên Excel lấy tổng bằng 0 chia cho số lượng bằng 0. Bọc công thức lại để hiện nội dung khác: =IFERROR(AVERAGEIF(A2:A7,"West",C2:C7),"No data").
Làm sao để tìm giá trị lớn nhất theo điều kiện?
Dùng MAXIFS, với vùng cần tìm đặt đầu tiên: =MAXIFS(C2:C7,A2:A7,"North") trả về giá trị North lớn nhất. MINIFS hoạt động tương tự để tìm giá trị nhỏ nhất. Cả hai cần Excel 2019 trở lên, hoặc Microsoft 365.
