Многоразовый ВПР (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
- Что такое формулы массива и с чем их едят
В моем случае заработало не с первого раза: в диапазоне ячеек была одна со значением #Н/Д
Главное не просто Enter нажимать, а Ctrl + Shift + Enter!
...и тогда все будет работать!
Не подскажите,как и что нужно добавить чтобы поиск был по 2-м переменным?
То есть в данном примере идет поиск по Е2 (№ заказа) ,а что если мне нужно добавить еще одно условие?
Например эта таблица будет иметь еще колонку с датами.Тогда мне нужно искать и по №заказа и по дате.И вывести все,что будет соответствовать этим двум параметрам.Заранее спасибо!
Примерно так: =IF(AND(A2=$P$1;G2=$Q$1);ROW(C2)-1) - это конкретно из моего отчета.
В столбце C (на видео) прописываем двойное условие, и заводим ещё одну ячейку с нужным параметром ( как Е2), к примеру в F2.
Обе эти ячейки я сделал выпадающим списком: при пересечении двух условий выводится нужный результат. (Работаю с клиентской базой в отделе продаж). Условия в моем случае это маршрут продаж и день недели, при "пересечении" которых выпадает список клиентов.
Ещё раз СПАСИБО! Для меня это большой шаг в собственном развитии!
Подскажите пож., как можно сделать так, чтобы из этого результата получить выпадающий список, без того чтобы они записывались на лист?
Пробовал эту формулу вставить в "источник списка" он ничего не находит(
Добавьте пожалуйста в описание.
Подскажите , пожалуйста, возможно ли применить многоразовый ВПР для нескольких страниц?
Я применяла функцию =ИНДЕКС($L$2:$L$35;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"&$L$2:$L$35&"'!D4: D200";D3)>0;0))
Данная функция работает, но отбирает только лишь один показатель по критерию отбора , а их допустим три на всех имеющихся листах.
Подскажите пожалуйста формулу в случае если оставить в исходной задаче все без изменения кроме того, что за место цифр в столбце "номер заказа" подставить слова и искать не по точному совпадению, а по наличию слова в ячейке. Например нужно вытащить все номера изделий, напротив, которых в столбце "цвет краски" имеется слово "синий":
Кажется, у меня есть решение Вашей задачи, если, конечно, решение еще не найдено.
Помогите, пожалуйста, с такими трудностями:
1.Как вывести результаты по заданных критериях в виде списка в одном столбце подряд?
Т.е. если произошла выборка всех результатов согласно первого критерия, то приступить ко второму и выдать результаты согласно второго критерия под результатами первого...
Возможно ли это прописать формулой?
Если исключительно макросом, то подскажите, пожалуйста, где искать и на что опираться.
Очень нужно!
Заранее спасибо!
К тому же я любитель!
Вот что произошло с предлагаемой вами формулой. Задачи одинаковые поменялись лишь аргументы ( номера столбцов и строк,- диапазоны)
Ваша формула
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5))
Мой аналог
=INDEX($D$5:$D$14,SMALL(IF($S$2=B6:B15,ROW(D6:D15)-4,""),ROW()-5)) не работает: менял оба смещения подбирал.
=INDEX($D$5:$D$14,SMALL(IF($S$2=B6:B15,ROW(D6:D15)-4,""),1)) с этим изменением работает.
Вопросов много. Нужен диалог
Вот одна подсказка:
"Думаю да. Во всяком случае с ВПР у меня такое работало.
Как-то так попробуйте:
=ЕСЛИ(ЕНД(A1);"Мама";"Рама") "
Пробую
И еще мне нужно чтобы информация , собиралась не по одному столбцу, а по 2-3.
Помогите пожалуйста
как улушить мульти ВПР чтоб он искал по выбранным столбцам?
Задача: таблица с названием магазинов в 6 торговых центрах, мне необходимо создать обновляемую таблицу где можно будит из выпадающего списка выбрать название торгового центра и будут автоматически появяться все магазины которые есть в нем.
Если бы мне поставили такую задачу, я не задумываясь бы начал писать макрос и циклом бы проходил массив.
Ваш вариант мне понравился
Спасибо
Что бы все товары из заказа отображались в одной ячейке?
Без использования макросов.