Hàm AVERAGEIFS trong Excel là gì là thắc mắc của nhiều người. Đây là một hàm giúp tính trung bình có điều kiện trong Excel, đặc biệt hữu ích khi làm việc với dữ liệu lớn. Hãy cùng tham khảo cách dùng công thức hàm AVERAGEIFS như thế nào để đạt hiệu quả ngay dưới đây.
Tóm tắt nội dung
Hàm AVERAGEIFS trong Excel là gì?
Hàm AVERAGEIFS trong Excel dùng để làm gì? Câu trả lời là: đây là một công cụ tính toán cho phép bạn lấy trung bình cộng của các giá trị chỉ khi chúng thỏa mãn một hay nhiều điều kiện cho trước. Điều này khác với hàm AVERAGE trong Excel – vốn tính trung bình toàn bộ phạm vi dữ liệu mà không lọc điều kiện.
Khả năng lọc dữ liệu trước khi tính toán của AVERAGEIFS giúp người dùng xử lý thông tin chính xác hơn, đặc biệt là với các bảng tính có hàng trăm dòng dữ liệu.
Công thức của hàm AVERAGEIFS
Cấu trúc hàm như sau:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Giải thích:
- average_range: Vùng chứa giá trị cần tính trung bình.
- criteria_range: Vùng chứa điều kiện.
- criteria: Điều kiện cần thỏa mãn.
Cách dùng hàm AVERAGEIFS trong Excel (kèm ví dụ thực tế)
Tính trung bình với điều kiện nhỏ hơn
Giả sử bạn có danh sách lương của nhân viên. Muốn tính trung bình các mức lương dưới 6 triệu, dùng công thức: =AVERAGEIFS(C2:C6, C2:C6, “<6000000”)
Ý nghĩa: Chỉ các giá trị trong C2:C6 bé hơn 6 triệu mới được đưa vào phép tính trung bình.
Hàm AVERAGEIFS nhiều điều kiện số
Ví dụ: Tính điểm trung bình các học sinh có điểm HK1 >5 và <8:
=AVERAGEIFS(D2:D6, D2:D6, “>5”, D2:D6, “<8”)
Kết quả sẽ chỉ tính điểm của học sinh nằm trong khoảng từ 5 đến 8.
Kết hợp điều kiện số và văn bản
Tình huống: Tính điểm học kỳ 2 trung bình cho học sinh nam có điểm >6:
=AVERAGEIFS(E2:E6, C2:C6, “Nam”, E2:E6, “>6”)
Trong đó:
- C2:C6, “Nam” là điều kiện giới tính.
- E2:E6, “>6” là điều kiện điểm số.
Tính trung bình với 3 điều kiện
Giả sử cần tính điểm học kỳ 1 của học sinh nữ, điểm HK1 >6 và điểm HK2 <10:
=AVERAGEIFS(D2:D7, C2:C7, “Nữ”, D2:D7, “>6”, E2:E7, “<10”)
Cách này cho phép bạn lọc dữ liệu phức tạp mà không cần tách bảng.
Loại trừ điều kiện bằng hàm AVERAGEIFS
Tính lương trung bình của nhân viên không tên “Nguyễn”: =AVERAGEIFS(D2:D6, B2:B6, “<>Nguyễn”)
Lưu ý: <> là biểu tượng của “khác với”.
Tính trung bình với điều kiện lớn hơn
Nếu bạn muốn tính trung bình lương trên 6 triệu: =AVERAGEIFS(C2:C6, C2:C6, “>6000000”)
Chỉ các mức lương vượt ngưỡng 6 triệu mới được tính.
Kết hợp hàm IF và AVERAGE
Trong một số trường hợp đặc biệt, bạn có thể kết hợp hàm IF và AVERAGE để xử lý điều kiện phức tạp hơn – nhất là khi cần tính trung bình theo điều kiện tùy chỉnh không thể gói gọn trong AVERAGEIFS.
Ví dụ: =AVERAGE(IF(C2:C6>5000000, C2:C6))
Gợi ý: Đây là công thức mảng, bạn cần nhấn Ctrl + Shift + Enter để thực hiện.
So sánh nhanh AVERAGEIF và AVERAGEIFS
| Tiêu chí | AVERAGEIF | AVERAGEIFS |
| Số lượng điều kiện | 1 | Nhiều |
| Dễ sử dụng | Có | Cần cấu trúc chính xác hơn |
| Dữ liệu phức tạp | Không phù hợp | Phù hợp với bảng dữ liệu có nhiều điều kiện |
AVERAGEIF trong Excel là tiền thân của AVERAGEIFS nhưng bị giới hạn về số điều kiện.
Các lỗi thường gặp khi dùng hàm AVERAGEIFS
Lỗi #DIV/0!
Xảy ra khi không có giá trị nào đáp ứng điều kiện. Excel không thể chia cho 0.
Khắc phục: Sử dụng IFERROR để che lỗi: =IFERROR(AVERAGEIFS(…), “Không có dữ liệu”)
Ô dữ liệu bị trống
Khi trong vùng dữ liệu chứa ô trống, phép tính sẽ bị ảnh hưởng hoặc không trả về kết quả.
Giải pháp: Điền giá trị mặc định vào các ô trống hoặc dùng công thức lọc loại ô trống.
Lưu ý khi sử dụng hàm AVERAGEIFS
- Không áp dụng cho dữ liệu chứa văn bản trong vùng tính trung bình.
- Có thể dùng dấu * và ? làm ký tự đại diện trong điều kiện (ví dụ: “Nguyễn *”).
- Các giá trị TRUE/FALSE sẽ được coi là số 1 và 0.
- Nếu tất cả dữ liệu bị loại, hàm sẽ trả về lỗi.
Tổng kết
Hàm tính trung bình có điều kiện trong Excel – AVERAGEIFS – là công cụ không thể thiếu khi bạn cần xử lý dữ liệu phức tạp. Với khả năng tính toán trung bình dựa trên nhiều tiêu chí, hàm AVERAGEIFS giúp tiết kiệm đáng kể thời gian và công sức.
Dù bạn là kế toán, nhân viên hành chính hay chuyên viên phân tích số liệu, việc nắm vững cách dùng hàm AVERAGEIFS sẽ giúp công việc trở nên chuyên nghiệp và hiệu quả hơn. Hãy ứng dụng linh hoạt các ví dụ trên để phù hợp với từng tình huống thực tế trong bảng tính của bạn.
Đối với những người dùng Excel chuyên nghiệp, việc sở hữu một chiếc máy tính xách tay với hiệu năng ổn định là điều vô cùng cần thiết để có thể thao tác mượt mà trên các tệp Excel chứa hàng chục trang tính và lượng lớn thông tin cần truy xuất. Hãy đến ngay Viettel Store để trải nghiệm và lựa chọn cho mình một chiếc máy tính xách tay phù hợp với nhu cầu.
Xem thêm:
