Excel match функция

5 вариантов использования функции ИНДЕКС (INDEX)

Бывает у вас такое: смотришь на человека и думаешь “что за @#$%)(*?” А потом при близком знакомстве оказывается, что он знает пять языков, прыгает с парашютом, имеет семеро детей и черный пояс в шахматах, да и, вообще, добрейшей души человек и умница?

Так и в Microsoft Excel: есть несколько похожих функций, про которых фраза “внешность обманчива” работает на 100%. Одна из наиболее многогранных и полезных – функция ИНДЕКС (INDEX) . Далеко не все пользователи Excel про нее знают, и еще меньше используют все её возможности. Давайте разберем варианты ее применения, ибо их аж целых пять.

Вариант 1. Извлечение данных из столбца по номеру ячейки

Самый простой случай использования функции ИНДЕКС – это ситуация, когда нам нужно извлечь данные из одномерного диапазона-столбца, если мы знаем порядковый номер ячейки. Синтаксис в этом случае будет:

=ИНДЕКС( Диапазон_столбец ; Порядковый_номер_ячейки )

Этот вариант известен большинству продвинутых пользователей Excel. В таком виде функция ИНДЕКС часто используется в связке с функцией ПОИСКПОЗ (MATCH) , которая выдает номер искомого значения в диапазоне. Таким образом, эта пара заменяет легендарную ВПР (VLOOKUP) :

. но, в отличие от ВПР, могут извлекать значения левее поискового столбца и номер столбца-результата высчитывать не нужно.

Вариант 2. Извлечение данных из двумерного диапазона

Если диапазон двумерный, т.е. состоит из нескольких строк и столбцов, то наша функция будет использоваться немного в другом формате:

=ИНДЕКС( Диапазон ; Номер_строки ; Номер_столбца )

Т.е. функция извлекает значение из ячейки диапазона с пересечения строки и столбца с заданными номерами.

Легко сообразить, что с помощью такой вариации ИНДЕКС и двух функций ПОИСКПОЗ можно легко реализовать двумерный поиск:

Вариант 3. Несколько таблиц

Если таблица не одна, а их несколько, то функция ИНДЕКС может извлечь данные из нужной строки и столбца именно заданной таблицы. В этом случае используется следующий синтаксис:

=ИНДЕКС( (Диапазон1;Диапазон2;Диапазон3) ; Номер_строки ; Номер_столбца ; Номер_диапазона )

Обратите особое внимание, что в этом случае первый аргумент – список диапазонов – заключается в скобки, а сами диапазоны перечисляются через точку с запятой.

Вариант 4. Ссылка на столбец / строку

Если во втором варианте использования функции ИНДЕКС номер строки или столбца задать равным нулю (или просто не указать), то функция будет выдавать уже не значение, а ссылку на диапазон-столбец или диапазон-строку соответственно:

Обратите внимание, что поскольку ИНДЕКС выдает в этом варианте не конкретное значение ячейки, а ссылку на диапазон, то для подсчета потребуется заключить ее в дополнительную функцию, например СУММ (SUM) , СРЗНАЧ (AVERAGE) и т.п.

Вариант 5. Ссылка на ячейку

Общеизвестно, что стандартная ссылка на любой диапазон ячеек в Excel выглядит как Начало-Двоеточие-Конец, например A2:B5. Хитрость в том, что если взять функцию ИНДЕКС в первом или втором варианте и подставить ее после двоеточия, то наша функция будет выдавать уже не значение, а адрес, и на выходе мы получим полноценную ссылку на диапазон от начальной ячейки до той, которую нашла ИНДЕКС:

Нечто похожее можно реализовать функцией СМЕЩ (OFFSET) , но она, в отличие от ИНДЕКС, является волатильной, т.е. пересчитывается каждый раз при изменении любой ячейки листа. ИНДЕКС же работает более тонко и запускает пересчет только при изменении своих аргументов, что ощутимо ускоряет расчет в тяжелых книгах по сравнению со СМЕЩ.

Один из весьма распространенных на практике сценариев применения ИНДЕКС в таком варианте – это сочетание с функцией СЧЁТЗ (COUNTA) , чтобы получить автоматически растягивающиеся диапазоны для выпадающих списков, сводных таблиц и т.д.

Источник: www.planetaexcel.ru

Метод метод WorksheetFunction. ПОИСКПОЗ (Excel) WorksheetFunction.Match method (Excel)

Возвращает относительное положение элемента в массиве, который соответствует заданному значению в указанном порядке. Returns the relative position of an item in an array that matches a specified value in a specified order. Используйте функцию ПОИСКПОЗ вместо одной из функций поиска , если требуется положение элемента в диапазоне, а не самого элемента. Use Match instead of one of the Lookup functions when you need the position of an item in a range instead of the item itself.

Синтаксис Syntax

Expression. ПОИСКПОЗ (согласование) (Arg1, arg2, arg3) expression.Match (Arg1, Arg2, Arg3)

Expression (выражение ) Переменная, представляющая объект метод WorksheetFunction . expression A variable that represents a WorksheetFunction object.

Параметры Parameters

Возвращаемое значение Return value

Double Double

Примечания Remarks

Искомое_значение — это значение, которое нужно найти в аргументе просматриваемый_массив. Lookup_value is the value that you want to match in lookup_array. Например, при поиске номера в телефонной книге используется имя пользователя в качестве значения подстановки, а номер телефона — это требуемое значение. For example, when you look up a number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value that you want.

Искомое_значение может представлять собой значение (число, текст или логическое значение) или ссылку на ячейку с числом, текстом или логическим значением. Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Если тип_сопоставления равен 1, то ПОИСКПОЗ находит наибольшее значение, которое меньше или равно искомое_значение. If match_type is 1, Match finds the largest value that is less than or equal to lookup_value. Просматриваемый_массив должен размещаться в возрастающем порядке. – 2, a – 1, 0, 1, 2. A – Z, FALSE, TRUE. Lookup_array must be placed in ascending order: . -2, -1, 0, 1, 2, . A-Z, FALSE, TRUE.

Если тип_сопоставления равен 0, то ПОИСКПОЗ находит первое значение, точно равное искомое_значение. If match_type is 0, Match finds the first value that is exactly equal to lookup_value. Просматриваемый_массив может быть в любом порядке. Lookup_array can be in any order. Обратите внимание, что в параметре ПОИСКПОЗ регистр не учитывается. Note that Match is case-insensitive.

Если тип_сопоставления равен-1, то ПОИСКПОЗ находит наименьшее значение, которое больше или равно искомое_значение. If match_type is -1, Match finds the smallest value that is greater than or equal to lookup_value. Просматриваемый_массив должен быть упорядочен по убыванию: TRUE, FALSE, Z, A. 2, 1, 0, – 1, – 2. и т. д. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, . 2, 1, 0, -1, -2, . and so on.

Если тип_сопоставления опущен, то предполагается, что он равен 1. If match_type is omitted, it is assumed to be 1.

ПОИСКПОЗ Возвращает позицию сопоставляемого значения в аргументе просматриваемый_массив, а не само значение. Match returns the position of the matched value within lookup_array, not the value itself. Например, MATCH(“b”,<"a","b","c">,0) возвращает 2, относительное положение “b” в массиве <"a","b","c">. For example, MATCH(“b”,<"a","b","c">,0) returns 2, the relative position of “b” within the array <"a","b","c">.

Match не различает прописные и строчные буквы при совпадении текстовых значений. Match does not distinguish between uppercase and lowercase letters when matching text values.

Если **** Поиск не дает результата, возвращается значение ошибки #N/a. If Match is unsuccessful in finding a match, it returns the #N/A error value.

Если тип_сопоставления равен 0, а искомое_значение — текст, можно использовать подстановочные знаки, вопросительный знак (Км) и звездочку (*) в искомое_значение. If match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. Вопросительный знак соответствует одному символу; Звездочка соответствует любой последовательности символов. A question mark matches any single character; an asterisk matches any sequence of characters. Если вы хотите найти фактический вопросительный знак или звездочку, введите тильду (

) перед символом. If you want to find an actual question mark or asterisk, type a tilde (

) before the character.

Пример Example

В этом примере для каждого значения в первом столбце первого листа выполняется поиск совпадающего значения для всей книги. For each value in the first column of the first worksheet, this example searches through the entire workbook for a matching value. Если макрос находит совпадающее значение, исходное значение на первом листе задается полужирным шрифтом. If the macro finds a matching value, it sets the original value on the first worksheet to be bold.

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Источник: docs.microsoft.com

ПОИСКПОЗ (функция ПОИСКПОЗ)

Совет: Попробуйте использовать новую функцию ксматч — улучшенную версию Match, которая работает в любом направлении и возвращает точные совпадения по умолчанию, упрощая и удобную в использовании, чем ее предшественник.

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

Совет: Функцией ПОИСКПОЗ следует пользоваться вместо одной из функций ПРОСМОТР, когда требуется найти позицию элемента в диапазоне, а не сам элемент. Например, функцию ПОИСКПОЗ можно использовать для передачи значения аргумента номер_строки функции ИНДЕКС.

Синтаксис

Аргументы функции ПОИСКПОЗ описаны ниже.

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

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

Просматриваемый_массив Обязательный аргумент. Диапазон ячеек, в которых производится поиск.

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

В приведенной ниже таблице описано, как функция находит значения в зависимости от аргумента тип_сопоставления.

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

Функция ПОИСКПОЗ находит первое значение, равное аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.

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

Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе просматриваемый_массив. Например, функция ПОИСКПОЗ(“б”; <"а”;”б”;”в “>;0) возвращает 2 — относительную позицию буквы “б” в массиве <"а";"б";"в">.

Функция ПОИСКПОЗ не различает регистры при сопоставлении текста.

Если функция ПОИСКПОЗ не находит соответствующего значения, возвращается значение ошибки #Н/Д.

Если тип_сопоставления равен 0 и искомое_значение является текстом, то искомое_значение может содержать подстановочные знаки: звездочку ( *) и вопросительный знак ( ?). Звездочка соответствует любой последовательности знаков, вопросительный знак — любому одиночному знаку. Если нужно найти сам вопросительный знак или звездочку, перед ними следует ввести знак тильды (

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Источник: support.office.com

Excel MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

• lookup_value – The value to match in lookup_array.
• lookup_array – A range of cells or an array reference.
• match_type – [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Use the MATCH function to get the position of a value in an array. Match offers three different matching modes, which makes it more flexible than other lookup functions. When used with INDEX, MATCH can retrieve the value at the matched position.

Match type information

Match type is optional. If not provided, match type defaults to 1 (exact or next smallest). When match type is 1 or -1, it is sometimes referred to as “approximate match”. However, keep in mind that MATCH will find an exact match with all match types, as noted in the table below:

Note: Be sure to set match type to zero (0) if you require an exact match. The default setting of 1 can cause MATCH to return results that look “normal” but are in fact incorrect. I recommend always providing a value for match_type, as a reminder of what behavior is expected.

Basic exact match

When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:

Basic approximate match

When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:

Basic wildcard match

When match type is set to zero (0), MATCH can perform a match using wildcards. In the example shown below, the formula in E3 is:

This is equivalent to:

See below for more examples of the MATCH function.

Источник: exceljet.net

30 функций Excel за 30 дней: ПОИСКПОЗ (MATCH)

Вчера в марафоне 30 функций Excel за 30 дней мы находили текстовые строки при помощи функции SEARCH (ПОИСК), а также использовали IFERROR (ЕСЛИОШИБКА) и ISNUMBER (ЕЧИСЛО) в ситуациях, когда функция выдаёт ошибку.

В 19-й день нашего марафона мы займёмся изучением функции MATCH (ПОИСКПОЗ). Она ищет значение в массиве и, если значение найдено, возвращает его позицию.

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

Функция 19: MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) возвращает позицию значения в массиве или ошибку #N/A (#Н/Д), если оно не найдено. Массив может быть, как сортированный, так и не сортированный. Функция MATCH (ПОИСКПОЗ) не чувствительна к регистру.

Как можно использовать функцию MATCH (ПОИСКПОЗ)?

Функция MATCH (ПОИСКПОЗ) возвращает позицию элемента в массиве, и этот результат может быть использован другими функциями, такими как INDEX (ИНДЕКС) или VLOOKUP (ВПР). Например:

• Найти положение элемента в несортированном списке.
• Использовать вместе с CHOOSE (ВЫБОР), чтобы перевести успеваемость учащихся в буквенную систему оценок.
• Использовать вместе с VLOOKUP (ВПР) для гибкого выбора столбца.
• Использовать вместе с INDEX (ИНДЕКС), чтобы найти ближайшее значение.

Синтаксис MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) имеет следующий синтаксис:

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

• lookup_value (искомое_значение) – может быть текстом, числом или логическим значением.
• lookup_array (просматриваемый_массив) – массив или ссылка на массив (смежные ячейки в одном столбце или в одной строке).
• match_type (тип_сопоставления) – может принимать три значения: -1, или 1. Если аргумент пропущен, это равносильно 1.

Ловушки MATCH (ПОИСКПОЗ)

Функция MATCH (ПОИСКПОЗ) возвращает положение найденного элемента, но не его значение. Если требуется вернуть значение, используйте MATCH (ПОИСКПОЗ) вместе с функцией INDEX (ИНДЕКС).

Пример 1: Находим элемент в несортированном списке

Для несортированного списка можно использовать в качестве значения аргумента match_type (тип_сопоставления), чтобы выполнить поиск точного совпадения. Если требуется найти точное совпадение текстовой строки, то в искомом значении допускается использовать символы подстановки.

В следующем примере, чтобы найти положение месяца в списке, мы можем написать название месяца либо целиком, либо частично с применением символов подстановки.

В качестве аргумента lookup_array (просматриваемый_массив) можно использовать массив констант. В следующем примере искомый месяц введен в ячейку D5, а названия месяцев подставлены в качестве второго аргумента функции MATCH (ПОИСКПОЗ) в виде массива констант. Если в ячейке D5 ввести более поздний месяц, например, Oct (октябрь), то результатом функции будет #N/A (#Н/Д).

Пример 2: Изменяем оценки учащихся c процентов на буквы

Вы можете преобразовать оценки учащихся в буквенную систему, используя функцию MATCH (ПОИСКПОЗ) так же, как Вы делали это с VLOOKUP (ВПР). В этом примере функция использована в сочетании с CHOOSE (ВЫБОР), которая и возвращает нужную нам оценку. Аргумент match_type (тип_сопоставления) принимаем равным -1, поскольку баллы в таблице отсортированы в порядке убывания.

Когда аргумент match_type (тип_сопоставления) равен -1, результатом будет наименьшее значение, которое больше искомого или эквивалентное ему. В нашем примере искомое значение равно 54. Поскольку такого значения нет в списке баллов, то возвращается элемент, соответствующий значению 60. Так как 60 стоит на четвёртом месте списка, то результатом функции CHOOSE (ВЫБОР) будет значение, которое находится на 4-й позиции, т.е. ячейка C6, в которой находится оценка D.

Пример 3: Создаем гибкий выбор столбца для VLOOKUP (ВПР)

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

Пример 4: Находим ближайшее значение при помощи INDEX (ИНДЕКС)

Функция MATCH (ПОИСКПОЗ) отлично работает в сочетании с функцией INDEX (ИНДЕКС), которую мы рассмотрим более пристально чуть позже в рамках данного марафона. В этом примере функция MATCH (ПОИСКПОЗ) использована для того, чтобы найти из нескольких угаданных чисел ближайшее к правильному.

1. Функция ABS возвращает модуль разницы между каждым угаданным и правильным числами.
2. Функция MIN (МИН) находит наименьшую из разниц.
3. Функция MATCH (ПОИСКПОЗ) находит адрес наименьшей разницы в списке разниц. Если в списке есть несколько совпадающих значений, то возвращено будет первое.
4. Функция INDEX (ИНДЕКС) возвращает имя, соответствующее этой позиции, из списка имен.

Источник: office-guru.ru

Excel MATCH Function

The Excel Match and Choose functions both look up information from an array of data.

• The Match function receives a value to lookup, and returns the postion of the value in the array.
• The Choose function receives a position and returns the value that has this position in the array.

Function Description

The Excel Match function looks up a value in an array, and returns the position of the value within the array.

The user can specify that the function should only return a result if an exact match is found, or that the function should return the position of the closest match (above or below), if an exact match is not found.

The syntax of the Match function is:

Where the arguments are as follows:

You can use the following wildcards in the lookup_value for text matches (but only when the [match_type] argument is set to 0):

? – matches any single character
* – matches any sequence of characters

(If you do actually want to find the ? or * character, type the

symbol before this character in your search).

E.g. the condition “A*e” will match all cells containing a text string beginning with “A” and ending in “e”.

The Match function can be used to match numeric values, logical values, or text strings. Note that, when looking up a text string, the function is NOT case-sensitive. So, for example, the text strings “TEXT” and “text” will both be considered to be a match.

Wildcards can be used when the lookup_value is a text string and the [match_type] argument is set to 0 (requiring an exact match). This is illustrated in the examples below.

Excel Match Function Example 1: Text Match

In the following Excel Match function examples, the [match_type] argument is set to 0. Therefore, in these examples, the function only returns a result if an exact match to the lookup_value is found. Otherwise, the function returns an error.

In each case, the lookup_array is the range of cells A1-A5.

Note that, in the above examples, as the [match_type] argument is set to 0, the text strings in the lookup_array (cells A1-A5) do not need to be ordered.

Note also, the use of the wildcard characters ‘?’ and ‘*’ in the examples in cells B2 and B3.

Match Function Example 2: Number Match

The following Excel Match function examples also all have the [match_type] argument set to 0 (requiring an exact match), but in this case the function is used to look up numeric values.

In each of these examples, the lookup_array is the range of cells A1-A6.

As in the previous examples, as the [match_type] argument is set to 0, the values in the lookup_array (cells A1-A6) do not need to be ordered.

Match Function Example 3: Match Closest Value

In the following examples, the [match_type] argument is set to 1. Therefore, the function returns the position of the exact match to the lookup_value if this is found, or the position of the closest value below the lookup_value if an exact match is not found.

Note that, as the [match_type] argument is set to 1, the data in the lookup_array must be in ascending order.

Note that the [match_type] argument could have been omitted from the functions in the above spreadsheet, as this argument has the value 1 by default.

For further information and examples of the Excel Match Function, see the Microsoft Office website.

Match Function Errors

If you get an error from the Excel Match function, this is likely to be the #N/A error:

Occurs if the match function fails to find a match for the lookup_value . This may be either:

If you have checked that you have the correct [match_type] argument value and that the lookup_value (or a closest match) exists in the lookup_array , it may be that the match has failed because either:

• there are unseen characters in either the lookup_value or the values in the lookup_array

or

• the lookup_value and the data in the lookup_array have different data types

Either of these cause the lookup_value and the values in the lookup_array to be seen by Excel as different, and will therefore cause the Match function to fail.

For further details on how to identify and resolve this problem, see the Failure to Match Values page.

Also, some users experience the following common problem with the Excel Match function:

Common Match Function Problem

The Excel Match function returns the wrong result

If your Excel Match function simply returns the wrong result, this may be because the lookup_array is not ordered correctly.

Check the following:

1. Make sure the [match_type] argument is set to the correct value (Note that if this argument is omitted, it will default to 1).
2. If you have set the [match_type] argument to 1 or -1, check that the lookup_array is correctly ordered:

• If [match_type] is set to 1, the lookup_array should be in ascending order;
• If [match_type] is set to -1, the lookup_array should be in descending order.

Источник: www.excelfunctions.net