Cara menggabungkan beberapa lembar Excel menjadi satu: semua opsi

Jika Anda sering bekerja dengan banyak spreadsheet, Anda mungkin pernah mendapati diri Anda menyalin dan menempel tanpa henti untuk mengumpulkan informasi yang tersebar di berbagai tab. Gabungkan beberapa lembar Excel menjadi satu. Hal ini tidak hanya menghemat waktu: tetapi juga mengurangi kesalahan, menyederhanakan pelaporan, dan memungkinkan Anda menganalisis data dengan jauh lebih mudah.

Kabar baiknya adalah Anda tidak terbatas pada metode salin dan tempel biasa. Excel dan beberapa alat eksternal menawarkan beberapa metode. Untuk menggabungkan lembar kerja dari file yang sama, dari beberapa buku kerja yang berbeda, dengan atau tanpa duplikat, dan bahkan berdasarkan kolom umum seperti pengidentifikasi produk. Kita akan melihat, langkah demi langkah, semua alternatif praktis dan apa yang terbaik untuk digunakan dalam setiap kasus.

Apa saja opsi yang Anda miliki untuk menggabungkan beberapa lembar kerja Excel?

Sebelum kita membahas langkah-langkahnya, ada baiknya kita memiliki gambaran umum yang jelas terlebih dahulu. Ada berbagai pendekatan yang berbeda untuk menyatukan spreadsheet.Mulai dari fungsi internal Excel (Consolidate, Power Query) hingga makro VBA atau layanan web eksternal dan solusi otomatisasi seperti Coupler.io atau ASPOSE.

Metode terbaik akan bergantung pada apakah tabel Anda memiliki struktur yang sama, apakah tabel tersebut berada dalam file yang sama atau dalam folder yang berbeda, dan seberapa mahir Anda menggunakan Excel. Mengumpulkan total angka untuk laporan manajemen tidaklah sama. Menghubungkan tabel penjualan dan keuangan melalui kolom umum untuk melakukan analisis terperinci.

Gabungkan data dari beberapa lembar kerja menggunakan fungsi Konsolidasi Excel.

Salah satu cara paling klasik dan paling kurang dikenal untuk mengumpulkan informasi adalah dengan menggunakan perintah Konsolidasi di Excel. Konsolidasi memungkinkan Anda untuk meringkas data numerik yang tersebar di beberapa lembar atau buku kerja. dan mengelompokkannya pada lembar "master" dengan total, rata-rata, jumlah, dll.

Bayangkan Anda mencatat pengeluaran per cabang: satu lembar untuk setiap kantor, semuanya dengan tabel pengeluaran yang serupa. Dengan konsolidasi, Anda dapat membuat lembar data perusahaan hanya dalam beberapa klik. yang mengumpulkan data dari semua lokasi dan menghitung jumlah, rata-rata, stok, atau metrik lain yang Anda butuhkan.

Excel menawarkan dua mode konsolidasi: berdasarkan posisi y berdasarkan kategoriPilihan tersebut bergantung pada bagaimana rentang data Anda diatur di berbagai lembar kerja: apakah sel-sel tersebut sejajar dengan koordinat yang sama atau apakah Anda mengandalkan label baris dan kolom.

Konsolidasikan berdasarkan posisi: ketika semua lembar "ditata" dengan cara yang sama.

Konsolidasi posisi bekerja sangat baik ketika Semua lembar memiliki desain tabel yang sama.: kolom yang sama dalam urutan yang sama, baris yang sama dalam rangkaian yang sama, dan tidak ada spasi kosong di dalam daftar data.

Untuk memastikan sistem ini berjalan lancar, tinjau setiap lembar sumber dan Pastikan rentang data menempati sel yang persis sama. Dalam semua sel tersebut, tanpa ada baris atau kolom kosong di antaranya. Ini seperti menumpuk beberapa transparansi: Excel akan menjumlahkan atau memproses data yang berada di "sel" yang sama.

El proses umum Apakah ini:

• Buka semua lembar atau buku sumber. dengan data yang ingin Anda tambahkan atau ringkas, lalu periksa apakah tabel sudah sejajar di setiap bagian.
• Di buku kerja tempat Anda ingin menampilkan total, buka lembar tujuan dan pilih sel paling kiri atas Anda ingin saya pergi ke mana? boot tabel konsolidasi.
• Di tab data, masukkan alat yang sesuai (dalam versi bahasa Spanyol biasanya Data > Konsolidasi).
• Di jendela Konsolidasi, pilih di dalam kotak Función Jika Anda ingin menambahkan, menghitung rata-rata, menghitung jumlah, dll.
• Untuk setiap lembar sumber, pilih rentang yang berisi data yang akan dikonsolidasikan; Excel akan menambahkan setiap referensi ke dalam daftar. dari rentang yang membentuk konsolidasi tersebut.
• Setelah Anda memasukkan semua area, tekan OK agar Excel membuat tabel gabungan dengan hasilnya.

Ini adalah metode yang sangat praktis jika Anda membutuhkan, misalnya, memperoleh ringkasan numerik dari beberapa lembar periodik (bulan, delegasi, proyek) yang memiliki struktur identik.

Konsolidasikan berdasarkan kategori: ketika tag yang menentukan, bukan posisi.

Mode konsolidasi lainnya bekerja paling baik ketika Tidak semua lembar kerja memiliki baris dan kolom dalam urutan yang sama.Namun, keduanya memiliki label yang sama. Di sini, Excel menggunakan header baris dan kolom untuk menentukan apa yang akan ditambahkan ke apa.

Pendekatan ini ideal jika tabel Anda berupa daftar data tanpa spasi kosong tetapi dengan sedikit perbedaan urutan, dengan syarat bahwa Nama-nama kategori ditulis secara konsisten.Jika Anda memasukkan "Prom." di satu lembar kerja dan "Rata-rata" di lembar kerja lain, Excel akan memperlakukannya sebagai kolom terpisah dan tidak akan menggabungkannya.

Untuk menggunakan Konsolidasi berdasarkan kategori:

• Buka semua lembar sumber yang berisi data yang akan dikelompokkan.
• Pada lembar kerja tempat Anda ingin melihat ringkasan, pilih lagi sel kiri atas dari rentang keluaran.
• Akses lagi Data > Konsolidasi lalu pilih fungsi yang Anda inginkan (jumlah, rata-rata, dll.).
• Centang kotak yang menunjukkan letak label: Baris atas, Kolom kiri atau keduanya, tergantung pada judul Anda.
• Untuk setiap lembar sumber, pilih rentang data termasuk baris atau kolom tempat label berada, sehingga Excel dapat mengenali kategori-kategori tersebut..
• Setelah semua rentang ditambahkan, konfirmasi dengan OK untuk menghasilkan tabel gabungan.

Dengan cara ini kamu bisa Gabungkan data numerik yang memiliki kategori yang sama meskipun tidak sepenuhnya sejajar. pada semua lembar kerja, selama nama kolom dan barisnya konsisten.

Gabungkan beberapa lembar Excel secara manual dari dalam program itu sendiri.

Jika yang Anda inginkan bukanlah untuk mengkonsolidasikan nilai-nilai tetapi memiliki beberapa lembar dokumen yang saat ini berada dalam berkas terpisah dalam satu buku.Excel juga mempermudah proses ini dengan opsi untuk memindahkan atau menyalin lembar kerja.

Metode paling dasar melibatkan membuka file-file terkait dan, dari lembar yang ingin Anda gandakan, Gunakan opsi pemformatan untuk memindahkan atau menyalin.Ini adalah pendekatan sederhana yang tidak memerlukan rumus atau otomatisasi, sangat cocok jika hanya ada beberapa lembar kerja.

Langkah langkahSecara garis besar, berikut ini adalah daftarnya:

• Buka semua lembar Excel yang ingin Anda gabungkan menjadi satu buku kerja.
• Letakkan kursor Anda pada tab lembar kerja yang ingin Anda salin. menuju buku lain.
• Buka tab Beranda dan, di dalam grup Sel, masukkan opsi Format, di mana perintah tersebut muncul untuk pindahkan atau salin lembar.
• Di kotak yang akan muncul, Anda dapat memilih. Lembaran tersebut harus dikirim ke buku yang mana? (satu buku yang sudah terbuka atau buku baru yang akan dibuat) dan di posisi mana buku tersebut harus diletakkan.
• Pastikan Anda mencentang kotaknya Buat salinan jika Anda ingin lembar asli tetap berada di file sumbernya.

Jika halaman-halaman yang ingin Anda satukan sudah berada dalam satu buku yang sama, Anda juga bisa pilih beberapa tab sekaligus Dengan menggunakan Ctrl (untuk memilih lembar kerja individual) atau Shift (untuk rentang berurutan) dan mengulangi proses pemindahan atau penyalinan, Anda dapat menggabungkan berbagai lembar kerja dari beberapa buku kerja ke dalam satu file tanpa kehilangan file aslinya.

Menggabungkan beberapa lembar kerja dari file berbeda menggunakan kode VBA.

Ketika data tersebar luas di banyak file dan Anda ingin mengotomatiskan prosesnya, VBA (Visual Basic for Applications) adalah alat serbaguna yang sangat ampuh.Dengan makro, Anda dapat membuka folder yang berisi banyak buku kerja Excel dan menyalin semua lembar kerjanya ke file utama hanya dalam hitungan detik.

Namun janganlah kita menipu diri sendiri: Penggunaan makro memerlukan tingkat pemahaman tertentu tentang kode.Anda tidak perlu menjadi programmer profesional, tetapi Anda perlu tahu cara membuka editor Visual Basic, menempelkan makro, dan menjalankannya tanpa rasa takut.

Contoh umum makro untuk menggabungkan lembar kerja dari beberapa file melakukan hal berikut: membuka kotak pemilihan file, memungkinkan Anda untuk memilih beberapa buku kerja, Buka setiap file, gulir melalui semua halamannya, dan salin semuanya. Di bagian akhir buku utama, dan setelah selesai, tutup buku-buku sumber.

Alur kerja Ini mirip dengan hal berikut:

• Buat buku baru yang akan berfungsi sebagai file "master".
• pers ALT + F11 untuk membuka Editor Visual Basic.
• Sisipkan a Modul Buat dan tempel kode makro baru yang akan menangani penelusuran buku dan penyalinan lembar kerja.
• Simpan file tersebut sebagai buku kerja yang diaktifkan makro dan Jalankan makro (F5) dari editor.
• Saat jendela penjelajah file muncul, pilih file Excel yang berisi lembar kerja yang akan digabungkan, lalu terima.

Setelah beberapa saat, Anda akan melihatnya di buku utama Anda. Semua lembar dari berkas yang Anda pilih telah muncul., ditempatkan satu per satu. Dari situ Anda dapat mengatur ulang, mengganti nama, atau membersihkan apa pun yang Anda butuhkan.

Varian lain yang banyak digunakan adalah a naskah VBA yang Secara otomatis memindai semua file .xlsx dalam folder tertentu.Program ini membuka file satu per satu dan menyalin lembar kerjanya ke buku kerja utama Anda. Ini adalah pendekatan yang fantastis jika Anda akan sering mengulangi operasi tersebut dan selalu menyimpan laporan di folder yang sama.

Tampilkan data dari beberapa lembar kerja dalam satu lembar kerja utama menggunakan alat Konsolidasi.

Selain menyalin seluruh halaman, seringkali yang Anda cari adalah... memiliki satu lembar kerja utama dengan data ringkasan dari banyak lembar kerja.tanpa perlu memanipulasi setiap tab secara manual. Di sinilah fungsi konsolidasi Excel berperan kembali.

Idenya adalah Anda memiliki lembar detail secara terpisah (misalnya, satu per bulan, atau satu per area bisnis) dan Buat tabel di lembar utama yang mencerminkan jumlah, rata-rata, atau hitungan dari semuanya.Lembar kerja utama ini dapat menjadi dasar untuk grafik, dasbor, atau laporan berkala.

Tergantung pada bagaimana data Anda diatur, Anda dapat memilih konsolidasi. berdasarkan posisi mengapa kategoriSeperti yang telah kita lihat sebelumnya. Yang penting adalah rentang sumber didefinisikan sebagai daftar yang bersih (tanpa baris atau kolom kosong di antaranya) dan bahwa label dan strukturnya konsisten di antara daun-daun yang berbeda.

Pendekatan ini sangat berguna terutama ketika Anda menangani volume informasi yang besar dan hanya membutuhkan Lembar utama harus berisi ringkasan, bukan semua detail baris demi baris.Namun, Anda selalu dapat menyimpan lembaran asli berisi detailnya jika sewaktu-waktu perlu menelusuri lebih dalam di kemudian hari.

Gabungkan beberapa lembar kerja tanpa duplikat: gabungkan dan bersihkan.

Situasi umum lainnya adalah ketika Anda menggabungkan beberapa lembar kerja dengan catatan serupa (misalnya, daftar pelanggan, produk, atau transaksi) dan Anda melihat baris yang berulang. Setelah semuanya digabungkan. Excel tidak memiliki tombol ajaib yang menggabungkan lembar kerja dan menghapus duplikat secara bersamaan, tetapi Anda dapat menggabungkan dua alat untuk mencapai hal ini.

Idenya jelas: Pertama-tama, campurkan daun-daun tersebut. (menggunakan Salin dan Tempel, Power Query, Coupler.io, atau metode pilihan Anda) untuk mengumpulkan semua data dalam satu tabel; setelah selesai, Anda melanjutkan ke langkah berikutnya. Bersihkan tabel menggunakan opsi Hapus Duplikat dari Excel itu sendiri.

Untuk melakukannya dari Pilihan:

• Pilih tabel tempat Anda menyimpan data yang telah digabungkan.
• Buka tab datos dan di dalam grup Alat Data, klik pada menghapus duplikat.
• Di jendela yang muncul, centang atau hapus centang. Data saya memiliki header sesuai kebutuhan, agar Excel dapat membedakan header dengan benar.
• Pilih kolom mana yang ingin Anda gunakan untuk mendeteksi data duplikat; jika Anda memilih semua kolomHanya baris yang cocok di semua kolom yang akan dihapus.
• Setelah diterima, Excel akan menunjukkan berapa banyak data duplikat yang telah dihapus dan berapa banyak nilai unik yang tersisa.

Dengan kombinasi fusi dan pembersihan ini, Pada akhirnya, Anda akan mendapatkan satu tabel yang rapi dan tanpa pengulangan., sangat cocok untuk laporan, tabel pivot, atau ekspor ke sistem lain.

Menggabungkan lembar Excel berdasarkan kolom yang sama

Dalam banyak skenario dunia nyata, Anda tidak hanya ingin menumpuk rekaman, tetapi tautan informasi tambahan tersebar di berbagai tabelSebagai contoh, sebuah tabel keuangan yang berisi jumlah yang terkait dengan ID produk dan tabel penjualan lain yang berisi detail produk yang sama; hal yang logis adalah ingin menggabungkan keduanya menggunakan kunci umum tersebut.

Ada dua cara utama untuk melakukan ini di lingkungan pengguna: menggunakan alat eksternal seperti Coupler.io, yang mengotomatiskan penggabungan, atau menggunakan Power Queryyang terintegrasi ke dalam Excel modern dan juga memungkinkan penggabungan tabel seperti pada basis data.

Metode menggunakan Coupler.io untuk menggabungkan lembar kerja berdasarkan kolom.

Coupler.io adalah solusi berbasis cloud yang dirancang untuk mengotomatiskan laporan dan memindahkan data antar sumber yang berbeda.Di antara fungsinya, Anda dapat mengimpor beberapa lembar Excel, menggabungkannya, dan memuat hasilnya ke lembar lain atau bahkan ke alat analisis seperti BigQuery atau Looker Studio.

Jika Anda ingin menggabungkan, misalnya, lembar kerja bernama “Tabel Keuangan” dan lembar kerja lain bernama “Tabel Penjualan” berdasarkan kolom ID ProdukAlur tipikalnya adalah sebagai berikut:

• Di Coupler.io Anda memilih keduanya asal dan tujuan Microsoft Excel dalam bentuk awal dan lanjutkan.
• Anda menghubungkan akun Microsoft Anda, memilih file Excel, dan memilih lembar kerja tersebut sebagai sumber pertama. Tabel Keuangan.
• Anda menggunakan opsi hubungkan sumber lain untuk menambahkan lembar kedua, dalam hal ini Tabel Penjualan, dari file yang sama atau dari file lain.
• Pada langkah konfigurasi kombinasi, Anda memilih jenis operasi: Menambahkan (untuk menumpuk meja dengan struktur yang sama) atau Ikuti seminar (untuk mencocokkan data menggunakan kolom pengenal unik).
• Anda pilih Ikuti seminar dan Anda menetapkan “ID Produk” sebagai kolom kunci di kedua lembar kerja tersebut.
• Anda meninjau pratinjau data yang telah digabungkan dan menyesuaikan kolom yang ingin Anda pertahankan.

Selanjutnya, di bagian tujuan, Anda memilih file Excel dan lembar kerja tempat tabel gabungan akan disimpan. Anda dapat menjadwalkan pembaruan otomatis. sehingga Coupler.io memperbarui data secara berkala tanpa Anda harus mengulangi seluruh proses secara manual.

Gabungkan lembar kerja dengan Power Query berdasarkan kolom yang sama.

Jika Anda lebih memilih untuk tetap berada dalam ekosistem Excel, Power Query adalah alat yang sempurna untuk menggabungkan tabel. Berdasarkan pencocokan kolom. Ini adalah add-in gratis di Excel 2010 dan 2013, dan fungsi bawaan mulai dari Excel 2016.

Mari kita asumsikan lagi bahwa Anda memiliki dua tabel: satu di lembar "Tabel Keuangan" dan satu lagi di lembar "Tabel Penjualan", keduanya dengan kolom "ID Produk". Tujuannya adalah untuk membuat satu tabel yang menggabungkan data dari keduanya. berdasarkan pengidentifikasi tersebut.

Proses Hal ini dapat dibagi menjadi beberapa langkah:

Langkah 1: Buat koneksi Power Query

• Di buku kerja Excel Anda, temukan tabel pertama (misalnya, di sel pada lembar "Tabel Penjualan").
• Pada tab Data, di bagian Dapatkan dan ubah, pilih opsi Dari tabel atau rentang untuk memuat tabel tersebut ke dalam Power Query.
• Pada kotak yang terbuka, centang atau jangan centang Tabel saya memiliki header sesuai kebutuhan; dalam kebanyakan kasus, Anda akan membiarkan kotak ini tetap tercentang.
• Editor Power Query akan terbuka. Jika Anda belum ingin memuat tabel ke dalam buku kerja, pilih Tutup dan muat… dan centang opsinya Cukup buat koneksi..
• Ulangi proses tersebut dengan lembar kerja lainnya (misalnya, "Tabel Keuangan") untuk membuat koneksi yang sesuai. Di panel pencarian buku, Anda sekarang akan melihat kedua koneksi tersebut..

Langkah 2: Gabungkan kueri dengan kolom yang sama

• Di Excel, pilih salah satu kueri dan buka Data > Kueri baru (atau langsung dari panel kueri) > Gabungkan kueri > Gabungkan.
• Di jendela penggabungan, pilih “Tabel Keuangan” sebagai tabel pertama dan “Tabel Penjualan” sebagai tabel kedua.
• Di setiap tabel, Klik pada kolom “ID Produk” untuk menandainya sebagai kolom yang cocok; Anda akan melihat bahwa kolom tersebut disorot ketika pemilihan berhasil.
• Biarkan tipe penggabungan (join) tetap pada pengaturan default atau sesuaikan dengan kebutuhan Anda (misalnya, inner join, left join, dll.).
• Setujui agar Power Query membuat kueri baru dengan kolom tambahan yang berisi, untuk setiap baris, "Tabel Penjualan" yang terkait.

Langkah 3: Perluas kolom tabel gabungan

• Di Power Query Editor, Anda akan melihat kolom dengan nama tabel kedua, yang berisi kata "Tabel" di setiap barisnya.
• Klik ikon dengan kedua anak panah Klik di sebelah nama kolom tersebut untuk membukanya.
• Di kotak pop-up, pilih kolom mana dari “Tabel Penjualan” yang ingin Anda tambahkan ke kombinasi (misalnya, “Barang” dan “Jumlah ($)”).
• Jika Anda tidak ingin nama tabel asli muncul sebagai awalan di kolom-kolom tersebut, Hapus centang pada kotak untuk menggunakan nama kolom asli sebagai awalan. dan menerima.

Langkah 4: Muat tabel yang telah digabungkan ke dalam Excel

• Setelah tabel digabungkan dan dibersihkan di Power Query Editor, lanjutkan ke Tutup dan muat….
• Pilih apakah Anda ingin memuat data sebagai tabel di lembar baru atau pada lembar yang sudah ada, tergantung bagaimana Anda ingin mengatur buku Anda.

Setelah selesai, Anda akan memiliki tabel yang siap di Excel dengan data dari kedua lembar kerja dihubungkan oleh kolom yang sama, dengan semua kolom yang Anda pilih pada langkah perluasan.

Gunakan Kueri dan Koneksi untuk menggabungkan lembar kerja dari file yang sama.

Banyak pengguna menemukan hal ini hampir secara tidak sengaja. Fitur Kueri dan Koneksi Excel memungkinkan Anda untuk dengan mudah menggabungkan beberapa lembar kerja. dari file yang sama, selama keduanya memiliki struktur yang sama. Fungsi ini bergantung pada Power Query, tetapi antarmuka pengguna cukup ramah.

Jika, misalnya, Anda memiliki tiga lembar kerja dengan kolom yang sama (Tanggal – Barang – Nilai) dan Anda ingin Daftar semua data dalam satu tabel yang diurutkan berdasarkan tanggal.Pendekatan ini sangat praktis dan fleksibel.

Langkah-langkah umum akan:

• Pada tab Data, pilih Kueri baru (atau “Dapatkan data”, tergantung versi Anda) > Dari file > Dari buku.
• Pilih file Excel yang berisi lembar kerja yang ingin Anda gabungkan; Bahkan bisa jadi itu adalah buku yang sedang Anda kerjakan..
• Di browser, centang kotak untuk Pilih beberapa item dan beri tanda pada semua lembar yang ingin Anda sertakan.
• Klik pada Mengubah data untuk membuka tabel di Power Query Editor.
• Pada setiap kueri, hapus kolom yang tidak Anda perlukan dan Pastikan semua tabel memiliki struktur yang sama persis. (kolom yang sama, tipe data yang sama).
• Gunakan opsi menu Beranda di Power Query untuk tambahkan kuerisehingga pada akhirnya Anda memiliki satu kueri tunggal yang berisi baris dari semua lembar kerja.
• Setelah kueri terakhir siap, tekan Tutup dan muat sehingga ditambahkan ke dalam buku sebagai tabel pada lembar baru.

Dari situ Anda dapat mengurutkan berdasarkan tanggal, menerapkan warna sesuai dengan sumber data, atau bahkan Tambahkan kolom yang menunjukkan dari lembar mana setiap data berasal.yang sangat berguna untuk analisis yang lebih lanjut.