Hàm AVERAGE trung bình là một hàm được sử dụng khá phổ biến trong Excel, tuy nhiên vẫn có những hàm khác để tính trung bình rất hiệu quả mà còn có thể lọc theo điều kiện để tính, đó là hàm AVERAGEIFS. Vì vậy, hãy cùng theo dõi bài viết sau để biết cách sử dụng hàm AVERAGEIFS trong Excel nhé!
Bài viết được thực hiện trên laptop hệ điều hành Windows phiên bản Excel 2016, ngoài ra bạn cũng có thể thực hiện trên các phiên bản Excel 2007, 2010, 2013, 2019 và trên MacOS với thao tác tương tự.
I. Hàm AVERAGEIFS là gì? Ứng dụng của hàm AVERAGEIFS trong Excel
Hàm AVERAGEIFS hay hàm tính giá trị trung bình của nhiều điều kiện là hàm tính giá trị trung bình của tất cả các ô dựa trên các điều kiện có sẵn do người dùng nhập vào với cú pháp = AVERAGEIFS (phạm vi, tiêu chí, [average_range]).
Trong đó:
Phạm vi (Bắt buộc): Một hoặc nhiều ô tính trung bình, bao gồm số hoặc tên, mảng hoặc tham chiếu có chứa số.
Criteria_range1 (bắt buộc), các đối số criteria_range tiếp theo (tùy chọn): Phạm vi từ 1 đến 127 để đánh giá các tiêu chí liên quan.
Criteria1 (bắt buộc), các đối số tiêu chí tiếp theo (tùy chọn): 1 đến 127 tiêu chí ở dạng số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô là trung bình.
Bạn cũng có thể hiểu chức năng này là do 2 chức năng AVERAGE và IFS kết hợp với nhau.
Ghi chú:
Nếu average_range là giá trị trống hoặc giá trị văn bản, AVERAGEIFSS sẽ trả về giá trị # DIV0!
Nếu một ô trong phạm vi tiêu chí bị bỏ trống, AVERAGEIFS sẽ coi ô đó là 0.
Các ô trong phạm vi chứa TRUE có giá trị là 1; các ô trong phạm vi chứa FALSE có giá trị là 0.
Chỉ các số trung bình trong phạm vi dải_bình_đình có các giá trị thoả mãn điều kiện đã cho.
Nếu không thể chuyển đổi các ô trong average_range thành số, thì AVERAGEIFS sẽ trả về giá trị lỗi # DIV0!
Nếu không có ô nào đáp ứng tất cả các tiêu chí, AVERAGEIFSS sẽ trả về giá trị # DIV / 0!
Các ký tự thay thế như *.? có thể được sử dụng. trong điều kiện trung bình.
Ngoài ra, bạn có thể tham khảo thêm 3 hàm AVERAGEIF, AVERAGE và IFS tại link bên dưới để hiểu thêm về cách thức hoạt động của 2 hàm này nhé!
Cách sử dụng hàm AVERAGE trong Excel
Cách sử dụng hàm IFS trong Excel
Cách sử dụng hàm AVERAGEIF trong Excel
II. Cách sử dụng hàm AVERAGEIFS để tính trung bình nhiều điều kiện trong Excel
Ví dụ 1: Tính số trung bình cộng với 2 điều kiện là số
Câu hỏi: Tính điểm trung bình HK1 của những học sinh có điểm HK1 lớn hơn 6 và nhỏ hơn 9.
Bước 1: Nhập công thức = AVERAGEIFS (D2: D7; D2: D7; “> 6”; D2: D7; “vào ô trung bình.
Lưu ý: Hãy nhớ sử dụng, hoặc; để phù hợp với công thức! Để biết nên sử dụng dấu trọng âm nào, bạn có thể nhìn vào dòng công thức bên dưới ô bạn đang nhập!
Giải thích chức năng:
Đ2: D7: Là diện tích được tính trung bình (Điểm HK1).
D2: D7; “> 6”: Là điều kiện đầu tiên (điểm HK1 lớn hơn 6).
D2: D7; ”là điều kiện thứ 2 (điểm HK1 dưới 9).
Nhập công thức như trong hình
Bước 2: Nhấn Enter để hiển thị kết quả đã tính.
Giải thích kết quả:
Ví dụ ở đây lấy số điểm> 6 nghĩa là lấy 7 điểm và 8 điểm.
Ở HK1 có 3 bạn thoả mãn điều kiện là Nguyễn Văn A, Lê Thị B và Mai Thị H nên sẽ lấy điểm của 3 bạn để tính trung bình cộng.
Nhấn Enter để hiển thị kết quả được tính toán
Ví dụ 2: Tính số trung bình cộng với 2 điều kiện là số và chữ
Câu hỏi: Tính điểm trung bình HK2 của nam sinh có điểm HK2 lớn hơn 7.
Bước 1: Nhập công thức = AVERAGEIFS (E2: E7; C2: C7; “Nam”; E2: E7; “> 7”) vào ô trung bình.
Lưu ý: Hãy nhớ sử dụng, hoặc; để phù hợp với công thức! Để biết nên sử dụng dấu trọng âm nào, bạn có thể nhìn vào dòng công thức bên dưới ô bạn đang nhập!
Giải thích chức năng:
E2: E7: Là diện tích được tính trung bình (Điểm HK2).
C2: C7; “Nam”: Là điều kiện đầu tiên (Sinh viên Nam).
E2: E7; “> 7”: Là điều kiện thứ 2 (điểm HK2 lớn hơn 7).
Nhập công thức như trong hình
Bước 2: Nhấn Enter để hiển thị kết quả đã tính.
Giải thích kết quả:
Ví dụ ở đây lấy điều kiện học sinh là Nam và điểm HK2> 7 nghĩa là lấy 8 và 9 điểm.
Ở HK2 có 3 bạn nam thoả mãn điều kiện là Lê Văn C, Huỳnh Văn D, Nguyễn Văn M nên sẽ lấy điểm của 3 bạn để tính trung bình cộng.
Nhấn Enter để hiển thị kết quả được tính toán
Ví dụ 3: Trung bình với 3 điều kiện
Đề bài: Tính điểm trung bình HK1 của nữ sinh có điểm HK1> 6, điểm HK2.
Bước 1: Nhập công thức = AVERAGEIFS (D2: D7; C2: C7; “Female”; D2: D7; “> 6”; E2: E7; “vào ô trung bình.
Lưu ý: Hãy nhớ sử dụng, hoặc; để phù hợp với công thức! Để biết nên sử dụng dấu trọng âm nào, bạn có thể nhìn vào dòng công thức bên dưới ô bạn đang nhập!
Giải thích chức năng:
Đ2: D7: Là diện tích được tính trung bình (Điểm HK1).
C2: C7; “Female”: Là điều kiện đầu tiên (Nữ sinh).
D2: D7; “> 6”: Là điều kiện thứ 2 (điểm HK1 lớn hơn 6).
E2: E7; “10”:> Là điều kiện thứ 3 (điểm HK2 dưới 10).
Nhập công thức như trong hình
Bước 2: Nhấn Enter để hiển thị kết quả đã tính.
Giải thích kết quả:
Ở đây, ví dụ lấy điều kiện là nữ sinh và điểm HK1> 6 và điểm HK2 nghĩa là lấy 7, 8, 9 điểm.
Ở HK2 có 2 bạn nữ thoả mãn điều kiện là Lê Thị B và Mai Thị H nên lấy điểm trung bình chung của 2 bạn.
Nhấn Enter để hiển thị kết quả được tính toán
Ví dụ 4: Trung bình với 3 điều kiện
Câu hỏi: Tính điểm trung bình HK2 của nam sinh có điểm HK1, HK2> 5.
Bước 1: Nhập công thức = AVERAGEIFS (E2: E7; C2: C7; “Nam”; D2: D7; “5”) 9 “; e2: e7;”> vào ô trung bình.
Lưu ý: Hãy nhớ sử dụng, hoặc; để phù hợp với công thức! Để biết nên sử dụng dấu trọng âm nào, bạn có thể nhìn vào dòng công thức bên dưới ô bạn đang nhập!
Giải thích chức năng:
E2: E7: Là diện tích được tính trung bình (Điểm HK2).
C2: C7; “Nam”: Là điều kiện đầu tiên (Sinh viên Nam).
D2: D7; ”là điều kiện thứ 2 (điểm HK1 dưới 9).
E2: E7; “> 5”: Là điều kiện thứ 3 (điểm HK2 lớn hơn 5).
Nhập công thức như trong hình
Bước 2: Nhấn Enter để hiển thị kết quả đã tính.
Giải thích kết quả:
Ví dụ ở đây, lấy điều kiện là nam sinh, điểm HK1 9> và HK2 điểm> 5 nghĩa là lấy từ 1 đến 8 ở HK1 và từ 6 đến 10 ở HK2.
Ở HK1 có 2 bạn nam thoả mãn điều kiện là Nguyễn Văn A và Huỳnh Văn Đ.
Ở HK2, tất cả các bạn nam đều đạt điều kiện.
Cuối cùng cả Nguyễn Văn A và Huỳnh Văn D đều thỏa mãn điều kiện nên sẽ lấy điểm trung bình HK2 của hai bạn này.
Nhấn Enter để hiển thị kết quả được tính toán
III. Các lỗi thường gặp khi sử dụng AVERAGEIFS. hàm số
1. Lỗi # DIV / 0!
Đây là một lỗi cực kỳ phổ biến khi bạn áp dụng hàm AVERAGEIFS trên một tệp có nhiều dữ liệu.
Lỗi # DIV / 0!
Nguyên nhân: Vì không thể tìm thấy giá trị của điều kiện đã cho (Criteria_range) trong dữ liệu.
Nguyên nhân gây ra lỗi # DIV / 0!
Cách khắc phục: Thay đổi lại điều kiện.
2. Không chấp nhận các ô trống
Nếu bạn sử dụng hàm AVERAGEIFS trên một ô trống không có dữ liệu, thì hàm AVERAGEIFS sẽ mặc định xem dữ liệu trong ô đó là 0 và không sử dụng nó để tính toán.
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
Comments