Формула Excel: Проверка, содержит ли ячейка одни текстовые значения, но не содержит другие

Чтобы проверить, содержит ли ячейка текстовые значения из диапазона A, но не содержит тексты из диапазона B, используйте формулу массива, сочетающую функции СЧЁТ, ПОИСК и И в Excel. В этом руководстве подробно объясняется, как применять такую формулу массива.

Универсальная формула:

Аргументы

Возвращаемое значение:

Эта формула возвращает логическое значение: ИСТИНА, если ячейка содержит хотя бы один из аргументов «Включить» и при этом не содержит ни одного из аргументов «Исключить»; в противном случае — ЛОЖЬ.

Как работает эта формула

Допустим, вы хотите проверить, содержит ли ячейка B3 хотя бы одно из значений из диапазона E3:E5, но при этом не содержит ни одного значения из диапазона F3:F4. Воспользуйтесь приведённой ниже формулой.

Нажмите клавиши Shift + Ctrl + Enter, чтобы применить формулу к ячейке.

Пояснение

Часть 1: ( COUNT(SEARCH($E$3:$E$5,B3))>0 проверяет, содержит ли ячейка B3 одно из значений из диапазона E3:E5

Функция ПОИСК возвращает позицию первого символа искомой текстовой строки внутри другой строки. Если совпадение найдено, функция ПОИСК возвращает относительную позицию; если нет — ошибку #ЗНАЧ!. В данном случае формула SEARCH($E$3:$E$5,B3) ищет каждое значение из диапазона E3:E5 в ячейке B3 и возвращает позиции этих подстрок в B3. Результатом будет массив вида: {1;7;#VALUE!}.

Функция СЧЁТ подсчитывает количество числовых значений в диапазоне или массиве. Формула COUNT(SEARCH($E$3:$E$5,B3)) возвращает 2, поскольку в массиве {1;7;#VALUE!} содержится два числовых элемента.

COUNT(SEARCH($E$3:$E$5,B3))>0Сравнивает результат функции СЧЁТ с нулём: если он больше 0, формула возвращает ИСТИНА, в противном случае — ЛОЖЬ. В данном случае результат — ИСТИНА.

Часть 2: COUNT(SEARCH($F$3:$F$4,B3))=0 проверяет, не содержит ли ячейка B3 ни одного из значений из диапазона F3:F4

Функция ПОИСК возвращает позицию первого символа искомой текстовой строки внутри другой строки. Если совпадение найдено, функция ПОИСК возвращает относительную позицию; если нет — ошибку #ЗНАЧ!. В данном случае формула SEARCH($F$3:$F$4,B3) ищет каждое значение из диапазона F3:F4 в ячейке B3 и возвращает позиции этих строк в ячейке B3. Результатом будет массив вида: {#VALUE!; #VALUE!; #VALUE!}.

Функция СЧЁТ подсчитывает количество числовых значений в диапазоне или массиве. Формула COUNT(SEARCH($F$3:$F$4,B3)) возвращает 0, поскольку в массиве {#VALUE!; #VALUE!; #VALUE!} отсутствуют числовые элементы.

COUNT(SEARCH($F$3:$F$4,B3))>0Сравнивает результат функции СЧЁТ с нулём: если он равен 0, формула возвращает ИСТИНА, в противном случае — ЛОЖЬ. В данном случае результат — ИСТИНА.

Часть 3:функция И

=AND(COUNT(SEARCH($E$3:$E$5,B3))>,0,COUNT(SEARCH($F$3:$F$4,B3))=0)

=AND(TRUE,TRUE)

=TRUE

Пример файла

Нажмите, чтобы скачать пример файла

Связанные формулы

• Проверка, содержит ли ячейка определённый текст
  Чтобы проверить, содержит ли ячейка любой текст из диапазона A, но при этом не содержит ни одного текста из диапазона B, используйте формулу массива, объединяющую функции СЧЁТ, ПОИСК и И в Excel.
• Проверка, содержит ли ячейка одно из нескольких значений, но исключает другие
  В этом руководстве представлена формула для быстрой проверки: содержит ли ячейка хотя бы одно из заданных значений, но при этом не включает другие. Также подробно разобраны аргументы этой формулы.
• Проверка, содержит ли ячейка одно из значений
  Допустим, у вас в Excel есть список значений в столбце E. Вы хотите проверить, содержат ли ячейки в столбце B хотя бы одно из значений из столбца E, и получить результат ИСТИНА или ЛОЖЬ.
• Проверка, содержит ли ячейка число
  Иногда возникает необходимость проверить, содержит ли ячейка числовые значения. В этом руководстве представлена формула, которая возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ — если не содержит.