Note: The other languages of the website are Google-translated. Back to English

Формула 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 for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (1)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
avec les fonctions en français ça donne : SOMMEPROD(--ESTNUM(CHERCHE(Список;B2)))
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места