Улучшаем функцию ВПР (VLOOKUP)

«Как правильно уложить парашют?»
Пособие. Издание 2-е, исправленное.

Допустим, у нас имеется вот такая таблица заказов:

vlookup20.png

Нам необходимо узнать, например, какова была сумма третьего заказа Иванова или когда Петров оформил свою вторую сделку. Встроенная функция ВПР (VLOOKUP) умеет искать только первое вхождение фамилии в таблицу и нам не поможет. Вопросы типа "Кто был менеджером заказа с номером 10256?" тоже останутся без ответа, т.к. встроенная ВПР не умеет выдавать значения из столбцов левее поискового.

Обе эти проблемы решаются одним махом - напишем свою функцию, которая будет искать не только первое, а, в общем случае, N-ое вхождение. Причем и искать и выдавать результаты она сможет в любых столбцах. Назовем ее, допустим, VLOOKUP2. 

Откройте редактор Visual Basic, нажав ALT+F11 или выбрав в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor), вставьте новый модуль (меню Insert - Module) и скопируйте туда текст этой функции:

Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
                  N As Long, ResultColumnNum As Long)
    Dim i As Long, iCount As Long
    Select Case TypeName(Table)
    Case "Range"
        For i = 1 To Table.Rows.Count
            If Table.Cells(i, SearchColumnNum) = SearchValue Then
                iCount = iCount + 1
            End If
            If iCount = N Then
                VLOOKUP2 = Table.Cells(i, ResultColumnNum)
                Exit For
            End If
        Next i
    Case "Variant()"
        For i = 1 To UBound(Table)
            If Table(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1
            If iCount = N Then
                VLOOKUP2 = Table(i, ResultColumnNum)
                Exit For
            End If
        Next i
    End Select
End Function

Закройте редактор Visual Basic и вернитесь в Excel.

Теперь через Вставка - Функция (Insert - Function) в категории Определенные пользователем (User Defined) можно найти нашу функцию VLOOKUP2 и воспользоваться ей. Синтаксис функции следующий:

=VLOOKUP2(таблица; номер_столбца_где_ищем; искомое_значение; N; номер_столбца_из_которого_берем_значение)

Теперь ограничения стандартной функции нам не помеха:

vlookup21.png

P.S. Отдельное спасибо The_Prist за доработку функции, чтобы она могла искать в закрытых книгах.

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

 


14.03.2013 10:42:28
Отличная формула все работает.
16.03.2013 10:26:17
Подскажите! А можно осуществить в этой формуле поиск по двум условиям. Пробовал назначит возврат значения при 2-х условиях по принципу как в ИНДЕКС/ПОИСКПОЗ, но ничего не выходит...
16.03.2013 12:04:34
Надо переделывать макрос, т.е. добавлять в него новые аргументы (номер второго поискового столбца) и второе искомое значение.
01.07.2013 16:24:21
Вот было бы хорошо, если бы Вы, в общих чертах, поясняли код! Ясно, ято программистам это не нужно, я вот остальным...
Не интересно ведь просто скопировать, хотелось бы, чтобы и в голове немного прибавлялось, а не только в личной книге макросов.
25.05.2018 19:54:34
А переделывал ли кто-то этот макрос с параметрами которые вы описали?
18.03.2013 11:07:36
Как изменить макрос, чтобы данная формула возвращала все значения соответствующие данному условию. Т.е. как показать номера не первых пяти сделок Петров, а номера всех сделок Петрова.
01.05.2013 00:30:54
Можно вместо переделывания макроса воспользоваться формулой массива
30.04.2013 12:50:46
Можно ли усовершенствовать функцию такой возможностью: выбрать, например, максимальную сумму по сделкам Петрова или последнюю (первую) по времени сделку Сидорова?
04.06.2013 17:57:18
И меня интересует то же. Если номер или дата сделки Петрова (или вообще какой-либо её уникальный идентификатор) нам не известен, а известно лишь, что она -- "последняя" в списке. Как её искать? Есть варианты?
13.06.2013 09:54:05
Поменяйте цикл прохода по ячейкам на обратный, т.е. измените шестую строку:
For i = 1 To Table.Rows.Count
на
For i = Table.Rows.Count to 1 Step -1
25.05.2018 19:59:44
У меня была похожая задача, частично решил её с помощью "=СЧЁТЕСЛИМН(A:A;A1530;B:B;"Включить")-1
Если подставить эту формулу вместо аргумента "искомое значение", то будет показываться предпоследнее искомое значение из всех повторов.
06.06.2013 09:43:02
в каком случает тип Table есть "Variant()"? и почему в этом случае не задействована переменная
SearchColumnNum?
13.06.2013 21:13:14
Поисковую таблицу можно задать не только как диапазон, но и как массив - прямо в формуле. Например
{элемент1, элемент2, элемент3...} и т.п.
01.08.2013 18:25:52
Господа, всем доброго дня. Подскажите, пожалуйста, как можно данную формулу облегчить, оставив ее функционал?
просто когда в файле очень много значений и позиций, фаил просто зависает и с ним невозможно работать!
Заранее благодарен Вам!
01.08.2013 22:41:57
Любая макрофункция на VBA медленнее (в разы) своих встроенных аналогов. Тут ничего не поделать. Старайтесь выделять поменьше лишних ячеек в аргументах, не выделяйте столбцы целиком, отключите автоматический пересчет формул (вкладка Формулы - Вычисления - Вручную) и пересчитывайте только по клавише F9.
02.08.2013 13:39:37
Николай, а подскажите, почему после обратного включения пересчета в автоматеческий режим, макрос не работает, а при ручном пересчете с помощью F9 выдает просто 0...?
04.08.2013 16:14:49
Надо смотреть файл - пришлите на info@planetaexcel.ru - гляну.
16.08.2013 13:21:15
Доброго всем дня! Никак не могу сделать, чтобы номер заказа можно было выбрать скажем по условию больше 0.
Николай, исправьте код функции. Строка
If Table(i, 1) = SearchValue Then iCount = iCount + 1
должна быть такой:
If Table(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1
А то будет неверно значения возвращать при вычисления из закрытых книг. После этого комментарий можно удалить :-)
24.11.2013 12:00:52
Спасибо, поправил! :)
19.09.2013 12:18:36
Подскажите, пожалуйста, возможно ли как-то дополнить функцию (или есть другие возможности excel), чтобы данные из одной таблицы, где валюта операции указана в одном столбце, перетягивала данные в рабочую таблицу в три столбца (1- rub, 2 - usd, 3 - eur)? т.е., например, первое вхождение по сделке (оплата поставщику) в рублях - сумма подтягивается в колонку RUB, второе вхождение (выручка от покупателя по этой же сделке) в долларах - сумма выручки подтягивается именно в колонку usd. Заранее спасибо!
24.10.2013 00:41:54
Подскажите, пожалуйста, нужно чтоб  Table искало в диапазоне в другом файле, возможен ли такой вариант, например есть файл Книга1 в котором функция берет критерий поиска и чтоб искала в файле Книга2, а результат поиска выводился в файле Книга1. Спасибо!
09.01.2014 18:19:00
Николай, добрый день!
Возможно ли данную функцию дополнить интервальным просмотром (значение Истина) для поиска не точного значения, а ближайшего меньшего?  
30.01.2014 04:01:03
день добрый!
у меня задача несколько другая. помогите, пожалуйста...
любое упоминание аналогов ВПР (ГПР) на VBA сводится к выводу совпадений по строкам (столбцам), в то время как у ВПР есть еще одно полезное свойство.
дано:
столбец сортированных значений 1 (например, 1 5 10 17)
столбец произвольных значений 2 (например, 40 20 23 1)
ВПР выдает соответствующее значение 2 не только при полном совпадении аргумента с ячейкой из столбца 1, но и при попадании этого аргумента в диапазон значений соседних ячеек!
пример: аргумент 5 - ВПР выдаст 20, аргумент 7 - ВПР тоже выдаст 20, аргумент 10 - ВПР выдаст 23, аргумент 4 - ВПР выдаст 40.
КАК это реализовать на VBA?
спасибо.
08.03.2014 05:11:55
Здравствуйте,
Используя функцию ВПР необходимо найти приблизительное наименьшее значение которое больше чем искомое.
Как известно если интервальный просмотр ИСТИНА то получается наоборот, а если ЛОЖЬ то ищет только точное совпадение.
Как думаете можно как то реализовать задуманное при помощи ВПР или нужен макрос? Если макрос, то можно ли использовать этот и что в нем нужно заменить?
Даю пример есть значения в столбике А (10, 20, 30, 50, 80) Искомое значение 25, необходимо что бы эксель нашел 30 а не 20 как это происходит с ВПР.
Заранее спасибо
20.03.2014 10:29:29
Подскажите, а что такое N?
19.04.2014 11:20:54
N - это порядковый номер элемента, который надо найти (обычный ВПР находит только первое вхождение).
20.03.2014 17:41:33
Николай, отличная вещь. Спасибо!

Подскажите, как сделать доступной эту формулу для всех открытых книг в Excel 2007?
19.04.2014 11:19:27
Скопировать код макроса в личную книгу макросов.
25.03.2014 15:19:39
Добрый день!

Как подсчитать среднее значение сумму всех сделок Иванова, Петрова или др.
19.04.2014 11:20:11
Для этого в Excel есть встроенная функция СРЗНАЧЕСЛИ.
12.05.2014 13:57:00
Добрый день!

Спасибо за формулу! Не подскажете, что значит N? (в примере про первые пять сделок - J8).
12.05.2014 15:31:49
Порядковый номер извлекаемого элемента. Т.е. если товар встречается несколько раз, то будет извлечено N-е значение.
19.05.2014 15:48:22
Николай, добрый день!
Разъясните, пожалуйста, поподробней - что значит ссылка на ячейку J8? Она в Вашем примере вообще вне таблицы. Что делать, если нужно выдать какие-либо данные по, например, 8 или 10 первым сделкам?
22.10.2014 15:12:26
Не знаю просматриваете ли вы старые темы, но тем не менее: при сохранении данной функции, VBA присваивает ей в качестве имени название документа и название модуля вместе.
Например:
- мы создали документ под названием "Книга1";
- открыли редактор VBA, вставили исходный макрос и присвоили ему имя VLOOKUP2;
- в итоге имя функции принимает такой вид: =Книга1.xlsm!VLOOKUP2.VLOOKUP2().
Не подскажите как избавиться от данной приписки и сократить имя до VLOOKUP2.
26.03.2015 20:35:29
Отлично! Давно искал такую функцию. Существенно облегчит мне жизнь. Есть ли предел по N-вхождению в данной функции?
Николай доброго вечера.
Поставил всё работает. Очень благодарен.
Скажите возможно ли что бы данная функция находила все значения (не только какое то из них), и суммировала их....?
Спасибо заранее.
10.05.2015 00:13:12
Спасибо!
17.07.2015 12:45:10
Добрый день!
Не знаю актуально или нет, но вдруг кому пригодится, а влезать в дебри ООП на VB нет желания.
Есть способ решить данную проблему встроенными формулами:
1) Нужно лишь к подобному массиву добавить столбец с нумерацией по фамилиям менеджеров которая, будет вести подсчёт, в данном примере порядковые номера заказов, того или иного менеджера формулой
=ЕСЛИ(ЕПУСТО(B2);"";СЧЁТЕСЛИ($B$2:B2;B2))
Будет что-то , типа этого:
№ п/п
сделок
менеджеров
Менеджер
1Иванов
1Петров
1Сидоров
2Петров
2Сидоров
2Иванов
3Сидоров
3Петров
в данном случае первая ячейка с формулой - А2.

2) Встроенными функциями выбираем нужные данные по введенным условиям:

2.1) Данные по номеру заказа, например 10266
получать известной функцией
{=ИНДЕКС($B$2:$B$21;СУММ(СОВПАД($D$2:$D$21;$I$2)*СТРОКА($B$2:$B$21))-СТРОКА($C$1))}
(Не забываем фигурные скобки формулы массива!)
где заменяем первый массив, в моем примере $B$2:$B$21, другими диапазонами, содержащими нужные данные:
Номер заказа данная формула берет из ячейки $I$2
Заказ10266
МенеджерСидоров
Сумма346
Дата31.07.2003
2.2.) Сумма 5-го заказа Петрова решается формулой:
{=ИНДЕКС($E$2:$E$21;СУММ(ЕСЛИ($B$2:$B$21=$I$9;ЕСЛИ($A$2:$A$21=$I$10;СТРОКА($B$2:$B$21)-СТРОКА($B$1);0)));1)}
(Не забываем фигурные скобки формулы массива!)
получим следующее:
МенеджерПетров
сделка7
сумма сделки3536
Дата06.08.2003
Играя параметрами и диапазонами в формулах, вытаскиваем любые данные.

3) Добавив столбец с нумерацией по п.1 можно пользоваться функцией СУММЕСЛИМН.

А вообще-то разработчикам Excel давно пора расширить список функций работы с массивами.
Ну хотя бы увеличить в функции ВПР количество вводимых параметров и коэффициент вхождения, в данном случае 5-е вхождение ВПР по параметру Петров позволило бы получить все данные.
14.10.2015 11:34:10
Добрый день Николай и все посетители сайта.

Скажите пожалуйста как сдлать чтобы функция выдавала данные сразу на все вхождения фамилии в таблицу, у вас в примере пять вхождений и все прописаны вручную, а можно сделать также, только чтоб выдавал автоматом все строчки или N-ное кол-во вхождений?
14.10.2015 11:37:29
Иван, вам нужен Мульти-ВПР
15.10.2015 08:36:55
Добрый день!
Вопрос конечно может быть глупым но все же.
Как можно сделать чтобы выбираемый диапазон ячеек по полю "Table" стал сразу абсолютным, т.е. проставлялись знаки $ при выборе диапазона (без ручного ввода, без нажатия F4).  
31.01.2016 01:07:42
Здравствуйте Николай!

Помогите пожалуйста. Создала Вашу функцию, следовала строго инструкции, но она не работает. Т.к. мне нужно искать последнее значение, то поменяла строку 6, как Вы писали в комметариях. При сохранение, программа требует поменять фаил на "Microsoft Excel Macro-Enabled Worksheet (.xlsm)"
У меня Excel на английском языке.
Помогите пожалуйста, очень нужна эта функция.
05.02.2016 14:59:55
Думаю было бы отлично добавить данную строку перед концом функции, потому что при данном исполнении функция при поиске и не нахождении значения выдаёт 0 или пусто в зависимости от формата ячейки, а это может вводить в заблуждении. Так же это даёт возможность работать с функцией ЕСЛИОШИБКА.
If VLOOKUP2 = Empty Then VLOOKUP2 = CVErr(xlErrNA)

Так же удобно использовать эту функцию в связке с СЧЁТЕСЛИ, тогда не надо в ручную искать какой по счёту нужен элемент.

=VLOOKUP2(D:E;1;B8;СЧЁТЕСЛИ($B$1:B8;B8);2)
16.06.2016 10:04:08
Добрый день. Подскажите как сделать чтоб программа выдавала сумму по всем сделкам Петрова?
21.08.2016 06:41:20
Почему у Вас в примере, где "номера первых пяти сделок Петрова", в формуле указана ячейка J8??? Может быть все-таки G8?
13.10.2016 16:07:11
А можно обойтись без аргумента "N"? Чтоб он выдавал автоматом последнее значение
Николай Павлов, спасибо за Ваш сайт и прекрасный форум. Предлагаю вариант Вашей измененной функции ВПР. Теперь можно задавать 2 Искомых значения, и при совпадении, функция возвращает результат.
Т.е. получился ВПР по двум критериям.
Function Vlookup2Criteria(Table As Variant, SearchColumnNum1 As Long, SearchValue1 As Variant, SearchColumnNum2 As Long, SearchValue2 As Variant, N As Long, ResultColumnNum As Long)
    Dim i As Long, iCount As Long
    Select Case TypeName(Table)
    Case "Range"
        For i = 1 To Table.Rows.Count
            If Table.Cells(i, SearchColumnNum1) = SearchValue1 And Table.Cells(i, SearchColumnNum2) = SearchValue2 Then
                iCount = iCount + 1
            End If
            If iCount = N Then
                Vlookup2Criteria = Table.Cells(i, ResultColumnNum)
                Exit For
            End If
        Next i
    Case "Variant()"
        For i = 1 To UBound(Table)
            If Table.Cells(i, SearchColumnNum1) = SearchValue1 And Table.Cells(i, SearchColumnNum2) = SearchValue2 Then iCount = iCount + 1
            If iCount = N Then
                Vlookup2Criteria = Table(i, ResultColumnNum)
                Exit For
            End If
        Next i
    End Select
End Function
 
13.03.2017 18:14:30
Дмитрий Тарковский, подскажите, пожалуйста, как в таком случае будет выглядеть формула ГПР?
Большое спасибо!  
Владимир, пробуйте.
Function Glookup2Criteria(Table As Variant, SearchRowNum1 As Long, SearchValue1 As Variant, SearchRowNum2 As Long, SearchValue2 As Variant, N As Long, ResultRowNum As Long)
    Dim i As Long, iCount As Long
    Select Case TypeName(Table)
    Case "Range"
        For i = 1 To Table.Rows.Count
            If Table.Cells(SearchRowNum1, i) = SearchValue1 And Table.Cells(SearchRowNum2, i) = SearchValue2 Then
                iCount = iCount + 1
            End If
            If iCount = N Then
                Glookup2Criteria = Table.Cells(ResultRowNum, i)
                Exit For
            End If
        Next i
    Case "Variant()"
        For i = 1 To UBound(Table)
            If Table.Cells(i, SearchRowNum1) = SearchValue1 And Table.Cells(i, SearchRowNum2) = SearchValue2 Then iCount = iCount + 1
            If iCount = N Then
                Glookup2Criteria = Table(i, ResultRowNum)
                Exit For
            End If
        Next i
    End Select
End Function
 
05.04.2017 21:02:16
Добрый день, умные люди.

Очень нужна помощь. Есть необходимость сравнивать данные из одной таблицы  с данными другой по двум критериям.  Но не просто сравнивать, а вносить по итогам совпадения обоих критериев данные  из столбца второй таблицы. как то так.
Например:
есть таблица:
КЛАСС№поставщ
F7010101103402
F6050102102047
F7010301100298
F7020201103939
F2060202100342
F1060101102845
используя эти критерии, необходимо в неё залить данные из другой таблицы:
ГруппаМтрПоставщикОтветственный
F6050102100259Шашлова С.А.
F7010101100300Макеева А.В.
F7010101103402Макеева А.В.
F6050102100350Шашлова С.А.
F7010301100298Макеева А.В.
т.е. есть разные поставщики, разные группыМТР, но одновременное сочетание этих двух критериев  для "ответственного"  уникально и неповторимо.
Вот мне и необходимо в первую таблицу по итогам сравнения этих двух критериев, при их совпадении с данными из второй таблицы, вставлять ответственного.
Вроде ВПР но с использованием двух критериев.
Заранее спасибо за помощь. очень надеюсь, что есть такая возможность. и я смогу её с вашей помощью постичь.
25.08.2018 07:21:28
Как вариант - функцией "СЦЕПИТЬ" или оператором & (амперсанд) объединить два критерия (сколько угодно). Получиться уникальный индекс вида F7010101103402, а дальше как обычно.
25.05.2018 19:52:16
А возможно доработать всё это и сделать возможность поиска искомого значения более чем по одному параметру, например как работает функция "=СЧЁТЕСЛИМН"? Этого очень не хватает.
Здравствуйте, спасибо за статью. Скажите, а есть ли возможность не прописывать искомое значение словами, а заменить его ссылкой на ячейку?
26.04.2021 11:21:17
Добрый день Николай и форумчане.
Набралось сразу несколько вопросов :
- если VBA-аналог встроенной функции работает в разы медленнее, чем это обосновано ?
можно ли это как-то победить, например, вставкой на с++ ?
- касаемо VLOOKUP2 , а именно  N  (это порядковый номер элемента, который надо найти)
если мне нужны значения N с 1 по 50 , и далее с 81 по 90 , разделенные неким разделителем, напр. # и  выведенные
в 1 ячейку,  то это значит вызывать функцию  VLOOKUP2 60 раз, что очень медленно.
А что если расширить значение N таким образом: ...;1-50,81-90;...
ну и добавить доп параметр-  разделитель , напр.   "#"
- ну и посл.  вопрос, как быть если мне нужно отсортировать с помощью Excel массив CSV  20 млн. строк,
ведь excel позволяет запихнуть в лист только 1 млн... (;
 
11.12.2021 21:42:38
Подскажите, а можно "искомое значение" не вводить, а просто ссылаться на ячейку? Допустим меня очень интересует последний вариант, где по заказу ищем исполнителя. Просто если ссылаюсь на ячейку, где нужные данные выдаёт ошибку ЗНАЧ!. Заранее спасибо.
Добрый день, Николай!
Очень прошу помочь с формулой либо написанием макроса. Никак не получается подтянуть нужные данные.

Напротив Tag указанного в столбце А, необходимо подтянуть расценки указанные в строке 3 (может быть как одна из указанных расценок, так и несколько). Расценки необходимо подтянуть второй таблицы (Tag указан в столбце А, расценка в столбце L).
Очень признателен за помощь, огромное спасибо)))
Поставка и изготовление
Supply & Prefabrication
Tag3621.01.013621.01.023621.01.033621.01.043621.05.013621.05.023621.05.033621.06.013621.06.023621.06.033621.06.043621.06.05
P.3P5.DA-001/001-AA
P.3P5.DA-001/001-AB
P.3P5.DA-005/001
P.3P5.DA-005/002
P.3P5.DA-005/003
P.3P5.DA-006/001
P.3P5.DA-007/001-AA
P.3P5.DA-007/001-AB
P.3P5.DA-011/001
P.3P5.DA-012/001
P.3P5.DA-017/001
P.3P5.DA-017/002
P.3P5.DA-017/003
P.3P5.DA-018/001
P.3P5.DA-019/001
P.3P5.DA-020/002-AA
P.3P5.DA-020/002-AB
EP tag from KMD-MTOРаCценка
P.3P5.DA-001/001-AA3621.01.04
P.3P5.DA-001/001-AB3621.01.04
P.3P5.DA-005/0013621.01.04
P.3P5.DA-005/0013621.06.02
P.3P5.DA-005/0023621.01.04
P.3P5.DA-005/0023621.06.02
P.3P5.DA-005/0033621.01.04
P.3P5.DA-005/0033621.06.02
P.3P5.DA-006/0013621.01.04
P.3P5.DA-006/0013621.06.02
P.3P5.DA-007/001-AA3621.01.04
P.3P5.DA-007/001-AB3621.01.04
P.3P5.DA-011/0013621.01.04
P.3P5.DA-011/0013621.06.02
P.3P5.DA-012/0013621.01.04
P.3P5.DA-012/0013621.06.02
P.3P5.DA-017/0013621.01.04
P.3P5.DA-017/0023621.01.04
P.3P5.DA-017/0033621.01.04
P.3P5.DA-018/0013621.01.04
P.3P5.DA-018/0013621.06.02
P.3P5.DA-019/0013621.01.04
P.3P5.DA-019/0013621.06.02
P.3P5.DA-020/002-AA3621.01.04
P.3P5.DA-020/002-AA3621.06.02
P.3P5.DA-020/002-AB3621.01.04
P.3P5.DA-020/002-AB3621.06.02
P.3P5.DA-021/0013621.01.04
P.3P5.DA-021/0013621.06.02
P.3P5.DA-022/0013621.01.04
P.3P5.DA-022/0023621.01.04
P.3P5.DA-026/0013621.01.04
P.3P5.DA-026/0013621.06.02
P.3P5.DA-030/0013621.01.04
P.3P5.DA-030/0023621.01.04
P.3P5.DA-031/0013621.01.04
P.3P5.DA-031/0013621.06.02
Наверх