VLOOKUP e ka sebelisoang hape (VLOOKUP)

Tse ka Hare

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:

  1. khetha lisele moo liphetho li lokelang ho bontšoa (mohlala oa rona, ona ke mofuta oa D6: D20)
  2. kenya (kopitsa foromo ka har'a sele ea pele) ea mefuta
  3. 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?

 

Leave a Reply