Cara Membandingkan Dua Lajur Dalam Excel Menggunakan VLOOKUP

Apabila bekerja dengan set data yang besar dalam Excel, anda mungkin perlu membandingkan dua lajur untuk mencari persamaan atau perbezaan.

VLOOKUP ialah fungsi berkuasa yang membolehkan anda mencari data yang sepadan antara dua lajur. Fungsi ini membolehkan anda mencari nilai dalam satu lajur yang muncul dalam lajur lain.

Artikel ini menunjukkan kepada anda cara membandingkan dua lajur menggunakan VLOOKUP, supaya anda boleh menganalisis data anda dengan cekap.

Isi kandungan

• Asas Fungsi Vlookup
  • Fungsi Vlookup
• Menyediakan Data Anda untuk VLOOKUP
  • Memformat data
• Cara Menggunakan VLOOKUP Untuk Membandingkan Dua Lajur
  • Mengendalikan ralat lain
• Alternatif kepada VLOOKUP untuk Perbandingan Lajur
  • 1. Menggunakan Fungsi Indeks dan Padanan
  • 2. Menggunakan XLOOKUP dalam Excel
• Lima Petua untuk Meningkatkan Prestasi VLOOKUP
  • 1. Hadkan julat carian anda
  • 2. Gunakan rujukan mutlak
  • 3. Isih data anda
  • 4. Gunakan VLOOKUP berganda
  • 5. Gunakan pemformatan bersyarat
• Kegunaan Lanjutan Untuk VLOOKUP
• Kata Akhir Kami

Asas Fungsi Vlookup

Katakan anda mempunyai hamparan dengan dua senarai item dalam lajur A dan lajur B. Anda ingin mencari item dalam Senarai 1 yang turut muncul dalam Senarai 2.

Anda boleh bayangkan bahawa bekerja secara manual melalui senarai beribu-ribu item akan sangat memakan masa. Syukurlah, Excel membawa VLOOKUP untuk menyelamatkan!

Istilah VLOOKUP bermaksud Vertical Lookup. Fungsi ini akan membandingkan dua lajur, mencari padanan antaranya dan mengembalikan nilai yang berkaitan.

Fungsi Vlookup

Berikut ialah sintaks asas fungsi VLOOKUP:

=VLOOKUP(nilai_carian, tatasusunan_jadual, bilangan_indeks_kol, [julat_carian])

Ini adalah pecahan elemen:

• lookup_value : nilai yang anda ingin cari dalam lajur pertama table_array.

• table_array : julat sel yang mengandungi data yang anda ingin cari.

• col_index_num : nombor lajur dalam table_array yang anda ingin pulangkan nilainya.

• range_lookup : Pilihan. Ia sama ada BENAR (anggaran padanan) atau SALAH (padanan tepat). Lalai adalah BENAR.

Menyediakan Data Anda untuk VLOOKUP

Sebelum menggunakan VLOOKUP untuk membandingkan dua lajur dalam Excel , anda perlu menyediakan data anda.

Buat dua lajur berasingan dalam lembaran kerja anda di mana anda ingin membandingkan nilai. Artikel ini menggunakan Lajur A dan Lajur B untuk contoh kami.

Berikut ialah data sampel yang digunakan dalam artikel ini:

Ini ialah jadual contoh dengan pengepala untuk ditambahkan pada bahagian bertajuk "Menyediakan Data Anda untuk VLOOKUP":

Senarai 1 Senarai 2
Perkara 1
Perkara 3 Perkara 2
Perkara 4 Perkara 3 Perkara 1
Perkara 4 Perkara 5

Memformat data

Adalah penting untuk memastikan bahawa data dalam kedua-dua lajur diformatkan sama. VLOOKUP adalah sensitif huruf besar-besaran, yang bermaksud huruf besar dan huruf kecil penting.

Ralat pemadanan mungkin berlaku dalam hasil akhir jika pemformatan tidak konsisten.

Ia juga merupakan idea yang baik untuk mengalih keluar sebarang nilai pendua atau sel kosong untuk meminimumkan risiko ralat.

Untuk mengalih keluar nilai pendua:

1. Pilih lajur.

2. Pergi ke tab Data dalam reben atas.

3. Klik pada Alih Keluar Pendua dalam bahagian Alat Data.

Butangnya agak sukar untuk dilihat. Gambar ini akan membantu anda:

Untuk mengalih keluar sel kosong:

1. Pilih lajur.

2. Pergi ke tab Laman Utama dalam reben atas.

3. Kembangkan menu "Cari & Pilih".

4. Pilih "Pergi ke Khas".

5. Pilih "Kosong" daripada pilihan.

6. Klik pada "Padam" dalam bahagian Sel.

Cara Menggunakan VLOOKUP Untuk Membandingkan Dua Lajur

Setelah data disediakan, anda boleh menulis formula VLOOKUP untuk membandingkan dua lajur dalam Excel dan mengenal pasti padanan. Ikut langkah-langkah ini:

1. Pilih sel dalam lajur baharu yang anda mahu memaparkan hasil perbandingan (cth, sel C2).

2. Taip formula berikut: =VLOOKUP(A2, B:B, 1, FALSE))

3. Tekan Enter untuk menggunakan formula.

4. Dalam kes nilai yang sepadan, nilai akan dipaparkan dalam baris yang sama lajur hasil (cth, Lajur C).

5. Seret formula ke bawah dari C2 untuk menyalinnya ke atas seberapa banyak sel yang anda perlukan.

Jika anda menggunakan data sampel kami, letakkan hasil perbandingan anda dalam lajur ketiga. Formula akan mencari tiga padanan daripada empat item dalam Senarai 1.

Perhatikan bahawa Item 2 ditunjukkan sebagai #N/A. Excel memberitahu kami bahawa padanan itu tidak berkenaan iaitu ia tidak ditemui.

Itu betul dan merupakan maklumat berguna tentang nilai yang hilang. Walau bagaimanapun, sesetengah pengguna Excel mungkin berfikir bahawa terdapat beberapa isu data atau ralat fungsi.

Amalan yang baik untuk menunjukkan petunjuk berbeza bahawa tiada padanan ditemui. Itu boleh jadi kosong.

Untuk berbuat demikian, gabungkan fungsi VLOOKUP dengan fungsi IFNA seperti ini:

=IFNA(VLOOKUP(A1, B:B, 1, SALAH), “”)

Fungsi IFNA mengesan ralat #N/A dan menggantikan output dengan ruang kosong (“”). Anda juga boleh menggunakan fungsi ISNA atau mempunyai logik tambahan dengan fungsi IF.

Mengendalikan ralat lain

VLOOKUP kadangkala boleh menjana ralat lain dalam hasil perbandingan anda. #REF ! Ralat dalam Excel adalah masalah tulen dengan data anda.

Ia biasanya berlaku apabila julat yang anda tentukan tidak betul. Contoh kami telah merujuk keseluruhan lajur, tetapi anda juga boleh menggunakan julat sel menegak.

Pastikan julat carian yang anda rujuk meliputi semua nilai yang ingin anda bandingkan.

Alternatif kepada VLOOKUP untuk Perbandingan Lajur

Terdapat dua fungsi carian alternatif utama apabila membandingkan dua lajur dalam Excel untuk mencari padanan.

1. Menggunakan Fungsi Indeks dan Padanan

Daripada menggunakan VLOOKUP, anda boleh membandingkan dua lajur dalam Excel dengan menggabungkan fungsi INDEX dan MATCH.

Kaedah ini menyediakan cara yang lebih fleksibel untuk mencari data dan amat berguna apabila bekerja dengan lajur bukan bersebelahan atau apabila indeks lajur mungkin berubah.

Ini ialah sintaks untuk dimasukkan ke dalam lajur hasil:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range : julat sel yang mengandungi data yang anda ingin pulangkan.

• Lookup_value : nilai yang anda ingin cari dalam julat carian.

• Carian_julat : julat sel dalam mana anda ingin mencari nilai carian.

Menggunakan data yang sama dalam contoh sebelumnya, kami menggantikan formula VLOOKUP seperti berikut:

=(INDEX(B:B, PADANAN(A2, B:B, 0)))

Fungsi MATCH mengembalikan kedudukan relatif nilai carian dalam julat carian dan fungsi INDEX mengembalikan nilai yang sepadan daripada julat pulangan.

Keputusan pada data yang sama akan sama dengan VLOOKUP yang kami gunakan sebelum ini. Gambar ini menunjukkan fungsi yang digunakan:

Anda juga boleh menggantikan ralat #N/A dengan mesej tersuai. Berikut adalah contoh penggunaan fungsi IFERROR.

=IFERROR(INDEX(B:B, MATCH(A2, B:B, 0)), “Tidak Ditemui”)

2. Menggunakan XLOOKUP dalam Excel

Untuk pengguna dengan Excel 365 atau Excel 2019, XLOOKUP ialah ujian logik alternatif kepada VLOOKUP untuk mencari nilai sepunya dalam dua lajur.

XLOOKUP menyediakan beberapa kelebihan. Anda boleh menggunakannya untuk mencari data secara mendatar dan menegak, bekerja dengan lajur bukan bersebelahan dan menentukan nilai tersuai untuk ralat.

Sintaks XLOOKUP ialah:

=XLOOKUP(nilai_carian, julat_carian, julat_pulangan, [jika_tidak_ditemui], [mod_padanan], [mod_carian])

• lookup_value : nilai yang anda ingin cari dalam julat carian.

• lookup_range : julat sel di mana anda ingin mencari nilai carian.

• return_range : julat sel yang mengandungi data yang anda ingin pulangkan.

• Tambahkan nilai ralat tersuai, mod padanan dan parameter mod carian mengikut keperluan.

XLOOKUP akan mencari nilai carian dalam julat carian dan mengembalikan nilai yang sepadan daripada julat pulangan.

Formula ini amat berguna apabila anda mempunyai dua senarai yang dibenamkan dalam set data berbilang lajur. Dalam contoh kami sebelum ini, senarai telah berada dalam lajur pertama dan lajur kedua, tetapi helaian anda mungkin mempunyai lebih banyak data daripada itu.

Berikut ialah contoh dengan dua senarai dalam lajur pertama dan ketiga hamparan:

Formula XLOOKUP kelihatan seperti ini:

=XLOOKUP(A2,C:C,C:C,””)

Gambar ini menunjukkan keputusan dengan perbandingan dalam lajur D. Nilai pertama ada tetapi yang kedua tiada.

Ambil perhatian bahawa saya tidak mempunyai formula ralat tambahan tetapi nilai yang hilang dipaparkan sebagai kosong. Itu kerana saya menggunakan parameter keempat yang merupakan nilai ralat tersuai. Dalam kes ini, ia adalah rentetan kosong.

Sebagai bonus, saya akan menunjukkan kepada anda cara menggunakan XLOOKUP untuk membandingkan merentas baris. Jika anda mempunyai dua senarai yang berada di baris 1 dan baris 2, penggunaan formula yang paling mudah kelihatan seperti ini:

=XLOOKUP(B1, 2:2, 2:2, “”)

Gambar ini menunjukkan keputusan pada dua baris.

Dengan fungsi dan fleksibiliti yang dipertingkatkan, XLOOKUP ialah alternatif terbaik kepada VLOOKUP untuk membandingkan senarai dalam Excel untuk menentukan sama ada padanan ada.

Lima Petua untuk Meningkatkan Prestasi VLOOKUP

Mengoptimumkan fungsi VLOOKUP anda boleh membantu anda mengelakkan masa menunggu yang lama dan meningkatkan responsif keseluruhan lembaran kerja Excel anda.

Berikut ialah enam petua dan kiat yang boleh anda gunakan untuk meningkatkan prestasi VLOOKUP anda dalam Excel.

1. Hadkan julat carian anda

Saya menggunakan keseluruhan lajur dalam contoh untuk menjadikannya mudah. Jika anda bekerja dengan sejumlah besar data, anda harus mengelak daripada berbuat demikian. Menggunakan lajur penuh boleh melambatkan proses pengiraan Excel.

Sebaliknya, cuba gunakan julat tepat yang diperlukan untuk data anda (cth, A1:A100). Ini mengurangkan bilangan sel yang perlu dinilai oleh fungsi VLOOKUP anda.

2. Gunakan rujukan mutlak

Apabila menentukan julat data (cth sel dari B2 hingga B5), gunakan rujukan mutlak. Ini memastikan formula konsisten dan data wujud apabila anda menyalinnya merentas berbilang sel.

Berikut adalah contoh:

=VLOOKUP(A2, $B$2:$B$5, 1, SALAH)

3. Isih data anda

Jika anda tahu bahawa data dalam lajur carian diisih dalam tertib menaik, anda boleh menggunakan VLOOKUP dengan BENAR atau 1 untuk hujah 'range_lookup'.

Ini akan menyebabkan Excel melakukan padanan anggaran, yang lebih pantas daripada padanan tepat merentas semua sel. Walau bagaimanapun, berhati-hati semasa menggunakan pilihan ini, kerana isihan yang salah mungkin membawa kepada hasil yang salah.

4. Gunakan VLOOKUP berganda

Anda boleh menggunakan dua fungsi VLOOKUP untuk mempercepatkan proses carian dalam Excel.

VLOOKUP pertama akan menentukan sama ada nilai carian wujud dengan menetapkan 'col_index_num' kepada 1 dan 'range_lookup' kepada TRUE.

Jika ia mengembalikan TRUE, VLOOKUP kedua mendapatkan semula nilai yang dikehendaki dengan 'range_lookup' ditetapkan kepada TRUE.

5. Gunakan pemformatan bersyarat

Anda boleh menggunakan pemformatan bersyarat dalam lembaran kerja Excel anda untuk menyerlahkan nilai yang sepadan atau tiada dalam lajur yang ditentukan. Anda juga boleh menggunakan warna pada nilai unik. Ini menjadikan data anda lebih mudah dibaca.

Anda akan menemui menu pemformatan bersyarat dalam kumpulan gaya pada tab Laman Utama.

Kegunaan Lanjutan Untuk VLOOKUP

Sampel berpasangan Ujian-T digunakan untuk membandingkan min dua sampel yang berkaitan. Video ini menunjukkan penggunaannya dalam Excel.

Jika anda mempunyai data tambahan atau pembolehubah yang perlu anda rujuk semasa analisis anda, anda boleh menggunakan VLOOKUP untuk mendapatkan semula nilai yang diperlukan daripada jadual atau lembaran kerja lain.

Sebagai contoh, anda boleh menggunakan VLOOKUP untuk mendapatkan maklumat demografi atau keadaan rawatan bagi setiap pemerhatian berpasangan dalam set data anda.

Kata Akhir Kami

Sekarang, anda mempunyai pemahaman yang kukuh tentang menggunakan VLOOKUP. Fungsi berkuasa ini membolehkan anda mengenal pasti perbezaan dan nilai yang sepadan dengan cepat antara dua senarai, menjadikan analisis data lebih cekap dan tepat.

Anda juga melihat contoh fungsi carian dan rujukan lain, seperti XLOOKUP, INDEX dan MATCH. Menambah elemen ini pada kemahiran anda akan mengukuhkan lagi keupayaan analisis data anda.

Teruskan berlatih dan memperhalusi kemahiran VLOOKUP anda, dan tidak lama lagi anda akan menjadi pakar dalam membandingkan lajur dalam Excel, menjimatkan masa dan meningkatkan produktiviti anda.