Fungsi VLOOKUP dalam Excel – Panduan Pemula: Sintaks dan Contoh

Hari ini kami memulakan satu siri artikel yang menerangkan salah satu ciri Excel yang paling berguna − VPR (VLOOKUP). Fungsi ini, pada masa yang sama, adalah salah satu yang paling kompleks dan kurang difahami.

Dalam tutorial ini mengenai VPR Saya akan cuba meletakkan asas-asas semudah mungkin untuk menjadikan proses pembelajaran sejelas mungkin untuk pengguna yang tidak berpengalaman. Di samping itu, kami akan mengkaji beberapa contoh dengan formula Excel yang akan menunjukkan kes penggunaan yang paling biasa untuk fungsi tersebut VPR.

  • Penerangan umum dan sintaks
  • Contoh VLOOKUP
    • Cara menggunakan VLOOKUP untuk mencari dalam helaian Excel yang lain
    • Cari dalam buku kerja lain dengan VLOOKUP
    • Cara menggunakan julat atau jadual bernama dalam formula dengan VLOOKUP
    • Menggunakan Kad Liar dalam Formula VLOOKUP
  • Padanan tepat atau anggaran dalam fungsi VLOOKUP
  • VLOOKUP dalam Excel – anda perlu ingat ini!

Fungsi VLOOKUP dalam Excel – penerangan umum dan sintaks

Jadi apa itu VPR? Pertama sekali, ia adalah fungsi Excel. Apa yang dia buat? Ia mencari nilai yang anda tentukan dan mengembalikan nilai yang sepadan daripada lajur yang lain. Dari segi teknikal, VPR mencari nilai dalam lajur pertama julat yang diberikan dan mengembalikan hasil daripada lajur lain dalam baris yang sama.

Dalam aplikasi yang paling biasa, fungsi VPR mencari pangkalan data untuk pengecam unik yang diberikan dan mengekstrak beberapa maklumat yang berkaitan dengannya daripada pangkalan data.

Huruf pertama dalam nama fungsi VPR (VLOOKUP) bermaksud Вmenegak (Vmenegak). Dengan itu anda boleh membezakan VPR dari GPR (HLOOKUP), yang mencari nilai dalam baris atas julat − Гmendatar (Hsecara mendatar).

fungsi VPR tersedia dalam Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP dan Excel 2000.

Sintaks fungsi VLOOKUP

fungsi VPR (VLOOKUP) mempunyai sintaks berikut:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Seperti yang anda lihat, fungsi VPR dalam Microsoft Excel mempunyai 4 pilihan (atau argumen). Tiga yang pertama adalah wajib, yang terakhir adalah pilihan.

  • lookup_value (lookup_value) – Nilai yang perlu dicari. Ini boleh menjadi nilai (nombor, tarikh, teks) atau rujukan sel (mengandungi nilai carian), atau nilai yang dikembalikan oleh beberapa fungsi Excel lain. Sebagai contoh, formula ini akan mencari nilai 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

Jika nilai carian kurang daripada nilai terkecil dalam lajur pertama julat yang dicari, fungsinya VPR akan melaporkan ralat #AT (#N/A).

  • table_array (jadual) – dua atau lebih lajur data. Ingat, fungsinya VPR sentiasa mencari nilai dalam lajur pertama julat yang diberikan dalam hujah table_array (meja). Julat boleh dilihat boleh mengandungi pelbagai data, seperti teks, tarikh, nombor, boolean. Fungsi ini tidak peka huruf besar-besaran, bermakna huruf besar dan huruf kecil dianggap sama. Jadi formula kami akan mencari nilainya 40 dalam sel daripada A2 kepada A15, kerana A ialah lajur pertama julat A2:B15 yang diberikan dalam hujah table_array (jadual):

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • col_index_num (column_number) ialah nombor lajur dalam julat yang diberikan dari mana nilai dalam baris yang ditemui akan dikembalikan. Lajur paling kiri dalam julat yang diberikan ialah 1, lajur kedua ialah 2, lajur ketiga ialah 3 dan sebagainya. Kini anda boleh membaca keseluruhan formula:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    Formula mencari nilai 40 dalam julat A2: A15 dan mengembalikan nilai yang sepadan daripada lajur B (kerana B ialah lajur kedua dalam julat A2:B15).

Jika nilai hujah col_index_num (nombor_lajur) kurang daripada 1kemudian VPR akan melaporkan ralat #NILAI! (#VALUE!). Dan jika ia lebih daripada bilangan lajur dalam julat table_array (jadual), fungsi akan mengembalikan ralat #REF! (#LINK!).

  • range_lookup (range_lookup) – menentukan perkara yang perlu dicari:
    • padanan tepat, hujah mestilah sama SALAH (SALAH);
    • padanan anggaran, hujah sama KOD BENAR (BENAR) atau tidak dinyatakan sama sekali.

    Parameter ini adalah pilihan, tetapi sangat penting. Kemudian dalam tutorial ini pada VPR Saya akan menunjukkan kepada anda beberapa contoh yang menerangkan cara menulis formula untuk mencari padanan tepat dan anggaran.

Contoh VLOOKUP

Saya berharap fungsi VPR menjadi lebih jelas sedikit kepada anda. Sekarang mari kita lihat beberapa kes penggunaan VPR dalam formula dengan data sebenar.

Cara menggunakan VLOOKUP untuk mencari dalam helaian Excel yang lain

Dalam amalan, formula dengan fungsi VPR jarang digunakan untuk mencari data pada lembaran kerja yang sama. Lebih kerap daripada tidak, anda akan mencari dan mendapatkan nilai yang sepadan dari helaian lain.

Untuk menggunakan VPR, cari dalam helaian Microsoft Excel yang lain, Anda mesti dalam hujah table_array (jadual) nyatakan nama helaian dengan tanda seru diikuti dengan julat sel. Sebagai contoh, formula berikut menunjukkan bahawa julat A2: B15 terdapat pada helaian bernama Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Sudah tentu, nama helaian tidak perlu dimasukkan secara manual. Hanya mula menaip formula, dan apabila ia datang kepada hujah table_array (jadual), tukar ke helaian yang dikehendaki dan pilih julat sel yang dikehendaki dengan tetikus.

Formula yang ditunjukkan dalam tangkapan skrin di bawah mencari teks "Produk 1" dalam lajur A (ia lajur pertama julat A1:B2) pada lembaran kerja Harga.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Sila ingat bahawa apabila mencari nilai teks, anda mesti melampirkannya dalam tanda petikan (“”), seperti yang biasa dilakukan dalam formula Excel.

Untuk hujah table_array (jadual) adalah wajar untuk sentiasa menggunakan rujukan mutlak (dengan tanda $). Dalam kes ini, julat carian akan kekal tidak berubah apabila menyalin formula ke sel lain.

Cari dalam buku kerja lain dengan VLOOKUP

Untuk berfungsi VPR bekerja di antara dua buku kerja Excel, anda perlu menentukan nama buku kerja dalam kurungan persegi sebelum nama helaian.

Sebagai contoh, di bawah ialah formula yang mencari nilai 40 pada helaian Sheet2 dalam buku ini Nombor.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Berikut ialah cara termudah untuk mencipta formula dalam Excel dengan VPRyang memaut ke buku kerja lain:

  1. Buka kedua-dua buku. Ini tidak diperlukan, tetapi lebih mudah untuk mencipta formula dengan cara ini. Anda tidak mahu memasukkan nama buku kerja secara manual, bukan? Di samping itu, ia akan melindungi anda daripada kesilapan menaip secara tidak sengaja.
  2. Mula menaip fungsi VPRdan apabila ia datang kepada hujah table_array (jadual), tukar ke buku kerja lain dan pilih julat carian yang diperlukan di dalamnya.

Tangkapan skrin di bawah menunjukkan formula dengan carian ditetapkan kepada julat dalam buku kerja Senarai Harga.xlsx pada helaian Harga.

fungsi VPR akan berfungsi walaupun apabila anda menutup buku kerja yang dicari dan laluan penuh ke fail buku kerja muncul dalam bar formula, seperti ditunjukkan di bawah:

Jika nama buku kerja atau helaian mengandungi ruang, maka ia mesti disertakan dalam apostrof:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Cara menggunakan julat atau jadual bernama dalam formula dengan VLOOKUP

Jika anda bercadang untuk menggunakan julat carian yang sama dalam berbilang fungsi VPR, anda boleh mencipta julat bernama dan masukkan namanya ke dalam formula sebagai hujah table_array (meja).

Untuk mencipta julat bernama, hanya pilih sel dan masukkan nama yang sesuai dalam medan Nama Pertama, di sebelah kiri bar formula.

Kini anda boleh menulis formula berikut untuk mencari harga produk Produk 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Kebanyakan nama julat berfungsi untuk keseluruhan buku kerja Excel, jadi tidak perlu menentukan nama helaian untuk hujah table_array (jadual), walaupun formula dan julat carian berada pada lembaran kerja yang berbeza. Jika mereka berada dalam buku kerja yang berbeza, maka sebelum nama julat anda perlu menentukan nama buku kerja, contohnya, seperti ini:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Jadi formula kelihatan lebih jelas, setuju? Selain itu, menggunakan julat bernama ialah alternatif yang baik kepada rujukan mutlak kerana julat bernama tidak berubah apabila anda menyalin formula ke sel lain. Ini bermakna anda boleh yakin bahawa julat carian dalam formula akan sentiasa betul.

Jika anda menukar julat sel kepada hamparan Excel lengkap menggunakan arahan Jadual tab (Jadual). Memasukkan (Sisipkan), kemudian apabila anda memilih julat dengan tetikus, Microsoft Excel akan secara automatik menambah nama lajur (atau nama jadual jika anda memilih keseluruhan jadual) pada formula.

Formula siap akan kelihatan seperti ini:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Atau mungkin seperti ini:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Apabila menggunakan julat bernama, pautan akan menghala ke sel yang sama tidak kira di mana anda menyalin fungsi tersebut VPR dalam buku kerja.

Menggunakan Kad Liar dalam Formula VLOOKUP

Seperti banyak fungsi lain, VPR Anda boleh menggunakan aksara kad bebas berikut:

  • Tanda soal (?) – menggantikan mana-mana aksara tunggal.
  • Asterisk (*) – menggantikan sebarang jujukan aksara.

Menggunakan Kad Liar dalam Fungsi VPR boleh berguna dalam banyak kes, contohnya:

  • Apabila anda tidak ingat dengan tepat teks yang anda perlukan untuk mencari.
  • Apabila anda ingin mencari beberapa perkataan yang merupakan sebahagian daripada kandungan sel. Tahu bahawa VPR carian mengikut kandungan sel secara keseluruhan, seolah-olah pilihan itu didayakan Padankan keseluruhan kandungan sel (Seluruh sel) dalam carian Excel standard.
  • Apabila sel mengandungi ruang tambahan pada permulaan atau akhir kandungan. Dalam keadaan sedemikian, anda boleh memerah otak anda untuk masa yang lama, cuba memikirkan mengapa formula itu tidak berfungsi.

Contoh 1: Mencari teks yang bermula atau berakhir dengan aksara tertentu

Katakan anda ingin mencari pelanggan tertentu dalam pangkalan data yang ditunjukkan di bawah. Anda tidak ingat nama belakangnya, tetapi anda tahu bahawa ia bermula dengan “ack”. Berikut ialah formula yang akan melakukan kerja dengan baik:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Memandangkan anda pasti telah menemui nama yang betul, anda boleh menggunakan formula yang sama untuk mencari amaun yang dibayar oleh pelanggan ini. Untuk melakukan ini, ubah sahaja hujah ketiga fungsi tersebut VPR kepada nombor lajur yang dikehendaki. Dalam kes kami, ini ialah lajur C (ke-3 dalam julat):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Berikut ialah beberapa lagi contoh dengan kad bebas:

~ Cari nama yang berakhir dengan "lelaki":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Cari nama yang bermula dengan "iklan" dan berakhir dengan "anak lelaki":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Kami mencari nama pertama dalam senarai, yang terdiri daripada 5 aksara:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Untuk berfungsi VPR dengan kad bebas berfungsi dengan betul, sebagai hujah keempat yang anda harus sentiasa gunakan SALAH (SALAH). Jika julat carian mengandungi lebih daripada satu nilai yang sepadan dengan istilah carian dengan kad bebas, maka nilai pertama yang ditemui akan dikembalikan.

Contoh 2: Gabungkan kad bebas dan rujukan sel dalam formula VLOOKUP

Sekarang mari kita lihat contoh yang lebih kompleks tentang cara mencari menggunakan fungsi tersebut VPR mengikut nilai dalam sel. Bayangkan lajur A ialah senarai kunci lesen dan lajur B ialah senarai nama yang memiliki lesen. Selain itu, anda mempunyai sebahagian (beberapa aksara) beberapa jenis kunci lesen dalam sel C1 dan anda ingin mencari nama pemiliknya.

Ini boleh dilakukan menggunakan formula berikut:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Formula ini mencari nilai daripada sel C1 dalam julat yang diberikan dan mengembalikan nilai yang sepadan daripada lajur B. Ambil perhatian bahawa dalam hujah pertama, kami menggunakan aksara ampersand (&) sebelum dan selepas rujukan sel untuk memautkan rentetan teks.

Seperti yang anda lihat dalam rajah di bawah, fungsinya VPR mengembalikan "Jeremy Hill" kerana kunci lesennya mengandungi jujukan aksara daripada sel C1.

Perhatikan bahawa hujah table_array (jadual) dalam tangkapan skrin di atas mengandungi nama jadual (Jadual7) dan bukannya menentukan julat sel. Inilah yang kami lakukan dalam contoh sebelumnya.

Padanan tepat atau anggaran dalam fungsi VLOOKUP

Dan akhirnya, mari kita lihat lebih dekat pada hujah terakhir yang ditentukan untuk fungsi tersebut VPR - range_lookup (pandangan_selang). Seperti yang dinyatakan di awal pelajaran, hujah ini sangat penting. Anda boleh mendapatkan hasil yang sama sekali berbeza dalam formula yang sama dengan nilainya KOD BENAR (BENAR) atau SALAH (SALAH).

Mula-mula, mari kita ketahui maksud Microsoft Excel dengan padanan tepat dan anggaran.

  • Jika hujah range_lookup (julat_lookup) adalah sama dengan SALAH (FALSE), formula mencari padanan tepat, iaitu nilai yang sama seperti yang diberikan dalam hujah lookup_value (nilai_carian). Jika dalam lajur pertama julat tarray_mampu (jadual) menemui dua atau lebih nilai yang sepadan dengan hujah lookup_value (nilai_carian), maka yang pertama akan dipilih. Jika tiada padanan ditemui, fungsi akan melaporkan ralat #AT (#N/A). Sebagai contoh, formula berikut akan melaporkan ralat #AT (#N/A) jika tiada nilai dalam julat A2:A15 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • Jika hujah range_lookup (julat_lookup) adalah sama dengan KOD BENAR (BENAR), formula mencari padanan anggaran. Lebih tepat lagi, pertama fungsi VPR mencari padanan tepat, dan jika tiada yang ditemui, pilih satu anggaran. Padanan anggaran ialah nilai terbesar yang tidak melebihi nilai yang dinyatakan dalam hujah. lookup_value (nilai_carian).

Jika hujah range_lookup (julat_lookup) adalah sama dengan KOD BENAR (BENAR) atau tidak dinyatakan, maka nilai dalam lajur pertama julat hendaklah diisih dalam tertib menaik, iaitu, daripada terkecil kepada terbesar. Jika tidak, fungsi VPR boleh mengembalikan hasil yang salah.

Untuk lebih memahami kepentingan pilihan KOD BENAR (KEBENARAN) atau SALAH (SALAH), mari kita lihat beberapa lagi formula dengan fungsi tersebut VPR dan lihatlah hasilnya.

Contoh 1: Mencari Padanan Tepat dengan VLOOKUP

Seperti yang anda ingat, untuk mencari padanan tepat, hujah keempat fungsi VPR sepatutnya penting SALAH (SALAH).

Mari kita kembali ke jadual dari contoh pertama dan ketahui haiwan mana yang boleh bergerak dengan laju 50 Batu per jam. Saya percaya bahawa formula ini tidak akan menyebabkan anda kesulitan:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Ambil perhatian bahawa julat carian kami (lajur A) mengandungi dua nilai 50 – dalam sel A5 и A6. Formula mengembalikan nilai daripada sel B5. kenapa? Kerana apabila mencari padanan yang tepat, fungsi VPR menggunakan nilai pertama yang ditemui yang sepadan dengan nilai yang dicari.

Contoh 2: Menggunakan VLOOKUP untuk Mencari Padanan Anggaran

Apabila anda menggunakan fungsi tersebut VPR untuk mencari padanan anggaran, iaitu apabila hujah range_lookup (julat_lookup) adalah sama dengan KOD BENAR (BENAR) atau ditinggalkan, perkara pertama yang anda perlu lakukan ialah mengisih julat mengikut lajur pertama dalam tertib menaik.

Ini sangat penting kerana fungsi VPR mengembalikan nilai terbesar seterusnya selepas yang diberikan, dan kemudian carian berhenti. Jika anda mengabaikan pengisihan yang betul, anda akan mendapat hasil yang sangat pelik atau mesej ralat. #AT (#N/A).

Kini anda boleh menggunakan salah satu daripada formula berikut:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Seperti yang anda lihat, saya ingin mengetahui haiwan yang manakah mempunyai kelajuan yang paling hampir 69 Batu per jam. Dan inilah hasilnya fungsi dikembalikan kepada saya VPR:

Seperti yang anda lihat, formula mengembalikan hasil Antelope (Antelop), yang kelajuannya 61 batu sejam, walaupun senarai itu juga termasuk Cheetah (Cheetah) yang berlari laju 70 batu sejam, dan 70 lebih hampir kepada 69 daripada 61, bukan? Kenapa ini terjadi? Kerana fungsi VPR apabila mencari padanan anggaran, mengembalikan nilai terbesar yang tidak lebih besar daripada yang sedang dicari.

Saya harap contoh-contoh ini memberi sedikit penerangan tentang bekerja dengan fungsi tersebut VPR dalam Excel, dan anda tidak lagi memandangnya sebagai orang luar. Sekarang tidak salah untuk mengulangi secara ringkas perkara utama bahan yang telah kita pelajari untuk memperbaikinya dengan lebih baik dalam ingatan.

VLOOKUP dalam Excel – anda perlu ingat ini!

  1. fungsi VPR Excel tidak boleh melihat ke kiri. Ia sentiasa mencari nilai dalam lajur paling kiri julat yang diberikan oleh hujah table_array (meja).
  2. Dalam fungsi VPR semua nilai tidak peka huruf besar-besaran, iaitu huruf kecil dan besar adalah setara.
  3. Jika nilai yang anda cari adalah kurang daripada nilai minimum dalam lajur pertama julat yang dicari, fungsinya VPR akan melaporkan ralat #AT (#N/A).
  4. Jika hujah ke-3 col_index_num (nombor_lajur) kurang daripada 1fungsi VPR akan melaporkan ralat #NILAI! (#VALUE!). Jika ia lebih besar daripada bilangan lajur dalam julat table_array (jadual), fungsi akan melaporkan ralat #REF! (#LINK!).
  5. Gunakan rujukan sel mutlak dalam hujah table_array (jadual) supaya julat carian yang betul dikekalkan semasa menyalin formula. Cuba gunakan julat atau jadual bernama dalam Excel sebagai alternatif.
  6. Apabila melakukan carian padanan anggaran, ingat bahawa lajur pertama dalam julat yang anda cari mesti diisih dalam tertib menaik.
  7. Akhir sekali, ingat kepentingan hujah keempat. Gunakan nilai KOD BENAR (KEBENARAN) atau SALAH (SALAH) dengan sengaja dan anda akan menghilangkan banyak sakit kepala.

Dalam artikel berikut tutorial fungsi kami VPR dalam Excel, kita akan mempelajari contoh yang lebih maju, seperti melakukan pelbagai pengiraan menggunakan VPR, mengekstrak nilai daripada berbilang lajur dan banyak lagi. Terima kasih kerana membaca tutorial ini dan saya harap dapat berjumpa lagi minggu hadapan!

Sila tinggalkan balasan anda