Формула Excel: проверьте, содержит ли ячейка одно из множества значений
В этом руководстве представлена формула, позволяющая проверить, содержит ли ячейка одно из нескольких значений в Excel, а также объясняются аргументы в формуле и принцип работы формулы.
Общая формула:
| =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))>0 |
аргументы
| Things: the values you want to check if cell contains one of them. |
| Text: the cell reference or text string you want to check if contains number. |
Возвращаемое значение:
Эта формула возвращает логическое значение FALSE: ячейка не содержит ни одного из элементов; ИСТИНА ячейка содержит хотя бы одну из вещей.
Как работает эта формула
Например, вы хотите проверить, содержит ли ячейка B3 число, используйте формулу ниже
| =SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0 |
Нажмите Enter ключ для проверки.
объяснение
ПОИСК функция: функция ПОИСК возвращает расположение первого символа текстовой строки внутри другого. Если функция ПОИСК находит совпавший текст, она возвращает относительную позицию, если нет, она возвращает # ЗНАЧ! ошибка. Например, здесь формула SEARCH($E$3:$E$5,B3) будет искать каждое значение диапазона E3: E5 в ячейке B3 и возвращает расположение каждой текстовой строки в ячейке B3. Он вернет результат в виде массива: {18; # ЗНАЧ!; # ЗНАЧЕНИЕ!}.
Функция ЕЧИСЛО: функция ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число. Так ISNUMBER(SEARCH($E$3:$E$5,B3)) вернет результат массива как {истина, ложь, ложь}, поскольку функция ПОИСК находит 1 число.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) преобразует значение ИСТИНА в 1 и преобразует значение ЛОЖЬ в 0, поэтому эта формула преобразует результат массива в {1; 0; 0}.
SUMPRODUCT функция: используется для умножения диапазонов или суммирования массивов вместе и возвращает сумму продуктов. В SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) возвращает 1 + 0 + 0 = 1.
Наконец, сравните левую формулу SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) и 0, пока результат левой формулы больше 0, формула вернет ИСТИНА или ЛОЖЬ.
Файл примера
Нажмите, чтобы загрузить образец файла
Относительные формулы
- Проверьте, содержит ли ячейка определенный текст
Чтобы проверить, содержит ли ячейка текст в диапазоне 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.
