Hướng dẫn dùng hàm AVERAGEIFS trong Excel

Hàm tính trung bình AVERAGE là một hàm được dùng nhiều trong Excel, thế nhưng vẫn còn các hàm khác để tính trung bình rất tiện ích mà còn có thể tính ra kết quả theo yêu cầu, chính là hàm AVERAGEIFS. Vậy nên hãy cùng mygear.vn tìm hiểu về cách dùng hàm AVERAGEIFS trong Excel nhé!

I. Hàm AVERAGEIFS là gì? Công dụng của hàm AVERAGEIFS trong Excel

Hàm AVERAGEIFS hay hàm tính trung bình nhiều điều kiện là hàm tính giá trị trung bình của toàn bộ các ô dựa trên các điều kiện có sẵn do các bạn điền vào. Cú pháp =AVERAGEIFS(range, criteria, [average_range]).

Trong đó:

• Range (Bắt buộc): Một hoặc nhiều ô để tính giá trị trung bình, gồm những số hoặc tên, mảng hoặc tham chiếu chứa số.
• Criteria_range1 (đối số bắt buộc), những đối số criteria_range tiếp (tùy chọn): Phạm vi từ 1 đến 127 để đưa ra những yêu cầu liên quan.
• Criteria1 (đối số bắt buộc), những đối số criteria tiếp (tùy chọn): 1 tới 127 yêu cầu ở kiểu số, công thức, tham chiếu ô hoặc văn bản chỉ định những ô sẽ được tính trị giá trung bình.

Lưu ý:

• Nếu average_range là giá trị trống hoặc dạng văn bản, hàm AVERAGEIFS sẽ trả về giá trị lỗi #DIV0!
• Nếu một ô trong phạm vi mục tiêu bị bỏ trống, hàm AVERAGEIFS sẽ xem ô đấy như giá trị 0.
• Những ô trong phạm vi chứa TRUE có giá trị là 1; Những ô trong phạm vi chứa FALSE có giá trị là 0.
• Chỉ tính trung bình những số tại vùng average_range có giá trị thỏa mãn yêu cầu đã cho.
• Nếu những ô trong average_range không chuyển sang dạng số, hàm AVERAGEIFS sẽ cho kết quả lỗi #DIV0!
• Nếu không có ô nào đáp ứng toàn bộ những yêu cầu, hàm AVERAGEIFSS sẽ trả kết quả giá trị lỗi #DIV/0!
• Có thể dùng những ký tự thay thế như *.?. trong yêu cầu tính trung bình cộng.

II. Hướng dẫn dùng hàm AVERAGEIFS tính trung bình nhiều điều kiện trong Excel

Ví dụ 1: Tính trung bình với hai điều kiện là số

Đề: Tính điểm trung bình HK1 của những học trò với điểm HK1 lớn hơn 6 và nhỏ hơn 9.

Bước 1: Điền công thức =AVERAGEIFS(D2:D7;D2:D7;">6";D2:D7;"<9”) vào ô tính trung bình.

Lưu ý: Bạn dùng dấu , hoặc ; cho thích hợp với công thức nha! Để biết nên dùng dấu nào bạn xem tại công thức phía dưới ô bạn đang ghi nhé!

Giải thích hàm:

• D2:D7: Là phạm vi cần tính trung bình (Điểm HK1).
• D2:D7;">6": Là điều kiện thứ 1 (Điểm HK1 lớn hơn 6).
• D2:D7;"<9”: Là điều kiện thứ 2 (Điểm HK1 nhỏ hơn 9).

Nhập công thức

Bước 2: Ấn phím Enter để hiện kết quả.

Giải thích kết quả:

• Tại đây lấy số điểm >6 và <9 tức là lấy điểm 7 và 8.
• HK1 gồm 3 bạn thỏa mãn yêu cầu là Nguyễn Văn A, Lê Thị B và Mai Thị H nên sẽ lấy điểm của 3 bạn để tính trung bình.

Ấn phím Enter để hiện kết quả

Ví dụ 2: Tính trung bình có 2 điều kiện số và chữ

Đề: Tính điểm trung bình HK2 của những học trò Nam có số điểm HK2 lớn hơn 7.

Bước 1: Điền công thức =AVERAGEIFS(E2:E7;C2:C7;"Nam";E2:E7;">7") tại ô tính trung bình.

Lưu ý: Bạn dùng dấu , hoặc ; cho thích hợp với công thức nha! Để biết nên dùng dấu nào bạn xem tại công thức phía dưới ô bạn đang ghi nhé!

Giải thích hàm:

• E2:E7: Là phạm vi cần tính trung bình (Điểm HK2).
• C2:C7;"Nam": Là điều kiện thứ 1 (Học sinh Nam).
• E2:E7;">7": Là điều kiện thứ 2 (Điểm HK2 lớn hơn 7).

Nhập công thức

Bước 2: Ấn phím Enter để có kết quả.

Giải thích kết quả:

• Tại đây lấy yêu cầu là học trò Nam và điểm HK2 >7 tức là lấy điểm 8 và 9.
• HK2 có 3 bạn học trò Nam đủ yêu cầu là Nguyễn Văn M, Lê Văn C, Huỳnh Văn D. Vì vậy sẽ dùng điểm của 3 bạn đó để tính trung bình.

Ấn phím Enter để hiện kết quả

Ví dụ 3: Tính trung bình cùng 3 điều kiện

Đề: Tính điểm trung bình HK1 của những học trò Nữ có điểm HK1 > 6, điểm HK2 <10.

Bước 1: Điền công thức =AVERAGEIFS(D2:D7;C2:C7;"Nữ";D2:D7;">6";E2:E7;"<10”) tại ô tính trung bình.

Lưu ý: Bạn dùng dấu , hoặc ; cho thích hợp với công thức nha! Để biết nên dùng dấu nào bạn xem tại công thức phía dưới ô bạn đang ghi nhé!

Giải thích hàm:

• D2:D7: Là phạm vi cần tính trung bình (Điểm HK1).
• C2:C7;"Nữ": Là điều kiện thứ 1 (Học sinh Nữ).
• D2:D7;">6": Là điều kiện thứ 2 (Điểm HK1 lớn hơn 6).
• E2:E7;"<10": Là điều kiện thứ 3 (Điểm HK2 nhỏ hơn 10).

Nhập công thức

Bước 2: Ấn phím Enter để hiện kết quả.

Giải thích kết quả:

Tại đây lấy điều kiện là học trò Nữ và điểm HK1 >6 và điểm HK2 <10 tức là lấy 7, 8, 9 điểm.

HK2 gồm 2 bạn Nữ thỏa mãn yêu cầu là Lê Thị B và Mai Thị H nên sẽ lấy điểm của hai bạn để tính trung bình.

Ấn phím Enter để hiện kết quả

Ví dụ 4: Tính trung bình có 3 điều kiện

Đề: Tính điểm trung bình HK2 của học trò Nam với điểm HK1 <9 và điểm HK2 >5.

Bước 1: Điền công thức =AVERAGEIFS(E2:E7;C2:C7;"Nam";D2:D7;"<9";e2:e7;">5") ở ô tính trung bình.

Lưu ý: Bạn dùng dấu , hoặc ; cho thích hợp với công thức nha! Để biết nên dùng dấu nào bạn xem tại công thức phía dưới ô bạn đang ghi nhé!

Giải thích hàm:

• E2:E7: Là phạm vi cần tính làng nhàng (Điểm HK2).
• C2:C7;"Nam": Là điều kiện thứ 1 (Học trò Nam).
• D2:D7;"<9”: Là điều kiện thứ 2 (Điểm HK1 nhỏ hơn 9).
• E2:E7;">5": Là điều kiện thứ 3 (Điểm HK2 lớn hơn 5).

Nhập công thức

Bước 2: Ấn phím Enter để hiện kết quả.

Giải thích kết quả:

• Tại đây lấy điều kiện là học trò Nam, điểm HK1 <9 và điểm HK2 >5 tức là lấy từ điểm 1 tới 8 ở HK1 và điểm 6 tới 10 ở HK2.
• Tại HK1 gồm 2 bạn Nam thỏa mãn yêu cầu là Nguyễn Văn A và Huỳnh Văn D.
• Tại HK2 thì toàn bộ học trò Nam đều thỏa mãn yêu cầu.
• Cuối cùng cả Nguyễn Văn A và Huỳnh Văn D đều thỏa mãn yêu cầu nên sẽ lấy điểm của hai bạn để tính trung bình.

Ấn phím Enter để hiện kết quả

III. Những lỗi thường gặp khi dùng hàm AVERAGEIFS

1. Lỗi #DIV/0!

Đây là lỗi rất phổ biến nếu bạn vận dụng hàm AVERAGEIFS tại 1 file có nhiều dữ liệu.

Lỗi #DIV/0!

Nguyên nhân: Do không tìm ra giá trị của điều kiện đã cho (Criteria_range) trong vùng cần tính.

Nguyên nhân gây ra lỗi

Cách khắc phục: Đổi lại điều kiện.

2. Không nhận ô trống

Nếu bạn dùng hàm AVERAGEIFS lên một ô trống không có số liệu thì hàm AVERAGEIFS sẽ mặc định nhìn thông tin trong ô đấy là 0 và không dùng để tính.

Trên đây là bài viết tìm hiểu về cách tính trung bình cộng nhiều điều kiện tại Excel. Nếu có những câu hỏi thắc mắc bạn hãy bình luận ở phía dưới nhé! Chúc bạn thành công!