Hàm AVERAGEIFS trong Excel: Cách tính trung bình cộng theo nhiều điều kiện

Hàm AVERAGE là hàm Excel cơ bản, được dùng để tính trung bình cộng của các giá trị trong bảng dữ liệu. Và khi sử dụng nâng cao hàm AVERAGE, người dùng có thể kết hợp thêm để tính toán đa dạng các nội dung như, hàm AVERAGEIF để tính trung bình cộng với 1 điều kiện, hay hàm DAVERAGE. Hôm nay, chúng ta tìm hiểu thêm một hàm tính trung bình ít phổ biến hơn nhưng cũng vô cùng hữu ích đó là AVERAGEIFS.

Hàm AVERAGEIFS là gì?

Trong Excel, hàm AVERAGEIFS để tính giá trị trung bình của những số trong một dải ô đáp ứng được cùng lúc nhiều điều kiện. Các tiêu chí được sử dụng cho AVERAGEIFS có thể bao gồm toán tử logic (>,<,<>,=) và toán tử đại diện (*,?).

Nếu bạn thắc mắc hàm AVERAGEIFS khác với AVERAGEIF như thế nào thì rất đơn giản, AVERAGEIF chỉ tính được giá trị trung bình của các ô đáp ứng 1 điều kiện duy nhất. Nếu có nhiều hơn 1 điều kiện, bạn phải sử dụng kết hợp nó với hàm khác, hoặc đơn giản hơn là sử dụng hàm AVERAGEIFS.

Trong phần tiếp theo, bài viết sẽ đưa ra công thức hàm AVERAGEIFS cùng các ví dụ cụ thể để bạn dễ nắm bắt hơn.

Công thức hàm AVERAGEIFS Excel

Hàm AVERAGEIFS trên Excel có công thức hàm là AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

Trong đó:

• average_range: Vùng dữ liệu cần tính giá trị trung bình cộng bao gồm số hoặc tên hoặc mảng tham chiếu có chứa số, là tham số bắt buộc.
• criteria_range1, criteria_range2: Phạm vi chứa điều kiện cần tính tổng, criteria_range1 là tham số bắt buộc, các criteria_range còn lại tùy chọn tối đa 127 đối số, là tham số bắt buộc.
• criteria1, criteria2, ...: Là điều kiện cần tính trung bình cộng, riteria1 là tham số bắt buộc, các criteria khác tùy chọn chứa tối đa 127 điều kiện.

Lưu ý khi dùng hàm:

• Nếu giá trị các đối số có chứa giá trị logic hoặc ô trống thì giá trị đó sẽ bỏ qua.
• Nếu phạm vi là ô trống hoặc dạng văn bản thì hàm trả về giá trị lỗi #DIV/0.
• Nếu các ô trong Criteria_range là ô trống, hàm mặc định bằng 0.
• Nếu giá trị trong ô dữ liệu là giá trị logic = True thì được coi là 1, nếu giá trị False được coi là 0.
• Nếu các giá trị trong average_range không thể chuyển thành số thì báo lỗi #DIV/0.
• Nếu không có giá trị nào đáp ứng tất cả các điều kiện thì hàm báo lỗi #DIV/0!.
• Có thể sử dụng các ký tự thay thế như *.?. trong điều kiện tính trung bình cộng.

Ví dụ cách dùng hàm AVERAGEIFS trong Excel

Chúng ta sẽ lấy ví dụ với bảng dữ liệu như dưới đây.

Ví dụ 1: Tính trung bình cộng tiền lương của nhân viên lớn hơn 3,000,000 và nhỏ hơn 7,000,000.

Từ yêu cầu đề bài, ta nhận thấy vùng cần tính trung bình cộng là D2 đến D6 (D2:D6), điều kiện để tính trung bình cộng là mức lương lớn hơn 3 triệu (D2:D6,">3000000") và nhỏ hơn 6 triệu (D2:D6,"<7000000"). Vậy công thức sẽ như sau:

=AVERAGEIFS(D2:D6,D2:D6,">3000000",D2:D6,"<7000000")

Nhấn Enter và kết quả trung bình cộng các dữ liệu dựa trên điều kiện đã thiết lập sẽ hiển thị. Số tiền lương trung bình tính được là 5,433,333.

Ví dụ 2: Tính trung bình cộng tiền lương thưởng của nhân viên Nam có mức Lương thưởng là 250,000.

Ta thấy, E2:E6 là vùng cần tính giá trị, E2:E6,"250000" là điều kiện thứ nhất cho giá trị tại vùng đó. C2:C6,"Nam" là điều kiện thứ 2 với vùng dữ liệu khác. Vậy thì công thức cho bài toán này sẽ là:

=AVERAGEIFS(E2:E6,E2:E6,"250000",C2:C6,"Nam")

Khi nhấn Enter sẽ ra kết quả là 250,000 như hình dưới đây.

Ví dụ 3: Tính trung bình cộng lương thưởng của nhân viên nam lớn hơn 200,000 và tiền lương lớn hơn 4,500,000.

Vùng dữ liệu cần tính giá trị trung bình là E2:E6 là, E2:E6,">200000" là điều kiện thứ 1 tại vùng dữ liệu E2:E6, C2:C6,"Nam" là điều kiện thứ 2 tại vùng dữ liệu C2:C6, D2:D6,">4500000" là điều kiện thứ 3 tại vùng dữ liệu D2:D6. Tại ô nhập kết quả chúng ta nhập công thức tính:

=AVERAGEIFS(E2:E6,E2:E6,">200000",C2:C6,"Nam",D2:D6,">4500000")

Nhấn Enter và kết quả sẽ là 375,000 như hình dưới đây. Vì chỉ có 2 nhân viên nam đạt đủ cả 3 điều kiện trong yêu cầu.

Ví dụ 4: Tính trung bình cộng lương nhân viên nữ có tiền lương lớn hơn 7,500,000.

Bạn cũng xác định vùng dữ liệu, điều kiện tương tự như 3 ví dụ trên và cuối cùng ta sẽ có công thức tính với hàm AVERAGEIFS là:

=AVERAGEIFS(D2:D6,D2:D6,">7500000",C2:C6,"Nữ") 

rồi nhấn Enter.

Kết quả sẽ báo lỗi #DIV0! do chỉ thỏa mãn 1 điều kiện trong công thức, không có ô nào thỏa mãn tất cả 2 điều kiện trong công thức.

Trên đây là cách áp dụng hàm AVERAGEIFS trong Excel để tính giá trị trung bình cộng có nhiều điều kiện khác nhau. Nhìn chung cách sử dụng rất đơn giản, bạn cần nhập vùng tính giá trị trước, sau đó là điều kiện trên từng vùng dữ liệu khác nhau.

Xem thêm:

• Cách kết hợp hàm Sumif và hàm Vlookup trên Excel
• Cách dùng hàm Lookup trong Excel
• Cách sử dụng hàm Vlookup trong Excel
• Tổng hợp các hàm số lượng giác trong Excel
• Cách tự động hiện tên khi nhập mã trong Excel
• Cách tính tuổi trong Excel

Chúc các bạn thực hiện thành công!