Hàm AVERAGE là một trong những hàm cơ bản của Excel, được dùng để tính giá trị trung bình của một tập hợp nhất định. Là dân kế toán, chắc hẳn việc dùng hàm AVERAGE để tính trung bình thông thường trong Excel chắc hẳn đã quá quen thuộc với bạn, vậy bạn đã biết cách tính trung bình kết hợp điều kiện chưa? Trong bài viết dưới đây, chúng tôi sẽ giới thiệu chi tiết cách dùng hàm AVERAGE có điều kiện, những lưu ý và ví dụ cụ thể của hàm này!
Hàm AVERAGE là gì?
Hàm AVERAGE là hàm Thống kê rất phổ biến trong Excel. Hàm được sử dụng để tính trung bình cộng của một tập hợp các đối số nhất định, hay trả về giá trị trung bình của một chuỗi số nhất định trong Excel.
Công thức
=AVERAGE(number1, [number2], …)
Trong đó:
Number1 (đối số bắt buộc) – Đây là số đầu tiên của một tham chiếu ô hoặc một dải ô mà bạn muốn tìm giá trị trung bình.
Number2 (đối số tùy chọn) – Đây là các số bổ sung, tham chiếu ô hoặc một dải ô mà chúng tôi muốn có giá trị trung bình.
Ví dụ
Bạn muốn tính điểm trung bình của 3 bài test. Áp dụng công thức sau:
Ta có kết quả:
Lưu ý
- Hàm AVERAGE có thể xử lý tối đa 255 đối số.
- Các ô có giá trị = 0 được tính.
- Các đối số là giá trị lỗi hoặc văn bản không được tính.
- Hàm tự động bỏ qua các ô trống trong một tập dữ liệu. Tuy nhiên, nếu phạm vi không chứa giá trị số, AVERAGE sẽ trả về lỗi #DIV/0!
Tính trung bình bằng Function Arguments
Với Function Arguments, bạn chỉ cần chọn hàm, nhập các giá trị, điều kiện theo công thức có sẵn và nhấn OK.
Bước 1: Nhấn vào nút fx
Bước 2: Hộp Insert Function xuất hiện. Kéo tìm và chọn AVERAGE
Bước 3: Khi hiện ra hộp thoại Function Arguments, bạn chỉ cần điền giá trị và phạm vi muốn tính trung bình và nhấn OK.
Hàm AVERAGEIF là gì?
Có nhiều cách để tìm giá trị trung bình của các số thỏa mãn các tiêu chí nhất định. Với hàm AVERAGEIF, Excel sẽ tìm giá trị trung bình cộng của các ô đáp ứng điều kiện đã cho.
Công thức
= AVERAGEIF (range,criteria,[average_range])
range là phạm vi để tìm các ô đáp ứng tiêu chí.
criteria là giá trị hoặc biểu thức mà Excel sẽ tìm kiếm trong phạm vi.
Average_range là một đối số tùy chọn. Đây là phạm vi ô chứa các giá trị được tính trung bình. Nếu average_range không được sử dụng, range sẽ được sử dụng.
Ví dụ
Tìm các ô “apples” trong cột A và tìm giá trung bình của ở cột B.
Kết quả:
criteria trong một hàm AVERAGEIF cũng có thể ở dạng biểu thức logic, như trong ví dụ dưới đây:
= AVERAGEIF (B4: H4, “<> 0”)
Công thức trên sẽ tìm giá trị trung bình của các giá trị trong khoảng từ B4 đến H4 mà khác 0. Do đối số thứ ba (tùy chọn) không tồn tại, nên các ô trong range (B4:H4) được sử dụng để tính giá trị trung bình.
Lưu ý
- AVERAGEIF tính giá trị trung bình của các số trong một phạm vi đáp ứng các tiêu chí được cung cấp. Tiêu chí có thể được cung cấp dưới dạng số, chuỗi hoặc tham chiếu. Ví dụ: tiêu chí hợp lệ có thể là 10, “> 10”, A1 hoặc “<” & A1.
- Đối số Average_range là tùy chọn. Khi Average_range không có sẵn, AVERAGEIF sẽ tính giá trị trung bình của các giá trị dựa trên range. Khi được cung cấp Average_range, AVERAGEIF sẽ tính giá trị trung bình của các số dựa trên Average_range.
- Các criteria của hàm AVERAGEIF có thể bao gồm các dấu (>, <, <>, =) và các ký tự đại diện (*,?)
- AVERAGEIF sẽ tự động bỏ qua các ô trống, ngay cả khi giá trị khớp điều kiện. Nói cách khác, AVERAGEIF sẽ không tính các ô trống trong khi tính giá trị trung bình. AVERAGEIF trả về # DIV/0! nếu không có ô nào trong phạm vi đáp ứng điều kiện.
- Hàm AVERAGEIF chỉ có thể áp dụng một điều kiện duy nhất. Nếu bạn cần tính giá trị trung bình trên nhiều điều kiện, hãy sử dụng hàm AVERAGEIFS.
Hàm AVERAGEIFS là gì?
Công thức
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong đó:
Average_range: (đối số bắt buộc) – Đây là một hay nhiều ô mà bạn muốn lấy giá trị trung bình, có thể là số, tên hoặc mảng tham chiếu có chứa số.
Criteria_range1, criteria_range2 range (đối số bắt buộc) – Criteria_range1 là đối số bắt buộc. Tuy nhiên, các Criteria_range tiếp theo là các đối số tùy chọn. Có thể có tới 127 phạm vi và tiêu chí liên quan.
Criteria1, criteria2, … Điều kiện đầu tiên là bắt buộc, còn các điều kiện tiếp theo là tùy chọn. Có thể có từ 1 đến 127 điều kiện ở dạng số, biểu thức, tham chiếu ô hoặc văn bản. Ví dụ: điều kiện có thể được biểu thị dưới dạng 12, “12”, “> 12”, “Cornflakes” hoặc A4.
Ví dụ
Giả sử bạn muốn biết chi phí điện thoại trung bình (Telephone Expenses) cho khu vực phía Bắc (North), mức chi phí lớn hơn 100. Danh sách chi phí ở cột B và số tiền trong cột D. Công thức như sau:
Kết quả
Như chúng ta có thể thấy, chỉ có hai ô đáp ứng tất cả các điều kiện, và do đó, chỉ những ô này được tính trung bình.
Lưu ý
#DIV0! – Lỗi này xảy ra khi:
- Đối số Average_range để trống hoặc là một giá trị văn bản.
- Các ô trong Average_range không ở dạng số.
- Tất cả các điều kiện không được đáp ứng.
Khi bất kỳ ô nào trong Criteria_range trống, hàm này coi là giá trị 0 (không). Có thể sử dụng các ký tự đại diện như dấu hỏi (?) Và dấu hoa thị (*) trong điều kiện của hàm.
Khi sử dụng số hoặc ngày kết hợp với dấu trong điều kiện của AVERAGEIFS, bạn nên đặt sự kết hợp này trong dấu ngoặc kép, ví dụ: “<8/21/2017”.
Hàm AVERAGEIFS để các ô chứa TRUE là 1; các ô chứa FALSE được để là 0 (không).
Trên đây là toàn bộ công thức cách sử dụng và những lưu ý cần thiết khi sử dụng hàm AVERAGE trong Excel. Ngoài hàm tính trung bình thì dân Kế toán cũng có thể tham khảo hàm tính tổng SUM, hàm đếm COUNT, hàm điều kiện IF,… cũng là hàm rất phổ biến và có nhiều ứng dụng trong Excel. Chúc bạn áp dụng thành công!