Fungsi VLOOKUP dina Excel - Pituduh Pemula: Sintaksis sareng Conto

Daptar eusi

Fungsi VLOOKUP dina Excel - pedaran umum sareng sintaksis
- Sintaksis fungsi VLOOKUP
VLOOKUP Conto
Persis atanapi perkiraan cocog dina fungsi VLOOKUP
- Conto 1: Milarian Cocog sareng VLOOKUP
- Conto 2: Ngagunakeun VLOOKUP pikeun Milarian Perkiraan Cocog
VLOOKUP dina Excel - anjeun kedah émut ieu!

Dinten ieu kami ngamimitian runtuyan artikel ngajéntrékeun salah sahiji fitur pang gunana Excel - VPR (VLOOKUP). Fungsi ieu, dina waktos anu sami, mangrupikeun salah sahiji anu paling pajeulit sareng teu kahartos.

Dina tutorial ieu on VPR Kuring bakal nyobian iklas kaluar dasar-dasar sabisa-bisa pikeun ngajantenkeun prosés diajar sajelas-gancang pikeun pangguna anu teu ngalaman. Salaku tambahan, urang bakal diajar sababaraha conto nganggo rumus Excel anu bakal nunjukkeun kasus pamakean anu paling umum pikeun fungsi éta VPR.

Fungsi VLOOKUP dina Excel - pedaran umum sareng sintaksis

Jadi naon eta VPR? Nya, mimitina, éta mangrupikeun fungsi Excel. Naon manehna ngalakukeun? Sigana up nilai anjeun tangtukeun sarta mulih nilai pakait tina kolom séjén. Sacara teknis, VPR Sigana up nilai dina kolom mimiti rentang dibikeun tur mulih hasil tina kolom sejen dina baris anu sarua.

Dina aplikasi anu paling umum, fungsina VPR maluruh database pikeun identifier unik dibikeun tur extracts sababaraha émbaran nu patali jeung eta tina database.

Hurup munggaran dina ngaran fungsi VPR (VLOOKUP) hartina Вvertikal (Vnangtung). Ku eta anjeun bisa ngabedakeun VPR ti GPR (HLOOKUP), nu maluruh nilai dina baris luhur rentang − Гhorisontal (Hhorizontal).

fungsi VPR sayogi dina Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, sareng Excel 2000.

Sintaksis fungsi VLOOKUP

fungsi VPR (VLOOKUP) ngagaduhan sintaksis ieu:

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

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

Sakumaha anjeun tiasa tingali, hiji fungsi VPR dina Microsoft Excel boga 4 pilihan (atawa argumen). Tilu kahiji wajib, anu terakhir opsional.

lookup_value (lookup_value) - Nilai néangan. Ieu bisa mangrupa nilai (nomer, tanggal, téks) atawa rujukan sél (ngandung nilai lookup), atawa nilai balik ku sababaraha fungsi Excel séjén. Contona, rumus ieu bakal néangan nilai 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Lamun nilai lookup kirang ti nilai pangleutikna dina kolom mimiti rentang keur pilari up, fungsi VPR bakal ngalaporkeun kasalahan #AT (#N/A).

table_array (méja) - dua atawa leuwih kolom data. Inget, fungsi VPR salawasna néangan nilai dina kolom mimiti rentang dibikeun dina argumen table_array (méja). Rentang anu tiasa ditingali tiasa ngandung sababaraha data, sapertos téks, kaping, angka, boolean. Fungsina teu merhatikeun hurup, hartina aksara gede jeung aksara leutik dianggap sarua. Jadi rumus urang bakal néangan nilai 40 dina sél ti A2 ka A15, sabab A mangrupakeun kolom mimiti rentang A2:B15 dibikeun dina argumen table_array (méja):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (column_number) nyaeta jumlah kolom dina rentang dibikeun ti mana nilai dina baris kapanggih bakal balik. Kolom anu paling kénca dina rentang anu dipasihkeun nyaéta 1, kolom kadua nyaéta 2, kolom katilu nyaeta 3 teras salajengna. Ayeuna anjeun tiasa maca sadayana rumus:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Rumus pilari nilai 40 dina rentang A2:A15 sarta mulih nilai pakait ti kolom B (sabab B mangrupa kolom kadua dina rentang A2: B15).

Lamun nilai argumen col_index_num (column_number) kirang ti 1saterusna VPR bakal ngalaporkeun kasalahan #Nilai! (#VALUE!). Tur upami eta leuwih ti jumlah kolom dina rentang table_array (meja), fungsi bakal balik kasalahan #REF! (#LINK!).

range_lookup (range_lookup) - nangtukeun naon anu kedah dipilarian:
- cocok persis, argumen kudu sarua PALSU (PALSU);
- cocok perkiraan, argumen sarua KODE BENER (BENER) atanapi henteu dieusian pisan.
Parameter ieu opsional, tapi penting pisan. Engké dina tutorial ieu on VPR Kuring bakal nunjukkeun anjeun sababaraha conto anu ngajelaskeun kumaha cara nyerat rumus pikeun milarian patandingan anu pasti sareng perkiraan.

VLOOKUP Conto

Kuring miharep fungsi VPR janten sakedik langkung jelas ka anjeun. Ayeuna hayu urang nempo sababaraha kasus pamakéan VPR dina rumus jeung data nyata.

Kumaha ngagunakeun VLOOKUP pikeun milarian dina lambaran Excel anu sanés

Dina prakték, rumus kalawan fungsi VPR jarang dianggo pikeun milarian data dina lembar kerja anu sami. Seringna, anjeun bakal milarian sareng nyandak nilai anu saluyu tina lambaran anu sanés.

Dina raraga ngagunakeun VPR, Pilarian dina lambaran Microsoft Excel sejen, Anjeun kudu dina argumen table_array (méja) nangtukeun ngaran lambar kalayan tanda seru dituturkeun ku rentang sél. Contona, rumus di handap nembongkeun yen rentang A2: B15 aya dina lambaran ngaranna Lambaran2.

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

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

Tangtosna, nami lambar henteu kedah dilebetkeun sacara manual. Ngan ngamimitian ngetik rumus, sarta lamun datang ka argumen table_array (meja), pindah ka lambaran nu dipikahoyong tur pilih rentang nu dipikahoyong sél jeung mouse.

Rumus anu dipidangkeun dina screenshot di handap ieu milarian téks "Produk 1" dina kolom A (éta kolom ka-1 tina rentang A2:B9) dina lembar kerja. harga.

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

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

Punten émut yén nalika milarian nilai téks, anjeun kedah ngalampirkeun éta dina tanda petik (""), sapertos biasana dilakukeun dina rumus Excel.

Pikeun argumen table_array (méja) éta desirable salawasna ngagunakeun rujukan mutlak (jeung $ tanda). Dina hal ieu, rentang pilarian bakal tetep unchanged nalika nyalin rumus ka sél séjén.

Pilarian dina buku kerja anu sanés nganggo VLOOKUP

Pikeun fungsi VPR digawé antara dua workbooks Excel, Anjeun kudu nangtukeun nami workbook dina kurung kuadrat saméméh ngaran lambar.

Contona, di handap ieu rumus nu néangan nilai 40 dina lambaran Lambaran2 dina buku Angka.xlsx:

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

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

Ieu mangrupikeun cara panggampangna pikeun nyiptakeun rumus dina Excel nganggo VPRnu numbu ka workbook sejen:

Buka dua buku. Ieu teu diperlukeun, tapi leuwih gampang pikeun nyieun rumus cara kieu. Anjeun teu hoyong nuliskeun ngaran workbook sacara manual, naha? Sajaba ti éta, éta bakal ngajaga anjeun tina typos teu kahaja.
Mimitian ngetik hiji fungsi VPRjeung lamun datang ka argumen table_array (meja), pindah ka workbook sejen tur pilih rentang pilarian diperlukeun dina eta.

Potret layar di handap nunjukkeun rumus sareng pamilarian disetel ka rentang dina buku kerja Daptar Harga.xlsx dina lambaran harga.

fungsi VPR bakal tiasa dianggo sanajan anjeun nutup buku kerja anu dipilarian sareng jalur lengkep kana file buku kerja muncul dina bar rumus, sapertos anu dipidangkeun di handap ieu:

Upami nami buku kerja atanapi lambar ngandung rohangan, maka éta kedah diapit ku tanda kutip:

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

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

Kumaha ngagunakeun rentang ngaranna atawa tabel dina rumus jeung VLOOKUP

Lamun rencanana ngagunakeun rentang pilarian sarua dina sababaraha fungsi VPR, Anjeun bisa nyieun rentang ngaranna tur asupkeun ngaranna kana rumus salaku argumen table_array (méja).

Pikeun nyieun rentang ngaranna, cukup pilih sél jeung asupkeun ngaran luyu dina widang ngaran hareup, ka kénca bar rumus.

Ayeuna anjeun tiasa nyerat rumus di handap ieu pikeun milarian harga produk 1 produk:

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

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

Paling ngaran rentang dianggo pikeun sakabéh workbook Excel, jadi teu kudu nangtukeun nami lambar pikeun argumen table_array (méja), sanajan rumus jeung rentang pilarian aya dina lembar kerja béda. Upami aranjeunna aya dina buku kerja anu béda, maka sateuacan nami kisaran anjeun kedah netepkeun nami buku kerja, contona, sapertos kieu:

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

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

Janten rumusna katingali langkung jelas, satuju? Ogé, ngagunakeun rentang ngaranna mangrupakeun alternatif alus pikeun rujukan mutlak sabab rentang ngaranna teu robah mun anjeun nyalin rumus ka sél séjén. Ieu ngandung harti yén anjeun bisa yakin yén rentang pilarian dina rumus bakal salawasna tetep bener.

Lamun anjeun ngarobah sauntuyan sél kana spreadsheet Excel full-fledged maké paréntah meja (Tabel) tab panempatan (Selapkeun), lajeng mun anjeun milih rentang kalawan mouse, Microsoft Excel bakal otomatis nambahkeun ngaran kolom (atawa ngaran tabel lamun milih sakabéh tabel) kana rumus.

Rumus rengse bakal kasampak kawas kieu:

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

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

Atawa meureun malah kawas kieu:

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

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

Nalika nganggo rentang anu namina, tautan bakal nunjuk ka sél anu sami dimana waé anjeun nyalin fungsina VPR dina workbook.

Ngagunakeun Wildcards dina Rumus VLOOKUP

Sapertos seueur fungsi anu sanés, VPR Anjeun tiasa nganggo karakter wildcard di handap ieu:

tanda tanya (?) - ngagantikeun sagala karakter tunggal.
Asterisk (*) - ngagantikeun sagala runtuyan karakter.

Ngagunakeun Wildcards dina Fungsi VPR tiasa mangpaat dina seueur kasus, contona:

Lamun anjeun teu apal persis téks nu peryogi pikeun manggihan.
Lamun anjeun rék manggihan sababaraha kecap anu mangrupa bagian tina eusi sél. Nyaho éta VPR maluruh ku eusi sél sakabéhna, saolah-olah pilihan diaktipkeun Cocogkeun sakabéh eusi sél (Sakabeh sél) dina pilarian Excel baku.
Nalika sél ngandung spasi tambahan di awal atawa tungtung eusi. Dina kaayaan sapertos kitu, anjeun tiasa nyéépkeun uteuk anjeun pikeun waktos anu lami, nyobian terang naha rumusna henteu jalan.

Conto 1: Néangan téks anu dimimitian atawa ditungtungan ku karakter nu tangtu

Anggap anjeun hoyong milarian palanggan khusus dina pangkalan data anu dipidangkeun di handap ieu. Anjeun teu apal ngaran tukang na, tapi anjeun terang yen eta dimimitian ku "ack". Ieu rumus anu bakal ngalakukeun padamelan éta saé:

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

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

Ayeuna anjeun yakin yén anjeun parantos mendakan nami anu leres, anjeun tiasa nganggo rumus anu sami pikeun milarian jumlah anu dibayar ku nasabah ieu. Jang ngalampahkeun ieu, ngan ngarobah argumen katilu tina fungsi VPR kana nomer kolom anu dipikahoyong. Dina kasus urang, ieu kolom C (ka-3 dina rentang):

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

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

Ieu sababaraha conto deui sareng wildcard:

~ Manggihan ngaran tungtung "lalaki":

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

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

~ Milarian nami anu dimimitian ku "iklan" sareng ditungtungan ku "putra":

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

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

~ Urang manggihan ngaran hareup dina daptar, diwangun ku 5 karakter:

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

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

Pikeun fungsi VPR kalawan wildcards digawé leres, salaku argumen kaopat anjeun kudu salawasna make PALSU (PALSU). Lamun rentang pilarian ngandung leuwih ti hiji nilai nu cocog istilah pilarian kalawan wildcards, lajeng nilai munggaran kapanggih bakal balik.

Conto 2: Gabungkeun wildcards sareng rujukan sél dina rumus VLOOKUP

Ayeuna hayu urang tingali conto anu rada rumit ngeunaan cara milarian nganggo fungsina VPR ku nilai dina sél. Bayangkeun yén kolom A mangrupikeun daptar konci lisénsi, sareng kolom B mangrupikeun daptar nami anu gaduh lisénsi. Salaku tambahan, anjeun gaduh bagian (sababaraha karakter) sababaraha jinis konci lisénsi dina sél C1, sareng anjeun badé milarian nami anu gaduhna.

Ieu tiasa dilakukeun nganggo rumus ieu:

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

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

Rumus ieu kasampak up nilai tina sél C1 dina rentang dibikeun tur mulih nilai saluyu ti kolom B. Catet yén dina argumen kahiji, urang ngagunakeun hiji ampersand (&) karakter saméméh jeung sanggeus rujukan sél pikeun link string téks.

Sakumaha anjeun tiasa tingali dina gambar di handap, fungsi VPR mulih "Jeremy Hill" sabab konci lisénsi na ngandung runtuyan karakter ti sél C1.

Catet yén argumen table_array (tabel) dina screenshot di luhur ngandung nami tabel (Table7) tinimbang nangtukeun rentang sél. Ieu naon anu urang lakukeun dina conto sateuacana.

Persis atanapi perkiraan cocog dina fungsi VLOOKUP

Sarta pamustunganana, hayu urang nyandak hiji tampilan ngadeukeutan dina argumen panungtungan nu dieusian pikeun fungsi nu VPR - range_lookup (interval_view). Sakumaha didadarkeun di awal palajaran, argumen ieu penting pisan. Anjeun tiasa kéngingkeun hasil anu béda-béda dina rumus anu sami sareng nilaina KODE BENER (BENER) atawa PALSU (PALSU).

Mimiti, hayu urang terang naon hartosna Microsoft Excel ku patandingan anu pasti sareng perkiraan.

Lamun argumen range_lookup (range_lookup) sarua jeung PALSU (PALSU), rumus néangan hiji patandingan pasti, ie persis nilai sarua sakumaha dirumuskeun dina argumen lookup_value (lookup_value). Lamun dina kolom kahiji rentang tsanggup_array (méja) papanggih dua atawa leuwih nilai nu cocog argumen lookup_value (search_value), teras anu kahiji bakal dipilih. Upami teu aya patandingan anu kapendak, fungsina bakal ngalaporkeun kasalahan #AT (#N/A). Contona, rumus di handap ieu bakal ngalaporkeun kasalahan #AT (#N / A) lamun euweuh nilai dina rentang A2: A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Lamun argumen range_lookup (range_lookup) sarua jeung KODE BENER (BENER), rumus néangan hiji patandingan perkiraan. Leuwih tepat, kahiji fungsi VPR néangan hiji patandingan pasti, sarta lamun teu kapanggih, pilih hiji perkiraan. Hiji patandingan perkiraan mangrupa nilai pangbadagna nu teu ngaleuwihan nilai dieusian dina argumen. lookup_value (lookup_value).

Lamun argumen range_lookup (range_lookup) sarua jeung KODE BENER (BENER) atanapi henteu dieusian, maka nilai-nilai dina kolom kahiji tina rentang kedah diurutkeun dina urutan naek, nyaéta, ti pangleutikna ka panggedena. Upami teu kitu, fungsi VPR bisa balik hasil erroneous.

Pikeun langkung ngartos pentingna pilihan KODE BENER (TRUTH) atawa PALSU (PALSU), hayu urang tingali sababaraha deui rumus sareng fungsina VPR jeung kasampak di hasilna.

Conto 1: Milarian Cocog sareng VLOOKUP

Anjeun apal, pikeun milarian hiji patandingan pasti, argumen kaopat fungsi VPR kedah masalah PALSU (PALSU).

Hayu urang balik deui ka tabél tina conto anu pangheulana sareng milarian sato mana anu tiasa gerak gancang 50 mil per jam. Kuring yakin yén rumus ieu moal ngabalukarkeun anjeun kasusah:

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

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

Catet yén rentang pilarian kami (kolom A) ngandung dua nilai 50 - dina sél A5 и A6. Rumus mulihkeun nilai tina sél B5. Naha? Kusabab nalika néangan hiji patandingan pasti, fungsi VPR ngagunakeun nilai munggaran kapanggih nu cocog hiji keur searched pikeun.

Conto 2: Ngagunakeun VLOOKUP pikeun Milarian Perkiraan Cocog

Lamun anjeun ngagunakeun fungsi VPR pikeun néangan hiji cocok perkiraan, nyaéta nalika argumen range_lookup (range_lookup) sarua jeung KODE BENER (BENER) atanapi disingkahkeun, hal kahiji anu anjeun kedah laksanakeun nyaéta nyortir rentang ku kolom kahiji dina urutan naek.

Ieu penting pisan sabab fungsi VPR mulih nilai panggedena hareup sanggeus hiji dibikeun, lajeng pilarian eureun. Lamun maranéh ngalalaworakeun kana asihan bener, anjeun bakal mungkas nepi ka hasil pisan aneh atawa pesen kasalahan. #AT (#N/A).

Ayeuna anjeun tiasa nganggo salah sahiji rumus ieu:

=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)

Sakumaha anjeun tiasa tingali, abdi hoyong manggihan nu sato nu boga speed pangdeukeutna 69 mil per jam. Sarta di dieu mangrupa hasil fungsi balik ka kuring VPR:

Sakumaha anjeun tiasa tingali, rumus ngabalikeun hasil Antelop (Antelop), anu laju 61 mil per jam, najan daftar ogé ngawengku cetah (Cheetah) anu lumpat laju 70 mil per jam, sareng 70 langkung caket kana 69 tibatan 61, sanés? Naha ieu kajadian? Kusabab fungsi VPR lamun neangan hiji cocok perkiraan, mulih nilai pangbadagna nu teu leuwih gede ti hiji keur searched pikeun.

Kuring miharep conto ieu héd sababaraha lampu dina gawé bareng fungsi VPR dina Excel, jeung anjeun moal deui kasampak di dirina salaku urang luar. Ayeuna teu menyakiti sakeudeung ngulang titik konci tina bahan kami geus diajarkeun dina urutan hadé ngalereskeun eta dina memori.

VLOOKUP dina Excel - anjeun kedah émut ieu!

fungsi VPR Excel teu bisa nempo kénca. Éta salawasna milarian nilai dina kolom paling kenca tina rentang anu dipasihkeun ku argumen table_array (méja).
Dina fungsina VPR sadaya nilai henteu sensitip, nyaéta hurup leutik sareng ageung sami.
Lamun nilai nu Anjeun keur pilari kirang ti nilai minimum dina kolom mimiti rentang keur pilari up, fungsi VPR bakal ngalaporkeun kasalahan #AT (#N/A).
Lamun argumen 3rd col_index_num (column_number) kirang ti 1fungsi VPR bakal ngalaporkeun kasalahan #Nilai! (#VALUE!). Lamun leuwih gede ti jumlah kolom dina rentang table_array (meja), fungsi bakal ngalaporkeun kasalahan #REF! (#LINK!).
Paké rujukan sél mutlak dina argumen table_array (tabél) ku kituna rentang pilarian bener dilestarikan nalika nyalin rumus. Coba nganggo rentang anu dingaranan atanapi tabel dina Excel salaku alternatif.
Lamun ngalakukeun hiji pilarian cocok perkiraan, inget yen kolom kahiji dina rentang nu Anjeun keur pilari kudu diurutkeun dina urutan naek.
Tungtungna, inget pentingna argumen kaopat. Paké nilai KODE BENER (TRUTH) atawa PALSU (PALSU) ngahaja sareng anjeun bakal ngaleungitkeun seueur nyeri sirah.

Dina artikel di handap ieu tutorial fungsi urang VPR dina Excel, urang bakal diajar conto leuwih maju, kayaning ngajalankeun rupa itungan ngagunakeun VPR, nimba nilai tina sababaraha kolom, sareng seueur deui. Hatur nuhun pikeun maca tutorial ieu sareng kuring ngarepkeun anjeun tiasa ningali deui minggu payun!