Cara Menggabungkan Dua File Excel Tanpa VLOOKUP (Langkah demi Langkah)

Anda dapat menggabungkan dua file Excel tanpa VLOOKUP dengan tiga cara. XLOOKUP memperbaiki masalah arah dan pencocokan pada VLOOKUP. Merge pada Power Query melakukan penggabungan (join) yang sebenarnya dan memperbaruinya saat file berubah. Agen data AI memungkinkan Anda mendeskripsikan penggabungan tersebut dalam bahasa sehari-hari dan melewati rumus sepenuhnya. Pilihan mana yang cocok bergantung pada apakah Anda memerlukan tabel yang digabungkan atau jawaban di baliknya.

Tugas ini sendiri ada di mana-mana. Anda memiliki daftar pelanggan di satu file dan ekspor pesanan di file lain, dan satu-satunya hal yang menghubungkannya adalah alamat email atau ID akun. Anda membutuhkannya dalam satu tampilan sebelum dapat menjawab pertanyaan apa pun yang berguna.

VLOOKUP adalah rumus yang selalu dicari semua orang, dan juga rumus yang pada akhirnya membuat semua orang kerepotan. Berikut adalah alternatifnya, diurutkan berdasarkan seberapa banyak Anda ingin memikirkan Excel.

Apa arti sebenarnya dari menggabungkan dua file

Sebuah penggabungan (join) mencocokkan baris dari dua tabel menggunakan kunci (key) bersama, lalu membawa kolom dari satu tabel ke tabel lainnya. Tiga keputusan menentukan hal ini, dan kesalahan pada salah satunya akan menghasilkan jawaban salah yang terlihat benar.

Kolom mana yang menjadi kuncinya? Email, ID pesanan, SKU, nomor akun. Kunci tersebut harus memiliki arti yang sama di kedua sisi.

Apa yang terjadi pada baris yang tidak cocok? Pertahankan setiap pelanggan meskipun mereka tidak memiliki pesanan, atau hanya pertahankan pelanggan yang memesan? Ini adalah pertanyaan berbeda dengan jawaban yang berbeda pula, dan Excel dengan senang hati akan memberikan salah satunya tanpa bertanya.

Apakah kuncinya bisa berulang? Satu pelanggan dengan lima pesanan berarti satu baris di sebelah kiri dan lima baris di sebelah kanan. Apakah Anda menginginkan lima baris atau satu baris ringkasan akan mengubah seluruh hasil.

Jawab ketiga pertanyaan tersebut sebelum Anda menulis apa pun. Sebagian besar penggabungan yang rusak bukanlah kesalahan rumus. Melainkan asumsi yang tidak dinyatakan.

Cara bawaan untuk menggabungkan dua file Excel

Opsi 1: VLOOKUP, dan mengapa rumus ini sering kali bermasalah

VLOOKUP mencari kolom paling kiri dari suatu rentang dan mengembalikan nilai dari kolom di sebelah kanannya, yang diidentifikasi dengan nomor posisi. Desain tersebut menciptakan empat jebakan umum, yang semuanya didokumentasikan pada referensi fungsi VLOOKUP Microsoft.

• Tidak dapat mencari ke arah kiri. Jika kunci Anda berada di sebelah kanan nilai yang Anda inginkan, Anda harus mengatur ulang file sumber terlebih dahulu.
• Indeks kolom adalah angka yang ditulis manual (hardcoded). Sisipkan kolom di dalam rentang pencarian dan rumus akan tetap menunjuk ke posisi 4, yang sekarang menjadi bidang yang berbeda. Tidak ada kesalahan yang muncul. Angkanya saja yang berubah.
• Jenis pencocokan secara default adalah perkiraan (approximate). Jika argumen terakhir dikosongkan, VLOOKUP akan mencari pencocokan terdekat pada data yang diasumsikan telah diurutkan. Pada data yang tidak berurutan, rumus ini akan mengembalikan nilai salah dengan sangat meyakinkan.
• Hanya mengembalikan pencocokan pertama. Jika kunci Anda berulang, Anda hanya mendapatkan baris pertama tanpa ada peringatan bahwa baris kedua hingga kelima itu ada.

VLOOKUP tidaklah buruk. Ini adalah desain era 1980-an yang diminta untuk melakukan pekerjaan database, dan ia gagal secara diam-diam alih-alih memunculkan pesan eror, yang merupakan cara kegagalan terburuk.

Opsi 2: XLOOKUP

XLOOKUP adalah pengganti modern, dan rumus ini menghilangkan tiga dari empat jebakan tersebut. XLOOKUP mencari ke segala arah dan secara default menggunakan pencocokan persis (exact match). Rumus ini menerima argumen if_not_found yang tepat alih-alih membiarkan nilai #N/A di lembar kerja Anda. Dan rumus ini merujuk ke rentang kolom alih-alih nomor posisi, sehingga menyisipkan kolom tidak akan merusaknya secara diam-diam. Referensi XLOOKUP Microsoft memiliki sintaksisnya.

Batasan yang tersisa sama dengan yang dimiliki VLOOKUP: ini masih berupa pencarian (lookup), bukan penggabungan (join). Rumus ini menarik satu nilai per baris. Kunci yang berulang tetap hanya mengembalikan hasil pertama, dan Anda masih harus memelihara rumus di ribuan baris dalam file yang akan dibuka orang lain pada kuartal berikutnya.

Opsi 3: Power Query Merge, solusi bawaan yang sebenarnya

Jika Anda menginginkan penggabungan yang sebenarnya di Excel, Merge dari Power Query adalah solusinya. Muat kedua file sebagai kueri, pilih Merge Queries, lalu pilih kolom kunci di setiap sisi. Sekarang pilih jenis penggabungan (join kind): left outer mempertahankan semua yang ada di sebelah kiri, inner hanya mempertahankan yang cocok, full outer mempertahankan kedua sisi, dan anti memisahkan baris yang gagal dicocokkan.

Anti join adalah fitur yang sering diremehkan. Fitur ini menjawab "pelanggan mana di daftar saya yang tidak memiliki pesanan sama sekali" dalam satu langkah, yang sangat melelahkan jika dilakukan dengan pencarian (lookup). Merge juga dapat diperbarui, sehingga file bulan depan akan diproses melalui penggabungan yang sama tanpa Anda harus membuatnya kembali dari awal.

Konsekuensinya adalah kurva pembelajaran. Langkah-langkah kueri, kolom tabel yang diperluas, dan jenis penggabungan semuanya sangat penting untuk diketahui. Konsep-konsep tersebut juga merupakan empat atau lima hal yang membatasi Anda dengan pertanyaan yang sebenarnya bisa Anda tanyakan dalam satu kalimat saja.

Di Mana Ketiga Cara Tersebut Menemui Jalan Buntu

Setiap cara bawaan memiliki tiga batasan yang sama.

Kunci jarang sekali bersih. john@acme.com dan John@Acme.com adalah pelanggan yang sama, tetapi pencocokan persis tidak akan menganggapnya demikian. Kunci di dunia nyata sering kali memiliki spasi di akhir, huruf besar-kecil yang tidak konsisten, angka yang disimpan sebagai teks, dan ID dengan tanda kutip tunggal yang tersasar dari hasil ekspor lama. Setiap metode bawaan mengharuskan Anda untuk menormalisasi kunci terlebih dahulu, dan tidak ada satu pun dari metode tersebut yang memberi tahu Anda bahwa itulah alasan mengapa tingkat pencocokan Anda hanya 60%.

Tabel yang digabungkan bukanlah jawaban akhir. Tidak ada yang hanya menginginkan lembar kerja yang digabungkan. Mereka ingin tahu segmen mana yang berkembang, akun mana yang churn, atau SKU mana yang menghasilkan margin. Penggabungan hanyalah sistem pipa, dan sistem pipa inilah yang menghabiskan sebagian besar waktu.

Orang berikutnya akan mewarisi rumus Anda. Buku kerja yang penuh dengan pencarian bersarang (nested lookup) adalah beban pemeliharaan. Rumus tersebut berfungsi sampai ada kolom yang bergeser.

Cara Menggabungkan Dua File Excel dengan Powerdrill Bloom

Powerdrill Bloom memperlakukan penggabungan sebagai bagian dari pertanyaan, bukan sebagai langkah yang harus Anda selesaikan terlebih dahulu. Anda cukup mengunggah kedua file, menyebutkan apa yang menghubungkannya, dan alat ini akan mencocokkan baris, melaporkan tingkat pencocokan, dan langsung melanjutkan ke analisis.

Langkah 1: Unggah kedua file

Masukkan kedua buku kerja ke dalam satu ruang kerja. Bloom membaca Excel, CSV, TSV, dan PDF, serta membersihkannya secara otomatis saat dimasukkan, sehingga spasi di akhir dan kunci dengan huruf besar-kecil yang bercampur akan ditangani alih-alih diabaikan begitu saja secara diam-diam.

Anda tidak perlu mengatur ulang kolom agar kunci berada di sebelah kiri, dan Anda tidak memerlukan kedua file tersebut untuk memiliki tata letak yang sama.

Langkah 2: Deskripsikan penggabungan dalam bahasa sehari-hari

Katakan apa yang menghubungkan keduanya dan hasil apa yang Anda inginkan. "Cocokkan file pesanan dengan file pelanggan berdasarkan alamat email, pertahankan setiap pelanggan meskipun mereka tidak memiliki pesanan, dan beri tahu saya berapa banyak yang gagal dicocokkan" adalah instruksi yang lengkap.

Lalu lanjutkan langsung, karena ini adalah bagian yang tidak dapat dilakukan oleh pencarian (lookup): "sekarang tunjukkan pendapatan berdasarkan segmen pelanggan, dan daftarkan sepuluh akun dengan penurunan terbesar dibandingkan kuartal lalu." Penggabungan dan analisis terjadi dalam satu proses sekaligus.

Jika ini adalah rutinitas bulanan, simpan sebagai keahlian agen (agent skill) dan jalankan kembali pada file bulan berikutnya alih-alih mengetiknya ulang.

Langkah 3: Ekspor hasil penggabungan, grafik, atau dek presentasi

Ambil tabel yang digabungkan sebagai file, ambil grafiknya, atau ubah seluruh kanvas menjadi dek presentasi dalam satu klik — Professional, Business, atau Fancy — lalu ekspor ke PowerPoint atau Notion.

Opsi terakhir itulah yang menghemat waktu sore Anda. Penggabungan itu sendiri bukanlah hasil akhir yang sebenarnya dicari.

Mengapa Hal Ini Lebih Penting daripada Sekadar Menghemat Rumus

Perbandingan yang penting bukanlah rumus-lawan-tanpa-rumus. Melainkan bagaimana perilaku setiap cara ketika data bermasalah.

	VLOOKUP	XLOOKUP	Power Query Merge	Powerdrill Bloom
Kunci dapat berada di mana saja	No	Yes	Yes	Yes
Tetap berfungsi saat kolom disisipkan	No	Yes	Yes	Yes
Menangani kunci berulang dengan benar	No	No	Yes	Yes
Memisahkan baris yang tidak cocok	Manual	Manual	Yes (anti join)	Yes
Membersihkan kunci yang berantakan untuk Anda	No	No	Manual steps	Yes
Melaporkan tingkat pencocokan	No	No	No	Yes
Melanjutkan untuk menjawab pertanyaan	No	No	No	Yes
Keahlian yang dibutuhkan	Formula	Formula	Query editor	Natural language

Bacalah tabel tersebut dengan jujur dan kesimpulannya bukanlah "Excel sudah usang". Melainkan bahwa alat-alat Excel dibuat untuk menghasilkan tabel yang digabungkan, dan menghasilkan tabel yang digabungkan hanyalah setengah bagian pekerjaan yang mudah.

Praktik Terbaik Saat Menggabungkan Spreadsheet

Normalisasikan kunci sebelum Anda mencocokkan apa pun

Hapus spasi kosong (trim whitespace), seragamkan huruf besar-kecil, dan pastikan bahwa ID disimpan sebagai tipe data yang sama di kedua sisi. Penggabungan pada kunci yang kotor tidak akan memunculkan eror — melainkan hanya menghasilkan pencocokan yang kurang lengkap secara diam-diam, dan tingkat pencocokan 60% akan terlihat seperti temuan bisnis alih-alih masalah data.

Selalu hitung baris yang tidak cocok

Kumpulan data yang tidak cocok biasanya merupakan hasil yang paling menarik. Pelanggan tanpa pesanan, pesanan tanpa catatan pelanggan, SKU yang ada di satu sistem tetapi tidak ada di sistem lainnya: di situlah letak masalah operasionalnya. Panduan kami tentang menggabungkan file data membahas hal ini secara lebih mendalam.

Periksa jumlah baris setelah penggabungan, bukan sebelum

Jika file kiri memiliki 4,000 baris dan hasil penggabungan memiliki 11,000 baris, kunci Anda berulang dan Anda telah melipatgandakan data tersebut. Hal itu tidak masalah jika memang Anda sengaja melakukannya, tetapi menjadi masalah serius jika tidak — terutama sebelum Anda menjumlahkan kolom pendapatan.

Tentukan hubungan satu-ke-banyak (one-to-many) sebelum Anda melakukan agregasi

Jika satu pelanggan memiliki lima pesanan, Anda mungkin menginginkan lima baris atau satu baris hasil agregasi. Menjumlahkan pendapatan pada versi data yang melipatganda akan menghasilkan perhitungan ganda. Kesalahan tunggal ini menghasilkan lebih banyak dasbor yang salah dibandingkan dengan kesalahan rumus apa pun.

Kesalahan Umum yang Harus Dihindari

1. Menggabungkan berdasarkan nama alih-alih ID. "Acme Corp", "Acme Corp.", dan "ACME Corporation" adalah tiga perusahaan yang berbeda bagi pencocokan persis.
2. Mengosongkan argumen keempat VLOOKUP. Default-nya adalah pencocokan perkiraan, yang mengembalikan nilai salah pada data yang tidak berurutan tanpa memunculkan eror.
3. Mengartikan #N/A sebagai nol. Tidak ada kecocokan dan nilai nol yang sebenarnya memiliki arti yang berlawanan, dan membungkus semuanya dalam IFERROR(...,0) akan menyembunyikan perbedaan tersebut.
4. Menggabungkan sebelum menghapus duplikat. Jika salah satu sisi memiliki kunci duplikat, penggabungan akan melipatgandakannya. Bersihkan terlebih dahulu, baru gabungkan.
5. Menjumlahkan setelah penggabungan satu-ke-banyak. Ini adalah kesalahan perhitungan ganda yang klasik. Periksa jumlah baris Anda sebelum memercayai total apa pun.

Kesimpulan

Untuk penarikan data cepat sekali pakai dengan kunci yang bersih, XLOOKUP is the right tool and takes thirty seconds. Untuk penggabungan berulang pada file yang stabil, buatlah Merge pada Power Query dan gunakan anti join untuk menemukan data yang tidak cocok. Ketika kuncinya berantakan, ketika kuncinya berulang, atau ketika yang sebenarnya Anda butuhkan adalah grafik dan dek presentasi alih-alih lembar kerja yang digabungkan, deskripsikan penggabungan tersebut alih-alih menulis rumusnya.

Anda dapat mengujinya pada dua file Anda sendiri tanpa biaya — Powerdrill Bloom menyertakan 1.000 kredit gratis yang diperbarui setiap hari. Halaman Excel AI assistant dan merge CSV files menunjukkan alur kerja yang sama, dan analyzing Excel with AI membahas versi satu file.

Pertanyaan yang sering diajukan

Apa yang bisa saya gunakan selain VLOOKUP untuk menggabungkan dua file Excel?

XLOOKUP adalah pengganti langsung dan memperbaiki kelemahan terbesar VLOOKUP: ia mencari ke segala arah, mencocokkan secara persis secara default, dan tidak rusak saat kolom disisipkan. Untuk penggabungan yang sebenarnya di antara dua tabel, Merge dari Power Query adalah alat bawaan yang lebih baik karena dapat menangani kunci yang berulang dan dapat memisahkan baris yang tidak cocok.

Apakah Power Query lebih baik daripada VLOOKUP untuk menggabungkan file?

Untuk hal apa pun yang bersifat berulang, ya. Power Query melakukan penggabungan yang sebenarnya dengan jenis penggabungan yang dapat dipilih, memperbarui data saat file sumber berubah, dan tidak meninggalkan ribuan rumus di buku kerja Anda. VLOOKUP tetap lebih cepat untuk penarikan data ad-hoc sekali pakai pada satu kolom yang bersih.

Bagaimana cara menggabungkan dua file Excel ketika kolomnya memiliki nama yang berbeda?

Power Query memungkinkan Anda memilih kolom kunci yang berbeda di setiap sisi, sehingga namanya tidak harus sama — hanya nilainya saja yang harus cocok. Agen data AI melangkah lebih jauh dengan mencocokkan kolom saat membaca file, lalu melaporkan bagian mana yang tidak sesuai di kedua sisi.

Mengapa VLOOKUP saya mengembalikan nilai yang salah alih-alih pesan eror?

Hampir selalu karena argumen keempat dikosongkan. VLOOKUP kemudian melakukan pencocokan perkiraan, yang mengasumsikan data telah diurutkan dan jika tidak, ia akan mengembalikan nilai terendah terdekat yang dapat ditemukannya. Atur argumen terakhir ke FALSE untuk memaksa pencocokan persis.

Apakah saya bisa menggabungkan dua file Excel tanpa rumus sama sekali?

Ya. Merge dari Power Query adalah cara tanpa rumus di dalam Excel, meskipun menggunakan editor kueri. Dengan agen data AI, Anda cukup mengunggah kedua file dan mendeskripsikan penggabungan tersebut dalam satu kalimat, yang tidak memerlukan rumus maupun langkah kueri sama sekali.