Fungsi VLOOKUP Excel digunakan untuk mencari dan mengembalikan maklumat dari jadual data berdasarkan nilai pencarian yang anda pilih.
Biasanya, VLOOKUP memerlukan nilai carian untuk berada di lajur paling kiri jadual data, dan fungsi mengembalikan bidang data lain yang terletak pada baris yang sama di sebelah kanan nilai ini.
Walau bagaimanapun, dengan menggabungkan VLOOKUP dengan fungsi CHOOSE, rumus pencarian kiri boleh dibuat bahawa:
- Mematuhi nilai carian dari mana-mana lajur dalam jadual data
- Mengembalikan maklumat yang terletak di mana-mana lajur ke kiri dari nilai carian
Catatan: Arahan ini digunakan untuk versi Excel 2019, 2016, 2013, 2010, dan Excel untuk Office 365.
01 dari 05Tutorial: Menggunakan VLOOKUP dan PILIHAN Fungsi dalam Formula Lookup Kiri
Untuk membuat formula carian kiri dilihat dalam imej contoh, gunakan formula:
= VLOOKUP ($ D $ 2, PILIH ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
Dalam contoh ini, formula menjadikannya mungkin untuk mencari bahagian yang dibekalkan oleh syarikat-syarikat yang berlainan yang disenaraikan dalam lajur 3 jadual data.
Tugas fungsi PILIH dalam formula adalah untuk menipu VLOOKUP untuk mempercayai bahawa lajur 3 adalah lajur 1. Sebagai hasilnya, nama syarikat boleh digunakan sebagai nilai pencarian untuk mencari nama bahagian yang dibekalkan oleh setiap syarikat.
Masukkan Data Tutorial
- Masukkan tajuk Pembekal ke dalam sel D1.
- Masukkan tajuk Bahagian dalam sel E1.
- Masukkan jadual data yang dilihat pada imej di atas ke dalam sel D4 hingga F9
- Baris 2 dan 3 dibiarkan kosong untuk menampung kriteria carian dan rumus pencarian kiri yang dibuat semasa tutorial ini
Buka kotak dialog VLOOKUP
Walaupun adalah mungkin untuk menaip formula secara langsung ke dalam sel F1 dalam lembaran kerja, ramai orang mengalami kesulitan dengan sintaks formula.
Dalam kes ini, lebih selamat menggunakan kotak dialog VLOOKUP. Hampir semua fungsi Excel mempunyai kotak dialog yang membolehkan anda memasukkan setiap hujah fungsi pada baris berasingan.
- Klik pada sel E2 lembaran kerja. E2 adalah lokasi di mana hasil carian formula kiri akan dipaparkan.
- Klik pada Formula tab reben.
- Klik pada Lookup & Rujukan pilihan dalam reben untuk membuka senarai drop-down fungsi.
- Klik pada VLOOKUP dalam senarai untuk memaparkan kotak dialog fungsi.
Memasuki Argumen Ke dalam Peti Dialog VLOOKUP
Hujah fungsi adalah nilai yang digunakan oleh fungsi untuk mengira hasilnya.
Dalam kotak dialog fungsi, nama setiap hujah adalah pada baris berasingan diikuti oleh medan untuk memasukkan nilai.
Masukkan nilai berikut untuk setiap hujah VLOOKUP pada baris dialog kotak yang betul seperti yang ditunjukkan dalam imej yang disertakan.
Nilai Lookup
Nilai carian ialah medan maklumat yang digunakan untuk mencari pelbagai jadual. VLOOKUP mengembalikan bidang data lain dari baris yang sama dengan nilai carian.
Contoh ini menggunakan rujukan sel untuk lokasi di mana nama syarikat akan dimasukkan ke dalam lembaran kerja. Kelebihan ini adalah menjadikannya mudah untuk menukar nama syarikat tanpa menyunting formula.
- Klik pada lookup_value baris dalam kotak dialog.
- Klik pada sel D2 untuk menambah rujukan sel ini kepada lookup_value talian.
- Tekan butang F4 kunci pada papan kekunci untuk membuat rujukan sel mutlak - $ D $ 2.
Rujukan Sel Absolute
Rujukan sel mutlak digunakan untuk nilai carian dan hujah array meja untuk mengelakkan kesalahan jika formula carian disalin ke sel lain dalam lembaran kerja.
Memasuki PILIHAN Fungsi
Hujah tatasusunan meja ialah blok data bersebelahan yang mana maklumat khusus diambil.
Biasanya, VLOOKUP hanya kelihatan di sebelah kanan hujah nilai pencarian untuk mencari data dalam pelbagai jadual. Untuk mendapatkannya untuk melihat ke kiri, VLOOKUP mesti ditipu dengan menyusun semula lajur dalam pelbagai jadual menggunakan fungsi PILIH.
Dalam formula ini, fungsi CHOOSE menyelesaikan dua tugas:
- Ia mewujudkan pelbagai jadual yang hanya terdapat dua lajur lebar (lajur D dan F).
- Ia mengubah urutan kanan-ke-kiri lajur dalam array jadual supaya lajur F menjadi lebih awal dan lajur D adalah kedua.
Memasuki Fungsi
Apabila memasukkan fungsi secara manual, setiap hujah fungsi mesti dipisahkan dengan koma.
- Dalam kotak dialog fungsi VLOOKUP, klik pada Table_array talian.
- Sila isikan PILIH fungsi:PILIH ({1,2}, $ F: $ F, $ D: $ D)
Nombor Indeks Lajur
Biasanya, nombor indeks lajur menunjukkan lajur dari jadual jadual mengandungi data yang anda ikuti. Walau bagaimanapun, dalam formula ini, ia merujuk kepada urutan lajur yang ditetapkan oleh fungsi PILIH.
Fungsi CHOOSE mewujudkan satu array jadual yang mempunyai dua lajur lebar dengan lajur F yang pertama diikuti oleh lajur D. Oleh kerana maklumat yang dicari-nama bahagian - berada di lajur D, nilai argumen indeks lajur harus ditetapkan ke 2.
- Klik pada Col_index_num baris dalam kotak dialog.
- Jenis A 2 dalam baris ini.
Penjejakan Pelbagai
Argumen Range_lookup VLOOKUP adalah nilai logik (TRUE atau FALSE sahaja) yang menunjukkan sama ada anda mahu VLOOKUP untuk mencari persamaan yang tepat atau anggaran untuk nilai carian.
- Jika TRUE atau jika hujah ini ditinggalkan, VLOOKUP mengembalikan sama ada padanan tepat pada Lookup_value atau, jika padanan tepat tidak dijumpai, VLOOKUP mengembalikan nilai seterusnya seterusnya. Untuk formula untuk melakukan ini, data dalam lajur pertama Table_array mesti disusun dalam urutan menaik.
- Sekiranya FALSE, VLOOKUP hanya menggunakan padanan tepat pada Lookup_value. Jika terdapat dua atau lebih nilai dalam lajur pertama Table_array yang sepadan dengan nilai carian, nilai pertama yang dijumpai digunakan. Jika padanan tepat tidak dijumpai, ralat # N / A dikembalikan.
Dalam tutorial ini, kerana kami mencari nama bahagian tertentu, Range_lookup ditetapkan kepada Salah supaya hanya padanan tepat dikembalikan oleh formula.
- Klik pada Range_lookup baris dalam kotak dialog.
- Taip perkataan Salah dalam baris ini untuk menunjukkan bahawa kami mahu VLOOKUP untuk mengembalikan padanan tepat untuk data yang kami cari.
- Klik okey untuk melengkapkan formula pencarian kiri dan kotak dialog yang rapat.
- Kerana kita belum memasukkan nama syarikat ke dalam sel D2, ralat # N / A muncul di sel E2.
Mengembalikan Data Dengan Formula Lookup Kiri
Untuk mencari syarikat yang membekalkan bahagian mana, taipkan nama syarikat ke dalam sel D2 dan tekan ENTER kekunci pada papan kekunci.
Nama bahagian dipaparkan dalam sel E2.
- Klik pada sel D2 dalam lembaran kerja anda.
- Taipkan Alat Plus ke dalam sel D2 dan tekan ENTER kekunci pada papan kekunci.
- Teks "Alat" - bahagian yang dibekalkan oleh syarikat Gadget Plus - sepatutnya dipaparkan dalam sel E2.
Uji formula pencarian lagi dengan menaip nama syarikat lain ke dalam sel D2, dan nama bahagian yang sepadan akan muncul di sel E2.
Jika mesej ralat seperti # N / A muncul di sel E2, periksa kesilapan ejaan dalam sel D2.
04 dari 05Mewujudkan Arahan Jadual Dua Lajur
Sintaks untuk fungsi PILIH adalah:
= PILIHAN (Index_number, Value1, Value2, … Value254)
Fungsi CHOOSE biasanya mengembalikan satu nilai dari senarai nilai (Value1 hingga Value254) berdasarkan nombor indeks yang dimasukkan.
Jika nombor indeks adalah 1, fungsi mengembalikan nilai1 dari senarai; jika nombor indeks adalah 2, fungsi itu mengembalikan Value2 dari senarai dan sebagainya.
Apabila nombor indeks berganda dimasukkan, fungsi mengembalikan beberapa nilai dalam sebarang urutan yang dikehendaki. Mendapatkan PILIH untuk mengembalikan beberapa nilai dilakukan dengan membuat array.
Untuk memasukkan array, mengelilingi nombor yang dimasukkan dengan pendakap atau kurungan kerinting. Dua nombor dimasukkan untuk nombor indeks:{ 1,2 }.
Perlu diingatkan bahawa CHOOSE tidak terhad untuk membuat jadual dua lajur. Dengan menyertakan nombor tambahan dalam array - seperti {1,2,3} - dan julat tambahan dalam hujah nilai, CHOOSE mencipta jadual tiga lajur.
Lajur tambahan membolehkan anda untuk mengembalikan maklumat yang berbeza dengan formula carian kiri dengan menukar hujah indeks lajur kolum VLOOKUP kepada bilangan lajur yang mengandungi maklumat yang dikehendaki.
05 dari 05Mengubah Perintah Lajur Dengan PILIHAN Fungsi
Dalam fungsi CHOOSE yang digunakan dalam formula ini:
PILIH ({1,2}, $ F: $ F, $ D: $ D)
julat bagi lajur F disenaraikan sebelum lajur D.
Oleh kerana fungsi CHOOSE menetapkan pelbagai jadual VLOOKUP (sumber data untuk fungsi itu) menukar urutan lajur dalam fungsi CHOOSE diluluskan bersama untuk VLOOKUP.
Setakat VLOOKUP, array meja hanya dua lajur lebar dengan lajur F di sebelah kiri dan lajur D di sebelah kanan. Oleh sebab lajur F mengandungi nama syarikat yang ingin dicari, dan sejak lajur D mengandungi nama-nama bahagian, VLOOKUP dapat melakukan tugas pencarian biasa dalam mencari data yang terletak di sebelah kiri nilai pencarian.
Akibatnya, VLOOKUP boleh menggunakan nama syarikat untuk mencari bahagian yang mereka sediakan.
