Funktsiooni VLOOKUP kasutamine väärtuste asendamiseks

Kes on liiga laisk või kellel pole aega lugeda – vaadake videot. Üksikasjad ja nüansid on allolevas tekstis.

Probleemi sõnastamine

Niisiis, meil on kaks lauda - tellimislaud и hinnakiri:

Ülesanne on asendada hinnakirjast hinnad automaatselt tellimuste tabelisse, keskendudes toote nimetusele, et hiljem saaksid oma maksumust arvutada.

Lahendus

Exceli funktsioonikomplektis kategooria all Viited ja massiivid (Otsing ja viide) on funktsioon VPR (OTSING).See funktsioon otsib antud tabeli (hinnakirja) vasakpoolsemast veerust antud väärtust (meie näites on see sõna "õunad") liikudes ülalt alla ja pärast selle leidmist kuvab külgneva lahtri sisu (23 rubla) .Skeemiliselt saab selle funktsiooni toimimist kujutada Nii:

Funktsiooni edasise kasutamise hõlbustamiseks tehke üks asi korraga – andke hinnakirja lahtrite vahemikule oma nimi. Selleks vali kõik hinnakirja lahtrid peale “päise” (G3: H19), vali menüüst Sisesta – nimi – määra (Sisesta - nimi - määrake) või vajutage CTRL + F3 ja sisestage mis tahes nimi (ilma tühikuteta) nagu Hind… Nüüd saate seda nime kasutada hinnakirja linkimiseks.

Nüüd kasutame funktsiooni VPR… Valige lahter, kuhu see sisestatakse (D3) ja avage vahekaart Valemid – funktsiooni sisestamine (Valemid – lisafunktsioon)… Kategoorias Viited ja massiivid (Otsing ja viide) leida funktsioon VPR (OTSING) ja vajutage OK… Ilmub funktsiooni argumentide sisestamise aken:

Funktsiooni VLOOKUP kasutamine väärtuste asendamiseks

Täidame need omakorda:

  • Soovitud väärtus (Otsinguväärtus) – toote nimetus, mille funktsioon peaks leidma hinnakirja kõige vasakpoolsemast veerust. Meie puhul sõna "Õunad" lahtrist B3.
  • Tabel (Tabelimassiiv) – tabel, millest on võetud soovitud väärtused uXNUMXbuXNUMX, see tähendab meie hinnakiri. Viitamiseks kasutame meie enda varem antud nimetust “Price”. Kui te nime ei andnud, saate lihtsalt valida tabeli, kuid ärge unustage nuppu vajutada F4et kinnitada link dollarimärkidega, sest vastasel juhul libiseb see meie valemi kopeerimisel alla veeru D3:D30 ülejäänud lahtritesse.
  • Veeru_number (Veeru indeksi number) – hinnakirja veeru seerianumber (mitte täht!), kust võtame hinnaväärtused. Hinnakirja esimene veerg koos nimetustega on nummerdatud 1, seepärast vajame hinda numbriga 2 veerust.
  • intervall_otsing (vahemiku otsing) – sellele väljale saab sisestada ainult kaks väärtust: FALSE või TRUE:
      • Kui sisestatakse väärtus 0 or VALETAMINE (VALE), siis tegelikult tähendab see, et ainult otsing on lubatud täpne vaste, ehk kui funktsioon ei leia hinnakirjast tellimustabelis märgitud mittestandardset kaupa (kui on sisestatud näiteks “Kookospähkel”), genereerib see vea #N/A (no data).
      • Kui sisestatakse väärtus 1 or TRUE (TÕSI), siis see tähendab, et lubate otsida mitte täpset, vaid ligikaudne vaste, st “kookose” puhul püüab funktsioon leida toote, mille nimi on võimalikult lähedane “kookospähklile” ja tagastab selle nimetuse hinna. Enamasti võib selline ligikaudne asendus kasutajale triki mängida, asendades vale toote väärtuse, mis seal tegelikult oli! Nii et enamiku tegelike äriprobleemide korral on parem mitte lubada ligikaudset otsingut. Erandiks on siis, kui otsime numbreid, mitte teksti – näiteks sammude allahindluste arvutamisel.

Kõik! Jääb üle vajutada OK ja kopeerige sisestatud funktsioon kogu veergu.

# N / A vead ja nende summutamine

funktsioon VPR (OTSING) tagastab vea #N/A (#N/A) kui a:

  • Täpne otsing on lubatud (argument Intervallvaade = 0) ja soovitud nime pole Tabel.
  • Kaasatud jämeotsing (Intervallvaade = 1), aga Tabel, milles otsimine toimub, ei ole järjestatud nimede kasvavas järjekorras.
  • Lahtri vorming, kust pärineb nime nõutav väärtus (meie puhul näiteks B3) ja tabeli esimese veeru lahtrite vorming (F3: F19) on erinevad (näiteks numbriline ja tekst ). See juhtum on eriti tüüpiline, kui tekstinimede asemel kasutatakse numbrilisi koode (kontonumbrid, identifikaatorid, kuupäevad jne). Sel juhul saate funktsioone kasutada Ч и TEKST andmevormingute teisendamiseks. See näeb välja umbes selline:

    =VLOOKUP(TEKST(B3),hind,0)

    Täpsemalt saad selle kohta lugeda siit.

  • Funktsioon ei leia vajalikku väärtust, kuna kood sisaldab tühikuid või nähtamatuid mitteprinditavaid märke (reavahetused jne). Sel juhul saate kasutada tekstifunktsioone TRIM (TRIM) и PRINT(PUHAS) nende eemaldamiseks:

    =VLOOKUP(TRIMSPACES(CLEAN(B3)),hind,0)

    =VLOOKUP(TRIM(CLEAN(B3));hind;0)

Veateate summutamiseks # N / A (#N/A) juhtudel, kui funktsioon ei leia täpset vastet, saate funktsiooni kasutada VIGA (IFERROR)... Näiteks see konstruktsioon peatab kõik VLOOKUP-i tekitatud vead ja asendab need nullidega:

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

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

PS

Kui teil on vaja eraldada mitte üks väärtus, vaid kogu komplekt korraga (kui neid on mitu erinevat), peate šamaniseerima massiivi valemiga. või kasutage Office 365 uut XLOOKUP-funktsiooni.

 

  • Funktsiooni VLOOKUP täiustatud versioon (VLOOKUP 2).
  • Sammu (vahemiku) allahindluste kiire arvutamine funktsiooni VLOOKUP abil.
  • Kuidas teha vasakpoolset VLOOKUP-i, kasutades funktsioone INDEX ja MATCH
  • Kuidas kasutada VLOOKUP funktsiooni vormide täitmiseks loendi andmetega
  • Kuidas tabelist välja tõmmata mitte esimene, vaid kõik väärtused korraga
  • VLOOKUP2 ja VLOOKUP3 funktsioonid PLEXi lisandmoodulist

 

Jäta vastus