Улучшаем функцию ВПР (VLOOKUP)
«Как правильно уложить парашют?»
Пособие. Издание 2-е, исправленное.
Допустим, у нас имеется вот такая таблица заказов:
Нам необходимо узнать, например, какова была сумма третьего заказа Иванова или когда Петров оформил свою вторую сделку. Встроенная функция ВПР (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; номер_столбца_из_которого_берем_значение)
Теперь ограничения стандартной функции нам не помеха:
P.S. Отдельное спасибо The_Prist за доработку функции, чтобы она могла искать в закрытых книгах.
Ссылки по теме
- Поиск и подстановка данных из одной таблицы в другую с помощью функции ВПР (VLOOKUP)
- "Левый ВПР" с помощью функций ИНДЕКС и ПОИСКПОЗ
Не интересно ведь просто скопировать, хотелось бы, чтобы и в голове немного прибавлялось, а не только в личной книге макросов.
For i = 1 To Table.Rows.Count
на
For i = Table.Rows.Count to 1 Step -1
Если подставить эту формулу вместо аргумента "искомое значение", то будет показываться предпоследнее искомое значение из всех повторов.
{элемент1, элемент2, элемент3...} и т.п.
просто когда в файле очень много значений и позиций, фаил просто зависает и с ним невозможно работать!
Заранее благодарен Вам!
Возможно ли данную функцию дополнить интервальным просмотром (значение Истина) для поиска не точного значения, а ближайшего меньшего?
у меня задача несколько другая. помогите, пожалуйста...
любое упоминание аналогов ВПР (ГПР) на VBA сводится к выводу совпадений по строкам (столбцам), в то время как у ВПР есть еще одно полезное свойство.
дано:
столбец сортированных значений 1 (например, 1 5 10 17)
столбец произвольных значений 2 (например, 40 20 23 1)
ВПР выдает соответствующее значение 2 не только при полном совпадении аргумента с ячейкой из столбца 1, но и при попадании этого аргумента в диапазон значений соседних ячеек!
пример: аргумент 5 - ВПР выдаст 20, аргумент 7 - ВПР тоже выдаст 20, аргумент 10 - ВПР выдаст 23, аргумент 4 - ВПР выдаст 40.
КАК это реализовать на VBA?
спасибо.
Используя функцию ВПР необходимо найти приблизительное наименьшее значение которое больше чем искомое.
Как известно если интервальный просмотр ИСТИНА то получается наоборот, а если ЛОЖЬ то ищет только точное совпадение.
Как думаете можно как то реализовать задуманное при помощи ВПР или нужен макрос? Если макрос, то можно ли использовать этот и что в нем нужно заменить?
Даю пример есть значения в столбике А (10, 20, 30, 50, 80) Искомое значение 25, необходимо что бы эксель нашел 30 а не 20 как это происходит с ВПР.
Заранее спасибо
Подскажите, как сделать доступной эту формулу для всех открытых книг в Excel 2007?
Как подсчитать среднее значение сумму всех сделок Иванова, Петрова или др.
Спасибо за формулу! Не подскажете, что значит N? (в примере про первые пять сделок - J8).
Разъясните, пожалуйста, поподробней - что значит ссылка на ячейку J8? Она в Вашем примере вообще вне таблицы. Что делать, если нужно выдать какие-либо данные по, например, 8 или 10 первым сделкам?
Например:
- мы создали документ под названием "Книга1";
- открыли редактор VBA, вставили исходный макрос и присвоили ему имя VLOOKUP2;
- в итоге имя функции принимает такой вид: =Книга1.xlsm!VLOOKUP2.VLOOKUP2().
Не подскажите как избавиться от данной приписки и сократить имя до VLOOKUP2.
Поставил всё работает. Очень благодарен.
Скажите возможно ли что бы данная функция находила все значения (не только какое то из них), и суммировала их....?
Спасибо заранее.
Не знаю актуально или нет, но вдруг кому пригодится, а влезать в дебри ООП на VB нет желания.
Есть способ решить данную проблему встроенными формулами:
1) Нужно лишь к подобному массиву добавить столбец с нумерацией по фамилиям менеджеров которая, будет вести подсчёт, в данном примере порядковые номера заказов, того или иного менеджера формулой
=ЕСЛИ(ЕПУСТО(B2);"";СЧЁТЕСЛИ($B$2:B2;B2))
Будет что-то , типа этого:
сделок
менеджеров
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
{=ИНДЕКС($E$2:$E$21;СУММ(ЕСЛИ($B$2:$B$21=$I$9;ЕСЛИ($A$2:$A$21=$I$10;СТРОКА($B$2:$B$21)-СТРОКА($B$1);0)));1)}
(Не забываем фигурные скобки формулы массива!)
получим следующее:
3) Добавив столбец с нумерацией по п.1 можно пользоваться функцией СУММЕСЛИМН.
А вообще-то разработчикам Excel давно пора расширить список функций работы с массивами.
Ну хотя бы увеличить в функции ВПР количество вводимых параметров и коэффициент вхождения, в данном случае 5-е вхождение ВПР по параметру Петров позволило бы получить все данные.
Скажите пожалуйста как сдлать чтобы функция выдавала данные сразу на все вхождения фамилии в таблицу, у вас в примере пять вхождений и все прописаны вручную, а можно сделать также, только чтоб выдавал автоматом все строчки или N-ное кол-во вхождений?
Вопрос конечно может быть глупым но все же.
Как можно сделать чтобы выбираемый диапазон ячеек по полю "Table" стал сразу абсолютным, т.е. проставлялись знаки $ при выборе диапазона (без ручного ввода, без нажатия F4).
Помогите пожалуйста. Создала Вашу функцию, следовала строго инструкции, но она не работает. Т.к. мне нужно искать последнее значение, то поменяла строку 6, как Вы писали в комметариях. При сохранение, программа требует поменять фаил на "Microsoft Excel Macro-Enabled Worksheet (.xlsm)"
У меня Excel на английском языке.
Помогите пожалуйста, очень нужна эта функция.
Так же удобно использовать эту функцию в связке с СЧЁТЕСЛИ, тогда не надо в ручную искать какой по счёту нужен элемент.
Т.е. получился ВПР по двум критериям.
Большое спасибо!
Очень нужна помощь. Есть необходимость сравнивать данные из одной таблицы с данными другой по двум критериям. Но не просто сравнивать, а вносить по итогам совпадения обоих критериев данные из столбца второй таблицы. как то так.
Например:
есть таблица:
Вот мне и необходимо в первую таблицу по итогам сравнения этих двух критериев, при их совпадении с данными из второй таблицы, вставлять ответственного.
Вроде ВПР но с использованием двух критериев.
Заранее спасибо за помощь. очень надеюсь, что есть такая возможность. и я смогу её с вашей помощью постичь.
Набралось сразу несколько вопросов :
- если VBA-аналог встроенной функции работает в разы медленнее, чем это обосновано ?
можно ли это как-то победить, например, вставкой на с++ ?
- касаемо VLOOKUP2 , а именно N (это порядковый номер элемента, который надо найти)
если мне нужны значения N с 1 по 50 , и далее с 81 по 90 , разделенные неким разделителем, напр. # и выведенные
в 1 ячейку, то это значит вызывать функцию VLOOKUP2 60 раз, что очень медленно.
А что если расширить значение N таким образом: ...;1-50,81-90;...
ну и добавить доп параметр- разделитель , напр. "#"
- ну и посл. вопрос, как быть если мне нужно отсортировать с помощью Excel массив CSV 20 млн. строк,
ведь excel позволяет запихнуть в лист только 1 млн... (;