Excel VBA Match Function

Функція відповідності VBA шукає позицію або номер рядка значення пошуку в масиві таблиці, тобто в головній таблиці excel. Наприклад, VLOOKUP, HLOOKUP, MATCH, INDEX тощо. Це функції пошуку, які важливіші за інші. На жаль, у нас немає однакових функцій, доступних у VBA для полегшення роботи. Однак ми можемо використовувати ці функції як функції робочого листа за сценарієм VBA, щоб полегшити наше життя.

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

VBA Match має таке ж використання, як і формула 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 () Кінець Sub

Крок 2: Тепер ми хочемо, щоб наш вихід зберігався у комірці E2. Тому почніть писати код як Діапазон ("E2")

Це визначає діапазон виходу для нашого результату.

Код:

 Діапазон суб exmatch1 () ("E2"). Значення = Кінець Sub

Крок 3: Використовуйте WorksheetFunction, щоб мати можливість використовувати функції VBA.

Код:

 Діапазон Sub exmatch1 () ("E2"). Значення = WorkheetFunction End Sub

Крок 4: WorkheetFunction має різноманітні функції, до яких можна отримати доступ та використовувати під VBA. Після "WorksheetFunction" поставте крапку (.) І тоді ви зможете отримати доступ до функцій. Виберіть функцію MATCH зі спадного списку.

Код:

 Діапазон Sub exmatch1 () ("E2"). Значення = WorksheetFunction.Match End Sub

Крок 5: Тепер наведіть аргументи функції MATCH. Як і Lookup_value. Наше значення Lookup_va зберігається у комірці D2, як показано на скріншоті нижче. Ви можете отримати доступ до нього за допомогою функції MATCH за допомогою функції Range.

Код:

 Діапазон Sub exmatch1 () ("E2"). Значення = WorksheetFunction.Match (Діапазон ("D2"). Значення, кінець

Крок 6: Другий аргумент - Lookup_array. Це діапазон таблиць, в якому ви хочете з’ясувати позицію Lookup_value. У нашому випадку це (B1: B11). Надайте цей масив за допомогою функції Range.

Код:

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

Крок 7: Останній аргумент для розробки цього коду - Match_type. Ми хотіли мати точну відповідність для Lookup_value у заданому діапазоні> Отже, наведіть Zero (0) як аргумент відповідності.

Код:

 Sub exmatch1 () Діапазон ("E2"). Значення = WorksheetFunction.Match (Діапазон ("D2"). Значення, діапазон ("B1: B11"), 0) Кінець Sub

Крок 8: Запустіть цей код, натиснувши кнопку F5 або Запустити і подивіться вихід.

Ви можете бачити в комірці E2, є числове значення (6), яке показує положення значення з комірки D2 через діапазон B1: B11.

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

Це легко, коли у вас є лише одне значення для пошуку у всьому діапазоні. Але що робити, якщо вам потрібно перевірити положення для декількох комірок? Людина, яка додає, буде суворою, щоб попросити його написати окремі коди для кожної комірки.

У таких випадках функцію MATCH можна використовувати з циклом (особливо для циклу в нашому випадку). Дивіться наступні кроки, щоб отримати уявлення про те, як ми використовуємо функцію MATCH з циклом.

Крок 1. Визначте підпроцедуру, давши ім’я макросу.

Код:

 Sub Example2 () Кінець Sub

Крок 2: Визначте ціле число, яке може містити значення для декількох комірок у циклі.

Код:

 Sub Example2 () Dim i As Integer End Sub

Крок 3: Використовуйте для циклу на ціле число, щоб використовувати різні значення пошуку, положення яких можна зберігати у стовпці Е.

Код:

 Sub Example2 () Dim i As Integer For i = 2 - 6 Кінець Sub

Крок 4: Тепер використовуємо той самий метод, який ми використовували в прикладі 1, просто замість діапазону ми будемо використовувати функцію Cells і будемо використовувати двовимірний масив (рядки та стовпці) на відміну від першого прикладу.

Код:

 Під приклад2 () Dim i як ціле число для i = від 2 до 6 комірок (i, 5) .Value = WorkheetFunction.Match (Осередки (i, 4) .Value, Range ("B2: B11"), 0) Наступний i кінець Під

Тут Cells (i, 5) .Value = зберігає значення результуючих позицій у кожному рядку від 2 до 6 (рядок i) у колонці E (колонка № 5). Під функцією "Збіг" клітинки (i, 4) .Values ​​перевіряє кожен Lookup_value, присутній у рядках 2 до 6 у 4- му стовпці. Це значення пошуку потім шукали в масиві B2: B11 на аркуші excel, де є дані та відносні позиції можуть зберігатися в кожному рядку стовпця 5 (стовпець E).

Крок 5. Запустіть цей код, натиснувши кнопку F5 або Запустити одночасно і побачити результат. Це майже витягне магію з фрагмента коду з одним рядком.

У цій статті ми дізналися, як ми можемо використовувати функцію MATCH під VBA як особливий випадок WorksheetFunction.

Що потрібно пам’ятати

  • Lookup_value може бути числом / текстом / логічним значенням або може бути посиланням комірки на число, текст або логічне значення.
  • За замовчуванням Match_type може розглядатися як 1, якщо пропущено / не згадується.
  • Як і у функції Excel MATCH, VBA MATCH також надає відносну позицію Lookup_value під Lookup_array, а не саме значення.
  • Якщо відповідність не буде знайдена, відносна комірка Excel буде заповнена # N / A.
  • Якщо функція MATCH використовується для текстових значень, вона не в змозі розмежовувати нижню і верхню регістри. Наприклад, Lalit і lalit однакові. Так само LALIT і lalit.
  • Замісні символи можна використовувати, якщо ви дізнаєтеся точну відповідність (тобто тип відповідності дорівнює нулю). Зірочка символів підстановки (*) може бути використана для пошуку ряду символів. У той час як знак питання (?) Може використовуватися для з'ясування одного символу.

Рекомендовані статті

Це посібник з функції VBA Match. Тут ми обговорюємо VBA Match та як використовувати функцію матчу Excel VBA разом із практичними прикладами та шаблоном Excel, який можна завантажити. Ви також можете ознайомитися з іншими запропонованими нами статтями -

  1. Повне керівництво по помилці VBA
  2. Як використовувати формат номера VBA?
  3. Функція VBA VLOOKUP з прикладами
  4. Створення функції VBA в Excel
  5. Функція відповідності Excel (приклади)

Категорія:

VBA