Bạn đã mệt mỏi vì phải lọc thủ công các dòng trong Google Sheets nếu tất cả các ô trong một dòng đều trống? Đừng lo, tôi có một giải pháp cho bạn mà không cần sử dụng bất kỳ cột trợ giúp nào. Hãy cùng khám phá!
Công thức không động: Lọc ra nếu toàn bộ ô trong dòng trống
Thường thì chúng ta coi một dòng là trống nếu ô đầu tiên trong dòng đó trống. Tuy nhiên, trong trường hợp của chúng ta, chúng ta muốn kiểm tra xem liệu có ô nào đó trong một dòng có giá trị hay không. Đây là một bảng mẫu chứa dữ liệu về chim ở Google Sheets:
Để lọc ra các dòng nếu toàn bộ dòng trống trong một phạm vi, bạn có thể sử dụng công thức LỌC sau đây trong ô I1:
=filter(A1:G,(A1:A<>"")+(B1:B<>"")+(C1:C<>"")+(D1:D<>"")+(E1:E<>"")+(F1:F<>"")+(G1:G<>""))Sử dụng công thức này, bạn có thể dễ dàng xác định và loại bỏ các dòng không có giá trị. Đây là kết quả đầu ra:
Bạn cũng có thể thử cách tiếp cận sử dụng công thức LEN và ISBLANK:
=ArrayFormula(filter(A1:G,len(A1:A&B1:B&C1:C&D1:D&E1:E&F1:F&G1:G)))Cả hai công thức này đều có nhược điểm riêng, vì chúng yêu cầu bạn phải chỉ định các cột riêng biệt. Hãy tưởng tượng việc phải chỉ định 26 cột nếu phạm vi của bạn là A1:Z! Hơn nữa, việc chỉ định các cột một cách tự nhiên sẽ không hoạt động nếu bạn muốn loại bỏ các dòng trống khi phạm vi là một biểu thức.
Hãy để tôi minh họa điểm thứ hai bằng một ví dụ. Hãy biến phạm vi A1:G thành một biểu thức bằng cách bọc nó bằng dấu ngoặc nhọn mở:
=filter({A1:G},(CHOOSECOLS(A1:G,1)<>"")+(CHOOSECOLS(A1:G,2)<>"")+(CHOOSECOLS(A1:G,3)<>"")+(CHOOSECOLS(A1:G,4)<>"")+(CHOOSECOLS(A1:G,5)<>"")+(CHOOSECOLS(A1:G,6)<>"")+(CHOOSECOLS(A1:G,7)<>""))Trong trường hợp này, chúng ta đã sử dụng hàm CHOOSECOLS để lấy từng cột riêng lẻ cho mục đích kiểm tra. Tuy nhiên, rất mất công khi số lượng cột lớn.
Công thức động: Lọc ra nếu toàn bộ dòng trống
Cho dù bạn đang làm việc với một phạm vi vật lý hay một kết quả công thức, bạn có thể sử dụng công thức sau để lọc ra một dòng nếu toàn bộ dòng đó trống:
=filter(A1:G,len(trim(transpose(query(transpose(A1:G),,9^9)))))Công thức động này loại bỏ nhu cầu chỉ định từng cột một. Bạn chỉ cần thay thế A1:G bằng {A1:G} trong công thức, và nó sẽ hoạt động mượt mà với cả phạm vi vật lý và kết quả công thức.
Làm thế nào nó hoạt động (Đặc biệt là phần truy vấn)?
Trong công thức trên, chúng ta sử dụng hàm QUERY để kết hợp các cột một cách động. Bằng cách sử dụng LEN, chúng ta có thể kiểm tra xem các cột đã được kết hợp có bất kỳ giá trị nào hay không. Dưới đây là cách nó hoạt động:
- Chúng ta chuyển vị dữ liệu bằng cách sử dụng
transpose(A1:G)để đảm bảo các cột được kết hợp thành một dòng. - Trong cú pháp của QUERY, chúng ta để phần truy vấn rỗng và đặt tham số headers là
9^9(một số rất lớn). Điều này cho biết cho hàm QUERY rằng tất cả các dòng trong dữ liệu đều là tiêu đề và nó sẽ kết hợp chúng thành một dòng. - Việc sử dụng dữ liệu đảo trong hàm QUERY là rất quan trọng để kết hợp các giá trị trong các dòng, không phải các cột. Bạn có thể thử
query(A1:G9,,9^9)trên tờ tính của riêng bạn để khám phá thêm.
Vậy là đã xong! Bây giờ bạn có thể lọc ra các dòng trong Google Sheets nếu toàn bộ dòng đó trống mà không cần sử dụng cột trợ giúp. Thông qua việc phân tích dữ liệu được tối ưu hóa!
Bạn có thể thích: Lọc ra các cột trống trong Google Sheets bằng Công thức Truy vấn
