Bạn có thể loại bỏ giá trị ẩn hoặc được lọc ra trong tổng cộng trong Google Sheets, nhưng không phải bằng hàm SUM thông thường. Đối với mục đích này, bạn nên sử dụng hàm SUBTOTAL với mã chức năng 9 (loại bỏ/nhắc đi hàng được lọc) hoặc 109 (loại bỏ/nhắc đi hàng được lọc và ẩn). Vì vậy, bài viết này thực sự là về cách sử dụng hàm SUBTOTAL trong Google Sheets để tổng cộng các hàng hiển thị.
Google Docs Spreadsheet có một số hàm giống Excel để đáp ứng nhu cầu nhập dữ liệu của người dùng. SUBTOTAL là một trong số đó. Các hàm như QUERY, REGEXMATCH và IMPORTRANGE giúp Google Sheets trở thành một giải pháp bảng tính hoàn hảo mà những người khác ngưỡng mộ.
Lưu ý rằng bạn có thể tham khảo hướng dẫn về các hàm Google Sheets của tôi để tìm hiểu tất cả các hàm phổ biến của Google Sheets kèm theo ví dụ.
Công thức để Loại Bỏ Giá Trị Ẩn Hoặc Được Lọc Ra Trong Tổng Cộng
Chúng tôi sẽ bắt đầu với một ví dụ cơ bản sử dụng dữ liệu nguồn và công thức trong cùng một bảng. Tiếp theo, chúng tôi sẽ xem cách sử dụng dữ liệu nguồn trong một bảng và công thức trong một bảng khác, nhưng trong cùng một workbook. Cuối cùng, chúng tôi sẽ xem cách loại bỏ giá trị ẩn hoặc được lọc ra khi dữ liệu nguồn nằm trong một tệp tin và công thức nằm trong một tệp tin khác.
Dữ Liệu Nguồn và Công Thức SUBTOTAL Trong Cùng Một Bảng
Tôi có các giá trị muốn tổng cộng trong B2:B5. Hãy xem cách loại bỏ các giá trị trong các hàng ẩn hoặc được lọc ra trong tổng cộng trong ô B7.
Ghi chú:
- Được lọc ra có nghĩa là lọc bản ghi bằng cách chọn Dữ liệu > Tạo bộ lọc hoặc Dữ liệu > Thêm bộ lọc.
- Các hàng ẩn có nghĩa là ẩn các bản ghi bằng cách nhấp chuột phải vào một hàng và chọn Ẩn, hoặc áp dụng nhóm cho các hàng đã chọn bằng cách sử dụng tính năng Xem > Nhóm.
Như đã nêu rõ ở trên, để tạo tổng hoặc tổng cộng mà bỏ qua các hàng bị ẩn hoặc được lọc ra, bạn nên sử dụng hàm SUBTOTAL() thay vì hàm SUM().
Bạn phải sử dụng mã chức năng tổng, tức là 9 hoặc 109 vì hàm SUBTOTAL có khả năng tổng cộng theo mười một loại tổng hợp.
Trong ví dụ dưới đây, tôi đã ẩn hàng số 3 bằng cách nhấp chuột phải vào nó và chọn Ẩn từ menu ngắn.
=SUBTOTAL(109,B2:B5)
Khi hàm SUM cộng giá trị trong hàng ẩn vào tổng, hàm SUBTOTAL bỏ qua nó.
Cùng với đó, điều tương tự sẽ xảy ra nếu bạn áp dụng hàm SUBTOTAL vào một bảng được lọc.
Nếu bạn chỉ muốn bỏ qua các hàng được lọc, hãy thay mã chức năng SUM 109 trong công thức thành 9. Nó sẽ bao gồm các hàng ẩn.
Dữ Liệu Nguồn và Công Thức SUBTOTAL Trong Hai Bảng Khác Nhau Trong Cùng Một Tệp Tin
Nếu phạm vi để tổng cộng ở trên là trong Sheet1, hãy sử dụng công thức sau:
=SUBTOTAL(9,Sheet1!B2:B5)
Bạn có thể sử dụng công thức này trong Sheet1 hoặc bất kỳ bảng nào khác trong cùng một tệp tin.
Cách Loại Bỏ Giá Trị Ẩn Hoặc Được Lọc Ra Trong Tổng Cộng Dữ Liệu IMPORTRANGE
Chúng tôi sử dụng hàm IMPORTRANGE để nhập dữ liệu từ một tệp tin Google Sheets vào tệp tin khác. Bạn có thể dễ dàng nhập dữ liệu bằng cách sử dụng hàm này.
Để làm điều này, bạn chỉ cần chỉ định hai đối số: URL của tệp tin Google Sheets mà bạn muốn nhập dữ liệu và tên bảng cùng với phạm vi.
Ví dụ, công thức sau đây trong ô A1 sẽ nhập phạm vi B2:B5 trong Sheet1:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1as2K3NyJ58WipNSm56rtE__-DD3F6_x_RAp2ElpHNJw/edit#gid=1892488864","Sheet1!B2:B5")
Ghi chú: Công thức này sẽ không hoạt động đối với bạn vì tôi đã sử dụng một URL giả cho mục đích giới thiệu. Bạn cần thay thế URL bằng URL của bảng chứa dữ liệu để nhập.
Bạn có thể bao quanh công thức này bằng hàm SUM để có tổng của các giá trị đã nhập:
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1as2K3NyJ58WipNSm56rtE__-DD3F6_x_RAp2ElpHNJw/edit#gid=1892488864","Sheet1!B2:B5"))
Làm thế nào chúng ta có thể bỏ qua các giá trị ẩn hoặc được lọc ra trong tổng cộng dữ liệu đã nhập này? Bọc nó bởi SUBTOTAL không hoạt động vì đối số SUBTOTAL phải là một phạm vi và chúng ta đang cung cấp cho nó một biểu thức.
Giải pháp tạm thời:
Trong bảng nguồn, hãy chèn công thức MAP sau vào ô C2 (C2:C5 phải trống trước đó):
=MAP(B2:B5, LAMBDA(row, SUBTOTAL(103,row)))
Nhập B2:C5 thay vì B2:B5.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1as2K3NyJ58WipNSm56rtE__-DD3F6_x_RAp2ElpHNJw/edit#gid=1892488864","Sheet1!B2:C5")
Sử dụng công thức SUMIF sau đây thay vì SUM.
=SUMIF(B1:B4,1,A1:A4)
Theo cách này, chúng tôi có thể bỏ qua các giá trị ẩn hoặc được lọc ra trong tổng cộng dữ liệu đã nhập vào Google Sheets.
