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

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

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

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

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

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

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

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

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

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

Николай, отличная вещь. Спасибо!

Подскажите, как сделать доступной эту формулу для всех открытых книг в Excel 2007?

Как подсчитать среднее значение сумму всех сделок Иванова, Петрова или др.

Спасибо за формулу! Не подскажете, что значит N? (в примере про первые пять сделок - J8).

Добрый день!Не знаю актуально или нет, но вдруг кому пригодится, а влезать в дебри ООП на VB нет желания.Есть способ решить данную проблему встроенными формулами: 1) Нужно лишь к подобному массиву добавить столбец с нумерацией по фамилиям менеджеров которая, будет вести подсчёт, в данном примере порядковые номера заказов, того или иного менеджера формулой =ЕСЛИ(ЕПУСТО(B2);"";СЧЁТЕСЛИ($B$2:B2;B2)) Будет что-то , типа этого:№ п/п сделок менеджеровМенеджер1Иванов1Петров1Сидоров2Петров2Сидоров2Иванов3Сидоров3Петровв данном случае первая ячейка с формулой - А2.

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

2.1) Данные по номеру заказа, например 10266получать известной функцией (Не забываем фигурные скобки формулы массива!) где заменяем первый массив, в моем примере $B$2:$B$21, другими диапазонами, содержащими нужные данные:Номер заказа данная формула берет из ячейки $I$2 Заказ 10266 МенеджерСидоровСумма346Дата31.07.2003 2.2.) Сумма 5-го заказа Петрова решается формулой: (Не забываем фигурные скобки формулы массива!) получим следующее: Менеджер Петров сделка 7 сумма сделки3536Дата06.08.2003Играя параметрами и диапазонами в формулах, вытаскиваем любые данные.

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

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

Добрый день Николай и все посетители сайта.

Скажите пожалуйста как сдлать чтобы функция выдавала данные сразу на все вхождения фамилии в таблицу, у вас в примере пять вхождений и все прописаны вручную, а можно сделать также, только чтоб выдавал автоматом все строчки или N-ное кол-во вхождений?

Помогите пожалуйста. Создала Вашу функцию, следовала строго инструкции, но она не работает. Т.к. мне нужно искать последнее значение, то поменяла строку 6, как Вы писали в комметариях. При сохранение, программа требует поменять фаил на "Microsoft Excel Macro-Enabled Worksheet (.xlsm)"У меня Excel на английском языке.Помогите пожалуйста, очень нужна эта функция.

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

Добрый день, умные люди.

Очень нужна помощь. Есть необходимость сравнивать данные из одной таблицы с данными другой по двум критериям. Но не просто сравнивать, а вносить по итогам совпадения обоих критериев данные из столбца второй таблицы. как то так.Например:есть таблица:КЛАСС№поставщF7010101103402F6050102102047F7010301100298F7020201103939F2060202100342F1060101102845используя эти критерии, необходимо в неё залить данные из другой таблицы:ГруппаМтрПоставщикОтветственныйF6050102100259Шашлова С.А.F7010101100300Макеева А.В.F7010101103402Макеева А.В.F6050102100350Шашлова С.А.F7010301100298Макеева А.В.т.е. есть разные поставщики, разные группыМТР, но одновременное сочетание этих двух критериев для "ответственного" уникально и неповторимо.Вот мне и необходимо в первую таблицу по итогам сравнения этих двух критериев, при их совпадении с данными из второй таблицы, вставлять ответственного. Вроде ВПР но с использованием двух критериев.Заранее спасибо за помощь. очень надеюсь, что есть такая возможность. и я смогу её с вашей помощью постичь.

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

За изображения спасибо Depositphotos.com

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРН 310633031600071

📎📎📎📎📎📎📎📎📎📎