Поиск первого частичного совпадения числа
В некоторых случаях вам может понадобиться найти позицию первого частичного совпадения, содержащего определенное число в диапазоне числовых значений в 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.
Связанные функции
Функция MATCH в Excel ищет определенное значение в диапазоне ячеек и возвращает относительную позицию этого значения.
Функция TEXT преобразует значение в текст с указанным форматом в Excel.
Функция INDEX в Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Связанные формулы
Найти первое частичное совпадение с использованием подстановочных знаков
Чтобы получить позицию первого частичного совпадения, содержащего определенную текстовую строку в диапазоне в Excel, вы можете использовать формулу MATCH с подстановочными знаками — звездочкой (*) и вопросительным знаком (?).
Чтобы найти ближайшее совпадение для значения поиска в числовом наборе данных в Excel, вы можете использовать функции INDEX, MATCH, ABS и MIN вместе.
Поиск значения ближайшего совпадения с несколькими критериями
В некоторых случаях вам может потребоваться поиск значения ближайшего или приблизительного совпадения на основе более чем одного критерия. С помощью комбинации функций INDEX, MATCH и IF вы сможете быстро выполнить эту задачу в Excel.
Двухстороннее приблизительное совпадение с несколькими критериями
В этом руководстве мы расскажем, как найти приблизительное совпадение на основе нескольких критериев, перечисленных как в столбцах, так и в строках электронной таблицы Excel, с помощью функций INDEX, MATCH и IF.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.