Найдите первое частичное совпадение
В некоторых случаях необходимо получить позицию первого частичного совпадения, содержащего определенное число в диапазоне числовых значений в Excel. В этом случае МАТЧ и ТЕКСТ формула, содержащая звездочку (*), подстановочный знак, который соответствует любому количеству символов, окажет вам услугу. И если вам также нужно знать точное значение в этой позиции, вы можете добавить ИНДЕКС функцию к формуле.
Получить позицию первого частичного совпадения
Получить первое частичное совпадение
Получить позицию первого частичного совпадения
Чтобы получить позиция первого частичного совпадения, содержащего «345» из диапазона номеров, как показано выше, формула ПОИСКПОЗ и ТЕКСТ вместе с подстановочными знаками поможет вам: чтобы сопоставить любые числа, содержащие 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)
- ТЕКСТ (B5: B16, «0»): Функция ТЕКСТ преобразует все числовые значения в B5: B16 в текст с кодом формата "0". Итак, мы получим такой текстовый массив: {"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"}.
Щелкните здесь, чтобы узнать больше о функции ТЕКСТ. - ПОКАЗАТЕЛЬ("*" & 345 & "*",ТЕКСТ (B5: B16, «0»), 0) = ИНДЕКС ("*" & 345 & "*",{"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"}, 0): Значение поиска "*" & 345 & "*" может соответствовать любым текстовым строкам, содержащим строку «345», независимо от того, какая позиция 345 находится в текстовых строках. В match_type 0 просит функцию ПОИСКПОЗ найти позицию первого точного искомого значения в массиве. Итак, МАТЧ вернется 5.
Получить первое частичное совпадение
Чтобы получить первый частичный номер совпадения на основе позиции, предоставленной MATCH, как показано ниже, мы можем обратить внимание на функцию INDEX.
Общий синтаксис
=INDEX(MATCH(return_range,"*"&number&"*",TEXT(lookup_array,"0"),0))
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- диапазон_возврата: Диапазон, из которого вы хотите, чтобы комбинационная формула возвращала первое частичное совпадение.
- номер: Номер, который вы указали для поиска его первого частичного совпадения.
- искомый_массив: Диапазон числовых значений, из которого требуется получить первое частичное совпадение.
Чтобы получить номер первого совпадения, содержащий «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 & "*",ТЕКСТ (B5: B16, «0»), 0) = 5: Нажмите, чтобы увидеть подробный процесс.
- ПОКАЗАТЕЛЬ(B5: B16,ПОИСКПОЗ ("*" & E5 & "*",ТЕКСТ (B5: B16, «0»), 0)) = ИНДЕКС (B5: B16,5): Функция ИНДЕКС возвращает 5th значение в возвращаемом диапазоне B5: B16, Которая является 34545.
Связанные функции
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
Функция ТЕКСТ преобразует значение в текст с заданным форматом в Excel.
Функция ИНДЕКС Excel возвращает отображаемое значение на основе заданной позиции из диапазона или массива.
Связанные формулы
Найдите первое частичное совпадение с помощью подстановочных знаков
Чтобы получить позицию первого частичного совпадения, содержащего определенную текстовую строку в диапазоне в Excel, вы можете использовать формулу ПОИСКПОЗ с подстановочными знаками - звездочкой (*) и вопросительным знаком (?).
Чтобы найти наиболее близкое соответствие искомому значению в числовом наборе данных в Excel, вы можете использовать функции ИНДЕКС, ПОИСКПОЗ, АБС и МИН вместе.
Найти значение ближайшего соответствия по нескольким критериям
В некоторых случаях может потребоваться поиск ближайшего или приблизительного значения соответствия на основе нескольких критериев. С помощью комбинации функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы можете быстро сделать это в Excel.
Двустороннее приблизительное соответствие с несколькими критериями
В этом руководстве мы поговорим о том, как искать приблизительное совпадение на основе нескольких критериев, перечисленных в столбцах и строках электронной таблицы Excel, с помощью функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80% времени.
- Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
