Sino ang masyadong tamad o walang oras na magbasa - panoorin ang video. Ang mga detalye at nuances ay nasa teksto sa ibaba.
Pagbubuo ng problema
Kaya, mayroon kaming dalawang mesa - mesa ng order и Listahan ng Presyo:
Ang gawain ay awtomatikong palitan ang mga presyo mula sa listahan ng presyo sa talahanayan ng mga order, na tumutuon sa pangalan ng produkto upang sa paglaon ay makalkula mo ang gastos.
Solusyon
Sa Excel function set, sa ilalim ng kategorya Mga sanggunian at array (Paghahanap at sanggunian) may function VPR (VLOOKUP).Ang function na ito ay naghahanap ng isang ibinigay na halaga (sa aming halimbawa, ito ay ang salitang "Mansanas") sa pinakakaliwang column ng tinukoy na talahanayan (listahan ng presyo) na lumilipat mula sa itaas hanggang sa ibaba at, kapag natagpuan ito, ipinapakita ang mga nilalaman ng katabing cell. (23 rubles) .Schematically, ang pagpapatakbo ng function na ito ay maaaring katawanin Kaya:
Para sa kadalian ng karagdagang paggamit ng function, gawin ang isang bagay nang sabay-sabay - bigyan ang hanay ng mga cell sa listahan ng presyo ng iyong sariling pangalan. Upang gawin ito, piliin ang lahat ng mga cell ng listahan ng presyo maliban sa "header" (G3: H19), pumili mula sa menu Ipasok – Pangalan – Italaga (Ipasok — Pangalan — Tukuyin) o pindutin CTRL + F3 at ipasok ang anumang pangalan (walang mga puwang) tulad ng presyo… Ngayon, sa hinaharap, maaari mong gamitin ang pangalang ito para mag-link sa listahan ng presyo.
Ngayon ginagamit namin ang function VPR… Piliin ang cell kung saan ito ilalagay (D3) at buksan ang tab Mga Formula – Pagpasok ng Function (Mga Formula — Ipasok ang Function)… Sa kategorya Mga sanggunian at array (Paghahanap at Sanggunian) hanapin ang function VPR (VLOOKUP) at pindutin ang OK… Lilitaw ang isang window para sa pagpasok ng mga argumento para sa function:
Pinupuno namin ang mga ito nang sunud-sunod:
- Ninanais na halaga (Halaga ng Lookup) – ang pangalan ng produkto na dapat makita ng function sa pinakakaliwang column ng listahan ng presyo. Sa aming kaso, ang salitang "Mansanas" mula sa cell B3.
- mesa (Table Array) – isang talahanayan kung saan kinuha ang mga gustong halaga uXNUMXbuXNUMXbare, iyon ay, ang aming listahan ng presyo. Para sa sanggunian, ginagamit namin ang aming sariling pangalan na "Presyo" na ibinigay kanina. Kung hindi ka nagbigay ng pangalan, maaari mo lamang piliin ang talahanayan, ngunit huwag kalimutang pindutin ang pindutan F4para i-pin ang link na may mga dollar sign, dahil kung hindi, ito ay magda-slide pababa kapag kinokopya ang aming formula pababa sa iba pang mga cell sa column D3:D30.
- Column_number (Numero ng index ng column) – serial number (hindi isang letra!) Ng column sa listahan ng presyo kung saan kami kukuha ng mga halaga ng presyo. Ang unang column ng listahan ng presyo na may mga pangalan ay may bilang na 1, kaya kailangan namin ang presyo mula sa column na may numerong 2.
- interval_lookup (Paghahanap ng Saklaw) – dalawang value lang ang maaaring ilagay sa field na ito: FALSE o TRUE:
- Kung may ipinasok na halaga 0 or KASINUNGALINGAN (MALI), at sa katunayan, nangangahulugan ito na ang paghahanap lamang ang pinapayagan eksaktong tugma, ibig sabihin, kung hindi mahanap ng function ang hindi karaniwang item na tinukoy sa talahanayan ng order sa listahan ng presyo (kung ang "Coconut" ay ipinasok, halimbawa), bubuo ito ng #N/A (walang data) na error.
- Kung may ipinasok na halaga 1 or TRUE (TOTOO), nangangahulugan ito na pinapayagan mo ang paghahanap hindi para sa eksaktong, ngunit tinatayang tugma, ibig sabihin, sa kaso ng "coconut", susubukan ng function na maghanap ng isang produkto na may pangalan na mas malapit hangga't maaari sa "coconut" at ibalik ang presyo para sa pangalang ito. Sa karamihan ng mga kaso, ang naturang tinatayang pagpapalit ay maaaring gumawa ng isang lansihin sa user sa pamamagitan ng pagpapalit sa halaga ng maling produkto na talagang naroon! Kaya para sa karamihan ng mga totoong problema sa negosyo, ang tinatayang paghahanap ay pinakamainam na huwag payagan. Ang exception ay kapag naghahanap kami ng mga numero at hindi text – halimbawa, kapag nagkalkula ng Step Discounts.
Lahat! Ito ay nananatiling pindutin OK at kopyahin ang ipinasok na function sa buong column.
# N / A error at ang kanilang pagsugpo
tungkulin VPR (VLOOKUP) nagbabalik ng #N/A error (#N/A) kung ang:
- Pinagana ang eksaktong paghahanap (argument Interval view = 0) at ang gustong pangalan ay wala sa mesa.
- Kasama ang magaspang na paghahanap (Interval view = 1), ngunit mesa, kung saan ang paghahanap ay nagaganap ay hindi pinagsunod-sunod sa pataas na pagkakasunud-sunod ng mga pangalan.
- Ang format ng cell kung saan nagmula ang kinakailangang halaga ng pangalan (halimbawa, B3 sa aming kaso) at ang format ng mga cell ng unang column (F3: F19) ng talahanayan ay magkaiba (halimbawa, numeric at text ). Ang sitwasyong ito ay partikular na karaniwan kapag gumagamit ng mga numerong code (mga numero ng account, identifier, petsa, atbp.) sa halip na mga pangalan ng teksto. Sa kasong ito, maaari mong gamitin ang mga function Ч и TEXT upang i-convert ang mga format ng data. Magiging ganito ang hitsura nito:
=VLOOKUP(TEXT(B3),presyo,0)
Maaari mong basahin ang higit pa tungkol dito.
- Hindi mahanap ng function ang kinakailangang halaga dahil naglalaman ang code ng mga puwang o hindi nakikitang hindi napi-print na mga character (line break, atbp.). Sa kasong ito, maaari mong gamitin ang mga function ng teksto TRIM (TRIM) и PRINT(MALINIS) upang alisin ang mga ito:
=VLOOKUP(TRIMSPACES(MALINIS(B3)),presyo,0)
=VLOOKUP(TRIM(CLEAN(B3));presyo;0)
Upang sugpuin ang mensahe ng error # N / A (#N/A) sa mga kaso kung saan ang function ay hindi makahanap ng eksaktong tugma, maaari mong gamitin ang function IFERROR (IFERROR)… Kaya, halimbawa, ang konstruksiyon na ito ay humarang sa anumang mga error na nabuo ng VLOOKUP at pinapalitan ang mga ito ng mga zero:
= IFERROR (VLOOKUP (B3, presyo, 2, 0), 0)
= IFERROR (VLOOKUP (B3; presyo; 2; 0); 0)
PS
Kung kailangan mong kunin ang hindi isang halaga, ngunit ang buong set nang sabay-sabay (kung mayroong maraming iba't ibang mga), pagkatapos ay kailangan mong shamanize gamit ang array formula. o gamitin ang bagong feature na XLOOKUP mula sa Office 365.
- Isang pinahusay na bersyon ng VLOOKUP function (VLOOKUP 2).
- Mabilis na pagkalkula ng mga hakbang (saklaw) na mga diskwento gamit ang VLOOKUP function.
- Paano gumawa ng "kaliwang VLOOKUP" gamit ang INDEX at MATCH function
- Paano gamitin ang VLOOKUP function upang punan ang mga form na may data mula sa listahan
- Paano i-pull out hindi ang una, ngunit ang lahat ng mga halaga mula sa talahanayan nang sabay-sabay
- Ang VLOOKUP2 at VLOOKUP3 ay gumagana mula sa PLEX add-on