Intinya. Dalam posting ini kita melihat 3 cara untuk menyalin nilai dalam sel kosong di kolom. Tekniknya antara lain menggunakan rumus, Power Query, dan makro VBA
Tingkat keahlian. Intermediat
Tonton Tutorialnya
Tonton di YouTube & Berlangganan ke Saluran kamiUnduh File Excel
Anda dapat mengunduh file BEGIN dan FINAL jika Anda ingin mengikuti dan berlatih
Isi-Down-Teknik-MULAI. zip Unduh
Isi-Down-Teknik-FINAL. zip Unduh
Mengisi Sel Kosong
Excel memudahkan untuk mengisi, atau menyalin, nilai ke dalam sel di bawah ini. Anda cukup mengklik dua kali atau menyeret gagang isian untuk sel yang ingin Anda salin, untuk mengisi sel di bawahnya dengan nilai yang sama
Namun apakah ada cara mudah untuk mereplikasi proses tersebut ratusan kali untuk laporan yang memiliki data dalam jumlah besar?
Dalam posting ini kita akan melihat tiga cara untuk mengotomatiskan proses ini. rumus sederhana, Power Query, dan makro VBA
1. Mengisi Bawah Menggunakan Rumus
Cara pertama untuk mengatasi masalah ini adalah dengan menggunakan rumus yang sangat sederhana di semua sel kosong yang mereferensikan sel di atasnya. Berikut langkah-langkahnya
Langkah 1. Pilih Sel Kosong
Untuk memilih sel kosong dalam kolom dan mengisinya dengan rumus, kita mulai dengan memilih semua sel (termasuk sel berpopulasi). Ada banyak cara untuk melakukannya, termasuk menahan tombol Shift saat Anda menavigasi ke bagian bawah kolom, atau jika data Anda ada di Tabel Excel, menggunakan pintasan keyboard Ctrl+Spasi
Lihat posting saya di 7 Pintasan Keyboard untuk Memilih Sel dan Rentang di Excel dan 5 Pintasan Keyboard untuk Baris dan Kolom di Excel untuk mempelajari lebih lanjut
Dengan semua sel yang dipilih, kita dapat mengurangi pilihan kita untuk hanya menyertakan sel kosong. Ini dilakukan dengan menggunakan jendela Buka Spesial dari menu Temukan & Pilih di tab Beranda di pita
Alternatifnya adalah dengan membuka jendela Go To menggunakan F5 atau Ctrl+G. Kemudian tekan tombol Spesial untuk jendela Ke Spesial
Setelah jendela Go To Special terbuka, Anda dapat memilih opsi yang bertuliskan Blanks. Ini hanya akan memilih sel kosong dari pilihan saat ini
Saat Anda menekan OK, Anda akan melihat bahwa sel yang memiliki nilai di dalamnya tidak lagi dipilih
Langkah 2. Tulis Formulanya
Dari sini Anda bisa mulai mengetik rumus yang sangat sederhana. Ketik tanda sama dengan (=) lalu rujuk sel di atas (dalam kasus contoh kita, B2). Itu semua yang Anda butuhkan untuk formula
Langkah 3. Ctrl+Masukkan Rumus
Setelah menuliskan rumus, jangan langsung tekan Enter. Sebagai gantinya, gunakan Ctrl+Enter untuk mengisi semua sel yang dipilih dengan rumus yang sama. Tahan tombol Ctrl, lalu tekan Enter
Karena referensi rumus Anda relatif (B2), bukan absolut ($B$2), setiap sel hanya akan menyalin nilai sel tepat di atasnya
Langkah 4. Salin & Tempel Nilai
Merupakan ide bagus untuk sekarang menyalin seluruh kolom data dan kemudian menempelkannya menggunakan opsi Tempel Nilai sehingga datanya dikodekan dengan keras. Kemudian, jika Anda melakukan penyortiran apa pun, data tidak akan berubah. Anda dapat menemukan opsi Tempel Nilai pada tab Beranda di menu drop-down Tempel
Ada cara lain untuk menempelkan nilai, termasuk menu klik kanan atau menggunakan pintasan keyboard. Posting ini menjelaskan opsi tersebut
Tempel Nilai dengan Klik Kanan & Seret Pintasan Mouse
5 Pintasan Keyboard untuk Menempelkan Nilai di Excel
2. Mengisi Menggunakan Power Query
Untuk opsi ini, data Anda harus dalam format Tabel Excel. Dari mana saja di dalam tabel, Anda dapat memilih tab Data atau Power Query, lalu memilih Dari Tabel/Rentang. Anda juga bisa membuat kueri yang terhubung ke sumber data yang berbeda seperti database atau web
Ini membuka Editor Power Query. Di Power Query, sel kosong diberi label sebagai null di setiap sel
Untuk mengisi, cukup klik kanan pada tajuk kolom dan pilih Isi lalu Turun
Power Query akan mengisi setiap bagian sel kosong di kolom dengan nilai dari sel di atasnya
Saat Anda mengklik Tutup & Muat, lembar baru akan ditambahkan ke buku kerja dengan perubahan ini
Proses pengisian ini dapat dilakukan untuk banyak kolom secara bersamaan dengan terlebih dahulu memilih beberapa kolom dengan Ctrl atau Shift, kemudian melakukan operasi Fill > Down
Jika Power Query agak baru bagi Anda, saya mengundang Anda untuk melihat postingan ini. Ikhtisar Power Query. Pengantar Alat Data Excel yang Paling Kuat
Webinar Gratis tentang Power Query, VBA, & Lainnya
Saya juga memiliki webinar gratis yang sedang berjalan saat ini yang mencakup pengantar Power Query dan alat Excel lainnya seperti Power Pivot, Power BI, Makro & VBA, tabel pivot, dan lainnya
Klik di sini untuk mendaftar webinar
3. Mengisi Menggunakan Makro
Untuk mengisi menggunakan makro, mulailah dengan membuka VB Editor. Anda dapat melakukan ini dengan masuk ke tab Pengembang dan mengklik tombol Visual Basic atau dengan menggunakan pintasan keyboard Alt+F11. Sisipkan modul kode baru, lalu tulis makro Anda
Makro ini melewati semua sel dalam rentang yang dipilih dan melakukan metode FillDown jika sel kosong. Metode FillDown menyalin nilai dari sel di atas
Jika ini adalah makro yang sering Anda gunakan, Anda mungkin ingin mempertimbangkan untuk menambahkan makro ini ke Buku Kerja Makro pribadi Anda dan membuat tombol yang dikustomisasi untuknya. Itulah yang telah saya lakukan dan terlihat seperti ini di Pita saya
Untuk instruksi tentang cara membuat Buku Kerja Makro Pribadi dan tombol Pita khusus, lihat tutorial ini. Cara Membuat Buku Kerja Makro Pribadi (Serial Video)
Pro dan kontra
Jadi, mana dari ketiga metode ini yang tepat untuk Anda?
- Metode rumusnya sederhana dan mudah diterapkan. Itu bisa dilakukan tanpa sepengetahuan Power Query atau VBA. Namun, ini tidak secepat solusi tombol sekali klik
- Power Query memerlukan data sumber yang dihasilkan menjadi lembar kerja terpisah, jadi ini lebih merupakan proses daripada sekadar mengisi sel kosong. Jika Anda sudah melakukan pembersihan data secara teratur untuk hal-hal lain, menambahkan ini ke proses Anda yang sudah ada mungkin lebih masuk akal
- Setelah makro awal disiapkan, VBA adalah solusi cepat yang mudah digunakan—cukup klik tombol. Namun, ada kerugian besar dari metode ini. Anda tidak dapat membatalkan tindakan Anda setelah tombol itu ditekan, jadi Anda harus memastikan untuk menyimpan salinan cadangan sebelumnya jika itu menjadi perhatian Anda.
Kesimpulan
Ada tiga metode berbeda untuk tugas Excel yang sangat umum. Apakah saya melewatkan sesuatu? . terima kasih. 🙂
Sebelumnya FILTER Formula untuk Mengembalikan Kolom yang Tidak Berdekatan dalam Urutan Apa Pun
Berikutnya Vlookup ke Workbook Lain – Mengelola, Memperbarui, dan Berbagi File dengan Formula Link
Anda mungkin juga suka
Referensi Seluruh Kolom dapat membuat Anda MASALAH dengan Excel
Tantangan Rumus Excel. Plat Genap atau Ganjil
Cara Memperbaiki dan Mencegah Kesalahan #VALUE dengan VLOOKUP
Kapan menggunakan VLOOKUP alih-alih XLOOKUP
kata malaikat .
Hai Jon, apakah Anda memiliki video atau tutorial tentang mengimpor data menggunakan Power Query saat nomor dan urutan kolom berubah?
Contoh (anggap semua kolom memiliki data numerik dan [A], [B], [C], [D], [F] adalah header)
Laporan bulan 1 berisi kolom-kolom ini
[A] [B] [C] [D]
Laporan bulan 2 berisi kolom-kolom ini
[A] [C] [D] [F]
Laporan bulan 3 berisi kolom-kolom ini
[B] [F]
kata Arif .
Video luar biasa lainnya Jon
kata Sid .
Hai, yang di sana,
Saya telah menggunakan Excel & VBA selama 20~25 tahun, dan saya tidak tahu tentang isi dbl-klik. -HAI
Jadi, berlangganan buletin Anda, tetapi saya tidak melihat cara untuk menjadi anggota?
Terima kasih - situs yang bagus btw
kata Jian .
Video yang luar biasa, sangat bermanfaat. terima kasih, Yohanes
kata Shayla .
Hei Jon. Saya telah menggunakan tombol GoTo untuk memilih sel kosong untuk sementara waktu, jadi saya senang melihat makro untuk itu. Ketika saya mencoba milik Anda, itu mengunci komputer saya karena saya memilih seluruh kolom karena jumlah baris dalam data saya berfluktuasi. Namun, ketika saya mencoba menambahkan perbaikan "jangkauan aktif" untuk memenuhi kebutuhan saya, saya menemukan bahwa metode GoTo (Pilihan. Sel Khusus(xlCellTypeBlanks). Pilih) di awal makro melakukan pekerjaan itu. Terima kasih atas insentif untuk mengotomatiskan tugas harian lainnya
3 kata kunci - Excel dengan VBA mengatakan.
[…] Източник. 3 Cara Mengisi Sel Kosong. […]
kata Adrian .
Saya telah menggunakan kode VBA kosong yang telah saya tambahkan ke sabuk alat pribadi saya dan berfungsi dengan baik. Saya memiliki masalah sekarang dengan beberapa data
Saya memiliki rumus dalam sel yang mengembalikan nilai atau membiarkan sel kosong =IF(ISBLANK('AR Balance Pivot'). B4),””,’AR Balance Pivot’. B4). Namun ketika saya kemudian mencoba menggunakan makro isian kosong itu tidak akan berfungsi karena sel sebenarnya tidak kosong tetapi berisi rumus, itu adalah hasil rumus yang ditampilkan yang kosong
Apakah ada cara untuk mengubah kode isian kosong sehingga memperlakukan hasil rumus kosong sebagai sel kosong?
kata Scott .
Ini luar biasa. Saya telah mencoba mencari solusi untuk ini selama beberapa waktu. Terima kasih untuk ini – ini adalah penghemat waktu yang sangat besar
kata Jeff Coulter .
Terimakasih atas infonya. Sayangnya, metode rumus ini digunakan untuk saya, tetapi sekarang tidak lagi untuk saya, ctrl+shift menempelkan fungsi yang sama ke setiap sel kosong yang dipilih =a1, =a1, =a1. Itu tidak menempel sebagai rumus inkremental =a1, =a2, =a3
yl wong berkata.
Dear Jon, ada metode yang lebih sederhana. Pilih sel yang terisi ditambah sel kosong di bawah (atau ke kanan jika Anda ingin menyalin secara horizontal) dan tekan CTRL+D (atau CTRL+R untuk penyalinan horizontal)
kata Sean .
Itu akan mengisi semua sel dengan nilai di sel yang dipilih paling atas. Metode yang dijelaskan digunakan saat menyalin untuk mengisi celah di antara nilai yang berbeda
kata JAVIER SILVA .
Saya benci kodenya tidak mudah disalin
kata Abe .
Saya butuh bantuan untuk menduplikasi satu kolom dengan 10 sel di bawah baris dan menduplikasinya di bawah satu sama lain di kolom yang sama. Metode ini hanya berfungsi jika Anda memiliki satu sel dan menggandakan di bawahnya. Bagaimana jika Anda memiliki banyak (katakanlah 5) dan perlu menduplikasi 5 itu di bawah satu sama lain dalam satu kolom?
kata Kirt .
Terima kasih banyak. Inilah yang saya butuhkan untuk membuat laporan di tempat kerja
kata Dani Figueiro .
Terima kasih banyak. Itu sangat membantu 🙂
kata Anand PN .
CT 9/6/2022 16. 42 Aktif Belum mulai lagi_vert
Tan Cheung Meng
1000800. IEEE-000114
NN 9/6/2022 16. 42 Aktif Belum mulai lagi_vert
Nursari Napitupulu
10000900. AEe-185476
jadi untuk data seperti diatas jika saya harus menarik cell kedua “Tan Cheung Meng” dan cell ketiga “1000800. IEEE-000114” ke baris yang sama yang menunjukkan aktif, rumus VBA mana yang dapat saya gunakan?