Ndani ali waulesi kwambiri kapena alibe nthawi yowerenga - penyani kanema. Tsatanetsatane ndi ma nuances ali m'munsimu.
Kupanga vuto
Chifukwa chake, tili ndi magome awiri - dongosolo tebulo и mndandanda mtengo:
Ntchito ndikusintha mitengo kuchokera pamndandanda wamitengo kupita pagome la maoda okha, ndikungoyang'ana dzina la chinthucho kuti pambuyo pake mutha kuwerengera mtengo wake.
Anakonza
Mu Excel ntchito set, pansi pa gulu References ndi magulu (Kufufuza ndi kutanthauzira) pali ntchito VPR (VLOOKUP).Ntchitoyi imayang'ana mtengo womwe wapatsidwa (mwachitsanzo, awa ndi mawu oti "maapulo") kumanzere kwenikweni kwa tebulo lomwe latchulidwa (mndandanda wamitengo) likuyenda kuchokera pamwamba mpaka pansi ndipo, mutalipeza, likuwonetsa zomwe zili mu cell yoyandikana nayo. (23 rubles) .Schematically, ntchito ya ntchitoyi ikhoza kuyimiridwa Choncho:
Kuti muchepetse kugwiritsa ntchito ntchitoyi, chitani chinthu chimodzi nthawi imodzi - perekani kuchuluka kwa maselo omwe ali pamndandanda wamitengo dzina lanu. Kuti muchite izi, sankhani ma cell onse amndandanda wamitengo kupatula "mutu" (G3: H19), sankhani kuchokera pamenyu. Ikani - Dzina - Perekani (Lowetsani - Dzina - Tanthauzirani) kapena kukanikiza CTRL + F3 ndipo lowetsani dzina lililonse (palibe mipata) monga Price… Tsopano, m'tsogolomu, mutha kugwiritsa ntchito dzinali kulumikiza mndandanda wamitengo.
Tsopano timagwiritsa ntchito VPR… Sankhani selo kumene adzalowetsedwa (D3) ndi kutsegula tabu Mafomula - Kuyika kwa Ntchito (Mafomula - Ikani Ntchito)… Mu gulu References ndi magulu (Kufufuza ndi Kufotokozera) pezani ntchito VPR (VLOOKUP) Ndi kukanikiza OK… Zenera lolowetsa mfundo za ntchitoyi liwoneka:
Timawadzaza motsatira:
- Mtengo wofunidwa (Kufufuza Mtengo) - dzina la mankhwala omwe ntchitoyo iyenera kupeza kumanzere kwa mndandanda wamtengo. Kwa ife, mawu oti "maapulo" ochokera ku cell B3.
- Table (Table Array) - tebulo lomwe zinthu zomwe timafunikira uXNUMXbuXNUMXbare zimatengedwa, ndiye kuti, mndandanda wathu wamitengo. Kufotokozera, timagwiritsa ntchito dzina lathu "Price" lomwe laperekedwa kale. Ngati simunapereke dzina, mutha kungosankha tebulo, koma musaiwale kukanikiza batani F4kulumikiza ulalo ndi zizindikiro za dollar, chifukwa ngati sichoncho, idzatsika pokopera fomula yathu mpaka ma cell ena onse omwe ali mgawo la D3:D30.
- Nambala_gawo (Nambala yazanja) - nambala ya serial (osati kalata!) Pa ndime mu mndandanda wamtengo womwe tidzatengere mitengo yamtengo wapatali. Mzere woyamba wa mndandanda wamitengo wokhala ndi mayina ndi nambala 1, chifukwa chake timafunikira mtengo kuchokera pamndandanda womwe uli ndi nambala 2.
- interval_lookup (Kufufuza kwa Range) - zikhalidwe ziwiri zokha zitha kulowetsedwa m'gawoli: ZABODZA kapena ZOONA:
- Ngati mtengo walowa 0 or KUnama (ZABODZA), ndiye kwenikweni izi zikutanthauza kuti kufufuza kokha ndikololedwa kufanana kwenikweni, mwachitsanzo, ngati ntchitoyi siipeza chinthu chomwe sichinali chodziwika bwino chomwe chatchulidwa patebulo lamtengo wapatali (ngati "Kokonati" yalowetsedwa, mwachitsanzo), idzapanga cholakwika cha #N/A (palibe deta).
- Ngati mtengo walowa 1 or WOONA (ZOONA), ndiye izi zikutanthauza kuti mumalola kufufuza osati zenizeni, koma pafupifupi machesi, mwachitsanzo pa "kokonati", ntchitoyi idzayesa kupeza mankhwala omwe ali ndi dzina lomwe liri pafupi kwambiri ndi "kokonati" ndikubwezera mtengo wa dzinali. Nthawi zambiri, kuyerekezera kotereku kumatha kusokoneza wogwiritsa ntchito posintha mtengo wa chinthu cholakwika chomwe chinalipo! Chifukwa chake pazovuta zambiri zamabizinesi, kusaka pafupifupi ndikwabwino kusalola. Kupatulapo ndi pamene tikufuna manambala osati malemba - mwachitsanzo, powerengera Magawo Ochotsera.
Zonse! Imakhalabe kukanikiza OK ndi kukopera zomwe zalowetsedwa ku gawo lonse.
# N / A zolakwika ndi kuponderezedwa kwawo
ntchito VPR (VLOOKUP) imabweretsa cholakwika cha #N/A (#N / A) ngati a:
- Kusaka kwenikweni kwayatsidwa (kukangana Kuwona kwapakati = 0) ndipo dzina lomwe mukufuna silinalowemo Table.
- Kusaka koyipa kumaphatikizidwa (Kuwona kwapakati = 1), koma Table, m’mene kufufuzaku kukuchitika sikukusanjidwa m’dongosolo lokwera la mayina.
- Mawonekedwe a selo komwe mtengo wofunikira wa dzina umachokera (mwachitsanzo, B3 kwa ife) ndi mawonekedwe a maselo a gawo loyamba (F3: F19) a tebulo ndi osiyana (mwachitsanzo, manambala ndi malemba. ). Izi zimachitika makamaka mukamagwiritsa ntchito manambala (manambala aakaunti, zozindikiritsa, madeti, ndi zina zotero) m'malo mwa mayina. Pankhaniyi, mungagwiritse ntchito ntchito Ч и TEXT kutembenuza mafayilo a data. Idzawoneka motere:
=VLOOKUP(TEXT(B3),mtengo,0)
Mutha kuwerenga zambiri za izi apa.
- Ntchitoyi siyingapeze mtengo wofunikira chifukwa code ili ndi malo kapena zilembo zosasindikizidwa zosasindikizidwa (kupuma kwa mzere, etc.). Pankhaniyi, mutha kugwiritsa ntchito zolemba TRIM (TRIM) и Sindikizani(CLEAN) kuwachotsa:
=VLOOKUP(TRIMSPACES(CLEAN(B3)),mtengo,0)
=VLOOKUP(TRIM(CLEAN(B3)));mtengo;0)
Kuletsa uthenga wolakwika # N / A (#N / A) muzochitika zomwe ntchitoyi singapeze kufanana kwenikweni, mungagwiritse ntchito ntchitoyi IFERROR (IFERROR)… Chifukwa chake, mwachitsanzo, chomangachi chimasokoneza zolakwika zilizonse zopangidwa ndi VLOOKUP ndikusintha ndi ziro:
= IFERROR (VLOOKUP (B3, mtengo, 2, 0), 0)
= IFERROR (VLOOKUP (B3; mtengo; 2; 0); 0)
PS
Ngati simukufuna kuchotsa mtengo umodzi, koma seti yonse nthawi imodzi (ngati pali zingapo zosiyana), ndiye kuti muyenera kuchititsa shamanize ndi ndondomekoyi. kapena gwiritsani ntchito mawonekedwe atsopano a XLOOKUP kuchokera ku Office 365.
- Mtundu wowongoleredwa wa ntchito ya VLOOKUP (VLOOKUP 2).
- Kuwerengera mwachangu masitepe (mitundu) kuchotsera pogwiritsa ntchito VLOOKUP.
- Momwe mungapangire "VLOOKUP yakumanzere" pogwiritsa ntchito INDEX ndi MATCH
- Momwe mungagwiritsire ntchito ntchito ya VLOOKUP kuti mudzaze mafomu ndi deta kuchokera pamndandanda
- Momwe mungatulutsire osati woyamba, koma zikhalidwe zonse patebulo nthawi imodzi
- VLOOKUP2 ndi VLOOKUP3 zimagwira ntchito kuchokera pazowonjezera za PLEX