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

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

Общая формула:

Аргументы

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

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

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

Например, вы хотите проверить, содержит ли ячейка B3 число. Пожалуйста, используйте следующую формулу:

Нажмите Enter для проверки.

Объяснение

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

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

--ЕЧИСЛО(ПОИСК($E$3:$E$5;B4)) преобразует значение ИСТИНА в 1, а значение ЛОЖЬ в 0, поэтому эта формула преобразует массив результатов в {1;0;0}.

СУММПРОИЗВ: используется для умножения диапазонов или суммирования массивов вместе и возвращает сумму произведений. Функция СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК($E$3:$E$5;B3))) возвращает 1+0+0=1.

В конце концов, сравните левую часть формулы СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК($E$3:$E$5;B3))) с 0, пока результат левой части формулы больше 0, формула вернет ИСТИНА, иначе она вернет ЛОЖЬ.

Пример файла

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

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

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