Hướng dẫn cách sử dụng Hàm AVERAGEIFS trong Excel đơn giản

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.

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

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.

Laptop Dell Inspiron 15 3520 (i5-1235U, 16GB, 512GB SSD,15.6 FHD, UMA, Win11, OfficeHS 21, Đen)

15,390,000 ₫

16,190,000 ₫

Mua ngay

MTXT HP 15-fd1045TU,Core 5-120U,16GB RAM,512GB SSD,Intel Graphics,15.6 FHD,Webcam,3 Cell,Wlan ax+BT,Win11 Home 64,Natural Silver,1Y WTY_9Z2X1PA

18,490,000 ₫

19,990,000 ₫

Mua ngay

MTXT HP 240 G9 i5-1235U,8GD4,512GSSD,14.0FHD,WLax,BT5,3C41WHr,W11SL,BẠC (9E5W3PT)

13,490,000 ₫

16,990,000 ₫

Mua ngay

MTXT HP 15-fd0237TU ,Core i5-1334U,8GB RAM,512GB SSD,Intel Graphics,15.6 Inches FHD,Webcam,3 Cell,Wlan ax+BT,Win11 Home 64,Natural Silver,1Y WTY_9Q972PA

16,590,000 ₫

18,790,000 ₫

Mua ngay

MTXT Acer N23Q23 (AG15-31P-32U6) (i3-N305; 8GB; 512GB SSD; UMA; 15.6 FHD; Win11; Bạc; NX.KRPSV.002)

9,190,000 ₫

9,990,000 ₫

Mua ngay

MTXT DELL Inspiron 15 3530 (i5-1334U, 8GB, 512GB SSD, 15.6 FHD, UMA, Win11, Office HS 21, Bạc, P112F010)

16,390,000 ₫

16,990,000 ₫

Mua ngay

Xem thêm:

• Phím tắt Merge Cell trong Excel: Cách gộp ô nhanh chóng và hiệu quả
• Cách tạo thời khóa biểu trên Excel đơn giản, độc đáo