Dib loo isticmaali karo VLOOKUP (VLOOKUP)

Contents

Waxaan haynaa liis dalabaad ah oo wata lambaro iyo magacyada alaabta. Waxaan jeclaan lahaa, tusaale ahaan, in aan miiska kala soo baxo amarka nambarka dhammaan alaabta ku jirta. Ka badan ama ka yar sidan:

 

Muuqaal cajiib ah VLOOKUP (VLOOKUP) Xaaladdan oo kale waxay qayb ahaan caawin doontaa, sababtoo ah waxay awood u leedahay inay soo saarto xogta kaliya ciyaarta ugu horeysa ee la helo, ie kaliya waxay ina siin doontaa tufaax. Si aad u heshid oo aad uga soo saarto dhammaan alaabta miiska, waxa fiican in aad isticmaasho hab habaysan. Midkan oo kale:

=INDEX($B$2:$B$16;HADDII(IF($ E $ 2=J2: A16;LINE(B2:B16-1;»);LINE(-5))

Waa in loo geliyaa sida soo socota:

  1. dooro unugyada meesha natiijadu ay tahay in lagu soo bandhigo (tusaale ahaan, tani waa xadka D6:D20)
  2. geli (ku koobi caanaha caanaha unugga koowaad) ee kala duwanaanshaha
  3. -saxaafadeed Ctrl + shift + Ku qor

Kala goynta cutubka oo jajab ah STRING (B2:B16)-1 waxaa loo sameeyaa sababtoo ah madaxa miiska. Isla sababtaas awgeed, si loo magdhabo beddelka tirada ka dhalatay marka loo eego kii asalka ahaa, lambarka shanaad ee jajabka waa la jaray. STRING()-5

Si loo qariyo #NUM! qalad ka soo bixi doona unug madhan oo ka soo baxay D6:D20, waxaad isticmaali kartaa hawlaha hubinta qaladka IF iyo EOSH, adigoo ku beddelaya qaacidadayada mid ka sii adag:

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

In Excel 2007, shaqo IFERROR oo ku habboon ayaa soo muuqatay - waxay kuu oggolaaneysaa inaad si kooban u xalliso dhibaatada:

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

PS

Nooca Ingiriisiga ee Excel, shaqooyinkani waxay u ekaan doonaan sidan:

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

  • Isticmaalka Shaqada VLOOKUP si aad u hesho Xogta Shaxda
  • Nooca la hagaajiyay ee shaqada VLOOKUP2 kaas oo ka raadin kara tiir kasta oo aan ahayn kaliya qiimaha koowaad
  • VLOOKUP2 iyo VLOOKUP3 waxay ka shaqeeyaan PLEX add-on
  • Waa maxay qaacidooyinka diyaarsan maxaase loo isticmaalaa?

 

Leave a Reply