Re na le lethathamo la litaelo tse nang le linomoro le mabitso a thepa. Nka rata, ka mohlala, ho ntša tafoleng ka nomoro ea taelo ea thepa eohle e kenyelletsoeng ho eona. Ho feta kapa ka tlase joalo ka sena:
Tšobotsi e makatsang VLOOKUP (VLOOKUP) maemong a joalo e tla thusa hanyane, hobane e khona ho ntša data feela ka papali ea pele e fumanoeng, ke hore, e tla re fa feela. Liapole. Ho fumana le ho ntša lintho tsohle tafoleng, ho molemo ho sebelisa foromo e hlophisitsoeng. Joalo ka ena:
=INDEX($B$2:$B$16;LEHLOHONOLO(IF($2$=A2:A16;LINE(B2:B16)-1;»);LINE()-5))
E tlameha ho kenngoa ka tsela e latelang:
- khetha lisele moo liphetho li lokelang ho bontšoa (mohlala oa rona, ona ke mofuta oa D6: D20)
- kenya (kopitsa foromo ka har'a sele ea pele) ea mefuta
- ebe o tobetsa konopo Ctrl + battlefield + kena
Ho tlosoa ha yuniti ka sekhechana LETALA(B2:B16)-1 e etsoa ka lebaka la hlooho ea tafole. Ka lebaka le tšoanang, ho lefella ho fetoha ha letoto la sephetho le amanang le la pele, palo ea bohlano sekhechanang e tlosoa. STRING()-5
Ho pata #NUM! phoso e tla hlaha liseleng tse se nang letho mofuteng o hlahisoang oa D6: D20, o ka sebelisa ts'ebetso ea ho hlahloba liphoso IF le EOSH, ho nkela foromo ea rona ka e rarahaneng haholoanyane:
=HAEBA(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)))
Ho Excel 2007, ho ile ha hlaha tšebetso e bonolo haholoanyane ea IFERROR - e u lumella ho rarolla bothata ka mokhoa o bonolo haholoanyane:
=HOITSE(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
Phetolelong ea Senyesemane ea Excel, mesebetsi ena e tla shebahala tjena:
=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)),»»)
- Ho sebelisa Mosebetsi oa VLOOKUP ho Fumana Lintlha ka Lethathamong
- Mofuta o ntlafalitsoeng oa tšebetso ea VLOOKUP2 e ka batlisisang kholomong efe kapa efe eseng boleng ba pele feela
- Mesebetsi ea VLOOKUP2 le VLOOKUP3 ho tsoa ho tlatsetso ea PLEX
- Li-array formula ke eng, 'me li sebelisetsoa eng?