Fungsi VLOOKUP ing Excel – Pandhuan Pamula: Sintaksis lan Conto

Dina iki kita miwiti seri artikel sing nggambarake salah sawijining fitur Excel - sing paling migunani VPR (VLOOKUP). Fungsi iki, ing wektu sing padha, minangka salah sawijining sing paling rumit lan paling ora dingerteni.

Ing tutorial iki ing VPR Aku bakal nyoba kanggo lay metu dhasar sabisa kanggo nggawe proses learning minangka cetha sabisa kanggo pangguna inexperienced. Kajaba iku, kita bakal sinau sawetara conto nganggo rumus Excel sing bakal nuduhake kasus panggunaan sing paling umum kanggo fungsi kasebut VPR.

  • Gambaran umum lan sintaksis
  • Tuladha VLOOKUP
    • Cara nggunakake VLOOKUP kanggo nggoleki ing lembar Excel liyane
    • Telusuri ing buku kerja liyane nganggo VLOOKUP
    • Cara nggunakake sawetara jeneng utawa tabel ing rumus karo VLOOKUP
    • Nggunakake Wildcards ing Formula VLOOKUP
  • Pertandhingan sing tepat utawa kira-kira ing fungsi VLOOKUP
  • VLOOKUP ing Excel - sampeyan kudu ngelingi iki!

Fungsi VLOOKUP ing Excel - gambaran umum lan sintaks

Dadi apa iku VPR? Inggih, pisanan kabeh, iku fungsi Excel. Apa dheweke nindakake? Iku katon munggah nilai sampeyan nemtokake lan ngasilake nilai sing cocog saka kolom liyane. Secara teknis, VPR katon munggah Nilai ing kolom pisanan saka sawetara diwenehi lan ngasilake asil saka kolom liyane ing baris padha.

Ing aplikasi sing paling umum, fungsi VPR nggoleki basis data kanggo pengenal unik sing diwenehake lan ngekstrak sawetara informasi sing ana gandhengane saka database.

Huruf pisanan ing jeneng fungsi VPR (VLOOKUP) tegese Вvertikal (Vvertikal). Miturut sampeyan bisa mbedakake VPR saka GPR (HLOOKUP), sing nggoleki nilai ing baris ndhuwur rentang − Гhorisontal (Hhorizontal).

fungsi VPR kasedhiya ing Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, lan Excel 2000.

Sintaks fungsi VLOOKUP

fungsi VPR (VLOOKUP) nduweni sintaks ing ngisor iki:

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

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

Nalika sampeyan bisa ndeleng, fungsi VPR ing Microsoft Excel duwe 4 opsi (utawa argumen). Telu sing pertama wajib, sing terakhir opsional.

  • goleki_nilai (lookup_value) - Nilai kanggo nggoleki. Iki bisa dadi nilai (nomer, tanggal, teks) utawa referensi sel (ngemot nilai goleki), utawa nilai sing dibalekake dening sawetara fungsi Excel liyane. Contone, rumus iki bakal nggoleki nilai kasebut 40:

    =VLOOKUP(40,A2:B15,2)

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

Yen nilai goleki kurang saka nilai paling cilik ing kolom pisanan saka sawetara sing katon munggah, fungsi kasebut VPR bakal nglaporake kesalahan #AT (#N/A).

  • table_array (tabel) - loro utawa luwih kolom data. Inget, fungsi VPR tansah golek nilai ing kolom pisanan saka sawetara diwenehi ing pitakonan table_array (meja). Rentang sing bisa dideleng bisa ngemot macem-macem data, kayata teks, tanggal, angka, boolean. Fungsi kasebut ora sensitif huruf cilik, tegese aksara gedhe lan aksara cilik dianggep padha. Dadi rumus kita bakal nggoleki nilai kasebut 40 ing sel saka A2 kanggo A15, amarga A iku kolom pisanan saka sawetara A2:B15 diwenehi ing pitakonan table_array (tabel):

    =VLOOKUP(40,A2:B15,2)

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

  • col_index_num (column_number) iku nomer kolom ing sawetara tartamtu saka kang Nilai ing baris ketemu bakal bali. Kolom paling kiwa ing kisaran kasebut yaiku 1, kolom kapindho yaiku 2, kolom katelu yaiku 3 lan liya-liyane. Saiki sampeyan bisa maca kabeh rumus:

    =VLOOKUP(40,A2:B15,2)

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

    Formula nggoleki nilai 40 ing kisaran A2:A15 lan ngasilake nilai sing cocog saka kolom B (amarga B minangka kolom kapindho ing kisaran A2: B15).

Yen nilai argumen col_index_num (column_number) kurang saka 1banjur VPR bakal nglaporake kesalahan #WARNA! (#VALUE!). Lan yen luwih saka jumlah kolom ing sawetara table_array (tabel), fungsi bakal ngasilake kesalahan #REF! (#LINK!).

  • range_lookup (range_lookup) - nemtokake apa sing kudu digoleki:
    • cocog persis, argumen kudu padha lupute (PALSU);
    • cocog kira-kira, argumentasi padha KODE BENER (TRUE) utawa ora ditemtokake ing kabeh.

    Parameter iki opsional, nanging penting banget. Mengko ing tutorial iki ing VPR Aku bakal nuduhake sawetara conto sing nerangake carane nulis rumus kanggo nemokake sing cocog lan kira-kira.

Tuladha VLOOKUP

Mugi fungsi VPR dadi luwih cetha kanggo sampeyan. Saiki ayo goleki sawetara kasus panggunaan VPR ing rumus karo data nyata.

Cara nggunakake VLOOKUP kanggo nggoleki ing lembar Excel liyane

Ing laku, rumus karo fungsi VPR arang digunakake kanggo nggoleki data ing lembar kerja sing padha. Luwih kerep tinimbang ora, sampeyan bakal nggoleki lan njupuk nilai sing cocog saka sheet liyane.

Supaya bisa nggunakake VPR, nelusuri ing sheet Microsoft Excel liyane, Sampeyan kudu ing pitakonan table_array (tabel) nemtokake jeneng sheet karo tandha seru ngiring dening sawetara sel. Contone, rumus ing ngisor iki nuduhake yen kisaran A2: B15 ana ing sheet jenenge Sheet2.

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

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

Mesthine, jeneng sheet ora kudu diketik kanthi manual. Mung miwiti ngetik rumus, lan nalika nerangake pitakonan table_array (tabel), ngalih menyang sheet sing dikarepake lan pilih sawetara sel sing dipengini nganggo mouse.

Rumus sing ditampilake ing gambar ing ngisor iki nggoleki teks "Produk 1" ing kolom A (iki kolom 1 saka rentang A2:B9) ing lembar kerja. prices.

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

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

Elinga yen nalika nggoleki nilai teks, sampeyan kudu nglampirake ing tandha petik (""), kaya sing biasane ditindakake ing rumus Excel.

Kanggo argumentasi table_array (tabel) iku seng di pengeni kanggo tansah nggunakake referensi Absolute (karo tandha $). Ing kasus iki, sawetara telusuran bakal tetep ora owah nalika nyalin rumus menyang sel liyane.

Telusuri ing buku kerja liyane nganggo VLOOKUP

Kanggo fungsi VPR makarya ing antarane rong buku kerja Excel, sampeyan kudu nemtokake jeneng buku kerja ing kurung kothak sadurunge jeneng sheet.

Contone, ing ngisor iki minangka rumus sing nggoleki nilai kasebut 40 ing lembaran Sheet2 ing buku Angka.xlsx:

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

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

Mangkene cara paling gampang kanggo nggawe rumus ing Excel nganggo VPRsing nyambung menyang buku kerja liyane:

  1. Bukak buku loro. Iki ora dibutuhake, nanging luwih gampang nggawe rumus kanthi cara iki. Sampeyan ora pengin ngetik jeneng buku kerja kanthi manual, ta? Kajaba iku, bakal nglindhungi sampeyan saka kesalahan ketik sing ora disengaja.
  2. Miwiti ngetik fungsi VPRlan nalika nerangake argumentasi table_array (tabel), ngalih menyang buku kerja liyane lan pilih sawetara telusuran sing dibutuhake.

Gambar ing ngisor iki nuduhake rumus kanthi telusuran disetel menyang kisaran ing buku kerja Daftar Harga.xlsx ing lembaran prices.

fungsi VPR bakal bisa digunakake sanajan sampeyan nutup buku kerja sing digoleki lan path lengkap menyang file buku kerja katon ing garis rumus, kaya sing kapacak ing ngisor iki:

Yen jeneng buku kerja utawa lembar ngemot spasi, mula kudu dilampirake ing apostrof:

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

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

Cara nggunakake sawetara jeneng utawa tabel ing rumus karo VLOOKUP

Yen sampeyan pengin nggunakake sawetara telusuran sing padha ing macem-macem fungsi VPR, sampeyan bisa nggawe sawetara jeneng lan ngetik jeneng menyang rumus minangka argumen table_array (meja).

Kanggo nggawe sawetara sing dijenengi, cukup pilih sel lan ketik jeneng sing cocog ing lapangan jeneng ngarep, ing sisih kiwa garis rumus.

Saiki sampeyan bisa nulis rumus ing ngisor iki kanggo nemokake rega produk Produk 1:

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

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

Umume jeneng sawetara bisa digunakake kanggo kabeh buku kerja Excel, mula ora perlu nemtokake jeneng sheet kanggo argumen kasebut table_array (tabel), sanajan rumus lan kisaran telusuran ana ing lembar kerja sing beda. Yen ana ing buku kerja sing beda, banjur sadurunge jeneng kisaran sampeyan kudu nemtokake jeneng buku kerja, umpamane, kaya iki:

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

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

Dadi rumus katon luwih jelas, setuju? Uga, nggunakake kisaran sing dijenengi minangka alternatif sing apik kanggo referensi absolut amarga rentang sing dijenengi ora owah nalika sampeyan nyalin rumus menyang sel liyane. Iki tegese sampeyan bisa yakin manawa sawetara telusuran ing rumus bakal tetep bener.

Yen sampeyan ngowahi sawetara sel dadi spreadsheet Excel lengkap nggunakake printah Tabel (Tabel) tab Insertion (Sisipake), banjur nalika sampeyan milih sawetara karo mouse, Microsoft Excel bakal kanthi otomatis nambah jeneng kolom (utawa jeneng tabel yen sampeyan milih kabeh tabel) menyang rumus.

Rumus sing wis rampung bakal katon kaya iki:

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

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

Utawa bisa uga kaya iki:

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

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

Nalika nggunakake kisaran sing dijenengi, pranala bakal tumuju menyang sel sing padha ora preduli ing ngendi sampeyan nyalin fungsi kasebut VPR ing buku kerja.

Nggunakake Wildcards ing Formula VLOOKUP

Kaya akeh fungsi liyane, VPR Sampeyan bisa nggunakake karakter wildcard ing ngisor iki:

  • Tandha pitakon (?) - ngganti karakter siji.
  • Asterisk (*) - ngganti sembarang urutan karakter.

Nggunakake Wildcards ing Fungsi VPR bisa migunani ing pirang-pirang kasus, contone:

  • Yen sampeyan ora ngelingi persis teks sing kudu ditemokake.
  • Nalika sampeyan pengin nemokake sawetara tembung sing minangka bagean saka isi sel. Ngerti iku VPR telusuran miturut isi sel kanthi sakabehe, kaya pilihan kasebut diaktifake Cocog kabeh isi sel (Kabeh sel) ing telusuran Excel standar.
  • Nalika sel ngemot spasi ekstra ing wiwitan utawa pungkasan isi. Ing kahanan kaya mengkono, sampeyan bisa rak otak kanggo dangu, nyoba kanggo mangerteni apa rumus ora bisa.

Conto 1: Nggoleki teks sing diwiwiti utawa diakhiri nganggo karakter tartamtu

Contone, sampeyan pengin nggoleki pelanggan tartamtu ing basis data sing ditampilake ing ngisor iki. Sampeyan ora ngelingi jeneng mburi, nanging sampeyan ngerti sing diwiwiti karo "ack". Mangkene rumus sing bakal nindakake pakaryan kanthi becik:

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

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

Saiki manawa sampeyan wis nemokake jeneng sing bener, sampeyan bisa nggunakake rumus sing padha kanggo nemokake jumlah sing dibayar dening pelanggan iki. Kanggo nindakake iki, mung ngganti argumen katelu saka fungsi kasebut VPR menyang nomer kolom sing dikarepake. Ing kasus kita, iki kolom C (3 ing kisaran):

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

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

Ing ngisor iki sawetara conto liyane sing nganggo wildcard:

~ Temokake jeneng sing pungkasane "wong":

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

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

~ Temokake jeneng sing diwiwiti karo "iklan" lan diakhiri karo "putra":

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

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

~ Kita nemokake jeneng ngarep ing dhaptar, kalebu 5 karakter:

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

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

Kanggo fungsi VPR karo wildcards makarya bener, minangka pitakonan papat sampeyan kudu tansah nggunakake lupute (PALSU). Yen sawetara telusuran ngemot luwih saka siji nilai sing cocog karo istilah telusuran karo wildcard, nilai pisanan sing ditemokake bakal dibalekake.

Conto 2: Gabungke wildcards lan referensi sel ing rumus VLOOKUP

Saiki ayo goleki conto sing rada rumit babagan carane nggoleki nggunakake fungsi kasebut VPR miturut nilai ing sel. Bayangake kolom A minangka dhaptar kunci lisensi, lan kolom B minangka dhaptar jeneng sing duwe lisensi. Kajaba iku, sampeyan duwe bagean (sawetara karakter) sawetara jinis kunci lisensi ing sel C1, lan sampeyan pengin nemokake jeneng pemilik.

Iki bisa ditindakake kanthi nggunakake rumus ing ngisor iki:

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

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

Rumus iki nggoleki nilai saka sel C1 ing kisaran sing diwenehake lan ngasilake nilai sing cocog saka kolom B. Elinga yen ing argumen pisanan, kita nggunakake karakter ampersand (&) sadurunge lan sawise referensi sel kanggo ngubungake string teks.

Nalika sampeyan bisa ndeleng ing tokoh ngisor, fungsi VPR ngasilake "Jeremy Hill" amarga kunci lisensine ngemot urutan karakter saka sel C1.

Elinga yen argumen kasebut table_array (tabel) ing gambar ing ndhuwur ngemot jeneng tabel (Tabel7) tinimbang nemtokake sawetara sel. Iki sing ditindakake ing conto sadurunge.

Pertandhingan sing tepat utawa kira-kira ing fungsi VLOOKUP

Lan pungkasane, ayo goleki kanthi luwih rinci babagan argumen pungkasan sing ditemtokake kanggo fungsi kasebut VPR - range_lookup (interval_view). Kaya sing kasebut ing wiwitan wulangan, argumentasi iki penting banget. Sampeyan bisa entuk asil temen beda ing rumus padha karo regane KODE BENER (BENER) utawa lupute (PALSU).

Pisanan, ayo ngerteni apa tegese Microsoft Excel kanthi cocog lan kira-kira.

  • Yen argumentasi range_lookup (range_lookup) padha karo lupute (FALSE), rumus katon kanggo match pas, IE persis nilai padha diwenehi ing pitakonan goleki_nilai (lookup_value). Yen ing kolom pisanan saka sawetara tbisa_array (tabel) nemoni loro utawa luwih nilai sing cocog karo argumen kasebut goleki_nilai (search_value), banjur sing pisanan bakal dipilih. Yen ora ana sing cocog, fungsi kasebut bakal nglaporake kesalahan #AT (#N/A). Contone, rumus ing ngisor iki bakal nglaporake kesalahan #AT (#N/A) yen ora ana nilai ing kisaran A2:A15 4:

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

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

  • Yen argumentasi range_lookup (range_lookup) padha karo KODE BENER (TRUE), rumus nggoleki pertandhingan kira-kira. Luwih tepate, pisanan fungsi VPR nggoleki sing cocog, lan yen ora ana sing ditemokake, pilih sing kira-kira. Pertandhingan kira-kira minangka nilai paling gedhe sing ora ngluwihi nilai sing ditemtokake ing argumentasi. goleki_nilai (nilai_goleki).

Yen argumentasi range_lookup (range_lookup) padha karo KODE BENER (TRUE) utawa ora ditemtokake, banjur nilai-nilai ing kolom pisanan saka kisaran kudu diurutake kanthi urutan munggah, yaiku, saka sing paling cilik nganti paling gedhe. Yen ora, fungsi VPR bisa ngasilake asil sing salah.

Kanggo luwih ngerti pentinge pilihan KODE BENER (TRUTH) utawa lupute (PALSU), ayo kang katon ing sawetara liyane rumus karo fungsi VPR lan katon ing asil.

Conto 1: Nemokake Cocokake Persis karo VLOOKUP

Nalika sampeyan ngelingi, kanggo nelusuri match pas, argumen papat fungsi VPR kudu penting lupute (PALSU).

Ayo bali menyang meja saka conto pisanan lan temokake kewan sing bisa mindhah kanthi cepet 50 mil saben jam. Aku percaya yen rumus iki ora bakal nyebabake kesulitan:

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

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

Elinga yen sawetara telusuran kita (kolom A) ngemot rong nilai 50 - ing sel A5 и A6. Formula ngasilake nilai saka sel B5. Kenging punapa? Amarga nalika looking for match pas, fungsi VPR nggunakake nilai pisanan sing ditemokake sing cocog karo sing digoleki.

Conto 2: Nggunakake VLOOKUP kanggo Nemokake Cocokake Kira-kira

Nalika sampeyan nggunakake fungsi VPR kanggo nggoleki sing cocog kira-kira, yaiku nalika argumentasi range_lookup (range_lookup) padha karo KODE BENER (TRUE) utawa diilangi, bab pisanan sing kudu sampeyan lakoni yaiku ngurutake kisaran miturut kolom pisanan kanthi urutan munggah.

Iki penting banget amarga fungsi VPR ngasilake nilai paling gedhe sabanjure sawise diwenehi, banjur telusuran mandheg. Yen sampeyan nglirwakake ngurutake sing bener, sampeyan bakal entuk asil sing aneh banget utawa pesen kesalahan. #AT (#N/A).

Saiki sampeyan bisa nggunakake salah siji saka rumus ing ngisor iki:

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

Kaya sing sampeyan ngerteni, aku pengin ngerteni kewan sing paling cedhak 69 mil saben jam. Lan iki asil fungsi bali kanggo kula VPR:

Nalika sampeyan bisa ndeleng, rumus ngasilake asil Antelop (Antelope), kang kacepetan 61 mil saben jam, sanajan dhaptar uga kalebu cheetah (Cheetah) sing mlaku kanthi cepet 70 mil saben jam, lan 70 luwih cedhak 69 saka 61, iku ora? Yagene iki kedadeyan? Amarga fungsi VPR nalika nggoleki pertandhingan kira-kira, ngasilake nilai paling gedhe sing ora luwih gedhe tinimbang sing digoleki.

Mugi conto iki ngeculaké sawetara cahya ing karya karo fungsi VPR ing Excel, lan sampeyan ora maneh katon ing dheweke minangka wong njaba. Saiki ora babras kanggo mbaleni sedhela TCTerms tombol saka materi sing wis sinau supaya luwih ndandani ing memori.

VLOOKUP ing Excel - sampeyan kudu ngelingi iki!

  1. fungsi VPR Excel ora bisa ndeleng ngiwa. Iku tansah nggoleki nilai ing kolom paling kiwa saka sawetara sing diwenehake dening argumen table_array (meja).
  2. Ing fungsi VPR kabeh nilai ora sensitif huruf cilik, yaiku huruf cilik lan gedhe padha.
  3. Yen nilai sing sampeyan goleki kurang saka nilai minimal ing kolom pisanan saka sawetara sing katon, fungsi kasebut VPR bakal nglaporake kesalahan #AT (#N/A).
  4. Yen argumen kaping 3 col_index_num (column_number) kurang saka 1fungsi VPR bakal nglaporake kesalahan #WARNA! (#VALUE!). Yen luwih saka jumlah kolom ing sawetara table_array (tabel), fungsi bakal laporan kesalahan #REF! (#LINK!).
  5. Gunakake referensi sel absolut ing argumentasi table_array (tabel) supaya sawetara telusuran sing bener dilestarekake nalika nyalin rumus. Coba gunakake kisaran utawa tabel sing dijenengi ing Excel minangka alternatif.
  6. Nalika nindakake telusuran sing cocog kira-kira, elinga yen kolom pisanan ing kisaran sing sampeyan goleki kudu diurutake kanthi urutan munggah.
  7. Pungkasan, elinga pentinge argumen kaping papat. Gunakake nilai KODE BENER (TRUTH) utawa lupute (PALSU) kanthi sengaja lan sampeyan bakal ngilangi akeh sirah.

Ing artikel ing ngisor iki tutorial fungsi kita VPR ing Excel, kita bakal sinau conto sing luwih maju, kayata nindakake macem-macem petungan nggunakake VPR, ngekstrak nilai saka pirang-pirang kolom, lan liya-liyane. Matur nuwun kanggo maca tutorial iki lan muga-muga bisa ketemu maneh minggu ngarep!

Ninggalake a Reply