Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách sử dụng Hàm SUMIFS để tính tổng dữ liệu tương ứng với các ô trống hoặc ô trống trong Excel và Google Trang tính.
Tổng nếu trống
Đầu tiên, chúng tôi sẽ trình bày cách tính tổng các hàng với các ô trống.
Hàm SUMIFS tổng hợp dữ liệu đáp ứng các tiêu chí nhất định.
Chúng ta có thể sử dụng Hàm SUMIFS để tổng hợp tất cả Điểm vì Người chơi không có tên trong ví dụ dưới đây.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Chúng tôi sử dụng dấu ngoặc kép (“”) để biểu thị một ô trống trong Excel. Ví dụ của chúng tôi bỏ qua Người chơi A, B, C và D và tính tổng điểm chưa biết Người chơi.
Xử lý khoảng trắng là ô trống - Với cột trợ giúp
Bạn cần phải cẩn thận khi tương tác với các ô trống trong Excel. Các ô có thể trống đối với bạn, nhưng Excel sẽ không coi chúng là ô trống. Điều này có thể xảy ra nếu ô chứa khoảng trắng, dấu ngắt dòng hoặc các ký tự ẩn khác. Đây là sự cố thường gặp khi nhập dữ liệu vào Excel từ các nguồn khác.
Nếu chúng ta cần xử lý bất kỳ ô nào chỉ chứa khoảng trắng giống như khi chúng trống, thì công thức trong ví dụ trước sẽ không hoạt động. Lưu ý cách Công thức SUMIFS không coi ô B9 bên dưới (”“) là ô trống:
Để xử lý một ô chỉ chứa khoảng trắng như thể nó là một ô trống, chúng ta có thể thêm cột trợ giúp bằng Hàm TRIM để loại bỏ các khoảng trắng thừa khỏi giá trị của mỗi ô:
| 1 | = TRIM (B3) |
Chúng tôi áp dụng Hàm SUMIFS cho cột trợ giúp và bây giờ nó sẽ tính toán tổng một cách chính xác.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Cột trợ giúp dễ tạo và dễ đọc, nhưng bạn có thể muốn có một công thức duy nhất để hoàn thành nhiệm vụ. Điều này được đề cập trong phần tiếp theo.
Xử lý khoảng trắng là ô trống - Không có cột trợ giúp
Nếu một cột trợ giúp không phù hợp với nhu cầu của bạn, thì bạn có thể sử dụng Hàm SUMPRODUCT kết hợp với Hàm LEN và TRIM để tính tổng các hàng trống.
| 1 | = SUMPRODUCT (- (LEN (TRIM (B3: B9)) = 0), D3: D9) |
Trong ví dụ này, chúng tôi sử dụng Hàm SUMPRODUCT để thực hiện một phép tính phức tạp "tổng nếu". Hãy xem qua công thức.
Đây là công thức cuối cùng của chúng tôi:
| 1 | = SUMPRODUCT (- (LEN (TRIM (B3: B9)) = 0), D3: D9) |
Đầu tiên, Hàm SUMPRODUCT liệt kê mảng giá trị từ hai phạm vi ô:
| 1 | = SUMPRODUCT (- (LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
Sau đó, Hàm TRIM loại bỏ các khoảng trắng đầu và cuối khỏi Người chơi tên:
| 1 | = SUMPRODUCT (- (LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Hàm LEN tính toán độ dài của phần được cắt tỉa Người chơi tên:
| 1 | = SUMPRODUCT (- ({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
Với kiểm tra logic (= 0), bất kỳ phần nào được cắt Người chơi tên có 0 ký tự được đổi thành TRUE:
| 1 | = SUMPRODUCT (- ({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
Tiếp theo, dấu gạch ngang kép (-) chuyển đổi các giá trị TRUE và FALSE thành 1s và 0s:
| 1 | = SUMPRODUCT ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
Sau đó, Hàm SUMPRODUCT nhân từng cặp mục nhập trong mảng để tạo ra một mảng Điểm chỉ dành cho Người chơi tên trống hoặc chỉ được tạo từ dấu cách:
| 1 | = SUMPRODUCT ({0; 0; 15; 0; 8; 0; 50) |
Cuối cùng, các số trong mảng được cộng lại với nhau:
| 1 | =73 |
Bạn có thể tìm thêm chi tiết về cách sử dụng câu lệnh Boolean và lệnh “-” trong Hàm SUMPRODUCT tại đây.
Tổng nếu trống trong Google Trang tính
Các công thức này hoạt động giống hệt nhau trong Google Trang tính cũng như trong Excel.
