Belajar dan Praktik XLOOKUP (Bandingkan dengan VLOOKUP)

Sebagai seorang yang baru belajar ke arah data analyst, salah satu kemahiran asas yang penting ialah kebolehan memadankan data dari jadual yang berbeza. Dalam Excel, fungsi yang sering digunakan untuk tujuan ini ialah VLOOKUP atau XLOOKUP.

Dalam entri blog ini, saya akan berkongsi:

Apa itu VLOOKUP dan XLOOKUP?
Beza antara kedua-duanya
Bagaimana saya praktis XLOOKUP dari sudut pemikiran data analyst.

Apa itu VLOOKUP?

VLOOKUP (Vertical Lookup) ialah fungsi dalam Microsoft Excel yang digunakan untuk mencari nilai tertentu dalam column pertama sesuatu jadual, kemudian memaparkan maklumat berkaitan dari column lain pada baris yang sama.

Bayangkan kita adalah seorang Data Analyst. Sebagai seorang Data Analyst, tugas utama kita bukan sekadar menaip data, tetapi menyelesaikan masalah menggunakan data. Bayangkan ada beribu-ribu baris data jualan, dan bos bertanya, "Berapa harga barang untuk kod produk ABC?". Tak kan nak cari satu persatu, kan.

Antara contoh yang lain, bos akan suruh kita:

Mencari harga barang berdasarkan kod produk
Mencari nama pekerja berdasarkan nombor ID
Mencari markah pelajar berdasarkan nombor matrik

Di sinilah betapa bergunanya kita menggunakan VLOOKUP. Tidak perlu lagi mencari maklumat satu-persatu secara manual sebab VLOOKUP akan menjadi "pembantu peribadi" kita.

Apa tugas sebenar Data Analyst bila guna VLOOKUP?

Seorang Data Analyst jarang membuat jadual dari kosong. Kerja kita biasanya macamni:

Bos bagi 2 fail yang berbeza. Satu fail ada Senarai Jualan, satu lagi ada Senarai Harga.
Kita perlu masukkan harga ke dalam fail jualan tadi supaya jumlah keuntungan boleh dikira.
VLOOKUP digunakan untuk mempamerkan harga dari fail kedua masuk ke fail pertama secara automatik.

Tanpa VLOOKUP, kita terpaksa copy-paste satu-persatu. Kalau ada 10,000 baris data, berpinar mata dibuatnya nanti!

Mengapa Data Analyst Perlu VLOOKUP?

Dalam dunia kerja sebenar, data selalunya bersepah. Contohnya:

Fail A: Senarai ID Pekerja dan Nama.
Fail B: Senarai ID Pekerja dan Gaji.
Tugas Anda: Gabungkan Nama dan Gaji dalam satu jadual. VLOOKUP akan buat kerja ini dalam sesaat.

Formula VLOOKUP

=VLOOKUP(nilai_dicari, kawasan_data, nombor_column, jenis_carian)

Komponen Penting:

nilai_dicari: Apa yang kita nak cari? (Contoh: ID Pekerja "E001").
kawasan_data: Di mana jadual rujukan? (Contoh: Sel A2 hingga C100).
Nota Penting: Nilai yang dicari mesti berada di column paling kiri dalam kawasan ini.
nombor_column: Di column ke berapa jawapan itu berada? (Column pertama dikira sebagai 1, kedua sebagai 2, dan seterusnya).
jenis_carian:

FALSE (Tepat): Cari nilai yang sama sebijik! (Paling kerap digunakan oleh Analyst).
TRUE (Anggaran): Cari nilai yang paling dekat (Biasanya untuk julat markah atau cukai).

Contoh Situasi (Work Case)

Katakan kita sedang menganalisis data jualan kedai gadget:

Tugasan: Kita ingin tahu harga bagi P002.
Formulanya: =VLOOKUP("P002", A2:C4, 3, FALSE)

"P002": Nilai yang kita cari.
A2:C4: Kawasan jadual kita.
3: Kita nak ambil Harga, dan Harga berada di column ke-3 (A=1, B=2, C=3).
FALSE: Kita nak cari kod "P002" yang tepat sahaja.

Hasilnya: Excel akan terus paparkan RM 120.

Kekurangan VLookup

Sebagai Data Analyst, kita akan sedar VLOOKUP ada beberapa kelemahan. Kita perlu tahu bahawa fungsi ini mempunyai beberapa "pantang larang" atau kekurangan yang boleh membuatkan kesilapan pada kerja analisis jika tidak berhati-hati.

Berikut adalah kekurangan utama VLOOKUP yang diterangkan secara ringkas:

1. Masalah "Left-to-Right"

Dalam Excel, VLOOKUP ada satu peraturan tetap: Lookup Value (nilai yang anda cari) MESTI berada di column paling kiri dalam Table Array (kawasan data).

Kekurangan: VLOOKUP tidak boleh buat "Left Lookup".
Contoh: Kalau kita cari ID Pekerja di column B, kita hanya boleh ambil data dari column C, D, atau E. Kita tidak boleh ambil data dari column A.

2. Static Column Index

VLOOKUP menggunakan nombor tetap (seperti 2 atau 3) untuk menentukan column mana yang hendak diambil datanya.

Kekurangan: Jika insert atau delete column di tengah-tengah jadual, nombor itu tidak akan berubah secara automatik.
Kesannya: Formula akan jadi broken (rosak) atau memaparkan data yang salah kerana kedudukan column sudah lari.

3. Performance Issue (Masalah Kelajuan)

Apabila anda bekerja sebagai Data Analyst, anda akan mengendalikan Large Datasets (data yang sangat besar, beribu-ribu baris).
Kekurangan: VLOOKUP agak "berat". Jika ada terlalu banyak formula VLOOKUP dalam satu fail, Excel akan menjadi sangat perlahan atau lag.

4. Default Search Mode
Secara default, kalau kita tidak letak perkataan FALSE di hujung formula, Excel akan anggap kita mahu Approximate Match (carian anggaran).

Kekurangan: Ini sangat bahaya untuk Data Analyst kerana kita selalunya mahukan Exact Match (carian tepat). Kalau terlupa menaip FALSE, tanpa kita sedari Excel mungkin beri jawapan yang salah.

Ringkasan Istilah Penting:

Table Array: Kawasan atau julat data.
Column Index Number: Nombor urutan column.
Exact Match: Carian yang 100% sama.
Reference: Rujukan data.

Apa Pilihan yang Lebih Baik?

Disebabkan beberapa kekurangan VLOOKUP, Data Analyst profesional zaman sekarang biasanya sudah mula beralih kepada fungsi yang lebih moden dan "tahan lasak" iaitu XLOOKUP.

Dahulu, Data Analyst terpaksa bergelut dengan kelemahan VLOOKUP, tetapi XLOOKUP adalah "versi moden" yang jauh lebih bijak, pantas, dan mudah.

Apa itu XLOOKUP?

XLOOKUP adalah fungsi pencarian data yang paling berkuasa di Excel. Ia dicipta untuk menggantikan fungsi lama iaitu VLOOKUP dan HLOOKUP.
Sebagai seorang Data Analyst, tugas kita adalah connecting the dots. Contohnya, kita ada satu fail yang mengandungi beribu-ribu Order ID, dan kita perlu mencari siapa Customer untuk setiap order itu dari fail lain. XLOOKUP adalah alat yang paling sesuai untuk melakukan tugas ini.

Kenapa Data Analyst "Wajib" tahu XLOOKUP?

Berikut adalah kelebihan XLOOKUP yang akan memudahkan kerja harian kita:

Flexible Direction:

Berbeza dengan VLOOKUP yang hanya boleh cari ke arah kanan, XLOOKUP boleh ke arah kiri, kanan, atas, atau bawah. Kita tidak perlu lagi menyusun semula column dalam jadual.

Lebih Selamat (Robust):

Jika kita tambah atau buang column dalam jadual, formula XLOOKUP tidak akan rosak (broken). Ini sangat penting untuk menjaga kualiti data kita.

Tiada lagi Error #N/A (Built-in "If Not Found"):

Dahulu, kalau data tidak dijumpai, Excel akan tunjuk ralat #N/A. Dalam XLOOKUP, kita boleh set supaya ia tunjuk perkataan seperti "Data Tiada" atau "0". Ini menjadikan laporan kita nampak lebih profesional.

Carian Tepat secara Automatik (Default Exact Match):

Secara automatik, XLOOKUP akan mencari nilai yang tepat. Kita tidak perlu lagi taip 'FALSE' di hujung formula seperti dalam VLOOKUP.

Formula XLOOKUP

Formula XLOOKUP biasanya menggunakan 3 bahagian utama ini sahaja:

=XLOOKUP(nilai_dicari, column_carian, column_hasil)

Komponen penting:

nilai_dicari (Lookup Value): Apa yang kita nak cari? (Contoh: Kod Produk "P001").
column_carian (Lookup Array): Di column mana kod itu berada? (Hanya pilih satu column sahaja).
column_hasil (Return Array): Di column mana jawapan yang kita nak ambil? (Contoh: Column Harga).

Situasi dalam Pekerjaan Data Analyst

Bayangkan bos bagi tugasan: "Tolong carikan nama penuh bagi 500 ID pekerja yang ada dalam senarai ini."

Cara Lama (VLOOKUP):

Kita kena pastikan ID Pekerja ada di coumn pertama, kemudian kira column ke berapa nama itu berada. Kalau tersalah kira, data jadi salah.

Cara Baru (XLOOKUP):

Klik pada ID yang dicari.
Pilih column ID dalam data.
Pilih column Nama.

Siap! Walaupun column Nama berada di sebelah kiri kolom ID, ia tetap berfungsi.

Kekurangan XLOOKUP

Sebagai seorang Data Analyst, anda perlu tahu bahawa XLOOKUP masih mempunyai beberapa kekurangan.

Memahami kekurangan ini penting supaya kita tahu bila masanya untuk menggunakan alat lain (seperti Power Query atau SQL) apabila data menjadi terlalu kompleks.

Berikut adalah kekurangan XLOOKUP:

1. Compatibility Issue
Ini adalah kekurangan yang paling besar sebab XLOOKUP hanya ada dalam versi Excel yang terbaru(2019 ke atas).

Masalah: Jika anda buat laporan menggunakan XLOOKUP dan hantar kepada pelanggan atau bos yang menggunakan Excel 2019 atau versi lebih lama, formula itu tidak akan berfungsi.
Kesannya: Mereka akan nampak ralat #NAME? dalam sel tersebut.

2. Boleh Menjadi Berat (Performance Impact)
Sama seperti VLOOKUP, jika anda menggunakan terlalu banyak XLOOKUP dalam satu fail yang mempunyai millions of rows (jutaan baris data), Excel anda akan mula manjadi "lembab" atau lag.

Masalah: XLOOKUP perlu mengira semula (recalculate) setiap kali ada perubahan pada data.
Kesannya: Fail anda mungkin akan freeze atau mengambil masa loading time yang lama untuk dikira semula.

3. Risiko Tersalah Pilih Column (Range Mismatch Error)
Dalam XLOOKUP, anda perlu pilih dua kawasan: Lookup Array (column carian) dan Return Array (column jawapan).

Masalah: Kedua-dua kawasan ini MESTI mempunyai saiz yang sama (contohnya: kedua-duanya dari baris 1 hingga 100).
Kesannya: Jika satu column dipilih 100 baris, tapi column jawapan dipilih 101 baris, XLOOKUP akan memberikan ralat #VALUE!.

4. Tidak Boleh Mencari Data dalam Fail Tertutup (Closed Workbook Issue)

Kadangkala, Data Analyst perlu ambil data dari fail Excel yang lain.

Masalah: Jika anda menggunakan XLOOKUP untuk merujuk kepada fail Excel lain, fail rujukan tersebut mesti dibuka pada waktu yang sama.
Kesannya: Jika fail rujukan itu ditutup, kadangkala data tidak dapat dikemaskini dengan betul atau menunjukkan ralat.

Tugas seorang Data Analyst bukan hanya tahu cara guna formula, tapi perlu tahu had setiap formula.

Jika data sikit: Guna XLOOKUP.
Jika perlu hantar fail ke orang yang guna Excel lama: Guna VLOOKUP.
Jika data terlalu besar (ratusan ribu baris): Guna Power Query atau SQL.

Perbezaan antara VLOOKUP dan XLOOKUP

Bayangkan kita ada senarai Stok Kedai:

Kolum A (Kod Produk)	Kolum B (Nama Barang)	Kolum C (Harga)
P01	Laptop	RM 2,500
P02	Mouse	RM 50
P03	Keyboard	RM 120

Soalan: kita mahu cari Harga bagi P02.

1. Cara VLOOKUP
Formula:
=VLOOKUP("P02", A2:C4, 3, 0)

Kenapa ia pening sikit?

3: Kena kira secara manual. Column A(1), B(2), C(3). Harga ada di column ke-3. Kalau tersalah kira, jawapan akan jadi salah.
0: Anda wajib letak 0 atau FALSE di hujung supaya Excel dapat mencari padanan yang tepat. Kalau lupa letak, Excel mungkin bagi jawapan yang salah.

2. Cara XLOOKUP
Formula:
=XLOOKUP("P02", A:A, C:C)

Kenapa ia jauh lebih senang?

A:A: Pilih column Kod Produk (di mana nak cari).
C:C: Pilih column Harga (apa nak diambil).
Tidak perlu nombor: Tak perlu kira "1, 2, 3". Hanya klik column yang kita mahu.
Tidak perlu taip "0": XLOOKUP secara automatik tahu kita nak cari padanan yang tepat.

Tips Tambahan untuk Beginner

Dalam XLOOKUP, jika data yang kita cari tiada dalam senarai, kita boleh tambah mesej sendiri tanpa guna formula lain.
Contoh: =XLOOKUP(105, A:A, C:C, "Tiada dalam rekod")

Kenapa kena letak FALSE atau 0 di formula VlookUp?

Dalam Excel, bahagian terakhir formula VLOOKUP itu dipanggil [range_lookup]. Ia memberitahu Excel: "Macam mana cara kau nak cari data aku ni?"

Ada dua pilihan, iaitu:
1. FALSE (atau 0) = Exact Match
Ini yang kita selalu guna. Kita akan minta Excel mencari kod "P02".

Jika Excel jumpa "P02", dia akan paparkan jawapan.
Jika Excel jumpa "P021" atau "P01", dia akan abaikan sebab tak sama seperti yang dicari.
Jika tak jumpa langsung, dia bagi error #N/A.
Contoh: Mencari ID pekerja, kod produk, nombor IC (benda yang unik).

2. TRUE (atau 1) = Approximate Match
Inilah masalahnya kalau kita lupa letak 0, Excel akan secara automatik menganggap kita mahu TRUE.

Jika Excel tak jumpa "P02", dia akan bagi jawapan daripada kod yang paling dekat (sebelumnya).
Dengan syarat, data kita mesti disusun mengikut abjad/nombor (A-Z). Kalau data bersepah, Excel akan bagi jawapan "sampah" atau salah.
Contoh: Mencari gred markah (Contoh: 0-39=Gagal, 40-59=Lulus).

Kenapa XLOOKUP lebih selamat?

XLOOKUP lebih selamat sebab pencipta Excel sedar yang 99% orang guna Excel mahukan "Padanan yang Tepat" (0/FALSE).

Dalam VLOOKUP: Kita "dipaksa" taip 0 setiap kali menulis formula. Kalau tertinggal, risiko mendapat data yang salah adalah sangat tinggi.
Dalam XLOOKUP: XLOOKUP sudah tetapkan secara automatik (default) untuk mencari padanan yang tepat. Kita tak perlu menaip apa-apa di belakang formula sebab jawapan yang diberikan adalah tepat dan sama.

Contoh Situasi "Bahaya" VLOOKUP

Katakan kita mencari ID pekerja 105... tapi dalam senarai cuma ada 101, 102, 103, 104.

Guna VLOOKUP (tanpa letak 0 atau FALSE): Excel mungkin akan bagi nama pekerja ID 104 kepada anda. Ini bahaya sebab maklumat itu salah!
Guna XLOOKUP: Excel akan terus bagi #N/A (atau mesej yang anda tetapkan) sebab 105 memang tiada dalam data.

Kesimpulannya: VLOOKUP itu ibarat mesin lama yang menggunakan suis "manual" yang kena sentiasa ditekan. XLOOKUP pula mesin moden yang sudah ada suis "automatik".

Jadual Perbandingan VLOOKUP dan XLOOKUP

Ciri-ciri (Feature)	VLOOKUP	XLOOKUP
Lookup Direction	Left-to-Right only	Any direction (Horizontal & Vertical)
Column Selection	Manual counting (1, 2, 3...)	Select range/column directly
Inserting Columns	Breaks the formula	Formula remains intact (Stable)
Default Match	Approximate (Anggaran)	Exact Match (Tepat)
Missing Data	Returns #N/A error	Built-in "If Not Found" argument

Kenapa Data Analyst kena tahu VLOOKUP dan XLOOKUP?

Walaupun XLOOKUP jauh lebih hebat, kita tetap kena tahu tentang cara menggunakan VLOOKUP sebab:

Banyak syarikat masih menggunakan Legacy Files (fail lama) yang menggunakan Excel versi lama.
Sesetengah sistem pangkalan data masih menggunakan logik yang sama dengan VLOOKUP.

Kesimpulannya:

Gunakan XLOOKUP untuk kerja harian sebab ianya lebih selamat dan lebih pantas.
Gunakan VLOOKUP hanya jika kita terpaksa bekerja dengan versi Excel yang sangat lama(sebelum versi 2019).

Praktis XLOOKUP dari sudut pandangan Data Analyst

Untuk berfikir seperti seorang Data Analyst, kita tidak boleh melihat XLOOKUP sekadar "formula Excel". Kita perlu melihatnya sebagai alat untuk Data Integration (menggabungkan data) dan Data Cleaning (membersihkan data).
Berikut adalah 3 tahap praktis untuk mengasah pemikiran kita.

Tahap 1: Pemikiran "Connecting the Dots" (Gabung Data)

Seorang Analyst jarang dapat data yang "siap". Biasanya data berterabur dalam pelbagai fail.

Tugasan: Kita ada dua jadual:

Jadual Jualan: Mengandungi Order ID dan Product ID.
Jadual Produk: Mengandungi Product ID, Category dan Price.

Cara Berfikir:

Masalah: Jadual Jualan tidak mempunyai harga, jadi kita tidak boleh kira jumlah keuntungan.
Solusi: Kita perlu "tarik" harga dari Jadual Produk masuk ke Jadual Jualan menggunakan Product ID sebagai penyambung (Common Key).

Praktis Formula: =XLOOKUP(Product_ID_Jualan, Column_ID_Produk_Rujukan, Column_Harga_Rujukan)

Tahap 2: Pemikiran "Defensive Reporting" (Kawal Ralat)

Data Analyst yang bagus tidak akan biarkan laporan mereka penuh dengan ralat #N/A. Ini membuatkan bos ragu-ragu dengan hasil kerja kita.

Tugasan: Kita sedang mencari status keahlian (Member Status) bagi pelanggan. Ada pelanggan baru yang belum didaftarkan dalam sistem rujukan.

Cara Berfikir:

Masalah: Jika kita guna VLOOKUP, pelanggan baru akan dipaparkan sebagai #N/A. Ini menunjukkan formula saya rosak.
Solusi: Taipkan fitur 'if_not_found' dalam XLOOKUP untuk menunjukkan status yang jelas.

Praktis Formula: =XLOOKUP(Cust_ID, ID_List, Status_List, "Belum Berdaftar") (Jika ID tiada, Excel akan tulis "Belum Berdaftar" dan bukannya error)

Tahap 3: Pemikiran "Automation & Scalability"

Seorang Analyst mahu formula yang "set and forget". Sekali buat, ia terus berfungsi walaupun data berubah.

Tugasan: Kita perlu mencari nama pelanggan berdasarkan nombor telefon. Tetapi dalam database, nombor telefon berada di column C dan nama di Column A.

Cara Berfikir:

Masalah: VLOOKUP tak boleh cari di bahagian kiri (Left Lookup). Kalau kita alihkan column, mungkin sistem lain yang berkongsi fail ini akan rosak.
Solusi: Guna XLOOKUP supaya kita tidak perlu usik susunan asal database (Maintain Data Integrity).

Praktis Formula: =XLOOKUP(Phone_No_Cari, Column_C_Database, Column_A_Database)

Tips Praktis

Untuk mahir, cuba buat aktiviti ini setiap kali kita melihat data:

Identify the Key: Kenalpasti column yang unik seperti ID, IC, atau SKU.
Locate the Source: Di mana "Kamus" atau rujukan bagi maklumat yang hilang itu?
Check Direction: Adakah data yang kita mahu itu di sebelah kiri atau kanan? (Kalau kiri, terus guna XLOOKUP).
Handle Missing Data: Apa maklumbalas yang kita mahu berikan kepada pengguna laporan jika data itu tiada?

Tips Tambahan untuk Nampak Seperti "Data Analyst"

Gunakan Terma Industri: Gunakan perkataan seperti Reference Table, Lookup Value, Source Data dan Data Integrity.
Tunjukkan Efisiensi: Sebutkan bahawa XLOOKUP menjimatkan masa pemprosesan (computation time) jika data anda sangat besar (ribuan baris).
Sediakan Link Latihan: Kita boleh buat satu fail Excel untuk pembaca muat turun dan cuba sendiri.

Sebagai bakal data analyst, kebolehan untuk menyesuaikan diri dengan formula Excel seperti XLOOKUP adalah satu nilai bonus yang berbaloi. Dalam dunia analisis, masa itu emas. Dengan beralih daripada VLOOKUP ke XLOOKUP, kita bukan sahaja dapat bekerja dengan lebih pantas, malah boleh menunjukkan kepada bakal majikan bahawa kita sentiasa up-to-date dengan teknologi terkini. Teruskan berlatih, bina portfolio yang mantap, dan jadikan XLOOKUP sebagai salah satu 'senjata' utama dalam profil anda!

Tugasan untuk Anda:

Cuba ambil salah satu projek Excel lama anda yang menggunakan VLOOKUP, dan tukarkan semuanya kepada XLOOKUP. Perhatikan perbezaannya dan ceritakan di ruangan komen jika anda rasa ia lebih memudahkan kerja anda!"

Search

Diari Data Analyst