Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tính giá trị trung bình của một tập hợp số trong khi bỏ qua các giá trị hoặc danh mục trống trong Excel và Google Trang tính.
Bỏ qua các giá trị trống với chức năng AVERAGE
Hàm AVERAGE tự động bỏ qua bất kỳ ô nào trống hoặc chứa văn bản.
Ví dụ này sử dụng Hàm AVERAGE để tính toán Điểm trung bình trong khi bỏ qua giá trị văn bản của Ghi bàn của Đội B và giá trị trống của Ghi bàn của Đội C:
| 1 | = AVERAGE (C3: C7) |
Hàm AVERAGE chỉ sử dụng các giá trị số được liên kết với Đội A, D và E
Xử lý các giá trị văn bản là 0 với chức năng AVERAGEA
Để coi các giá trị văn bản là 0 trong phép tính, có thể sử dụng Hàm AVERAGEA. Hàm này sẽ bỏ qua các giá trị ô trống.
Ví dụ này sử dụng Hàm AVERAGEA để tính toán Điểm trung bình trong khi bỏ qua khoảng trống Ghi bàn giá trị của Đội C và xử lý văn bản Ghi bàn giá trị của Đội B như là 0:
| 1 | = AVERAGEA (C3: C7) |
Ví dụ này tính giá trị trung bình của các giá trị 4, 0, 5 và 3
Bỏ qua giá trị danh mục trống bằng AVERAGEIFS
Ví dụ này sử dụng Hàm AVERAGEIFS để tính toán Điểm trung bình cho các nhóm có tên không trống và bỏ qua Điểm đó là các giá trị văn bản
| 1 | = AVERAGEIFS (C3: C7, B3: B7, "") |
Bỏ qua giá trị danh mục trống - Mảng
Nếu bạn không có quyền truy cập vào hàm AVERAGEIF, bạn có thể sử dụng hàm AVERAGE và IF lồng nhau.
Ví dụ này tính toán Điểm trung bình cho các nhóm có tên không trống và bỏ qua bất kỳ Điểm đó là một giá trị văn bản
| 1 | = AVERAGE (IF (B3: B7 "", C3: C7)) |
Công thức này sử dụng Hàm IF để đánh giá xem tên của mỗi Đội không trống, và chỉ sử dụng Điểm từ Đội với các tên không trống trong Hàm AVERAGE. Điểm của 100 bị bỏ qua vì Đội tên trống. Điểm của 'Không có dữ liệu' bị bỏ qua vì nó không phải là một giá trị số.
Trong các phiên bản Excel từ 2022 trở về trước, công thức này được yêu cầu nhập dưới dạng công thức mảng bằng cách nhấn CTRL + SHIFT + ENTER. Điều này không bắt buộc trong các phiên bản sau
Ví dụ này có thể được tính bằng cách sử dụng Hàm AVERAGEIFS như được hiển thị bên dưới
Bỏ qua giá trị danh mục trống và coi văn bản là 0
Để xử lý giá trị văn bản Điểm là 0, trong khi vẫn bỏ qua Đội với các tên trống, chúng ta có thể sử dụng Hàm AVERAGEA và IF lồng nhau:
| 1 | = AVERAGEA (IF (B3: B7 "", C3: C7)) |
Công thức này sử dụng Hàm IF để đánh giá xem tên của từng Đội không trống, và chỉ sử dụng Điểm từ Đội với các tên không trống trong Hàm AVERAGEA. Điểm của 100 bị bỏ qua vì Đội tên trống. Điểm của 'Không có dữ liệu' được coi là 0 bởi Hàm AVERAGEA và do đó mức trung bình là 3.0 được tính.
Trong các phiên bản Excel từ 2022 trở về trước, công thức này được yêu cầu nhập dưới dạng công thức mảng bằng cách nhấn CTRL + SHIFT + ENTER. Điều này không bắt buộc trong các phiên bản sau
Trung bình nếu không trống trong Google Trang tính
Hầu hết các ví dụ về công thức này hoạt động theo cùng một cách trong Google Trang tính cũng như trong Excel, nhưng với một số ngoại lệ:
Bất cứ khi nào một Hàm IF được lồng trong một hàm khác và nó tham chiếu đến một phạm vi ô, Google Trang tính cần phải coi công thức như một công thức mảng bằng cách sử dụng Hàm ARRAYFORMULA:
| 1 | = ArrayFormula (AVERAGE (IF (B3: B7 "", C3: C7))) |
Rất tiếc, Hàm AVERAGEA hoạt động trong Google Trang tính khác với trong Excel và vì vậy không thể sử dụng ví dụ về = AVERAGEA (IF (B3: B7 ””, C3: C7)). Điều này là do khi Hàm IF tìm thấy giá trị trống trong phạm vi B3: B7, nó tạo ra giá trị rỗng, nhưng sau đó giá trị này được tính là 0 bởi Hàm AVERAGEA của Google Trang tính, thay vì bỏ qua giá trị này như trường hợp Excel.
