Формула Excel: проверьте, содержит ли ячейка одно из нескольких значений, но исключите другие значения
Предположим, есть два списка значений, вы хотите проверить, содержит ли ячейка B3 одно из значений в диапазоне E3: E5, но в то же время она не содержит каких-либо значений в диапазоне F3: F4, как показано ниже. В этом руководстве будет представлена формула для быстрого решения этой задачи в Excel и объяснены аргументы формулы.
Общая формула:
| =(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0) |
аргументы
| Text: the text string you want to check. |
| Include: the values you want to check if argument text contains. |
| Exclude: the values you want to check if argument text does not contain. |
Возвращаемое значение:
Формула возвращает 1 или 0. Когда ячейка содержит одно из значений, которые необходимо включить, и не содержит каких-либо значений, которые необходимо исключить, она возвращает 1 или 0. В этой формуле 1 и 0 обрабатываются как логические значения. Правда и ложь.
Как работает эта формула
Предположим, вы хотите проверить, содержит ли ячейка B3 одно из значений в диапазоне E3: E5, но одновременно исключить значения в диапазоне F3: F4, используйте формулу ниже
| =(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) |
Нажмите 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)) вернет результат массива как {истина, истина, истина}, поскольку функция ПОИСК находит 3 числа.
--ISNUMBER(SEARCH($E$3:$E$5,B3)) преобразует значение ИСТИНА в 1 и преобразует значение ЛОЖЬ в 0, поэтому эта формула изменяет результат массива на {1; 1; 1}.
SUMPRODUCT функция: используется для умножения диапазонов или суммирования массивов вместе и возвращает сумму продуктов. В 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
Формула ПОИСК (3 F $: 4 F $, B3) будет искать каждое значение в диапазоне E3: E5 в ячейке B3 и возвращает расположение каждой текстовой строки в ячейке B3. Он вернет результат в виде массива: {#VALUE!; # VALUE!}.
ISNUMBER(SEARCH($F$3:$F$4,B3)) вернет результат массива как {ложь; ложь} поскольку функция ПОИСК находит число 0.
--ISNUMBER(SEARCH($F$3:$F$4,B3)) преобразует значение ИСТИНА в 1 и преобразует значение ЛОЖЬ в 0, поэтому эта формула изменяет результат массива на {0; 0}.
SUMPRODUCT функция: используется для умножения диапазонов или суммирования массивов вместе и возвращает сумму продуктов. В 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, и вернуть TRUE или FALSE. - Проверить, содержит ли ячейка номер
Иногда вам может потребоваться проверить, содержит ли ячейка числовые символы. В этом руководстве представлена формула, которая вернет ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если ячейка не содержит числа.
Лучшие инструменты для работы в офисе
Kutools for Excel - Помогает вам выделиться из толпы
Kutools for Excel Имеет более 300 функций, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Office Tab - Включить чтение и редактирование с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
