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

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.

Bài tập hướng dẫn cách dùng hàm tính trung bình cộng có điều kiện trong excel
Bài tập hướng dẫn cách dùng hàm tính trung bình cộng có điều kiện trong excel

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)

hướng dẫn cách tính trung bình cộng có điều kiện trong excel

cách sử dụng hàm tính trung bình trong excel đơn giản

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)

bài tập dùng hàm averageif

Hướng dẫn cách dùng hàm tính trung bình cộng có điều kiện cho trước
Hướng dẫn cách dùng hàm tính trung bình cộng có điều kiện cho trước.

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.

bài tập cách sử dụng hàm averageif trong excel đơn giản

cách sử dụng hàm tính trung bình cộng trong excel

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

cách sử dụng hàm average để tính trung bình cộng

ví dụ 4 cách sử dụng hàm tính trung bình cộng

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.

Lỗi #DIV/0 trong excel
Lỗi #DIV/0 trong excel.
một số chia cho 0 là phép toán chia sai nên ra lỗi #DIV/0
Một số chia cho 0 là phép toán chia sai nên 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.

Kết quả tính trung bình bằng #div/0 do không có học sinh nào dưới 2
Kết quả tính trung bình bằng #div/0 do không có học sinh nào 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!

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *