Isi Sel Kosong Sehingga Nilai Seterusnya dalam Excel (3 Cara Mudah)

Banyak kali, anda mungkin menemui set data Excel di mana hanya satu sel diisi dengan data dan sel di bawahnya kosong sehingga nilai seterusnya.

Seperti yang ditunjukkan di bawah:

Walaupun format ini berfungsi untuk sesetengah orang, masalah dengan data jenis ini ialah anda tidak boleh menggunakannya untuk membuat jadual pangsi atau menggunakannya dalam pengiraan.

Ia mudah untuk dibaiki!

Dalam tutorial ini saya akan menunjukkan kepada anda bagaimanaIsi sel dengan cepat sehingga nilai isian seterusnya dalam Excel.

Anda boleh melakukan ini dengan mudah menggunakan teknik dialog khas Go-To yang mudah, VBA atau Power Query.

Jadi mari kita mulakan!

Kaedah 1 – Isikan menggunakan formula Pergi Ke Khas +

Katakan anda mempunyai set data seperti yang ditunjukkan di bawah dan anda ingin mengisi data dalam lajur A dan B.

Dalam lajur B, matlamatnya adalah untuk mengisi "pencetak" sehingga sel kosong terakhir di bawahnya, kemudian apabila "pengimbas" bermula, kemudian isi "pengimbas" dalam sel di bawah sehingga sel kosong.

Langkah-langkah di bawah gunakan pergi ke khas untuk memilih semua sel kosong dan isi dengan formula mudah:

1. Pilih data untuk diisi (A1:D21 dalam contoh kami)
2. Pergi ke tab "Rumah".

3. Dalam kumpulan edit, klik ikon Cari dan Pilih (ini akan mendedahkan lebih banyak pilihan dalam menu lungsur)
4. Klik pada pilihan "Pergi".

5. Dalam dialog Pergi ke yang terbuka, klik butang Khas.Ini akan membuka dialog "Pergi ke Khas".

6. Dalam dialog Pergi ke Khas, klik Kosong

7. Klik OK

Langkah di atas akan memilih semua sel kosong dalam set data (ditunjukkan di bawah).

Di antara sel kosong yang dipilih, anda akan melihat bahawa satu sel lebih cerah daripada pilihan yang lain.Sel ini ialah sel aktif di mana kita ingin memasukkan formula.

Jangan risau tentang kedudukan sel dalam pemilihan, kerana kaedah kami akan berfungsi dalam apa jua keadaan.

Sekarang, berikut ialah langkah-langkah untuk mengisi data dalam sel kosong yang dipilih:

1. Klik kekunci tanda sama (=) pada papan kekunci anda.Ini akan memasukkan tanda sama dalam sel aktif
2. Tekan kekunci anak panah atas.Ini akan memasukkan rujukan sel sel di atas sel aktif.Sebagai contoh, dalam kes kita, B3 ialah sel aktif, dan apabila kita melakukan dua langkah ini, ia memasuki =B2 dalam sel
3. Tahan kekunci Kawalan dan tekan Enter

Langkah di atas secara automatik akan memasukkan semua sel kosong dengan nilai di atas.

Walaupun ini mungkin kelihatan seperti terlalu banyak langkah, setelah anda membiasakannya, anda akan dapat mengisi data dalam Excel dengan cepat dalam beberapa saat.

Kini, terdapat dua lagi perkara yang perlu anda ketahui apabila menggunakan kaedah ini.

Tukar formula kepada nilai

Yang pertama ialah memastikan anda menukar formula kepada nilai (supaya anda mempunyai nilai statik dan jangan kacau jika anda menukar data pada masa hadapan).

Tukar format tarikh

Jika anda menggunakan tarikh dalam data anda (seperti yang saya lakukan dalam data contoh saya), anda akan melihat bahawa nilai yang diisi dalam lajur tarikh ialah nombor dan bukannya tarikh.

Jika output dalam lajur tarikh adalah dalam format tarikh yang diingini maka anda baik dan tidak perlu melakukan apa-apa lagi.

Tetapi jika tarikh tersebut tidak dalam format yang betul, penalaan halus diperlukan.Walaupun anda mempunyai nilai yang betul, anda hanya perlu menukar format supaya ia muncul sebagai tarikh dalam sel.

Berikut adalah langkah-langkah untuk melakukan ini:

1. Pilih lajur yang mengandungi tarikh
2. Klik tab Laman Utama
3. Dalam kumpulan Nombor, klik menu lungsur Format dan pilih format tarikh.

Jika anda perlu melakukan padding ini dari semasa ke semasa, saya syorkan teknik Go-To khas dan formula ini.

Walaupun ia mempunyai beberapa langkah, ia mudah dan memberikan anda hasil dalam set data.

Tetapi jika anda perlu melakukan ini dengan kerap maka saya cadangkan anda untuk melihat VBA dan kaedah Power Query yang diterangkan seterusnya

Kaedah 2 - Isi dengan kod VBA mudah

Anda boleh menggunakan kod makro VBA yang sangat mudah untuk mengisi sel dengan cepat dalam Excel sehingga nilai kosong terakhir.

Anda hanya perlu menambah kod VBA pada fail sekali, kemudian anda boleh menggunakan semula kod itu beberapa kali dengan mudah dalam buku kerja yang sama atau bahkan merentas berbilang buku kerja pada sistem anda.

Berikut ialah kod VBA yang akan berulang melalui setiap sel dalam pemilihan dan mengisi mana-mana sel kosong:

Sub Isi Bawah() Untuk Setiap sel Dalam Pilihan Jika sel = "" Kemudian sel.Isi Bawah Tamat Jika Seterusnya Tamat Sub

Kod di atas menggunakan gelung For untuk lelaran melalui setiap sel dalam pemilihan.

Dalam gelung For, saya menggunakan syarat If-Then untuk menyemak sama ada sel itu kosong.

Jika sel kosong, ia diisi dengan nilai dari sel di atas, jika tidak, gelung for mengabaikan sel dan bergerak ke sel seterusnya.

Sekarang anda mempunyai kod VBA, izinkan saya menunjukkan kepada anda tempat untuk meletakkan kod ini dalam Excel:

1. Pilih data untuk diisi
2. Klik pada tab pembangun

3. Klik ikon Visual Basic (atau anda boleh menggunakan pintasan papan kekunci ALT+F11).Ini akan membuka editor Visual Basic dalam Excel

4. Dalam editor Visual Basic di sebelah kiri, anda akan mempunyai Project Explorer.Jika anda tidak melihatnya, klik pilihan Lihat dalam menu, kemudian klik Project Explorer

5. Jika anda mempunyai berbilang buku kerja Excel terbuka, Project Explorer memaparkan semua nama buku kerja.Cari nama buku kerja tempat anda mempunyai data.
6. Klik kanan mana-mana objek dalam buku kerja, pergi ke Sisipkan dan klik Modul.Ini akan memasukkan modul baharu untuk buku kerja ini

7. Klik dua kali pada "Modul" yang baru anda masukkan dalam langkah di atas.Ia akan membuka tetingkap kod untuk modul itu

8. Salin dan tampal kod VBA ke dalam tetingkap kod
9. Letakkan kursor di mana-mana dalam kod dan jalankan kod makro dengan mengklik butang hijau dalam bar alat atau menggunakan pintasan papan kekunci F5

Langkah di atas akan menjalankan kod VBA dan data anda akan diisi.

Jika anda ingin menggunakan kod VBA ini semula kemudian, anda perlu menyimpan fail ini sebagai buku kerja Excel yang didayakan makro (sambungan .XLSM)

Perkara lain yang boleh anda lakukan ialah menambah makro ini pada Bar Alat Akses Pantas anda, yang sentiasa kelihatan dan anda boleh mengakses makro ini dengan hanya satu klik (dalam buku kerja dengan kod di bahagian belakang).

Jadi pada kali seterusnya anda mempunyai data yang perlu diisi, anda hanya boleh membuat pilihan dan klik butang makro dalam Bar Alat Akses Pantas.

Anda juga boleh menambah makro ini pada buku kerja makro peribadi anda dan kemudian menggunakannya dalam mana-mana buku kerja pada sistem anda.

Klik di sini untuk membaca lebih lanjut tentang buku kerja makro peribadi anda dan cara menambah kod padanya.

Kaedah 3 - Isi menggunakan Power Query

Kuasa KuasaMempunyai fungsi terbina dalam yang membolehkan anda mengisinya dengan satu klik.

Adalah disyorkan apabila anda masih menggunakan Power Query untuk mengubah data atau menggabungkan data daripada berbilang lembaran kerja atau berbilang buku kerja.

Sebagai sebahagian daripada aliran kerja sedia ada, anda boleh mengisi sel kosong dengan cepat menggunakan pilihan Isi dalam Power Query.

Untuk menggunakan Power Query, adalah disyorkan agar data anda berada dalam format jadual Excel.Jika anda tidak boleh menukar data kepada jadual Excel, anda mesti mencipta julat bernama untuk data dan kemudian menggunakan julat bernama itu dalam Power Query.

Di bawah saya mempunyai set data yang telah ditukar kepada jadual Excel.Anda boleh melakukan ini dengan memilih set data, pergi ke tab Sisipkan dan mengklik ikon Jadual.

Berikut ialah langkah-langkah untuk menggunakan Power Query untuk mengisi data sehingga nilai seterusnya:

1. Pilih mana-mana sel dalam set data
2. Klik tab Data

3. Dalam kumpulan Dapatkan dan Ubah Data, klik Daripada Lembaran Kerja.Ini akan membuka editor Power Query.Ambil perhatian bahawa sel kosong akan memaparkan nilai "null" dalam Power Query

4. Dalam Editor Power Query, pilih lajur yang ingin anda isi.Untuk melakukan ini, tahan kekunci Kawalan dan klik pengepala lajur yang anda mahu pilih.Dalam contoh kami, lajur tarikh dan produk.
5. Klik kanan pada mana-mana tajuk yang dipilih

6. Pergi ke pilihan Isi dan klik Bawah.Ini akan mengisi semua sel kosong dengan data

7. Klik tab Fail, dan kemudian klik Tutup dan Muatkan.Ini akan memasukkan lembaran kerja baharu dengan jadual hasil dalam buku kerja

Walaupun pendekatan ini mungkin terdengar seperti berlebihan, salah satu faedah hebat menggunakan Power Query ialah ia membolehkan anda mengemas kini data yang terhasil dengan cepat apabila data asal berubah.

Sebagai contoh, jika anda menambah lebih banyak rekod pada data asal, anda tidak perlu melakukan semua perkara di atas sekali lagi.Anda boleh klik kanan pada jadual yang terhasil dan klik muat semula.

Walaupun kaedah Power Query berfungsi dengan baik, sebaiknya gunakannya apabila anda sudah menggunakan Power Query dalam aliran kerja anda.

Jika anda hanya mempunyai satu set data dengan sel kosong untuk diisi, lebih mudah untuk menggunakan kaedah khas Pergi Ke atau kaedah VBA (diterangkan di atas).

Jadi anda boleh menggunakan tiga kaedah mudah ini untukIsi sel kosong sehingga nilai seterusnya dalam Excel.

Saya harap anda mendapati tutorial ini berguna.