Excel match функция не работает

Функция MATCH (ПОИСКПОЗ) в Excel. Как использовать?

Функция ПОИСКПОЗ в Excel используют для поиска точной позиции искомого значения в списке или массиве данных.

Что возвращает функция

Возвращает число, соответствующее позиции искомого значения.

Синтаксис

=MATCH(lookup_value, lookup_array, [match_type]) – английская версия

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

Аргументы функции

• lookup_value (искомое_значение) – значение, с которым вы хотите сопоставить данные из массива или списка данных;
• lookup_array (просматриваемый_массив) – диапазон ячеек в котором вы осуществляете поиск искомых данных;
• [match_type] ([тип_сопоставления]) – (не обязательно) – этот аргумент определяет каким образом, будет осуществлен поиск. Допустимые значения для аргумента: “-1”, “0”, “1” (подробней читайте ниже).

Дополнительная информация

• Чаще всего функция MATCH используется в сочетании с функцией INDEX (ИНДЕКС);
• Подстановочные знаки могут использоваться в аргументах функции в тех случаях, когда значение поиска – текстовая строка;
• При использовании функции ПОИСКПОЗ регистр букв не учитывается;
• Функция возвращает #N/A ошибку, если искомое значение не найдено;
• Аргумент match_type (тип_сопоставления) определяет каким образом, будет осуществлен поиск:
  – Если аргумент match_type (тип_сопоставления) = 0, то это критерий точного соответствия. Он возвращает первую точную позицию соответствия (или ошибку, если совпадения нет);
  – Если аргумент match_type (тип_сопоставления) = 1 (по умолчанию), то в таком случае данные должны быть отсортированы в порядке возрастания для этой опции. Функция возвращает наибольшее значение, равное или меньшее значения поиска.
  – Если аргумент match_type (тип_сопоставления) = -1, то в таком случае данные должны быть отсортированы в порядке убывания для этой опции. Функция возвращает наименьшее и наибольшее значения поиска.

Примеры использования функции ПОИСКПОЗ в Excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Источник

Application.worksheetFunction.match не работает правильно VBA Excel

Я пытаюсь создать уникальный идентификатор с префиксом. для этого я генерирую случайное число от 0000 до 9999 и добавляю его в конец своего префикса. Затем я хочу проверить лист в столбце A, чтобы увидеть, существует ли уже этот идентификатор, и если да, то он просто генерирует другое случайное число и продолжается до тех пор, пока не будет найдено уникальное. Для этого я использую цикл while. Ниже мой код. Проблема, с которой я столкнулся, заключается в том, что после того, как функции match () ничего не находят, значение остается как последнее значение, то есть: номер строки, в которой было найдено последнее значение. Например, если мой идентификатор — T26 — 7055, он ищет лист и обнаруживает, что он уже существует в строке 59. Затем он генерирует другой идентификатор на этот раз, скажем, T26 -2099, который не существует, но функция сопоставления по-прежнему возвращает 59, а пока циклы просто переходят в бесконечный цикл. Я не понимаю, почему это происходит, и надеялся, что один из вас, ребята, сможет мне помочь.

3 ответа

Вы не сбрасываете match на 0 после того, как найдете совпадение.

Наличие Dim match as Long в начале цикла не сбрасывает его.

Вы используете Application.WorksheetFunction.match. Это вызовет ошибку, если совпадение не будет найдено и вы нашли способ его обойти. Но это также подавит любые обнаруженные законные ошибки.

Если вы используете Application.match, мы сможем отловить ошибку следующим образом:

Это, по крайней мере, даст вам представление об ошибке, с которой вы столкнулись. Также: вы не используете точное соответствие для вашего соответствия. Я предполагаю, что, исходя из вашей истории, вам это понадобится (это может быть причиной ваших проблем. Просто измените последний 0 на 1. Вы получите более точный результат.

Попробуйте переименовать переменную «match» во что-нибудь другое. Match — это имя встроенной функции Excel.

Источник

Почему Excel MATCH() не находит совпадение?

У меня есть таблица с некоторыми номерами, хранящимися в виде текста (коды UPC, поэтому я не хочу потерять ведущие нули). COUNTIF() распознает совпадения только отлично, но MATCH() не работает. Есть ли причина, по которой MATCH() не может обрабатывать числа, хранящиеся в виде текста, или это просто ограничение, с которым мне придется работать?

ОТВЕТЫ

Ответ 1

Такие функции, как MATCH , VLOOKUP и HLOOKUP должны соответствовать типу данных (номеру или тексту), тогда как COUNTIF/SUMIF делают различий. Используете ли вы MATCH для поиска позиции или просто для определения того, существует ли значение в ваших данных?

Если у вас есть числовое значение поиска, вы можете преобразовать текст в формулу с помощью & «», например

. или если это текстовое значение поиска, которое должно соответствовать номерам

Ответ 2

Если вы ищете слово test, например, в ячейке A2, введите следующее:

= ПОИСКПОЗ ( «» & «тест» и «», A2,0)

Если это не работает, тогда попробуйте = Обрезать и = Очистить, чтобы очистить колонку.

Если = Обрезать и = Очистить не помогают, то просто используйте оставшиеся 250 символов. (Поскольку в формуле соответствия может возникнуть тайм-аут/переполнение после 250 символов.)

= Левый (A2, 250)

Ответ 3

Если вы используете имена для обозначения диапазонов, то после того, как вы исправили, типы данных также переопределяют любые имена, относящиеся к этим диапазонам.

Источник

МАТЧ в Excel

Функция Excel MATCH (Содержание)

• МАТЧ в Excel
• МАТЧ Формула в Excel
• Как использовать функцию MATCH в Excel?

МАТЧ в Excel

В Microsoft Excel есть так много различных поисков / справочных функций, которые могут помочь вам найти определенное значение в диапазоне ячеек, и MATCH — одна из них. По сути, он идентифицирует относительную позицию или местоположение любого элемента в диапазоне ячеек. Тем не менее, матч может сделать гораздо больше, чем его чистая сущность.

Функция MATCH используется для поиска местоположения искомого значения в таблице или столбце строки. MATCH находит приблизительные и точные совпадения и подстановочные знаки (*?) Для ограниченных совпадений. Большую часть времени функция INDEX интегрирована с функцией MATCH для получения значения в местоположении, возвращаемом MATCH.

МАТЧ Формула в Excel

Функция MATCH проверяет определенное значение в диапазоне ячеек и возвращает соответствующее местоположение этого значения.

Формула для функции MATCH выглядит следующим образом:

• Lookup_value (обязательно) — значение, которое вы ищете. Это может быть числовое, текстовое или логическое значение, а также ссылка на ячейку.
• Lookup_array (обязательно) — поиск по диапазону ячеек.
• Match_type (необязательно) — объясняет тип соответствия. Это может быть любое из следующих значений: 1, 0, -1. Аргумент match_type при установке значения 0 возвращает точное совпадение, в то время как два других типа значений допускают приблизительное совпадение.

1 или опущено (по умолчанию) — поиск наибольшего значения в массиве поиска, которое меньше или равно значению поиска. Требуется сортировка массива поиска в порядке возрастания, от наименьшего к наибольшему или от А до Я.

0 — находит первое значение в массиве, которое абсолютно равно значению поиска. Сортировка не требуется.

-1 — находит наименьшее значение в массиве, которое равно или превышает значение поиска. Массив поиска должен быть отсортирован в порядке убывания, от наибольшего к наименьшему или от Z до A.

MATCH в Excel — Использование

Используйте функцию MATCH, чтобы получить соответствующее местоположение элемента в массиве. Match имеет разные типы режимов соответствия, что делает его более универсальным, чем функции поиска. Обычно, когда функция MATCH объединяется с INDEX, она может получить значение зафиксированной позиции.

Информация о типе соответствия

• Если 1 соответствует match_type, MATCH находит наибольшее значение, которое равно или меньше, чем lookup_value. Массив lookup_array должен быть отсортирован в порядке возрастания.
• Если 0 соответствует match_type, MATCH находит первое значение, точно равное lookup_value. lookup_array не требует никакой сортировки.
• Если -1 соответствует match_type, MATCH дает наименьшее значение, которое равно или больше

искомое_значение. Массив lookup_array должен быть отсортирован в порядке убывания.

• Если match_type опущен, он принимается равным 1.

Примечание: все типы совпадений обычно находят точное совпадение.

Типы функции MATCH в Excel

1. Точное совпадение

Функция MATCH выполняет точное совпадение, когда тип совпадения установлен на ноль. В приведенном ниже примере формула в E3:

Вы можете скачать этот шаблон Excel функции MATCH здесь — Шаблон Excel функции MATCH

= ПОИСКПОЗ (E2, B3: B10, 0)

Функция MATCH возвращает точное совпадение как 4 .

2. Приблизительное совпадение

MATCH будет выполнять приблизительное сопоставление для значений, отсортированных по AZ, когда тип соответствия установлен на 1, находя наибольшее значение, которое меньше или равно значению поиска. В приведенном ниже примере формула в E3:

MATCH в Excel возвращает приблизительное совпадение как 7.

3. Подстановочный знак

Функция MATCH может выполнять сопоставление с использованием подстановочных знаков, когда тип совпадения установлен на ноль. В приведенном ниже примере формула в E3:

Функция MATCH возвращает результат подстановочных знаков как «pq».

Примечания:

• Функция MATCH не чувствительна к регистру.
• Match возвращает ошибку # N / A, если совпадение не найдено.
• Аргумент lookup_array должен быть в порядке убывания: True, False, ZA, … 9, 8, 7, 6, 5, 4, 3, … и так далее.
• Подстановочные знаки, такие как звездочка и вопросительный знак, можно найти в lookup_value, если match_type равно 0, а lookup_value в текстовом формате,
• Lookup_value может иметь подстановочные символы, такие как звездочка и знак вопроса, если match_type равен 0, а lookup_value является текстом. Звездочка (*) соответствует любому типу последовательности символов; Любой отдельный символ соответствует знаку вопроса (?).

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

Функция MATCH в Excel очень проста и удобна в использовании. Давайте рассмотрим работу функции Match в Excel с некоторыми примерами.

Пример № 1

Чтобы лучше понять функцию MATCH, давайте составим простую формулу, основанную на этих данных: имена планет в столбце A с их позициями. Чтобы выяснить, где находится конкретная планета (скажем, Марс), используйте следующую простую формулу:

Функция MATCH возвращает позицию MARS как 4.

Как вы видите на скриншоте выше, имена планет вводятся в произвольном порядке, и поэтому мы устанавливаем аргумент match_type в 0 (точное совпадение), потому что только этот тип совпадения не требует сортировки значений в массиве поиска. Технически, формула соответствия возвращает относительное положение Марса в диапазоне поиска.

Пример № 2 — Если ячейка содержит одну из многих вещей

Общая формула: (= INDEX (результаты, MATCH (TRUE, ISNUMBER (SEARCH (вещи, A1))), 0)))

Объяснение: Для проверки ячейки на одну из многих вещей и выдачи пользовательского результата для первого найденного соответствия можно использовать функцию INDEX / MATCH, сформированную в функции ПОИСК.

В приведенном ниже примере формула в ячейке C5:

(= INDEX (результаты, MATCH (TRUE, ISNUMBER (ПОИСК (вещи, В5)), 0)))

Поскольку выше приведена формула массива, ее следует вводить с помощью клавиш Control + Shift + Enter .

Как работает эта формула?

Эта формула использует два диапазона имен: E5: E8 назван как «вещи», а F5: F8 назван как «результаты». Убедитесь, что вы используете диапазоны имен с одинаковыми именами (в зависимости от данных). Если вы не хотите использовать диапазоны имен, используйте как абсолютные ссылки.

Основной частью этой формулы является следующий фрагмент:

ISNUMBER (SEARCH (вещи, B5)

Это основано на другой формуле, которая проверяет ячейку на наличие одной подстроки. Если в ячейке есть подстрока, формула дает значение ИСТИНА. Если нет, формула дает ЛОЖЬ.

Пример № 3 — Поиск с использованием наименьшего значения

Общая формула = INDEX (диапазон, MATCH (MIN (значения), значения, 0))

Объяснение: Для поиска информации, связанной с самым низким значением в таблице, вы можете использовать формулу в зависимости от функций MATCH, INDEX и MIN.

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

= ИНДЕКС (В5: В9, MATCH (MIN (С5: С9), С5: C9, 0)))

Как работает эта формула?

Работая изнутри, функция MIN обычно используется, чтобы найти самую низкую ставку в диапазоне C5: C9:

Результат, 99500, подается в функцию MATCH в качестве значения поиска:

Match затем возвращает позицию этого значения в диапазоне 4, который переходит в INDEX в качестве номера строки вместе с B5: B9 в качестве массива:

= INDEX (B5: B9, 4) // возвращает тарелку

Затем функция INDEX возвращает значение в этой позиции: Cymbal .

Ошибки функции соответствия

Если вы получите сообщение об ошибке из функции Match, это может быть ошибкой # N / A:

Кроме того, некоторые пользователи испытывают следующую общую проблему с функцией соответствия:

Вывод

Функция MATCH — это популярная функция, которую большинство из нас использует, не углубляясь в ее механику. Даже после своей тупой цели (чтобы вернуть позицию значения в массиве), функция явно имеет ряд жизнеспособных применений в Excel.

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

Это было руководство по МАТЧ в Excel. Здесь мы обсуждаем формулу MATCH и как использовать функцию MATCH в Excel с функцией INDEX вместе с практическими примерами функций MATCH и загружаемыми шаблонами Excel. Вы также можете взглянуть на эти другие функции поиска и ссылки в Excel —

1. Руководство по НЕПОСРЕДСТВЕННОЙ функции в Excel
2. Использование функции PMT в Excel
3. Как использовать функцию AND в Excel
4. Функция FV в Excel — MS Excel

Источник