Korištenje funkcije VLOOKUP za zamjenu vrijednosti

Ko je lijen ili nema vremena za čitanje – pogledajte video. Detalji i nijanse su u tekstu ispod.

Formulacija problema

Dakle, imamo dva stola – naručite sto и cjenovnik:

Zadatak je da se cijene iz cjenovnika automatski zamijene u tablicu narudžbi, fokusirajući se na naziv proizvoda kako biste kasnije mogli izračunati cijenu.

rastvor

U skupu funkcija Excel, pod kategorijom Reference i nizovi (Traženje i upućivanje) postoji funkcija VPR (Vlookup).Ova funkcija traži datu vrijednost (u našem primjeru, to je riječ "Jabuke") u krajnjem lijevom stupcu navedene tablice (cjenovnik) krećući se od vrha do dna i, nakon što je pronađe, prikazuje sadržaj susjedne ćelije (23 rublja) .Šematski, rad ove funkcije može se predstaviti tako:

Radi lakšeg daljeg korišćenja funkcije, uradite jednu stvar odjednom – dajte raspon ćelija u cenovniku svoje ime. Da biste to učinili, odaberite sve ćelije cjenika osim "zaglavlja" (G3: H19), odaberite iz menija Umetnuti – Ime – Dodijeliti (Umetnuti — Naziv — Definirati) ili pritisnite CTRL + F3 i unesite bilo koje ime (bez razmaka) kao Cijena… Sada, u budućnosti, ovaj naziv možete koristiti za vezu sa cjenovnikom.

Sada koristimo funkciju VPR… Odaberite ćeliju u koju će se unijeti (D3) i otvorite karticu Formule – Umetanje funkcije (Formule — Umetanje funkcije)… U kategoriji Reference i nizovi (Potraga i referenca) pronađite funkciju VPR (Vlookup) i pritisnite OK… Pojavit će se prozor za unos argumenata za funkciju:

Korištenje funkcije VLOOKUP za zamjenu vrijednosti

Popunjavamo ih redom:

  • Željena vrijednost (Vrijednost traženja) – naziv proizvoda koji funkcija treba da pronađe u krajnjoj lijevoj koloni cjenovnika. U našem slučaju riječ "Jabuke" iz ćelije B3.
  • sto (Niz tablice) – tabela iz koje su preuzete željene vrijednosti uXNUMXbuXNUMXbare, odnosno naš cjenik. Za referencu, koristimo naše vlastito ime “Cijena” dato ranije. Ako niste dali ime, možete samo odabrati tabelu, ali ne zaboravite da pritisnete dugme F4da zakačite vezu sa znakovima dolara, jer će u suprotnom kliziti prema dolje kada kopirate našu formulu do ostatka ćelija u koloni D3:D30.
  • Broj_kolone (Broj indeksa kolone) – redni broj (ne slovo!) kolone u cjenovniku iz koje ćemo uzimati vrijednosti cijena. Prva kolona cjenovnika sa nazivima je označena brojem 1, stoga nam je potrebna cijena iz kolone pod brojem 2.
  • interval_lookup (Traženje dometa) – u ovo polje se mogu unijeti samo dvije vrijednosti: FALSE ili TRUE:
      • Ako je unesena vrijednost 0 or LAŽEŠ (NETOČNO), to zapravo znači da je dozvoljeno samo pretraživanje tačno podudaranje, tj. ako funkcija ne pronađe nestandardnu ​​stavku navedenu u tabeli narudžbi u cjenovniku (ako je npr. unesen „Kokos“), generirat će grešku #N/A (bez podataka).
      • Ako je unesena vrijednost 1 or ISTINITO (TAČNO), onda to znači da dozvoljavate pretragu ne tačnog, već približno podudaranje, tj. u slučaju “kokos”, funkcija će pokušati pronaći proizvod s nazivom koji je što bliži “kokos” i vratiti cijenu za taj naziv. U većini slučajeva, takva približna zamjena može izigrati trik na korisnika zamjenom vrijednosti pogrešnog proizvoda koji je zapravo bio tamo! Stoga je za većinu stvarnih poslovnih problema najbolje ne dozvoliti približnu pretragu. Izuzetak je kada tražimo brojeve, a ne tekst – na primjer, kada izračunavamo Step popuste.

Sve! Ostaje pritisnuti OK i kopirajte unesenu funkciju u cijeli stupac.

# N/A greške i njihovo suzbijanje

funkcija VPR (Vlookup) vraća grešku #N/A (#N / A) ako:

  • Omogućeno precizno pretraživanje (argument Intervalni prikaz = 0) a željeno ime nije u sto.
  • Uključeno grubo pretraživanje (Intervalni prikaz = 1), ali sto, u kojem se vrši pretraga nije sortirano uzlaznim redoslijedom imena.
  • Različiti su format ćelije iz koje dolazi tražena vrijednost imena (na primjer, B3 u našem slučaju) i format ćelija prve kolone (F3: F19) tabele (na primjer, numerički i tekstualni ). Ovaj slučaj je posebno tipičan kada se koriste numerički kodovi (brojevi računa, identifikatori, datumi, itd.) umjesto tekstualnih naziva. U tom slučaju možete koristiti funkcije Ч и TEKST za pretvaranje formata podataka. To će izgledati otprilike ovako:

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

    Više o ovome možete pročitati ovdje.

  • Funkcija ne može pronaći potrebnu vrijednost jer kod sadrži razmake ili nevidljive znakove koji se ne mogu ispisati (prijelomi reda, itd.). U tom slučaju možete koristiti tekstualne funkcije PODREZATI (PODREZATI) и PRINT(CLEAN) da ih uklonite:

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

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

Za potiskivanje poruke o grešci # N / A (#N / A) u slučajevima kada funkcija ne može pronaći točno podudaranje, možete koristiti funkciju IFERROR (UKOLIKO GREŠKA)… Tako, na primjer, ova konstrukcija presreće sve greške koje generiše VLOOKUP i zamjenjuje ih nulama:

= AKO GREŠKA (VLOOKUP (B3, cijena, 2, 0), 0)

= AKO GREŠKA (VLOOKUP (B3; cijena; 2; 0); 0)

PS

Ako trebate izdvojiti ne jednu vrijednost, već cijeli skup odjednom (ako postoji nekoliko različitih), tada ćete morati šamanizirati s formulom niza. ili koristite novu funkciju XLOOKUP iz Office 365.

 

  • Poboljšana verzija funkcije VLOOKUP (VLOOKUP 2).
  • Brzo izračunavanje popusta za korak (domet) pomoću funkcije VLOOKUP.
  • Kako napraviti "lijevu VLOOKUP" koristeći funkcije INDEX i MATCH
  • Kako koristiti funkciju VLOOKUP za popunjavanje obrazaca podacima sa liste
  • Kako izvući ne prvu, već sve vrijednosti iz tabele odjednom
  • VLOOKUP2 i VLOOKUP3 funkcije iz PLEX dodatka

 

Ostavite odgovor