Mengonsolidasi data di beberapa lembar kerja

Mengonsolidasi data di beberapa lembar kerja

Catatan:  Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Untuk meringkas dan laporan hasil dari lembar kerja terpisah, Anda bisa mengonsolidasikan data dari setiap lembar ke lembar kerja master. Lembar bisa berupa dalam buku kerja yang sama sebagai lembar kerja master, atau dalam buku kerja lain. Saat Anda mengkonsolidasikan data, Anda kumpulkan data sehingga Anda bisa lebih mudah memperbarui dan agregat sebagaimana diperlukan.

Misalnya, jika Anda memiliki lembar kerja pengeluaran untuk masing-masing kantor Anda, Anda mungkin menggunakan konsolidasi untuk memutar gambar ini ke dalam lembar kerja master pengeluaran perusahaan. Lembar kerja master ini mungkin juga berisi penjualan total dan rata-rata, tingkat inventaris saat ini, dan produk penjualan tertinggi untuk seluruh perusahaan.

Tips: Jika Anda sering mengkonsolidasikan data, mungkin membantu untuk membuat lembar kerja baru dari Templat lembar kerja yang menggunakan tata letak yang konsisten. Untuk mempelajari selengkapnya tentang Templat, lihat: membuat Templat. Ini adalah waktu yang ideal untuk membuat Templat Anda dengan tabel Excel.

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

Konsolidasi menurut posisi: data dalam area sumber memiliki urutan yang sama, dan menggunakan label yang sama. Gunakan metode ini untuk mengonsolidasikan data dari serangkaian lembar kerja, seperti lembar kerja anggaran Departemen yang telah dibuat dari Templat yang sama.

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

  • Menggabungkan data menurut kategori mirip dengan membuat PivotTable. Dengan PivotTable, namun, Anda bisa dengan mudah mengatur ulang kategori. Pertimbangkan untuk membuat PivotTable jika Anda membutuhkan lebih fleksibel konsolidasi menurut kategori.

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

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

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

    • Pastikan bahwa setiap rentang data dalam format daftar. Setiap kolom harus memiliki label (header) di baris pertama dan berisi data yang serupa. Harus ada tidak ada baris atau kolom kosong di mana saja dalam daftar.

    • Tempatkan setiap rentang di lembar kerja terpisah, tetapi tidak memasukkan apa pun di lembar kerja master 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 meninggalkan sel cukup ke kanan dan di bawah sel untuk data konsolidasi.

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

    Grup Alat Data pada tab Data

  4. Di kotak fungsi , klik fungsi ringkasan yang Anda inginkan Exel untuk mengonsolidasi data. Fungsi default adalah jumlah.

    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 mengecilkan panel dan pilih data di lembar kerja.

    Data konsolidasi Ciutkan dialog

    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 Anda perlukan untuk mengonsolidasi dalam buku kerja lain, klik Telusuri untuk menemukan buku kerja itu. Setelah menemukan dan mengklik OK, Excel akan Masukkan jalur file di kotak referensi dan menambahkan tanda seru ke jalur tersebut. Anda bisa lalu lanjutkan untuk memilih data lainnya.

    Berikut adalah contoh di mana tiga rentang lembar kerja telah dipilih dipilih:

    Dialog konsolidasi data

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

  7. Pembaruan otomatis vs Manual: Jika Anda ingin Excel untuk memperbarui konsolidasi tabel Anda secara otomatis saat data sumber perubahan, cukup centang kotak Buat link ke sumber data . Jika kotak ini tetap tidak dicentang, Anda bisa memperbarui konsolidasi secara manual.

    Catatan: 

    • Anda tidak bisa membuat link saat area sumber dan tujuan pada lembar yang sama.

    • Jika Anda perlu mengubah tingkat rentang — atau mengganti rentang — klik rentang sembulan Konsolidasikan dan memperbarui menggunakan langkah-langkah di atas. Ini akan membuat referensi rentang baru, jadi Anda perlu menghapus sebelumnya sebelum Anda menggabungkan lagi. Cukup pilih referensi yang lama dan tekan tombol Delete.

  8. Klik OK, dan Excel akan menghasilkan konsolidasi untuk Anda. Secara opsional, Anda bisa menerapkan pemformatan. Hal ini hanya diperlukan untuk memformat sekali, kecuali jika Anda menjalankan ulang 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 Anda tidak akan dikonsolidasi memiliki label unik yang muncul di hanya satu rentang sumber.

Jika data yang akan dikonsolidasi ada di sel lain di lembar kerja yang berbeda:

Masukkan rumus dengan referensi sel ke lembar kerja lain, satu untuk setiap lembar kerja terpisah. Sebagai contoh, untuk mengonsolidasi data dari lembar kerja bernama penjualan (di sel B4), HR (dalam sel F5) dan pemasaran (dalam sel B9), dalam sel A2 dari lembar kerja master, Anda akan memasukkan hal berikut ini:

Referensi rumus multi-lembar Excel

Tips: Untuk memasukkan referensi sel — seperti penjualan! B4 — di dalam rumus tanpa mengetikkan, ketikkan rumus titik di mana Anda perlu referensi, lalu klik lembar kerja tab, dan klik sel. Excel akan selesaikan lembar nama dan sel alamat untuk Anda. Catatan: rumus dalam kasus tersebut dapat sarat kesalahan, karena sangat mudah untuk memilih sel salah secara tidak sengaja. Juga akan menyulitkan untuk melihat kesalahan setelah memasukkan rumus kompleks.

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

Masukkan rumus dengan referensi 3-D yang menggunakan referensi ke rentang nama lembar kerja. Sebagai contoh, untuk mengonsolidasi data dalam sel A2 dari penjualan melalui pemasaran inklusif, di sel E5 lembar kerja master Anda akan memasukkan hal berikut ini:

Rumus referensi lembar 3D Excel

Tahukah Anda?

Jika tidak memiliki langganan Office 365 atau versi terbaru Office, Anda dapat mencobanya sekarang:

Coba Office 365 atau versi terbaru Excel

Apakah Anda memiliki pertanyaan tentang fungsi tertentu?

Posting pertanyaan dalam forum komunitas Excel

Bantu kami meningkatkan Excel

Memiliki saran tentang bagaimana kami dapat menyempurnakan versi Excel berikutnya? Jika demikian, silakan lihat topik-topik di User Voice Excel.

Lihat Juga

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.

×