Многоразовый ВПР (VLOOKUP)
Имеем список заказов с номерами и названиями товаров. Хотелось бы, для примера, вытаскивать из таблицы по номеру заказа все товары, которые в него входят. Примерно так:
Замечательная функция ВПР (VLOOKUP) в такой ситуации поможет только частично, т.к. умеет вытаскивать данные только по первому найденному соответствию, т.е. даст нам только Яблоки. Для нахождения и извлечения из таблицы всех наименований лучше использовать формулу массива. Вот такую:
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))
Ее надо ввести следующим образом:
- выделить ячейки, куда должны выводиться результаты (в нашем примере - это диапазон D6:D20)
- ввести (скопировать формулу в первую ячейку) диапазона
- нажать Ctrl + Shift + Enter
Вычитание единицы в фрагменте СТРОКА(B2:B16)-1 делается из-за шапки таблицы. По той же причине для компенсации сдвига результирующего диапазона относительно исходного вычитается число пять во фрагменте СТРОКА()-5
Чтобы скрыть ошибку #ЧИСЛО!, которая будет появляться в незаполненных ячейках результирующего диапазона D6:D20 можно использовать функции проверки ошибок ЕСЛИ и ЕОШ, заменив нашу формулу чуть более сложной:
=ЕСЛИ(ЕОШ(ИНДЕКС($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));"")
P.S.
В англоязычной версии 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
- Что такое формулы массива и с чем их едят
Если бы мне поставили такую задачу, я не задумываясь бы начал писать макрос и циклом бы проходил массив.
Ваш вариант мне понравился
Спасибо
Что бы все товары из заказа отображались в одной ячейке?
Без использования макросов.
спасибо!
А вот формула из примера работает в любой версии и без макросов, хотя и несколько громоздкая, конечно.
Исходный вариант :
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))
Вариант с двумя условиями:
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2=A2:A16;что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))
Николай, если сможете, добавьте к статье, очень часто ищут этот вариант, но его очень непросто найти в интернете.
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2<$A$2:$A$16;F$2>$A$2:$A$16);СТРОКА($B$2:$B$16)-1);СТРОКА()-5))
где Е2=2;F2=5.
Т.е. выбираем товары с номером заказа 3 и 4.
Не работает. Но почему?
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2=A2:A16;что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))
можно попробовать так:
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(($E$2=A2:A16)*(что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))
(Если второе условие ищется в другом диапазоне, все работает)
Подскажите, пожалуйста, в чем может быть проблема?
Возможно ли решить с помощью ВПР следующую задачу:
Имеется таблица по ЗП разбитая по датам:
Нужно просуммировать ЗП каждого сотрудника
В файле итоговое суммирование происходит с помощью функции СУММЕСЛИ, но получается очень громоздко...
Может у кого есть решение.
Задача похожа, но вместо одного поиска необходимо подставлять значения для нескольких строк. из листа 1 в лист 3 столбец В
Лук
Лук
Яблоко
Лук
Яблоко
Манго
манго
Как уникальные выбрать?
единственное возник вопрос если таблица где ищем увеличивается (добавляются данные), формулу каждый раз править? Как сделать так, чтоб формула автоматически подхватывала расширяемый диапазон?
Умная таблица, но тогда формула вообще как то по другому начинает выглядеть и не понятно как быть.
для того, чтобы не считать и не исправлять количество "отнимаемых" строк функцию надо немного усложнить:
{=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-СТРОКА($B$1);"") ; ;СТРОКА()-СТРОКА($D$4)));"" )}
А именно вместо цифры номер строки - указать абсолютную ссылку на ячейку в этой строке (ну или строку) и тогда при перемещении таблицы или изменении строк в массиве не придётся переписывать формулу вручную
конкретно по этому примеру - как допилить, чтобы результирующий диапазон (D6:D20) не был фиксированным, а автоподстраивался по размерам в зависимости от количества товаров в заказе?
или это уже слишком для и без того крутого решения?
с уважением
upd: разобрался - ларчик просто открывался.
Есть исходная таблица со всеми возможными артикулами без дубликатов.
Есть вторая таблица в которой есть замены.
Как сделать общую таблицу с 2 колонками АРТИКУЛ и ЗАМЕНА, во второй колоне вывести, без дубликатов, все возможные варианты замен.
АРТИКУЛ ЗАМЕНА АРТИКУЛ ЗАМЕНА
A001 A001/A002 A001 A002/A003/A009/A010
A002 A002/A003 A002 A001/A003/A009/A010
A003 A003 A003 A001/A002/A009/A010
A004 A004/A005/A006/A007 A004 A005/A006/A007/A008
A005 A00555 A005 A004/A006/A007/A008
A006 A006 A006 A004/A005/A007/A008
A007 A007/A008 A007 A004/A005/A006/A008
A008 A008 A008 A004/A005/A006/A007
A009 A009/A010/A001 A009 A001/A002/A003/A010
A010 A010 A010 A001/A002/A003/A009
Помогите решить задачу, т.к. перебрал все возможные варианты нет результата.
А можно ли данную формулу применить, если значения необходимо собрать не в столбик, а в строку?
Есть исходные данные (ремонт техники в разбивкой по наименованиям) на одном листе, на втором листе необходимо "вытащить" все наименования техники, которая ремонтировалась, в один день может быть 2-3 ремонта разной техники.
Вопрос снят, решил с помощью VLOOKUP3
Как использовать эту формулу если необходимо искать не по номеру заказа (цифрам), а например по тексту "ячейка содержит часть искомого текста"? как использовать функцию Если в этом случае?
отличное видео руководство! Спасибо большое за Вашу работу!
прошу помощи в аналогичной задаче, но чуть усложненной.
когда нужно наоборот по слову "яблоки" найти все заказы в которых они попались.
но поиск осуществить не по полному соответствию ячеек, а по части текста.
т.к. в товарах есть:
яблоки
яблоко зеленое
яблоки красные
яблоко green
и так далее...
но понять нужно в каких заказах они попались/к каким клиентам поехали.
и в исходной таблице нельзя добавлять или изменять столбцы.
я пытался использовать ПОИСК, но он не работает с массивом,а ВПР берет только первое значение.
что можно сделать?