Hàm AVERAGEIF trong excel: Tính trung bình cộng nhiều điều kiện

Nếu bạn là người học Excel chắc chắn bạn không còn cảm thấy xa lạ gì với hàm AVERAGEIF nữa. Đây là một hàm tính quan trọng trong excel có chức năng dùng để tính trung bình loạt giá trị của các số. Hàm AVERAGEIF trong excel mặc dù có cách sử dụng tương đối đơn giản, dễ hiểu nhưng không phải ai cũng biết, nhất là với người mới học excel. Thấu hiểu điều đó, bài viết sau Unica sẽ hướng dẫn cho bạn cách dùng, cùng tham khảo nội dung bài viết sau để biết cách thực hiện nhé.

1. AVERAGEIF là hàm gì?

Hàm Averageif là một hàm tích hợp trong Excel được phân loại là Hàm thống kê. Nó có thể được sử dụng như một hàm bảng tính (WS) trong Excel. Là một hàm trang tính, hàm Averageif có thể được nhập như một phần của công thức trong một ô của trang tính. Hàm trả về mức trung bình (trung bình số học) của tất cả các số trong một phạm vi ô, dựa trên một tiêu chí nhất định.

Hàm Averageif trong excel

2. Ứng dụng của hàm AVERAGEIF trong excel

Trong excel hàm AVERAGEIF được sử dụng để tính trung bình cộng của những giá trị có đi kèm theo điều kiện. Nói dễ hiểu hơn hàm AVERAGEIF được dùng để thay thế một số công thức kết hợp hàm như:

- Công thức kết hợp giữa hàm AVERAGEIF và hàm IF.

- Công thức kết hợp giữa hàm AVERAGEI và hàm VLOOKUP.

>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

3. Công thức tính hàm AVERAGEIF

Công thức hàm tính trung bình trong Excel:

Công thức tính hàm AVERAGEIF

=AVERAGEIF(range, criteria, [average_range])

Trong đó: 

- Range: một mảng các giá trị (hoặc phạm vi ô chứa giá trị) sẽ được kiểm tra theo các tiêu chí được cung cấp.

- Criteria: điều kiện được kiểm tra đối với từng giá trị trong phạm vi được cung cấp.

- [Average_range]: một mảng tùy chọn của các giá trị số (hoặc các ô chứa số) được tính trung bình, nếu giá trị tương ứng trong phạm vi thỏa mãn các tiêu chí được cung cấp.

Nếu đối số [Average_range] bị bỏ qua, trung bình được tính cho các giá trị trong phạm vi được cung cấp ban đầu.

>>> Xem thêm: Trọn bộ tin học văn phòng giúp nâng cao hiệu suất công việc 100%

4. Cách sử dụng hàm AVERAGEIF trong excel

Cách sử dụng hàm AVERAGEIF không khó, có rất nhiều cách để sử dụng hàm này. Dưới đây là một số ví dụ cụ thể giúp bạn ứng dụng hàm trung bình có điều kiện trong excel (AVERAGEIF) một cách nhanh chóng nhất, cùng khám phá nhé.

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

Áp dụng công thức hàm trung bình có điều kiện trong excel tiền lương của những người có mức lương được trên 6000000 đồng ở bảng thống kê dưới đây:

Ví dụ về cách sử dụng hàm Averageif - Hình 1

Chỉ cần chọn 1 ô để hiển thị kết quả tính trung bình và nhập công thức:

Ví dụ về cách sử dụng hàm Averageif - Hình 2

Kết quả hiện lên trên ô tính là 9683333,333 là tiền lương trung bình của tất cả những người có mức lương trên 6000000 đồng.

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

Quan sát bảng dữ liệu sau áp dụng hàm tính trung bình có điều kiện trong excel yêu cầu tính tiền lương trung bình của nhân viên có số tiền lương <>

- Bước 1: Trong ô muốn hiển thị kết quả trung bình, bạn nhập công thức =AVERAGEIF(D2:D7;"<>

Trong đó:

- D2:D7: Dữ liệu từ D1-D7 cần tính trung bình.

- ""<>: Điều kiện của đề bài

Ví dụ về cách sử dụng hàm Averageif - Hình 3

- Bước 2: Nhấn Enter để nhận kết quả. 

Ví dụ về cách sử dụng hàm Averageif - Hình 4

4.3. Tính trung bình với điều kiện khác 1 số

Quan sát bảng dữ liệu sau, yêu cầu tính tiền lương trung bình của nhân viên không có chữ Lê trong tên. 

- Bước 1: Trong ô muốn hiển thị kết quả trung bình, bạn nhập công thức sau: =AVERAGEIF(D2:D7;"<> Lê") 

Trong đó:

- D2:D7: Dữ liệu từ D1-D7 cần tính trung bình.

- "<>Lê": Là yêu cầu của đề bài không có chữ Lê trong tên

Ví dụ về cách sử dụng hàm Averageif - Hình 6

- Bước 2: Nhấn Enter để hoàn thành thao tác.

Ví dụ về cách sử dụng hàm Averageif - Hình 7

4.4. Tính trung bình với 2 điều kiện là số

Ví dụ: Chúng ta có bảng điểm học kỳ 1 (HK1) và học kỳ 2 (HK2) của một số học sinh.

Yêu cầu: 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.

Cách dùng hàm AVERAGEIF trong trường hợp này sẽ được thực hiện như sau.

- Bước 1: Ở ô hiển thị kết quả, bạn hãy nhập công thức tính 

=AVERAGEIFS(D2:D7;D2:D7;">6";D2:D7;"<9")

Trong đó 

- D2:D7 là vùng tham chiếu cần tính điểm trung bình 

- D2:D7;">6" là điều kiện 1 theo yêu cầu điểm HK1 lớn hơn 6

- D2:D7;"<9" là điều kiện 2 theo yêu cầu điểm HK2 nhỏ hơn 9

Nhập hàm AVERAGEIFS

- Bước 2: Cuối cùng bạn chỉ cần nhấn enter để nhận kết quả là điểm trung bình HK1 theo 2 điều kiện yêu cầu.

Kết quả sau khi nhấn enter

4.5. Tính trung bình với 2 điều kiện số và chữ

Ví dụ: Chúng ta có bảng điểm của một số học sinh. Yêu cầu hãy tính điểm trung bình học kỳ 2 (HK2) của các bạn học sinh nam có điểm HK2 lớn hơn 7.

Trong trường hợp này chúng ta sẽ nhập công thức tính như sau:

 =AVERAGEIFS(E2:E7;C2:C7;"Nam";E2:E7;">7") 

Trong đó: 

E2:E7: Là vùng tham chiếu cần tính điểm trung bình.

C2:C7;"Nam": Là điều kiện chữ (điều kiện 1) 

E2:E7;">7": Là điều kiện số (điều kiện 2)

Nhập hàm để tính trung bình với 2 điều kiện số và chữ

Sau khi nhập đúng công thức, bạn chỉ cần nhấn enter để nhận kết quả

Kết quả cuối cùng nhận được

4.6. Tính trung bình với 3 điều kiện

Ví dụ: Chúng ta có bảng điểm của một số học sinh. Yêu cầu bạn hãy tính điểm trung bình học kỳ 1 (HK1) của những học sinh nữ, có điểm HK1 lớn hơn 6 và điểm HK2 nhỏ hơn 10.

Theo yêu cầu này chúng ta sẽ sử dụng công thức tính sau đây:

=AVERAGEIFS(D2:D7;C2:C7;"Nữ";D2:D7;">6";E2:E7;"<10")

Trong đó:

- D2:D7: Là vùng tham chiếu cần tính điểm trung bình 

- C2:C7;"Nữ": Là điều kiện 1 

- D2:D7;">6": Là điều kiện 2 

- E2:E7;"<10": Là điều kiện 3

Nhập hàm tính trung bình với điều kiện 3 số

Sau khi nhập công thức chuẩn, bạn hãy nhấn enter để kết quả hiển thị

Kết quả hiển thị

5. Cách kết hợp hàm AVERAGEIF và vlookup

Trên thực tế, chúng ta sẽ không cần sử dụng hàm vlookup kết hợp với hàm AVERAGEIF. Bởi vì, hàm AVERAGEIF là hàm tính trung bình có điều kiện. Mà điều kiện ở đây có thể là một giá trị lọc của hàm Vlookup. Ví dụ minh họa dưới đây sẽ giúp bạn hiểu rõ hơn về trường hợp này.

Ví dụ, chúng ta có bảng sản lượng các loại hoa quả trong kho. Yêu cầu hãy tính sản lượng trung bình của táo.

Ví dụ bảng các loại hoa quả

Lúc này, chúng ta chỉ cần sử dụng công thức tính sau đây:

=AVERAGEIF(A1:A24,E2,B1:B24)

Trong công thức này E2 chính là điều kiện của hàm AVERAGEIF. Đồng thời nó cũng là kết quả của hàm =Vlookup("Táo",$A$1:$A$24,2,0)

6. Các lỗi thường gặp khi sử dụng hàm AVERAGEIF

Trong khi bạn sử dụng hàm tính trung bình cộng có điều kiện trong excel (AVERAGEIF) bạn sẽ thấy một số trường hợp kết quả bị lỗi phổ biến dưới đây.

6.1. Lỗi #DIV/0!

Đây là lỗi do không tìm thấy dữ liệu phù hợp với điều kiện yêu cầu. 

Nguyên nhân:

- Không có giá trị nào trong mảng phạm vi được cung cấp thỏa mãn các tiêu chí được cung cấp

- Các giá trị được tính trung bình đều không phải là số.

Ví dụ:

Dưới đây là ví dụ minh họa. Theo yêu cầu chúng ta sẽ tính tiền lương trung bình của những nhân viên có tiền lương dưới 1.000.000 VNĐ. Trong khi đó, không có bất kỳ ai trong bảng lương có mức tiền lương phù hợp với yêu cầu. 

Ví dụ bảng tính lương nhân viên

Kết quả hiển thị sẽ báo lỗi #DIV/0!:

Lỗi #DIV/0! xuất hiện

Cách khắc phục: Thay đổi lại điều kiện của giá trị

#GIÁ TRỊ!: xảy ra nếu đối số tiêu chí là một chuỗi văn bản có độ dài lớn hơn 255 ký tự.

6.2. Không nhận ô trống

Nếu bạn sử dụng hàm AVERAGEIF mà trong bảng tính giá trị trung bình có ô trống (không có dữ liệu) thì khi bạn nhấn enter excel sẽ báo lỗi cảnh báo lỗi như hình.

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

Lúc này để cải thiện lỗi, bạn hãy mặc định những ô trống sẽ là giá trị “0” hoặc biện luận hàm theo yêu cầu của từng trường hợp.

7. Lưu ý khi sử dụng hàm tính giá trị trung bình có điều kiện AVERAGEIF

- Các ô trong vùng dữ liệu có chứa TRUE hoặc FALSE sẽ bị bỏ qua.

- Nếu ô trong vùng dữ liệu trung bình là một ô trống, AVERAGEIF sẽ bỏ qua nó.

- Nếu vùng dữ liệu là 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 vùng điều kiện (Criteria) bị bỏ trống, AVERAGEIF sẽ xem ô đó như giá trị 0.

- Nếu không có ô nào trong vùng dữ liệu đáp ứng các tiêu chí, AVERAGEIF sẽ trả về giá trị lỗi #DIV/0!

8. Kết luận

Trên đây là hướng dẫn và ví dụ đơn giản nhất về hàm AVERAGEIFtrong excel. Hy vọng với những ví dụ minh họa cụ thể về cách sử dụng hàm AVERAGEIF trong excel mà chúng tôi đã chia sẻ sẽ giúp bạn ứng dụng hàm AVERAGEIF chuẩn xác và thuần thục hơn. 

>>> Xem thêm: 5 cách sử dụng hàm trừ trong excel không phải ai cũng biết

>>> Xem thêm: Cách sử dụng hàm OFFSET trong Excel để tham chiếu dữ liệu có ví dụ

>>> Xem thêm: Hướng dẫn chi tiết cách sử dụng hàm Dcounta trong excel