VLOOKUP Antara Dua Buku Kerja: Panduan Langkah Demi Langkah Mudah

Jika anda bekerja dengan berbilang buku kerja dalam Excel, anda mungkin perlu menggunakan fungsi VLOOKUP untuk mendapatkan semula data dari satu buku kerja ke buku kerja yang lain.

Fungsi VLOOKUP mencari nilai tertentu dalam jadual dalam buku kerja yang berasingan dan mengembalikan nilai yang sepadan daripada baris yang sama. Sintaks mesti menyertakan rujukan yang layak sepenuhnya kepada buku kerja kedua dan nama lembaran kerja.

Mendapatkan rujukan yang betul boleh menjadi mencabar apabila anda menggunakan fungsi untuk kali pertama. Artikel ini menggunakan hamparan sampel untuk menunjukkan kepada anda cara memasukkan formula dengan tepat.

Anda juga akan mempelajari beberapa peningkatan pada VLOOKUP dan beberapa alternatif kepada fungsi tersebut.

Mari kita mulakan!

Isi kandungan

• Apa itu VLOOKUP?
• Cara Menggunakan VLOOKUP Antara Dua Buku Kerja
  • Data Sampel
  • Langkah 1: Pastikan Kedua-dua Buku Kerja Terbuka
  • Langkah 2: Mula Memasuki Formula VLOOKUP
  • Langkah 3: Cara Rujuk Buku Kerja Kedua
• 3 Ralat Biasa VLOOKUP dan Cara Membaikinya
  • 1. #N/A Ralat
  • 2. #Ruj! Ralat Nombor Lajur
  • 3. Petikan dan Ralat Kurungan Segi Empat
• 4 Petua untuk Mengurus Buku Kerja Berbeza
• Cara Mengemas kini Data Secara Automatik
• Penggunaan Lanjutan Fungsi VLOOKUP
• 2 Alternatif Utama kepada VLOOKUP
  • 1. PADANAN INDEKS
  • 2. XLOOKUP
• Cara Menggunakan VLOOKUP Merentas Berbilang Helaian
  • Langkah 1: Kenalpasti Komponen
  • Langkah 2: Sediakan Data Anda
  • Langkah 3: Gunakan Fungsi VLOOKUP
  • (Pilihan) Langkah 4: Selesaikan Masalah #N/A Ralat
• Fikiran Akhir

Apa itu VLOOKUP?

Katakan anda mempunyai buku kerja jualan produk dan buku kerja berasingan dengan harga setiap produk. Anda ingin mengakses harga dari buku kerja kedua dan menggunakannya dalam pengiraan dalam buku kerja pertama.

VLOOKUP adalah satu penyelesaian untuk tugas ini. Istilah ini bermaksud “Pencarian Menegak . “

Ia ialah fungsi terbina yang membolehkan anda mencari nilai tertentu dalam lajur dan mengembalikan nilai yang sepadan dalam lajur lain berdasarkan lokasi.

Ia boleh digunakan dengan:

• hamparan tunggal

• berbilang helaian dalam buku kerja yang sama

• dua buku kerja Excel

Jika anda tidak mempunyai kerumitan tambahan berbilang buku kerja, lihat tutorial ini tentang membandingkan dua lajur dalam Excel menggunakan VLOOKUP .

Cara Menggunakan VLOOKUP Antara Dua Buku Kerja

Fungsi VLOOKUP amat berguna apabila anda perlu mencari data merentas berbilang lembaran kerja atau buku kerja.

Tiga langkah utama untuk melakukannya ialah:

1. Buka kedua-dua buku kerja.

2. Masukkan formula VLOOKUP dalam buku kerja Excel pertama.

3. Sediakan rujukan yang layak sepenuhnya kepada buku kerja kedua.

Data Sampel

Tutorial mudah ini berfungsi melalui contoh berdasarkan data sampel. Untuk mengikuti, buat buku kerja baharu yang dipanggil "Jualan" dan masukkan data ini dalam lajur A dan B:

• Epal, 40

• Oren, 30

• Pear, 70

• Pisang, 20

Tambahkan lajur ketiga untuk menyimpan maklumat Harga:

Buat buku kerja Excel kedua yang dipanggil "Produk" dan namakan semula Helaian1 sebagai "Harga". Masukkan data ini dalam lajur A dan lajur B:

• Epal, 1.50

• Jeruk, 2.30

• Pear, 3.60

• Pisang, 2.10

Data kelihatan seperti ini:

Untuk mengira hasil jualan dalam hamparan pertama, kita perlu mencari harga dalam hamparan kedua. VLOOKUP untuk menyelamatkan!

Langkah 1: Pastikan Kedua-dua Buku Kerja Terbuka

Kedua-dua buku kerja mesti dibuka apabila menggunakan formula VLOOKUP antara dua buku kerja. Jika buku kerja kedua ditutup, fungsi akan mengembalikan ralat.

Anda boleh sama ada membukanya secara berasingan atau menggunakan arahan "Buka" dalam Excel untuk membuka kedua-duanya sekali. Terdapat dua cara untuk membuka sama ada buku kerja:

• Pergi ke tab Fail dan pilih "Buka".

• Gunakan pintasan papan kekunci "Ctrl + O" .

Sebaik sahaja anda membuka kedua-dua buku kerja, anda boleh bertukar antara mereka dengan mengklik pada tab yang sesuai di bahagian bawah tetingkap Excel.

Langkah 2: Mula Memasuki Formula VLOOKUP

Berikut ialah sintaks asas fungsi VLOOKUP:

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

• lookup_value : Nilai yang anda ingin cari dalam buku kerja lain.

• table_array : Julat sel dalam buku kerja lain yang mengandungi data yang anda ingin dapatkan semula.

• col_index_num : Nombor lajur dalam julat yang mengandungi data yang anda ingin dapatkan semula.

• range_lookup : Parameter terakhir ini adalah pilihan. Jika anda menetapkannya kepada TRUE atau meninggalkannya, VLOOKUP akan mencari padanan anggaran. Jika anda menetapkannya kepada FALSE, VLOOKUP hanya akan mencari padanan tepat.

Katakan anda ingin mencari harga epal dalam buku kerja kedua data sampel kami. Ini adalah bagaimana formula akan terurai:

• Lookup_value berada dalam sel A2 buku kerja pertama.

• Tatasusunan jadual berada dalam julat sel A1:B5 buku kerja kedua.

• Col_index_num ialah lajur kedua yang memegang harga.

Bahagian paling rumit dalam formula VLOOKUP ialah merujuk buku kerja kedua dengan betul.

Langkah 3: Cara Rujuk Buku Kerja Kedua

Untuk melengkapkan formula VLOOKUP untuk dua buku kerja, anda perlu menggunakan rujukan berkelayakan sepenuhnya yang merangkumi butiran ini daripada buku kerja kedua:

• nama buku kerja

• nama helaian

• julat sel

Ini memastikan bahawa Excel mengetahui dengan tepat di mana untuk mencari data yang anda cari. Rujukan yang layak sepenuhnya digunakan untuk argumen table_array bagi fungsi VLOOKUP.

Ini ialah nilai untuk data sampel:

• nama buku kerja: Product.xlsx

• nama helaian: Harga

• julat sel: $A1:$B5

Untuk mencari harga untuk item pertama, masukkan formula berikut ke dalam sel C2 buku kerja pertama:

=VLOOKUP(A2, [Product.xlsx]Harga!$A$1:$B$5, 2, FALSE)

Gambar ini menunjukkan fungsi dalam tindakan dalam bar formula untuk sel pertama:

Ambil perhatian bahawa nama buku kerja disertakan dalam kurungan segi empat sama, dan nama helaian diikuti dengan tanda seru.

Jika nama fail Excel mengandungi ruang atau aksara khas, maka anda mesti melampirkan keseluruhan buku kerja dan helaian dalam petikan tunggal.

Formula menggunakan nilai mutlak untuk julat sel (awalan $). Ini membolehkan anda menyalin formula ke seluruh baris dengan mengklik pada pemegang isian.

Berikut ialah hasil VLOOKUP:

Dan itu sahaja! Anda kini tahu cara menggunakan VLOOKUP dengan berkesan. Untuk membantu anda sekiranya anda menghadapi sebarang ralat, kami telah menyusun tiga daripada ralat VLOOKUP yang paling biasa dalam bahagian seterusnya.

3 Ralat Biasa VLOOKUP dan Cara Membaikinya

Memandangkan anda telah mempelajari cara menggunakan VLOOKUP daripada buku kerja lain, anda mungkin menghadapi beberapa ralat biasa.

Berikut ialah tiga ralat yang paling kerap dan cara membetulkannya.

1. #N/A Ralat

Ralat #N/A berlaku apabila nilai carian tidak ditemui dalam tatasusunan jadual. Untuk membetulkan ralat ini, anda boleh mencuba perkara berikut:

• Semak ejaan nilai carian.

• Pastikan bahawa nilai carian berada dalam lajur pertama tatasusunan jadual.

• Semak bahawa nilai carian tidak diformatkan sebagai teks dalam dua helaian.

• Pastikan hujah range_lookup ditetapkan kepada FALSE untuk mencari padanan tepat.

Contoh di atas telah gagal mencari nilai yang sepadan untuk Ceri dalam buku kerja kedua.

2. #Ruj! Ralat Nombor Lajur

# Ruj! Ralat berlaku apabila argumen col_index_num lebih besar daripada bilangan lajur dalam tatasusunan jadual.

Untuk membetulkan ralat ini, semak bahawa argumen col_index_num adalah kurang daripada atau sama dengan bilangan lajur dalam jadual sumber.

Dalam gambar di bawah, col_index_num telah ditetapkan kepada 3 tetapi terdapat hanya dua lajur dalam julat rujukan.

3. Petikan dan Ralat Kurungan Segi Empat

Ralat Petikan dan Kurungan Square berlaku apabila anda melakukan VLOOKUP dengan nilai yang mengandungi petikan atau kurungan segi empat sama.

Untuk membetulkan ralat ini, anda boleh mencuba perkara berikut:

• Gunakan petikan berganda di sekitar nilai carian.

• Gunakan fungsi CHAR(34) untuk memasukkan petikan berganda.

• Gunakan fungsi IFERROR untuk mengembalikan sel kosong jika nilai carian mengandungi petikan atau kurungan segi empat sama.

Sebaik sahaja anda sudah biasa dengan ralat ini, membetulkannya adalah mudah! Untuk membantu anda menjadi profesional VLOOKUP, kami telah menyediakan empat petua untuk mengurus buku kerja yang berbeza dalam bahagian seterusnya.

4 Petua untuk Mengurus Buku Kerja Berbeza

Apabila bekerja dengan berbilang buku kerja, adalah penting untuk menjejaki buku kerja yang mengandungi data.

Berikut ialah empat petua terbaik kami untuk mengekalkan kewarasan anda:

1. Gunakan konvensyen penamaan untuk membantu anda mengingati hamparan Excel yang mengandungi data mana.

2. Gunakan rujukan mutlak dalam formula anda untuk memastikan bahawa julat carian merujuk kepada sel yang betul walaupun anda mengalihkan atau menyalin formula ke lokasi lain atau helaian Excel.

3. Gunakan fungsi IFERROR untuk mengendalikan sebarang ralat #N/A yang mungkin berlaku jika nilai carian tidak ditemui dalam data sumber.

4. Pertimbangkan untuk mengunci sel dalam buku kerja rujukan supaya ia tidak boleh ditimpa.

Cara Mengemas kini Data Secara Automatik

Apabila bekerja dengan berbilang fail Excel, ia boleh memakan masa untuk mengemas kini data secara manual .

Jika anda mahu formula VLOOKUP anda dikemas kini secara automatik apabila anda menambah atau mengalih keluar data daripada buku kerja lain, anda boleh menggunakan fungsi INDIRECT.

Ini caranya:

• Dalam formula VLOOKUP, gantikan argumen tatasusunan jadual dengan fungsi INDIRECT.

• Dalam fungsi INDIRECT, gunakan rujukan kepada sel yang mengandungi tatasusunan jadual dalam buku kerja lain.

Untuk mencapai ini dengan contoh buku kerja kami, ikuti langkah ini.

1. Gunakan satu sel dalam lajur D untuk memasukkan rujukan kepada helaian "Harga" buku kerja "Produk" dan julat sel A1:B5 seperti ini: [Product.xlsx]Harga!A:B

2. Dalam sel C2 buku kerja "Jualan", gunakan formula ini: =VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. Salin formula ke bawah (rujukan mutlak memastikan bahawa sel dengan hamparan dipelihara).

Tindakan ini memastikan bahawa perubahan dalam helaian asal dikemas kini secara automatik dalam sel yang disambungkan.

Gambar ini menunjukkan fungsi INDIRECT yang digabungkan dengan VLOOKUP:

Ambil perhatian bahawa contoh ini menggunakan julat "A:B" untuk merujuk dua lajur. Ini bermakna apabila anda menambah lebih banyak produk dan harga, formula akan terus berfungsi dalam buku kerja semasa.

Sekarang setelah anda merasai kuasa VLOOKUP, mari kita lihat beberapa kegunaan lanjutan fungsi dalam bahagian itu!

Penggunaan Lanjutan Fungsi VLOOKUP

Contoh dalam artikel ini mengembalikan padanan tunggal untuk setiap nilai carian. Jika anda ingin bekerja dengan berbilang padanan daripada buku kerja yang berbeza, anda boleh menggunakan formula tatasusunan sebaliknya.

Anda juga boleh menggabungkan VLOOKUP dengan fungsi lain. Sebagai contoh, anda boleh membenamkannya dalam fungsi IF untuk analisis kompleks tambahan.

Analisis kompleks termasuk teknik seperti ujian khi kuasa dua dan ujian T sampel berpasangan. Berikut ialah contoh menggunakan ujian khi kuasa dua dalam Excel:

Pengguna lanjutan memanfaatkan VLOOKUP untuk membandingkan set data dan mengenal pasti ketidakpadanan. Anda akan menemui cara lain dalam artikel ini tentang mencari percanggahan dalam Excel .

Tetapi bagaimana jika anda tidak mahu menggunakan VLOOKUP atas sebab apa pun? Anda bertuah! Terdapat beberapa alternatif, yang akan kami bincangkan dalam bahagian seterusnya.

2 Alternatif Utama kepada VLOOKUP

Apabila ia datang untuk mencari nilai dalam helaian yang berbeza, VLOOKUP selalunya menjadi fungsi utama. Walau bagaimanapun, terdapat beberapa alternatif yang boleh berguna dalam situasi tertentu.

Dua yang paling biasa ialah:

• PADANAN INDEKS

• XLOOKUP

1. PADANAN INDEKS

INDEX dan MATCH ialah gabungan berkuasa dua fungsi yang boleh digunakan untuk mencari nilai dalam jadual. Tidak seperti VLOOKUP, yang memerlukan nilai carian berada di lajur paling kiri jadual, INDEX MATCH boleh mencari nilai dalam mana-mana lajur.

Begini cara ia berfungsi:

1. Fungsi INDEX mengembalikan nilai daripada julat yang ditentukan berdasarkan nombor baris dan lajur.

2. Fungsi MATCH mengembalikan kedudukan nilai carian dalam julat yang ditentukan.

Untuk menggunakan INDEX MATCH, anda perlu menentukan julat yang ingin anda cari, baris tempat anda ingin mencari nilai carian dan lajur tempat anda ingin mengembalikan hasil.

Berikut ialah contoh formula:

=INDEX(Julat Jadual, MATCH(Nilai Carian,Julat Carian,0),Index Lajur)

Untuk menggunakan ini dengan hamparan sampel, ini ialah formulanya:

=INDEX([Product.xlsx]Harga!$B$1:$B$5, MATCH(A2, [Product.xlsx]Harga!$A$1:$A$5, 0))

Ini terpecah sebagai:

• Fungsi MATCH mencari nilai dalam sel A2 (nama produk) dalam julat A1:A5 dalam helaian "Harga" buku kerja "Produk".

• Fungsi INDEX mengembalikan nilai daripada julat B1:B5 dalam helaian "Harga" buku kerja "Produk", berdasarkan nombor baris yang disediakan oleh fungsi MATCH.

Gambar ini menunjukkan formula dalam tindakan:

2. XLOOKUP

XLOOKUP ialah fungsi yang lebih baharu yang diperkenalkan dalam Excel 365 . Ia serupa dengan VLOOKUP, tetapi dengan beberapa ciri tambahan.

Salah satu kelebihan terbesar XLOOKUP ialah ia boleh mencari nilai di sebelah kiri lajur carian.

Begini cara ia berfungsi:

1. Fungsi XLOOKUP mengembalikan nilai daripada julat yang ditentukan berdasarkan nilai carian.

2. Anda boleh menentukan sama ada anda mahu padanan tepat atau padanan anggaran.

3. Anda juga boleh menentukan perkara yang perlu dikembalikan jika tiada padanan ditemui.

Untuk menggunakan XLOOKUP, anda perlu menentukan nilai carian, julat yang anda ingin cari dan lajur yang anda mahu kembalikan hasilnya.

Berikut ialah contoh formula:

=XLOOKUP(LookupValue,LookupRange,ReturnRange,"Tidak Ditemui",0)

Untuk menggunakan ini dengan hamparan sampel, ini ialah formulanya:

=XLOOKUP(A2, [Produk.xlsx]Harga!$A$1:$A$5, [Produk.xlsx]Harga!$B$1:$B$5)

Ini terpecah sebagai:

• A2: Nilai yang anda ingin cari (nama produk).

• [Product.xlsx]Harga!$A$1:$A$5: Julat yang anda mahu cari nilai carian (nama produk dalam helaian "Harga" buku kerja "Produk").

• [Product.xlsx]Harga!$B$1:$B$5: Julat yang mengandungi nilai pulangan (harga dalam helaian "Harga" buku kerja "Produk").

Ini adalah formula dalam tindakan:

Cara Menggunakan VLOOKUP Merentas Berbilang Helaian

Jika anda perlu melakukan VLOOKUP antara dua buku kerja, anda juga mungkin perlu menggunakan VLOOKUP merentas berbilang helaian.

Nasib baik, proses untuk berbuat demikian sangat serupa dengan melaksanakan VLOOKUP antara dua buku kerja.

Terdapat empat langkah.

Langkah 1: Kenalpasti Komponen

Daripada memasukkan tatasusunan jadual seperti yang anda lakukan untuk satu helaian, anda perlu memasukkan nama helaian juga.

Contohnya, jika anda ingin mencari nilai dalam julat A2:B6 pada helaian Jan dalam buku kerja Sales_reports.xlsx, formula anda akan kelihatan seperti ini:

=VLOOKUP(A2,[Sales_reports.xlsx]Jan!$A$2:$B$6,2,FALSE)

Langkah 2: Sediakan Data Anda

Pastikan data anda disusun dengan cara yang memudahkan rujukan merentas berbilang helaian.

Anda mungkin ingin mempertimbangkan untuk membuat tab yang berbeza untuk data rujukan tertentu.

Langkah 3: Gunakan Fungsi VLOOKUP

Setelah data anda disediakan, anda boleh menggunakan fungsi VLOOKUP untuk mencari dan mendapatkan semula data daripada helaian lain.

Cuma pastikan anda memasukkan nama helaian dalam formula anda, seperti yang ditunjukkan di atas.

(Pilihan) Langkah 4: Selesaikan Masalah #N/A Ralat

Jika anda menghadapi ralat #N/A semasa menggunakan VLOOKUP merentas berbilang helaian, semak semula formula anda untuk memastikan anda telah merujuk semua helaian dan julat yang diperlukan dengan betul.

Anda juga mungkin ingin menyemak sama ada anda melakukan padanan yang tepat, bukannya carian julat.

Fikiran Akhir

Anda telah mempelajari cara menggunakan VLOOKUP untuk mencari dan mendapatkan semula data merentas dua hamparan. Fungsi serba boleh ini memudahkan untuk mencari nilai dalam satu buku kerja berdasarkan nilai yang sepadan dalam buku kerja lain.

Kuasa fungsi VLOOKUP melangkaui lembaran kerja tunggal, menjangkau buku kerja yang berbeza untuk memaut dan menganalisis data.

Menguasai fungsi ini merupakan langkah penting ke arah menjadi mahir dalam Excel, kerana ia membuka kemungkinan baharu untuk mengurus dan mentafsir data anda.

Dengan menggabungkan fungsi seperti INDIRECT, INDEX-MATCH, atau XLOOKUP, anda boleh meningkatkan lagi fleksibiliti dan keberkesanan carian data anda.

Teruskan berlatih dan menerokai keupayaan luas Excel; ini hanyalah puncak gunung ais. Selamat memburu data!