Google sheets memfilter sel kosong

Anda ingin menyembunyikan Baris 4 dan 7, karena benar-benar kosong. Untuk melakukan ini, Anda memerlukan kolom pembantu untuk menunjukkan jika suatu baris kosong menggunakan rumus COUNTA

1. Tambahkan kolom baru (F) yang akan berfungsi sebagai kolom pembantu untuk memfilter baris kosong. Di sel F2 masukkan rumusnya

=COUNTA(B2:E2)

Rumus ini mengembalikan jumlah sel yang tidak kosong dalam rentang yang diberikan (dalam hal ini, dalam B2. E2). Jika Anda mendapatkan nol di sel mana pun, itu berarti baris tersebut kosong, karena tidak ada nilai yang tidak kosong

2. Sekarang posisikan kursor di sudut kanan bawah sel F2 hingga tanda silang hitam muncul, dan seret melalui baris terakhir rentang data (Baris 8)

3. Sekarang rumusnya ada di kisaran F2. F8. Saring semua nilai nol untuk menyembunyikan baris kosong. Pertama, aktifkan filter untuk Kolom F (Kosong). Pilih sel mana pun di kolom (dalam hal ini, F1), dan di Pita, buka Beranda > Urutkan & Filter > Filter

4. Untuk memfilter nilai kosong (nol), (1) klik ikon filter di sel F1, (2) hapus centang 0, dan (3) klik OK

Akibatnya, semua baris kosong (dalam hal ini 4 dan 7) disembunyikan. Anda dapat melihat bahwa nomor baris berwarna biru dan judul baris tersembunyi tidak ada. Ini menunjukkan bahwa beberapa baris di lembar kerja disembunyikan

Catatan. Anda juga dapat menggunakan kode VBA untuk menyembunyikan atau menghapus baris kosong

Sembunyikan Baris Kosong di Google Sheets

Anda juga dapat menyembunyikan semua baris kosong di Google Sheets

1. Setelah Anda memasukkan rumus (=COUNTA(B2. E2)) di Kolom F, aktifkan filter dengan membuka Data > Buat filter

2. Untuk memfilter nilai nol, (1) klik ikon filter di sel F1, (2) hapus centang 0, dan (3) klik OK

Alih-alih menyembunyikan kolom kosong secara manual, Anda dapat menggunakan rumus kombinasi kueri di tab baru untuk memfilter kolom kosong di Google Sheets

Untuk mengecualikan baris kosong, Anda dapat menggunakan fungsi Filter atau Kueri. Berikut adalah salah satu contoh rumus untuk melakukan ini meskipun itu bukan topik kita

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

Bagaimana dengan mengecualikan kolom kosong di Google Sheets?

Itu juga dimungkinkan dengan rumus Kueri yang kompleks. Posting ini menjawab pertanyaan pengguna serupa berikut tentang kolom pemfilteran

• Cara mengecualikan kolom kosong di keluaran rumus di Google Sheets
• Cara mengekstrak hanya kolom yang berisi nilai di salah satu sel
• Gunakan Kueri untuk memfilter kolom kosong di Google Sheets

Mengapa Seseorang Harus Menyaring Kolom Kosong di Google Sheets?

Saya memiliki file Google Sheets di satu tab yang berisi beberapa kolom data. Dalam beberapa kolom yang kosong

Saya tidak dapat memprediksi kolom mana yang akan kosong di masa mendatang. Karena nilai pada kolom tersebut bersifat dinamis

Maksud saya nilainya adalah hasil dari beberapa perhitungan lain. Jadi jika kolom B kosong sekarang, lain kali, mungkin kolom C atau beberapa kolom lain dalam rentang

Mungkin ada beberapa kolom kosong seperti itu. Itu berarti menyembunyikan kolom secara manual bukanlah solusi yang ideal

Saya tidak ingin kolom kosong itu ada di cetakan. Jika saya dapat menghilangkan kolom kosong tersebut, saya mungkin dapat mencetak kumpulan data tersebut pada satu halaman

Dengan rumus Kueri, Anda dapat mengecualikan kolom kosong di Google Sheets. Yang ingin Anda lakukan adalah, di tab baru, saring kolom kosong menggunakan rumus kombo Kueri

Dalam posting ini, saya akan memberi Anda rumus dinamis yang dapat mencakup semua baris dalam data Anda dan beberapa kolom

Cara Menyaring Kolom Kosong Menggunakan Rumus Kueri

Contoh data. Pembelian Bijak Bulan Jumlah beberapa produk dalam setahun

Data di atas ada di tab bernama "Test Data". Ada total 13 kolom dalam kumpulan data ini

Pada 13 kolom tersebut, kolom pertama berisi nama produk dan 12 kolom lainnya berisi data bulanan

Saya hanya ingin mencetak kolom A, B, D, F dan K yang berisi nilai. Itu berarti total 5 kolom

Ini tidak hanya meningkatkan keterbacaan tetapi juga memungkinkan kami menghemat uang untuk mencetak. Bagaimana cara melakukannya?

Di tab kosong mana pun, di sel pertama, i. e. sel A1, masukkan Kueri di bawah ini. Itu saja

=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))

Ini akan mengisi output di bawah ini yang tidak berisi kolom kosong

Kecualikan Kolom Kosong dalam Rentang Data Berbeda – Bagaimana caranya?

Anda dapat menyertakan 'kolom sebanyak' dan baris yang Anda inginkan dalam rentang data Anda. Mari saya jelaskan itu

Saya menguji rumus hingga 100 kolom saja meskipun dapat menangani lebih banyak kolom. Saya pikir banyak kolom sudah cukup untuk tujuan normal

Untuk memfilter kolom kosong menggunakan rumus Kueri seperti di atas dalam rentang data yang berbeda, Anda harus mengetahui cara menyesuaikan rumus

Kueri di atas adalah untuk rentang A1. M. Untuk rentang yang berbeda, misalnya A1. Z, lakukan perubahan seperti di bawah ini

=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))

Catatan. Untuk lembar sampel saya dengan rumus, gulir ke bawah ke bagian terakhir dari tutorial ini

Saat Anda membandingkan rumus ini dengan yang sebelumnya, Anda dapat melihat di mana saya membuat perubahan

• Semua kejadian referensi A1. M1 telah diubah menjadi A1. Z1
• Demikian pula, semua kemunculan referensi A2. M telah diubah menjadi A2. Z

Semoga sekarang Anda dapat mengubah rumus untuk rentang data Anda

Coba pahami rumusnya lebih baik lagi dengan penjelasan rumus saya di bawah ini. Ini akan berguna untuk mengutak-atik formula lebih lanjut

Penjelasan Formula

Sebenarnya di atas adalah formula kombinasi yang kompleks dimana Query memainkan peran kunci. Fungsi lain yang terlibat adalah;

TRANSPOSE, IF, JOIN, COLUMN, dan ARRAYFORMULA

Saya sedang mempertimbangkan rentang data A1. Z dalam penjelasan rumus ini (rumus tepat di atas)

Kolom A berisi produk dan Kolom B hingga Z berisi data bulanan dari Jan-2019 hingga Jan-2021

Langkah 1

Uji semua sel dan ganti nilainya dengan # 1 dan kosongkan dengan # 0

Di tab baru, masukkan rumus yang disorot sebagai rumus larik seperti di bawah ini

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

Gambar di bawah menunjukkan output. Angka 0 mewakili nilai kosong dalam rentang A2. Z

Langkah 2

Saat Anda menjumlahkan atau menghitung setiap kolom, jika hasilnya >0 di kolom mana pun, itu berarti kolom tersebut tidak kosong, jika tidak kosong. Itu bagian logikanya

Kita dapat menggunakan output di atas sebagai range dalam Query untuk melakukan agregasi

Catatan. Rumus ini adalah contoh, bukan yang kita inginkan sebenarnya

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

Terkait. Cara Menjumlahkan, Rata-Rata, Menghitung, Maks, dan Min di Google Sheets Query

Karena ada 27 kolom, akan menjadi tugas yang sulit untuk memasukkan semua kolom dalam rentang secara manual. Selain itu, ada satu masalah lagi

Kami memiliki data dinamis di tab "Data Uji". Mungkin ada lebih banyak atau lebih sedikit kolom dengan nilai di masa mendatang

Jadi kami menginginkan Query yang dinamis. Artinya, Kueri harus mengisi bagian agregasi secara otomatis

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

Bagaimana?

Saya telah melakukannya dengan bantuan fungsi Kolom dan Gabung

Serupa. Cara Mendapatkan Referensi Kolom Dinamis di Google Sheets Query

Jika Anda ingin menguji formula yang disorot, gunakan seperti di bawah ini di tab baru

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

Ini akan mengembalikan nilai dalam dua baris seperti di bawah ini. Harap perhatikan bahwa baris pertama berisi label “Jumlah” di setiap kolom dan baris kedua berisi Jumlah kolom

Memperbarui

Kita harus mengonversi semua nilai dalam Kueri di atas menjadi teks. Ini penting karena;

Asumsikan Anda ingin memfilter kolom kosong di Google Sheets dan kolom Anda berisi teks, bukan angka

Pada langkah selanjutnya, saya akan menggabungkan keluaran Kueri di atas dengan data sumber. Itu akan membuat tipe data Anda tercampur dan menyebabkan masalah di Query

Saya juga menyertakan To_Text untuk konversi

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

Langkah #3

Pada langkah ketiga, saya telah menambahkan baris tajuk asli yang berada di kisaran A1. Z1 sebagai baris teratas dari output di atas

Kemudian gunakan itu sebagai data dalam Kueri baru dan offset baris yang berisi label sebagai "jumlah"

=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))

Langkah # 4.

Di sini saya pertama kali menambahkan data nyata dalam rentang A2. Z dengan data di atas dan kemudian ditransposisikan

=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}))

Langkah # 5

Kemudian saya telah menggunakannya di Kueri lain di mana kolom pertama berisi tajuk asli kami dan kolom kedua adalah jumlah angka

=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))

Rumus Kueri di atas memfilter baris yang berisi string "0" di kolom 2. Kemudian transpos lain mengoreksi orientasi data

=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))

Saya harus menunjukkan hasilnya sekarang. Maka hanya Anda yang bisa memahaminya.

Rumus Kueri terakhir baru saja mengimbangi baris yang ditandai ini. Itu saja. Anda dapat menggunakan Lembar pengujian saya untuk memfilter kolom kosong di Google Sheets

Bagaimana Anda memeriksa kekosongan di Google Sheets?

Bagaimana cara menghitung sel kosong dan tidak kosong di Google Sheets?