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

2076 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  3  
Aidos Рахимжанов
27.10.2017 08:01:22
Подскажите пожалуйста команду как повторить то же самое чтобы вводить номер заказа на одном листе, а список "в заказ входят" появлялся на втором, при этом  список товаров находится на третьем
спасибо!
Ссылка
Vladimir Kaverin
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 и тд, нужно найти все встречающиеся яблоки например и вывести их количество, заранее спасибо!
Ссылка
Dyroff
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))

Николай, если сможете, добавьте к статье, очень часто ищут этот вариант, но его очень непросто найти в интернете.
Ссылка
ival-l
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.

Не работает. Но почему?
Родитель Ссылка
Dyroff
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 вариант.
Родитель Ссылка
Даниил
23.03.2021 08:00:03
У меня в 2016 ни один из вариантов не работает если доп. условие ищется в том же диапазоне, что и первое.
(Если второе условие ищется в другом диапазоне, все работает)
Подскажите, пожалуйста, в чем может быть проблема?
Родитель Ссылка
Илья Афанасов
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
Спасибо большое за урок. Очень пригодилось
единственное возник вопрос если таблица где ищем увеличивается (добавляются данные), формулу каждый раз править? Как сделать так, чтоб формула автоматически подхватывала расширяемый диапазон?

Умная таблица, но тогда формула вообще как то по другому начинает выглядеть и не понятно как быть.
Ссылка
Кирилл Коньков
21.11.2019 17:23:02
Большое спасибо! Формула работает, но почему-то массив распространяется только на 20 строк((
Ссылка
Юрий Федянин
06.02.2020 17:05:19
Николай, а подскажите, данная конструкция работает при выборке данных с других листов? У меня не работает (((
Ссылка
User Free
20.03.2020 09:31:50
Очень классный, наглядный и понятный пример. От себя могу добавить только следующее:
для того, чтобы не считать и не исправлять количество "отнимаемых" строк функцию надо немного усложнить:
{=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-СТРОКА($B$1);"") ;  ;СТРОКА()-СТРОКА($D$4)));"" )}

А именно вместо цифры номер строки - указать абсолютную ссылку на ячейку в этой строке (ну или строку) и тогда при перемещении таблицы или изменении строк в массиве не придётся переписывать формулу вручную
Ссылка
Vadim
15.05.2020 01:56:06
спасибо за всю Вашу работу
конкретно по этому примеру - как допилить, чтобы результирующий диапазон (D6:D20) не был фиксированным, а автоподстраивался по размерам в зависимости от количества товаров в заказе?
или это уже слишком для и без того крутого решения?
с уважением
Ссылка
Андрей Нестеров
07.04.2021 11:30:40
Добрый день! Формула очень помогла сдвинуться с мёртвой точки, но моя задача немного другая. Как поступить если нужно отобрать все товары из примера из заказа №3 и всех более ранних заказов, т.е №2 и №1. Заранее благодарю!

upd: разобрался  - ларчик просто открывался.  
Ссылка
Александр Рева
12.04.2021 15:26:04
Здравствуйте. Нужная и полезная информация, спасибо. Хотелось бы спросить, возможно ли модернизировать формулу? Например, в правом столбике напротив каждого товара указать категорию (например овощи,фрукты и т.п.) и при запросе выдавалось не просто список заказа,а только овощи, например. Заранее благодарен.
Ссылка
Валерий Муратов
06.07.2021 10:13:30
Здравствуйте Николай. =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"";СТРОКА()-5));"" Подскажите пожалуйста. А если массив данных на одном листе, а вычисление формулы делаем на другом листе, что нужно указать в аргументе формулы СТРОКА?  У меня в таблице аргументы находятся на лист 1 а результат вычислений нужно вывести на лист 2 в ячейку A2. Как в этом случае составить формулу?
Ссылка
IeskelaHsydyv
11.11.2021 10:31:26
День добрый, уважаемые гуру Excel. Каждый день сталкиваюсь с рутинной задачей работы подбора замен номеров.
Есть исходная таблица со всеми возможными артикулами без дубликатов.
Есть вторая таблица в которой есть замены.
Как сделать общую таблицу с 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


Помогите решить задачу, т.к. перебрал все возможные варианты нет результата.
Ссылка
Yuliya Yuliya
12.04.2022 15:33:00
Добрый день!
А можно ли данную формулу применить, если значения необходимо собрать не в столбик, а в строку?
Есть исходные данные (ремонт техники в разбивкой по наименованиям) на одном листе, на втором листе необходимо "вытащить" все наименования техники, которая ремонтировалась, в один день может быть 2-3 ремонта разной техники.
Ссылка
Алексей
20.04.2022 12:07:20
Добрый день. А если аналогичная задача, но вместо цифр номера заказа текст и надо искать совпадение по его части? Какую функцию использовать?

Вопрос снят, решил с помощью VLOOKUP3
Ссылка
Сергей Якимов
29.09.2022 16:25:27
Добрый день!
Как использовать эту формулу если необходимо искать не по номеру заказа (цифрам), а например по тексту "ячейка содержит часть искомого текста"? как использовать функцию Если в этом случае?
Ссылка
Анатолий Иванов
11.12.2022 15:37:31
=ФИЛЬТР(B2:B16;$E$2=A2:A16;"")
Ссылка
Sergey Danta
01.02.2023 18:52:46
Добрый день.
отличное видео руководство! Спасибо большое за Вашу работу!

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

но понять нужно в каких заказах они попались/к каким клиентам поехали.
и в исходной таблице нельзя добавлять или изменять столбцы.

я пытался использовать ПОИСК, но он не работает с массивом,а ВПР берет только первое значение.
что можно сделать?
Ссылка
Виктор Сакал
19.05.2024 15:39:55
чучуть начал писать на VBA  вот мой код он делает тоже самое только результат помещает в одну ячейку разделяя ";"

Sub Найти совпадения ()
    Dim lastRowA As Long
    Dim lastRowF As Long
    Dim i As Long
    Dim j As Long
    Dim result As String
    
    lastRowA = Cells(Rows.Count, "A").End(xlUp).Row
    lastRowF = Cells(Rows.Count, "F").End(xlUp).Row

    For i = 1 To lastRowF
        result = ""
        For j = 1 To lastRowA
            If Cells(i, "F").Value = Cells(j, "A").Value Then
                If result = "" Then
                    result = Cells(j, "B").Value
                Else
                    result = result & ";" & Cells(j, "B").Value
                End If
            End If
        Next j
        Cells(i, "H").Value = result
    Next i
End Sub

Пример работы
Ссылка
Василий Горохов
28.08.2024 10:49:57
Николай доброго времени суток!
Подскажите пожалуйста как полученный результат из значений выборки объединить в одну ячейку отделяя их, например запятой.
Ссылка
Страницы: 1  2  3  
Наверх