Функция соответствия Excel VBA

Функция соответствия VBA выполняет поиск позиции или номера строки искомого значения в массиве таблиц, то есть в основной таблице Excel. Например, VLOOKUP, HLOOKUP, MATCH, INDEX и т. Д. Это функции поиска, которые важнее других. К сожалению, у нас нет тех же функций, доступных в VBA для упрощения работы. Тем не менее, мы можем использовать эти функции в качестве функций рабочего листа в скрипте VBA, чтобы облегчить нашу жизнь.

Сегодня мы собираемся изучить функцию MATCH, которую можно использовать в качестве функции рабочего листа в VBA.

VBA Match используется так же, как формула соответствия в Excel. Эта функция в MS Excel VBA находит совпадение в массиве со ссылкой на значение поиска и печатает его позицию. Эта функция становится полезной, когда вам нужно оценить данные на основе определенных значений. Например, VBA MATCH полезна, если у вас есть данные о зарплате сотрудников, и вам необходимо узнать числовую позицию сотрудника в ваших данных, у которого зарплата меньше / больше / равна некоторому значению. Это действительно полезно при анализе данных, а также одна строка кода может автоматизировать все для вас.

Синтаксис функции соответствия в Excel VBA

VBA Match имеет следующий синтаксис:

Где,

  • Arg1 - Lookup_value - значение, которое вам нужно найти в данном массиве.
  • Arg2 - Lookup_array - массив строк и столбцов, которые содержат возможные Lookup_value.
  • Arg3 - Match_type - тип соответствия, который принимает значение -1, 0 или 1.

Если match_type = -1 означает, что функция MATCH обнаружит наименьшее значение, которое больше или равно lookup_value. Чтобы это произошло, lookup_array должен быть отсортирован в порядке убывания.

Если match_type = 0, это означает, что функция MATCH найдет значение, которое точно совпадает со значением lookup_value.

Если match_type = +1, это означает, что функция MATCH найдет наибольшее значение, которое меньше или равно lookup_value. Чтобы это произошло, lookup_array должен быть отсортирован в порядке возрастания. Значением по умолчанию для типа совпадения является +1.

Как использовать функцию соответствия Excel VBA?

Мы научимся использовать функцию VBA Match Excel на нескольких примерах.

Вы можете скачать этот шаблон VBA Match Excel здесь - Шаблон VBA Match Excel

Функция соответствия VBA - Пример # 1

Предположим, у нас есть данные, как показано ниже:

Нам нужно выяснить, у кого из этого списка зарплата 30 000 евро вместе с должностью в Excel.

Хотя в этом наборе данных мы можем настроить это вручную, подумайте о более широкой картине, что если у вас есть миллионы строк и столбцов?

Выполните следующие шаги, чтобы использовать функцию MATCH в VBA.

Шаг 1: Определите подпроцедуру, дав имя макросу.

Код:

 Sub exmatch1 () End Sub

Шаг 2: Теперь мы хотим, чтобы наш вывод был сохранен в ячейке E2. Поэтому начните писать код как Range («E2»). Значение =

Это определяет выходной диапазон для нашего результата.

Код:

 Sub exmatch1 () Range ("E2"). Значение = End Sub

Шаг 3: Используйте WorksheetFunction, чтобы использовать функции VBA.

Код:

 Sub exmatch1 () Range ("E2"). Значение = WorksheetFunction End Sub

Шаг 4: WorksheetFunction имеет множество функций, которые могут быть доступны и использоваться в VBA. После «WorksheetFunction» поставьте точку (.), И вы сможете получить доступ к функциям. Выберите функцию MATCH из выпадающего списка.

Код:

 Sub exmatch1 () Range ("E2"). Значение = WorksheetFunction.Match End Sub

Шаг 5: Теперь передайте аргументы функции MATCH. Как Lookup_value. Наше значение Lookup_value хранится в ячейке D2, как показано на снимке экрана ниже. Вы можете получить к нему доступ через функцию MATCH, используя функцию Range.

Код:

 Sub exmatch1 () Range ("E2"). Value = WorksheetFunction.Match (Range ("D2"). Value, End Sub

Шаг 6: Второй аргумент - Lookup_array. Это диапазон таблицы, в котором вы хотите узнать позицию Lookup_value. В нашем случае это (B1: B11). Предоставьте этот массив, используя функцию Range.

Код:

 Sub exmatch1 () Range ("E2"). Значение = WorksheetFunction.Match (Range ("D2"). Значение, диапазон ("B1: B11"), End Sub

Шаг 7: Последний аргумент для этого кода - Match_type. Мы хотели иметь точное совпадение для Lookup_value в заданном диапазоне> Поэтому, дайте Zero (0) в качестве совпадающего аргумента.

Код:

 Sub exmatch1 () Range ("E2"). Value = WorksheetFunction.Match (Range ("D2"). Value, Range ("B1: B11"), 0) End Sub

Шаг 8: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.

Вы можете видеть в ячейке E2, есть числовое значение (6), которое показывает положение значения от ячейки D2 до диапазона B1: B11.

Пример №2 - Функция соответствия VBA с циклами

Это легко, когда у вас есть только одно значение для поиска во всем диапазоне. Но что, если вам нужно проверить положение на количество ячеек? Человек, который добавляет, попросил бы написать отдельные коды для каждой ячейки.

В таких случаях функцию MATCH можно использовать с циклом (особенно в нашем случае с циклом For). Посмотрите следующие шаги, чтобы получить представление о том, как мы используем функцию MATCH с циклом.

Шаг 1: Определите подпроцедуру, дав имя макросу.

Код:

 Sub Example2 () End Sub

Шаг 2: Определите целое число, которое может содержать значение для нескольких ячеек в цикле.

Код:

 Sub Example2 () Dim i As Integer End Sub

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

Код:

 Sub Example2 () Dim i As Integer для i = 2 до 6 End Sub

Шаг 4: Теперь используйте тот же метод, который мы использовали в примере 1, просто вместо Range мы будем использовать функцию Cells и будем использовать двумерный массив (строки и столбцы) в отличие от первого примера.

Код:

 Sub Example2 () Dim i As Integer для i = 2-6 ячеек (i, 5) .Value = WorksheetFunction.Match (Cells (i, 4) .Value, Range ("B2: B11"), 0) Next i End Sub

Здесь Cells (i, 5) .Value = сохраняет значение результирующих позиций в каждой строке от 2 до 6 (строка i) в столбце E (столбец № 5). В функции Match Cells (i, 4) .Values ​​проверяет каждое значение Lookup_value, присутствующее в строках со 2 по 6 в 4- м столбце. Это значение поиска затем ищется в массиве B2: B11 в листе Excel, где имеются данные, и относительные позиции могут быть сохранены в каждой строке столбца 5 (столбец E).

Шаг 5: Запустите этот код, нажав F5 или кнопку Run одновременно и посмотрите результат. Это почти вытянет магию в куске кода в одну строку.

В этой статье мы узнали, как мы можем использовать функцию MATCH в VBA как особый случай WorksheetFunction.

То, что нужно запомнить

  • Lookup_value может быть числом / текстом / логическим значением или может быть ссылкой ячейки на число, текст или логическое значение.
  • По умолчанию Match_type может рассматриваться как 1, если опущено / не упомянуто.
  • Подобно функции Excel MATCH, VBA MATCH также дает относительную позицию значения Lookup_value в Lookup_array, а не само значение.
  • Если совпадение не найдено, относительная ячейка Excel будет заполнена # N / A.
  • Если функция MATCH используется для текстовых значений, она не может различать строчные и прописные буквы. Например, Лалит и Лалит одинаковы. Так что ЛАЛИТ и ЛАЛИТ.
  • Подстановочные знаки могут использоваться, если вы находите точное совпадение (т. Е. Тип совпадения равен нулю). Подстановочный знак «звездочка» (*) можно использовать для определения последовательности символов. В то время как вопросительный знак (?) Можно использовать, чтобы узнать один символ.

Рекомендуемые статьи

Это руководство по функции соответствия VBA. Здесь мы обсуждаем VBA Match и как использовать функцию Excel VBA Match вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -

  1. Полное руководство по VBA при ошибке
  2. Как использовать числовой формат VBA?
  3. VBA VLOOKUP Функция с примерами
  4. Создание функции VBA в Excel
  5. Функция Excel Match (примеры)

Категория:

VBA