ifempty

    Урок 4. Пользовательская функция ВПР с N-м вхождением (2)

    Оглавление:

    Введение

    Создание и использование пользовательской функции ВПР

    Создание пользовательской функции ВПР

    Вызов пользовательской функции ВПР

     

    Введение

    У классической функции ВПР есть ряд недостатков. Одним из которых является то, что она может находить только первую позицию в таблице. Но что если нам нужно найти вторую, пятую или последнюю. В такой ситуации нам поможет создание и использование пользовательской функции ВПР..

    Создание и использование пользовательской функции ВПР

    Скачайте файл Пользовательская функция ВПР - Приложение 1 по ссылке выше. В файле на листе даны три таблицы. В таблице 1 список должностей и табельные номера. В таблице 2 также есть список должностей но нет табельных номеров. В таблице 3 нужно указывать номер вхождения. 

    Задача:

    В таблице 2 нужно найти табельные номера должности в соответствии с номером вхождения. Например: если номер вхождения 2, то для первой строки Менеджер Таблицы 2 табельный номер будет 211322035 и т.д.. Смотрите рисунок 1:

     пользовательская функция ВПР рис1

    Рисунок 1. Пример решения задачи

    Создание пользовательской функции

    Перейдите на вкладку Разработчик и откройте Visual Basic. Если у вас нет вкладки разработчик, то ее нужно активировать, как указано в данном уроке Запись макросов. 

    В  редакторе Visual Basic слева в окне щелкните правой кнопкой мыши по объекту Эта книга  и в контекстном меню выберите Insert - Module (см. рисунок 2)

     пользовательская функция ВПР рис2

    Рисунок 2. Создание модуля в VBA

    Далее в окне модуля вставьте данный код:

    Function MYVLOOKUP(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
    MYVLOOKUP = 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
    MYVLOOKUP = Table(i, ResultColumnNum)
    Exit For
    End If
    Next i
    End Select
    End Function

     У вас должно получиться так:

     пользовательская функция ВПР рис3

    Рисунок 3. Вставка кода в модуль VBA

    После вставки кода закройте окно VBA.

    Вызов пользовательской функции ВПР

    Теперь в списке функций появилась новая функция. Далее нужно встать на ячейку Е3 и вызвать созданную функцию. Для этого через мастер функций нужно открыть список функций и в категории Определенные пользователем выбрать нашу функцию MYVLOOKUP (см. рисунок 4).

     пользовательская функция ВПР рис4

    Рисунок 4. Вызов пользовательской функции ВПР

    Окно аргументов функции выглядит следующим образом:

     пользовательская функция ВПР рис5

    Рисунок 5. Окно аргументов функции MYVLOOKUP

    Здесь:

    1. Table  - это таблица, в которой будет производииться поиск;
    2. SearchColumnNum - это номер столбца таблицы, по которому будет производиться поиск искомого значения (SearchValue);
    3. SearchValue - искомое значение, значение через которое будет произволдиться поиск;
    4. N - номер вхождения;
    5. ResultColumnNum - это порядковый номер столбца таблицы, из которого нужно вернуть результат.

     Заполненное окно аргументов показано нв рисунке 6:

     пользовательская функция ВПР рис6

    Рисунок 6. Заполненное окно аргументов функции MYVLOOKUP

    На рисунке выше видно, что ссылки в аргументах Table и N являются абсолютными чтобы при протягивании формулы вниз с помощью маркера автозаполнения ссылки остались неизменными.

    Осталовь протянуть формулу вниз. Теперь, если вы будете менять номер вхожденияя, то функция будет возвращать разные табельные номера (см. рисунок 7):

     пользовательская функция ВПР рис7

    Рисунок 7. Результат работы пользовательской функции ВПР

    В случае если функция не находит нужное вхождение, то она возвращает 0.

    Обратите внимание, что сохранить изменения можно выбрав тип файла с поддержкой макросов.

     

     

     

     

     

     

     

     

     


    У вас недостаточно прав