Công thức SUMIF rất hữu ích để tính tổng có điều kiện trong Google Sheets. Nhưng bạn có biết cách sử dụng SUMIF để loại bỏ các hàng ẩn trong Google Sheets không?
Những gì chúng ta cần làm là kết hợp SUMIF và SUBTOTAL để loại bỏ các hàng ẩn. Có hai phương pháp chúng ta có thể sử dụng, một phương pháp sử dụng cột trợ giúp và một phương pháp không sử dụng cột trợ giúp.
Công thức SUMIF Loại bỏ Các hàng Ẩn trong Google Sheets Sử dụng Cột Trợ giúp
Cột trợ giúp – Một cột bổ sung cho mục đích tính toán của chúng ta.
Hãy xem cách áp dụng tổng có điều kiện loại bỏ các hàng ẩn.
Ở ví dụ này, tôi muốn tính tổng giá trị bán hàng của một mặt hàng cụ thể, ví dụ “Coverall”, trong cột B. Nghĩa là tổng cột E nếu các giá trị trong cột B bằng “Coverall”.
Chúng ta có thể dễ dàng thực hiện điều này với công thức SUMIF. Nhưng chúng ta muốn một điều nữa!
=sumif(B2:B7,”Coverall”,E2:E7)
Khi chúng ta ẩn bất kỳ hàng nào chứa “Coverall”, giá trị trong hàng ẩn không được hiển thị trong tổng.
Bước
Vì vậy, đây là những bước sử dụng SUMIF Loại bỏ Các hàng Ẩn trong Google Sheets.
Trong cột trợ giúp A, ở ô A2, áp dụng công thức Subtotal dưới đây và sao chép dán nó xuống cho đến ô A7.
=subtotal(109,E2)
SUBTOTAL ở trên là chìa khóa. Bạn có biết tại sao?
Nó trích xuất tất cả các giá trị từ cột E đến cột A.
Khi bạn ẩn bất kỳ hàng nào trong phạm vi, công thức subtotal trong hàng đó sẽ trả về số không thay vì giá trị thực tế.
Với tính năng này của SUBTOTAL, chúng ta có thể bỏ qua các giá trị hàng ẩn trong tổng với một công thức như sau.
=sumifs(E2:E7,A2:A7,”>0″,B2:B7,”Coverall”)
Nó sẽ trả về tổng của phạm vi ô E2:E7 nếu B2:B7 = “Coverall” và A2:A7>0.
Khi bạn ẩn bất kỳ hàng nào, giá trị trong ô tương ứng trong cột A sẽ trở thành 0 (số không).
Vì vậy, công thức SUMIFS sẽ loại bỏ hàng đó trong tổng.
Bây giờ, hãy ẩn bất kỳ hàng nào chứa mặt hàng “Coverall” và xem điều kỳ diệu.
SUMIFS Loại bỏ Các hàng Ẩn mà Không Sử dụng Cột Trợ giúp
Chúng ta đã học cách sử dụng SUMIF Loại bỏ Các hàng Ẩn trong Google Sheets với cột trợ giúp ở trên. Bây giờ chúng ta có thể làm điều đó với một cột trợ giúp ảo.
Hãy xóa cột A. Do đó, cột điều kiện (Mục) sẽ trở thành A và cột giá trị (Số lượng) sẽ trở thành D.
1. Phương pháp Không Động
Đây là phương pháp không động của chúng ta để SUMIF Loại bỏ Các hàng Ẩn trong Google Sheets mà không sử dụng bất kỳ cột trợ giúp nào.
=sumifs(D2:D7,{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)},”>0″,A2:A7,”Coverall”)
Công thức này có vẻ phức tạp, đúng không? Nhưng thực sự thì không phải vậy!
Công thức trong Dấu ngoặc nhọn là một cột trợ giúp ảo.
Bước (Cột trợ giúp ảo)
Đầu tiên, chúng ta cần một cột trợ giúp chứa SUBTOTAL và hãy sử dụng D2:D7. Chúng ta sử dụng cột này cho mục đích tạm thời và có thể xóa nó sau đó.
Vui lòng tham khảo cột E. Tôi đã sao chép công thức E2 thành E3:E7.
Bây giờ nhấn phím tắt Ctrl + ~ để hiển thị các công thức trong cột trợ giúp.
Xóa dấu ‘=’ trong các công thức trong cột trợ giúp.
Một lần nữa, nhấn phím tắt Ctrl + ~.
Bây giờ, điều chúng ta muốn làm là sử dụng công thức sau trong bất kỳ ô trống nào.
=join(“;”,E2:E7)
Sao chép kết quả và dán giá trị vào cùng một ô. Bạn có thể sử dụng Ctrl + C để sao chép và Alt + E + S + V để dán giá trị.
Nó sẽ trông như sau.
subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)
Chỉ đơn giản đặt Dấu ngoặc nhọn mở và đóng với văn bản trên để biến nó thành một mảng.
{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)}
Bây giờ bạn có thể xóa cột trợ giúp E.
2. Phương pháp Động Sử dụng Hàm BYROW
Phương pháp không động trên có hai điểm hạn chế chính.
- Mất nhiều thời gian để tạo cột trợ giúp ảo.
- Hữu ích chỉ khi số hàng trong dữ liệu nguồn nhỏ, ví dụ < 15.
Chúng ta có thể vượt qua nó bằng cách sử dụng BYROW, một hàm trợ giúp LAMBDA, trong Google Sheets.
=byrow(D2:D7,lambda(helper,subtotal(109,helper)))
Chúng ta có thể thay thế công thức cột trợ giúp ảo trên bằng BYROW trong SUMIFS, và đó là tất cả!
=sumifs(D2:D7,byrow(D2:D7,lambda(helper,subtotal(109,helper))),”>0″,A2:A7,”Coverall”)
Trên đây là công thức mà tôi đề xuất cho SUMIF Loại bỏ Các hàng Ẩn trong Google Sheets.
