Mengonsolidasi data di beberapa lembar kerja

Mengonsolidasi data di beberapa lembar kerja

Untuk meringkas dan melaporkan hasil dari lembar kerja terpisah, Anda dapat mengonsolidasi data dari setiap lembar ke lembar kerja master. Lembar bisa berada dalam buku kerja yang sama dengan lembar kerja master, atau di buku kerja lainnya. Ketika menggabungkan data, Anda akan merangkai data sehingga dapat dengan mudah memperbarui dan mengagregasinya sesuai kebutuhan.

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

Tips: Jika Anda sering mengonsolidasi data, mungkin 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 Excel tabel.

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

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

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

  • Menggabungkan data menurut kategori mirip dengan membuat PivotTable. Namun, dengan PivotTable, Anda bisa dengan mudah menata ulang kategori. Pertimbangkan untuk membuat PivotTable jika perlu konsolidasi yang lebih fleksibel menurut kategori.

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

Ikuti langkah-langkah ini untuk mengonsolidasi beberapa lembar kerja ke lembar kerja master:

  1. Jika Anda belum melakukannya, setel data di setiap lembar konstituen dengan melakukan hal berikut:

    • Pastikan bahwa setiap rentang data berada dalam format daftar. Setiap kolom harus mempunyai 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, tetapi jangan masukkan apa pun dalam lembar kerja master di mana Anda berencana untuk mengonsolidasi data. Excel akan melakukan ini untuk Anda.

    • Pastikan bahwa setiap rentang memiliki tata letak yang sama.

  2. Di lembar kerja master, klik sel kiri atas di area tempat menampilkan data yang dikonsolidasi.

    Catatan: Untuk menghindari menimpa data yang sudah ada di lembar kerja master, pastikan bahwa Anda menyisakan cukup sel di sebelah kanan dan di bawah sel ini untuk data terkonsolidasi.

  3. Klik Data>Konsolidasikan (di grup Alat Data).

    Grup Alat Data pada tab Data

  4. 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:

    Dialog Konsolidasi Data

  5. Pilih data Anda.

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

    Dialog Ciutkan Konsolidasi Data

    Klik lembar kerja yang berisi data yang akan dikonsolidasi, pilih data, lalu klik tombol Perluas Dialog di sebelah kanan untuk kembali ke dialog Konsolidasikan.

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

    Berikut contoh tiga rentang lembar kerja yang telah dipilih:

    Dialog Konsolidasi Data

  6. Di jendela popup Konsolidasikan, klik Tambahkan. Ulangi ini untuk menambahkan semua rentang yang dikonsolidasi.

  7. Pembaruan otomatis vs. Manual: Jika Anda ingin Excel tabel konsolidasi secara otomatis ketika 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 bisa membuat link saat area sumber dan tujuan berada di lembar yang sama.

    • Jika perlu mengubah luas 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 rentang sebelumnya sebelum Anda mengonsolidasi kembali. Cukup pilih referensi lama dan tekan tombol Delete.

  8. Klik OK,Excel akan memberikan 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 akan dikonsolidasi memiliki label unik yang hanya muncul dalam satu rentang sumber.

Jika data yang akan dikonsolidasi ada di sel lain dalam 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:

Excel rumus multi-lembar
 

Tips: Untuk memasukkan referensi sel,misalnya Sales! B4—dalam rumus tanpa mengetik, ketikkan rumus sampai pada titik Anda memerlukan referensi, lalu klik tab lembar kerja, lalu klik sel tersebut. Excel akan menyelesaikan nama lembar dan alamat sel untuk Anda. CATATAN: rumus dalam kasus seperti ini dapat rentan akan kesalahan, karena sangat mudah untuk memilih sel yang salah secara tidak sengaja. Bisa juga sulit menemukan kesalahan setelah memasukkan rumus kompleks.

Jika data yang akan dikonsolidasi ada 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 di sel A2 dari Sales sampai Marketing, di sel E5 lembar kerja master, Anda perlu memasukkan hal berikut:

Excel referensi Lembar 3D

Perlu bantuan lainnya?

Anda selalu dapat bertanya kepada pakar di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Jawaban, atau menyarankan fitur maupun fitur baru di Suara Pengguna Excel.

Lihat Juga

Gambaran umum rumus di Excel

Cara menghindari kesalahan rumus

Menemukan dan memperbaiki kesalahan dalam rumus

Pintasan keyboard dan tombol fungsi Excel

Fungsi Excel (sesuai alfabet)

Fungsi Excel (menurut kategori)

Perlu bantuan lainnya?

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×