Hoʻohana hou ʻia VLOOKUP (VLOOKUP)

Contents

Loaʻa iā mākou kahi papa inoa o nā kauoha me nā helu a me nā inoa o nā waiwai. Makemake au, no ka laʻana, e huki mai ka papaʻaina ma ka helu kauoha i nā waiwai a pau i hoʻokomo ʻia i loko. ʻOi aku a emi mai paha e like me kēia:

 

Hiʻona kupanaha VLOOKUP (VLOOKUP) i loko o ia kūlana e kōkua hapa wale ia, no ka mea hiki ke unuhi i ka ʻikepili wale nō e ka pāʻani mua i loaʻa, ʻo ia hoʻi e hāʻawi wale iā mākou. Mele. No ka huli ʻana a unuhi i nā mea a pau mai ka papaʻaina, ʻoi aku ka maikaʻi o ka hoʻohana ʻana i kahi ʻano hana. E like me kēia:

=INDEX($B$2:$B$16;KA MEA KOA(IF($E$2=A2:A16;LINE(B2:B16)-1;»»);LINE()-5))

Pono e hookomo penei:

  1. koho i nā pūnaewele kahi e hōʻike ʻia ai nā hopena (i kā mākou laʻana, ʻo ia ka laulā D6:D20)
  2. komo (kope i ke ʻano i loko o ka cell mua) o ka laula
  3. kaomi Ctrl + Shift + komo

Unuhi ʻāpana ma ka ʻāpana STRING(B2:B16)-1 hana ʻia ma muli o ke poʻo papaʻaina. No ke kumu hoʻokahi, no ka uku ʻana i ka neʻe ʻana i ka pae hopena e pili ana i ka mea mua, e unuhi ʻia ka helu ʻelima o ka ʻāpana. STRING()-5

E hūnā i ka #NUM! ka hewa e ʻike ʻia i loko o nā pūnaewele ʻole i ka pae hopena D6:D20, hiki iā ʻoe ke hoʻohana i nā hana nānā hewa IF a me EOSH, e hoʻololi ana i kā mākou kumu me kahi mea paʻakikī iki.

=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)))

Ma Excel 2007, ua ʻike ʻia kahi hana IFERROR ʻoi aku ka maʻalahi - hiki iā ʻoe ke hoʻoponopono i ka pilikia me ka maʻalahi:

=IFERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

Ma ka ʻōlelo Pelekania o Excel, e like kēia mau hana:

=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)),»»)

  • Ke hoʻohana nei i ka hana VLOOKUP no ka ʻimi ʻana i ka ʻikepili ma kahi papa
  • ʻO kahi mana i hoʻomaikaʻi ʻia o ka hana VLOOKUP2 hiki ke ʻimi i kekahi kolamu a ʻaʻole i ka waiwai mua wale nō
  • VLOOKUP2 a me VLOOKUP3 hana mai ka PLEX add-on
  • He aha nā ʻōnaehana array a no ke aha lākou e hoʻohana ai?

 

Waiho i ka Reply