Sử dụng hàm AVERAGEIF và SUMIF/COUNTIF để tính trung bình cộng trong excel

Bài viết này sẽ hướng dẫn các bạn cách sử dụng hàm AVERAGEIF và SUMIF/COUNTIF để tính trung bình cộng trong excel với nhiều điều kiện khác nhau.

đôi khi có những hàm rất dễ để thao tác để tính ra những con số vô cùng hay và tuyệt vời, tuy nhiên có nhiều bạn mới tiếp xúc với excel nên còn nhiều vấn đề còn bâng khuâng và lúng túng, không biết mình phải làm như thế nào, cũng vì thế mà bài viết này sẽ đi hướng dẫn một thủ thuật vô cùng cơ bản để các bạn có thể thao tác với hàm AVERAGEIF và SUMIF/COUNTIF, các bạn cùng theo dõi các bước làm dưới đây nhé.

Sơ lược hàm AVERAGEIF và SUMIF/COUNTIF trong excel

Đối với Hàm AVERAGEIF(range, criteria, [average_range]) giải thích các cú pháp như sau:

• Range    Bắt buộc. Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số.
• Criteria    Bắt buộc. Tiêu chí ở dạng số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô sẽ tính giá trị trung bình. Ví dụ, các tiêu chí có thể được biểu thị dưới dạng như 32, “32”, “>32”, “táo” hoặc B4.
• Average_range    Tùy chọn. Tập hợp các ô thực tế để tính giá trị trung bình. Nếu bỏ qua, phạm vi được dùng.

Đối với hàm SUMIF(range, criteria, [sum_range]) giải thích các cú pháp như sau:

• Range   Bắt buộc. Phạm vi ô bạn muốn được đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn (ví dụ bên dưới).
• criteria   Bắt buộc. Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Có thể bao gồm các ký tự đại diện – dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào, dấu sao (*) để khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.

cuối cùng là hàm hàm COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…) giả thích từng cú pháp như sau:

• criteria_range1    Bắt buộc. Phạm vi thứ nhất trong đó cần đánh giá các tiêu chí liên kết.
• criteria1    Bắt buộc. Tiêu chí dưới dạng một số, biểu thức, tham chiếu ô hoặc văn bản để xác định những ô nào cần đếm. Ví dụ: tiêu chí có thể được biểu thị là 32, “>32”, B4, “táo” hoặc “32”.
• criteria_range2, criteria2, …    Tùy chọn. Những phạm vi bổ sung và tiêu chí liên kết của chúng. Cho phép tối đa 127 cặp phạm vi/tiêu chí.

vậy là chúng ta đã đi sơ lược qua AVERAGEIF và SUMIF/COUNTIF trong excel để hiểu rõ hàm một xíu để bắt đầu áp dụng công thức vào trong bài tập nhé.

Áp dụng hàm hàm AVERAGEIF và SUMIF/COUNTIF trong excel

Bây giờ chúng ta đi vào nội dung chín nào, bài tập nó tương tự như hình này, mình sẽ đi mô phỏng lại.

Để tính trung bình cộng thường chúng ta sẽ dùng hàm AVERAGE nhưng trong trường hợp này thì hàm AVERAGE không làm được mà để tính trung bình cộng theo điều kiện ta sẽ dùng hàm AVERAGEIF và áp dụng như sau:

Ở ô F3 các bạn gõ công thức: =AVERAGEIF($B$2:$B$10;E3;$C$2:$C$10)

Trong đó:

– B2:B10: Là dãy ô chứa giá trị cần tính toán.

– E3: Là điều kiện cần tính toán và nó nằm trong B2:B10

– C2: C10: Giá trị cần tính trung bình.

Kết quả đạt được như sau.

Ngoài ra các bạn có thể sử dụng nhiều công thức excel khác nhau để thực hiện yêu cầu trên, và mình sẽ sử dụng hàm SUMIF và hàm COUNTIF để tính toán xem kết quả có giống với hàm AVERAGEIF không nhé.

Mình copy thêm một cột bên cạnh để sau khi tính toán xong chúng ta dễ so sánh

Ở ô G3 nhập vào công thức: 

=SUMIF($B$2:$B$10;E3;$C$2:$C$10)/COUNTIF($B$2:$B$10;E3)

Trong đó: 

– Hàm SUMIF để tính tổng theo điều kiện.

– Hàm COUNTIF để đếm giá trị.

Kết quả như chúng ta thấy giống với khi dùng hàm AVERAGEIF rồi

Kết Luận

vậy là chúng ta đã hoàn thành xong rồi, với công thức này các bạn có thể áp dụng tính trung bình cộng một cách rất tuyệt vời, bài viết tuy ít nhưng phần nào cũng giúp các bạn hiểu rõ hàm tính trung bình này hơn, để đảm bảo các bạn có thể giải những bài toán trung bình cộng sau này.

chúc các bạn thành công!