VLOOKUP mai sake amfani da shi (VLOOKUP)

Contents

Muna da jerin oda tare da lambobi da sunayen kaya. Ina so, alal misali, cire daga tebur ta hanyar oda duk kayan da aka haɗa a ciki. Sama ko ƙasa da haka kamar haka:

 

Siffar ban mamaki VLOOKUP (VLOOKUP) a cikin irin wannan yanayin zai taimaka kawai a wani bangare, saboda yana iya fitar da bayanai kawai ta hanyar wasan farko da aka samo, watau zai ba mu kawai. apples. Don nemo da cire duk abubuwa daga tebur, yana da kyau a yi amfani da dabarar tsararru. Kamar wannan:

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

Dole ne a shigar da shi kamar haka:

  1. zaɓi sel inda ya kamata a nuna sakamakon (a cikin misalinmu, wannan shine kewayon D6:D20)
  2. shigar (kwafi dabarar cikin tantanin halitta na farko) na kewayon
  3. latsa Ctrl + Motsi + Shigar

Rage raka'a a guntu STRING (B2:B16)-1 ana yinsa ne saboda taken tebur. Saboda wannan dalili, don rama canjin canji a cikin kewayon da aka samu dangane da na asali, an cire lamba biyar a cikin guntu. STRING()-5

Don ɓoye #NUM! Kuskuren da zai bayyana a cikin sel maras komai a cikin kewayon sakamakon D6: D20, zaku iya amfani da ayyukan bincika kuskuren IF da EOSH, maye gurbin tsarin mu tare da ɗanɗano mai rikitarwa:

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

A cikin Excel 2007, aikin IFEROR mafi dacewa ya bayyana - yana ba ku damar magance matsalar cikin ƙaramin ƙarfi:

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

PS

A cikin Ingilishi na Excel, waɗannan ayyuka za su yi kama da haka:

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

  • Amfani da Ayyukan VLOOKUP don Neman Bayanai a cikin Tebur
  • Ingantattun sigar aikin VLOOKUP2 wanda zai iya bincika kowane shafi ba kawai ƙimar farko ba
  • Ayyukan VLOOKUP2 da VLOOKUP3 daga PLEX add-on
  • Menene tsarin tsararru kuma menene amfani da su?

 

Leave a Reply