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

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

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

Аргументы

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

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

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

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

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

Пояснение

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

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

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

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

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

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

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

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

ISNUMBER(SEARCH($F$3:$F$4,B3)) вернёт массив {ЛОЖЬ;ЛОЖЬ}, так как функция ПОИСК не нашла ни одного совпадения.

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

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

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

Часть 3: Перемножение двух формул

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>,0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

В этой формуле значения 1 и 0 интерпретируются как логические ИСТИНА и ЛОЖЬ.

Пример файла

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

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

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