Menghapus baris dan kolom kosong dalam data

Baris dan kolom kosong bisa menjadi masalah di tabel dalam banyak kasus. Fungsi standar untuk menyortir, memfilter, meringkas, membuat tabel pivot, dll. menganggap baris dan kolom kosong sebagai pemisah tabel, tanpa mengambil data yang terletak lebih jauh di belakangnya. Jika ada banyak celah seperti itu, maka menghapusnya secara manual bisa sangat mahal, dan tidak akan berhasil untuk menghapus sekaligus "dalam jumlah besar" menggunakan penyaringan, karena filter juga akan "tersandung" saat rusak.

Mari kita lihat beberapa cara untuk memecahkan masalah ini.

Metode 1. Cari sel kosong

Ini mungkin bukan yang paling nyaman, tapi pasti cara termudah yang layak disebutkan.

Misalkan kita berurusan dengan tabel seperti itu yang berisi banyak baris dan kolom kosong di dalamnya (disorot untuk kejelasan):

Misalkan kita yakin bahwa kolom pertama tabel kita (kolom B) selalu berisi nama kota. Kemudian sel kosong di kolom ini akan menjadi tanda baris kosong yang tidak perlu. Untuk menghapus semuanya dengan cepat, lakukan hal berikut:

  1. Pilih rentang dengan kota (B2:B26)
  2. Tekan tombolnya F5 lalu tekan Menyoroti (Pergi ke Spesial) atau pilih di tab Beranda — Temukan dan Pilih — Pilih sekelompok sel (Beranda — Temukan&Pilih — Pergi ke spesial).
  3. Di jendela yang terbuka, pilih opsi sel kosong (Kosong) dan tekan OK – semua sel kosong di kolom pertama tabel kita harus dipilih.
  4. Sekarang pilih pada tab Beranda perintah Hapus – Hapus baris dari lembar (Hapus — Hapus baris) atau tekan pintasan keyboard Ctrl+kurang - dan tugas kita terpecahkan.

Tentu saja, Anda dapat membuang kolom kosong dengan cara yang persis sama, menggunakan header tabel sebagai dasarnya.

Metode 2: Cari baris kosong

Seperti yang mungkin sudah Anda ketahui, metode sebelumnya hanya akan berfungsi jika data kita harus berisi baris dan kolom yang terisi penuh, yang dapat dikaitkan saat mencari sel kosong. Tetapi bagaimana jika tidak ada kepercayaan seperti itu, dan data mungkin berisi sel kosong juga?

Lihatlah tabel berikut, misalnya, untuk kasus seperti itu:

Di sini pendekatannya akan sedikit lebih rumit:

  1. Masukkan di sel A2 fungsinya COUNT (JUMLAH), yang akan menghitung jumlah sel yang terisi pada baris di sebelah kanan dan menyalin rumus ini ke seluruh tabel:
  2. Pilih sel A2 dan nyalakan filter dengan perintah Data – Saring (Data — Filter) atau pintasan keyboard Ctrl+perubahan+L.
  3. Mari kita saring angka nol berdasarkan kolom yang dihitung, yaitu semua baris yang tidak memiliki data.
  4. Tetap memilih garis yang difilter dan menghapusnya dengan perintah Beranda — Hapus -' Hapus baris dari lembar (Beranda — Hapus — Hapus baris) atau pintasan keyboard Ctrl+kurang.
  5. Kami mematikan filter dan mendapatkan data kami tanpa baris kosong.

Sayangnya, trik ini tidak lagi dapat dilakukan dengan kolom – Excel belum mempelajari cara memfilter berdasarkan kolom.

Metode 3. Makro untuk menghapus semua baris dan kolom kosong pada lembar

Anda juga dapat menggunakan makro sederhana untuk mengotomatiskan tugas ini. Tekan pintasan keyboard lain+F11 atau pilih dari tab pembangun - Dasar visual (Pengembang — Editor Visual Basic). Jika tab pembangun tidak terlihat, Anda dapat mengaktifkannya melalui File – Opsi – Pengaturan Pita (File — Opsi — Kustomisasi Pita).

Di jendela editor Visual Basic yang terbuka, pilih perintah menu Sisipkan – Modul dan di modul kosong yang muncul, salin dan tempel baris berikut:

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые оки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Kemudian If rng Bukan Apa-apa Kemudian Tetapkan rng = Baris(r) Lain Tetapkan rng = Serikat(rng, Baris(r)) Akhiri Jika Selanjutnya r Jika Tidak rng Bukan Apa-apa Kemudian rng.Hapus 'удаляем пустые олбцы Atur rng = Tidak Ada Untuk r = 1 Untuk ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Jika Application.CountA(Columns(r)) = 0 Kemudian Jika rng Bukan Apa-apa Kemudian Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) Akhiri Jika Selanjutnya r Jika Tidak rng Bukan Apa-apa Maka rng.Delete End Sub

Tutup editor dan kembali ke Excel. 

Sekarang tekan kombinasi lain+F8 atau tombol Macro tab pembangun. Jendela yang terbuka akan mencantumkan semua makro yang saat ini tersedia untuk Anda jalankan, termasuk makro yang baru saja Anda buat. HapusKosong. Pilih dan klik tombol Run (Lari) – semua baris dan kolom kosong pada lembar akan langsung dihapus.

Metode 4: Power Query

Cara lain untuk memecahkan masalah kami dan skenario yang sangat umum adalah menghapus baris dan kolom kosong di Power Query.

Pertama, mari muat tabel kita ke dalam Power Query Editor. Anda dapat mengubahnya menjadi "pintar" dinamis dengan pintasan keyboard Ctrl+T atau cukup pilih rentang data kami dan beri nama (misalnya Data) di bilah rumus, mengonversi menjadi bernama:

Sekarang kita menggunakan perintah Data – Dapatkan data – Dari tabel / rentang (Data – Dapatkan Data – Dari tabel / rentang) dan memuat semuanya ke dalam Power Query:

Maka semuanya sederhana:

  1. Kami menghapus baris kosong dengan perintah Beranda – Kurangi baris – Hapus baris – Hapus baris kosong (Beranda – Hapus Baris – Hapus baris kosong).
  2. Klik kanan pada judul kolom Kota pertama dan pilih perintah Unpivot Other Columns dari menu konteks. Meja kami akan, seperti yang secara teknis disebut dengan benar, dinormalisasi – dikonversi menjadi tiga kolom: kota, bulan dan nilai dari persimpangan kota dan bulan dari tabel asli. Keunikan operasi ini di Power Query adalah melewati sel kosong di data sumber, yang kami butuhkan:
  3. Sekarang kita melakukan operasi sebaliknya – kita mengubah tabel yang dihasilkan kembali menjadi tabel dua dimensi untuk mengembalikannya ke bentuk aslinya. Pilih kolom dengan bulan dan pada tab Transformasi pilih tim Kolom pivot (Transformasi — Kolom Pivot). Di jendela yang terbuka, sebagai kolom nilai, pilih yang terakhir (Nilai), dan di opsi lanjutan – operasi Jangan agregat (Jangan agregat):
  4. Tetap mengunggah hasilnya kembali ke Excel dengan perintah Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup&Muat — Tutup&Muat ke…)

  • Apa itu makro, bagaimana cara kerjanya, di mana menyalin teks makro, bagaimana menjalankan makro?
  • Mengisi semua sel kosong dalam daftar dengan nilai sel induk
  • Menghapus semua sel kosong dari rentang tertentu
  • Menghapus semua baris kosong di lembar kerja dengan add-on PLEX

Tinggalkan Balasan