Số trung bình nếu không để trống trong Excel
Trong bài viết này, chúng ta sẽ học cách lấy Số trung bình nếu không để trống 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í chỉ nhận được mức trung bình khi các ô không trống. 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 ta được cung cấp dữ liệu và chúng ta cần tìm AVERAGE của các số bỏ qua các ô trống trong Excel
Công thức chung:
= AVERAGEIFS ( Average_range , range, "<>")
Average_range: phạm vi giá trị mà trung bình cần được đánh giá phạm vi: phạm vi áp dụng tiêu chí.
-
“<>”: tiêu chí, bỏ qua các ô trống.
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 (A1: D15) của các đơn đặt hàng đã nhận từ các khu vực khác nhau với số lượng và giá trị nhất định. Chúng ta cần tìm AVERAGE của giá mà số lượng không trống.
Điều kiện hoặc tiêu chí trên cần phải phù hợp. Hàm AVERAGEIFS có thể giúp chúng tôi trích xuất giá trị trung bình không có ô trống.
Sử dụng công thức:
= AVERAGEIFS ( Price , quantity , "<>" )
Số lượng: dải ô đã đặt tên được sử dụng cho mảng C2: C15 Giá: dải ô đã đặt tên được sử dụng cho mảng D2: D15 “<>”: tiêu chí Giải thích:
-
Hàm AVERAGEIFS lấy giá trị trung bình của phạm vi mà tất cả các điều kiện đều thỏa mãn.
-
Ở đây, điều kiện áp dụng cho phạm vi số lượng, tức là giá trung bình trong đó số lượng không trống.
-
“<>” khớp với các giá trị mà các ô không trống.
Ở đây Các phạm vi được đặt tên được đưa ra dưới dạng (C2: C15) số lượng và (D2: D15) Giá.
Công thức được sử dụng trong ô sẽ được hiển thị giống như ảnh chụp nhanh ở trên. Nhấn Enter để nhận Giá trung bình nếu số lượng không trống.
Như bạn có thể thấy trong ảnh chụp nhanh ở trên, AVERAGE của Giá trong đó số lượng không trống sẽ là 158,26. Bạn có thể kiểm tra kết quả bằng cách áp dụng bộ lọc trên dữ liệu và lọc các giá trị số lượng không trống như hình dưới đây.
Kết quả Trung bình sẽ được nhìn thấy ở phía dưới bên phải của trang tính excel cùng với số và Tổng.
Ví dụ 2:
Ở đây chúng tôi đã cung cấp dữ liệu (A1: D15) của các đơn đặt hàng đã nhận từ các khu vực khác nhau với số lượng và giá trị nhất định. Chúng tôi cần tìm TRUNG BÌNH của giá trong đó số lượng không trống và khu vực là Đông.
Điều này sẽ bao gồm nhiều tiêu chí trong công thức. 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 ( Price , quantity , "<>" , Region , "East" ) )
Vùng: dải ô đã đặt tên được sử dụng cho mảng B2: B15 Số lượng: dải ô đã đặt tên được sử dụng cho mảng C2: C15 Giá: dải ô đã đặt tên được sử dụng cho mảng D2: D15 “<>”: tiêu chí Giải thích:
-
Hàm AVERAGEIFS lấy giá trị trung bình của phạm vi mà tất cả các điều kiện đều thỏa mãn.
-
Ở đây, điều kiện áp dụng cho phạm vi số lượng, tức là giá trung bình trong đó số lượng không trống.
-
“<>” khớp với các giá trị mà các ô không trống.
-
Vùng là phạm vi tiêu chí thứ hai trong đó điều kiện là Mảng vùng phải khớp với giá trị “Đông”
Ở đây Các phạm vi được đặt tên được đưa ra như (C2: C15) số lượng, (D2: D15) Giá &
(B2: B15) Vùng.
Công thức được sử dụng trong ô sẽ được hiển thị giống như ảnh chụp nhanh ở trên. Nhấn Enter để nhận Giá trung bình nếu số lượng không trống và Khu vực là Đông.
Như bạn có thể thấy trong ảnh chụp nhanh ở trên, AVERAGE của Giá trong đó số lượng không trống sẽ là 171,35. Bạn có thể kiểm tra kết quả bằng cách áp dụng bộ lọc trên dữ liệu và lọc các giá trị số lượng không trống như hình dưới đây.
Kết quả Trung bình sẽ được nhìn thấy ở phía dưới bên phải của trang tính excel cùng với số và Tổng. Điều này chứng tỏ công thức hoạt động tốt.
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ú:
-
Hàm trả về lỗi #VALUE! lỗi nếu đối số của hàm không phải là số.
-
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.
-
Toán tử tiêu chí Công thứ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 sử dụng hàm AVERAGEIFS trong Excel để nhận kết quả trả về từ các ô không trống. 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ề Lập công thức toán học với điều kiện tại đây. Nếu bạn có bất kỳ vấn đề nào liên quan đến bài viết này hoặc bất kỳ câu hỏi nào chưa được giải đáp, vui lòng bình luận trong khung bình luận bên dưới. 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]