Cara Menggabungkan File atau Sheet Excel Berbeda (Consolidate)

Dalam beberapa kasus, mungkin Anda ingin menggabungkan File atau Sheet Excel berbeda menjadi satu bagian.

Pada Excel, Anda bisa menggabungkan File ataupun Sheet dalam WorkBook (File Excel) yang sama ataupun berbeda sekalipun. Anda bisa menggunakan fitur Consolidate atau fitur Move or Copy.

Kedua fitur tersebut memiliki fungsi yang berbeda. Silahkan gunakan salah satu saja sesuai kebutuhan Anda. Saya mulai jelaskan untuk fitur consolidate.

Cara Menggabungkan File Excel Berbeda Menjadi 1 Sheet

Dalam panduan ini, Saya menggunakan 3 buah WorkBook (file Excel). Masing-masing WorkBook memiliki 1 sheet seperti gambar berikut:

1. WorkBook 1 dengan Nama File Penjualan Cabang 1. Berisi 1 Sheet dengan nama Cabang 1

2. WorkBook 2 dengan Nama File Penjualan Cabang 2. Berisi Sheet dengan nama Cabang 2

3. WorkBook 3 dengan Nama File Penjualan Cabang 3. Berisi Sheet dengan nama Cabang 3

Anda bisa lihat, semua WorkBook (file Excel) memiliki format data yang sama. Ini akan memudahkan penggabungan Sheet ke dalam 1 Sheet. Saya berikan contoh cara menggabungkan ke tiga file Excel tersebut menggunakan fitur Consolidate.

Silahkan buka ke tiga WorkBook yang akan digabungkan, kemudian ikuti 2 tahap berikut:

#1 Set Consolidate

1. Pertama, Buka WorkBook baru (kosong) kemudian klik Cell manapun yang menjadi tempat gabungan data Anda. Saya klik Cell A1
2. Kedua, Klik Tab Data pada WorkBook yang baru, kemudian klik Consolidate
3. Ketiga, pada Window Consolidate, Anda bisa menggunakan Fungsi yang diinginkan. Misalnya Saya menggunakan Fungsi SUM.

Kemudian biar lebih mudah, semua WorkBook (Cabang 1, Cabang 2, dan Cabang 3) Saya perkecil bersama dengan Window Consolidate seperti gambar berikut:

#2 Ambil Sumber Data

Sekarang kita akan memasukan data dari setiap WorkBook ke Kolom Reference yang ada pada Window Consolidate. Silahkan ikuti cara menggabungkan banyak file Excel menjadi 1 berikut:

1. Pertama, klik kolom Reference, kemudian blok Range A5:E8 pada WorkBook Penjualan Cabang 1 untuk mengambil data Penjualan Pada Cabang 1. Sehingga kolom Reference berisi ‘[Penjualan Cabang 1.xlsx]Cabang 1’!$A$5:$E$8
2. Kedua, klik Add sehingga kolom All Reference berisi ‘E:\Selesaikan Segera[Penjualan Cabang 1.xlsx]Cabang 1’!$A$5:$E$8 (bagian ini akan Saya jelaskan dibawah)
3. Ketiga, karena baru 1 WorkBook yang kita masukkan, selanjutnya ulangi tahap 1 sampai tahap 2, yaitu klik kolom Reference, kemudian blok Range A5:E7 pada WorkBook Penjualan Cabang 2. Kemudian klik Add dan pastikan kolom All Reference bertambah referensi kedua yaitu ‘E:\Selesaikan Segera[Penjualan Cabang 2.xlsx]Cabang 2’!$A$5:$E$7
4. Keempat ulangi tahap 1 sampai 2 untuk mengambil data pada WorkBook ke 3.
5. Kelima, centang Top Row karena semua bentuk table pada ke tiga WorkBook serupa, yaitu memiliki Header / Judul pada Setiap tabel (lihat gambar).
6. Keenam, centang Left Column karena semua kolom pertama pada ke tiga WorkBook sama-sama berisi nama produk.
7. Terakhir, centang Create Link to Source Data. Kemudian klik OK untuk melanjutkan dan berikut hasilnya:

Sebelum Saya jelaskan hasil Consolidate (penggabungan Data) WorkBook ini, sedikit Saya jelaskan makna dari Reference yang terdapat pada Kolom All Reference di Window Consolidate seperti gambar berikut:

Terdapat 3 sumber data (reference) yang berarti sebagai berikut:

Lalu gimana dengan makna Referensi ke 2 dan ke 3 ? Kurang lebih cara baca nya sama saja. Silahkan Anda artikan sendiri.

Cara Baca dan Menggunakan Hasil Consolidate

Sebelumnya kita sudah dapat hasil penggabungan (Consolidate) Sheet dari WorkBook berbeda menjadi 1 sheet dalam 1 WorkBook (file Excel).

Cara membaca hasil consolidate, silahkan klik Icon + tepat di Mistar ROW sebelah kiri untuk melihat semua data seperti gambar berikut:

Benar kan ? Semua data tersedia dalam 1 tabel yang jauh lebih mudah Anda pahami. Tentu Anda bisa menemukan gambaran yang lebih spesifik tentang data dari ke tiga WorkBook tersebut sekaligus. Berikut cara baca data consolidate tersebut:

1. Pertama, Row 5 (berwarna kuning) merupakan Total Penjualan Kopi dari seluruh Cabang. Sementara data penjualan kopi masing-masing cabang terletak di atasnya.
2. Kedua, Row 9 (berwarna hijau) merupakan Total Penjualan Susu dari seluruh Cabang. Sementara data Penjualan Susu masing-masing cabang terletak di atasnya.
3. Ketiga, Row 12 (berwarna biru) merupakan Total Penjualan Gula dari seluruh Cabang. Sementara data Penjualan Susu masing-masing cabang terletak di atasnya.

Lalu timbul pertanyaan, kenapa Data Total Penjualan Gula untuk Cabang 2 tidak Ada ? Untuk itu, silahkan Anda lihat kembali WorkBook Penjualan Cabang 2. Data Penjualan Gula untuk Cabang 2 memang tidak ada seperti gambar berikut:

Oh ya, Dengan Fitur Consolidate, ada Keuntungan yang bisa Anda dapatkan.

Yaitu, ketika data pada WorkBook Sumber (Penjualan Cabang 1, Penjualan Cabang 2 dan Penjualan Cabang 3) berubah, maka data pada WorkBook baru ini juga akan ikut berubah.

Jadi semua data pada WorkBook sumber sudah linked (terhubung) dengan WorkBook Anda yang baru ini. Ketika Anda mengubah data pada WorkBook sumber, maka WorkBook baru akan ikut berubah.

Misalnya, Saya ubah Total Penjualan Kopi untuk Cabang 2 pada Q1. Semula adalah 30.240.000, kemudian Saya ubah menjadi 2.000. Lihat hasil berikut:

Benar kan ? Data pada WorkBook baru (consolidate / gabungan) juga ikut berubah.

Apakah cara ini yang Anda butuhkan ? Jika tidak, silahkan simak Cara menggabungkan File Excel Berbeda ke dalam 1 File menggunakan Move of Copy berikut:

Cara Menggabungkan File Excel Berbeda Menjadi 1 File

Tujuan cara ini adalah menggabungkan Sheet yang terdapat pada File Excel (WorkBook) lain ke File Excel Anda saat ini.

Saya menggunakan contoh yang sama dengan sebelumnya yaitu File Excel Penjualan Cabang 1, Penjualan Cabang 2, dan Penjualan Cabang 3. Saya akan gabungkan semua Sheet pada file Excel tersebut ke dalam 1 File Excel yang baru.

Pertama-tama, silahkan buka ketiga WorkBook tersebut. Kemudian ikuti tahap-tahap berikut:

1. Pertama, Buka WorkBook (File Excel) Pertama yaitu Penjualan Cabang 1. Kemudian klik kanan pada Nama Sheet “Cabang 1”
2. Kedua, Pilih Move or Copy.
3. Ketiga, Pilih (New Book) untuk memindahkan atau menyalin sheet ini ke File Excel yang baru. Atau Anda bisa pilih File yang sedang aktif (tepat di bawah new book).
4. Terakhir, Centang Box Create a Copy untuk menyalin Sheet tersebut, sedagkan jika tidak dicentang, Excel akan memindahkannya dari file yang lama. Kemudian klik Ok untuk melanjutkan.

Selanjutnya, untuk memindahkan Sheet yang selanjutnya, silahkan buka File Penjualan Cabang 2. Kemudian ikuti tahap berikut:

1. Pertama, klik kanan pada nama sheet “Cabang 2”, kemudian klik Move or Copy seperti cara sebelumnya.
2. Kedua, pada Window Move or Copy, Anda tidak perlu memilih (new book), tapi pilih Nama WorkBook yang baru dibuat. Dalam contoh ini, nama Workbook tersebut “Book2”.
3. Ketiga, centang Create a Copy kemudian klik Ok untuk melanjutkan.

Lalu gimana dengan dengan Sheet untuk Penjualan Cabang 3 ? Silahkan ulangi tahap 1-3 pada Penjualan Cabang 2 diatas. Jika sudah, hasilnya seperti gambar berikut:

Note: Data pada WorkBook yang lama tidak tersinkron dengan WorkBook yang baru ini. Karena fitur Move or Copy hanya bertujuan untuk menyalin atau memindahkan saja.

Jadi jika data pada File Excel yang lama berubah, maka data pada WorkBook yang baru tidak akan berubah.

Lalu apa selanjutnya ? Silahkan gunakan Pintasan Panduan berikut untuk mempelajari Semua Tentang WorkSheet Excel. Atau kunjungi Pusat Panduan Excel M Jurnal untuk mempelajari Semua Tentang Microsoft Excel.