Contents [show]
Baris dan lajur kosong boleh menyusahkan jadual dalam banyak kes. Fungsi standard untuk mengisih, menapis, meringkaskan, mencipta jadual pangsi, dll. menganggap baris dan lajur kosong sebagai pemisah jadual, tanpa mengambil data yang terletak jauh di belakangnya. Sekiranya terdapat banyak jurang sedemikian, maka mengalih keluarnya secara manual boleh menjadi sangat mahal, dan ia tidak akan berfungsi untuk mengalih keluar sekaligus "secara pukal" menggunakan penapisan, kerana penapis juga akan "tersandung" pada waktu rehat.
Mari lihat beberapa cara untuk menyelesaikan masalah ini.
Kaedah 1. Cari sel kosong
Ini mungkin bukan yang paling mudah, tetapi pastinya cara yang paling mudah patut disebut.
Katakan kita berurusan dengan jadual sedemikian yang mengandungi banyak baris dan lajur kosong di dalamnya (diserlahkan untuk kejelasan):
Katakan kami pasti bahawa lajur pertama jadual kami (lajur B) sentiasa mengandungi nama bandar. Kemudian sel kosong dalam lajur ini akan menjadi tanda baris kosong yang tidak perlu. Untuk mengalih keluar semuanya dengan cepat, lakukan perkara berikut:
- Pilih julat dengan bandar (B2:B26)
- Tekan kekunci F5 dan kemudian tekan Serlahkan (Pergi ke Khas) atau pilih pada tab Laman Utama — Cari dan Pilih — Pilih kumpulan sel (Laman Utama — Cari&Pilih — Pergi ke khas).
- Dalam tetingkap yang terbuka, pilih pilihan sel kosong (Kosong) dan tekan OK – semua sel kosong dalam lajur pertama jadual kami hendaklah dipilih.
- Sekarang pilih pada tab Laman Utama Perintah Padam – Padam baris daripada helaian (Padam — Padam baris) atau tekan pintasan papan kekunci Ctrl+tolak – dan tugas kami selesai.
Sudah tentu, anda boleh menyingkirkan lajur kosong dengan cara yang sama, menggunakan pengepala jadual sebagai asas.
Kaedah 2: Cari baris kosong
Seperti yang anda mungkin telah fikirkan, kaedah sebelumnya hanya akan berfungsi jika data kami semestinya mengandungi baris dan lajur yang diisi sepenuhnya, yang boleh disambungkan semasa mencari sel kosong. Tetapi bagaimana jika tidak ada keyakinan sedemikian, dan data mungkin mengandungi sel kosong juga?
Lihat jadual berikut, sebagai contoh, untuk kes sedemikian:
Di sini pendekatannya akan menjadi lebih rumit:
- Masukkan dalam sel A2 fungsi COUNT (COUNTA), yang akan mengira bilangan sel yang diisi dalam baris di sebelah kanan dan menyalin formula ini ke seluruh jadual:
- Pilih sel A2 dan hidupkan penapis dengan arahan Data – Penapis (Data — Penapis) atau pintasan papan kekunci Ctrl+Beralih+L.
- Mari tapis sifar mengikut lajur yang dikira, iaitu semua baris yang tiada data.
- Ia kekal untuk memilih baris yang ditapis dan memadamnya dengan arahan Laman Utama — Padam -' Padam baris daripada helaian (Laman Utama — Padam — Padam baris) atau pintasan papan kekunci Ctrl+tolak.
- Kami mematikan penapis dan mendapatkan data kami tanpa baris kosong.
Malangnya, helah ini tidak lagi boleh dilakukan dengan lajur – Excel belum lagi mempelajari cara menapis mengikut lajur.
Kaedah 3. Makro untuk mengalih keluar semua baris dan lajur kosong pada helaian
Anda juga boleh menggunakan makro mudah untuk mengautomasikan tugas ini. Tekan pintasan papan kekunci Alt+F11 atau pilih daripada tab pemaju - Visual Basic (Pembangun — Editor Visual Basic). Jika tab pemaju tidak kelihatan, anda boleh mendayakannya melalui Fail – Pilihan – Persediaan Reben (Fail — Pilihan — Sesuaikan Reben).
Dalam tetingkap editor Visual Basic yang terbuka, pilih arahan menu Sisipan – Modul dan dalam modul kosong yang muncul, salin dan tampal baris berikut:
Sub DeleteEmpty() Dim r As Long, rng As Julat 'удаляем пустые строки Untuk r = 1 Kepada ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count If Application.CountA(Columns(r)) = 0 Then If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns( r)) Tamat Jika Seterusnya r Jika Tidak rng Tiada Maka rng.Padam Tamat Sub
Tutup editor dan kembali ke Excel.
Sekarang tekan kombinasi Alt+F8 atau butang Makro tab pemaju. Tetingkap yang terbuka akan menyenaraikan semua makro yang tersedia untuk anda jalankan pada masa ini, termasuk makro yang baru anda buat. DeleteEmpty. Pilih dan klik butang Main (lari) – semua baris dan lajur kosong pada helaian akan dipadamkan serta-merta.
Kaedah 4: Power Query
Satu lagi cara untuk menyelesaikan masalah kami dan senario yang sangat biasa ialah mengalih keluar baris dan lajur kosong dalam Power Query.
Mula-mula, mari muatkan jadual kami ke dalam Editor Pertanyaan Power Query. Anda boleh menukarnya kepada "pintar" dinamik dengan pintasan papan kekunci Ctrl+T atau hanya pilih julat data kami dan beri nama (contohnya Tarikh) dalam bar formula, menukar kepada bernama:
Sekarang kita menggunakan arahan Data – Dapatkan data – Dari jadual / julat (Data – Dapatkan Data – Dari jadual / julat) dan memuatkan semuanya ke dalam Power Query:
Kemudian semuanya mudah:
- Kami memadamkan baris kosong dengan arahan Home – Kurangkan baris – Padam baris – Padam baris kosong (Home – Alih Baris – Alih keluar baris kosong).
- Klik kanan pada tajuk lajur Bandar pertama dan pilih perintah Nyahpivot Lajur Lain daripada menu konteks. Jadual kami akan, seperti yang dipanggil secara teknikal, dinormalisasi – ditukar kepada tiga lajur: bandar, bulan dan nilai daripada persimpangan bandar dan bulan daripada jadual asal. Keistimewaan operasi ini dalam Power Query ialah ia melangkau sel kosong dalam data sumber, iaitu apa yang kita perlukan:
- Sekarang kami melakukan operasi terbalik - kami menukar jadual yang terhasil semula menjadi dua dimensi untuk mengembalikannya kepada bentuk asalnya. Pilih lajur dengan bulan dan pada tab Transformasi memilih pasukan Lajur pangsi (Ubah — Lajur Pivot). Dalam tetingkap yang terbuka, sebagai lajur nilai, pilih yang terakhir (Nilai), dan dalam pilihan lanjutan – operasi Jangan agregat (Jangan agregat):
- Ia kekal untuk memuat naik hasil kembali ke Excel dengan arahan Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke…)
- Apakah makro, bagaimana ia berfungsi, di mana untuk menyalin teks makro, bagaimana untuk menjalankan makro?
- Mengisi semua sel kosong dalam senarai dengan nilai sel induk
- Mengalih keluar semua sel kosong daripada julat tertentu
- Mengalih keluar semua baris kosong dalam lembaran kerja dengan alat tambah PLEX