Okuqukethwe
Lesi sifundo esincane sichaza indlela yokwenza umsebenzi VPR (I-VLOOKUP) i-case-sensitive, ibonisa amanye amafomula ambalwa i-Excel engakwazi ukuwasesha ngendlela ezwelayo, futhi ikhomba amandla nobuthaka bomsebenzi ngamunye.
Ngicabanga ukuthi wonke umsebenzisi we-Excel uyazi ukuthi yimuphi umsebenzi owenza ukusesha okuqondile. Kunjalo, kungumsebenzi VPR. Nokho, bambalwa abantu abakwaziyo lokho VPR ayizwela kakhulu, okungukuthi izinhlamvu ezincane nezinkulu ziyafana kuyo.
Nasi isibonelo esisheshayo esibonisa ukungakwazi VPR bona irejista. Ake sithi kuseli A1 iqukethe inani "ibhili" kanye neseli A2 – “Bill”, ifomula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… izomisa ukusesha kwayo “kubhili” njengoba lelo nani liza kuqala ohlwini, futhi likhiphe inani kuseli B1.
Kamuva kulesi sihloko, ngizokukhombisa ukuthi kwenziwa kanjani VPR iyazwela kofeleba. Ngaphezu kwalokho, sizofunda eminye imisebenzi embalwa engenza usesho oluzwelayo ku-Excel.
Sizoqala ngokulula kakhulu - VIEW (LOOKUP) kanye SUMPRODUCT (SUMPRODUCT), okuthi, ngeshwa, ibe nemikhawulo embalwa ebalulekile. Okulandelayo, sizobhekisisa ifomula eyinkimbinkimbi kakhudlwana INDEX+MATCH (INDEX+MATCH), esebenza ngokungenasici kunoma yisiphi isimo nanganoma iyiphi idathasethi.
Umsebenzi we-VLOOKUP ubucayi kakhulu
Njengoba wazi kakade, umsebenzi ojwayelekile VPR alizweli. Nokho, ikhona indlela yokwenza kube bucayi. Ukuze wenze lokhu, udinga ukwengeza ikholomu eyisizayo etafuleni, njengoba kuboniswe esibonelweni esilandelayo.
Ake sithi kukholamu B kukhona izihlonzi zomkhiqizo (Into) futhi ufuna ukukhipha intengo yomkhiqizo kanye namazwana ahambisanayo kumakholomu C и D. Inkinga ukuthi izihlonzi ziqukethe kokubili izinhlamvu ezincane kanye nezinhlamvu ezinkulu. Isibonelo, amanani weseli B4 (001Tvci3u) kanye B5 (001Tvci3U) ziyehluka kuphela esimweni somlingisi wokugcina, u и U ngokulandelana.
Njengoba ungacabanga, ifomula yokusesha evamile
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
uzobuya $ 90, kusukela inani 001Tvci3u isendaweni yokusesha ngaphambi kwesikhathi 001Tvci3U. Kodwa akukhona lokho esikudingayo, akunjalo?
Ukusesha ngomsebenzi VPR ku-Excel case sensitive, kuzodingeka wengeze ikholomu yomsizi bese ugcwalisa amaseli ayo ngefomula elandelayo (lapho B kuyikholomu yokubheka):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Le fomula yephula inani elifiswayo libe izinhlamvu ezihlukene, imiselele uhlamvu ngalunye ngekhodi yalo (isibonelo, esikhundleni sokuthi A ngo-65, kunalokho a ikhodi 97) bese ihlanganisa lawa makhodi abe yiyunithi yezinhlamvu eyingqayizivele yezinombolo.
Ngemva kwalokho, sisebenzisa umsebenzi olula VPR ngokusesha okubucayi kwecala:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Ukusebenza kahle komsebenzi VPR ukuzwela kwecala kuncike ezintweni ezimbili:
- Ikholomu yomsizi kumelwe ibe ikholomu kwesokunxele kakhulu ebangeni elibukwayo.
- Inani olifunayo kufanele libe nekhodi yezinhlamvu esikhundleni senani langempela.
Ungawusebenzisa kanjani umsebenzi we-CODE ngendlela efanele
Ifomula efakwe kumaseli wekholomu eyisiza ithatha ukuthi wonke amanani akho okusesha anenombolo efanayo yezinhlamvu. Uma kungenjalo, udinga ukwazi izinombolo ezincane nezinkulu futhi wengeze izici eziningi IFERROR (IFERROR) zingaki izinhlamvu eziwumehluko phakathi kwevelu elifushane nelide kakhulu eliseshiwe.
Isibonelo, uma inani lokusesha elifushane kakhulu linezinhlamvu ezi-3 futhi elide kunawo wonke linezinhlamvu ezi-5, sebenzisa le fomula:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Okokusebenza PSTR (MID) Unikeza izimpikiswano ezilandelayo:
- Impikiswano yokuqala - umbhalo (umbhalo) umbhalo noma ireferensi yeseli equkethe izinhlamvu ezizokhishwa (kithina ithi B2)
- Impikiswano yokuqala - inombolo_yokuqala (start_position) indawo yokuqala yalezo zinhlamvu ezizokhishwa. uyangena 1 emsebenzini wokuqala PSTR, 2 – kumsebenzi wesibili PSTR njll
- Impikiswano yokuqala - inombolo_yezinhlamvu (inombolo_yezinhlamvu) - Icacisa inani lezinhlamvu ezizokhishwa embhalweni. Njengoba sidinga uhlamvu olu-1 kuphela ngaso sonke isikhathi, kuyo yonke imisebenzi esiyibhalayo 1.
IZINDLELA: Umsebenzi VPR akusona isixazululo esingcono kakhulu sosesho olubucayi ku-Excel. Okokuqala, ukungezwa kwekholomu eyisiza kuyadingeka. Okwesibili, ifomula yenza umsebenzi omuhle kuphela uma idatha i-homogeneous, noma inani eliqondile lezinhlamvu kumanani aseshwayo laziwa. Uma lokhu kungelona icala lakho, kungcono ukusebenzisa esinye sezixazululo esisibonisa ngezansi.
Umsebenzi we-LOOKUP wokusesha okubucayi kwekesi
Umsebenzi VIEW (LOOKUP) okuhlobene VPR, nokho i-syntax yayo ivumela ukusesha okungathi sína ngaphandle kokwengeza ikholomu eyinsiza. Ukuze wenze lokhu, sebenzisa VIEW kuhlanganiswe nomsebenzi ISEXOLO (QINISO).
Uma sithatha idatha esibonelweni sangaphambilini (ngaphandle kwekholomu eyisiza), ifomula elandelayo izobhekana nomsebenzi:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Usesho lwefomula ebangeni A2:A7 ukufana ncamashi nenani leseli F2 i-case sensitive futhi ibuyisela inani elisuka kukholomu B yomugqa ofanayo.
Like VPRumsebenzi VIEW isebenza ngokulinganayo ngombhalo namanani ezinombolo, njengoba ubona esithombeni esingezansi:
Kubalulekile! Ukuze umsebenzi VIEW esebenza kahle, amanani kukholamu yokubheka kufanele ahlungwe ngendlela ekhuphukayo, okungukuthi kusukela kwencane kakhulu kuye kwenkulu.
Ake ngichaze kafushane ukuthi umsebenzi usebenza kanjani ISEXOLO kufomula ekhonjiswe ngenhla, njengoba leli kuyiphuzu elibalulekile.
Umsebenzi ISEXOLO iqhathanisa amanani ombhalo amabili kuma-agumenti 1st and 2nd bese ibuyisela TRUE uma ifana ncamashi, noma FALSE uma ingafani. Kubalulekile kithi ukuthi umsebenzi ISEXOLO iyazwela kofeleba.
Ake sibone ukuthi ifomula yethu isebenza kanjani BUKA+NGQONDO:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Umsebenzi ISEXOLO iqhathanisa inani leseli F2 nazo zonke izakhi kukholamu A (A2:A7). Ibuyisela TRUE uma kutholakala okufanayo ngqo, ngaphandle kwalokho FALSE.
- Njengoba unikeza ukuphikisana komsebenzi wokuqala VIEW inani TRUE, ikhipha inani elihambisanayo kukholomu eshiwo (kithi, ikholomu B) kuphela uma kutholakala okufana ncamashí, okuzwela kakhulu.
Ngethemba ukuthi le ncazelo yayicacile futhi manje niyawuqonda umqondo oyinhloko. Uma kunjalo, ngeke ube nobunzima neminye imisebenzi esizoyihlaziya ngokuqhubekayo, ngoba. bonke basebenza ngesimiso esifanayo.
IZINDLELA: Idatha kukholomu yokubheka kufanele ihlungwe ngohlelo olukhuphukayo.
I-SUMPRODUCT – ithola amanani ombhalo, i-case sensitive, kodwa ibuyisela izinombolo kuphela
Njengoba usuvele uqondisise esihlokweni, SUMPRODUCT (I-SUMPRODUCT) ingomunye umsebenzi we-Excel ozokusiza ukuthi wenze usesho oluzwelayo, kodwa uzobuyisela amanani ezinombolo kuphela. Uma le nketho ingahambisani nawe, khona-ke ungakwazi ukuqhubekela phambili ngokushesha ku-bundle INDEX+MATCH, enikeza isixazululo sanoma isiphi isimo nanoma yiziphi izinhlobo zedatha.
Okokuqala, ake ngichaze kafushane i-syntax yalo msebenzi, lokhu kuzokusiza ukuthi uqonde kangcono ifomula elandelayo ezwelayo.
Umsebenzi SUMPRODUCT iphindaphinda izakhi zamalungu afanayo anikeziwe futhi ibuyisela isamba semiphumela. I-syntax ibonakala kanje:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Njengoba sidinga usesho olubucayi, sisebenzisa umsebenzi ISEXOLO (EXACT) kusukela kusibonelo sangaphambilini njengesiphindaphinda:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Njengoba ukhumbula, ISEXOLO iqhathanisa inani leseli F2 nazo zonke izakhi kukholamu A. Ibuyisela TRUE uma kutholakala okufanayo ngqo, ngaphandle kwalokho FALSE. Emisebenzini yezibalo, i-Excel ithatha TRUE njenge 1, kanye nokuthi AMANGA we 0Ngaphezu kwalokho SUMPRODUCT iphindaphinda lezi zinombolo bese ihlanganisa imiphumela.
Amaqanda awabalwa ngoba uma ephindaphindeka anikela njalo 0. Ake sibhekisise ukuthi kwenzekani uma okufanayo kukholamu A wathola wabuya 1… Umsebenzi SUMPRODUCT iphindaphinda inombolo kukholamu B on 1 futhi ibuyisela umphumela - inombolo efanayo ncamashi! Lokhu kungenxa yokuthi imiphumela yeminye imikhiqizo inguziro, futhi ayithinti isamba esiwumphumela.
Ngeshwa umsebenzi SUMPRODUCT ayikwazi ukusebenza ngamavelu ombhalo namadethi njengoba engakwazi ukuphindaphindeka. Kulokhu, uzothola umlayezo wephutha #INANI! (#INANI!) njengakuseli F4 esithombeni esingezansi:
IZINDLELA: Ibuyisela amanani ezinombolo kuphela.
INDEX + MATCH – usesho oluzwela kakhulu kunoma yiluphi uhlobo lwedatha
Okokugcina, siseduze nefomula yosesho engenamkhawulo futhi ebucayi esebenza nanoma iyiphi isethi yedatha.
Lesi sibonelo sifika ekugcineni, hhayi ngoba okuhamba phambili kushiyelwa uphudingi, kodwa ngoba ulwazi olutholwe ezibonelweni zangaphambilini luzokusiza ukuthi uqonde ifomula ebucayi kangcono futhi ngokushesha. INDEX+MATCH (INDEX+IMESHI).
Njengoba cishe uqagele, inhlanganisela imisebenzi OKUNINGI KUDANWE и INDEX esetshenziswa ku-Excel njengendlela evumelana nezimo futhi enamandla VPR. Isihloko esithi Ukusebenzisa i-INDEX kanye ne-MATCH esikhundleni se-VLOOKUP sizochaza kahle ukuthi le misebenzi isebenza kanjani ndawonye.
Ngizophinda ngiphinde amaphuzu abalulekile:
- Umsebenzi OKUNINGI KUDANWE (MATCH) icinga inani kububanzi obunikeziwe futhi ibuyisela indawo yayo ehlobene, okungukuthi, umugqa kanye/noma inombolo yekholomu;
- Okulandelayo, umsebenzi INDEX (INDEX) ibuyisela inani elisuka kukholomu ethile kanye/noma umugqa.
Ukuze ifomula INDEX+MATCH ingasesha ngendlela ebucayi, udinga kuphela ukwengeza umsebenzi owodwa kuyo. Akunzima ukuqagela ukuthi yini futhi ISEXOLO (QINISO):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Kule fomula ISEXOLO isebenza ngendlela efanayo njengoba ihlangene nomsebenzi VIEW, futhi inikeza umphumela ofanayo:
Qaphela ukuthi ifomula INDEX+MATCH evalwe ngezikaki ezigoqekile iyifomula yamalungu afanayo futhi kufanele uyigcwalise ngokucindezela Ctrl + Shift + Faka.
Kungani i-INDEX+MATCH iyisixazululo esingcono kakhulu sosesho olubucayi?
Izinzuzo eziyinhloko ze-bundle INDEX и OKUNINGI KUDANWE:
- Akudingi ukungeza ikholomu eyisiza, ngokungafani VPR.
- Ayidingi ikholomu yosesho ukuthi ihlungwe, ngokungafaniyo VIEW.
- Isebenza nazo zonke izinhlobo zedatha - izinombolo, umbhalo namadethi.
Le fomula ibonakala iphelele, akunjalo? Empeleni, akunjalo. Futhi yingakho.
Cabanga ukuthi iseli kukholomu yenani lokubuyisela elihlotshaniswa nenani lokubheka alinalutho. Ifomula izobuya muphi umphumela? Cha? Ake sibone ukuthi ifomula ibuyisela ini ngempela:
Eshu, ifomula ibuyisela uziro! Lokhu kungase kungabi inkinga enkulu uma usebenza ngamavelu ombhalo amsulwa. Kodwa-ke, uma ithebula liqukethe izinombolo, kuhlanganise noziro "wangempela", lokhu kuba yinkinga.
Eqinisweni, wonke amanye amafomula okubheka (i-VLOOKUP, i-LOOKUP, ne-SUMPRODUCT) esixoxile ngayo ngaphambili aziphatha ngendlela efanayo. Kodwa ufuna ifomula ephelele, akunjalo?
Ukwenza ikesi lefomula lizwele INDEX+MATCH ephelele, ibeke kuhlelo lokusebenza IF (IF) ezohlola iseli ngenani lokubuyisela bese ibuyisela umphumela ongenalutho uma ingenalutho:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Kule fomula:
- B iyikholomu enamanani okubuyisela
- 1+ iyinombolo ephendula indawo ehlobene yeseli elibuyiswe umsebenzi OKUNINGI KUDANWE, ekhelini langempela leseli. Ngokwesibonelo, emsebenzini wethu OKUNINGI KUDANWE uhlu lokucinga lunikiwe A2:A7, okungukuthi, ukuma okuhlobene kweseli A2 kuthanda 1, ngoba ingeyokuqala ohlwini. Kodwa isikhundla sangempela seseli A2 kukholamu i 2, ngakho sengeza 1ukwenza umehluko nokuba nomsebenzi INDALO (INDIRECT) ibuyise inani kuseli oyifunayo.
Izithombe ezingezansi zibonisa ifomula elungisiwe ezwelayo INDEX+MATCH Ngesenzo. Ibuyisela umphumela ongenalutho uma iseli elibuyisiwe lingenalutho.
Ngibhale kabusha ifomula kumakholomu B:Dukuze ulingane ibha yefomula kusithombe-skrini.
Ukubuyiselwa kwefomula 0uma iseli elibuyisiwe liqukethe uziro.
Uma ufuna isixhumanisi INDEX и OKUNINGI KUDANWE ibonise umlayezo othile lapho inani lokubuyisela lingenalutho, ungabhala kumacaphuno okugcina (“”) efomula, isibonelo, kanje:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
