Bài viết này mình sẽ giới thiệu các bạn sử dụng hàm AVERAGEIF() trong Excel và kèm theo ví dụ thực tế về hàm AVERAGEIF(). Hàm AVERAGEIF() giúp chúng ta tính trung bình cộng có điều kiện trong Excel rất hay. 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() 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.  Để 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(). 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:D19) Trong đó, công thức có:
Ở ví dụ này, mình đang cần tính giá trị trung bình của mỗi lớp. Ảnh bên dưới là ảnh kết quả tính trung bình của lớp 12A1, nhưng điều kiện truyền vào là cố định. 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ị. 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:D19) 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. 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ả. Để không bị lỗi, các bạn phải điển giá trị vào ô C21 trong bảng tí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!
Nếu bạn là người đã từng học Excel biết đến hàm average với công dụng đó là tính trung bình của một loạt các giá trị các số. Nhưng nếu gặp phải công việc thực hiện tìm giá trị trung bình cộng của các giá trị theo tiêu chí điều kiện cho trước thì nếu chỉ áp dụng riêng có hàm average thì không thể thực hiện được bài toán, vậy nên hàm Averageif được xây dựng lên với mục đích làm việc nâng cao hơn những gì hàm average không thể thực hiện được. 1. Hàm AVERAGE là gì1.1. Định nghĩaHàm AVERAGE trong Excel là hàm trả về giá trị trung bình cộng được cung cấp dưới dạng nhiều đối số. Hàm AVERAGE có thể xử lý tối đa 255 đối số riêng lẻ, có thể bao gồm số, tham chiếu ô, phạm vi, mảng và hằng số. 1.2. Công thức tính hàm AVERAGEHàm AVERAGE được thể hiện như sau: =AVERAGE (number 1, [number 2],..) Trong đó: - number1: Một tham chiếu số hoặc ô tham chiếu đến các giá trị số - number2: Tùy chọn. Một tham chiếu số hoặc ô tham chiếu đến các giá trị số 1.3. Chú ý khi sử dụng hàm AVERAGE+ Hàm AVERAGE trả về giá trị trung bình của một nhóm các số được cung cấp + Để tính giá trị trung bình, Excel cộng các số lại với nhau và chia chi tổng số. Ví dụ AVERAGE (2,4,6) sẽ cho kết quả trả về là 4. + Các số có thể được cung cấp dưới dạng số, phạm vi, phạm vi được đặt tên hoặc tham chiếu ô có chứa giá trị số. lên đến 255 số có thể được cung cấp. + Chức năng AVERAGE sẽ tự động bỏ qua các ô trống. 1.4. Ví dụ về hàm AVERAGEVí dụ : Cho bảng dữ liệu sau, hãy tính điểm trung bình cho nhóm học sinh dưới đây Mô tả cách dùng hàm AVERAGE trong Excel. Hình 1 Các bước thực hiện: - Bước 1: Kích chuột vào địa chỉ ô cần tính giá trị trung bình (E3) - Bước 2: Nhập công thức: =AVERAGE(B3:D3) - Bước 3: Nhấn Enter - Bước 4: Kích chuột vào ô E3 đến khi hiện ra dấu “+” kéo thả chuột đến ô cuối cùng cần tính, bạn sẽ được kết quả như bảng dưới đây. Mô tả cách dùng hàm AVERAGE trong Excel. Hình 2 Ngoài những ví dụ mà Unica đã cung cấp, nếu bạn muốn tìm hiểu chi tiết hơn nữa về cách sử dụng của hàm AVERAGE thì có thể tham khảo nội dung Video bài giảng dưới đây: Hướng dẫn cách sử dụng hàm AVERAGE >> Xem thêm: Cách dùng hàm Count để đếm số lượng ô trong excel 2. Hàm Averageif là 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 Công thức hàm: =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. 3. Cách sử dụng hàm AverageifVí dụ 1: Tính trung bình với điều kiện lớn hơnÁp dụng công thức của hàm averageif để tính trung bình 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. Ví dụ 2: Tính trung bình với điều kiện bé hơnQuan 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 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 Ví dụ 3: Tính trung bình với điều kiện là chữĐể thực hiện tăng các điều kiện cho việc tính trung bình, thực hiện ví dụ trên với điều kiện, tìm trung bình tiền lương của các nhân viên nữ: Ví dụ về cách sử dụng hàm Averageif - Hình 5 Chèn công thức trên ví dụ 3 vào và kết quả tính ra được 6740000 là mức tiền lương trung bình của những người có giới tính là nữ. Ví dụ 4: 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. Lưu ý khi sử dụng hàm 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! 5. Một số lỗi thường gặp khi sử dụng hàm Averageif# DIV / 0!:# DIV / 0! - Đây là một lỗi phổ biến khi sử dụng hàm AVERAGEIF Lỗi thường gặp khi dùng hàm AVERAGRIF - Hình 1 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ố. Cách khắc phục: Thay đổi lại điều kiện trong bảng tính Excel Lỗi thường gặp khi dùng hàm AVERAGRIF - Hình 2 #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ự. >> Xem thêm: Hướng dẫn chi tiết cách sử dụng hàm Dcounta trong excel Trên đây là hướng dẫn và ví dụ đơn giản nhất về hàm averageif trong excel, có thể dựa vào những hướng dẫn cơ bản nhất đó để phát triển về các cách tính theo các điều kiện khác nhau nữa. Tags: Excel |
