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

В этом руководстве представлена формула для проверки, содержит ли ячейка одно из нескольких значений в Excel, а также подробно разобраны её аргументы и принцип работы.

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

Аргументы

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

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

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

Например, чтобы проверить, содержит ли ячейка B3 число, воспользуйтесь приведённой ниже формулой.

Нажмите клавишу Enter, чтобы выполнить проверку.

Пояснение

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

Функция ЕЧИСЛО возвращает значение ИСТИНА, если ячейка содержит число. Поэтому формула ISNUMBER(SEARCH($E$3:$E$5,B3)) вернёт массив {ИСТИНА;ЛОЖЬ;ЛОЖЬ}, так как функция ПОИСК обнаружила одно совпадение.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) преобразует значение ИСТИНА в 1 и значение ЛОЖЬ в 0, поэтому данная формула преобразует массив в следующий вид:{1;0;0}.

Функция СУММПРОИЗВ: умножает соответствующие элементы заданных массивов и суммирует полученные произведения. Формула SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) возвращает результат 1+0+0=1.

Наконец, результат левой части формулы SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) сравнивается с 0: если он больше 0, формула возвращает ИСТИНА, в противном случае — ЛОЖЬ.

Пример файла

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

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

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