Microsoft Excel:

  Таблицы и VBA. Справочник.
  Вопросы и Ответы. Советы. Примеры.
Меню Заметки | Примеры использования функций Match() , MatchMultiColumn()


Rambler's Top100


Counter CO.KZ
Если необходимо определить позицию искомого элемента в одномерном массиве или диапазоне ячеек, состоящем из одного столбца (или строки), то в таком случае, достаточно использовать стандартную функцию рабочего =ПОИСКПОЗ() описание которой, и примеры, опубликованы ниже :


ПОИСКПОЗ (MATCH)
Возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент.

Синтаксис

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)

Искомое_значение - это значение, используемое при поиске значения в таблице.

Искомое_значение - это значение, для которого ищется соответствие в аргументе просматриваемый_массив. Например, когда Вы ищете номер телефона в телефонной книге, Вы используете имя человека как искомое_значение, но значение, которое Вам нужно получить - это сам номер телефона.

Искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение.

Просматриваемый_массив - это непрерывный интервал ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив.

Тип_сопоставления - это число -1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

  • Если тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА.
  • Если тип_сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть в любом порядке.
  • Если тип_сопоставления равен -1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно и больше чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, -2, ..., и так далее.
  • Если тип_сопоставления опущен, то предполагается, что он равен 1.

    Замечания

  • ПОИСКПОЗ возвращает позицию соответствующего значения в аргументе просматриваемый_массив, а не само значение. Например: ПОИСКПОЗ("б";{"а";"б";"в"};0) возвращает 2 -- относительную позицию буквы "б" в массиве {"а";"б";"в"}.
  • ПОИСКПОЗ не различает регистры при сопоставлении текстов.
  • Если функция ПОИСКПОЗ не находит соответствующего значения, то возвращается значение ошибки #Н/Д.
  • Если тип_сопоставления равен 0 и искомое_значение является текстом, то искомое_значение может содержать символы шаблона, звездочка (*) и знак вопроса (?). Звездочка соответствует любой последовательности символов, знак вопроса соответствует любому одиночному символу.

    Примеры



    Заметим, что ячейки C2:C8 содержат текст, отформатированный как проценты.

    В предшествующем рабочем листе:

    ПОИСКПОЗ(39000;B2:B8;1) равняется 3

    ПОИСКПОЗ(38000;B2:B8;0) равняется 2

    ПОИСКПОЗ(39000;B2:B8;-1) равняется значению ошибки #Н/Д, поскольку интервал B2:B8 неправильно упорядочен для значения аргумента тип_сопоставления, равного -1 (порядок должен быть убывающим для такого сопоставления).

    Предположим, что имя Иена ссылается на ячейки A2:A8, имя ИенаДоллар ссылается на ячейки A2:C8, а имя МойДоход ссылается на ячейку, содержащую число 6 301 126,33.
    Тогда формула:

    "Ваша ставка налога равняется "&ПРОСМОТР(МойДоход;ИенаДоллар)&", что относит Вас к категории налогоплательщиков номер "&ПОИСКПОЗ(МойДоход;Иена)&"."

    выдает следующий результат:

    "Ваша ставка налога равняется 22,41%, что относит Вас к категории налогоплательщиков номер 7."

    Справка © Microsoft Excel 97


    Обратите внимание на то, что ПОИСКПОЗ() позволяет искать нужное значение не только в столбце, но и в строке, иначе говоря, если просматриваемый_массив это диапазон A2:A100, то поиск искомого_значения будет осуществляться в столбце, а если указать A2:X2 , то функция будет просматривать указанную строку. К сожалению, из-за этой универсальности, функция ПОИСКПОЗ() не работает с диапазоном A2:X100 и если Вам необходим поиск именно в таком диапазоне, то используйте нижеопубликованную пользовательскую функцию MatchMultiColumn() где, по умолчанию, перебираются столбцы, а для перебора строк необходимо использовать четвёртый (необязательный) аргумент SearchByColumns передав 0 или ЛОЖЬ

    Примеры



    Предположим, что ячейки E3:G22 содержат числа, а искомое значение, которое находится в ячейке B3, наличествует также в ячейках F16 и G6.

    В таком случае :

    =MatchMultiColumn(B3;E3:G22) равняется 14

    =MatchMultiColumn(B3;E3:G22;;0) равняется 3
  • Public Function MatchMultiColumn(Search As Variant, Diapazon As Range, _
    Optional MatchType% = 0, Optional SearchByColumns As Boolean = True) As Variant
    '*****************************************************************************'
    '          Дата создания : 20/08/2005                                         '
    '          Автор         : Климов Павел Юрьевич                               '
    '          Сайт          : http://www.msoffice.nm.ru                          '
    '          Версия Excel  : 95, 97, 2000, XP, 2003                             '
    '*****************************************************************************'
        Dim tempRange As Range, varPosition As Variant
        If SearchByColumns = True Then
           For Each tempRange In Diapazon.Columns
               varPosition = Application.Match(Search, tempRange, MatchType)
               If IsNumeric(varPosition) = True Then
                  MatchMultiColumn = varPosition
                  Exit Function
               End If
           Next
        Else
           For Each tempRange In Diapazon.Rows
               varPosition = Application.Match(Search, tempRange, MatchType)
               If IsNumeric(varPosition) = True Then
                  MatchMultiColumn = varPosition
                  Exit Function
               End If
           Next
        End If
        
        MatchMultiColumn = CVErr(xlErrNA)
    End Function
    Примечание : Если Вы не используете поиск по строкам, то в примере можно найти упрощённую версию вышеопубликованной пользовательской функции.


    Дополнение : Определить позицию искомого значения в диапазоне, можно также с помощью метода Find об'екта Range, однако, использовать этот метод в функции, вызываемой из ячеек рабочего листа, имеет смысл только начиная с версии Microsoft Excel XP , т.к. в более ранних версиях Find всегда возвращает Nothing ( даже в случае наличия искомого значения в указанном диапазоне )



    Пример можно скачать здесь




    Автор [UDF MatchMultiColumn] : Климов Павел Юрьевич
    © 2004-2016 Климов П.Ю. Все права защищены. WebDesign & Error's Klimoff