Trong bài viết này, chúng ta sẽ tìm hiểu cách lấy Giá trị trung bình với nhiều tiêu chí trong Excel.

Vấn đề?

Đối với Phiên bản, Chúng tôi có một danh sách lớn dữ liệu và chúng tôi cần tìm Trung bình của giá hoặc số tiền được đưa ra theo một số tiêu chí. Tiêu chí có thể được áp dụng cho bất kỳ cột nào của dữ liệu.

Làm thế nào để giải quyết vấn đề.

Bây giờ chúng ta sẽ tạo một công thức ngoài hàm. Ở đây chúng tôi được cung cấp dữ liệu và chúng tôi cần tìm AVERAGE của các số có một số tiêu chí

Công thức chung:

= AVERAGEIFS ( Average_range , range1, criteria1, [ range2, criteria2 ], [ range3, criteria3 ], … )

Average_range: phạm vi giá trị trong đó trung bình cần được đánh giá phạm vi1: phạm vi đầu tiên nơi tiêu chí1 được áp dụng.

  • tiêu chí1: tiêu chí đầu tiên được áp dụng trên phạm vi1.

  • range2: phạm vi thứ hai nơi tiêu chí1 được áp dụng.

  • tiêu chí2: tiêu chí thứ hai được áp dụng trên phạm vi2.

  • phạm vi3: phạm vi thứ ba nơi tiêu chí1 được áp dụng.

  • tiêu chí3: tiêu chí thứ ba được áp dụng trên phạm vi3.

Lưu ý: không cung cấp ngày trực tiếp cho chức năng. Sử dụng hàm DATE hoặc sử dụng tham chiếu ô cho đối số ngày trong excel vì Excel chỉ đọc ngày với thứ tự chính xác.

Ví dụ:

Tất cả những điều này có thể khó hiểu. Vì vậy, hãy kiểm tra công thức này bằng cách chạy nó trên ví dụ được hiển thị bên dưới.

Ở đây chúng tôi đã cung cấp dữ liệu từ A1: D51. Chúng tôi cần tìm AVERAGE của Số lượng nhận được theo các tiêu chí khác nhau.

Điều kiện như sau.

  1. Ngày phải trước ngày 31/01/2019.

  2. Thành phố “Boston”

  3. Sản phẩm được định nghĩa là “Cà rốt”

  4. Số lượng phải lớn hơn 50 (> 50).

image

4 điều kiện hoặc tiêu chí đã nêu ở trên cần phải phù hợp với nhau. Hàm AVERAGEIFS có thể giúp chúng tôi trích xuất giá trị trung bình có các tiêu chí khác nhau.

Sử dụng công thức:

= AVERAGEIFS ( Quantity , order_date , "<=" & F4 , city , "Boston" , product , F5 , Quantity , F6 )

Số lượng: dải ô đã đặt tên được sử dụng cho mảng D2: D50 Ngày thứ tự: dải ô được đặt tên được sử dụng cho mảng A2: A50 Thành phố: dải ô đã đặt tên được sử dụng cho mảng B2: B50 Sản phẩm: dải ô đã đặt tên được sử dụng cho mảng C2: C50 F4: tham chiếu ô được sử dụng cho tiêu chí 1 F5: tham chiếu ô được sử dụng cho tiêu chí 2 F6: tham chiếu ô được sử dụng cho tiêu chí 3 Giải thích:

  1. order_date, “⇐” & F4 * tiêu chí đầu tiên đề xuất ngày trước ngày 31 tháng 1 năm 2019 ở F4.

  2. thành phố, “Boston” tiêu chí thứ hai trong đó thành phố khớp với “Boston”

  3. product, F5 trong đó sản phẩm khớp với Carrot trong ô F5. Số lượng, phải dưới tiêu chí trong ô F6, tức là lớn hơn 50. Ở đây A2 được cung cấp dưới dạng tham chiếu ô & Phạm vi được đặt tên được cung cấp dưới dạng rng (D2: D51) và order_date (A2: A51).

image

Như bạn có thể thấy trong ảnh chụp nhanh ở trên, AVERAGE của số lượng trong tháng 1 là 54,00. Vì chỉ có một giá trị thỏa mãn tất cả các điều kiện.

Ví dụ 2:

Ở đây chúng tôi đã cung cấp dữ liệu từ A1: D51. Chúng tôi cần tìm AVERAGE của Số lượng nhận được theo các tiêu chí khác nhau.

Điều kiện như sau.

  1. Ngày phải trước 27/02/2019.

  2. Thành phố “New York”

  3. Sản phẩm được định nghĩa là “Chocolate Chip”.

  4. Số lượng phải nhỏ hơn 50 (<50).

image

4 điều kiện hoặc tiêu chí đã nêu ở trên cần phải phù hợp với nhau. Hàm AVERAGEIFS có thể giúp chúng tôi trích xuất giá trị trung bình có các tiêu chí khác nhau.

Sử dụng công thức:

= AVERAGEIFS ( Quantity , order_date , "<=" & F4 , city , "New York" , product , F5 , Quantity , F6 )

Số lượng: dải ô đã đặt tên được sử dụng cho mảng D2: D50 Ngày thứ tự: dải ô được đặt tên được sử dụng cho mảng A2: A50 Thành phố: dải ô đã đặt tên được sử dụng cho mảng B2: B50 Sản phẩm: dải ô đã đặt tên được sử dụng cho mảng C2: C50 F4: tham chiếu ô được sử dụng cho tiêu chí 1 F5: tham chiếu ô được sử dụng cho tiêu chí 2 F6: tham chiếu ô được sử dụng cho tiêu chí 3 Giải thích:

  1. order_date, “⇐” & F4 * tiêu chí đầu tiên đề xuất ngày trước 27/02/2019 trong ô F4.

  2. thành phố, “Boston” tiêu chí thứ hai trong đó thành phố khớp với “Boston”

  3. product, F5 trong đó sản phẩm khớp với Carrot trong ô F5. Số lượng, phải theo tiêu chí trong ô F6, tức là nhỏ hơn 50.

Ở đây A2 được đưa ra dưới dạng tham chiếu ô & Phạm vi được đặt tên được cho là rng (D2: D51) và order_date (A2: A51).

image

Như bạn có thể thấy trong ảnh chụp nhanh ở trên, AVERAGE của số lượng trong tháng Giêng là 33,33 …​ Vì chỉ có một giá trị thỏa mãn tất cả các điều kiện.

Dưới đây là một số quan sát về cách sử dụng công thức dưới đây.

Ghi chú:

  1. Hàm trả về lỗi #VALUE! lỗi nếu đối số của hàm không phải là số.

  2. Hàm trả về lỗi #VALUE! lỗi nếu không có giá trị nào phù hợp với tiêu chí đã cho.

  3. Công thức chấp nhận các toán tử như =, <, ⇐,> =, ⇐ & <>.

  4. Không cung cấp đối số ngày trực tiếp cho hàm. Sử dụng hàm DATE hoặc sử dụng tham chiếu ô cho đối số ngày trong excel vì Excel chỉ đọc ngày với thứ tự chính xác.

Hy vọng bài viết này về cách Tính trung bình với nhiều tiêu chí trong Excel là giải thích. Tìm thêm các bài viết về hàm SUMPRODUCT tại đây. Vui lòng chia sẻ câu hỏi của bạn bên dưới trong hộp bình luận. Chúng tôi sẽ hỗ trợ bạn.

Các bài viết liên quan

liên kết: / excel-mảng-công thức-tính-trung bình-chênh lệch-giữa-danh-giá-và-giá-bán [Sự khác biệt trung bình giữa các danh sách]

Bài viết phổ biến

liên kết: / logic-công thức-nếu-hàm-với-ký tự đại diện [Nếu có ký tự đại diện]