Имаме списък с поръчки с номера и имена на стоки. Бих искал например да извадя от масата по номер на поръчка всички стоки, които са включени в нея. Горе-долу така:
Прекрасна функция VLOOKUP (VLOOKUP) в такава ситуация ще помогне само частично, защото може да извлече данни само от първото намерено съвпадение, т.е. само ще ни даде ябълки. За да намерите и извлечете всички елементи от таблицата, е по-добре да използвате формула за масив. Като този:
=INDEX($B$2:$B$16;ПОНЕ(IF($2=A2: A16;LINE(В2: В16)-1;»»);LINE()-5))
Трябва да се въведе, както следва:
- изберете клетките, където трябва да се показват резултатите (в нашия пример това е диапазонът D6:D20)
- въведете (копирайте формулата в първата клетка) на диапазона
- натискане 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
- Какво представляват формулите за масиви и за какво се използват?