Giới thiệu hàm AVERAGEIF()
Hàm AVERAGEIF() là gì?
Hàm AVERAGEIF() là hàm tính trung bình cộng cho các ô trong Excel nhưng phải thõa mãng điều kiện mà mình chỉ định trước đó.
Cú pháp hàm AVERAGEIF()
Cú pháp hàm AVERAGEIF() như sau: =AVERAGEIF(range, criteria, [average_range])
Trong đó:
Ở trên, mình đã giới thiệu cho các bạn về hàm AVERAGEIF() để tính trung bình cộng có điều kiên trong Excel. Tiếp tục để hiểu rõ hơn về hàm này, mình có một ví dụ thực tế đơn giản để cho các bạn hiểu rõ hơn về hàm AVERAGEIF().
Giả sử các bạn có một danh sách bảng điểm học sinh của nhiều lớp. Yêu cầu đặc ra là làm sao để tính trung bình cộng của từng lớp.
Ảnh bài tập sử dụng hàm AVERAFEIF trong excel
Để tính được điểm trung bình của tất cả sinh viên ở mỗi lớp ở bài tập trên thì không thể nào áp dụng được hàm AVERAGE() được. Vì danh sách điểm trên có các sinh viên ở các lớp khác nhau. Nhưng chúng ta có thể tính trung bình cửa mỗi lớp trên bằng cách áp dụng hàm AVERAGEIF().
Bước 1: Nhập công thức ô tính trung bình
Cú pháp hàm AVERAGEIF(): =AVERAGEIF(range, criteria, [average_range])
So với cú pháp ta có công thức như sau: =AVERAGEIF(C5:C19;”12A1″;D5
19)
Trong đó, công thức có:
Ảnh áp dụng hàm AVERAGEIF() vào ví dụ
Bước 2: Nhấn Enter và xem kết quả sau khi áp dụng hàm truyền giá trị cố trị
Sau khi nhập công thức vào ô chúng ta sẽ nhận được kết quả như ảnh, nhưng giá trị điều kiện ở Criteria là giá trị cố trị.
Kết quả sau khi áp dụng hàm truyền điều kiện cố trị
Bước 3: Lấy điều kiện là giá trị động ở một ô trong bảng
Sau khi áp dụng công thức vào danh sách bảng điểm trên, nhưng có một vấn đề lớn cần giải quyết. Vấn đề là không lẽ cứ vào công thức sửa điều kiện, như vậy không ổn. Cách giải quyết là chúng ta sẽ thay thế tham số điều kiện Criteria bằng giá trị của một ô.
Ta có công thức sau khi thay đổi như sau: =AVERAGEIF(C5:C19;C21;D519)
Trong đó: công thức có thay đổi mới là địa chỉ C21 là giá trị một của một ô trong bảng tính. giá trị đó sẽ là điều kiện để tính trung bình cộng.
Kết quả sau khi áp dụng hàm AVEGAREIF() truyền điều kiện là giá trị một ô
Bước 4: Nhấn Enter và xem kết quả sau khi áp dụng hàm truyền giá trị động
Khi nhấn Enter, tại ô điền công thức sẽ xuất hiện lỗi #DIV/0!. Lý do xuất hiện lỗi này là giá trị ở ô C21 lấy giá trị điều kiên không có dữ liệu, hoặc điều kiện truyền vào không có kết quả.
Ảnh hàm AVERAGE() bị lỗi #DIV/0 trong Excel
Để không bị lỗi, các bạn phải điển giá trị vào ô C21 trong bảng tính.
Kết quả sử dụng hàm AVERAGEIF() bằng cách truyền giá trị điều kiện từ một ô
Kết luận và file tải thực hành
Vậy là mình đã giới thiệu cho các bạn hàm AVERAGEIF() để tính tổng có điều kiện trong Excel. Ở bài hướng dẫn này mình có ví dụ cụ thể về hàm này. Hàm này thường dùng để tính trung bình cộng khi dữ liệu có chứa điều kiện. Bên dưới là file thực hành trong bài hướng dẫn này, các bạn có thể tải về và làm theo để hiểu hơn về hàm tính trung bình cộng có điều kiện sử dụng hàm AVERAGEIF().
Cảm ơn các bạn, chúc các bạn thành công!
Hàm AVERAGEIF() là gì?
Hàm AVERAGEIF() là hàm tính trung bình cộng cho các ô trong Excel nhưng phải thõa mãng điều kiện mà mình chỉ định trước đó.
Cú pháp hàm AVERAGEIF()
Cú pháp hàm AVERAGEIF() như sau: =AVERAGEIF(range, criteria, [average_range])
Trong đó:
- Range: Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số. Đây là tham số bắt buộc.
- Criteria: Tiêu chí ở dạng số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô sẽ tính giá trị trung bình. Điều kiện để lọc ra giá trị trung bình.Ví dụ, các tiêu chí có thể được biểu thị dưới dạng như 32, “32”, “>32”, “táo” hoặc B4. Đây là tham số bắt buộc.
- Average_range: Tùy chọn. Tập hợp các ô thực tế để tính giá trị trung bình. Nếu bỏ qua, phạm vi được dùng.
- Ô có giá trị trả về TRUE hoặc FALSE sẽ bị bỏ qua.
- Nếu ô trong vùng dữ liệu tính trung bình rỗng, thì hàm AVERAGEIF() sẽ bỏ qua.
- Nếu vùng dữ liệu có giá trị trống hoặc là văn bản, hàm AVERAGEIF() sẽ trả về lỗi #DIV0!.
- Nếu ô trong vùng điều kiện Criteria bị bỏ trống, thì hàm AVERAGEIF() sẽ xem giá trị đó là số 0.
- Nếu không có ô nào trong vùng dữ liệu Range đáp ứng điều kiện Criteria, thì hàm AVERAGEIF() sẽ trả về 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.
Ở trên, mình đã giới thiệu cho các bạn về hàm AVERAGEIF() để tính trung bình cộng có điều kiên trong Excel. Tiếp tục để hiểu rõ hơn về hàm này, mình có một ví dụ thực tế đơn giản để cho các bạn hiểu rõ hơn về hàm AVERAGEIF().
Giả sử các bạn có một danh sách bảng điểm học sinh của nhiều lớp. Yêu cầu đặc ra là làm sao để tính trung bình cộng của từng lớp.
Ảnh bài tập sử dụng hàm AVERAFEIF trong excel
Để tính được điểm trung bình của tất cả sinh viên ở mỗi lớp ở bài tập trên thì không thể nào áp dụng được hàm AVERAGE() được. Vì danh sách điểm trên có các sinh viên ở các lớp khác nhau. Nhưng chúng ta có thể tính trung bình cửa mỗi lớp trên bằng cách áp dụng hàm AVERAGEIF().
Bước 1: Nhập công thức ô tính trung bình
Cú pháp hàm AVERAGEIF(): =AVERAGEIF(range, criteria, [average_range])
So với cú pháp ta có công thức như sau: =AVERAGEIF(C5:C19;”12A1″;D5
19)Trong đó, công thức có:
- C5:C19 là tham số Range, tức là mảng chứa các giá trị trong bảng từ ô C5 đến ô C19. Điều kiện sẽ dò ở đây để xác định giá trị trung bình.
- “12A1” là tham số Criteria, điền tên lớp vào. Là điều kiện để xác định kết quả trả về giá trị trung bình. giá trị này sẽ dựa vào Range để xác định.
- D519 là tham số Average_range, chứa các ô dạng số để tính giá trị trung bình sau khi xác đinh được điều kiện từ ô D5 đến ô D19.
Ảnh áp dụng hàm AVERAGEIF() vào ví dụ
Bước 2: Nhấn Enter và xem kết quả sau khi áp dụng hàm truyền giá trị cố trị
Sau khi nhập công thức vào ô chúng ta sẽ nhận được kết quả như ảnh, nhưng giá trị điều kiện ở Criteria là giá trị cố trị.
Kết quả sau khi áp dụng hàm truyền điều kiện cố trị
Bước 3: Lấy điều kiện là giá trị động ở một ô trong bảng
Sau khi áp dụng công thức vào danh sách bảng điểm trên, nhưng có một vấn đề lớn cần giải quyết. Vấn đề là không lẽ cứ vào công thức sửa điều kiện, như vậy không ổn. Cách giải quyết là chúng ta sẽ thay thế tham số điều kiện Criteria bằng giá trị của một ô.
Ta có công thức sau khi thay đổi như sau: =AVERAGEIF(C5:C19;C21;D5
19)Trong đó: công thức có thay đổi mới là địa chỉ C21 là giá trị một của một ô trong bảng tính. giá trị đó sẽ là điều kiện để tính trung bình cộng.
Kết quả sau khi áp dụng hàm AVEGAREIF() truyền điều kiện là giá trị một ô
Bước 4: Nhấn Enter và xem kết quả sau khi áp dụng hàm truyền giá trị động
Khi nhấn Enter, tại ô điền công thức sẽ xuất hiện lỗi #DIV/0!. Lý do xuất hiện lỗi này là giá trị ở ô C21 lấy giá trị điều kiên không có dữ liệu, hoặc điều kiện truyền vào không có kết quả.
Ảnh hàm AVERAGE() bị lỗi #DIV/0 trong Excel
Để không bị lỗi, các bạn phải điển giá trị vào ô C21 trong bảng tính.
Kết quả sử dụng hàm AVERAGEIF() bằng cách truyền giá trị điều kiện từ một ô
Kết luận và file tải thực hành
Vậy là mình đã giới thiệu cho các bạn hàm AVERAGEIF() để tính tổng có điều kiện trong Excel. Ở bài hướng dẫn này mình có ví dụ cụ thể về hàm này. Hàm này thường dùng để tính trung bình cộng khi dữ liệu có chứa điều kiện. Bên dưới là file thực hành trong bài hướng dẫn này, các bạn có thể tải về và làm theo để hiểu hơn về hàm tính trung bình cộng có điều kiện sử dụng hàm AVERAGEIF().
Cảm ơn các bạn, chúc các bạn thành công!