Cách dùng hàm AVERAGEIFS trên Excel

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. Trong bài viết dưới đây chúng tôi sẽ hướng dẫn bạn đọc cách dùng hàm AVERAGEIFS, tính giá trị trung bình kết hợp nhiều điều kiện khác nhau trong bảng dữ liệu.

• 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

Hướng dẫn dùng 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ị đó 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.

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.

Bước 1:

Bạn nhập công thức =AVERAGEIFS(D2:D6,D2:D6,">3000000",D2:D6,"<7000000").

Trong đó D2:D6 là cột dữ liệu cần tính và D2:D6,">3000000",D2:D6,"<7000000" là các điều kiện trong vùng dữ liệu đó.

Bước 2:

Sau đó sẽ hiển thị 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ố tiền 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.

Bước 1:

Cũng tại ô nhập kết quả, người dùng nhập công thức tính giá trị là =AVERAGEIFS(E2:E6,E2:E6,"250000",C2:C6,"Nam").

Trong đó 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.

Bước 2:

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.

Bước 1:

Tại ô nhập kết quả chúng ta nhập công thức tính là =AVERAGEIFS(E2:E6,E2:E6,">200000",C2:C6,"Nam",D2:D6,">4500000").

Trong đó E2:E6 là vùng dữ liệu cần tính giá trị trung bình, 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.

Bước 2:

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ước 1:

Cũng tại ô hiển thị kết quả người dùng nhập công thức tính hàm AVERAGEIFS là =AVERAGEIFS(D2:D6,D2:D6,">7500000",C2:C6,"Nữ") rồi nhấn Enter.

Bước 2:

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:

• 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!