Ubani ovilapha kakhulu noma ongenaso isikhathi sokufunda - bukela ividiyo. Imininingwane nama-nuances akumbhalo ongezansi.
Ukwakhiwa kwenkinga
Ngakho, sinamatafula amabili - ithebula le-oda и uhlu lwamanani:
Umsebenzi uwukushintsha amanani ohlwini lwamanani uwafake etafuleni lama-oda ngokuzenzakalelayo, ugxile egameni lomkhiqizo ukuze kamuva ukwazi ukubala izindleko.
Isixazululo
Kusethi yomsebenzi we-Excel, ngaphansi kwesigaba Izinkomba kanye nezinhla (Ukubheka nokubhekisela) kukhona umsebenzi VPR (VLOOKUP).Lo msebenzi ubheka inani elinikeziwe (esibonelweni sethu, leli yigama elithi “ama-apula”) kukholamu kwesokunxele yethebula elishiwo (uhlu lwamanani) ukusuka phezulu kuye phansi, futhi, lapho selitholile, libonisa okuqukethwe kweseli eliseduze. (23 ruble) .Ngokwezinhlelo, ukusebenza kwalo msebenzi kungamelwa Ngakho:
Ukuze kube lula ukusetshenziswa okwengeziwe komsebenzi, yenza into eyodwa ngesikhathi esisodwa - nikeza uhla lwamaseli ohlwini lwamanani igama lakho siqu. Ukuze wenze lokhu, khetha wonke amaseli ohlu lwamanani ngaphandle “kwesihloko” (G3: H19), khetha kumenyu. Faka – Igama – Yabela (Faka - Igama - Chaza) noma cindezela CTRL + F3 bese ufaka noma yiliphi igama (akukho zikhala) njenge Inani… Manje, esikhathini esizayo, ungasebenzisa leli gama ukuze uxhume ohlwini lwamanani.
Manje sisebenzisa umsebenzi VPR… Khetha iseli lapho izofakwa khona (D3) bese uvula ithebhu Amafomula – Ukufakwa Komsebenzi (Amafomula — Faka Umsebenzi)… Esigabeni Izinkomba kanye nezinhla (Bheka futhi Inkomba) thola umsebenzi VPR (VLOOKUP) bese ucindezela OK… Iwindi lokufaka izimpikiswano zomsebenzi lizovela:
Siwagcwalisa ngokulandelana:
- Inani elifunekayo (Ivelu yokubheka) – igama lomkhiqizo umsebenzi okufanele uwuthole kukholamu kwesokunxele sohlu lwamanani. Esimweni sethu, igama elithi "ama-apula" elivela kuseli B3.
- Ithebula (Uhlu Lwethebula) - itafula okuthathwe kulo amanani afiswayo uXNUMXbuXNUMXbare, okungukuthi, uhlu lwethu lwamanani. Njengereferensi, sisebenzisa igama lethu elithi "Intengo" esinikezwe ngaphambili. Uma ungazange unikeze igama, ungavele ukhethe ithebula, kodwa ungakhohlwa ukucindezela inkinobho F4ukuphina isixhumanisi ngezimpawu zamadola, ngoba uma kungenjalo, izoshelela phansi lapho sikopisha ifomula yethu iye phansi kuwo wonke amaseli kukholamu D3:D30.
- Inombolo_yekholomu (Inombolo yenkomba yekholomu) – inombolo ye-serial (hhayi incwadi!) Kukholomu ohlwini lwamanani esizothatha kuwo amanani amanani. Ikholomu yokuqala yohlu lwamanani anamagama inomboro 1, ngakho-ke sidinga inani elisuka kukholamu enomboro 2.
- ukubheka_kwesikhashana (Ukubuka Kwebanga) - amanani amabili kuphela angafakwa kulo mkhakha: AMANGA noma IQINISO:
- Uma inani lifakiwe 0 or AMANGA (AMANGA), khona-ke empeleni lokhu kusho ukuthi ukusesha kuphela kuvunyelwe ukufana ngqo, okungukuthi uma umsebenzi ungayitholi into engajwayelekile eshiwo kuthebula le-oda ohlwini lwamanani (uma u-“Coconut” ufakiwe, isibonelo), uzokhiqiza iphutha elithi #N/A (ayikho idatha).
- Uma inani lifakiwe 1 or TRUE (IQINISO), khona-ke lokhu kusho ukuthi uvumela ukusesha hhayi okuqondile, kodwa isilinganiso sokufana, okungukuthi esimweni "kakhukhunathi", umsebenzi uzozama ukuthola umkhiqizo onegama eliseduze "kakhukhunathi" futhi ubuyisele intengo yaleli gama. Ezimweni eziningi, ukushintshanisa okunjalo okulinganiselwe kungadlala iqhinga kumsebenzisi ngokushintsha inani lomkhiqizo ongalungile obukhona ngempela! Ngakho-ke ezinkingeni eziningi zebhizinisi zangempela, ukusesha okulinganiselwe kungcono kakhulu ukungakuvumeli. Okuhlukile kulapho sifuna izinombolo hhayi umbhalo - isibonelo, lapho sibala Izaphulelo Zesinyathelo.
Konke! Kusele ukucindezela OK bese ukopisha umsebenzi ofakiwe kuyo yonke ikholomu.
# N / A amaphutha kanye nokucindezelwa kwawo
Umsebenzi VPR (VLOOKUP) ibuyisela iphutha elingu-#N/A (#N/A) uma:
- Ukusesha okuqondile kunikwe amandla (impikiswano Ukubuka kwesikhawu = 0) negama olifunayo alikho Ithebula.
- Usesho oluqinile lufakiwe (Ukubuka kwesikhawu = 1), kodwa Ithebula, lapho ukusesha okwenziwa khona akuhlelwanga ngokulandelana okunyukayo kwamagama.
- Ifomethi yeseli lapho inani elidingekayo legama livela khona (isibonelo, B3 kithi) kanye nefomethi yamaseli ekholomu yokuqala (F3: F19) yethebula ihlukile (ngokwesibonelo, izinombolo nombhalo. ). Lesi simo sijwayelekile uma kusetshenziswa amakhodi ezinombolo (izinombolo ze-akhawunti, izihlonzi, amadethi, njll.) esikhundleni samagama ombhalo. Kulokhu, ungasebenzisa imisebenzi Ч и TEXT ukuguqula amafomethi edatha. Kuzobukeka kanjena:
=I-VLOOKUP(TEXT(B3),intengo,0)
Ungafunda kabanzi ngalokhu lapha.
- Umsebenzi awukwazi ukuthola inani elidingekayo ngenxa yokuthi ikhodi iqukethe izikhala noma izinhlamvu ezingabonakali ezingaphrinteki (ukuhlukana kwemigqa, njll.). Kulokhu, ungasebenzisa imisebenzi yombhalo I-TRIM (TRIM) и PHRINTA(HLANZEKILE) ukuwasusa:
=I-VLOOKUP(TRIMSPACES(CLEAN(B3)),intengo,0)
=I-VLOOKUP(TRIM(CLEAN(B3)));inani;0)
Ukucindezela umlayezo wephutha # N / A (#N/A) ezimeni lapho umsebenzi ungakwazi ukuthola okufanayo ncamashi, ungasebenzisa umsebenzi IFERROR (IFERROR)… Ngakho-ke, ngokwesibonelo, lesi sakhiwo sibamba noma yimaphi amaphutha enziwe i-VLOOKUP bese sifaka oziro esikhundleni sawo:
= IFERROR (VLOOKUP (B3, intengo, 2, 0), 0)
= IFERROR (VLOOKUP (B3; inani; 2; 0); 0)
PS
Uma udinga ukukhipha hhayi inani elilodwa, kodwa isethi yonke ngesikhathi esisodwa (uma kukhona amaningana ahlukene), kuzodingeka u-shamanize ngefomula yohlu. noma sebenzisa isici esisha se-XLOOKUP esivela ku-Office 365.
- Inguqulo ethuthukisiwe yomsebenzi we-VLOOKUP (VLOOKUP 2).
- Ukubalwa okusheshayo kwezaphulelo zesinyathelo (ububanzi) kusetshenziswa umsebenzi we-VLOOKUP.
- Uyenza kanjani “i-VLOOKUP engakwesobunxele” usebenzisa imisebenzi ye-INDEX kanye ne-MATCH
- Ungawusebenzisa kanjani umsebenzi we-VLOOKUP ukuze ugcwalise amafomu ngedatha evela ohlwini
- Ungayikhipha kanjani eyokuqala, kodwa wonke amanani asuka etafuleni ngesikhathi esisodwa
- I-VLOOKUP2 kanye nemisebenzi ye-VLOOKUP3 ivela kusengezo se-PLEX