Nggunakake fungsi VLOOKUP kanggo ngganti nilai

Sapa sing kesed utawa ora duwe wektu kanggo maca - nonton video kasebut. Rincian lan nuansa ana ing teks ing ngisor iki.

Formulasi masalah

Dadi, kita duwe rong tabel - meja pesenan и price list:

Tugas kasebut yaiku ngganti rega saka dhaptar rega menyang tabel pesenan kanthi otomatis, fokus ing jeneng produk supaya mengko sampeyan bisa ngetung biaya.

solusi

Ing set fungsi Excel, ing kategori Referensi lan susunan (Goleki lan referensi) ana fungsi VPR (VLOOKUP).Fungsi iki nggoleki nilai sing diwenehake (ing conto kita, iki tembung "Apel") ing kolom paling kiwa saka tabel sing ditemtokake (dhaptar rega) sing pindhah saka ndhuwur menyang ngisor lan, sawise ditemokake, nampilake isi sel jejer. (23 rubel) .Schematically, operasi fungsi iki bisa dituduhake Dadi:

Kanggo luwih gampang nggunakake fungsi kasebut, lakoni siji-sijine - menehi sawetara sel ing dhaptar rega jeneng sampeyan dhewe. Kanggo nindakake iki, pilih kabeh sel dhaptar rega kajaba "header" (G3: H19), pilih saka menu Lebokake - Jeneng - Temtokake (Sisipake - Jeneng - Define) utawa pencet CTRL + F3 lan ketik jeneng apa wae (ora ana spasi) kaya price… Saiki, ing mangsa ngarep, sampeyan bisa nggunakake jeneng iki kanggo nyambung menyang dhaptar rega.

Saiki kita nggunakake fungsi kasebut VPR… Pilih sel sing bakal dilebokake (D3) banjur bukak tab Rumus - Sisipan Fungsi (Formula - Fungsi Sisipake)… Ing kategori Referensi lan susunan (Nggoleki lan Referensi) golek fungsi VPR (VLOOKUP) lan penet OK… Jendhela kanggo ngetik argumen kanggo fungsi bakal katon:

Nggunakake fungsi VLOOKUP kanggo ngganti nilai

Kita ngisi kanthi giliran:

  • Nilai sing dikarepake (Nilai Golek) – jeneng produk sing fungsi kudu nemokake ing kolom paling kiwa saka dhaftar rega. Ing kasus kita, tembung "Apel" saka sel B3.
  • Tabel (Tabel Array) – Tabel saka ngendi nilai sing dikarepake uXNUMXbuXNUMXbare dijupuk, yaiku, dhaptar rega kita. Kanggo referensi, kita nggunakake jeneng kita dhewe "Rega" diwenehi sadurungé. Yen sampeyan ora menehi jeneng, sampeyan mung bisa milih meja, nanging aja lali pencet tombol F4kanggo pin link karo pratandha dollar, amarga yen ora, bakal geser mudhun nalika nyalin rumus kita mudhun menyang sel liyane ing kolom D3:D30.
  • nomer_kolom (Nomer indeks kolom) – nomer serial (ora layang!) Saka kolom ing dhaftar rega saka kang kita bakal njupuk nilai rega. Kolom pisanan dhaptar rega kanthi jeneng nomer 1, mula kita butuh rega saka kolom nomer 2.
  • interval_lookup (Range Lookup) - mung rong nilai sing bisa dilebokake ing lapangan iki: FALSE utawa TRUE:
      • Yen nilai diketik 0 or GUSTI (SALAH), banjur nyatane iki tegese mung telusuran sing diidini cocog persis, IE yen fungsi ora nemokake item non-standar kasebut ing tabel pesenan ing dhaftar rega (yen "Klapa" ngetik, contone,), bakal generate kesalahan #N / ​​A (ora ana data).
      • Yen nilai diketik 1 or TRUE (BENER), banjur iki tegese sampeyan ngidini panelusuran ora kanggo pas, nanging cocog kira-kira, IE ing cilik saka "klapa", fungsi bakal nyoba kanggo golek produk karo jeneng sing cedhak sabisa kanggo "klapa" lan bali rega kanggo jeneng iki. Umume kasus, substitusi kira-kira kaya ngono bisa ngapusi pangguna kanthi ngganti nilai produk sing salah sing ana! Dadi kanggo paling masalah bisnis nyata, panelusuran kira-kira paling apik ora kanggo ngidini. Pangecualian yaiku nalika kita nggoleki nomer lan dudu teks - contone, nalika ngitung Diskon Langkah.

Kabeh! Iku tetep kanggo mencet OK lan nyalin fungsi sing dilebokake menyang kabeh kolom.

# N / A kasalahan lan dipatèni sing

fungsi VPR (VLOOKUP) ngasilake kesalahan #N / ​​A (#N/A) yen a:

  • Telusuran sing tepat diaktifake (argumen Tampilan interval = 0) lan jeneng sing dikarepake ora ana ing Tabel.
  • Telusuran kasar kalebu (Tampilan interval = 1), nanging Tabel, sing digoleki ora diurutake miturut urutan jeneng.
  • Format sel sing asale saka nilai jeneng sing dibutuhake (umpamane, B3 ing kasus kita) lan format sel ing kolom pisanan (F3: F19) tabel beda (contone, angka lan teks. ). Kasus iki utamané khas nalika nggunakake kode numerik (nomer akun, pengenal, tanggal, etc.) tinimbang jeneng teks. Ing kasus iki, sampeyan bisa nggunakake fungsi Ч и TEXT kanggo ngowahi format data. Bakal katon kaya iki:

    =VLOOKUP(TEXT(B3),rega,0)

    Sampeyan bisa maca liyane babagan iki kene.

  • Fungsi kasebut ora bisa nemokake nilai sing dibutuhake amarga kode kasebut ngemot spasi utawa karakter sing ora bisa dicithak (line break, lsp.). Ing kasus iki, sampeyan bisa nggunakake fungsi teks TRIM (TRIM) и PRINT(RESIH) kanggo mbusak:

    =VLOOKUP(TRIMSPACES(BERSIH(B3)),rega,0)

    =VLOOKUP(TRIM(BERSIH(B3));rega;0)

Kanggo nyuda pesen kesalahan # N / A (#N/A) ing kasus ngendi fungsi ora bisa nemokake match pas, sampeyan bisa nggunakake fungsi IFERROR (IFERROR)… Dadi, contone, konstruksi iki nyegat kesalahan sing digawe dening VLOOKUP lan ngganti karo nol:

= IFERROR (VLOOKUP (B3, rega, 2, 0), 0)

= IFERROR (VLOOKUP (B3; rega; 2; 0); 0)

PS

Yen sampeyan kudu ngekstrak ora siji nilai, nanging kabeh set bebarengan (yen ana sawetara beda), sampeyan kudu shamanize karo rumus array. utawa gunakake fitur XLOOKUP anyar saka Office 365.

 

  • Versi apik saka fungsi VLOOKUP (VLOOKUP 2).
  • Pitungan cepet diskon langkah (jarak) nggunakake fungsi VLOOKUP.
  • Carane nggawe "VLOOKUP kiwa" nggunakake fungsi INDEX lan MATCH
  • Cara nggunakake fungsi VLOOKUP kanggo ngisi formulir kanthi data saka dhaptar
  • Carane narik metu ora pisanan, nanging kabeh nilai saka meja bebarengan
  • Fungsi VLOOKUP2 lan VLOOKUP3 saka tambahan PLEX

 

Ninggalake a Reply