VLOOKUP yogwiritsidwanso ntchito (VLOOKUP)

Zamkatimu

Tili ndi mndandanda wamaoda okhala ndi manambala ndi mayina a katundu. Ndikufuna, mwachitsanzo, kuti nditulutse patebulo potengera nambala yazinthu zonse zomwe zikuphatikizidwamo. Zambiri kapena zochepa monga chonchi:

 

Zodabwitsa mawonekedwe VLOOKUP (VLOOKUP) Zikatero zidzangothandiza pang'ono, chifukwa zimatha kuchotsa deta pongopeza machesi oyamba, ndiye kuti adzatipatsa. Maapulo. Kuti mupeze ndi kuchotsa zinthu zonse patebulo, ndi bwino kugwiritsa ntchito njira yotsatizana. Monga ichi:

=INDEX($B$2:$B$16;KAPENA(IF($E$2=A2:A16;LINE(B2:b16-1;»);LINE()-5))

Iyenera kulembedwa motere:

  1. sankhani ma cell omwe zotsatira zake ziyenera kuwonetsedwa (mu chitsanzo chathu, iyi ndi mitundu ya D6: D20)
  2. lowetsani (koperani fomula mu selo loyamba) la mndandanda
  3. Sindikizani Ctrl + kosangalatsa + Lowani

Kuchotsa ma unit mu fragment MTIMA(B2:B16)-1 zimachitika chifukwa cha mutu wa tebulo. Pachifukwa chomwechi, kuti apereke chindapusa cha kusintha kwa mtundu wotsatira wokhudzana ndi woyambayo, nambala yachisanu pachidutswacho imachotsedwa. STRING()-5

Kubisa #NUM! cholakwika chomwe chidzawonekere m'maselo opanda kanthu mumtundu wotsatira D6: D20, mungagwiritse ntchito zolakwika zofufuza ntchito IF ndi EOSH, m'malo mwa formula yathu ndi yovuta kwambiri:

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

Mu Excel 2007, ntchito yabwino kwambiri ya IFERROR idawonekera - imakupatsani mwayi wothana ndi vutoli mwachangu:

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

PS

Mu mtundu wa Chingerezi wa Excel, ntchito izi ziziwoneka motere:

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

  • Kugwiritsa Ntchito Ntchito ya VLOOKUP Kupeza Zambiri patebulo
  • Mtundu wowongoleredwa wa VLOOKUP2 womwe ungafufuze mugawo lililonse osati mtengo woyamba
  • VLOOKUP2 ndi VLOOKUP3 zimagwira ntchito kuchokera pazowonjezera za PLEX
  • Kodi ma formula ndi chiyani ndipo amagwiritsidwa ntchito bwanji?

 

Siyani Mumakonda