Найдите первое совпадение, которое не содержит
Чтобы найти позицию первого совпадения, не содержащего определенную строку, можно использовать формулу, основанную на МАТЧ, ISNUMBER и ПОИСК функции.
Как найти первое совпадение, не содержащее строку «ИНСТРУМЕНТ»?
Для того, чтобы найти позиция первого значения, не содержащего строки «ИНСТРУМЕНТ» в приведенном выше списке вы можете использовать функции SEARCH и ISNUMBER для создания массива TRUE и FALSE, в котором TRUE представляет значение, содержащее строку «ИНСТРУМЕНТ», а FALSE представляет значение, не содержащее строку. Затем функция ПОИСКПОЗ будет сопоставлять первое ЛОЖЬ в массиве, чтобы вернуть позицию первого значения, не содержащего строку «ИНСТРУМЕНТ».
Общий синтаксис
=MATCH(FALSE,ISNUMBER(SEARCH(lookup_value,range)),0)
√ Примечание. Это формула массива, требующая ввода с помощью Ctrl + Shift + Enter.
- искомое_значение: Указанная вами строка соответствует первому значению, которое ее не содержит. Строка должна быть заключена в двойные кавычки, иначе результат будет неверным. Или вы можете использовать ссылку на ячейку, чтобы избежать добавления двойных кавычек.
- ассортимент: Список, из которого следует вернуть первое значение, не содержащее искомое_значение.
- 0: Ассоциация тип_соответствия 0 заставляет ПОИСКПОЗ выполнить точное совпадение.
Для того, чтобы найти позиция первого значения, не содержащего строки «ИНСТРУМЕНТ», скопируйте или введите формулу ниже в ячейку E5 и нажмите Ctrl + Shift + Enter чтобы получить результат:
=ПОИСКПОЗ(ЛОЖЬ,ЧИСЛО(ПОИСК("ИНСТРУМЕНТ",B5: B11))0)
Или используйте ссылку на ячейку, чтобы сделать формулу динамической:
=ПОИСКПОЗ(ЛОЖЬ,ЧИСЛО(ПОИСК(E4,B5: B11))0)
Пояснение формулы
=MATCH(FALSE,(ISNUMBER(SEARCH(E4,B5:B11)),0)
- ПОИСК(E4,B5:B11): Функция ПОИСК ищет строку ИНСТРУМЕНТ (значение в E4) в каждом значении в диапазоне B5: B11и возвращает номера начальной позиции строки в этих значениях в массиве: {3;3;4;#VALUE!;1;3;#VALUE!}.
- ISNUMBER (ПОИСК(E4,B5:B11)) = ISNUMBER ({3;3;4;#VALUE!;1;3;#VALUE!}): ISNUMBER превращает числа в массиве в TRUE, а другие значения в FALSE. Итак, результат будет таким: {ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}.
- СОВПАДЕНИЕ(ЛОЖЬ,(ISNUMBER (ПОИСК(E4,B5:B11)),0) = СОВПАДЕНИЕ(ЛОЖЬ,({ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}, 0): Ассоциация тип_соответствия 0 заставляет функцию ПОИСКПОЗ выполнять точное совпадение. Затем функция возвращает позицию первого точного FALSE в массиве. Итак, функция вернет 4 так как первый FALSE находится в 4-я позиция.
Что необходимо знать
- Формула не чувствительна к регистру. Чтобы выполнить совпадение с учетом регистра, см. руководство здесь.
- Чтобы получить значение в позиции, предоставленной ПОИСКПОЗОМ, вы можете добавить функцию ИНДЕКС.
Связанные функции
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
В Excel функция ПОИСК может помочь вам найти положение определенного символа или подстроки в заданной текстовой строке, как показано на следующем снимке экрана. В этом руководстве я расскажу, как использовать эту функцию ПОИСК в Excel.
Связанные формулы
Найдите первое частичное совпадение с помощью подстановочных знаков
Чтобы получить позицию первого частичного совпадения, содержащего определенную текстовую строку в диапазоне в Excel, вы можете использовать формулу ПОИСКПОЗ с подстановочными знаками - звездочкой (*) и вопросительным знаком (?).
Найдите первое совпадение, которое не начинается с
Чтобы найти позицию первого совпадения, которое не начинается с определенной строки, вам могут помочь функции ПОИСКПОЗ, ВЛЕВО и ЕСЛИ.
Чтобы найти позицию первой ошибки в столбце или строке, вам могут помочь функции ПОИСКПОЗ и ЕОШИБКА.
Найдите максимальное значение в диапазоне
Бывают случаи, когда вам нужно найти положение максимального значения в списке, таблице или строке в Excel. В этом руководстве мы обсудим подход к выполнению задачи с помощью функций MAX и MATCH.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
