Ini adalah makro serbaguna dan mandiri yang membuka buku kerja lain, mendapatkan informasi darinya, lalu menutup buku kerja lain itu. Ini terjadi begitu cepat sehingga Anda bahkan tidak menyadari apa yang sedang terjadi; Show
(Teknik ini juga memungkinkan Anda untuk mengubah data atau memasukkan data ke buku kerja Excel lain menggunakan VBA dan makro. ) Makro untuk Mendapatkan Data dari Buku Kerja LainSub get_data() Dim Wb1 As Workbook 'Dont update the screen - makes it work faster Application.ScreenUpdating = False 'Open the other workbook 'Input the FULL path to the file, including its extension Set Wb1 = Workbooks.Open("C:\sample.xls") 'You can do whatever you want here with the other workbook - it is now open. 'This just copies some cells. Wb1.Sheets(1).Range("A1:A10").Copy 'Close the workbook from which we just got some data and make sure not to save it in case accidental changes were made to it. Wb1.Close SaveChanges:=False 'Turn on screen updating again - makes Excel usable Application.ScreenUpdating = True End Sub Cara menggunakan MakroC. \Sampel. xls adalah jalur ke file Excel yang ingin Anda buka. Ini harus jalan yang lengkap. Bisa untuk file yang terletak di komputer Anda atau melalui jaringan. Juga, jangan lupa ekstensi file Wb1 adalah objek yang mereferensikan buku kerja yang dibuka makro. Itu berarti kapan pun Anda ingin mereferensikan buku kerja ini dalam kode, Anda menulis Wb1 lalu memilih lembar kerja dan melakukan sesuatu Misalnya, untuk memilih sel A1 pada Sheet1, Anda akan melakukannya. Wb1. Lembar("Lembar1"). Rentang("A1"). Pilih Pastikan Anda meletakkan semua kode yang mendapatkan data dari buku kerja setelah baris yang membuka buku kerja. Tetapkan Wb1 = Buku Kerja. Buka("C. \Sampel. xls"), dan sebelum baris kode yang menutup buku kerja. Wb1. Tutup Simpan Perubahan. =Salah Jika Anda ingin menyimpan setiap perubahan yang dibuat di buku kerja yang Anda buka, ubah False menjadi True di baris ini. Wb1. Tutup Simpan Perubahan. =Salah CatatanMakro ini memungkinkan Anda mendapatkan data dari buku kerja lain, atau memasukkan data ke dalamnya, atau melakukan apa pun dengan buku kerja itu Kode tersebut adalah templat yang memungkinkan Anda mengakses file Excel lain dengan mudah. Power query bisa sangat membantu saat Anda ingin menggabungkan beberapa buku kerja menjadi satu buku kerja tunggal Misalnya, Anda memiliki data penjualan untuk berbagai wilayah (Timur, Barat, Utara, dan Selatan). Anda bisa menggabungkan data ini dari buku kerja yang berbeda ke dalam satu lembar kerja menggunakan Power Query Jika Anda memiliki buku kerja ini di lokasi/folder yang berbeda, sebaiknya pindahkan semua ini ke dalam satu folder (atau buat salinan dan letakkan salinan buku kerja itu di folder yang sama) Jadi untuk memulainya, saya memiliki empat buku kerja dalam satu folder (seperti yang ditunjukkan di bawah) Sekarang, dalam tutorial ini, saya membahas tiga skenario di mana Anda bisa menggabungkan data dari buku kerja yang berbeda menggunakan Power Query
Mari kita lihat cara menggabungkan data dari buku kerja ini di setiap kasus Tutorial ini Mencakup Setiap buku kerja memiliki data dalam Tabel Excel dengan struktur yang samaTeknik di bawah ini akan bekerja ketika Tabel Excel Anda telah disusun dengan cara yang sama (nama kolom yang sama) Jumlah baris di setiap tabel bisa berbeda-beda Jangan khawatir jika beberapa Tabel Excel memiliki kolom tambahan. Anda dapat memilih salah satu Tabel sebagai templat (atau sebagai 'kunci' sebagaimana Power Query menyebutnya), dan Power Query akan menggunakannya untuk menggabungkan semua Tabel Excel lainnya dengannya Jika ada kolom tambahan di tabel lain, kolom tersebut akan diabaikan dan hanya kolom yang ditentukan di template/kunci yang akan digabungkan. Misalnya, jika templat/tabel kunci yang Anda pilih memiliki 5 kolom, dan salah satu tabel di buku kerja lain memiliki 2 kolom tambahan, kolom tambahan tersebut akan diabaikan Sekarang saya memiliki empat buku kerja dalam satu folder yang ingin saya gabungkan Di bawah ini adalah snapshot dari tabel yang saya miliki di salah satu buku kerja Berikut adalah langkah-langkah untuk menggabungkan data dari buku kerja ini menjadi satu buku kerja (sebagai satu tabel)
Ini akan memuat hasil akhir (gabungan data) ke dalam lembar kerja aktif Anda Perhatikan bahwa bersama dengan data, Power Query secara otomatis menambahkan nama buku kerja sebagai kolom pertama dari gabungan data. Ini membantu dalam melacak data apa yang berasal dari buku kerja mana Jika Anda ingin mengedit data terlebih dahulu sebelum memuatnya ke Excel, pada Langkah 6, pilih 'Gabungkan dan Edit'. Ini akan membuka hasil akhir di editor Power Query tempat Anda dapat mengedit data Beberapa hal yang perlu diketahui
Menambahkan File Baru ke FolderSekarang mari luangkan waktu sebentar dan pahami apa yang kita lakukan dengan langkah-langkah di atas (yang hanya membutuhkan waktu beberapa detik) Kami menggabungkan data dari empat buku kerja berbeda dalam satu tabel dalam beberapa detik bahkan tanpa membuka buku kerja apa pun Tapi itu belum semuanya KEKUATAN sebenarnya dari Power Query adalah sekarang saat Anda menambahkan lebih banyak file ke folder, Anda tidak perlu mengulangi langkah-langkah ini Yang perlu Anda lakukan adalah memindahkan buku kerja baru di folder, menyegarkan kueri, dan secara otomatis menggabungkan data dari semua buku kerja di folder itu Misalnya, dalam contoh di atas, jika saya menambahkan buku kerja baru – ‘Mid-West. xlsx' ke folder, dan segarkan kueri, itu akan langsung memberi saya kumpulan data gabungan baru Inilah cara Anda menyegarkan kueri
Setiap buku kerja memiliki data dengan nama lembar kerja yang samaJika Anda tidak memiliki data di Tabel Excel, tetapi semua nama sheet (yang ingin Anda gabungkan datanya) adalah sama, maka Anda dapat menggunakan metode yang ditunjukkan di bagian ini Ada beberapa hal yang perlu Anda waspadai jika hanya berupa data tabular dan bukan Tabel Excel
Sekarang mari kita lihat cara menggabungkan data dengan cepat dari buku kerja yang berbeda dengan nama lembar kerja yang sama Dalam contoh ini, saya memiliki folder dengan empat file Di setiap buku kerja, saya memiliki lembar kerja dengan nama 'Data' yang berisi data dalam format berikut (perhatikan bahwa ini bukan Tabel Excel) Berikut adalah langkah-langkah untuk menggabungkan data dari beberapa workbook menjadi satu lembar kerja tunggal
Ini akan memuat hasil akhir (gabungan data) ke dalam lembar kerja aktif Anda Jika Anda ingin mengedit data terlebih dahulu sebelum memuatnya ke Excel, pada Langkah 6, pilih 'Gabungkan dan Edit'. Ini akan membuka hasil akhir di editor Power Query tempat Anda dapat mengedit data Setiap Buku Kerja memiliki data dengan Nama Tabel atau Nama Lembar yang BerbedaTerkadang, Anda mungkin tidak mendapatkan data yang terstruktur dan konsisten (seperti Tabel dengan nama yang sama atau lembar kerja dengan nama yang sama) Misalnya, Anda mendapatkan data dari seseorang yang membuat kumpulan data ini tetapi menamai lembar kerja sebagai Data Timur, Data Barat, Data Utara, dan Data Selatan Atau, orang tersebut mungkin telah membuat tabel Excel, tetapi dengan nama yang berbeda Dalam kasus seperti itu, Anda masih bisa menggunakan Power Query, tetapi Anda perlu melakukannya dengan beberapa langkah tambahan
Sekarang izinkan saya mencoba dan dengan cepat menjelaskan apa yang kami lakukan di sini. Karena tidak ada konsistensi dalam nama sheet atau nama tabel, kami menggunakan =Excel. Rumus buku kerja untuk mengambil semua objek buku kerja di Power Query. Objek ini dapat menyertakan lembar, tabel, dan rentang bernama. Setelah kami memiliki semua objek dari semua file, kami memfilternya untuk hanya mempertimbangkan Tabel Excel. Kemudian kami memperluas data dalam tabel dan menggabungkan semua ini Dalam contoh ini, kami memfilter data untuk hanya menggunakan Tabel Excel (pada Langkah 13). Jika Anda ingin menggabungkan lembar dan bukan tabel, Anda dapat memfilter lembar Catatan – teknik ini akan memberi Anda data gabungan meskipun ada ketidakcocokan dalam nama kolom. Misalnya, jika di Timur. xlsx, Anda memiliki kolom yang salah eja, Anda akan mendapatkan 5 kolom. Power Query akan mengisi data dalam kolom jika menemukannya, dan jika tidak dapat menemukan kolom, Power Query akan melaporkan nilainya sebagai 'null' Demikian pula, jika Anda memiliki beberapa kolom tambahan di salah satu lembar kerja tabel, ini akan disertakan dalam hasil akhir Sekarang jika Anda mendapatkan lebih banyak buku kerja dari mana Anda perlu menggabungkan data, cukup salin-tempel ke folder dan segarkan Power Query Anda mungkin juga menyukai Tutorial Excel Berikut
BUKU EXCEL GRATIS Dapatkan 51 Ebook Tips Excel untuk meningkatkan produktivitas Anda dan menyelesaikan pekerjaan lebih cepat
Nama Surel YA - KIRIM SAYA EBOOK 17 pemikiran pada “Menggabungkan Data dari Beberapa Buku Kerja di Excel (menggunakan Power Query)”
|