Hướng dẫn này trình bày cách sử dụng Hàm AVERAGEIF và AVERAGEIFS trong Excel và Google Trang tính để tính trung bình dữ liệu đáp ứng các tiêu chí nhất định.
Tổng quan về hàm AVERAGEIF
Bạn có thể sử dụng hàm AVERAGEIF trong Excel để đếm các ô có chứa một giá trị cụ thể, đếm các ô lớn hơn hoặc bằng một giá trị, v.v.
Để sử dụng Hàm AVERAGEIF Excel Worksheet, hãy chọn một ô và nhập:
(Chú ý cách các đầu vào công thức xuất hiện)
Cú pháp và đối số của hàm AVERAGEIF:
= AVERAGEIF (phạm vi, tiêu chí, [phạm vi trung bình])
phạm vi - Phạm vi ô cần đếm.
tiêu chuẩn - Các tiêu chí kiểm soát ô nào nên được đếm.
phạm vi trung bình - [tùy chọn] Các ô tính trung bình. Khi bị bỏ qua, phạm vi sẽ được sử dụng.
Hàm AVERAGEIF là gì?
Hàm AVERAGEIF là một trong những hàm cũ được sử dụng trong bảng tính. Nó được sử dụng để quét qua một loạt các ô kiểm tra một tiêu chí cụ thể và sau đó đưa ra giá trị trung bình (còn gọi là giá trị trung bình toán học) nếu các giá trị trong một phạm vi tương ứng với các giá trị đó. Chức năng AVERAGEIF ban đầu chỉ được giới hạn ở một tiêu chí. Sau năm 2007, hàm AVERAGEIFS được tạo ra cho phép vô số tiêu chí. Hầu hết cách sử dụng chung vẫn giống nhau giữa hai loại, nhưng có một số khác biệt quan trọng trong cú pháp mà chúng ta sẽ thảo luận trong suốt bài viết này.
Nếu bạn chưa làm như vậy, bạn có thể xem lại nhiều cấu trúc và ví dụ tương tự trong bài viết COUNTIFS.
Ví dụ cơ bản
Hãy xem xét danh sách doanh số được ghi lại này và chúng tôi muốn biết thu nhập trung bình.
Bởi vì chúng tôi đã có một khoản chi phí, giá trị âm, chúng tôi không thể chỉ tính mức trung bình cơ bản. Thay vào đó, chúng tôi chỉ muốn tính trung bình các giá trị lớn hơn 0. “Lớn hơn 0” là tiêu chí của chúng tôi trong một hàm AVERAGEIF. Công thức của chúng tôi để xác định điều này là
= AVERAGEIF (A2: A7, "> 0")
Ví dụ về hai cột
Mặc dù hàm AVERAGEIF ban đầu được thiết kế để cho phép bạn áp dụng tiêu chí cho phạm vi số bạn muốn tính tổng, nhưng phần lớn thời gian bạn sẽ cần áp dụng một hoặc nhiều tiêu chí cho các cột khác. Hãy xem xét bảng này:
Bây giờ, nếu chúng ta sử dụng hàm AVERAGEIF ban đầu để tìm xem trung bình chúng ta có bao nhiêu Chuối. Chúng tôi sẽ đặt tiêu chí của mình vào ô D1 và chúng tôi sẽ cần đưa ra phạm vi mà chúng tôi muốn Trung bình là đối số cuối cùng và vì vậy công thức của chúng tôi sẽ là
= AVERAGEIF (A2: A7, D1, B2: B7)
Tuy nhiên, khi các lập trình viên cuối cùng nhận ra rằng người dùng muốn đưa ra nhiều hơn một tiêu chí, thì hàm AVERAGEIFS đã được tạo ra. Để tạo một cấu trúc có thể hoạt động cho bất kỳ số lượng tiêu chí nào, AVERAGEIFS yêu cầu phạm vi tổng được liệt kê trước. Trong ví dụ của chúng tôi, điều này có nghĩa là công thức cần phải
= AVERAGEIFS (B2: B7, A2: A7, D1)
LƯU Ý: Hai công thức này nhận được cùng một kết quả và có thể trông giống nhau, vì vậy hãy chú ý đến hàm nào đang được sử dụng để đảm bảo bạn liệt kê tất cả các đối số theo đúng thứ tự.
Làm việc với Ngày, Nhiều tiêu chí
Khi làm việc với ngày tháng trong bảng tính, mặc dù có thể nhập ngày tháng trực tiếp vào công thức, nhưng cách tốt nhất là có ngày tháng trong một ô để bạn có thể chỉ tham chiếu ô trong công thức. Ví dụ: điều này giúp máy tính biết rằng bạn đang muốn sử dụng ngày 27/5/2020 chứ không phải số 5 chia cho 27 chia cho 2022.
Hãy xem bảng tiếp theo của chúng tôi ghi lại số lượng khách truy cập vào một trang web hai tuần một lần.
Chúng ta có thể chỉ định điểm bắt đầu và điểm kết thúc của phạm vi mà chúng ta muốn xem xét trong D2 và E2. Sau đó, công thức của chúng tôi để tìm số lượng khách truy cập trung bình trong phạm vi này có thể là:
= AVERAGEIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Lưu ý cách chúng tôi có thể nối các so sánh của “=” với các tham chiếu ô để tạo tiêu chí. Ngoài ra, mặc dù cả hai tiêu chí đang được áp dụng cho cùng một phạm vi ô (A2: A7), bạn cần phải viết ra phạm vi hai lần, một lần cho mỗi tiêu chí.
Nhiều cột
Khi sử dụng nhiều tiêu chí, bạn có thể áp dụng chúng cho cùng một phạm vi như chúng tôi đã làm với ví dụ trước đó hoặc bạn có thể áp dụng chúng cho các phạm vi khác nhau. Hãy kết hợp dữ liệu mẫu của chúng tôi vào bảng này:
Chúng tôi đã thiết lập một số ô để người dùng nhập nội dung họ muốn tìm kiếm vào các ô từ E2 đến G2. Do đó, chúng tôi cần một công thức tính tổng số táo được hái trong tháng Hai. Công thức của chúng tôi trông giống như sau:
= AVERAGEIFS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS với logic loại OR
Cho đến thời điểm này, các ví dụ mà chúng tôi đã sử dụng đều là so sánh dựa trên AND, nơi chúng tôi đang tìm kiếm các hàng đáp ứng tất cả các tiêu chí của chúng tôi. Bây giờ, chúng tôi sẽ xem xét trường hợp khi bạn muốn tìm kiếm khả năng một hàng đáp ứng một tiêu chí này hoặc tiêu chí khác.
Hãy xem danh sách bán hàng này:
Chúng tôi muốn cộng doanh số bán hàng trung bình cho cả Adam và Bob. Đầu tiên, thảo luận nhanh về việc lấy số trung bình. Nếu bạn có một số lượng không đồng đều, chẳng hạn như có 3 mục nhập cho Adam và 2 mục nhập cho Bob, bạn không thể chỉ đơn giản lấy giá trị trung bình của doanh số bán hàng của mỗi người. Điều này được gọi là lấy giá trị trung bình của các giá trị trung bình, và cuối cùng bạn sẽ đưa ra một trọng số không công bằng cho mặt hàng có ít mục nhập. Nếu trường hợp này xảy ra với dữ liệu của bạn, bạn cần tính giá trị trung bình theo cách "thủ công": lấy tổng của tất cả các mục chia cho số lượng các mục của bạn. Để xem lại cách thực hiện, bạn có thể xem các bài viết tại đây:
Bây giờ, nếu số lượng mục nhập giống nhau, chẳng hạn như trong bảng của chúng tôi, thì bạn có một số tùy chọn có thể thực hiện. Đơn giản nhất là cộng hai AVERAGEIFS với nhau, như vậy, rồi chia cho 2 (số mục trong danh sách của chúng tôi)
= (AVERAGEIFS (B2: B7, A2: A7, "Adam") + AVERAGEIFS (B2: B7, A2: A7, "Bob")) / 2
Ở đây, chúng tôi đã để máy tính tính điểm cá nhân của chúng tôi và sau đó chúng tôi cộng chúng lại với nhau.
Tùy chọn tiếp theo của chúng tôi phù hợp khi bạn có nhiều phạm vi tiêu chí hơn, như vậy bạn không muốn phải viết lại toàn bộ công thức nhiều lần. Trong công thức trước, chúng tôi đã yêu cầu máy tính thêm hai AVERAGEIFS khác nhau với nhau theo cách thủ công. Tuy nhiên, bạn cũng có thể làm điều này bằng cách viết các tiêu chí của bạn bên trong một mảng, như sau:
= AVERAGE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"}))
Nhìn vào cách mảng được xây dựng bên trong dấu ngoặc nhọn. Khi máy tính đánh giá công thức này, nó sẽ biết rằng chúng ta muốn tính một hàm AVERAGEIFS cho mỗi mục trong mảng của chúng ta, do đó tạo ra một mảng số. Sau đó, hàm AVERAGE bên ngoài sẽ lấy mảng số đó và biến nó thành một số duy nhất. Bước qua đánh giá công thức, nó sẽ giống như sau:
= AVERAGE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"})) = AVERAGE (13701, 21735) = 17718
Chúng tôi nhận được cùng một kết quả, nhưng chúng tôi có thể viết ra công thức ngắn gọn hơn một chút.
Xử lý khoảng trống
Đôi khi tập dữ liệu của bạn sẽ có các ô trống mà bạn cần tìm hoặc tránh. Việc thiết lập các tiêu chí cho những tiêu chí này có thể hơi phức tạp, vì vậy hãy xem một ví dụ khác.
Lưu ý rằng ô A3 thực sự trống, trong khi ô A5 có công thức trả về một chuỗi có độ dài bằng 0 là “”. Nếu chúng ta muốn tìm tổng số trung bình của thực sự ô trống, chúng tôi sẽ sử dụng tiêu chí là “=” và công thức của chúng tôi sẽ giống như sau:
= AVERAGEIFS (B2: B7, A2: A7, "=")
Mặt khác, nếu chúng tôi muốn lấy giá trị trung bình cho tất cả các ô trông trống về mặt trực quan, chúng tôi sẽ thay đổi tiêu chí thành “” và công thức trông giống như
= AVERAGEIFS (B2: B7, A2: A7, "")
Hãy lật lại: điều gì sẽ xảy ra nếu bạn muốn tìm giá trị trung bình của các ô không trống? Rất tiếc, thiết kế hiện tại sẽ không cho phép bạn tránh được chuỗi có độ dài bằng 0. Bạn có thể sử dụng tiêu chí là “”, nhưng như bạn có thể thấy trong ví dụ, tiêu chí đó vẫn bao gồm giá trị từ hàng 5.
= AVERAGEIFS (B2: B7, A2: A7, "")
Nếu bạn không cần đếm các ô chứa chuỗi độ dài bằng 0, bạn sẽ muốn xem xét sử dụng hàm LEN bên trong hàm SUMPRODUCT
AVERAGEIF trong Google Trang tính
Hàm AVERAGEIF hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
