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

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

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

Аргументы

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

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

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

Например, в столбце B есть список текстовых строк, которые вы хотите проверить на наличие всех значений в диапазоне E3:E5, используйте следующую формулу.

Нажмите Enter клавишу, протяните маркер заполнения по ячейкам, которые вы хотите проверить. ЛОЖЬ указывает, что ячейка не содержит все значения в диапазоне E3:E5, а ИСТИНА указывает, что соответствующая ячейка содержит все значения.

Объяснение

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

Функция ЕЧИСЛО: функция ЕЧИСЛО проверяет, является ли значение числом, и возвращает ИСТИНА или ЛОЖЬ. Здесь ЕЧИСЛО(ПОИСК($E$3:$E$5;B4)) вернет массив результатов как {true;true;true} так как функция ПОИСК находит 3 числа.

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

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

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

Примечания:

Формула =СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(значения;текст)))=СЧЁТЗ(значения) не является точной проверкой. Смотрите скриншот:

Пример файла

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

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

• Подсчет ячеек, равных
  С помощью функции СЧЁТЕСЛИ вы можете подсчитать ячейки, равные или не содержащие определенное значение.
• Подсчет ячеек, равных x или y
  Иногда вам может понадобиться подсчитать количество ячеек, соответствующих одному из двух критериев, в этом случае вы можете использовать функцию СЧЁТЕСЛИ.
• Подсчет ячеек, равных x и y
  В этой статье описывается формула для подсчета ячеек, которые одновременно соответствуют двум критериям.
• Подсчет ячеек, не равных
  В этой статье рассказывается, как использовать функцию СЧЁТЕСЛИ для подсчета количества ячеек, не равных определенному значению.