Excel trung bình bỏ qua 0

=AVERAGEIF(B2:B14, "<>0")

Công thức cụ thể này tính toán giá trị trung bình trong phạm vi B2. B14 và bỏ qua các ô bằng 0 hoặc trống

Ghi chú. Excel bỏ qua các ô trống theo mặc định khi tính trung bình

Vì vậy, chúng ta chỉ cần xác định trong công thức rằng các ô cũng không được bằng ( “<>0”) bằng 0

Ví dụ sau đây cho thấy cách sử dụng công thức này trong thực tế

Thí dụ. Tính trung bình và bỏ qua các ô không và ô trống trong Excel

Giả sử chúng ta có tập dữ liệu sau trong Excel cho biết tổng doanh số bán hàng của các nhân viên khác nhau tại một công ty

Nếu chúng tôi chỉ đơn giản sử dụng công thức AVERAGE(), chúng tôi sẽ tìm thấy doanh số bán hàng trung bình cho tất cả nhân viên có giá trị không trống

Doanh số trung bình trên mỗi nhân viên có giá trị không trống là 3

Tuy nhiên, giả sử chúng ta chỉ muốn tính trung bình cho những nhân viên có giá trị bán hàng không trống và không bằng 0

Chúng ta có thể gõ công thức sau vào ô D2

=AVERAGEIF(B2:B14, "<>0")

Ảnh chụp màn hình sau đây cho thấy cách sử dụng công thức này trong thực tế

Doanh số bán hàng trung bình của những nhân viên có doanh số bán hàng lớn hơn 0 là 4. 125

Công thức này tính trung bình bằng cách chỉ sử dụng các giá trị không trống và không bằng 0

Chúng tôi có thể xác nhận rằng điều này là chính xác bằng cách tính toán thủ công giá trị trung bình của tất cả các giá trị không trống hoặc bằng 0

Khi muốn tính trung bình trong Microsoft Excel mà không có ô chứa số 0, chúng ta có thể áp dụng các hàm AVERAGEIF, AVERAGE và IF. Bộ dữ liệu hôm nay của chúng tôi là về các loại Sản phẩm khác nhau đã được đặt hàng trong các tháng khác nhau. Trong bài viết này, chúng ta sẽ tìm hiểu hai cách nhanh chóng và phù hợp để tính trung bình cộng trong Excel trừ 0 bằng cách sử dụng các hàm AVERAGEIF, AVERAGE và IF

Mục lục

Tải sách bài tập thực hành

Tải xuống sách bài tập thực hành này để thực hành trong khi bạn đang đọc bài viết này

Trung bình loại trừ 0. xlsx

2 cách phù hợp để tính trung bình cộng trong Excel không bao gồm 0

Giả sử, chúng tôi có một tập dữ liệu chứa thông tin về các loại Sản phẩm khác nhau và số lượng của chúng đã được đặt hàng trong các Tháng khác nhau được đưa ra trong các cột C, D và B tương ứng. Chúng tôi sẽ tính trung bình Số lượng của các sản phẩm được đặt hàng đó, không bao gồm các đơn đặt hàng bằng 0 trong vài tháng trong Excel. Dưới đây là tổng quan về tập dữ liệu cho nhiệm vụ hôm nay

1. Áp dụng hàm AVERAGEIF để tính giá trị trung bình trong Excel không bao gồm 0

Chúng ta có thể dễ dàng tính trung bình cộng trừ 0 trong Excel bằng cách áp dụng hàm AVERAGEIF. Đây là hàm đơn giản nhất và tiết kiệm thời gian nhất để tính trung bình không bao gồm 0 trong Excel. Hãy làm theo hướng dẫn dưới đây

bước

• Trước hết, hợp nhất các ô E5 thành E15. Sau đó chọn các ô đã hợp nhất

• Ngoài ra, hãy nhập hàm AVERAGEIF vào Thanh công thức. Hàm AVERAGEIF trong Thanh công thức là,

=AVERAGEIF(D5:D15, "<>0")

• đâu D5. D15 là phạm vi ô của hàm
• <>0 = tiêu chí có nghĩa là giá trị của ô lớn hơn 0

• Do đó, chỉ cần nhấn Enter trên bàn phím của bạn và bạn sẽ nhận được giá trị trung bình trừ 0 là 81, đây là kết quả trả về của hàm AVERAGEIF đã được cung cấp bên dưới ảnh chụp màn hình

• Sau đó, chúng tôi đếm các ô chứa giá trị 0, giá trị trung bình là 66. 27. Từ ảnh chụp màn hình bên dưới, bạn sẽ có thể hiểu được sự khác biệt giữa giá trị trung bình của việc bao gồm và loại trừ số 0

Đọc thêm. Cách tính giá trị trung bình trong Excel (Bao gồm tất cả các tiêu chí)

bài đọc tương tự

• [Đã sửa. ] Công thức AVERAGE không hoạt động trong Excel (6 giải pháp)
• Cách lấy thời gian trung bình trong Excel (3 ví dụ)
• Tạo đường trung bình động trong biểu đồ Excel (4 phương pháp)
• Cách tính TRUNG BÌNH VLOOKUP trong Excel (6 cách nhanh chóng)
• Tính trung bình xếp hạng 5 sao trong Excel (3 cách dễ dàng)

2. Chèn các hàm AVERAGE và IF để tính trung bình trong Excel không bao gồm 0

Trong phương pháp này, chúng tôi sẽ tính trung bình của các sản phẩm được đặt hàng trong các tháng khác nhau, ngoại trừ thứ tự không trong một số tháng trong Excel bằng cách áp dụng các hàm AVERAGE và IF. Các chức năng này có thể được áp dụng khi các ô trống hoặc cũng chứa văn bản. Hãy làm theo các bước dưới đây để tìm hiểu

bước

• Chọn ô E5 trước để tính trung bình trừ 0

• Do đó, hãy viết các hàm AVERAGE và IF trong Thanh công thức. Các chức năng là,

=AVERAGE(IF(D5:D15<>0, D5:D15))

• đâu D5. D15<>0 = logic_test có nghĩa là ô chứa giá trị lớn hơn 0
• D5. D15 = value_if_true có nghĩa là giá trị của các ô

• Sau đó, nhấn Enter trên bàn phím của bạn và bạn sẽ nhận được giá trị trung bình trừ 0 là 81, đây là kết quả trả về của các hàm AVERAGE và IF đã được cung cấp bên dưới ảnh chụp màn hình

• Hơn nữa, chúng tôi sẽ tính giá trị trung bình của các ô bao gồm giá trị 0 và giá trị trung bình bao gồm 0 trở thành 27. Từ ảnh chụp màn hình bên dưới, bạn sẽ có thể hiểu giá trị trung bình bao gồm và loại trừ số không

Đọc thêm. Cách loại trừ một ô trong Công thức AVERAGE của Excel (4 phương pháp)

Những điều cần ghi nhớ

👉 Hàm AVERAGEIF trả về #DIV/0. lỗi khi giá trị của tất cả các ô không phải là số

👉 Nếu bạn đang dùng Excel 2003 thì có thể áp dụng công thức như sau

=SUM(range) / COUNTIF(range, “<>0”)

Phần kết luận

Tôi hy vọng tất cả các phương pháp phù hợp được đề cập ở trên để tính trung bình không bao gồm số 0 giờ đây sẽ kích thích bạn áp dụng chúng trong bảng tính Excel của mình với năng suất cao hơn. Bạn được hoan nghênh nhất để bình luận nếu bạn có bất kỳ câu hỏi hoặc thắc mắc nào