Google sheet lọc ô trống

Thay vì ẩn các cột trống theo cách thủ công, bạn có thể sử dụng công thức kết hợp Truy vấn trong tab mới để lọc ra các cột trống trong Google Trang tính

Để loại trừ các hàng trống, bạn có thể sử dụng chức năng Lọc hoặc Truy vấn. Đây là một công thức ví dụ để làm điều này mặc dù nó không phải là chủ đề của chúng tôi

=QUERY(A1:B,"Select * Where A<>'' and B<>''")

Còn việc loại trừ các cột trống trong Google Trang tính thì sao?

Điều đó cũng có thể thực hiện được với một công thức Truy vấn phức tạp. Bài đăng này trả lời các truy vấn tương tự sau đây của người dùng về các cột lọc

• Cách loại trừ các cột trống trong đầu ra công thức trong Google Sheets
• Cách chỉ trích xuất những cột chứa giá trị trong bất kỳ ô nào
• Sử dụng Truy vấn để lọc ra các cột trống trong Google Trang tính

Tại sao nên lọc các cột trống trong Google Sheets?

Tôi có tệp Google Trang tính trong đó một tab chứa nhiều cột dữ liệu. Trong đó một vài cột trống

Tôi không thể dự đoán cột nào sẽ trống trong tương lai. Bởi vì các giá trị trong các cột đó là động

Ý tôi là các giá trị là đầu ra của một số tính toán khác. Vì vậy, nếu cột B hiện đang trống, lần sau, nó có thể là cột C hoặc một số cột khác trong phạm vi

Có thể có nhiều cột trống như vậy. Điều đó có nghĩa là ẩn các cột theo cách thủ công không phải là một giải pháp lý tưởng

Tôi không muốn những cột trống đó trong bản in. Nếu tôi có thể bỏ qua các cột trống đó, thì tôi có thể in tập dữ liệu đó trên một trang

Với Công thức truy vấn, bạn có thể loại trừ các cột trống trong Google Trang tính. Những gì bạn muốn làm là, trong một tab mới, lọc ra các cột trống bằng công thức kết hợp Truy vấn

Trong bài đăng này, tôi sẽ cung cấp cho bạn một công thức động có thể bao gồm tất cả các hàng trong dữ liệu của bạn và một số cột

Cách lọc các cột trống bằng công thức truy vấn

Dữ liệu mẫu. Tháng Mua khôn ngoan Số lượng ít sản phẩm trong năm

Dữ liệu trên nằm trong tab có tên “Test Data”. Có tổng cộng 13 cột trong tập dữ liệu này

Trong 13 cột đó, cột đầu tiên chứa tên sản phẩm và 12 cột còn lại chứa dữ liệu hàng tháng

Tôi chỉ muốn in các cột A, B, D, F và K chứa các giá trị. Điều đó có nghĩa là tổng cộng có 5 cột

Nó sẽ không chỉ cải thiện khả năng đọc mà còn cho phép chúng tôi tiết kiệm một số tiền khi in. Làm thế nào để làm điều đó?

Trong bất kỳ tab trống nào, trong ô đầu tiên, tôi. e. ô A1, nhập Truy vấn bên dưới. Đó là tất cả

=ArrayFormula(Query(transpose(Query(TRANSPOSE({Query({'Test Data'!A1:M1;to_text(Query({if('Test Data'!A2:M<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:M1)&")")))},"Offset 1",1);'Test Data'!A2:M}),"Select * Where Col2<>'0'",1)),"Select * Offset 1",1))

Điều này sẽ điền vào đầu ra bên dưới không chứa bất kỳ cột trống nào

Loại trừ các cột trống trong một phạm vi dữ liệu khác – Cách thực hiện?

Bạn có thể bao gồm 'bao nhiêu cột' và hàng tùy thích trong phạm vi dữ liệu của mình. Hãy để tôi giải thích điều đó

Tôi chỉ kiểm tra công thức lên đến 100 cột mặc dù nó có thể xử lý nhiều cột hơn. Tôi nghĩ rằng nhiều cột là đủ cho mục đích bình thường

Để lọc ra các cột trống bằng công thức Truy vấn như trên ở một vùng dữ liệu khác, bạn phải biết cách điều chỉnh công thức

Truy vấn trên dành cho phạm vi A1. M. Đối với một phạm vi khác, ví dụ, A1. Z, thực hiện các thay đổi như bên dưới

=ArrayFormula(Query(transpose(Query(TRANSPOSE({Query({'Test Data'!A1:Z1;to_text(Query({if('Test Data'!A2:Z<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:Z1)&")")))},"Offset 1",1);'Test Data'!A2:Z}),"Select * Where Col2<>'0'",1)),"Select * Offset 1",1))

Ghi chú. Đối với bảng mẫu có công thức của tôi, hãy cuộn xuống phần cuối cùng của hướng dẫn này

Khi bạn so sánh công thức này với công thức trước đó, bạn có thể thấy tôi đã thực hiện các thay đổi ở đâu

• Tất cả các lần xuất hiện của tham chiếu A1. M1 đã được đổi thành A1. Z1
• Tương tự, tất cả các lần xuất hiện của tham chiếu A2. M đã được đổi thành A2. Z

Hy vọng bây giờ bạn có thể sửa đổi công thức cho phạm vi dữ liệu của mình

Cố gắng hiểu công thức tốt hơn với phần giải thích công thức của tôi bên dưới. Nó sẽ hữu ích cho việc điều chỉnh công thức hơn nữa

Giải thích công thức

Trên thực tế ở trên là một công thức kết hợp phức tạp trong đó Truy vấn đóng vai trò chính. Các chức năng khác có liên quan là;

TRANSPOSE, IF, JOIN, COLUMN và ARRAYFORMULA

Tôi đang xem xét phạm vi dữ liệu A1. Z trong giải thích công thức này (công thức vừa ở trên)

Cột A chứa các sản phẩm và Cột B đến Z chứa dữ liệu hàng tháng từ tháng 1 năm 2019 đến tháng 1 năm 2021

Bước 1

Kiểm tra tất cả các ô và thay thế các giá trị bằng # 1 và khoảng trống bằng # 0

Trong một tab mới, hãy nhập công thức được đánh dấu dưới dạng công thức mảng như bên dưới

=ArrayFormula(if('Test Data'!A2:Z<>"",1,0))

Hình ảnh dưới đây cho thấy đầu ra. 0 đại diện cho các giá trị trống trong phạm vi A2. Z

Bước 2

Khi bạn cộng hoặc đếm từng cột, nếu đầu ra > 0 ở bất kỳ cột nào, điều đó có nghĩa là cột đó không trống, các cột khác để trống. Đó là phần logic

Chúng ta có thể sử dụng đầu ra ở trên làm phạm vi trong Truy vấn để thực hiện tổng hợp

Ghi chú. Công thức này là một ví dụ, không phải là công thức mà chúng tôi thực sự muốn

=ArrayFormula(Query(if('Test Data'!A2:Z<>"",1,0),"Select Sum(Col1), Sum(Col2)"))

Có liên quan. Cách tính Tổng, Trung bình, Đếm, Tối đa và Tối thiểu trong Truy vấn Google Trang tính

Vì có 27 cột nên sẽ rất khó để bao gồm tất cả các cột trong phạm vi theo cách thủ công. Ngoài vấn đề này, còn một vấn đề nữa

Chúng tôi có dữ liệu động trong tab “Dữ liệu thử nghiệm”. Có thể có nhiều hoặc ít cột có giá trị trong tương lai

Vì vậy, chúng tôi muốn có một Truy vấn động. Điều đó có nghĩa là Truy vấn phải tự động điền vào phần tổng hợp

"Select Sum(Col1), Sum(Col2), Sum(Col3)...Sum(Col26)

Thế nào?

Tôi đã làm điều đó với sự trợ giúp của các chức năng Cột và Tham gia

Tương tự. Cách nhận tham chiếu cột động trong truy vấn Google Sheets

Nếu bạn muốn kiểm tra công thức được đánh dấu, hãy sử dụng nó như bên dưới trong một tab mới

=ArrayFormula(Query({if('Test Data'!A2:Z<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:Z1)&")")))

Nó sẽ trả về các giá trị trong hai hàng như bên dưới. Xin lưu ý rằng hàng đầu tiên chứa nhãn “Tổng” trong mỗi cột và hàng thứ hai chứa Tổng của các cột

Cập nhật

Chúng ta phải chuyển đổi tất cả các giá trị trong Truy vấn trên thành văn bản. Điều này rất quan trọng bởi vì;

Giả sử bạn muốn lọc ra các cột trống trong Google Trang tính và các cột của bạn chứa văn bản chứ không phải số

Trong các bước tiếp theo, tôi sẽ kết hợp đầu ra Truy vấn ở trên với dữ liệu nguồn. Điều đó sẽ làm cho dữ liệu của bạn trở thành loại hỗn hợp và gây ra sự cố trong Truy vấn

Tôi cũng đã bao gồm To_Text cho chuyển đổi

=ArrayFormula(TO_TEXT(Query({if('Test Data'!A2:Z<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:Z1)&")"))))

Bước 3

Trong bước thứ ba, tôi đã thêm hàng tiêu đề ban đầu nằm trong phạm vi A1. Z1 là hàng trên cùng của đầu ra ở trên

Sau đó, sử dụng dữ liệu đó làm dữ liệu trong Truy vấn mới và bù cho hàng chứa nhãn là “tổng”

=ArrayFormula(Query({'Test Data'!A1:Z1;to_text(Query({if('Test Data'!A2:Z<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:Z1)&")")))},"Offset 1",1))

Bước # 4.

Ở đây, lần đầu tiên tôi đã thêm dữ liệu thực trong phạm vi A2. Z với dữ liệu trên và sau đó chuyển đổi nó

=ArrayFormula(TRANSPOSE({Query({'Test Data'!A1:Z1;to_text(Query({if('Test Data'!A2:Z<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:Z1)&")")))},"Offset 1",1);'Test Data'!A2:Z}))

Bước # 5

Sau đó, tôi đã sử dụng nó trong một Truy vấn khác trong đó cột đầu tiên chứa tiêu đề ban đầu của chúng tôi và cột thứ hai chứa các số tổng

=ArrayFormula(Query(transpose(Query(TRANSPOSE({Query({'Test Data'!A1:M1;to_text(Query({if('Test Data'!A2:M<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:M1)&")")))},"Offset 1",1);'Test Data'!A2:M}),"Select * Where Col2<>'0'",1)),"Select * Offset 1",1))

Công thức truy vấn trên lọc ra các hàng có chứa chuỗi “0” trong cột 2. Sau đó, một chuyển vị khác sửa hướng của dữ liệu

=ArrayFormula(Query(transpose(Query(TRANSPOSE({Query({'Test Data'!A1:M1;to_text(Query({if('Test Data'!A2:M<>"",1,0)},"Select "&JOIN(",","Sum(Col"&column('Test Data'!A1:M1)&")")))},"Offset 1",1);'Test Data'!A2:M}),"Select * Where Col2<>'0'",1)),"Select * Offset 1",1))

Tôi phải cho bạn xem đầu ra ngay bây giờ. Sau đó, chỉ có bạn có thể hiểu nó.

Công thức Truy vấn cuối cùng chỉ bù cho hàng được đánh dấu này. Đó là tất cả. Bạn có thể sử dụng Bảng kiểm tra của tôi để lọc ra các cột trống trong Google Trang tính