Формула Excel: Проверка, содержит ли ячейка все из множества значений
Предположим, в Excel есть список значений в столбце E, и вы хотите проверить, содержат ли ячейки в столбце B все значения из столбца E, возвращая ИСТИНА или ЛОЖЬ, как показано на скриншоте ниже. В этом руководстве представлена формула для выполнения этой задачи.
Общая формула:
=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(значения;текст)))=СЧЁТЗ(значения) |
Аргументы
Значения: список значений, которые вы хотите использовать для проверки, содержит ли аргумент текст. |
Текст: ячейка или строка текста, которую вы хотите проверить на наличие аргумента значения. |
Возвращаемое значение:
Эта формула возвращает логическое значение, ЛОЖЬ: ячейка не содержит все значения; ИСТИНА: ячейка содержит все значения.
Как работает эта формула
Например, в столбце B есть список текстовых строк, которые вы хотите проверить на наличие всех значений в диапазоне E3:E5, используйте следующую формулу.
=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК($E$3:$E$5;B3)))=СЧЁТЗ($E$3:$E$5) |
Нажмите Enter клавишу, протяните маркер заполнения по ячейкам, которые вы хотите проверить. ЛОЖЬ указывает, что ячейка не содержит все значения в диапазоне E3:E5, а ИСТИНА указывает, что соответствующая ячейка содержит все значения.
Объяснение
Функция ПОИСК: функция ПОИСК вернет позицию первого символа текстовой строки внутри другой. Если функция ПОИСК находит текст, она возвращает позицию, если нет — возвращает ошибку #ЗНАЧ!. Например, здесь формула ПОИСК($E$3:$E$5;B4) найдет каждое значение диапазона E3:E5 в ячейке B4 и вернет позицию каждой текстовой строки в ячейке B4. Она вернет массив результатов следующего вида: {1;7;12}
Функция ЕЧИСЛО: функция ЕЧИСЛО проверяет, является ли значение числом, и возвращает ИСТИНА или ЛОЖЬ. Здесь ЕЧИСЛО(ПОИСК($E$3:$E$5;B4)) вернет массив результатов как {true;true;true} так как функция ПОИСК находит 3 числа.
--ЕЧИСЛО(ПОИСК($E$3:$E$5;B4)) преобразует значение ИСТИНА в 1, а значение ЛОЖЬ в 0, поэтому эта формула меняет массив результатов на {1;1;1}.
Функция СУММПРОИЗВ: используется для умножения диапазонов или суммирования массивов вместе и возвращает сумму произведений. Функция СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК($E$3:$E$5;B4))) возвращает 1+1+1=3.
Функция СЧЁТЗ: возвращает количество непустых ячеек. СЧЁТЗ($E$3:$E$5) возвращает 3, так что результат СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК($E$3:$E$5;B4))) равен 3, вся формула вернет ИСТИНА, иначе вернет ЛОЖЬ.
Примечания:
Формула =СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(значения;текст)))=СЧЁТЗ(значения) не является точной проверкой. Смотрите скриншот:
Пример файла
Нажмите, чтобы скачать пример файла
Связанные формулы
- Подсчет ячеек, равных
С помощью функции СЧЁТЕСЛИ вы можете подсчитать ячейки, равные или не содержащие определенное значение. - Подсчет ячеек, равных x или y
Иногда вам может понадобиться подсчитать количество ячеек, соответствующих одному из двух критериев, в этом случае вы можете использовать функцию СЧЁТЕСЛИ. - Подсчет ячеек, равных x и y
В этой статье описывается формула для подсчета ячеек, которые одновременно соответствуют двум критериям. - Подсчет ячеек, не равных
В этой статье рассказывается, как использовать функцию СЧЁТЕСЛИ для подсчета количества ячеек, не равных определенному значению.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.