Buzz
Tận dụng ngay cách sử dụng hàm AVERAGEIF trong Excel để tính trung bình cộng với nhiều điều kiện, giúp tiết kiệm thời gian xử lý dữ liệu văn phòng. Mặc dù có nhiều tính năng, nhưng không ít người vẫn chưa nắm rõ về hàm tính này khi sử dụng Excel. Trong bài viết này, Mytour tổng hợp 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.
Hàm AVERAGEIF là gì? Có ứng dụng như thế nào?
Hàm AVERAGEIFS trong Excel thuộc nhóm Hàm Thống kê và được sử dụng như một hàm Bảng tính (WS). Cụ thể, AVERAGEIF là một hàm tích hợp trong ứng dụng tính toán của bảng tính Microsoft Excel. Kết quả trả về của AVERAGEIF là giá trị trung bình của các ô thỏa mãn điều kiện chỉ định.
Trong Excel, cách sử dụng hàm AVERAGEIF giúp người dùng nhanh chóng tính trung bình cộng của các giá trị với nhiều điều kiện khác nhau. Đơn giản hóa công việc, hàm AVERAGEIF thay thế cho một số công thức 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 một cách 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ó điều kiện này. Cú pháp công thức chính xác của hàm AVERAGEIF với nhiều điều kiện như sau:
=AVERAGEIF(dải, điều kiện, [dải_trung_bình])
Trong đó:
- Dải (bắt buộc): Phạm vi bao gồm các ô cần được tính giá trị trung bình dưới dạng số hoặc chữ.
- Điều kiện (Bắt buộc): Điều kiện sử dụng để lấy các số để tính giá trị trung bình. Có thể là các dạng như 45, '45', '>45', 'lê' hoặc B2.
- Dải_trung_bình (Bắt buộc): Khoảng dữ liệu chọn để sử dụng hàm AVERAGEIF. Nếu không có khoảng dữ liệu, 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 ý: Theo hệ điều hành và dòng máy tính bạn đang sử dụng, dấu “,” trong hàm AVERAGEIF có thể thay đổi thành dấu “;” khi bạn nhập công thức.
Hướng dẫn cách sử dụng hàm AVERAGEIF trong Excel
Sử dụng hàm tính trung bình cộng với nhiều điều kiện AVERAGEIF trong Excel sẽ trở nên dễ dàng hơn khi bạn tham khảo các ví dụ chi tiết. Để giúp bạn thành thạo hơn về hàm này, Mytour sẽ hướng dẫn chi tiết cách sử dụng theo nhiều dạng khác nhau. Hãy 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 đó, nhấn Enter để xem kết quả.
Định nghĩa của công thức hàm:
- C2:C6: Phạm vi chứa dữ liệu cần tính trung bình.
- '>6000000': Điều kiện được đặt ra trong bài toán.
Tính trung bình theo điều kiện nhỏ hơn
Hướng dẫn sử dụng hàm AVERAGEIF trong Excel để tính trung bình cộng với điều kiện nhỏ hơn như sau:
Ví dụ: Yêu cầu tính số tiền lương trung bình của những nhân viên có mức lương ít hơn 6000000.
Hướng dẫn nhanh: Điền công thức sau đây: =AVERAGEIF(C2:C6,'<6000000”) tại ô hiển thị số lương trung bình. Nhấn Enter để xem kết quả phép tính.
Trong đó:
- C2:C6: Khu vực dữ liệu chứa điều kiện tính lương trung bình.
- “<6000000”: Yếu tố thỏa điều kiện của đề bài.
Để đảm bảo an toàn thông tin dữ liệu khi sử dụng hàm AVERAGEIF trong Excel, hãy sử dụng phiên bản nâng cao của Microsoft. Tham khảo ngay một số phần mềm Microsoft được nhiều người tin dùng.
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ủa nhân viên không chứa chữ Nguyễn trong tên.
Hướng dẫn nhanh: Nhập công thức =AVERAGEIF(C2:C6,'<>Nguyễn') tại ô tính kết quả lương trung bình của nhân viên không có chữ Nguyễn. Nhấn Enter để xem kết quả tính.
Giải thích công thức:
- C2:C6: Khu vực dữ liệu chứa các ô cần tính lương trung bình.
- '<> Nguyễn': Điều kiện thỏa 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 nhân viên có giới tính Nam.
Hướng dẫn nhanh: Với đề bài này, sử dụng hàm AVERAGEIF để tính trung bình cộng theo điều kiện nhập công thức: =AVERAGEIF(C2:C6,'Nam',D2:D6). Sau đó, nhấn Enter để xem kết quả.
Giải thích công thức hàm:
- C2:C6: Khu vực chứa 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 của đề bài.
- D2:D6: Phạm vi dữ liệu số để thực hiện phép tính lương trung bình.
Tính trung bình với 2 điều kiện số
Ngoài 1 điều kiện, bạn cũng có thể sử dụng hàm AVERAGEIF để tính trung bình cộng với nhiều điều kiện như ví dụ sau:
Ví dụ: Cho bảng điểm HK1 và HK2 của học sinh trong lớp. Hãy tính điểm trung bình HK1 của những học sinh có điểm lớn hơn 5 và nhỏ hơn 8 trong HK1.
Hướng dẫn nhanh: Trong bài toán này, nhập công thức là: =AVERAGEIFS(D2:D6,D2:D6,'>5',D2:D7,'<8'). Nhập xong phép tính, nhấn Enter để hoàn tất.
Giải thích công thức hàm:
- D2:D6: Khu vực 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 với 2 điều kiện bao gồm số và chữ
Ví dụ: Cho bảng điểm học sinh lớp 6A. Thực hiện cách sử dụng hàm AVERAGEIF với nhiều điều kiện để tính điểm trung bình HK2 từ những học sinh nam có điểm số lớn hơn 6 trong học kỳ này.
Hướng dẫn nhanh: Công thức cho bài toán này là: =AVERAGEIFS(E2:E6,C2:C6,'Nam',E2:E6,'>6'). Hiển thị kết quả bằng cách nhấn Enter sau khi nhập công thức.
Giải thích công thức hàm:
- E2:E6: Vùng chứa dữ liệu số cần tính điểm trung bình.
- C2:C6,'Nam': Lọc ra học sinh nam (điều kiện chữ).
- E2:E6,'>6': Thỏa điều kiện học sinh nam có đ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
Khi sử dụng hàm AVERAGEIF trong Excel để tính trung bình cộng có 1 hoặc nhiều điều kiện, có thể gặp một số lỗi cơ bản như sau khi nhập công thức tính:
Lỗi báo #DIV/0
Lỗi này thường xuất hiện trong các bảng tính chứa nhiều dữ liệu. Nguyên nhân là do các ô chọn tính trung bình thường không phải là số, hoặc không có giá trị nào trong vùng dữ liệu thỏa mãn tiêu chí.
Ví dụ: Yêu cầu tính điểm trung bình HK2 của nam sinh có điểm trên 9. Tuy nhiên, bảng tính không có điểm thỏa mãn yêu cầu. Dù công thức AVERAGEIF đúng, nhưng kết quả vẫn báo lỗi #DIV/0. Để khắc phục, bạn cần điều chỉnh tiêu chí giá trị.
Không chấp nhận ô trống dữ liệu
Khi sử dụng hàm AVERAGEIF, thường gặp lỗi sau khi nhấn Enter. Lỗi này xuất hiện khi vùng dữ liệu tính trung bình chứa ô trống. Để sửa, bạn có thể mặc định giá trị “0” cho các ô trống. Hoặc điều chỉnh hàm theo yêu cầu cụ thể cho từng trường hợp.
Lưu ý khi sử dụng hàm AVERAGEIF trong Excel
Ngoài những vấn đề thường gặp đã được đề cập, hãy lưu ý một số điều khi sử dụng hàm AVERAGEIF với nhiều điều kiện:
- Các ô trong phạm vi chọn sẽ bị bỏ qua nếu chứa TRUE hoặc FALSE.
- Nếu giá trị trong phạm vi là trống hoặc văn bản, hàm AVERAGEIF sẽ báo lỗi #DIV0!
- Hàm AVERAGEIF sẽ bỏ qua các ô trống trong phạm vi trung bình.
- Với hàm AVERAGEIF, các ô trống trong phạm vi điều kiện (Criteria) sẽ được giả định là giá trị 0.
- Nếu không có ô nào đáp ứng tiêu chí, AVERAGEIF sẽ báo lỗi #DIV/0!
- Bạn có thể sử dụng ký tự đại diện như dấu sao hoặc dấu chấm hỏi cho tiêu chí.
- Kích thước và hình dạng “Average_range” không cần phải giống với phạm vi. Các ô thực tế sẽ sử dụng ô trái trên cùng làm ô đầu tiên và gộp những ô tương đồng về kích thước và hình dạng với phạm vi.
Tổng hợp thông tin quan trọng về cách sử dụng hàm AVERAGEIF để tính trung bình cộng với 1 hoặc nhiều điều kiện trong Excel. Hi vọng bài viết giúp bạn xử lý các phép tính trung bình nhanh chóng và chính xác. Đừng quên theo dõi Mytour để cập nhật thêm thủ thuật sử dụng Microsoft Office.
- Xem thêm bài viết: Tin học văn phòng
