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