Sel yang digabungkan menjadi masalah saat ini, tapi ini adalah sel gabungan vertikal. Perhatikan gambar di bawah ini. Jika ada 7 baris untuk wilayah Midwest, kata Midwest hanya muncul di baris pertama. Seseorang kemudian menggabungkan enam sel kosong yang tersisa dengan sel Midwest untuk membuat sel gabungan setinggi 7 sel.
Ini adalah cara yang mengerikan untuk mengirimkan data. Jika seseorang mahir dalam Excel, mereka mungkin ingin mengurutkan data, menambahkan subtotal, menggunakan tabel pivot. Sel yang digabungkan mencegah hal ini.
Lebih buruk lagi, Anda mendapatkan file baru dari kantor pusat setiap hari dengan struktur seperti ini. Kantor pusat mengirimkan file serupa ke 500 kantor cabang. Menjadi hanya satu pos terdepan kecil, Anda tidak memiliki banyak kesempatan untuk meyakinkan Kantor Pusat bahwa ini adalah cara yang mengerikan untuk mengirimkan data.
Artikel hari ini adalah tentang cara cepat menyelesaikan masalah ini dengan Power Query. Solusi Power Query akan lebih mudah pada hari ke-2 hingga 9999 daripada langkah-langkah berikut di Excel:
Guru Excel mungkin menyarankan langkah-langkah ini setiap hari:
- Pilih semua sel dengan memilih persegi panjang di atas dan di sebelah kiri A1.
- Klik peluncur dialog di sudut kanan bawah grup Alignment dari tab Home.
- Klik kotak Gabung Sel dua kali untuk membatalkan pilihannya.
- Klik OK untuk menutup dialog Format Cells
- Pilih dari ujung kolom A kembali ke A1.
- Beranda, Temukan & Pilih, Ke Spesial, Kosong, OK
- Ketik = UpArrow. Tekan Ctrl + Enter
- Pilih dari ujung kolom A kembali ke A1.
- Ctrl + C untuk menyalin. Klik kanan dan Tempel Nilai
Tapi ada cara yang lebih mudah. Alat Power Query baru disertakan dalam Office 365 dan Excel versi Windows 2016. Jika Anda memiliki versi Windows Excel 2010 atau Excel 2013, Anda dapat mengunduh Power Query secara gratis dari Microsoft.
Berikut adalah strategi dengan Power Query:
- Buatlah rencana bahwa Anda akan menyimpan buku kerja setiap hari dari HQ di folder yang sama dengan nama yang sama.
- Buka buku kerja kosong baru yang akan menyimpan data tetap dari HQ.
-
Di buku kerja kosong, pilih Data, Dapatkan Data, Dari File, Dari Buku Kerja.
Mulai proses Power Query - Jelajahi folder dan file dari langkah # 1.
-
Saat jendela Power Query terbuka, perhatikan bahwa sel yang digabungkan telah hilang. Anda memiliki Midwest di baris 1 dan kemudian enam sel yang berisi "null". Pilih kolom ini dengan mengklik tajuk Wilayah.
Power Query secara otomatis menghapus sel yang digabungkan -
Di Power Query, pilih tab Transformasi. Pilih Isi, Bawah. Kata Midwest disalin dari baris 1 ke baris 2 sampai 7. Transformasi serupa terjadi di seluruh kumpulan data.
Isi sel nol dengan nilai dari atas -
Beranda, Tutup & Muat. Power Query akan ditutup. Dalam waktu sekitar 10-20 detik, data yang dibersihkan dari HQ akan muncul di lembar kerja baru di buku kerja.
Kembalikan data ke Excel - Simpan buku kerja dengan nama seperti CleanedDataFromHQ.xlsx.
- Ketika data dipilih, Anda akan melihat panel Queries & Connections di sisi kanan. Jika Anda tidak melihat panel, buka Data, Kueri & Koneksi.
-
Klik kanan kueri di panel Queries & Connections. Pilih Properties.
Buka Properti untuk kueri ini -
Pilih "Refresh Data Saat Membuka File". Klik OK.
Atur kueri untuk dijalankan setiap kali Anda membuka buku kerja.
Alur kerja Anda kemudian menjadi: (a) Menerima buku kerja dari Kantor Pusat di email. (b) Simpan lampiran di folder yang benar dengan nama yang benar. (c) Buka buku kerja CleanedDataFromHQ.xlsx. Data baru akan dimuat ke dalam buku kerja.
Menonton video
Transkrip Video
Pelajari Excel dari Podcast, Episode 2221: Isi Sel yang Digabung ke Bawah.
Hei, selamat datang kembali di netcast. Saya Bill Jelen, saya berada di Kota Calumet, Elizabeth muncul dan berkata dia mendapatkan file ini-- file kami, seperti file ini - setiap hari dan itu mengerikan. Di sini, di Kolom A, bukan hanya mereka meletakkan Midwest dan meninggalkan banyak kekosongan, ini adalah sel gabungan. Baik? Mereka mengirim-- Markas Besar mengirimkan file-file ini ke lokasi-lokasi di seluruh dunia dengan sel gabungan yang aneh di sini. Sel yang digabungkan itu jahat.
Baiklah, berikut adalah cara menyingkirkan ini. Pertama, saya akan mengklik kanan, Pindahkan atau Salin, Buat salinan, dan pindahkan ke buku baru. Jadi kami menganggap ini seperti buku kerja yang didapat Elizabeth. File, Save As, dan setiap kali Elizabeth mendapatkan salah satu buku kerja ini, saya ingin dia meletakkannya di tempat yang sama persis dengan nama yang sama persis. Baik? Nama yang persis sama-- sempurna. Jadi sekarang kita hanya memiliki satu file, satu lembar, saya akan menutup file. Dan kemudian kita akan membuat buku kerja baru. Saya hanya akan memasukkan lembar kerja baru di sini dan lembar kerja baru ini akan disebut Laporan. Dan di bagian kosong ini, Laporan kami akan melakukan Data, Dapatkan Data, Dari File, Dari Buku Kerja, arahkan ke file tempat kami akan menyimpannya-- jadi, dengan nama yang persis sama setiap saat-- klik Impor , pilih lembar kerja pertama,lalu klik Edit. Baik. Pertama, ini sangat luar biasa - kueri daya tidak akan memiliki omong kosong sel gabungan ini, mereka langsung mengubah sel gabungan menjadi sel individu dan semua sel ini nol. Pilih kolom itu, itu sudah dipilih di sini, lalu Transformasi, Isi, Isi, seperti itu, lalu Beranda, Tutup & Muat. Baiklah, jadi, ini buku kerja saya.
Sekarang, saya melihat beberapa memiliki beberapa kolom tambahan di sini. Biarkan saya klik kanan dan kami akan Edit.
Dan sepertinya apa pun yang sesuai dengan biaya harus disingkirkan. Berapa banyak barang ekstra di luar sana? Klik kanan dan Hapus kolom itu-- sempurna-- Tutup & Muat. Baiklah, sekarang, alur kerjanya menjadi, setiap kali Elizabeth mendapat laporan baru dari kantor pusat, Dia akan menyimpannya di tempat yang sama dengan nama yang dapat diandalkan atau apa pun-- apa pun-- kami menyebutnya, lalu buka buku kerja ini, dan lalu di sini klik Refresh.
Atau, jika kami ingin bekerja secara otomatis, klik kanan di sini, buka Properti, tepat di luar layar Anda sampai ke bagian bawah, lalu katakan, Setiap kali saya membuka file ini, segarkan data. Jadi setiap kali saya membuka buku kerja ini, buku kerja ini akan keluar ke buku kerja baru yang kami dapatkan dari kantor pusat, itu akan menggantikan sel gabungan kosong itu dengan nilai yang seharusnya ada di sana, dan hidup menjadi hebat seperti itu.
Power Query tercakup dalam buku saya, tetapi juga tercakup dalam buku ini oleh Ken Puls dan Miguel Escobar, M adalah untuk (DATA) MONKEY.
Baiklah, jadi, Elizabeth: Bagaimana cara menghilangkan sel gabungan vertikal? Kantor pusat saya terus mengirimi saya file-file ini setiap hari. Jadi, rencananya: Kami akan menyimpan buku kerja di tempat yang dapat diandalkan dengan nama yang dapat diandalkan; buat workbook pelaporan kosong, Data, Get Data, From File, From Workbook, tentukan sheetnya, BAM! Sel gabungan hilang. Saya memilih kolom itu dan Fill Down, Close & Load; lalu setiap kali Anda mendapatkan buku kerja baru, simpan buku kerja baru itu di tempat yang dapat diandalkan dengan nama yang sama dan dapat diandalkan; Saya membuka buku kerja Pelaporan dan Refresh, atau kami hanya menyetel buku kerja Pelaporan ke Refresh secara otomatis saat dibuka.
Untuk mengunduh buku kerja dari video hari ini adalah URL dalam deskripsi YouTube.
Ingin berterima kasih kepada Elizabeth karena telah mengikuti seminar saya di Calumet City, saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mengunduh file excel: fill-merged-cells-down.xlsx
Power Query adalah alat yang luar biasa - ini memungkinkan Anda untuk memecahkan berbagai masalah
saeed Alimohammadi
