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

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

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

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

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

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

=MATCH("*"&,number&,"*",TEXT(lookup_array,"0"),0)

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

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

=ПОИСКПОЗ(«*»&)345&«*»;ТЕКСТ()B5:B16;"0");0)

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

=ПОИСКПОЗ(«*»&)E5&«*»;ТЕКСТ()B5:B16;"0");0)

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

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

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

• TEXT(B5:B16,"0"):Функция ТЕКСТ преобразует все числовые значения в диапазоне B5:B16 в текст с помощью кода формата «0». В результате получится текстовый массив следующего вида:{"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"}.
  Нажмите здесь, чтобы узнать больше о функции ТЕКСТ.
• ИНДЕКС()«*»&345&«*»,TEXT(B5:B16,"0"),0) = ИНДЕКС(«*»&345&«*»Значение поиска «*»&345&«*»может совпадать с любыми текстовыми строками, содержащими подстроку «345», независимо от позиции этой подстроки. Параметр тип_сопоставления 0указывает функции ПОИСКПОЗ найти позицию первого точного совпадения значения поиска в массиве. Таким образом, функция ПОИСКПОЗ вернёт значение 5.

Извлечение первого частичного совпадающего числа

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

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

=INDEX(MATCH(return_range,"*"&,number&,"*",TEXT(lookup_array,"0"),0))

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

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

=ИНДЕКС(ПОИСКПОЗ())B5:B16,«*»&345&«*»;ТЕКСТ()B5:B16;"0");0))

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

=ИНДЕКС(ПОИСКПОЗ())B5:B16,«*»&E5&«*»;ТЕКСТ()B5:B16;"0");0))

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

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

• ПОИСКПОЗ(«*»&E5&«*»,)TEXT(B5:B16,"0"),0)=5:Нажмите, чтобы увидеть подробный процесс.
• ИНДЕКС()B5:B16,ПОИСКПОЗ(«*»&E5&«*»,)TEXT(B5:B16,"0"),0)) = ИНДЕКС(B5:B16Функция ИНДЕКС возвращает 5-е значение из диапазона B5:B16, то есть 34545.

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

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