Cách sử dụng hàm AVERAGEIFS trong Excel -

Tóm lược

Hàm AVERAGEIFS trong Excel tính toán trung bình của các số trong một phạm vi đáp ứng một hoặc nhiều tiêu chí. Tiêu chí được sử dụng cho AVERAGEIFS có thể bao gồm toán tử logic (>, <,, =) và ký tự đại diện (*,?) Để đối sánh từng phần.

Mục đích

Các ô trung bình phù hợp với nhiều tiêu chí

Giá trị trả lại

Giá trị trung bình của các ô đáp ứng tất cả các tiêu chí

Cú pháp

= AVERAGEIFS (avg_rng, range1, tiêu chí1, (range2), (tiêu chí2),…)

Tranh luận

  • avg_rng - Phạm vi trung bình.
  • range1 - Phạm vi đầu tiên cần tránh.
  • tiêu chí1 - Tiêu chí sử dụng trên phạm vi1.
  • range2 - (tùy chọn) Phạm vi thứ hai để đánh giá.
  • tiêu chí2 - (tùy chọn) Tiêu chí sử dụng trên phạm vi2.

Phiên bản

Excel 2007

Ghi chú sử dụng

Hàm AVERAGEIFS 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. Phạm vi đầu tiên được cung cấp cho AVERAGEIFS là phạm vi chứa các số đến trung bình. Tiêu chí được cung cấp theo cặp (phạm vi, tiêu chí) và chỉ cặp đầu tiên là bắt buộc. Đối với mỗi tiêu chí bổ sung, hãy cung cấp một cặp phạm vi / tiêu chí khác. Các tiêu chí được cung cấp cho AVERAGEIFS được áp dụng với logic AND - tất cả các tiêu chí phải đúng với một số được đưa vào giá trị trung bình.

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. Tiêu chí cũng có thể bao gồm các ký tự đại diện để đối sánh từng phần.

AVERAGEIFS nằm trong nhóm tám hàm trong Excel chia tiêu chí logic thành hai phần (phạm vi + tiêu chí). Do đó, cú pháp được sử dụng để xây dựng tiêu chí khác nhau và AVERAGEIFS yêu cầu một phạm vi ô cho các đối số phạm vi - bạn không thể sử dụng một mảng.

AVERAGEIFS sẽ tự động bỏ qua các ô trống, ngay cả khi tiêu chí khớp. Nói cách khác, AVERAGEIFS sẽ không bao gồm các ô trống trong giá trị trung bình. AVERAGEIFS trả về # DIV / 0! nếu không có ô nào đáp ứng tiêu chí.

Ví dụ

Trong ví dụ được hiển thị, các công thức trong H5: H7 là:

=AVERAGEIFS(C5:C15,C5:C15,">0") =AVERAGEIFS(C5:C15,C5:C15,">0",C5:C15,"=2",E5:E15,">1")

Các công thức này trả về giá trung bình của các thuộc tính trong đó:

  1. giá lớn hơn 0
  2. giá lớn hơn 0 và nhỏ hơn 500.000 đô la
  3. tài sản có ít nhất 2 phòng ngủ và nhiều hơn 1 phòng tắm

Dấu ngoặc kép ("") trong tiêu chí

Nói chung, giá trị văn bản trong tiêu chí được đặt trong dấu ngoặc kép ("") và số thì không. Tuy nhiên, khi một toán tử logic được bao gồm với một số, thì số và toán tử phải được đặt trong dấu ngoặc kép. Lưu ý sự khác biệt trong hai ví dụ dưới đây. Vì công thức thứ hai sử dụng toán tử lớn hơn hoặc bằng với (> =), nên toán tử và số đều được đặt trong dấu ngoặc kép.

=AVERAGEIFS(C5:C15,D5:D15,2) // 2 bedrooms =AVERAGEIFS(C5:C15,D5:D15,">=2") // 2+ bedrooms

Dấu ngoặc kép cũng được sử dụng cho các giá trị văn bản. Ví dụ: để tính trung bình các giá trị trong B1: B10 khi các giá trị trong A1: A10 bằng "đỏ", bạn có thể sử dụng công thức như sau:

=AVERAGEIFS(B1:B10,A1:A10,"red")

Nhiều tiêu chí

Nhập tiêu chí theo cặp (phạm vi, tiêu chí). Ví dụ: để các giá trị trung bình trong A1: A10, trong đó B1: B10 = "A" và C1: C10> 5, hãy sử dụng:

=AVERAGEIFS(A1:A10,B1:B10,"A",C1:C10,">5")

Giá trị từ một ô khác

Một giá trị từ một ô khác có thể được đưa vào tiêu chí bằng cách sử dụng phép nối. Trong ví dụ dưới đây, AVERAGEIFS sẽ trả về giá trị trung bình của các số trong A1: A10 nhỏ hơn giá trị trong ô B1. Chú ý toán tử less than (là văn bản) được đặt trong dấu ngoặc kép.

=AVERAGEIFS(A1:A10,A1:A10,"<"&B1) // average values less than B1

Ký tự đại diện

Các ký tự đại diện dấu chấm hỏi (?), Dấu hoa thị (*) hoặc dấu ngã (~) có thể được sử dụng trong tiêu chí. Dấu chấm hỏi (?) Khớp với bất kỳ ký tự nào và dấu sao (*) khớp với 0 hoặc nhiều ký tự thuộc bất kỳ loại nào. Ví dụ: để tính trung bình các giá trị trong B1: B10 khi các giá trị trong A1: A10 chứa văn bản "màu đỏ", bạn có thể sử dụng công thức như sau:

=AVERAGEIFS(B1:B10,A1:A10,"*red*")

Dấu ngã (~) là một ký tự thoát cho phép bạn tìm các ký tự đại diện theo nghĩa đen. Ví dụ: để khớp với dấu chấm hỏi nghĩa đen (?), Dấu hoa thị (*) hoặc dấu ngã (~), hãy thêm dấu ngã vào trước ký tự đại diện (tức là ~ ?, ~ *, ~~).

Lưu ý: thứ tự của các đối số khác nhau giữa AVERAGEIFS và AVERAGEIF. Phạm vi trung bình luôn là đối số đầu tiên trong AVERAGEIFS.

Ghi chú

  • Nếu không có dữ liệu nào phù hợp với tiêu chí, AVERAGEIFS trả về giá trị # DIV0! lỗi
  • Mỗi phạm vi bổ sung phải có cùng số hàng và số cột với phạm vi trung bình .
  • Tiêu chí không phải số cần được đặt trong dấu ngoặc kép nhưng tiêu chí số thì không. Ví dụ: 100, "100", "> 32", "jim" hoặc A1 (trong đó A1 chứa một số).
  • Các ký tự đại diện? và * có thể được sử dụng trong tiêu chí. Dấu chấm hỏi khớp với bất kỳ ký tự nào và dấu hoa thị khớp với không hoặc nhiều ký tự thuộc bất kỳ loại nào.
  • Để tìm dấu hỏi hoặc dấu hoa thị theo nghĩa đen, hãy sử dụng dấu ngã (~) ở phía trước dấu hỏi hoặc dấu hoa thị (tức là ~ ?, ~ *).

thú vị bài viết...