Sinohlu lwama-oda anezinombolo namagama ezimpahla. Ngingathanda, isibonelo, ukukhipha etafuleni ngenombolo ye-oda zonke izimpahla ezifakwe kuyo. Okuningi noma okuncane okufana nalokhu:
Isici esimangalisayo I-VLOOKUP (VLOOKUP) esimweni esinjalo kuzosiza kancane, ngoba ikwazi ukukhipha idatha kuphela ngokufanisa kokuqala okutholiwe, okungukuthi izosinika kuphela Apples. Ukuze uthole futhi ukhiphe zonke izinto etafuleni, kungcono ukusebenzisa ifomula yohlu. Njengalena:
=INDEX($B$2:$B$16;OKUPHELELE(IF($E$2=A2:A16;LINE(B2:B16)-1;»»);LINE()-5))
Kumele ifakwe kanje:
- khetha amaseli lapho imiphumela kufanele iboniswe khona (esibonelweni sethu, lolu wububanzi D6:D20)
- faka (kopisha ifomula kuseli lokuqala) lobubanzi
- Cindezela Ctrl + Shift + Faka
Ukukhipha iyunithi kucezu STRING(B2:B16)-1 kwenziwa ngenxa yesihloko setafula. Ngesizathu esifanayo, ukuze kunxeshezelwe ukushintsha kobubanzi obuwumphumela ngokuhlobene nowokuqala, inombolo yesihlanu esiqeshini iyasuswa. STRING()-5
Ukufihla i-#NUM! iphutha elizovela kumaseli angenalutho ebangeni eliwumphumela we-D6:D20, ungasebenzisa iphutha ukuhlola imisebenzi ethi IF kanye ne-EOSH, esikhundleni sefomula yethu ngeyinkimbinkimbi kakhudlwana:
=IF(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
Ku-Excel 2007, kwavela umsebenzi we-IFERROR olula kakhulu - ikuvumela ukuthi uxazulule inkinga ngobumbene:
=I-IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
Enguqulweni yesiNgisi ye-Excel, le misebenzi izobukeka kanje:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- Ukusebenzisa Umsebenzi we-VLOOKUP ukuthola Idatha Kuthebula
- Inguqulo ethuthukisiwe yomsebenzi we-VLOOKUP2 engasesha kunoma iyiphi ikholomu hhayi nje inani lokuqala
- I-VLOOKUP2 kanye nemisebenzi ye-VLOOKUP3 ivela kusengezo se-PLEX
- Ayini amafomula afanayo futhi asetshenziselwa ini?