Korištenje funkcije VLOOKUP za zamjenu vrijednosti

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

Formulacija problema

Dakle, imamo dvije tablice – tablica narudžbi и Cjenik:

Zadatak je automatski zamijeniti cijene iz cjenika u tablicu narudžbi, fokusirajući se na naziv proizvoda kako biste kasnije mogli izračunati trošak.

Riješenje

U skupu funkcija programa Excel, pod kategorijom Reference i nizovi (Traženje i upućivanje) postoji funkcija VPR (VLOOKUP).Ova funkcija traži zadanu vrijednost (u našem primjeru to je riječ "Jabuke") u krajnjem lijevom stupcu navedene tablice (cjenika) krećući se odozgo prema dolje i, nakon što je pronađe, prikazuje sadržaj susjedne ćelije (23 rublja). Shematski se rad ove funkcije može prikazati na sljedeći način:

Radi lakšeg daljnjeg korištenja funkcije, učinite jednu stvar odjednom – dajte rasponu ćelija u cjeniku svoje ime. Da biste to učinili, odaberite sve ćelije cjenika osim "zaglavlja" (G3: H19), odaberite iz izbornika Umetni – Ime – Dodijeli (Umetni — Ime — Definiraj) ili pritisnite CTRL + F3 i unesite bilo koje ime (bez razmaka) poput Cijena… Sada, u budućnosti, možete koristiti ovo ime za poveznicu na cjenik.

Sada koristimo funkciju VPR… Odaberite ćeliju u koju će se unijeti (D3) i otvorite karticu Formule – umetanje funkcija (Formule — Umetni funkciju)… U kategoriji Reference i nizovi (Traženje i referenca) pronaći funkciju VPR (VLOOKUP) i pritisnite OK… Pojavit će se prozor za unos argumenata za funkciju:

Korištenje funkcije VLOOKUP za zamjenu vrijednosti

Ispunjavamo ih redom:

  • Željena vrijednost (Vrijednost traženja) – naziv proizvoda koji funkcija treba pronaći u krajnjem lijevom stupcu cjenika. U našem slučaju, riječ "Jabuke" iz ćelije B3.
  • Stol (Niz tablica) – tablica iz koje se uzimaju željene vrijednosti uXNUMXbuXNUMXbara, odnosno naš cjenik. Za referencu koristimo vlastiti naziv "Cijena" dat ranije. Ako niste dali naziv, možete samo odabrati tablicu, ali ne zaboravite pritisnuti gumb F4za prikvačivanje veze sa znakovima dolara, jer će inače kliziti prema dolje prilikom kopiranja naše formule u ostale ćelije u stupcu D3:D30.
  • Broj_stupca (Broj indeksa stupca) – redni broj (ne slovo!) stupca u cjeniku iz kojeg ćemo preuzimati vrijednosti cijena. Prvi stupac cjenika s imenima je označen brojem 1, stoga nam je potrebna cijena iz stupca pod brojem 2.
  • traženje_intervala (Traženje raspona) – u ovo polje mogu se unijeti samo dvije vrijednosti: FALSE ili TRUE:
      • Ako je unesena vrijednost 0 or LAGANJE (NETOČNO), onda to zapravo znači da je dopušteno samo pretraživanje točno podudaranjetj. ako funkcija ne pronađe nestandardni artikl naveden u tablici narudžbi u cjeniku (ako je upisan npr. “Kokos”), generira grešku #N/A (bez podataka).
      • Ako je unesena vrijednost 1 or TRUE (PRAVI), onda to znači da dopuštate pretragu ne za točnim, već približno podudaranje, tj. u slučaju "kokosa", funkcija će pokušati pronaći proizvod s nazivom koji je što sličniji "kokosu" i vratiti cijenu za taj naziv. U većini slučajeva takva približna zamjena može izigrati korisnika zamjenom vrijednosti pogrešnog proizvoda koji je zapravo bio tamo! Stoga je za većinu stvarnih poslovnih problema najbolje ne dopustiti približno pretraživanje. Izuzetak je kada tražimo brojeve, a ne tekst – na primjer, kada izračunavamo popuste za korake.

Sve! Ostaje pritisnuti OK i kopirati upisanu funkciju na cijeli stupac.

# N / A pogreške i njihovo suzbijanje

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

  • Omogućeno točno pretraživanje (argument Intervalni prikaz = 0), a željeno ime nije unutra Stol.
  • Uključeno grubo pretraživanje (Intervalni prikaz = 1), ali Stol, u kojem se odvija pretraga nije poredano uzlaznim redoslijedom imena.
  • Format ćelije iz koje dolazi tražena vrijednost naziva (na primjer, B3 u našem slučaju) i format ćelija prvog stupca (F3: F19) tablice su različiti (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 ovom slučaju možete koristiti funkcije Ч и TEKST za pretvaranje formata podataka. Izgledat će otprilike ovako:

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

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

  • Funkcija ne može pronaći potrebnu vrijednost jer kôd sadrži razmake ili nevidljive znakove koji se ne mogu ispisati (prijelomi redaka, itd.). U ovom slučaju možete koristiti tekstualne funkcije TRIM (TRIM) и PRINT(ČIST) da biste ih uklonili:

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

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

Da biste potisnuli poruku o pogrešci # N / A (#N/A) u slučajevima kada funkcija ne može pronaći točno podudaranje, možete koristiti funkciju AKO POGREŠKA (UKOLIKO GREŠKA)… Tako, na primjer, ova konstrukcija presreće sve pogreške koje generira VLOOKUP i zamjenjuje ih nulama:

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

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

PS

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

 

  • Poboljšana verzija funkcije VLOOKUP (VLOOKUP 2).
  • Brzi izračun koraka (raspona) popusta pomoću funkcije VLOOKUP.
  • Kako napraviti "lijevi VLOOKUP" pomoću funkcija INDEX i MATCH
  • Kako pomoću funkcije VLOOKUP ispuniti obrasce podacima s popisa
  • Kako izvući ne prvu, već sve vrijednosti iz tablice odjednom
  • Funkcije VLOOKUP2 i VLOOKUP3 iz PLEX dodatka

 

Ostavi odgovor