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

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

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

Аргументы

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

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

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

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

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

Пояснение

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

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

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

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

Функция СЧЁТЗ возвращает количество непустых ячеек. Если формула COUNTA($E$3:$E$5) возвращает 3, то результат выражения SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) также равен 3, и вся формула выдаст ИСТИНА; в противном случае — ЛОЖЬ.

Примечания:

Формула =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) выполняет приблизительную проверку. См. скриншот:

Пример файла

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

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

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