Шматразовы ВПР (VLOOKUP)

змест

У нас ёсць спіс заказаў з нумарамі і назвамі тавараў. Хацелася б, напрыклад, выцягнуць са стала па нумары замовы ўсе тавары, якія ў яго ўваходзяць. Больш-менш так:

 

Цудоўная функцыя VLOOKUP (ВПР) у такой сітуацыі гэта дапаможа толькі часткова, таму што можа здабываць дадзеныя толькі па першым знойдзеным супадзенні, г.зн. дасць нам толькі яблыкі. Каб знайсці і атрымаць усе элементы з табліцы, лепш выкарыстоўваць формулу масіва. Як гэты:

=індэкс($B$2:$B$16;МЕНШ(IF($E$2=A2: A16;ЛІНІЯ(В2: В16)-1;»»);ЛІНІЯ()-5))

Яго трэба ўвесці наступным чынам:

  1. абярыце ячэйкі, у якіх павінны быць адлюстраваны вынікі (у нашым прыкладзе гэта дыяпазон D6:D20)
  2. увядзіце (скапіруйце формулу ў першую ячэйку) дыяпазону
  3. press Ctrl + Зрух + Уводзіць

Адзінкавае адніманне ў фрагменце РАДОК(B2:B16)-1 робіцца з-за загалоўка табліцы. Па гэтай жа прычыне для кампенсацыі зруху атрыманага дыяпазону адносна зыходнага ва фрагменце адымаецца лічба пяць STRING()-5

Каб схаваць #NUM! памылку, якая будзе з'яўляцца ў пустых вочках у атрыманым дыяпазоне D6:D20, вы можаце выкарыстоўваць функцыі праверкі памылак IF і EOSH, замяніўшы нашу формулу крыху больш складанай:

=КАЛІ(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)))

У Excel 2007 з'явілася больш зручная функцыя IFERROR - яна дазваляе больш кампактна вырашыць задачу:

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

PS

У англійскай версіі Excel гэтыя функцыі будуць выглядаць наступным чынам:

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

  • Выкарыстанне функцыі VLOOKUP для пошуку даных у табліцы
  • Палепшаная версія функцыі VLOOKUP2, якая можа шукаць у любым слупку, а не толькі па першым значэнні
  • Функцыі VLOOKUP2 і VLOOKUP3 з надбудовы PLEX
  • Што такое формулы масіваў і для чаго яны выкарыстоўваюцца?

 

Пакінуць каментар