Mungkin kita sering menghadapi situasi di mana data tersebar di beberapa worksheet dan perlu digabungkan menjadi satu. Proses ini dikenal sebagai konsolidasi worksheet, yang memungkinkan penggabungan data dari beberapa sheet Excel ke dalam satu sheet baru.
Artikel ini akan membahas langkah-langkah praktis untuk menggabungkan worksheet Excel menjadi satu, termasuk cara mengelola data yang terpisah dan menghasilkan worksheet konsolidasi yang rapi.
Unduh file demo untuk mengikuti contoh yang disediakan.
File: konsolidasi-worksheet-excel.xlsx.
Apa yang Dimaksud dengan Menggabungkan Beberapa Sheet Excel Menjadi Satu Sheet?
Menggabungkan beberapa sheet Excel menjadi satu sheet adalah proses penggabungan data dari beberapa sheet sumber ke dalam satu sheet baru. Sheet konsolidasi ini dapat berada dalam file workbook yang sama atau berbeda.
Terdapat dua jenis penggabungan yang bisa dilakukan:
- Berdasarkan Lokasi:
- Data pada sheet sumber memiliki struktur, label, dan ukuran range yang sama. Setiap nilai dengan label yang sama akan digabungkan menggunakan fungsi tertentu seperti SUM atau AVERAGE.
- Contoh: Penjualan rumah di tahun 2020 dan 2021 dijumlahkan menggunakan fungsi SUM untuk menghasilkan total penjualan.
- Berdasarkan Kategori:
- Data pada sheet sumber memiliki struktur yang sama tetapi label atau ukuran range yang berbeda. Dalam kondisi ini, Excel menggabungkan data secara berdampingan sesuai dengan kategori masing-masing.
Alat Konsolidasi Data Excel
Alat konsolidasi Excel ditemui di tab Data di Ribbon, pada kelompok Data Tools.
Konsolidasi data Excel hanya mengikutkan label dan data, tanpa format apa pun.
Cara Menggabungkan Sheet Excel Berdasarkan Lokasi
Penggabungan berdasarkan lokasi dilakukan jika data memiliki struktur dan ukuran range yang seragam.
Ikuti langkah-langkah berikut:
- Buka workbook atau file yang memiliki sheet yang ingin digabungkan.
- Buat worksheet baru sebagai worksheet konsolidasi.
- Klik cell tujuan di worksheet konsolidasi untuk meletakkan data gabungan.
- Pada tab Data di Ribbon, klik Consolidate.
- Pilih fungsi penggabungan, misalnya Sum.
- Klik Reference.
- Pilih range data, beserta header-header yang ada, pada sheet sumber (misalnya sheet 2020). Klik Add untuk memasukkan range ke All references.
- Ulangi langkah ini untuk sheet sumber lainnya (sheet 2021).
- Aktifkan opsi Top row dan Left column untuk mengikutsertakan header kolom dan baris.
- Klik OK untuk menghasilkan data gabungan.
Tips:
- Berpindah antar worksheet dimungkinkan saat kotak dialog Consolidate.
- Pastikan untuk mengklik Reference sebelum membuka worksheet atau workbook lain.
- Menekan kunci F2 setelah mengklik Reference dapat mencegah penambahan referensi yang tidak diinginkan karena menekan kunci panah.
Cara Menggabungkan Banyak Sheet Berdasarkan Kategori
Jika label baris atau kolom tidak konsisten antar sheet atau dimensi tabel berbeda, Excel akan menggabungkan data berdasarkan kategori atau apa adanya. Berikut langkah-langkahnya:
- Buka workbook yang memiliki sheet sumber.
- Buat worksheet baru sebagai tujuan konsolidasi.
- Klik cell tujuan data konsolidasi.
- Buka kotak dialog Consolidate dan pilih fungsi penggabungan.
- Pilih range data dari masing-masing sheet sumber, lalu klik Add untuk menambahkan ke All references.
- Aktifkan Top Row dan Left Column.
- Klik OK untuk melihat hasil penggabungan.
Cara Menghubungkan Data Antar Sheet
Pada dua demo sebelumnya, data hasil konsolidasi berdiri sendiri.
Untuk memastikan data konsolidasi berubah secara otomatis ketika data di sheet sumber diubah, aktifkan opsi Create links to source data saat menggunakan Consolidate.
- Buka worksheet konsolidasi.
- Pilih range data gabungan.
- Buka kotak dialog Consolidate dan centang Create links to source data.
- Klik OK.
Hasilnya akan berubah menjadi tampilan outline, di mana kita dapat mengeksplorasi data sumber langsung dari sheet konsolidasi. Setiap perubahan pada sheet sumber akan langsung tecermin pada data gabungan.
Mengapa Sheet Excel Tidak Bisa Digabungkan?
- Error: Source reference overlaps destination area
- Ini terjadi jika lokasi konsolidasi dimasukkan sebagai salah satu referensi sumber. Solusinya, hapus lokasi tersebut dari All references.
- Error: No source reference specified for consolidation
- Ini terjadi jika tidak ada referensi data di All references. Pastikan untuk memilih data sumber sebelum klik OK.
Penutup
Menggabungkan worksheet Excel menjadi satu sheet mempermudah pengelolaan data yang tersebar. Teknik konsolidasi ini dapat dilakukan berdasarkan lokasi atau kategori, sesuai struktur data sumber.
Jika teman-teman memiliki pertanyaan tentang artikel ini, silakan tinggalkan komentar atau hubungi kami melalui form yang tersedia.