Học Excel

Hàm AVERAGEIF – Cách sử dụng hàm tính trung bình có điều kiện

June 20, 20211053 views0

Nếu như tính hàm AVERAGE có điều kiện thì các bạn nên làm như thế nào? Giống như hàm SUM có điều kiện thì dùng SUMIF thì lần này sẽ có hàm AVERAGEIF.

1/ Hàm AVERAGEIF là gì?

Hàm AVERAGEIF dùng để tính trung bình cộng các hệ số trong excel có điều kiện. Hàm sẽ tính trung bình các ô có giá trị được khoanh vùng có trong bảng, kèm theo đó là điều kiện cho trước mà người dùng thiết lập.

2/ Công thức hàm AVERAGEIF

Câu lệnh này hoạt động tương tự như hàm SUMIF:

=AVERAGEIF(range, criteria, [average_range])

Trong đó:

• Range là phạm vi cần tính giá trị trung bình có thể là số, tên, các tham chiếu, các mảng.
• Criteria là điều kiện dưới dạng số, biểu thức, tham chiếu ô hoặc văn bản để xác định tính giá trị trung bình. Điều kiện nằm trong dấu ” “. Ví dụ: Các tiêu chí có thể được biểu thị như 32, “32”, “>32”, “táo” hoặc B4
• Average_range (Tùy chọn): Vùng dữ liệu được chọn để dùng hàm AVERAGEIF (nếu bỏ qua thì mặc định chọn toàn bộ).

3/ Cách sử dụng hàm AVERAGEIF

Ở đây mình có một bảng dữ liệu về điểm trung bình và xếp loại của các học sinh trong lớp học sau khi tổng hợp cuối năm.

Ví dụ 1: Tính điểm trung bình của các học sinh giỏi trong lớp

=AVERAGEIF(D2:D7,”GIỎI”,C2:C7)

Trong đó:

• D2:D7 – đầu tiên hàm sẽ chạy các tham số được chọn từ ô D2 đến ô D7
• GIỎI – câu lệnh sẽ tìm điều kiện giỏi (xếp loại giỏi) trong các tham số từ ô D2 đến D7. Nếu như có ô trùng khớp với điều kiện sẽ lưu lại điểm trung bình tương ứng, và cứ thế chạy tiếp. Một điểm lưu ý là điều kiện không phân biệt chữ hoa và chữ thường.
• C2:C7 – sau khi đã rà soát các điều kiện đã khớp, việc cuối cùng là tính tổng các ô được chọn và chia trung bình.

Ví dụ 2: Tính điểm trung bình cộng của các học sinh không được xếp loại giỏi

=AVERAGEIF(D2:D7,”<>GIỎI”,C2:C7)

Trong đó:

• D2:D7 – đầu tiên hàm sẽ chạy các tham số được chọn từ ô D2 đến ô D7
• <>GIỎI – cách dùng toán tử để lấy các tham số khác với điều kiện giỏi. Có nghĩa là nếu học sinh nào được xếp loại giỏi thì điểm sẽ không được lấy để trính trung bình.
• C2:C7 – sau khi đã rà soát các điều kiện đã khớp, việc cuối cùng là tính tổng các ô được chọn và chia trung bình.

Ví dụ 3: Tính điểm trung bình của các học sinh có điểm lớn hơn hoặc bằng 5

=AVERAGEIF(C2:C7,”>=5″)

Trong đó:

• C2:C7 – là dãy số các điểm trung bình của học sinh
• >= 5 – là điều kiện điểm trên 5. Điều kiện này sẽ xét trong C2:C7, nếu như có điểm nào đúng với điều kiện thì sẽ tính.

Ví dụ 4: Tính điểm trung bình cộng của các học sinh được xếp loại bắt đầu bằng chữ G

=AVERAGEIF(D2:D7,”=g*”,C2:C7)

Trong đó:

• D2:D7 – hàm chạy các tham số
• =g* – chọn các điều kiện bắt đầu bằng chữ g
• C2:C7 – sau khi lấy được điều kiện phù hợp sẽ tính trung bình các tham số được chọn

4/ Một số lưu ý

• Các ô trong phạm vi có chứa TRUE hoặc FALSE sẽ bị bỏ qua.
• Nếu ô trong phạm vi trung bình là một ô trống, hàm AVERAGEIF sẽ bỏ qua nó.
• Giá trị trống hoặc dạng văn bản, AVERAGEIF sẽ trả về giá trị lỗi #DIV0!
• Nếu một ô trong tiêu chí bị bỏ trống, hàm này sẽ xem ô đó như giá trị 0.
• Nếu không có ô nào trong phạm vi đáp ứng các tiêu chí, AVERAGEIF sẽ trả về giá trị lỗi #DIV/0! .
• Bạn có thể dùng các ký tự đại diện, dấu chấm hỏi (?) và dấu sao (*) trong tiêu chí. Dấu chấm hỏi sẽ khớp với bất kỳ ký tự đơn nào; dấu sao sẽ 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ự.
• Average_range không nhất thiết phải có cùng kích cỡ và hình dạng như phạm vi. Các ô thực tế được tính giá trị trung bình sẽ được xác định bằng cách dùng ô trái trên cùng trong average_range làm ô đầu tiên, sau đó gộp các ô tương ứng về kích cỡ và hình dạng với phạm vi.

5/ Một số lỗi khi dùng hàm AVERAGEIF

Lỗi #DIV/0

Lỗi #div/0 trong hàm AVERAGEIF là một lỗi phổ biến. Đây là một lỗi sai khi dùng phép tính chia cho 0. Theo nguyên tắc toán học thì không tính được cho 1 phép chia mà mẫu số bằng 0, do đó đây là 1 lỗi. Ví dụ: A1=8, B1=0 => Phép chia A1 cho B1 sẽ ra lỗi #DIV/0.

Ví dụ 5: Tính trung bình cộng học sinh có điểm trung bình dưới 2. Kết quả là ra #DIV/0! do không có học sinh nào có điểm trung bình dưới 2.

Công thức sửa lỗi #DIV/0: Bạn nên thay đổi lại điều kiện

Lỗi không nhận ô trống

Nếu như bạn sử dụng hàm AVERAGEIF lên 1 ô trống không có dữ liệu thì hàm này sẽ mặc định xem dữ liệu trong ô đó là 0 và không sử dụng để tính.

Kienit.com đã hướng dẫn chi tiết về cách sử dụng hàm AVERAGEIF trong excel, hàm tính trung bình có điều kiện sẽ giúp bạn xử lý một cách nhanh chóng hơn nhiều.

Nếu như bạn gặp phải lỗi #div/0 trong thì hãy kiểm tra lại điều kiện coi chính xác không hoặc là kiểm tra lại dữ liệu đã nhập chính xác chưa.

Nếu như xem đến đây bạn có thắc mắc về hàm AVERAGE thì hãy xem lại bài trước trong chuyên mục excel. Hy vọng bài viết hôm nay sẽ giúp ích cho mọi người. Cảm ơn các bạn đã xem qua!