01 dari 03
Cari Perlawanan Anggaran ke Data dengan VLOOKUP Excel
Bagaimana Fungsi VLOOKUP berfungsi
Fungsi VLOOKUP Excel, yang bermaksud pencarian menegak , boleh digunakan untuk mencari maklumat khusus yang terletak dalam jadual data atau pangkalan data.
VLOOKUP biasanya mengembalikan satu bidang data sebagai outputnya. Bagaimana ia berlaku:
- Anda memberikan nama atau lookup_value yang memberitahu VLOOKUP di mana baris atau rekod jadual data untuk mencari data yang dikehendaki
- Anda membekalkan nombor lajur - yang dikenali sebagai col_index_num - dari data yang anda cari
- Fungsi ini mencari lookup_value dalam lajur pertama jadual data
- VLOOKUP kemudian menempatkan dan mengembalikan maklumat yang anda cari dari medan lain yang sama dengan menggunakan nombor lajur yang dibekalkan
Menyusun Data Pertama
Walaupun tidak selalu diperlukan, biasanya adalah yang terbaik untuk menyusun rangkaian data yang pertama yang dicari oleh VLOOKUP dalam urutan menaik menggunakan lajur pertama julat untuk kunci jenis.
Jika data tidak disusun, VLOOKUP mungkin mengembalikan hasil yang salah.
Sintaks dan Argumen Fungsi VLOOKUP
Sintaks fungsi merujuk kepada susun atur fungsi dan termasuk nama, kurungan, dan argumen fungsi itu.
Sintaks untuk fungsi VLOOKUP ialah:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (diperlukan) nilai untuk dicari - seperti kuantiti yang dijual dalam imej di atas
table_array - (diperlukan) ini adalah jadual data yang dicari VLOOKUP untuk mencari maklumat yang anda cari.
- Table_array mesti mengandungi sekurang-kurangnya dua lajur data
- Lajur pertama biasanya mengandungi lookup_value
col_index_num - (diperlukan) nombor lajur nilai yang anda mahu dijumpai.
- Penomboran bermula dengan lajur carian_key sebagai lajur 1
- Jika col_index_num ditetapkan ke nombor yang lebih besar daripada bilangan lajur yang dipilih dalam argumen table_array sebuah #REF! ralat dikembalikan oleh fungsi
range_lookup - (pilihan) menunjukkan sama ada atau tidak julat disusun dalam urutan menaik.
- Data dalam lajur pertama digunakan sebagai kunci jenis
- Nilai Boolean - TRUE atau FALSE adalah satu-satunya nilai yang boleh diterima
- Jika ditinggalkan, nilai ditetapkan kepada TRUE secara lalai
- Jika ditetapkan kepada TRUE atau diabaikan dan lajur pertama julat tidak disusun dalam urutan menaik, hasil yang salah mungkin berlaku
- Sekiranya ditetapkan kepada TRUE atau diabaikan dan padanan tepat untuk _value carian tidak dijumpai, padanan terdekat yang lebih kecil dalam ukuran atau nilai digunakan sebagai search_key
- Sekiranya ditetapkan kepada FALSE, VLOOKUP hanya menerima padanan tepat untuk paparan _value . Jika terdapat banyak nilai padanan, nilai padanan pertama dikembalikan
- Sekiranya ditetapkan kepada FALSE dan tiada nilai padanan untuk cari_key yang ditemui, ralat # N / A dikembalikan oleh fungsi
Contoh: Cari Kadar Diskaun untuk Kuantiti yang Dibeli
Contoh dalam imej di atas menggunakan fungsi VLOOKUP untuk mencari kadar diskaun yang berbeza bergantung kepada kuantiti item yang dibeli.
Contohnya menunjukkan bahawa diskaun untuk pembelian 19 item ialah 2%. Ini kerana lajur Kuantiti mengandungi julat nilai. Akibatnya, VLOOKUP tidak dapat mencari padanan tepat. Sebaliknya, perlawanan anggaran mesti dijumpai untuk memulangkan kadar diskaun yang betul.
Untuk mencari padanan anggaran:
- menyusun data dalam jadual_array dalam urutan menaik;
- tetapkan argumen range_lookup kepada TRUE
Contohnya, formula berikut yang mengandungi fungsi VLOOKUP digunakan untuk mencari diskaun untuk kuantiti barangan yang dibeli.
= VLOOKUP (C2, $ C $ 5: $ D $ 8.2, TRUE)
Walaupun rumus ini hanya boleh ditaip ke sel lembaran kerja, satu lagi pilihan, seperti yang digunakan dengan langkah-langkah yang disenaraikan di bawah, adalah menggunakan kotak dialog fungsi untuk memasukkan hujah-hujahnya.
- Menggunakan kotak dialog sering menjadikannya lebih mudah untuk memasukkan hujah fungsi dengan betul.
Membuka Peti Dialog VLOOKUP
Langkah-langkah yang digunakan untuk memasuki fungsi VLOOKUP yang ditunjukkan dalam imej di atas ke dalam sel B2 adalah:
- Klik pada sel B2 untuk menjadikannya sel aktif - lokasi di mana hasil fungsi VLOOKUP dipaparkan
- Klik pada tab Formula .
- Pilih Lookup & Rujukan dari reben untuk membuka senarai drop-down fungsi
- Klik pada VLOOKUP dalam senarai untuk memaparkan kotak dialog fungsi
02 dari 03
Memasuki Argumen Fungsi VLOOKUP Excel
Menunjuk kepada Rujukan Sel
Argumen untuk fungsi VLOOKUP dimasukkan ke dalam barisan berasingan kotak dialog seperti yang ditunjukkan dalam imej di atas.
Rujukan sel yang akan digunakan sebagai argumen boleh diketik ke dalam baris yang betul, atau, seperti yang dilakukan dalam langkah-langkah di bawah, menunjuk, yang melibatkan menyerlahkan rangkaian sel yang dikehendaki dengan penunjuk tetikus, boleh digunakan untuk memasukkannya ke dalam kotak dialog .
Kelebihan menggunakan penunjuk ialah:
- Ia lebih cepat daripada menaip;
- Lebih kurang kesilapan dibuat memasuki rujukan sel yang betul.
Menggunakan Rujukan Sel Relatif dan Mutlak dengan Argumen
Ia tidak biasa menggunakan beberapa salinan VLOOKUP untuk mengembalikan maklumat yang berbeza dari jadual data yang sama. Untuk menjadikannya lebih mudah untuk melakukan ini, seringkali VLOOKUP boleh disalin dari satu sel ke yang lain. Apabila fungsi disalin ke sel lain, penjagaan mesti diambil untuk memastikan rujukan sel yang dihasilkan adalah betul dengan lokasi baru fungsi tersebut.
Dalam imej di atas, tanda dolar ( $ ) mengelilingi rujukan sel untuk argumen table_array yang menunjukkan bahawa ia adalah rujukan sel mutlak , yang bermaksud ia tidak akan berubah jika fungsi itu disalin ke sel lain. Ini adalah wajar kerana banyak salinan VLOOKUP akan merujuk jadual data yang sama sebagai sumber maklumat.
Rujukan sel yang digunakan untuk lookup_value, sebaliknya , tidak dikelilingi oleh tanda dolar, yang menjadikannya rujukan sel relatif. Rujukan sel relatif berubah apabila ia disalin untuk mencerminkan lokasi baru mereka berbanding kedudukan data yang mereka rujuk.
Memasuki Argumen Fungsi
- Klik pada garis Cari _value dalam kotak dialog VLOOKUP
- Klik pada sel C2 dalam lembaran kerja untuk memasukkan rujukan sel ini sebagai hujah carian_key
- Klik pada baris Table_array kotak dialog
- Sorot sel C5 hingga D8 dalam lembaran kerja untuk memasukkan julat ini sebagai argumen Table_array - tajuk jadual tidak dimasukkan
- Tekan kekunci F4 pada papan kekunci untuk menukar julat ke rujukan sel mutlak
- Klik pada baris Col_index_num kotak dialog
- Taipkan 2 pada baris ini sebagai hujah Col_index_num , kerana kadar diskaun terletak pada lajur 2 argumen Table_array
- Klik pada baris Range_lookup kotak dialog
- Taip perkataan Benar sebagai hujah Range_lookup
- Tekan kekunci Enter pada papan kekunci untuk menutup kotak dialog dan kembali ke lembaran kerja
- Jawapan 2% (kadar diskaun untuk kuantiti yang dibeli) sepatutnya muncul di sel D2 lembaran kerja
- Apabila anda mengklik pada sel D2, fungsi lengkap = VLOOKUP (C2, $ C $ 5: $ D $ 8.2, TRUE) muncul di bar formula di atas lembaran kerja
Kenapa VLOOKUP Kembali 2% sebagai Keputusan
- Dalam contoh, lajur Kuantiti tidak mengandungi padanan tepat untuk nilai carian_key sebanyak 19.
- Oleh kerana argumen is_sorted ditetapkan kepada TRUE, VLOOKUP akan mencari padanan anggaran untuk nilai carian_key .
- Nilai bersaiz terdekat yang masih kecil daripada nilai carian_key 19 ialah 11.
- Oleh itu, VLOOKUP mencari peratus diskaun dalam barisan yang mengandungi 11, dan, akibatnya, mengembalikan kadar diskaun sebanyak 2%.
03 dari 03
Excel VLOOKUP Tidak Bekerja: # N / A dan #REF Kesalahan
Mesej Gangguan VLOOKUP
Mesej ralat berikut dikaitkan dengan VLOOKUP.
A # N / A ("nilai tidak tersedia") Ralat Dipaparkan Jika:
- Penyaringan _value tidak terdapat dalam lajur pertama argumen julat
- Argumen Table_array tidak tepat. Sebagai contoh, hujah mungkin termasuk lajur kosong di sebelah kiri julat
- Argumen Range_lookup ditetapkan kepada FALSE dan padanan tepat untuk hujah carian_key tidak dapat dijumpai di lajur pertama julat
- Argumen Range_lookup ditetapkan kepada TRUE dan semua nilai dalam lajur pertama julat adalah lebih besar daripada search_key
A #REF! ("rujukan daripada rentang") Ralat Dipaparkan Jika:
- Argumen Col_index_num lebih besar daripada bilangan lajur dalam Table_array