Поиск первого частичного совпадения числа

В некоторых случаях вам может понадобиться найти позицию первого частичного совпадения, содержащего определенное число в диапазоне числовых значений в Excel. В этом случае формула с использованием функций MATCH и TEXT, включающая звездочку (*), которая является подстановочным знаком, соответствующим любому количеству символов, окажет вам помощь. Если вам также нужно знать точное значение на этой позиции, вы можете добавить функцию INDEX к формуле.

Получить позицию первого частичного совпадения числа
Извлечь первое частичное совпадение числа

Получить позицию первого частичного совпадения числа

Чтобы получить позицию первого частичного совпадения числа, содержащего «345», из указанного выше числового диапазона, формулы MATCH и TEXT вместе с подстановочными знаками помогут вам следующим образом: Чтобы найти любые числа, содержащие 345, вы заключите число 345 между двумя звездочками (*). Однако эта операция преобразует числовое значение в текстовое. Поэтому вам придется использовать функцию TEXT для преобразования чисел в числовом диапазоне в текст. Только в этом случае функция MATCH сможет правильно найти позицию частичного совпадения.

Общий синтаксис

=MATCH("*"&число&"*",TEXT(диапазон_поиска,"0"),0)

√ Примечание: Это формула массива, для которой требуется ввод с помощью Ctrl + Shift + Enter.

Чтобы получить позицию первого совпадающего числа, содержащего «345», скопируйте или введите формулы ниже в ячейку E6, и нажмите Ctrl + Shift + Enter, чтобы получить результат:

=MATCH("*"&345&"*",TEXT(B5:B16,"0"),0)

Или используйте ссылку на ячейку, чтобы сделать формулу динамической:

=MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0)

√ Примечание: Чтобы объединить ссылку на ячейку/число и текст, необходимо добавить амперсанд (&) между ними. Текст должен быть заключен в двойные кавычки.

Объяснение формулы

=INDEX("*"&345&"*",TEXT(B5:B16,"0"),0)

• TEXT(B5:B16,"0"): Функция TEXT преобразует все числовые значения в B5:B16 в текст с кодом формата “0”. Таким образом, мы получим текстовый массив следующего вида: {"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"}.
  Нажмите здесь, чтобы узнать больше о функции TEXT.
• INDEX("*"&345&"*""*"&345&"*",TEXT(B5:B16,"0"),0) = INDEX("*"&345&"*""*"&345&"*",{"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"},0): Значение поиска "*"&345&"*" может соответствовать любой текстовой строке, содержащей строку “345”, независимо от того, в каком положении находится 345 в текстовых строках. Параметр match_type 0 требует от функции MATCH найти позицию первого точного значения поиска в массиве. Таким образом, MATCH вернет 5.

Извлечь первое частичное совпадение числа

Чтобы извлечь первое частичное совпадение числа на основе позиции, предоставленной MATCH, как показано ниже, мы можем обратить внимание на функцию INDEX.

Общий синтаксис

=INDEX(MATCH(диапазон_возврата,"*"&число&"*",TEXT(диапазон_поиска,"0"),0))

√ Примечание: Это формула массива, для которой требуется ввод с помощью Ctrl + Shift + Enter.

Чтобы получить первое совпадающее число, содержащее «345», скопируйте или введите формулы ниже в ячейку E7, и нажмите Ctrl + Shift + Enter, чтобы получить результат:

=INDEX(MATCH(B5:B16,"*"&345&"*",TEXT(B5:B16,"0"),0))

Или используйте ссылку на ячейку, чтобы сделать формулу динамической:

=INDEX(MATCH(B5:B16,"*"&E5&"*",TEXT(B5:B16,"0"),0))

Объяснение формулы

=INDEX(B5:B16,MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0))

• MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0) = 5: Нажмите, чтобы увидеть подробный процесс.
• INDEX(B5:B16B5:B16,MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0)) = INDEX(B5:B16B5:B16,5): Функция INDEX возвращает 5-е значение в диапазоне возврата B5:B16, которое равно 34545.

Связанные функции

Связанные формулы