Улучшаем функцию ВПР (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
Подскажите, а можно "искомое значение" не вводить, а просто ссылаться на ячейку? Допустим меня очень интересует последний вариант, где по заказу ищем исполнителя. Просто если ссылаюсь на ячейку, где нужные данные выдаёт ошибку ЗНАЧ!. Заранее спасибо.
Наверх