Bagaimana cara membuat konsolidasi dinamis di excel?

Konsolidasi di Excel digunakan untuk menggabungkan data lebih dari 2 buku kerja yang tersedia di tab menu Data di bawah bagian Alat data dengan nama Konsolidasi. Untuk ini, kita harus memiliki tipe data yang sama di buku kerja yang berbeda. Meskipun kumpulan data yang berbeda juga akan berfungsi, tidak akan ada penyelarasan yang tepat dalam data yang terkonsolidasi. Pilih fungsi matematika yang ingin kita jalankan pada akhirnya. Kemudian pilih semua data menggunakan referensi dari semua buku kerja dan klik OK. Ini akan menggabungkan tabel yang dipilih dengan eksekusi fungsi matematika yang dipilih di bagian akhir

Bagaimana Mengkonsolidasikan Data di Beberapa Lembar Kerja?

Mari pahami cara menggabungkan data dalam beberapa lembar kerja dengan beberapa contoh

Contoh #1 – Gabungkan Data di Buku Kerja yang Sama

Katakanlah kita memiliki lembar kerja di mana kita memiliki data penjualan dengan empat lembar kerja yang ditandai sesuai wilayahnya sebagai Utara, Selatan, Timur, dan Barat. Sekarang kami ingin mengkonsolidasikan data ke dalam satu tempat daripada di lembar dalam buku kerja yang sama. Ada lembar kelima bernama file konsolidasi

Mulai Kursus Excel Gratis Anda

Fungsi Excel, rumus, bagan, pemformatan membuat dasbor excel & lainnya

Contoh ini akan menampilkan penjualan gabungan untuk semua wilayah. Pertama, berikut adalah contoh file data. Di sini, Anda dapat melihat berbagai nama lembar kerja dan file konsolidasi terakhir yang harus kami kerjakan

Paket VBA Excel Semua dalam Satu(120+ Kursus, 30+ Proyek)

Harga
Lihat Kursus

120+ Kursus Daring. 30+ Proyek. 500+ Jam. Sertifikat yang Dapat Diverifikasi. Akses Seumur Hidup
4. 9 (64.639 peringkat)

Ini adalah templat kami di lembar "file terkonsolidasi", dan sekarang kami akan mulai menggabungkan data dari lembar kerja

Kami sekarang akan mengklik sel B3

Di mana kami ingin fungsi "Konsolidasi" untuk memasukkan data dari lembar lain. Seperti yang bisa kita lihat di atas, sel B3 dipilih, dan sekarang kita akan berpindah ke tab Data di Pita Excel dan akan menuju ke Konsolidasi

Setelah kita mengklik Konsolidasi, jendela di bawah ini akan muncul

Hal pertama yang kita lihat adalah dropdown "Fungsi" yang menunjukkan banyak fungsi aritmatika seperti jumlah, hitung, maks, rata-rata, dll. Karena kami menginginkan jumlah penjualan, kami akan memilih "Jumlah" di dropdown. Sekarang, kita akan masuk ke tab referensi untuk menambahkan referensi ke data kita dari lembar kerja yang berbeda. Kami kemudian akan pergi ke lembar pertama kami yang berisi data penjualan untuk Utara. Kami hanya harus memilih data penjualan dan bukan judul dan baris. Ini ditunjukkan di bawah ini

Referensi ditampilkan di kotak "Referensi" seperti ini

Klik "Tambah", dan referensi akan ditambahkan di kotak "Semua Referensi".

Dengan cara yang sama, kami sekarang akan menambahkan referensi dari semua lembar lain seperti Utara, Selatan, Timur, dan Barat. Setelah kita selesai menambahkan referensi, klik “OK”

Sekarang kami telah mengkonsolidasikan data penjualan untuk para eksekutif per bulan, di tingkat nasional

Contoh #2 – Konsolidasi Produk Penjualan Tahunan dengan Bijaksana

Dalam hal ini, kami memiliki penjualan per kuartal untuk produk A, B, C dan D, dan kami ingin konsolidasi penjualan tahunan berdasarkan produk

Di sini kami memiliki data dalam empat lembar; . Kami sekarang akan pergi ke tab referensi untuk menambahkan referensi ke data kami dari lembar kerja yang berbeda. Kami akan pergi ke lembar pertama kami yang berisi data penjualan untuk Q1. Kami akan memilih data seperti yang ditunjukkan di bawah ini

Sekarang, kita akan pergi ke tab Data dan klik Konsolidasi, dan jendela di bawah ini akan muncul

Kami akan mengklik "Tambah", dan referensi akan ditambahkan di kotak "Semua referensi".

Kami akan mengklik "Tambah", dan referensi akan ditambahkan di kotak "Semua referensi".

Kami akan mengklik "Tambah", dan referensi akan ditambahkan di kotak "Semua referensi".

Di bawah ini adalah template kami untuk lembar data gabungan. Kami sekarang akan memilih sel B2 untuk mendapatkan total data penjualan dari lembar lain

Kami akan memilih fungsi "Sum" dari drop-down. Demikian juga, kami akan menambahkan referensi dari lembar Q2, Q3, dan Q4. Akan muncul seperti ini. Semua referensi dari semua sheet sekarang ditambahkan. Klik "OK"

Kami sekarang memiliki data penjualan tahunan terkonsolidasi dengan jumlah total untuk setiap produk

Jika kita ingin menyisipkan tabel konsolidasi dalam lembar kerja kosong alih-alih membuat tabel template. Kita juga bisa melakukannya. Mengambil data sampel kami sebelumnya, kami akan melakukan konsolidasi di bawah ini

Kami tidak memiliki template untuk tabel konsolidasi, dan kami ingin mendapatkan data konsolidasi dalam lembar kerja kosong dengan label baris dan kolom. Kami akan menambahkan lembar kerja baru;

Sekarang, seperti sebelumnya, kita akan masuk ke tab Data

Kami sekarang akan memilih referensi dari lembar data kami. Kami akan memulai ini dengan lembar "Utara" dan kemudian akan melanjutkan dengan lembar "Selatan", "Timur", dan "Barat". Kami akan memilih data seperti yang ditunjukkan di bawah ini, yang menyertakan label baris dan kolom

Kami kemudian akan menambahkan referensi di kotak "Semua referensi".

Sekarang tambahkan semua referensi dengan cara yang sama dari semua lembar data

Kami sekarang akan memeriksa "Baris Atas" dan "Kolom Kiri" dan kemudian tekan OK

Kami sekarang melihat data penjualan terkonsolidasi dengan label baris dan kolom

Jika Anda ingin memperbarui data gabungan saat lembar individual diperbarui, klik kotak "Buat Tautan untuk membuat data". Jika Anda ingin memperbarui data secara manual, maka jangan centang kotaknya dan klik OK

Sel-sel dalam tabel gabungan sekarang berisi rumus penjumlahan, seperti yang ditunjukkan di bawah ini. Ini membuatnya dinamis di alam

Ini adalah tabel konsolidasi dinamis

Contoh #3 – Mengkonsolidasikan Data Dari Berbagai Buku Kerja

Jika kami memiliki data di buku kerja yang berbeda dan kami ingin menggabungkannya menjadi satu buku kerja. Kita bisa melakukan ini juga. Ini memberi kami fleksibilitas dan kemudahan yang luar biasa

Kami memiliki data penjualan seperempat bijaksana dari berbagai produk, katakanlah A, B, C, dan D, di buku kerja yang berbeda, seperti yang ditunjukkan di bawah ini

Setelah semua buku kerja terbuka, kita akan membuka buku kerja baru dan mengklik sel B3. Tambahkan referensi dari semua lembar kerja di bawah ini

Kami telah menggabungkan data dari semua buku kerja ke dalam buku kerja baru

Selain itu, setiap perubahan pada data di buku kerja mana pun juga akan diperbarui di buku kerja data terkonsolidasi yang baru

Jadi kami telah mempelajari cara menggunakan fungsi konsolidasi di Excel dengan bantuan contoh. Ini adalah fungsi yang sangat berguna dalam menggabungkan atau mengumpulkan data ke dalam satu lembar dari lembar kerja atau buku kerja yang berbeda

Hal yang Perlu Diingat Tentang Konsolidasi di Excel

• Hati-hati dalam memilih data referensi saat Anda mencentang kotak untuk "Baris Atas" dan "Kolom Kiri". Anda kemudian harus memilih data lengkap, termasuk label baris dan kolom
• Saat Anda menggabungkan data yang bersifat dinamis dari lembar kerja dan buku kerja yang berbeda, centang "buat tautan ke data sumber", yang akan secara otomatis memperbarui perubahan dalam data jika selesai

Artikel yang Direkomendasikan

Ini adalah panduan untuk Konsolidasi di Excel. Di sini kita membahas Cara Konsolidasi Data di Beberapa lembar kerja beserta contoh-contoh praktis dan template excel yang dapat diunduh. Anda juga dapat membaca artikel terkait kami yang lain –

Bagaimana cara mengotomatiskan konsolidasi di Excel?

Apa cara terbaik untuk menggabungkan data di Excel?

Bagaimana cara menggabungkan data secara otomatis dari beberapa lembar di Excel?

Apa itu konsolidasi data statis di Excel?