Mengonsolidasi data di beberapa lembar kerja

Untuk meringkas dan melaporkan hasil dari lembar kerja terpisah, Anda bisa mengonsolidasikan data dari setiap lembar ke dalam lembar kerja master. Lembar bisa berada di buku kerja yang sama dengan lembar kerja master, atau di buku kerja lain. Saat mengonsolidasi data, Anda mengumpulkan data sehingga Anda bisa lebih mudah memperbarui dan mengagregasi seperlunya.

Misalnya, jika Anda memiliki lembar kerja pengeluaran untuk setiap kantor regional, Anda mungkin menggunakan konsolidasi untuk menggulung angka-angka ini menjadi lembar kerja pengeluaran perusahaan master. Lembar kerja master ini mungkin juga berisi total dan rata-rata penjualan, tingkat persediaan saat ini, dan produk dengan penjualan tertinggi untuk seluruh perusahaan.

Tips: Jika Anda sering mengonsolidasikan data, mungkin akan membantu membuat lembar kerja baru dari templat lembar kerja yang menggunakan tata letak yang konsisten. Untuk mempelajari selengkapnya tentang Templat, lihat: Membuat templat. Ini juga merupakan waktu yang ideal untuk menyiapkan templat Anda dengan tabel Excel.

Ada dua cara untuk mengonsolidasi data, baik menurut posisi atau kategori.

Konsolidasi menurut posisi: Data di area sumber memiliki urutan yang sama, dan menggunakan label yang sama. Gunakan metode ini untuk menggabungkan data dari serangkaian lembar kerja, seperti lembar kerja anggaran departemen yang telah dibuat dari templat yang sama.

Konsolidasi menurut kategori: Ketika data di area sumber tidak disusun dalam urutan yang sama tetapi menggunakan label yang sama. Gunakan metode ini untuk mengonsolidasi data dari serangkaian lembar kerja yang memiliki tata letak berbeda tapi memiliki label data yang sama.

Menggabungkan data menurut kategori mirip dengan membuat PivotTable. Namun, dengan PivotTable, Anda dapat mengatur ulang kategori dengan mudah. Pertimbangkan untuk membuat PivotTable jika Anda membutuhkan konsolidasi yang lebih fleksibel menurut kategori.

Catatan: Contoh dalam artikel ini dibuat dengan Excel 2016. Meskipun tampilan Anda mungkin berbeda jika Anda menggunakan versi Excel lain, langkah-langkahnya sama.

Ikuti langkah-langkah ini untuk menggabungkan beberapa lembar kerja ke dalam lembar kerja master:

Jika Anda belum melakukannya, siapkan data di setiap lembar konstituen dengan melakukan hal berikut:
- Pastikan bahwa setiap rentang data berada dalam format daftar. Setiap kolom harus memiliki label (header) di baris pertama dan berisi data yang serupa. Tidak boleh ada baris atau kolom kosong di mana pun dalam daftar.
- Letakkan setiap rentang di lembar kerja terpisah, tapi jangan masukkan apa pun di lembar kerja master tempat Anda berencana untuk mengonsolidasikan data. Excel akan melakukan ini untuk Anda.
- Pastikan bahwa setiap rentang memiliki tata letak yang sama.
Di lembar kerja master, klik sel kiri atas di area tempat menampilkan data yang dikonsolidasi.

Catatan: Untuk menghindari timpa data yang sudah ada dalam lembar kerja master, pastikan Anda membiarkan cukup sel di sebelah kanan dan di bawah sel ini untuk data yang dikonsolidasikan.
Klik Data>Konsolidasikan (dalam grup Alat Data ).
Di kotak Fungsi, klik fungsi ringkasan yang akan digunakan Microsoft Excel untuk mengonsolidasi data. Fungsi defaultnya adalah SUM.

Berikut adalah contoh di mana tiga rentang lembar kerja telah dipilih:
Pilih data Anda.

Berikutnya, dalam kotak Referensi , klik tombol Ciutkan untuk menciutkan panel dan memilih data dalam lembar kerja.

Klik lembar kerja yang berisi data yang ingin Anda konsolidasikan, pilih data, lalu klik tombol Perluas Dialog di sebelah kanan untuk kembali ke dialog Konsolidasi .

Jika lembar kerja yang berisi data yang perlu Anda konsolidasikan ada di buku kerja lain, klik Telusuri untuk menemukan buku kerja tersebut. Setelah menemukan dan mengklik OK, Excel akan memasukkan jalur file dalam kotak Referensi dan menambahkan tanda seru ke jalur tersebut. Anda kemudian dapat melanjutkan untuk memilih data lain.

Berikut adalah contoh di mana tiga rentang lembar kerja telah dipilih:
Di jendela popup Konsolidasikan , klik Tambahkan. Ulangi ini untuk menambahkan semua rentang yang Anda konsolidasikan.
Pembaruan otomatis vs. Manual: Jika Anda ingin Excel memperbarui tabel konsolidasi Secara otomatis saat data sumber berubah, cukup centang kotak Buat link ke data sumber . Jika kotak ini tetap tidak dicentang, Anda dapat memperbarui konsolidasi secara manual.
Catatan:
- Anda tidak dapat membuat tautan saat area sumber dan tujuan berada di lembar yang sama.
- Jika Anda perlu mengubah tingkat rentang—atau mengganti rentang—klik rentang dalam popup Konsolidasikan dan perbarui menggunakan langkah-langkah di atas. Ini akan membuat referensi rentang baru, jadi Anda harus menghapus yang sebelumnya sebelum Anda mengonsolidasi lagi. Cukup pilih referensi lama dan tekan tombol Delete.
Klik OK, dan Excel akan menghasilkan konsolidasi untuk Anda. Secara opsional, Anda bisa menerapkan pemformatan. Hanya perlu memformat sekali, kecuali Anda menjalankan kembali konsolidasi.
- Setiap label yang tidak cocok dengan label dalam area sumber lainnya menghasilkan baris atau kolom yang terpisah dalam konsolidasi.
- Pastikan bahwa setiap kategori yang tidak ingin Anda konsolidasikan memiliki label unik yang hanya muncul dalam satu rentang sumber.

Jika data yang akan dikonsolidasi berada di sel yang berbeda pada lembar kerja yang berbeda:

Masukkan rumus dengan referensi sel ke lembar kerja lain, satu untuk setiap lembar kerja yang terpisah. Misalnya, untuk mengonsolidasi data dari lembar kerja bernama Sales (di sel B4), HR (di sel F5), dan Marketing (di sel B9), di sel A2 lembar kerja master, Anda akan memasukkan hal berikut:

Referensi rumus multi-lembar Excel

Tips: Untuk memasukkan referensi sel—seperti Penjualan! B4—dalam rumus tanpa mengetik, ketikkan rumus hingga titik di mana Anda memerlukan referensi, lalu klik tab lembar kerja, lalu klik sel. Excel akan menyelesaikan nama lembar dan alamat sel untuk Anda. CATATAN: rumus dalam kasus seperti itu bisa rentan terhadap kesalahan, karena sangat mudah untuk memilih sel yang salah secara tidak sengaja. Juga sulit untuk menemukan kesalahan setelah memasukkan rumus yang kompleks.

Jika data yang akan dikonsolidasi berada di sel yang sama pada lembar kerja yang berbeda:

Masukkan rumus dengan referensi 3-D yang menggunakan referensi ke rentang nama lembar kerja. Misalnya, untuk mengonsolidasi data dalam sel A2 dari Sales hingga Marketing inklusif, di sel E5 lembar kerja master Anda akan memasukkan hal berikut:

Rumus referensi Lembar 3D Excel