Перейти к основному содержанию

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

Предположим, есть два списка значений, вы хотите проверить, содержит ли ячейка B3 одно из значений в диапазоне E3: E5, но в то же время она не содержит каких-либо значений в диапазоне F3: F4, как показано ниже. В этом руководстве будет представлена ​​формула для быстрого решения этой задачи в Excel и объяснены аргументы формулы.
doc проверьте, содержат ли они одну из вещей, но исключают 1

Общая формула:

=(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 ключ для получения результата проверки.
doc проверьте, содержат ли они одну из вещей, но исключают 2

объяснение

Часть 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, результатом будет ИСТИНА или ЛОЖЬ. Здесь возвращается ИСТИНА.
doc проверьте, содержат ли они одну из вещей, но исключают 3

Часть 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, результат будет возвращать ИСТИНА или ЛОЖЬ. Здесь возвращается ИСТИНА.
doc проверьте, содержат ли они одну из вещей, но исключают 4

Часть 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 AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...

Описание


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
<p>avec les fonctions en français ça donne : SOMMEPROD(--ESTNUM(CHERCHE(Liste;B2)))</p>
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations