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 liên quan đến các ô không trống hoặc không trống trong Excel và Google Trang tính.
Tổng nếu không trống
Đầu tiên, chúng tôi sẽ trình bày cách tính tổng dữ liệu liên quan đến các ô không trống.
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 với các tên không trống.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Để tính tổng các hàng có ô không trống, chúng tôi loại trừ Điểm với sự mất tích Người chơi những cái tên. Chúng tôi sử dụng tiêu chí “không bằng khoảng trống” (“”) bên trong Hàm SUMIFS.
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:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Để 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 một cột trợ giúp bằng cách sử dụng Hàm LEN và TRIM để xác định Người chơi với những cái tên.
Hàm TRIM loại bỏ các khoảng trắng thừa ở đầu và cuối giá trị của mỗi ô và sau đó, Hàm LEN sẽ đếm số ký tự còn lại. Nếu kết quả của Hàm LEN là 0, thì Người chơi tên phải được để trống hoặc chỉ có dấu cách:
| 1 | = LEN (TRIM (B3)) |
Chúng tôi áp dụng Hàm SUMIFS cho cột trợ giúp (Tính tổng nếu lớn hơn 0) và bây giờ nó tính tổng chính xác.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
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 bắt buộc phải xử lý bất kỳ ô nào chỉ chứa khoảng trắng giống như khi chúng trống, nhưng việc sử dụng cột trợ giúp là không thích hợp, thì chúng ta có thể sử dụng Hàm SUMPRODUCT kết hợp với Hàm LEN và TRIM để tổng hợp dữ liệu liên quan đến các ô chứa không trống Người chơi tên:
| 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 các phép tính phức tạp "tính 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ó nhiều hơn 0 ký tự được đổi thành TRUE:
| 1 | = SUMPRODUCT (- ({TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}), {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 ({1; 1; 0; 1; 0; 1; 0}, {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 không trống hoặc không chỉ được tạo từ dấu cách:
| 1 | = SUMPRODUCT ({25; 10; 0; 5; 0; 17; 0) |
Cuối cùng, các số trong mảng được cộng lại với nhau
| 1 | =57 |
Bạn có thể tìm thêm thông tin 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 không 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.
