Многоразовый ВПР (VLOOKUP)

105192 26.10.2012 Скачать пример

Имеем список заказов с номерами и названиями товаров. Хотелось бы, для примера, вытаскивать из таблицы по номеру заказа все товары, которые в него входят. Примерно так:

vlookup_all1.gif

 

Замечательная функция ВПР (VLOOKUP) в такой ситуации поможет только частично, т.к. умеет вытаскивать данные только по первому найденному соответствию, т.е. даст нам только Яблоки. Для нахождения и извлечения из таблицы всех наименований лучше использовать формулу массива. Вот такую:

=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Ее надо ввести следующим образом:

  1. выделить ячейки, куда должны выводиться результаты (в нашем примере - это диапазон D6:D20)
  2. ввести (скопировать формулу в первую ячейку) диапазона
  3. нажать 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)),"")

Ссылки по теме

 



Страницы: 1  2  
25.03.2016 12:42:03
Товарищи, а как сделать обратную функцию, кто подскажет? Когда есть список контрагентов, к каждому контрагенту есть подгруппа договоров. Как вывести их в отдельный список, чтобы наименование контрагента было напротив каждого из договоров?
19.04.2016 08:09:25
Всем привет!
Подскажите пож., как можно сделать так, чтобы из этого результата получить выпадающий список, без того чтобы они записывались на лист?
Пробовал эту формулу вставить в "источник списка" он ничего не находит(
27.04.2016 16:43:59
Функция нахождения всех значений, а не только первого, не сработает, если зотя бы в одной ячейке из индексируемых будет присутствовать значение #Н/Д
Добавьте пожалуйста в описание.  
27.10.2016 14:52:11
Добрый день!
Подскажите , пожалуйста, возможно ли применить многоразовый ВПР для нескольких страниц?
Я применяла функцию =ИНДЕКС($L$2:$L$35;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"&$L$2:$L$35&"'!D4: D200";D3)>0;0))
Данная функция работает, но отбирает только лишь один показатель по критерию отбора , а их допустим три на всех имеющихся листах.  
07.11.2016 10:06:43
Добрый день!

Подскажите пожалуйста формулу в случае если оставить в исходной задаче все без изменения кроме того, что за место цифр в столбце "номер заказа" подставить слова и искать не по точному совпадению, а по наличию слова в ячейке. Например нужно вытащить все номера изделий, напротив, которых в столбце "цвет краски" имеется слово "синий":
Цвет краски №изделия
синий131
светло синий221
темно синий 312
желтый 322
светло желтый417
Желаемый результат работы формулы должен быть таким:
синий131221312
желтый322417
Очень поможете если такая формула есть, иначе руками 27000 наименований прийдется обрабатывать.
26.05.2017 08:48:24
Добрый день, Мирас,
Кажется, у меня есть решение Вашей задачи, если, конечно, решение еще не найдено. :)
09.11.2016 15:05:22
Здравствуйте, Николай! Спасибо за Вашу помощь!
Помогите, пожалуйста, с такими трудностями:
1.Как вывести результаты по заданных критериях в виде списка в одном столбце подряд?
Т.е. если произошла выборка всех результатов согласно первого критерия, то приступить ко второму и выдать результаты согласно второго критерия под результатами первого...
Возможно ли это прописать формулой?
Если исключительно макросом, то подскажите, пожалуйста, где искать и на что опираться.
Очень нужно!
Заранее спасибо!
14.12.2016 12:17:44
Привет. У меня планшет Самсунг, Эксель в приложении OffiseSuite, на клавиатуре нет Shift, а нужны массивные функции.
К тому же я любитель!

Вот что произошло с предлагаемой вами формулой. Задачи одинаковые поменялись лишь аргументы ( номера столбцов и строк,- диапазоны)
Ваша формула
=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)) с этим изменением работает.
Вопросов много. Нужен диалог
14.12.2016 12:51:02
Теперь бъюсь над ошибками, что забивают пустые ячейки. В арсенале нет =IFERROR, а надо сделать переход по условию "ошибка"
Вот одна подсказка:
"Думаю да. Во всяком случае с ВПР у меня такое работало.
Как-то так попробуйте:
=ЕСЛИ(ЕНД(A1);"Мама";"Рама") "
Пробую
22.01.2017 15:44:42
А если в таблице не две, а несколько колонок? Если это будет работать - напишите, plz, как можно выводить все остальные, заранее спасибо!
Доброго дня! Мне очень нужно , чтобы найденная информация , помещалась не в столбец, а в одну ячейку через запятую . Как это можно реализовать?
И еще мне нужно чтобы информация , собиралась не по одному столбцу, а по 2-3.
Помогите пожалуйста
Добрый день! А как вытащить не "яблоко" или весь список, а только последнее "авокадо"?
22.04.2017 10:00:40
Владимир, поменяйте в формуле НАИМЕНЬШИЙ на НАИБОЛЬШИЙ - и будет вытаскивать в обратном порядке, т.е. начнет сразу с последнего :)
27.06.2017 19:09:10
Спасибо Вам за подробное пояснение! Очень помогло в решении задачки, над которой я ( в силу "начинания" пользования экселем ) долго мучался)
03.08.2017 14:46:54
Здравствуйте,
как улушить мульти ВПР чтоб он искал по выбранным столбцам?

Задача: таблица с названием магазинов в 6 торговых центрах, мне необходимо создать обновляемую таблицу где можно будит из выпадающего списка выбрать название торгового центра и будут автоматически  появяться все магазины которые есть в нем.
RankRetailACMAPABCHEGBEEZEAGH
1Vodafonexxxxxx
2Bijou Brigittexxxxxx
3Douglasxxxxxx
4Nanu-Nanaxxxx
5McPaperxxxxxx
6o2 Germanyxxxxx
7Gamestopxxxxxx
8Deichmannxxxx
9Nordseexxxxx
10DMxxxxx
11Telekom/T-mobilexxxxxx
спасибо
07.09.2017 22:40:55
Спасибо большое.
Если бы мне поставили такую задачу, я не задумываясь бы начал писать макрос и циклом бы проходил массив.
Ваш вариант мне понравился
Спасибо
16.10.2017 00:41:22
Подскажите, а как результат поместить не в массив, а в одну ячейку?
Что бы все товары из заказа отображались в одной ячейке?
Без использования макросов.
27.10.2017 08:01:22
Подскажите пожалуйста команду как повторить то же самое чтобы вводить номер заказа на одном листе, а список "в заказ входят" появлялся на втором, при этом  список товаров находится на третьем
спасибо!
31.10.2017 16:24:12
Добрый день! Замечательная формула! Огромное спасибо! Вопрос: в исходном списке есть повторяющиеся значения, при этом нужно выбрать только последнее из повторяющихся значений, удовлетворяющее условию. Как это можно сделать?
30.01.2018 07:19:57
Чем для такой задачи хуже VLOOKUP2? Я его применяю.
06.07.2019 01:31:15
VLOOKUP2 - это макрофункция из моей надстройки PLEX. Она пока еще не у всех пользователей Excel установлена :)
А вот формула из примера работает в любой версии и без макросов, хотя и несколько громоздкая, конечно.
30.01.2018 22:51:50
Здравствуйте! Подскажите пожалуйста, как можно сделать наоборот - есть таблица с данными - в ячейке А1 яблоки, А2 апельсины, А3 виноград, А4 яблоки с указанием количества - в ячейке В1-1, в ячейке В2-2 и тд, нужно найти все встречающиеся яблоки например и вывести их количество, заранее спасибо!
26.04.2018 12:20:07
Добавлю вариант формулы для поиска по двум условиям, долго искал, но нигде не нашел информации.Например, если нам надо искать не только по номеру заказа но и по дате или по какому-то другому критерию:

Исходный вариант :
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Вариант с двумя условиями:
 =ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2=A2:A16;что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Николай, если сможете, добавьте к статье, очень часто ищут этот вариант, но его очень непросто найти в интернете.
19.09.2018 16:37:47
Положим вот в таких условиях мы оказались:
=ИНДЕКС($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.

Не работает. Но почему?
27.11.2018 23:36:33
Если не срабатывает вариант с
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2=A2:A16;что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))
можно попробовать так:
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(($E$2=A2:A16)*(что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))
14.01.2019 14:25:56
Спасибо большое! Тоже долго искал решение. Подходит в 2016 только 2 вариант.
26.02.2019 13:42:16
Добрый день.
Возможно ли решить с помощью ВПР следующую задачу:
Имеется таблица по ЗП разбитая по датам: Пример
Нужно просуммировать ЗП каждого сотрудника
В файле итоговое суммирование происходит с помощью функции СУММЕСЛИ, но получается очень громоздко...
20.05.2019 15:48:14
Добрый день!

Может у кого есть решение.

Задача похожа, но вместо одного поиска необходимо подставлять значения для нескольких строк.пример из листа 1 в лист 3 столбец В
04.07.2019 09:24:20
Добрый день! Подскажите уважаемые гуру, как формулу с мультивпр развернуть, чтобы данные подтягивались при протяжке вправо, а не вниз. Ситуация следующая: имеются магазины у каждого свой id и улица по местоположению (столбцы А и В), есть варианты, что кол-во магазинов от двух и более находятся на одной улице. Требуется, чтобы по названию улицы, справа при протяжке по горизонтали (от столбца в С в сторону D, E...) притягивались id магазинов и  далее вниз напротив каждой улицы.
06.07.2019 01:28:33
С таким вопросом лучше на форум. И обязательно приложить файл с примером - по вашему описанию понять что-либо нереально :)
31.07.2019 17:58:11
Спасибо огромное, очень доступно объяснили. Вопрос, а вот если в столбце В есть повторяющиеся значения, как выбрать уникальные? Т.е. есть повторяющиеся номера заказов с теми же наименованиями товара. Тогда будет запрос так :
Лук
Лук
Яблоко
Лук
Яблоко
Манго
манго

Как уникальные выбрать?
14.10.2019 09:50:23
Спасибо большое за урок. Очень пригодилось
единственное возник вопрос если таблица где ищем увеличивается (добавляются данные), формулу каждый раз править? Как сделать так, чтоб формула автоматически подхватывала расширяемый диапазон?

Умная таблица, но тогда формула вообще как то по другому начинает выглядеть и не понятно как быть.
Страницы: 1  2  
Наверх