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:

=

( 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:

=

( 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 theo tiêu chí trong ô F6, tức là lớn 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 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:

=

( 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.

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

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.

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

  6. Các Tiêu chí khác nhau bên trong công thức được chấp nhận, chẳng hạn như bằng (=), nhỏ hơn (<), nhỏ hơn hoặc bằng (⇐), lớn hơn hoặc bằng (> =), lớn hơn (>) & không bằng (<>).

Bằng cách này, chúng tôi đã học Cách Tìm Trung bình Dựa trên Nhiều Tiêu chí trong Microsoft Excel 2010. Bạn có thể sử dụng các hàm này trong Excel 2016, 2013 và 2010. Tìm thêm các bài viết về Công thức toán học với các tiêu chí khác nhau. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến ​​của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected]

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

Bài viết phổ biến: