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

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

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  
Дмитрий
23.12.2012 16:32:17
Работает. Супер. ОГРОМНОЕ СПАСИБО.
ахалай махалай
25.12.2012 16:08:04
осилил и пригодилось ))) огромное спасибо!
05.01.2013 22:51:48
У меня список заказов с номерами оформлен таблицей, заменил диапазоны из примера на столбцы таблицы - не получается. Что я делаю не так? Excel 2010.
12.01.2013 07:34:22
Спасибо вам за огромную работу. А можно вынести результаты в одну ячейку? В моем отчете обычно для товары бывают 1 рода и поэтому в бланке выделена одна строке. Но иногда выходит 2-3 вида, в этом случае чтобы результаты вышли в одну ячейку. Преждевременно запасить 2-3 строки не желательно т.к. таких товаров в бланке много и на каждый товар 2-3 строки это уже превышает поле и выглядеть плохо.
20.01.2013 23:26:37
А если не в столбец, а в строку? И склеивать потом функцией СЦЕПИТЬ?
30.01.2013 10:12:40
Вот у меня такая же проблема. как их сцепить?
30.01.2013 14:18:19
Функция СЦЕПИТЬ (CONCATENATE) из категории Текстовые.
22.08.2013 15:45:55
Как сделать из столбца строку? Если транспонировать выдает ошибки!
30.01.2014 14:04:07
Чтобы вывести результирующий список в строку, необходимо
1) выделять строку вместо столбца перед вставкой формулы массива
2) изменить формулу из примера в части "СТРОКА()-5" соответственно на "СТОЛБЕЦ()-Х", где Х=номер первого столбца в списке минус 1
13.06.2018 13:01:41
Николай, у меня два глобальных вопроса. Как научиться хорошо Понимать такие вот мартёшки-формулы  (чтоб самому писать их), а так же как всё запоминать? Смотрел уроки по Экселю, заносил всё в памятку, проходит неделя-две, значительную часть не помню! =(
20.01.2013 23:00:03
Для Excel 2007 и выше можно так (не формула массива):
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));"")
Формулу протянуть вниз на несколько ячеек.

Не я придумал, из уроков Mike Girvin
20.01.2013 23:27:02
Ух ты! Интересное решение, спасибо! Только функция АГРЕГАТ, по-моему, только в 2010 версии появилась?
17.05.2016 12:23:43
Николай, добрый день!
А не могли бы вы подробнее разобрать данную формулу
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));"")

Подставить её легко можно изменив на нужные диапазоны, но нет понимания откуда что берётся.

Заранее спасибо!
03.10.2014 16:07:46
Огромное спасибо!!! Выручил!
22.01.2013 14:44:02
Не совсем понятно, зачем вводим минус. По идее, шапку таблицы формула и так не должна учитывать, так как мы начали диапазон со второй строки (В2:В16). Со вторым минусом также не понятно - что означает строка () и почему -5. Вообще, нельзя ли разжевать синтаксис поподробнее - например, в случае с ВПР-ом у Вас даже видео есть, и схема, откуда что берется и куда переносится, хотя сама формула ВПР и так очень наглядная и логичная. Здесь же как-то все запутано.
Заранее спасибо
22.01.2013 19:44:13
Извиняюсь, функции АГРЕГАТ нет в Excel 2007.
Чтобы увидеть что происходит в какой-либо части функции нужно выделить ее, нажать F9. Возврат в прежнее состояние Ctrl+Z или Esc. Как происходит вычисление функции (формулы): панель ФОРМУЛЫ -->ВЫЧИСЛИТЬ ФОРМУЛУ.
24.01.2013 11:56:35
Добрый день,
пример СУПЕРСКИЙ-РЕСПЕКТ АВТОРУ. Мне облегчит жизнь очень и очень.
Пользуюсь excel-ем не первый день, но никогда не приходилось через панель "ФОРМУЛЫ -->ВЫЧИСЛИТЬ ФОРМУЛУ" смотреть, что и как вычисляется. Вчера пытался разобраться, но не понял сути (может есть инфа где почитнуть можно что бы разобраться?).
ПОЖАЛУЙСТА, РАЗЖУЙТЕ ДАННЫЙ КУСОЧЕК ФОРМУЛЫ. Поскольку применить для своих данных не получается.
НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"";);СТРОКА()-5))
26.01.2013 23:43:13
Индекс(Массив;Номер_строки), отсюда
Массив=$B$2:$B$16, Номер_строки=НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)


НАИМЕНЬШИЙ(Массив;k), отсюда
Массив=ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;""), k=СТРОКА()-5)

ЕСЛИ(лог_выраж.;знач._если_истина;знач._если_ложь), отсюда
если значение $E$2 равно значению из диапазона A2:A16, если ИСТИНА, то СТРОКА(В2:В16)-1, иначе пусто "".
Здесь СТРОКА(В2:В16) получает массив строк ={2:3:4:5:6:7:8:9:10:11:12:13:14:15:16}, а вычитаем 1 для уменьшения массива до = {1:2:3:4:5:6:7:8:9:10:11:12:13:14:15}.
То же самое и с k:
Функция СТРОКА() в ячейке D6 (как в примере) получает {6}, т.е. номер строки, вычитаем 5, получаем {1}
В ячейке D7 получаем {7}, вычитаем 5, получаем {2}

Если бы диапазон "В заказ входят" начинался бы к примеру не с D6, а с D3, то мы бы отняли ......
смекайте......

правильно! Два
Как мог...
28.01.2013 11:13:16
спасибо, буду разбираться.
13.02.2013 17:46:34
 Добрый день, уже 4й день сижу на вашем сайте не могу найти решение:( Имеется таблица первый столбец с адресами домов и 3 следующих столбца с телефонами. Необходимо в пятый столбец вывести телефоны, которые встречаются только с одним и тем же адресом.
Я бы прикрепил пример но как это тут сделать не понятно??  
15.02.2013 07:49:28
вам на форум нужно... по вопросу - поможет функция ВПР
18.02.2013 12:34:48
Да, Игорь, лучше создайте тему на форуме и приложите свой файл-пример. "По фотографии лечить тяжело", а тут файл не прикрепить - это комментарии к приему, а не форум.
25.09.2018 07:52:05
Дайте, пожалуйста, ссылку на вашу тему на форуме, если создавали. Аналогичная ситуация, сижу уже несколько дней над проблемой.
23.03.2013 10:09:18
а есть возможность, чтобы результат выводился не в столбец, а в строку?
19.06.2013 11:31:04
Можно переделать формулу или простотранспонировать столбец в строку.
19.06.2013 02:51:28
Николай! Благодарю за формулу!
С Вашей помощью удалось создать компактный проект. И на этом волшебство этой формулы не заканчивается: часто формулы возвращают нули, текст нулевой длины или просто есть пустые ячейки. Их можно удалять с помощью "Многоразового ВПР".
Пример на форуме
24.06.2013 19:44:04
Отличное решение, но не понял как его применить для поиска совпадений по нескольким условиям. Допустим у меня вместо одного номера заказа, столбец значений - E2 : E10, в котором числа {3;4;1;2;2;2;4;4;5}, а в D6 : D16 мне нужно вывести все значения из B2:B12, которые совпадают с числами E2:E10, в том числе и дубликаты...
30.10.2014 09:14:37
Используем ту же формулу что предложил Rustem
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;АГРЕГАТ(15;6;(СТРОКА($B$2:$B$16)-1)/($E$2=$A$2:$A$16);СТРОКА()-5));"")

Выделенная область является условием, что бы сделать больше условий необходимо так же через знак дроби (/) их прописать дальше. Единственное не получилось сделать условие с неопределенными типа A2&"*" в таком случае результат не выдавался.
11.02.2018 21:53:22

Все получилось)
26.09.2013 12:19:03
Автору спасибо!
Чтобы данные выводились в строку, можно сделать так. На листе с примером выделите диапазон F2:I2
и введите в него как формулу массива:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($A2=$A$2:$A$16;СТРОКА($B$2:$B$16)-1;"");СТОЛБЕЦ()-5));"")
Затем формулу можно "протянуть" вниз.
В строках диапазона F2:I2 напротив номера заказа по столбцам будут разнесены товары, которые в него входят. Затем формулой их можно сцепить в одну ячейку.
14.10.2013 14:13:56
Огромное спасибо автору за формулу!
27.10.2013 01:07:37
Автору респект. Очаровательная просто формула. Пользуюсь ей для превращения больших таблиц где есть множество пустых строк и столбцов в маленькие результирующие таблицы содержащие только данные. Спасибо
05.12.2013 00:08:26
А можно сделать так, чтобы переносилась не одна ячейка "Яблоки" а две "Яблоки" + цена к примеру, или "Яблоки" по названию. Или сделать то же но для двух условий.
Спасибо, если поняли.
16.01.2014 13:17:09
а если № заказа содержит 0 в начале?
24.04.2014 08:55:19
добрый день!

можно ли исключить дубликаты?
т.е. если номер заказа и товар совпадает несколько раз, то в итоговую таблицу попадало только одно значение?

заранее спасибо!
08.05.2014 10:15:29
Я бы сначала убил дубликаты тогда, а потом уже извлекал.
08.05.2014 10:31:50
спасибо!
воспользовался "Способ 3. Выборка уникальных записей формулой"
24.07.2014 15:41:55
Николай!
здравствуйте, помогите с решением проблемы.
суть такова.
есть файл с двумя листами (лист1 и лист2) на листе1 выгрузка из программы (таблица excel) очень много строк и столбцов с данными (даты, суммы, двадцатизначные номера и т.д.)
на листе2 в ячейке А2 выпадающий список со уникальными значениями из столбца А2:А7000 листа 1. в ячейку В2 заведена заведена формула =ЕСЛИ(ЕНД(ВПР...... либо (=ИНДЕКС(ПОИСКПОЗ....) с поиском и подстановкой всех имеющихся значений из листа 1 (т.е. грубо говоря лист 2 пустой в нем выпадающий список и формула, протянутая на весь массив данных как ы листе1.
лист 2 заполняется по мере поступления заказа в обработку.
проблема 1: искать значения в выпадающем списке долго (их более 7000), заводить вручную значения из выпадающего списка тоже долго (значения двадцатизначные, можно ошибиться) какую формулу либо макрос можно применить, чтобы через фильтр искать нужные значения в выпадающем списке допустим по первым пяти введенным знакам иили более и  выбирать уже из фильтра.

проблема2: при вводе значения на листе 2 вручную или через выпадающий список, значения листа 1 либо вводят сведения других срок, либо указывают на ссылку. причем если значения выпадающего списка вводятся по порядку, то бпроблем нет а если выборочно, то сведения в ячейках листа 2 не соответствуют сведениям ячеек листа 1.

 
24.08.2014 15:59:56
Здравствуйте!
Подскажите, пж, а как сделать так. чтобы в массиве выбиралось по значениям меньше и больше.
Например, имеем три столбца для подбора оборудования, в первом столбце значение объема помещения  min, во втором - max, а в третьем наименование. Задан объем комнаты, например, 2,4 m3 и надо, что бы показало два котла (котел 1 и котел 1.1)
2  m3   / 4  m3   котел 1
2  m3   / 4  m3   котел 1.1
5  m3   / 8  m3   котел 2
7  m3   / 12  m3   котел 3
=ЕСЛИОШИБКА(ИНДЕКС($H$14:$H$50;НАИМЕНЬШИЙ(ЕСЛИ(G$58>F$14:F$50<G14:G50;СТРОКА(H$14:H$50)-13;"";);СТРОКА()-57));"";)) - такой вариант, к сожалению, не работает... Работает, только при точном указании значения или min или max и то, как указано в таблице, например 5,5 уже не выдает ((
24.08.2014 16:02:37
2 m3     - 4 m3 котел 1
2 m3     -4 m3  котел 1.1
5 m3     -  8 m3  котел 2
7 m3     -12 m3  котел 3
что то сетка не отобразилась ))
29.10.2014 13:24:06
Николай, подскажите, если возможно, как усовершенствовать формулу многоразового ВПР, чтобы поиск осуществлялся по приблизительным формулировкам, а не точным (например, чтобы в запросе не писать "женьшень", а указать  - "жень" или "шен", а в итоге чтобы было "женьшень"). Можно ли как-то организовать такой поиск по маске - например *женьш* и т. п.?
27.11.2014 14:41:07
Доброго времени суток! Николай - огромное спасибо, за ваши "приемы" - по работе неоценимо помогло. Работаю с большим колличеством входящих данных из БД, из которых необходима сортировка по одному уникальному значению остальных ячеек, (помогло очень VLook). А для выборки текущих заданий закрепленных на меня! + в нужном статусе - да еще и чтобы возвращалась в виде гипперссылки - неоценимо помогла (INDEX - функция, все переработал под свои нужды) т.к. INDEX у вас описанна для сравнения определенного/фиксированного "$E$2", и после нажатия Ctrl + Shift + Enter использовал только для одной ячейки и автозаполнением "протянул" вниз но при этом не верно стала работать ф-я: "СТРОКА() - n" ROW()-n) начал было в ручную менять значение "n" на нужное, но потом исправил следующим: образом:  =IFERROR(INDEX(O$80:$O$5045,SMALL(IF(MID($C8,35,12)=D$80:$D$5045,ROW($O$80:$O$5045)-79,""),ROW()-(ROW()-1))),INDEX(O$80:$O$5045,SMALL(IF($C8=D$80:$D$5045,ROW($O$80:$O$5045)-79,""),ROW()-(ROW()-1))))  
Т.к. данные в искомом диапазоне представленны в двух вариантах ссылкой, и частью ссылки, для этого использованна "MID" функция.
13.01.2015 13:11:03
В моем случае при использовании формулы =IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5)),"")
ячейка пустая, но когда смотришь формулу подробно в развернутом варианте, значение формула определяет верно.
Помогите, пожалуйста, разобраться почему выводится пустое значение и как получить искомое?
14.01.2015 16:22:30
Добрый день! Подскажите, пожалуйста, возможно ли, чтобы список создавался, если рядом с ячейкой поставить флажок? Мне нужно из множества позиций выбирать несколько и чтобы они добавлялись в новый список, по порядку. Спасибо!
17.01.2015 22:35:13
У меня похожая задача. Есть организация с несколькими объектами. Нужно вывести список объектов. Сколько не бьюсь - ничего не получается. Попробовала точ в точ набрать Ваш пример - тоже ничего не выходит. Не могу понять, почему. Формат ячеек "общий".
04.02.2015 12:50:51
Большое спасибо за ценный пример. Давно искал способ делать такие выборки.
В моем случае заработало не с первого раза: в диапазоне ячеек была одна со значением #Н/Д
24.02.2015 16:29:31
Всем привет, и большое спасибо Николаю очень выручил этот прием. Но скажем если мне необходимо проводить выборку данных не по одному параметру как в примере а по двум или трём. Скажем есть в таблице еще поле склад т.е вводим номер партии, склад и получаем результата. Я пытался после "ЕСЛИ" добавить функцию "И" но у меня почему-то ничего не выходит. Пишет ошибку "ЗНАЧ!".
15.08.2015 10:55:30
Не сразу смог разобраться, но в итоге очень полезно, спасибо.
16.08.2015 17:05:04
Ааа!
Главное не просто Enter нажимать, а Ctrl + Shift + Enter!
...и тогда все будет работать! ;)
21.09.2015 15:21:01
Подскажите пожалуйста,  в столбце Е находится формула массива, которая позволяет отбирать коды из столбца А, если рядом (в столбце В) находятся значения. Как преобразовать эту формулу для нескольких массивов, в данном случае добавить еще кода из столбца С?
код1знач.код2знач.заказ
110610{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$6>0;СТРОКА($B$2:$B$6)-1);СТРОКА(E1)));"";)}
27
{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$6>0;СТРОКА($B$2:$B$6)-1);СТРОКА(E2)));"";)}
3208{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$6>0;СТРОКА($B$2:$B$6)-1);СТРОКА(E3)));"";)}
4920{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$6>0;СТРОКА($B$2:$B$6)-1);СТРОКА(E4)));"";)}
51010{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$6>0;СТРОКА($B$2:$B$6)-1);СТРОКА(E5)));"";)}
22.09.2015 11:08:29
Уже дали ответ: {=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$6;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$6>0;СТРОКА($B$2:$B$6)-1);СТРОКА(E1)));ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$6;НАИМЕНЬШИЙ(ЕСЛИ($D$2:$D$6>0;СТРОКА($D$2:$D$6)-1);СТРОКА(E1)-СЧЁТЕСЛИ($B$2:$B$6;">0")));""))} или вот лаконичнее {=НАИМЕНЬШИЙ(ЕСЛИ((B$2:$99>0)*ОСТАТ(СТОЛБЕЦ(A$2:C$99);2);A$2:C$99);СТРОКА(A1))}
Страницы: 1  2  
Наверх