Формула Excel: проверьте, содержит ли ячейка одни тексты, но нет ли других
Чтобы проверить, содержит ли ячейка текст в диапазоне A, но не содержит ли текст в диапазоне B, вы можете использовать формулу массива, которая объединяет функции СЧЁТ, ПОИСК и И в Excel. В этом руководстве объясняется, как использовать эту формулу массива.
Общая формула:
| =AND(COUNT(SEARCH(include,text))>0,COUNT(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. |
Возвращаемое значение:
Эта формула возвращает логическое значение. Если ячейка содержит хотя бы один из аргументов Include, но не содержит ни одного из аргументов Exclude, она вернет TRUE или FALSE.
Как работает эта формула
Предположим, вы хотите проверить, содержит ли ячейка B3 одно из значений в диапазоне E3: E5, но в то же время она не содержит никаких значений в диапазоне F3: F4, используйте формулу ниже
| =AND(COUNT(SEARCH($E$3:$E$5,B3))>0,COUNT(SEARCH($F$3:$F$4,B3))=0) |
Нажмите Shift + Ctrl + Ввод ключ для проверки ячейки.
объяснение
Часть 1: ( COUNT(SEARCH($E$3:$E$5,B3))>0 проверьте, содержит ли ячейка B3 одно из значений в диапазоне E3: E5
ПОИСК функция: функция ПОИСК возвращает позицию первого символа текстовой строки внутри другой, если функция ПОИСК находит совпавший текст, она возвращает относительную позицию, если нет, она возвращает # ЗНАЧ! ошибка. Здесь формула SEARCH($E$3:$E$5,B3) будет искать каждое значение в диапазоне E3: E5 в ячейке B3 и возвращать расположение каждой текстовой строки в ячейке B3. Он вернет результат в виде массива: {1;7;#VALUE!}.
СЧИТАТЬ функция: функция COUNT подсчитывает количество элементов в диапазоне или массиве. COUNT(SEARCH($E$3:$E$5,B3)) возвращает 2 как количество элементов в массиве {1;7;#VALUE!} два.
COUNT(SEARCH($E$3:$E$5,B3))>0, сравните результат функции COUNT и ноль, если результат функции COUNT больше 0, результат будет возвращать ИСТИНА или ЛОЖЬ. Здесь возвращается ИСТИНА.
Часть 2: COUNT(SEARCH($F$3:$F$4,B3))=0 проверьте, не содержит ли ячейка B3 одно из значений в диапазоне F3: F4
ПОИСК функция: функция ПОИСК возвращает позицию первого символа текстовой строки внутри другого, если функция ПОИСК находит совпавший текст, она возвращает относительную позицию; в противном случае возвращается #VALUE! ошибка. Здесь формула SEARCH($F$3:$F$4,B3) будет искать каждое значение диапазона F3: F4 в ячейке B3 и возвращает расположение каждой текстовой строки в ячейке B3. Он вернет результат в виде массива: {#VALUE!; #VALUE!;#VALUE!}.
СЧИТАТЬ функция: функция COUNT подсчитывает количество элементов в диапазоне или массиве. COUNT(SEARCH($F$3:$F$4,B3)) возвращает 0, поскольку в массиве нет элементов {#VALUE!; #VALUE!;#VALUE!} .
COUNT(SEARCH($F$3:$F$4,B3))>0, сравните результат функции COUNT и ноль, если результат функции COUNT равен 0, она вернет ИСТИНА или ЛОЖЬ. Здесь возвращается ИСТИНА.
Часть 3: И функция
=AND(COUNT(SEARCH($E$3:$E$5,B3))>0,COUNT(SEARCH($F$3:$F$4,B3))=0)
=AND(TRUE,TRUE)
=TRUE
Файл примера
Нажмите, чтобы загрузить образец файла
Относительные формулы
- Проверьте, содержит ли ячейка определенный текст
Чтобы проверить, содержит ли ячейка текст в диапазоне A, но не содержит ли текст в диапазоне B, вы можете использовать формулу массива, которая объединяет функции СЧЁТ, ПОИСК и И в Excel. - Проверить, содержит ли ячейка одно из нескольких значений, но исключить другие значения
В этом руководстве будет представлена формула для быстрого решения задачи, которая проверяет, содержит ли ячейка одно из элементов, но исключает другие значения в Excel, и объясняет аргументы формулы. - Проверьте, содержит ли ячейка что-либо из
Предположим, что в Excel есть список значений в столбце E, вы хотите проверить, содержат ли ячейки в столбце B все значения в столбце E, и вернуть TRUE или FALSE. - Проверить, содержит ли ячейка номер
Иногда вам может потребоваться проверить, содержит ли ячейка числовые символы. В этом руководстве представлена формула, которая вернет ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если ячейка не содержит числа.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
