У нас есть список заказов с номерами и наименованиями товаров. Мне хотелось бы, например, вытащить из таблицы по номеру заказа все товары, которые в нее входят. Примерно так:
Замечательная функция ВПР (ВПР) в такой ситуации поможет лишь частично, т.к. способен извлечь данные только по первому найденному совпадению, т.е. даст нам только Яблоки. Чтобы найти и извлечь все элементы из таблицы, лучше использовать формулу массива. Как этот:
=ИНДЕКС($B$2:$B$16;НАИМЕНЕЕ(IF(2 доллара США=A2: A16;ЛИНИЯ(В2: В16)-1;»»);ЛИНИЯ()-5))
Его необходимо ввести следующим образом:
- выделите ячейки, в которых должны отображаться результаты (в нашем примере это диапазон D6:D20)
- введите (скопируйте формулу в первую ячейку) диапазона
- нажмите Ctrl + Shift + Enter
Вычитание единицы во фрагменте СТРОКА(B2:B16)-1 делается из-за заголовка таблицы. По этой же причине для компенсации смещения полученного диапазона относительно исходного из фрагмента вычитается цифра пять СТРОКА()-5
Чтобы скрыть #NUM! ошибка, которая появится в пустых ячейках результирующего диапазона D6:D20, можно воспользоваться функциями проверки ошибок IF и 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 появилась более удобная функция ЕСЛИОШИБКА — она позволяет компактнее решить задачу:
=ЕСЛИОШИБКА(ИНДЕКС($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)),»»)
- Использование функции ВПР для поиска данных в таблице
- Улучшенная версия функции VLOOKUP2, которая может выполнять поиск в любом столбце, а не только по первому значению.
- Функции VLOOKUP2 и VLOOKUP3 из дополнения PLEX
- Что такое формулы массива и для чего они используются?