Cách sử dụng hàm AVERAGEIF trong Excel và ví dụ tham khảo

Tận dụng cách sử dụng hàm AVERAGEIF trong Excel để tính trung bình cộng nhiều điều kiện sẽ giúp bạn tiết kiệm thời gian khi xử lý một số dữ liệu văn phòng. Dù sở hữu nhiều tiện ích nhưng không ít người vẫn chưa hiểu rõ về hàm tính này khi dùng Excel. Trong bài viết này, Sforum sẽ tổng hợp các thông tin hữu ích liên quan đến cách sử dụng hàm trung bình cộng có điều kiện AVERAGEIFS trong Excel cho bạn tham khảo.

Hàm AVERAGEIF là hàm gì? Có ứng dụng gì?

Hàm AVERAGEIFS trong Excel được phân nhóm vào loại Hàm thống kê và sử dụng như một hàm bảng tính (WS). Cụ thể hơn, AVERAGEIF là một hàm tích hợp trong các ứng dụng tính toán của bảng tính Microsoft Excel. Giá trị mà AVERAGEIF trả về sẽ là trung bình của các ô thỏa mãn điều kiện được chỉ định.

Trong Excel, cách sử dụng hàm AVERAGEIF sẽ giúp người sử dụng tính nhanh trung bình cộng của những giá trị có đính kèm nhiều điều kiện khác nhau. Nói một cách dễ hiểu, hàm AVERAGEIF sử dụng để thay cho một vài công thức hàm kết hợp như:

• Công thức hàm AVERAGE kết hợp với hàm IF.
• Công thức hàm AVERAGE kết hợp với hàm VLOOKUP.

Công thức của hàm AVERAGEIF

Để áp dụng cách sử dụng hàm AVERAGEIF trong Excel hiệu quả nhất, bạn cần hiểu về công thức của hàm tính trung bình cộng có điều kiện này. Cú pháp công thức chính xác của hàm AVERAGEIF nhiều điều kiện như sau:

=AVERAGEIF(range, criteria, [average_range])

Trong đó:

• Range (yếu tố bắt buộc): Phạm vi bao gồm các ô cần được tính giá trị trung bình ở dạng số hoặc chữ.
• Criteria (Yếu tố bắt buộc): Điều kiện dùng để lấy các số cho việc tính trung bình. Các dạng biểu thị ví dụ như 45, '45', '45', 'lê' hoặc B2.
• Average_range (Yếu tố tùy chọn): Khoanh vùng dữ liệu chọn để sử dụng hàm AVERAGEIF. Nếu không khoanh vùng thì hàm tính sẽ mặc định chọn tất cả dữ liệu trong phạm vi cho phép.

Lưu ý:Tùy theo hệ điều hành và dòng máy tính mà bạn sử dụng, dấu “,” trong hàm AVERAGEIF có thể sẽ đổi thành dấu “;” khi nhập công thức.

Hướng dẫn cách sử dụng hàm AVERAGEIF trong Excel

Cách sử dụng hàm tính trung bình cộng có nhiều điều kiện AVERAGEIF trong Excel sẽ không còn khó khi bạn tham khảo các ví dụ chi tiết về nó. Để giúp bạn sử dụng thành thạo hàm tính này, Sforum sẽ hướng dẫn chi tiết cách sử dụng theo nhiều dạng khác nhau. Cùng xem qua những ví dụ cụ thể khi nhập công thức hàm AVERAGEIFS trong Excel ngay dưới đây.

Tính trung bình với điều kiện lớn hơn

Ví dụ: Tính Lương trung bình của những người có mức Lương trên 6000000.

Hướng dẫn nhanh: Tại ô cần tính trung bình, nhập =AVERAGEIF(C2:C6,'6000000'). Sau đó, ấn Enter để xuất kết quả.

Giải thích công thức hàm:

• C2:C6: Vùng chứa những dữ liệu cần tính trung bình.
• '6000000': Điều kiện đưa ra của đề bài.

Tính trung bình theo điều kiện bé hơn

Cách sử dụng hàm AVERAGEIF trong Excel để tính trung bình cộng có điều kiện bé hơn như sau:

Ví dụ: Yêu cầu tính số tiền lương trung bình của các nhân viên có mức lương thấp hơn 6000000.

Hướng dẫn nhanh: Bạn điền công thức sau: =AVERAGEIF(C2:C6,'<6000000”) tại ô cần hiển thị số lương trung bình. Bấm Enter để xuất kết quả phép tính.

Trong đó:

• C2:C6: Vùng dữ liệu chứa điều kiện cần tính lương trung bình.
• “<6000000”: Yếu tố thỏa điều kiện đề bài.

Để bảo mật thông tin dữ liệu được tốt nhất khi áp dụng cách sử dụng hàm AVERAGEIF trong Excel, bạn nên sử dụng phần mềm Microsoft phiên bản nâng cao. Tham khảo ngay một số phần mềm Microsoft đang được nhiều người tin tưởng sử dụng.

[Product_Listing categoryid='1149' propertyid=' customlink='https://cellphones.com.vn/phu-kien/may-tinh-laptop/phan-mem/microsoft.html' title='Danh sách phần mềm Microsoft giá tốt tại CellphoneS']

Tính trung bình theo điều kiện khác 1 số

Ví dụ: Yêu cầu tính lương trung bình các nhân viên không chứa chữ Nguyễn trong tên.

Hướng dẫn nhanh: Bạn điền công thức =AVERAGEIF(C2:C6,' và ô cần tính kết quả lương trung bình các nhân viên tên không có chữ Nguyễn. Sau đó, ấn Enter để xuất kết quả tính.

Giải thích công thức:

• C2:C6: Khoanh vùng dữ liệu có chứa các ô cần tính lương trung bình.
• '< Nguyễn': Điều kiện thỏa theo yêu cầu của bài.

Tính trung bình theo điều kiện là chữ

Ví dụ: Yêu cầu tính tiền lương trung bình của các nhân viên có giới tính Nam.

Hướng dẫn nhanh: Với đề bài này, cách sử dụng hàm AVERAGEIF để tính trung bình cộng có điều kiện sẽ nhập theo công thức sau: =AVERAGEIF(C2:C6,'Nam',D2:D6). Sau đó, bạn nhấn Enter để nhận kết quả.

Giải thích công thức hàm:

• C2:C6: Khoanh vùng chứa các dữ liệu cần cho điều kiện của đề bài.
• 'Nam': Yếu tố thỏa theo điều kiện mà đề bài giao.
• D2:D6: Vùng có chứa dữ liệu số để thực hiện phép tính lương trung bình.

Tính trung bình có 2 điều kiện đều là số

Ngoài 1 điều kiện, bạn cũng có thể áp dụng cách sử dụng hàm AVERAGEIF để tính trung bình cộng có nhiều điều kiện theo ví dụ như sau:

Ví dụ: Cho bảng điểm HK1 và HK2 của các học sinh trong lớp. Bạn hãy tính điểm trung bình HK1 của những học sinh có số điểm lớn hơn 5 và nhỏ hơn 8 trong HK1.

Hướng dẫn nhanh: Ở đề bài này, bạn nhập công thức là: =AVERAGEIFS(D2:D6,D2:D6,'5',D2:D7,'<8'). Nhập xong phép tính, bạn ấn Enter để hoàn tất.

Giải thích công thức hàm:

• D2:D6: Vùng chứa dữ liệu cần để tính điểm trung bình.
• D2:D6,'5': Thỏa điều kiện thứ nhất với yêu cầu học sinh có điểm HK1 lớn hơn 5.
• D2:D6,'<8': Thỏa điều kiện thứ hai với yêu cầu học sinh có điểm HK1 nhỏ hơn 8.

Tính trung bình có 2 điều kiện gồm cả số và chữ

Ví dụ: Cho bảng điểm học sinh lớp 6A. Yêu cầu hãy thực hiện cách sử dụng hàm AVERAGEIF nhiều điều kiện để xuất ra kết quả điểm trung bình HK2 từ những học sinh nam có điểm số lớn hơn 6 của học kỳ này.

Hướng dẫn nhanh: Công thức cho bài này như sau: =AVERAGEIFS(E2:E6,C2:C6,'Nam',E2:E6,'6'). Xuất kết quả tính bằng cách ấn phím Enter sau khi nhập xong công thức trên.

Giải thích công thức hàm:

• E2:E6: Vùng chứa dữ liệu số cần cho việc tính điểm trung bình.
• C2:C6,'Nam': Thỏa điều kiện thứ nhất là lọc ra đối tượng học sinh có giới tính nam (điều kiện chữ).
• E2:E6,'6': Thỏa điều kiện thứ hai với yêu cầu học sinh nam có số điểm HK2 lớn hơn 6 (điều kiện số).

Một số lỗi thường gặp khi sử dụng hàm AVERAGEIF

Với cách sử dụng hàm AVERAGEIF trong Excel để tính trung bình cộng có 1 hay nhiều điều kiện, không ít người sẽ gặp một số lỗi cơ bản dưới đây khi nhập công thức tính:

Lỗi báo #DIV/0

Lỗi này thường xảy ra ở các bảng tính có nhiều dữ liệu. Nguyên nhân xuất phát từ các ô chọn để tính trung bình đều có giá trị không phải dạng số, hoặc trong vùng chọn dữ liệu cung cấp không có giá trị nào thỏa mãn tiêu chí cung cấp.

Ví dụ: Yêu cầu tính điểm trung bình HK2 các bạn nam trong lớp có điểm HK2 trên 9. Tuy nhiên, bảng tính lại không có số điểm phù hợp yêu cầu. Lúc này, dù công thức hàm AVERAGEIF của bạn đúng thì kết quả vẫn báo lỗi #DIV/0. Bạn cần thay đổi điều kiện giá trị thì mới khắc phục được lỗi trên.

Không chấp nhận ô trống dữ liệu

Cách sử dụng hàm AVERAGEIF thường báo lỗi như hình bên dưới sau khi nhấn lệnh Enter. Lỗi này xảy ra khi bạn dùng hàm trung bình cộng có điều kiện AVERAGEIF để tính giá trị trung bình. Nhưng trong bảng tính vẫn còn chứa ô trống. Để khắc phục lỗi trên, bạn hãy chọn mặc định cho các ô trống đó với giá trị “0”. Cách khác, biện luận hàm dựa trên yêu cầu ứng với từng trường hợp.

Lưu ý khi sử dụng hàm AVERAGEIF trong Excel

Ngoài các lỗi thường gặp được nêu ở nội dung trên, bạn cũng nên chú ý thêm một số điều dưới đây khi dùng hàm AVERAGEIF nhiều điều kiện:

• Những ô trong vùng dữ liệu chọn sẽ bị bỏ qua nếu chứa TRUE hoặc FALSE.
• Khi giá trị vùng dữ liệu trống hoặc mang định dạng văn bản thì hàm trung bình cộng có điều kiện AVERAGEIF sẽ báo lỗi #DIV0!
• Hàm AVERAGEIF sẽ bỏ qua các ô trống trong vùng dữ liệu trung bình.
• Với hàm AVERAGEIF, các ô trống trong vùng điều kiện (Criteria) sẽ mặc định là giá trị 0.
• Nếu các ô trong vùng dữ liệu đều không đáp ứng những tiêu chí cần, AVERAGEIF sẽ báo lỗi #DIV/0!
• Có thể dùng ký tự đại diện như dấu sao hoặc dấu chấm hỏi cho tiêu chí. Bạn có thể nhấn dấu ngã để tìm một dấu chấm hỏi hoặc dấu sao thật sự.
• Hình dáng và kích thước “Average_range” không cần phải đúng với phạm vi. Những ô thực tế dùng để tính giá trị trung bình luôn sử dụng ô trái trên cùng để xác định làm ô đầu tiên. Tiếp theo sẽ gộp những ô tương đồng về kích thước và hình dáng với phạm vi.

Trên đây là tổng hợp các thông tin hữu ích liên quan đến cách sử dụng hàm AVERAGEIF để tính trung bình cộng có 1 hoặc nhiều điều kiện trong Excel mà bạn cần biết. Hy vọng bài viết sẽ giúp bạn sẽ xử lý các phép tính trung bình nhanh chóng và chính xác hơn. Đừng quên theo dõi các bài viết mới của Sforum để cập nhật những thủ thuật hay khi sử dụng các phần mềm Microsoft Office.

• Xem thêm bài viết: Tin học văn phòng