Funkcijos VLOOKUP naudojimas reikšmėms pakeisti

Kas tingi ar neturi laiko skaityti – žiūrėkite vaizdo įrašą. Išsami informacija ir niuansai yra žemiau esančiame tekste.

Problemos formulavimas

Taigi, mes turime dvi lenteles - užsakymų lentelė и Kainoraštis:

Užduotis yra automatiškai pakeisti kainas iš kainoraščio į užsakymų lentelę, sutelkiant dėmesį į prekės pavadinimą, kad vėliau galėtumėte apskaičiuoti kainą.

Sprendimas

„Excel“ funkcijų rinkinio kategorijoje Nuorodos ir masyvai (Peržiūra ir nuoroda) yra funkcija VPR (ŽIŪRĖTI).Ši funkcija ieško nurodytos reikšmės (mūsų pavyzdyje tai žodis „obuoliai“) nurodytos lentelės (kainoraščio) kairiajame stulpelyje judant iš viršaus į apačią ir ją radusi parodo gretimo langelio turinį. (23 rubliai) .Schemiškai šios funkcijos veikimą galima pavaizduoti Taigi:

Kad būtų lengviau naudotis funkcija, iš karto atlikite vieną veiksmą – nurodykite kainoraščio langelių diapazoną savo vardu. Norėdami tai padaryti, pažymėkite visus kainoraščio langelius, išskyrus „antraštę“ (G3: H19), pasirinkite iš meniu Įterpti – Vardas – Priskirti (Įterpti – pavadinimas – apibrėžti) arba paspauskite CTRL + F3 ir įveskite bet kokį pavadinimą (be tarpų), pvz Kaina … Dabar ateityje galėsite naudoti šį pavadinimą, kad susietumėte su kainoraščiu.

Dabar mes naudojame funkciją VPR… Pasirinkite langelį, kuriame jis bus įvestas (D3) ir atidarykite skirtuką Formulės – Funkcijos įterpimas (Formulės – įterpti funkciją)… Kategorijoje Nuorodos ir masyvai (Ieškoti ir nuoroda) rasti funkciją VPR (ŽIŪRĖTI) ir paspauskite OK... Atsiras langas funkcijos argumentams įvesti:

Funkcijos VLOOKUP naudojimas reikšmėms pakeisti

Mes juos užpildome paeiliui:

  • Norima vertė (Paieškos vertė) – prekės pavadinimas, kurį funkcija turėtų rasti kairiajame kainoraščio stulpelyje. Mūsų atveju žodis „obuoliai“ iš langelio B3.
  • Lentelė (Lentelių masyvas) – lentelė, iš kurios paimtos norimos reikšmės uXNUMXbuXNUMX, tai yra mūsų kainoraštis. Kaip nuoroda, naudojame savo pavadinimą „Kaina“, pateiktą anksčiau. Jei nenurodėte pavadinimo, galite tiesiog pasirinkti lentelę, bet nepamirškite paspausti mygtuko F4kad prisegtų nuorodą dolerio ženklais, nes priešingu atveju, kopijuojant formulę į likusius D3:D30 stulpelio langelius, jis nuslys žemyn.
  • Stulpelio_numeris (Stulpelio indekso numeris) – eilės numeris (ne raidė!) Kainyno stulpelio, iš kurio paimsime kainų reikšmes. Pirmas kainoraščio stulpelis su pavadinimais yra sunumeruotas 1, todėl mums reikia kainos iš 2 stulpelio.
  • interval_lookup (diapazono paieška) – šiame lauke galima įvesti tik dvi reikšmes: FALSE arba TRUE:
      • Jei įvedama reikšmė 0 or MELAVIMAS (FALSE), tai iš tikrųjų reiškia, kad leidžiama tik paieška tikslus atitikimas, ty jei funkcija kainoraštyje neranda užsakymų lentelėje nurodytos nestandartinės prekės (jei įvedama, pvz., „Coconut“), sugeneruos klaidą #N/A (nėra duomenų).
      • Jei įvedama reikšmė 1 or TIKROJI (TIESA), tai reiškia, kad leidžiate ieškoti ne tikslios, o apytikslis atitikmuo, ty „kokoso“ atveju funkcija bandys surasti prekę, kurios pavadinimas būtų kuo artimesnis „kokosui“ ir grąžins šio pavadinimo kainą. Daugeliu atvejų toks apytikslis pakeitimas gali apgauti vartotoją, pakeisdamas netinkamo produkto vertę, kuri iš tikrųjų buvo! Taigi daugeliui tikrų verslo problemų apytikslės paieškos geriausia neleisti. Išimtis yra tada, kai ieškome skaičių, o ne teksto – pavyzdžiui, skaičiuodami žingsnių nuolaidas.

Viskas! Belieka paspausti OK ir nukopijuokite įvestą funkciją į visą stulpelį.

# N / A klaidos ir jų slopinimas

Funkcija VPR (ŽIŪRĖTI) grąžina klaidą #N/A (#N/A) jeigu:

  • Tiksli paieška įjungta (argumentas Intervalinis vaizdas = 0) ir norimo pavadinimo nėra Lentelė.
  • Įtraukta grubi paieška (Intervalinis vaizdas = 1), bet Lentelė, kuriame atliekama paieška, nerūšiuojama pavadinimų didėjimo tvarka.
  • Skiriasi langelio, iš kurio gaunama reikiama pavadinimo reikšmė (pavyzdžiui, mūsų atveju, B3), ir lentelės pirmojo stulpelio (F3: F19) langelių formatas (pavyzdžiui, skaitinis ir tekstinis). ). Šis atvejis ypač būdingas, kai vietoj tekstinių pavadinimų naudojami skaitmeniniai kodai (sąskaitų numeriai, identifikatoriai, datos ir kt.). Tokiu atveju galite naudoti funkcijas Ч и TEKSTAS konvertuoti duomenų formatus. Tai atrodys maždaug taip:

    =ŽIŪRA(TEKSTAS(B3),kaina,0)

    Daugiau apie tai galite paskaityti čia.

  • Funkcija negali rasti reikiamos reikšmės, nes kode yra tarpų arba nematomų nespausdinamų simbolių (eilučių lūžių ir pan.). Tokiu atveju galite naudoti teksto funkcijas TRIM (APKARPYTI) и SPAUSDINIMO(ŠVARUS) Norėdami juos pašalinti:

    =ŽIŪRĖTI(TRIMSPACES(CLEAN(B3)),kaina,0)

    =ŽIŪROS(APDARYTI(VALYTI(B3));kaina;0)

Norėdami užblokuoti klaidos pranešimą # N / A (#N/A) Tais atvejais, kai funkcija negali rasti tikslios atitikties, galite naudoti funkciją IFERROR (IFERROR)… Taigi, pavyzdžiui, ši konstrukcija perima visas VLOOKUP sugeneruotas klaidas ir pakeičia jas nuliais:

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

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

PS

Jei reikia išgauti ne vieną reikšmę, o visą rinkinį iš karto (jei yra keli skirtingi), tuomet teks šamanizuoti su masyvo formule. arba naudokite naują XLOOKUP funkciją iš Office 365.

 

  • Patobulinta funkcijos VLOOKUP versija (VLOOKUP 2).
  • Greitas žingsnio (diapazono) nuolaidų apskaičiavimas naudojant VLOOKUP funkciją.
  • Kaip padaryti „kairįjį VLOOKUP“ naudojant INDEX ir MATCH funkcijas
  • Kaip naudoti VLOOKUP funkciją norint užpildyti formas su duomenimis iš sąrašo
  • Kaip iš lentelės ištraukti ne pirmą, o visas vertes vienu metu
  • VLOOKUP2 ir VLOOKUP3 funkcijos iš PLEX priedo

 

Palikti atsakymą