Метод WorksheetFunction.Match — аналог функции ПОИСКПОЗ в VBA Excel — поиск относительного положения элемента в массиве (диапазоне). Примеры.
Метод WorksheetFunction.Match
Обратите внимание на то, что
- поиск позиции в массиве возможен, если он одномерный или двумерный, но с набором элементов только по одному измерению, например: myArray(8, 0) или myArray(1 To 1, 1 To 20);
- поиск позиции в диапазоне рабочего листа возможен, если он содержит только одну строку или один столбец;
- нумерация относительного положения элемента в массиве начинается с единицы, независимо от заданной индексации массива.
Синтаксис
Синтаксис метода WorksheetFunction.Match в VBA Excel:
|
1 |
WorksheetFunction.Match (Arg1, Arg2, [Arg3]) |
Параметры
Описание параметров метода WorksheetFunction.Match:
| Параметр | Описание |
|---|---|
| Arg1 | Обязательный параметр. Значение элемента массива, по которому будет произведен поиск относительного положения элемента в массиве. |
| Arg2 | Обязательный параметр. Непрерывный диапазон ячеек или массив, в котором будет произведен поиск позиции элемента, значение которого совпадет со значением параметра Arg1. |
| Arg3 | Необязательный параметр. Задает тип сопоставления значения Arg1 со значениями в массиве Arg2. |
В параметре Arg1 можно использовать знаки подстановки для шаблонов, те же, что и для методов Find и Replace.
Значения параметра Arg3, задающие тип сопоставления:
| Значение | Тип сопоставления |
|---|---|
| -1 | Метод WorksheetFunction.Match находит наименьшее значение элемента в Arg2, большее или равное Arg1. Значения элементов Arg2 должны быть расположены в убывающем порядке: [m, … 2, 1, 0, -1, -2, … -n], [z – a], [True, False] и т. д. |
| 0 | Метод WorksheetFunction.Match находит в Arg2 первое значение, равное Arg1. Значения элементов Arg2 могут быть расположены в любом порядке. |
| 1 | Значение по умолчанию. Метод WorksheetFunction.Match находит наибольшее значение элемента в Arg2, меньшее или равное Arg1. Значения элементов Arg2 должны быть расположены в возрастающем порядке: [-n, … -2, -1, 0, 1, 2, … m], [a – z], [False, True] и т. д. |
При сопоставлении строк регистр не учитывается.
Примеры
Пример 1
Поиск относительного положения элемента в массиве, индексация которого начинается с нуля:
|
1 2 3 4 5 6 7 8 9 10 |
Sub Primer1() Dim myArray As Variant, n As Long 'заполнение массива значениями, нумерация элементов массива начинается с нуля myArray = Array("Арка", 45, "Дуб", "Клуб", 85.37, "Литр", 103, "Небо", "Столб") 'определяем относительное положение элемента в массиве, при чем 'нумерация относительного положения начинается с единицы n = WorksheetFunction.Match("Клуб", myArray) MsgBox n 'Результат: 4 MsgBox myArray(n) 'Результат: 85.37, так как нумерация массива начинается с нуля End Sub |
Пример 2
Определение индекса элемента в массиве по его относительному положению, возвращенному методом WorksheetFunction.Match:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub Primer2() Dim myArray(7 To 15) As Variant, i As Long, n As Long 'заполнение элементов массива значениями For i = 7 To 15 myArray(i) = Choose(i, "", "", "", "", "", "", "Арка", 45, "Дуб", "Клуб", 85.37, "Литр", 103, "Небо", "Столб") Next n = WorksheetFunction.Match("Клуб", myArray) MsgBox n 'Результат: 4 'находим индекс элемента в массиве по его относительному положению n = n + LBound(myArray) - 1 MsgBox myArray(n) 'Результат: Клуб End Sub |
Пример 3
Определение адреса ячейки на рабочем листе по найденному методом WorksheetFunction.Match относительному положению этой ячейки в заданном диапазоне:
|
1 2 3 4 5 6 7 8 9 10 |
Sub Primer3() Dim n As Long n = WorksheetFunction.Match("Брелок", Range("B1400:B1410"), 0) With Range("B1400:B1410") MsgBox "Значение = " & .Cells(n) & vbNewLine & _ "Адрес = " & .Cells(n).Address & vbNewLine & _ "Строка = " & .Cells(n).Row & vbNewLine & _ "Столбец = " & .Cells(n).Column End With End Sub |
Здравствуйте, Евгений! Здравствуйте, коллеги!
Почему код
даёт n=14 (кол-во элементов myArray) вместо верных 9?
А если кол-во элементов myArray сократить до 13, удалив
«, "14?"», то 13.А при 12 тот же код даёт верную 9.
Что это за нечистая сила?
P.S. «при чем» лучше заменить на «причём»
Здравствуйте!
Если тип сопоставления используется по умолчанию — 1, элементы массива должны быть расположены по возрастанию, в том числе и текстовые значения. Для вашего примера следует использовать тип сопоставления — 0:
Спасибо, Евгений.
Как пользоваться, более-менее понятно.
Как работает при 1 (по умолчанию) – не до конца: почему при 12 элементах для «Столб» n=9, а не ожидаемым 12; почему при 14 для «Небо» n=8, а не 13. Это всё при типе сопоставления = 1.
Здравствуйте, Евгений!
Здравствуйте, коллеги!
А работает ли Match с датами? Что-то у меня при выполнении
Sub tmp()
Dim ThAy(4) As Date, fff As Date
ThAy(0) = "1.1.2000"
ThAy(1) = "2.8.2009"
fff = "02.08.2009"
Debug.Print Application.Match(fff, ThAy, 0), Application.Match(fff, ThAy), WorksheetFunction.Match(fff, ThAy), fff, ThAy(1), WorksheetFunction.Match("то, чего не м.б.", ThAy), TypeName(ThAy), TypeName(ThAy(1)), TypeName(fff)
End Sub
любая дата, даже известно находящаяся в массиве, даже известно не находящаяся, даже «то, чего не м.б.», находится. На Ubound+1 месте массива.
Прошу объяснения или совета, в каком направлении искать.
Спасибо.
Добрый вечер!
В вашем примере возвращается порядковый номер, соответствующий элементу массива Ubound, так как нумерация массива идет с нуля, а порядковый номер, возвращаемый функцией Match, с единицы.
Почему-то с датами Match не работает. Могу предложить такой вариант:
А также можно использовать текстовый формат:
Обсуждение закрыто.