У нас ёсць спіс заказаў з нумарамі і назвамі тавараў. Хацелася б, напрыклад, выцягнуць са стала па нумары замовы ўсе тавары, якія ў яго ўваходзяць. Больш-менш так:
Цудоўная функцыя VLOOKUP (ВПР) у такой сітуацыі гэта дапаможа толькі часткова, таму што можа здабываць дадзеныя толькі па першым знойдзеным супадзенні, г.зн. дасць нам толькі яблыкі. Каб знайсці і атрымаць усе элементы з табліцы, лепш выкарыстоўваць формулу масіва. Як гэты:
=індэкс($B$2:$B$16;МЕНШ(IF($E$2=A2: A16;ЛІНІЯ(В2: В16)-1;»»);ЛІНІЯ()-5))
Яго трэба ўвесці наступным чынам:
- абярыце ячэйкі, у якіх павінны быць адлюстраваны вынікі (у нашым прыкладзе гэта дыяпазон D6:D20)
- увядзіце (скапіруйце формулу ў першую ячэйку) дыяпазону
- 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
- Што такое формулы масіваў і для чаго яны выкарыстоўваюцца?