Формула Excel: частичное совпадение чисел с подстановочным знаком -

Общая формула

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

Резюме

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

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

Это формула массива, и ее нужно вводить с помощью Control + Shift + Enter, кроме Excel 365.

Объяснение

Excel поддерживает подстановочные знаки «*» и «?», И эти подстановочные знаки можно использовать для выполнения частичных (подстрок) совпадений в различных формулах поиска.

Однако, если вы используете подстановочные знаки вместе с числом, вы преобразуете числовое значение в текстовое значение. Другими словами, «*» & 99 & «*» = «* 99 *» (текстовая строка). И если вы попытаетесь найти текстовое значение в диапазоне чисел, совпадение не удастся.

Одним из решений является преобразование числовых значений в текст внутри формулы с помощью функции ТЕКСТ или путем объединения пустой строки в диапазон. Для этого формула в E6:

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

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

Эта формула использует функцию ТЕКСТ для преобразования чисел в B5: B10 в текст с числовым форматом «0». Поскольку мы передаем весь диапазон TEXT, мы возвращаем все значения, преобразованные в текст в массиве, который возвращается непосредственно в функцию MATCH в качестве аргумента массива. Когда числа преобразованы в текст, функция ПОИСКПОЗ может найти частичное совпадение, как обычно.

Обратите внимание, что MATCH должен быть настроен для точного совпадения с использованием подстановочных знаков, установив для третьего аргумента значение 0 или FALSE.