Fungsi VLOOKUP dalam Excel mencari nilai dalam julat sel, kemudian ia mengembalikan nilai yang berada dalam baris yang sama dengan nilai yang anda cari.
VLOOKUP yang bermaksud 'Vertical Lookup', ialah fungsi carian yang mencari nilai dalam lajur paling kiri (lajur pertama) julat dan mengembalikan nilai selari dari lajur ke kanannya. Fungsi VLOOKUP hanya melihat nilai atas (atas ke bawah) dalam jadual yang disusun secara menegak.
Sebagai contoh, katakan, kami mempunyai senarai inventori dalam lembaran kerja dengan jadual yang menunjukkan nama item, tarikh pembelian, kuantiti dan harga. Kemudian, kita boleh menggunakan VLOOKUP dalam lembaran kerja lain untuk mengekstrak kuantiti dan harga untuk nama item tertentu daripada lembaran kerja inventori.
Fungsi VLOOKUP mungkin kelihatan menakutkan pada mulanya, tetapi ia sebenarnya agak mudah digunakan sebaik sahaja anda memahami cara ia berfungsi. Di sini, dalam tutorial ini, kami akan menunjukkan kepada anda cara menggunakan fungsi VLOOKUP dalam Excel.
Sintaks dan Argumen VLOOKUP
Jika anda akan menggunakan fungsi VLOOKUP, anda perlu mengetahui sintaks dan hujahnya.
Sintaks fungsi VLOOKUP:
=VLOOKUP(nilai_carian, tatasusunan_jadual, bilangan_indeks_col, [julat_carian])Fungsi ini terdiri daripada 4 parameter atau argumen:
- nilai_cari: Ini menentukan nilai yang anda cari dalam lajur pertama tatasusunan jadual yang diberikan. Nilai Carian mestilah sentiasa berada di bahagian paling kiri (lajur jadual carian.
- table_array: Ini ialah jadual (julat sel) di mana anda ingin mencari nilai. Jadual ini (jadual carian) boleh berada dalam lembaran kerja yang sama atau lembaran kerja yang berbeza, atau pun buku kerja yang berbeza.
- kol_index_num: Ini menentukan nombor lajur tatasusunan jadual yang mempunyai nilai yang anda ingin ekstrak.
- [range_lookup]: Parameter ini menentukan sama ada anda ingin mengekstrak padanan tepat atau padanan anggaran. Ia sama ada BENAR atau SALAH, masukkan 'FALSE' jika anda mahukan nilai yang tepat atau masukkan 'TRUE' jika anda OK dengan nilai anggaran.
Menggunakan fungsi VLOOKUP dalam Excel
Mari terokai cara menggunakan VLOOKUP dalam Microsoft Excel.
Contoh Asas
Untuk menggunakan VLOOKUP, pertama, anda perlu mencipta pangkalan data atau jadual anda (lihat di bawah).
Kemudian buat jadual atau julat dari tempat anda ingin mencari dan mengekstrak nilai daripada jadual carian.
Seterusnya, pilih sel di mana anda mahu nilai yang diekstrak dan masukkan formula VLOOKUP berikut. Sebagai contoh, kita ingin mencari nombor telefon 'Ena', kemudian kita perlu memasukkan nilai carian sebagai B13, A2:E10 sebagai tatasusunan jadual, 5 untuk nombor lajur nombor telefon dan FALSE untuk mengembalikan yang tepat. nilai. Kemudian, tekan 'Enter' untuk menyelesaikan formula.
=VLOOKUP(B13,A2:E10,5,PALSU)Anda tidak perlu menaip julat jadual secara manual, anda hanya boleh memilih julat atau jadual menggunakan tetikus untuk argumen table_array. Dan ia akan ditambahkan pada hujah secara automatik.
Ingat, untuk ini berfungsi, nilai Carian mestilah berada di bahagian paling kiri jadual carian kami (A2:E10). Selain itu, Lookup_value tidak semestinya perlu berada dalam lajur A lembaran kerja, ia hanya perlu menjadi lajur paling kiri julat yang anda ingin cari.
Vlookup Nampak Betul
Fungsi VLOOKUP hanya boleh melihat ke sebelah kanan jadual. Ia mencari nilai dalam lajur pertama jadual atau julat dan mengekstrak nilai yang sepadan daripada lajur di sebelah kanan.
Padanan Tepat
Fungsi Excel VLOOKUP mempunyai dua kaedah pemadanan, iaitu: tepat dan anggaran. Parameter 'range_lookup' dalam fungsi VLOOKUP menentukan jenis yang anda cari, tepat atau anggaran.
Jika anda memasukkan range_lookup sebagai 'FALSE' atau '0', formula mencari nilai yang betul-betul sama dengan lookup_value (ia boleh menjadi nombor, teks atau tarikh).
=VLOOKUP(A9,A2:D5,3,PALSU)
Jika padanan tepat tidak ditemui dalam jadual, ia akan mengembalikan ralat #N/A. Apabila kami cuba mencari 'Jepun' dan mengembalikan nilai yang sepadan dalam lajur 4, ralat #N/A berlaku kerana tiada 'Jepun' dalam lajur pertama jadual.
Anda boleh memasukkan sama ada nombor '0' atau 'PALSU' dalam hujah akhir. Kedua-duanya bermaksud perkara yang sama dalam Excel.
Anggaran Padanan
Kadangkala anda tidak semestinya memerlukan padanan yang tepat, padanan terbaik sudah memadai. Dalam kes sedemikian, anda boleh menggunakan mod padanan anggaran. Tetapkan hujah akhir fungsi kepada 'TRUE' untuk mencari padanan anggaran. Nilai lalai ialah TRUE, yang bermaksud jika anda tidak menambah hujah terakhir, fungsi akan menggunakan padanan anggaran secara lalai.
=VLOOKUP(B10,A2:B7,2,TRUE)Dalam contoh ini, kami tidak memerlukan skor tepat untuk mencari gred yang sesuai. Apa yang kita perlukan ialah markah untuk berada dalam julat skor tersebut.
Jika VLOOKUP menemui padanan yang tepat, maka ia akan mengembalikan nilai tersebut. Dalam contoh di atas, jika formula tidak dapat mencari nilai carian 89 dalam lajur pertama, maka ia akan mengembalikan nilai terbesar seterusnya (80).
Perlawanan Pertama
Jika lajur paling kiri jadual mengandungi pendua, VLOOKUP akan mencari dan mengembalikan padanan pertama.
Sebagai contoh, VLOOKUP dikonfigurasikan untuk mencari nama akhir untuk nama pertama 'Mia'. Oleh kerana terdapat 2 entri dengan nama pertama 'Mia', jadi fungsi tersebut mengembalikan nama akhir untuk entri pertama, 'Bena'.
Perlawanan Wildcard
Fungsi VLOOKUP membolehkan anda mencari padanan separa pada nilai yang ditentukan menggunakan aksara kad bebas. Jika anda ingin mencari nilai yang mengandungi nilai carian dalam sebarang kedudukan, tambahkan tanda ampersand (&) untuk menyertai nilai carian kami dengan aksara kad bebas (*). Gunakan tanda '$' untuk membuat rujukan sel mutlak dan tambah tanda '*' kad bebas sebelum atau selepas nilai carian.
Dalam contoh, kita hanya mempunyai sebahagian daripada nilai carian (Vin) dalam sel B13. Jadi, untuk melakukan padanan separa pada aksara yang diberikan, gabungkan kad bebas '*' selepas rujukan sel.
=VLOOKUP($B$13&"*",$A$2:$E$10,3,PALSU)
Berbilang carian
Fungsi VLOOKUP membolehkan anda mencipta carian dua hala dinamik, padanan pada kedua-dua baris dan lajur. Dalam contoh berikut, VLOOKUP disediakan untuk melakukan carian berdasarkan Nama Pertama (Mayra) dan Bandar. Sintaks dalam B14 ialah:
=VLOOKUP(B13,A2:E10,MATCH(A14,A1:E1,0),0)
Bagaimana untuk VLOOKUP dari helaian lain dalam Excel
Biasanya, fungsi VLOOKUP digunakan untuk mengembalikan nilai yang sepadan daripada lembaran kerja yang berasingan dan ia jarang digunakan dengan data dalam lembaran kerja yang sama.
Untuk Vlookup dari helaian Excel lain tetapi dalam buku kerja yang sama, masukkan nama helaian sebelum table_array dengan tanda seru (!).
Contohnya, untuk mencari nilai sel A2 lembaran kerja 'Produk' dalam julat A2:B8 pada lembaran kerja 'ItemPrices' dan mengembalikan nilai yang sepadan daripada lajur B:
=VLOOKUP(A2,ItemPrices!$A$2:$C$8,2,FALSE)Gambar di bawah menunjukkan jadual dalam lembaran kerja 'ItemPrices'.
Apabila kita memasukkan formula VLOOKUP dalam lajur C lembaran kerja 'Produk', ia menarik data yang sepadan daripada lembaran kerja 'ItemPrices'.
Bagaimana untuk VLOOKUP daripada Buku Kerja Lain dalam Excel
Anda juga boleh mencari nilai pada buku kerja yang sama sekali berbeza. Jika anda ingin VLOOKUP daripada buku kerja lain, anda perlu melampirkan nama buku kerja dalam kurungan segi empat sama diikuti dengan nama helaian sebelum table_array dengan tanda seru (!) (seperti ditunjukkan di bawah).
Sebagai contoh, gunakan formula ini untuk mencari nilai sel A2 lembaran kerja yang berbeza daripada lembaran kerja bernama 'ItemPrices' dalam buku kerja 'Item.xlsx':
=VLOOKUP(A2,[Item.xls]ItemPrices!$A$2:$B$8,2,FALSE)Mula-mula, buka kedua-dua buku kerja, kemudian mula masukkan formula pada sel C2 lembaran kerja (Lembaran kerja produk), dan apabila anda sampai ke argumen table_array, pergi ke buku kerja data utama (Item.xlsx) dan pilih julat jadual. Dengan cara ini anda tidak perlu menaip buku kerja dan nama lembaran kerja secara manual. Taipkan argumen yang lain dan tekan kekunci 'Enter' untuk menyelesaikan fungsi.
Walaupun anda menutup buku kerja yang mengandungi jadual carian, formula VLOOKUP akan terus berfungsi, tetapi anda kini boleh melihat laluan penuh buku kerja tertutup seperti yang ditunjukkan dalam tangkapan skrin berikut.
Gunakan Fungsi VLOOKUP daripada Excel Ribbon
Jika anda tidak ingat formula, anda sentiasa boleh mengakses fungsi VLOOKUP daripada Excel Ribbon. Untuk mengakses VLOOKUP, pergi ke tab 'Formula' dalam Excel Ribbon dan klik ikon 'Lookup & Rujukan'. Kemudian, pilih pilihan 'VLOOKUP' di bahagian bawah menu lungsur.
Kemudian, masukkan argumen dalam kotak dialog 'Argumen Fungsi'. Kemudian, klik butang ‘OK’.
Dalam contoh, kami mencari nama pertama 'Sherill' dalam jadual untuk mengembalikan keadaan yang sepadan dalam lajur D.
Kami harap anda mempelajari cara menggunakan fungsi VLOOKUP dalam Excel daripada artikel ini. Jika anda ingin mengetahui lebih lanjut tentang cara menggunakan Excel, lihat artikel berkaitan Excel kami yang lain.