Cách sử dụng hàm AVERAGEIF trong Excel

Trong bài viết này, chúng ta sẽ tìm hiểu Cách sử dụng hàm AVERAGEIF trong Excel.

TRUNG BÌNH hoặc Ý NGHĨA dựa trên tiêu chí

Trong EXCEL, khi làm việc với giá trị trung bình hay thường được gọi là giá trị trung bình cộng. Sau đó, chúng tôi sử dụng chức năng AVERAGE. Nhưng nếu chúng ta có một tiêu chí để so khớp, thì hãy sử dụng hàm AVERAGEIF. Sử dụng hàm AVERAGEIFS để đối sánh nhiều tiêu chí. Hãy cùng tìm hiểu cú pháp hàm AVERAGEIF và một số ví dụ minh họa cách sử dụng hàm.

Hàm AVERAGEIF trong Excel

Hàm AVERAGEIF tính giá trị trung bình của mảng đáp ứng câu lệnh SINGLE được cung cấp.

AVERAGEIF Cú pháp hàm:

=AVERAGEIF (phạm vi, tiêu chí, [phạm vi trung bình])

phạm vi : nơi tiêu chí được áp dụng.

tiêu chí: điều kiện để được áp dụng

[phạm vi trung bình] : phạm vi [tùy chọn] trong đó mức trung bình cần được tính, nếu nó khác với phạm vi đầu tiên được cung cấp làm đối số

Ví dụ :

Tất cả những điều này có thể gây nhầm lẫn để hiểu. Hãy hiểu cách sử dụng chức năng bằng một ví dụ. Ở đây chúng tôi có một danh sách các loại trái cây với màu sắc và trọng lượng của chúng.

Đầu tiên chúng ta sẽ tìm hiểu trung bình về trọng lượng chung của những quả có điều kiện <50 Kg.

Sử dụng công thức

=AVERAGEIF(C2:C10,”<50″)

C2:C10 : phạm vi thỏa mãn điều kiện (cell_value<50).

<50: điều kiện hoặc tiêu chí

C2:C50 : phạm vi trung bình vì không có phạm vi nào khác được cung cấp.

Chúng tôi có Trung bình của các ô nhỏ hơn 50.

Bây giờ chúng ta sẽ tính Trung bình của trọng lượng có quả dưa.

Sử dụng công thức

=AVERAGEIF(A2:A10,”dưa”,C2:C10)

Chúng tôi có Trung bình của các ô trong hộp màu.

Bây giờ chúng ta sẽ tính Trung bình của trọng lượng không có quả Chuối

Sử dụng công thức

=AVERAGEIF(A2:A10,”<>Chuối”,C2:C10)

Chúng tôi có Trung bình của các ô trong hộp màu Đỏ.

Bây giờ chúng ta sẽ tính Trung bình của trọng số có nhiều điều kiện. Chúng ta cần sử dụng hàm AVERAGEIFS .

Điều kiện

1. quả táo
2. Màu đỏ

Sử dụng công thức

=AVERAGEIFS(C2:C10, A2:A10,”Táo”, B2:B10,”Đỏ”)

Như bạn có thể thấy Trung bình của bạn ở đây.

Bỏ qua Zero trong danh sách bằng hàm AVERAGEIF

Ở đây, chúng tôi được cung cấp một danh sách các số và chúng tôi cần tìm giá trị trung bình của các số trong đó các số 0 bị bỏ qua và các số còn lại được xem xét.

Trước hết chúng ta cần Trung bình cộng của các số bao gồm cả số không.

Công thức này sẽ chấp nhận các giá trị trừ 0.

Sử dụng công thức:

=AVERAGEIF(A2:A21, “<>0”)

A2:A21 : phạm vi

“<>0” : điều kiện bỏ qua giá trị 0.

Như bạn có thể thấy sự khác biệt trong các giá trị. Trung bình cộng của các số bỏ qua số 0 là 43,4.

Đối với các ô trống

Một điều nữa cần hiểu là hàm AVERAGE, AVERAGEIF & AVERAGEIFS bỏ qua các ô trống (và các ô chứa giá trị văn bản). Vì vậy, không cần phải lọc kết quả.

AVERAGEIF với tiêu chí tháng

Ở đây chúng ta có dữ liệu từ A1:D51. chúng ta cần tìm TRUNG BÌNH của Số lượng trong tháng Giêng.

Đầu tiên chúng ta sẽ tìm một ngày có ngày đầu tiên của tháng là ô A2. Phạm vi được đặt tên là rng ( D2 : D51 ) và order_date ( A2 : A51 ).

Sử dụng Công thức:

= AVERAGEIFS ( rng , order_date , “>=” & A2 , order_date , “<=” & EOMONTH ( A2 , 0 ) )

Giải trình:

• EOMONTH (A2, 0) trả về ngày 31 tháng 1 là ngày cuối cùng của tháng 1.
• “>=” & A2 trả về tiêu chí có giá trị lớn hơn ngày trong A2.
• “<=” & EOMONTH ( A2 , 0 ) ) trả về tiêu chí có giá trị nhỏ hơn ngày cuối tháng.
• AVERAGEIFS lấy tất cả các đối số trên và trả về giá trị trung bình của số được đưa ra bên dưới.

{ 33 ; 87 ; 58 ; 82 ; 38 ; 54 ; 149 ; 51 ; 100 ; 28 ; 36 }

Ở đây, A2 được cung cấp dưới dạng tham chiếu ô & Phạm vi được đặt tên là rng ( D2 : D51 ) và order_date ( A2 : A51 ).

Như bạn có thể thấy trong ảnh chụp nhanh ở trên, TRUNG BÌNH của số lượng trong tháng 1 là 65,091 .

Bây giờ chúng ta sẽ lấy TRUNG BÌNH của số lượng trong tháng 2 bằng cách thay đổi đối số Ngày đầu tiên của hàm

Sử dụng Công thức:

= AVERAGEIFS ( rng , order_date , “>=” & A13 , order_date , “<=” & EOMONTH ( A13 , 0 ) )

Như bạn có thể thấy trong ảnh chụp nhanh ở trên, TRUNG BÌNH của số lượng trong tháng 1 là 43,778 .

AVERAGE và AVERAGEIF Ví dụ:

Ở đây Hãy hiểu chức năng này bằng cách sử dụng nó trong một ví dụ.

Trước hết chúng ta cần Trung bình cộng của các số bao gồm cả số không.

Công thức này sẽ chấp nhận các giá trị trừ 0.

Sử dụng công thức:

=AVERAGEIF(A2:A21, “<>0”)

A2:A21 : phạm vi

“<>0” : điều kiện bỏ qua giá trị 0.

Như bạn có thể thấy sự khác biệt trong các giá trị. Trung bình cộng của các số bỏ qua số 0 là 43,4.

Nếu bạn không có bất kỳ giá trị 0 nào trong mảng. Bạn có thể sử dụng giá trị trả về là 0 trong số tất cả các giá trị lỗi và sử dụng công thức cách lấy giá trị trung bình của các số bỏ qua các số 0 tại đây. Nhưng công thức này không hoạt động với các giá trị lỗi.

Dưới đây là tất cả các ghi chú quan sát sử dụng hàm AVERAGEIF trong Excel
Notes :

1. Công thức chỉ hoạt động với các số
2. Hàm trả về lỗi #VALUE! lỗi nếu không có giá trị nào phù hợp với tiêu chí đã cho.
3. Không cung cấp ngày trực tiếp cho chức năng. Sử dụng hàm DATE hoặc sử dụng tham chiếu ô cho đối số ngày trong excel vì Excel chỉ đọc ngày theo đúng thứ tự.
4. Các tiêu chí khác nhau bên trong công thức được chấp nhận, chẳng hạn như bằng (=), nhỏ hơn (<), nhỏ hơn hoặc bằng (<=), lớn hơn hoặc bằng (>=), lớn hơn (>) & không bằng (<>).