Как правильно упаковать парашют?
Выгода. Издание 2, переработанное.
Допустим, у нас есть следующая таблица заказов:
Нам нужно знать, например, какова была сумма третьего ордера Иванова или когда Петров выполнил свою вторую сделку. Встроенная функция ВПР может искать только первое вхождение фамилии в таблицу и нам не поможет. Вопросы типа «Кто был руководителем заказа № 10256?» тоже останется без ответа, т.к. встроенный ВПР не умеет возвращать значения из столбцов слева от искомого.
Обе эти проблемы решаются одним махом — напишем свою функцию, которая будет искать не только первое, но, в общем случае, N-е вхождение. Более того, он сможет искать и выдавать результаты в любых столбцах. Назовем это, скажем, ВПР2.
Откройте редактор Visual Basic, нажав ALT+F11 или выбрав в меню Сервис – Макрос – Редактор Visual Basic (Инструменты — Макросы — Редактор Visual Basic), вставьте новый модуль (меню Вставка – Модуль) и скопируйте туда текст этой функции:
Функция VLOOKUP2 (таблица как вариант, SearchColumnNum как длинный, SearchValue как вариант, _ N как длинный, ResultColumnNum как длинный) Dim i как длинный, iCount как длинный Выберите регистр TypeName(Table) Case «Диапазон» для 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 Тогда iCount = iCount + 1 Если iCount = N Тогда VLOOKUP2 = Table(i, ResultColumnNum) Выход For End Если Next i End Выбрать конечную функцию
Закройте редактор Visual Basic и вернитесь в Excel.
Сейчас через Вставка – Функция (Вставка — Функция) в категории User Defined (Определяемые пользователем) вы можете найти нашу функцию VLOOKUP2 и использовать ее. Синтаксис функции следующий:
=VLOOKUP2(таблица; номер_столбца_где_мы ищем; искомое_значение; N; номер_столбца_от_до_полученного_значения)
Теперь ограничения стандартной функции нам не помеха:
PS Особая благодарность The_Prist за улучшение функции, чтобы она могла искать в закрытых книгах.
- Поиск и подмена данных из одной таблицы в другую с помощью функции ВПР
- «Левый ВПР» с использованием функций ИНДЕКС и ПОИСКПОЗ.