Добрый день уважаемый пользователь Excel!
Я хочу продолжить описание работы с массивами данных, и сейчас рассмотрим способы улучшения работоспособности функции ВПР. Для чего это собственно нужно, расширять и улучшать функционал работы функции ВПР в Excel, просто достаточно часто возникает необходимость собрать все доступные вхождение условий, а не только первое, которое встретится.
Саму работу функции ВПР, а также ее сестры функцию ГПР, я описывал в отдельных статьях, с которыми вы можете ознакомиться, перейдя по соответствующим ссылкам.
Итак, создадим таблицу примеров: Теперь можно узнать, к примеру, какая прибыль принесла вторая отгрузка компании «Ванта» ну или когда отгружалась третий раз компания «Каскад». Так как стандартная возможность функции ВПР этого сделать не может, она найдет первое вхождения по названию компании и остановится. А на вопрос о том кто отгрузил товар по накладной №874, ответа вы не получите, т.к. функционал ВПР не работает на поиск значений левее от столбика поиска и нужно использовать комбинацию функций ПОИСКПОЗ и ИНДЕКС.
Но, все ваши проблемы можно решить одним простым способом, ну или не совсем простым, это создать собственную функцию, с помощью возможностей макросов VBA, которая сможет искать не только в любых столбиках, но и находить все вложения согласно заданных условий.
На просторе Интернета много полезностей, вот и нашёл там код функции от Николая Павлова, который вам поможет. Условно назовите ее, к примеру, VPR, чтобы не забыть. Откройте редактор VBA с помощью горячего сочетания клавиш ALT+F11 или на панели управления в разделе «Разработчик» в блоке «Код», нажимаете кнопку «Visual Basic».
Следующим шагом создаете новый модуль, в меню выбираете пункт «Insert», а потом нажать «Module» и вставляете в него следующий код:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
FunctionVPR(Table AsVariant,SearchColumnNum AsLong,SearchValue AsVariant,_ NAsLong,ResultColumnNum AsLong) DimiAsLong,iCount AsLong Select CaseTypeName(Table) Case«Range» Fori=1ToTable.Rows.Count IfTable.Cells(i,SearchColumnNum)=SearchValue Then iCount=iCount+1 EndIf IfiCount=NThen VPR=Table.Cells(i,ResultColumnNum) Exit For EndIf Nexti Case«Variant()» Fori=1ToUBound(Table) IfTable(i,SearchColumnNum)=SearchValue TheniCount=iCount+1 IfiCount=NThen VPR=Table(i,ResultColumnNum) Exit For EndIf Nexti EndSelect EndFunction |
Теперь можно закрыть редактор макросов и переключится на вашу рабочую книгу. Устанавливаете курсор в нужном месте и вызываете «Мастер функций», указываете категорию функций «Определенные пользователем» находите созданную функцию VPR и вставляете ее.
Синтаксис созданной функции такой:
= VPR (_таблица_ поиска_; _номер_столбика _где_ищем_; _значение_ которое _ищем_; _номер_значения_ которое_нужно_ найти_; _номер_столбика _из_которого_изымаем _значение_).
Детально описывать я не буду, так как функция достаточно проста и понятна. Как видите, теперь ограничений на работоспособность функций нет, и все ваши задачи смогут воплотиться в жизнь. Я очень хочу, чтобы границ используемых возможностей функции ВПР в Excel у вас расширились, и вы могли применить их в своей работе. Если у вас есть чем дополнить меня пишите комментарии, я буду их ждать с нетерпением, ставьте лайки и делитесь полезной статьей в соц.сетях!
Не забудьте подкинуть автору на кофе…
