Cách sử dụng hàm AVERAGEIFS kèm ví dụ minh hoạ

Hàm AVERAGEIFS là gì? Cách sử dụng hàm AVERAGEIFS kèm ví dụ minh hoạ!

Excel cung cấp các hàm tính giúp mọi công việc được thực hiện nhanh chóng, hiệu quả hơn. Chắc hẳn bạn đã quen với hàm AVERAGE để tính trung bình cộng phổ biến trong các bảng tính, vậy nếu cần tính trung bình của các giá trị dựa trên nhiều điều kiện thì phải làm thế nào? Lúc này, hãy để hàm AVERAGEIFS xử lý!

Nếu như bạn chưa biết:

• Hàm AVERAGEIFS là gì? Cần nhập cú pháp như thế nào?
• Cách dùng hàm AVERAGEIFS trong Excel?

Vậy thì đừng nên bỏ qua bài viết này bởi Mega sẽ hướng dẫn chi tiết cách dùng hàm AVERAGEIFS trong Excel đơn giản kèm ví dụ cụ thể!

Hàm Averageifs tính trung bình cộng dựa trên nhiều điều kiện

I. Chức năng và cú pháp của hàm AVERAGEIFS

Hàm AVERAGEIFS là hàm nâng cao của hàm tính trung bình cộng AVERAGE. Dẫu không được dùng phổ biến như “người anh” của mình, nhưng AVERAGEIFS vẫn là hàm giúp ích rất nhiều cho các yêu cầu tính toán phức tạp.

AVERAGEIFS được dùng để tính giá trị trung bình của các ô hoặc vùng dữ liệu dựa trên điều kiện người dùng cho sẵn.

Cú pháp:

= AVERAGEIFS (average_range1, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Công thức hàm AVERAGEIFS

Trong đó:

average_range1: là vùng dữ liệu cần tính giá trị trung bình cộng, đây là tham số bắt buộc, có thể chứa số, tên hoặc mảng tham chiếu chứa số

criteria_range1, criteria_range2…: là phạm vi chứa điều kiện cần thoả mãn để tính tổng trung bình. Criteria_range1 là phần bắt buộc, các phạm vi tiếp theo là tuỳ chọn. Excel cho phép người dùng thêm vào tối đa 127 đối số.

criteria1, criteria2, ...: là các điều kiện cần thoả mãn. Trong đó, criteria1 là phần bắt buộc, criteria2 và các tiêu chí tiếp theo là tuỳ chọn, bạn có thể thêm đến 127 điều kiện.

Lưu ý:

• Các ô hoặc vùng giá trị phải chứa số, tên hoặc tham chiếu chứa đối số, nếu ô chứa giá trị không phải là số (giá trị logic, ô rỗng) hàm sẽ tự động bỏ qua.
• Nếu phạm vi tính là các ô trông hoặc dữ liệu dạng văn bản, hàm sẽ trả về kết quả lỗi #DIV/0.

Lỗi #DIV/0! ở hàm AVERAGEIFS

• Nếu một ô trong phạm vi chứa điều kiện bị bỏ trống, hàm sẽ tự động quy về giá trị 0.
• Nếu các giá trị trong ô dữ liệu là giá trị logic TRUE sẽ được quy định là 1, là FALSE sẽ quy về 0
• Nếu các ô giá trị trong average_range là dạng văn bản hay tham chiếu không thể chuyển thành số, hàm sẽ
• Hàm sẽ chỉ tính trung bình cộng của các ô dữ liệu ở vùng dữ liệu (average_range) thoả mãn điều kiện, nếu không có giá trị nào đáp ứng tiêu chí hàm sẽ báo lỗi #DIV/0!
• Bạn có thể sử dụng các ký tự thay thế như *_. trong phần điều kiện

II. Cách dùng hàm AVERAGEIFS kèm ví dụ cụ thể

Hàm AVERAGEIFS với điều kiện là số

Muốn tính trung bình cộng doanh thu của các tháng có doanh thu lớn hơn 7,000,000 và nhỏ hơn 10,000,000. Ta nhập công thức sau: =AVERAGEIFS(B2:E13,B2:E13,">7000000",B2:E13,"<10000000")

Cách dùng hàm AVERAGEIFS trong Excel

Trong đó:

B2:E13: vùng chứa dữ liệu cần tính, ở đây ta tính doanh thu nên cần khoanh mảng dữ liệu chứa số doanh thu

B2:E13,">7000000": Phạm vi chứa điều kiện và điều kiện thứ nhất, ta sẽ xét cột doanh thu với tiêu chí phải trên 7.000.000

B2:E13,"<10000000": Phạm vi chứa điều kiện và điều kiện thứ hai, ta sẽ xét cột doanh thu với tiêu chí phải trên 10.000.000

Kết quả cho ra sẽ là:

Doanh thu trung bình dựa trên điều kiện 

Hàm AVERAGEIFS với điều kiện là số và chữ

Muốn tính trung bình cộng thu nhập của riêng nhân viên bán hàng tầng 1 có số lượng bán trên 500, ta cần nhập công thức: =AVERAGEIFS(D2:D7,D2:D7,">500",C2:C7,"Bán hàng tầng 1")

Hàm AVERAGEIFS cho phép điều kiện dạng số lẫn văn bản

Trong đó:

D2:D7: Vùng dữ liệu chứa giá trị cần tính, ở đây ta cần số lượng bán trung bình nên khoanh mảng dữ liệu cột “Số lượng bán”

D2:D7,">500": Phạm vi chứa điều kiện và điều kiện thứ 1

C2:C7,"Bán hàng tầng 1": Pham vi chứa điều kiện và điều kiện thứ 2

Sau khi Enter ta thu được kết quả như hình dưới:

Số lượng bán trung bình 

Hàm AVERAGEIFS với 3 điều kiện

Để tính trung bình cộng tiền lương của nhân viên bán hàng tầng 2 có số lượng bán trên 400 và tiền thưởng phải trên 1.000.000. Ta nhập theo công thức sau: =AVERAGEIFS(E2:E7,C2:C7,"Bán hàng tầng 2",D2:D7,">400",E2:E7,">1000000")

Hàm Average cho phép bổ sung nhiều điều kiện 

Trong đó:

E2:E7: Phạm vi chứa giá trị cần tính, ở đây cần tính tiền thưởng trung bình nên ta khoanh mảng giá trị cột Tiền thưởng

C2:C7,"Bán hàng tầng 2": Phạm vi chứa điều kiện và điều kiện 1

D2:D7,">400": Phạm vi chứa điều kiện và điều kiện 2

E2:E7,">1000000": Phạm vi chứa điều kiện và điều kiện 3

Hàm sẽ cho ra kết quả:

Hàm cho kết quả với tốc độ nhanh chóng

Cũng với bảng dữ liệu trên, ta thử nâng mức điều kiện lên là tính trung bình cộng dựa trên yêu cầu phải là nhân viên bán hàng có số lượng bán trên 900, tiền thưởng phải trên 3.000.000.

Sử dụng hàm Averageifs 3 điều kiện 

Kết quả cho ra lỗi #DIV/0! bởi không có giá trị nào thoả mãn.

 Lỗi #DIV/0! vì không có giá trị nào thoả mãn điều kiện

III. Tổng kết

Hàm AVERAGEIFS giúp bạn dễ dàng xử lý các yêu cầu tính toán phức tạp chỉ trong một cú pháp. Vừa rồi, Mega đã hướng dẫn cụ thể cách sử dụng hàm AVERAGEIFS kèm ví dụ dễ hiểu. Hi vọng những thông tin trên sẽ hữu ích cho công việc, học tập của bạn!

Xem thêm >>>

Hướng dẫn sử dụng hàm GAMMADIST trong Excel 

Hướng dẫn sử dụng hàm AVERAGEIF có điều kiện trong Excel

Hàm LOGINV trong Excel là gì? Công thức Hàm LOGINV

copyright © mega.com.vn