Bạn có thể loại bỏ các giá trị ẩn hoặc được lọc ra khỏi tổng trong công cụ Google Sheets, nhưng không thể sử dụng chức năng SUM thông thường để làm điều này. Thay vào đó, bạn nên sử dụng chức năng SUBTOTAL với mã chức năng 9 (loại bỏ/cắt bỏ các hàng bị lọc) hoặc 109 (loại bỏ/cắt bỏ các hàng bị lọc và ẩn). Vì vậy, bài viết này sẽ giúp bạn hiểu cách sử dụng chức năng SUBTOTAL trong Google Sheets để tính tổng các hàng hiển thị.
Google Docs Spreadsheet có một số chức năng giống như Excel để đáp ứng nhu cầu nhập dữ liệu của người dùng. SUBTOTAL cũng là một trong số chúng. Các chức năng như QUERY, REGEXMATCH và IMPORTRANGE khiến Google Sheets trở thành một giải pháp bảng tính hoàn hảo mà người khác ngưỡng mộ.
Lưu ý, bạn có thể tham khảo hướng dẫn Google Sheets Functions của tôi để tìm hiểu tất cả các chức năng phổ biến trong 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
Chúng ta 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 tính. Tiếp theo, chúng ta sẽ xem cách sử dụng dữ liệu nguồn trong một bảng tính và công thức trong một bảng tính khác, nhưng trong cùng một tệp. Cuối cùng, chúng ta sẽ thấy 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 riêng và công thức nằm trong một tệp khác.
Dữ Liệu Nguồn Và Công Thức SUBTOTAL Trong Cùng Một Bảng Tính
Tôi có các giá trị cần tính tổng trong B2:B5. Hãy xem cách loại bỏ giá trị trong các hàng ẩn hoặc được lọc ra trong tổng cộng trong ô B7.
Ghi chú:
- Filtered out có nghĩa là lọc bản ghi bằng cách sử dụng Data > Create a filter hoặc Data > Add a slicer.
- Hidden có nghĩa là ẩn các hàng bằng cách nhấp chuột phải vào một hàng và chọn Hide, hoặc bằng cách áp dụng nhóm cho các hàng được chọn bằng tính năng View > Group.
Như đã nêu rõ ở trên, để tính tổng hoặc tổng cộng không bao gồm các hàng ẩn hoặc được lọc ra, bạn nên sử dụng chức năng SUBTOTAL() thay vì chức năng SUM().
Bạn phải sử dụng mã chức năng tổng, tức là 9 hoặc 109 vì chức năng SUBTOTAL có thể thực hiện mười một loại tổng hợp.
Trong ví dụ sau, tôi đã ẩn hàng số 3 bằng cách nhấp chuột phải vào nó và chọn Hide từ menu ngắn.
=SUBTOTAL(109,B2:B5)Khi chức năng SUM cộng giá trị trong hàng ẩn vào tổng, chức năng SUBTOTAL bỏ qua nó.
Cũng tương tự, điều này cũng xảy ra khi bạn áp dụng chức năng SUBTOTAL vào một bảng được lọc.
Nếu bạn chỉ muốn loại bỏ các hàng bị lọc ra, thay thế mã chức năng tổng 109 bằng 9 trong công thức. Điều này sẽ bao gồm các hàng ẩn.
Dữ Liệu Nguồn Và Công Thức SUBTOTAL Trong Hai Bảng Tính Khác Nhau Trong Cùng Một Tệp
Nếu phạm vi tổng ở trên nằm trong Sheet1, 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 tính nào khác trong cùng một tệp.
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 ta sử dụng chức năng IMPORTRANGE để nhập dữ liệu từ một tệp Google Sheets vào tệp khác. Bạn có thể dễ dàng nhập dữ liệu bằng cách sử dụng chức năng này.
Để làm điều này, bạn chỉ cần chỉ định hai đối số: URL của tệp Google Sheets từ đó bạn muốn nhập dữ liệu và tên bảng tính cùng với phạm vi.
Ví dụ, công thức sau 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")Lưu ý: Công thức này sẽ không hoạt động với bạn vì tôi đã sử dụng một URL giả cho mục đích minh họa. Bạn cần thay thế URL bằng URL của bảng chứa dữ liệu cần nhập.
Bạn có thể bao bọc công thức này bằng SUM để có tổng của các giá trị được nhập:
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1as2K3NyJ58WipNSm56rtE__-DD3F6_x_RAp2ElpHNJw/edit#gid=1892488864","Sheet1!B2:B5"))Làm sao chúng ta có thể loại bỏ các giá trị ẩn hoặc được lọc ra trong tổng cộng của dữ liệu đã nhập này? Bọc nó bằng SUBTOTAL không hoạt động vì đối số của SUBTOTAL phải là một phạm vi và chúng ta đang cung cấp một biểu thức thay vì nó.
Giải pháp tạm thời:
Trong bảng nguồn, 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 thay vì SUM.
=SUMIF(B1:B4,1,A1:A4)Cách này, chúng ta có thể loại bỏ các giá trị ẩn hoặc được lọc ra khỏi tổng cộng của dữ liệu đã nhập trong Google Sheets.
Related:
