Uporaba funkcije VLOOKUP za zamenjavo vrednosti

Kdor je prelen ali nima časa za branje – si oglejte video. Podrobnosti in nianse so v spodnjem besedilu.

Formulacija problema

Torej, imamo dve tabeli – tabela naročil и cenik:

Naloga je samodejno nadomestiti cene iz cenika v tabelo naročil, s poudarkom na imenu izdelka, tako da lahko kasneje izračunate stroške.

Rešitev

V naboru funkcij Excel pod kategorijo Sklici in nizi (Iskanje in sklicevanje) obstaja funkcija VPR (VLOOKUP).Ta funkcija išče dano vrednost (v našem primeru je to beseda "Jabolka") v skrajnem levem stolpcu navedene tabele (cenik), ki se premika od zgoraj navzdol in, ko jo najde, prikaže vsebino sosednje celice (23 rubljev). Shematično lahko delovanje te funkcije predstavimo tako:

Za lažjo nadaljnjo uporabo funkcije naredite eno stvar hkrati – poimenujte obseg celic v ceniku svoje lastno ime. Če želite to narediti, izberite vse celice cenika razen "glave" (G3: H19), izberite v meniju Vstavi – Ime – Dodeli (Vstavi — Ime — Določi) ali pritisnite CTRL + F3 in vnesite poljubno ime (brez presledkov), kot je Cena… Zdaj, v prihodnosti, lahko uporabite to ime za povezavo do cenika.

Zdaj uporabljamo funkcijo VPR… Izberite celico, v katero bo vnesen (D3) in odprite zavihek Formule – vstavljanje funkcij (Formule — Vstavi funkcijo)… V kategoriji Sklici in nizi (Iskanje in referenca) poiščite funkcijo VPR (VLOOKUP) in pritisnite OK… Pojavilo se bo okno za vnos argumentov za funkcijo:

Uporaba funkcije VLOOKUP za zamenjavo vrednosti

Polnimo jih po vrsti:

  • Želena vrednost (Iskalna vrednost) – ime izdelka, ki naj ga funkcija najde v skrajnem levem stolpcu cenika. V našem primeru beseda "Jabolka" iz celice B3.
  • Tabela (Tabelni niz) – tabela, iz katere so vzete želene vrednosti uXNUMXbuXNUMXbarve, to je naš cenik. Za referenco uporabljamo naše lastno ime »Cena«, ki smo ga dali prej. Če niste navedli imena, lahko preprosto izberete tabelo, vendar ne pozabi pritisniti gumba F4da pripnete povezavo z znaki za dolar, ker bo drugače zdrsnilo navzdol, ko boste kopirali našo formulo navzdol v preostale celice v stolpcu D3:D30.
  • Številka_stolpca (Številka indeksa stolpca) – zaporedno številko (ne črko!) stolpca v ceniku, iz katerega bomo prevzeli vrednosti cen. Prvi stolpec cenika z imeni je oštevilčen z 1, zato potrebujemo ceno iz stolpca z 2.
  • interval_lookup (Iskanje obsega) – v to polje lahko vnesete samo dve vrednosti: FALSE ali TRUE:
      • Če je vnesena vrednost 0 or LAŽI (FALSE), potem to dejansko pomeni, da je dovoljeno samo iskanje natančno ujemanje, torej če funkcija v ceniku ne najde nestandardnega artikla, ki je naveden v tabeli naročil (če je npr. vpisan »Kokos«), generira napako #N/A (ni podatkov).
      • Če je vnesena vrednost 1 or TRUE (PRAV), potem to pomeni, da dovolite iskanje ne natančnega, ampak približno ujemanje, tj. v primeru “coconut” bo funkcija poskušala najti izdelek z imenom, ki je čim bližje “coconut” in vrniti ceno za to ime. V večini primerov lahko takšna približna zamenjava zaigra uporabnika z zamenjavo vrednosti napačnega izdelka, ki je dejansko bil tam! Za večino resničnih poslovnih težav je torej najbolje, da ne dovolite približnega iskanja. Izjema je, ko iščemo številke in ne besedila – na primer pri izračunu stopenjskih popustov.

Vse! Ostaja še pritisniti OK in kopirajte vneseno funkcijo v celoten stolpec.

# N / Napake in njihovo odpravljanje

funkcija VPR (VLOOKUP) vrne napako #N/V (#N/A) če:

  • Omogočeno natančno iskanje (argument Intervalni pogled = 0) in želenega imena ni Tabela.
  • Vključeno grobo iskanje (Intervalni pogled = 1), vendar Tabela, v katerem poteka iskanje, ni razvrščeno po naraščajočem vrstnem redu imen.
  • Oblika celice, iz katere izvira zahtevana vrednost imena (na primer B3 v našem primeru) in oblika celice prvega stolpca (F3: F19) tabele se razlikujeta (na primer številska in besedilna ). Ta primer je še posebej značilen pri uporabi številčnih kod (številke računov, identifikatorji, datumi itd.) namesto besedilnih imen. V tem primeru lahko uporabite funkcije Ч и BESEDILO za pretvorbo formatov podatkov. Videti bo nekako takole:

    =VLOOKUP(BESEDILO(B3),cena,0)

    Več o tem si lahko preberete tukaj.

  • Funkcija ne najde zahtevane vrednosti, ker koda vsebuje presledke ali nevidne nenatisljive znake (prelomi vrstic itd.). V tem primeru lahko uporabite besedilne funkcije TRIM (OBREZI) и PRINT(ČISTO) da jih odstranite:

    =VLOOKUP(OBREZI(ČISTO(B3)),cena,0)

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

Za zatiranje sporočila o napaki # N / A (#N/A) v primerih, ko funkcija ne najde natančnega ujemanja, lahko uporabite funkcijo NAPAKA (NAPAKA)… Tako na primer ta konstrukcija prestreže vse napake, ki jih ustvari VLOOKUP, in jih nadomesti z ničlami:

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

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

PS

Če ne želite izvleči ene vrednosti, ampak celoten niz hkrati (če obstaja več različnih), potem boste morali šamanizirati s formulo polja. ali uporabite novo funkcijo XLOOKUP iz Office 365.

 

  • Izboljšana različica funkcije VLOOKUP (VLOOKUP 2).
  • Hiter izračun stopenjskih (razponov) popustov s funkcijo VLOOKUP.
  • Kako narediti "levi VLOOKUP" z uporabo funkcij INDEX in MATCH
  • Kako s funkcijo VLOOKUP izpolniti obrazce s podatki s seznama
  • Kako iz tabele izvleči ne prvo, ampak vse vrednosti naenkrat
  • Funkciji VLOOKUP2 in VLOOKUP3 iz dodatka PLEX

 

Pustite Odgovori