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

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

Чтобы проверить, содержит ли ячейка текст в диапазоне A, но не содержит ли текст в диапазоне B, вы можете использовать формулу массива, которая объединяет функции СЧЁТ, ПОИСК и И в Excel. В этом руководстве объясняется, как использовать эту формулу массива.
документ проверьте, содержит ли он одни, но нет других 1

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

=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 + Enter ключ для проверки ячейки.
документ проверьте, содержит ли он одни, но нет других 2

объяснение

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

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

Часть 3: И функция

=AND(COUNT(SEARCH($E$3:$E$5,B3))>0,COUNT(SEARCH($F$3:$F$4,B3))=0)

=AND(TRUE,TRUE)

=TRUE

Файл примера

образец документаНажмите, чтобы загрузить образец файла


Относительные формулы


Лучшие инструменты для работы в офисе

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)
Сортировать комментарии по
Комментарии (3)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Есть ли способ объединить этот инструмент и другие, чтобы помочь со следующим примером?
На странице 1, столбец A, ячейка A: сумма всех значений, найденных в диапазоне страницы 2.
Этот комментарий был сведен к минимуму модератором на сайте
...Страница 2, диапазон столбцов A15–A25, если соответствующая ячейка на странице 2, диапазон столбцов B15–B25 содержит слово «Яблоки».
Этот комментарий был сведен к минимуму модератором на сайте
Привет, GoodEvening, я думаю, вы хотите суммировать значения в столбце A, если соответствующие ячейки в столбце B являются словом «яблоки», как показано на скриншоте ниже, если это так, вы можете обратиться к ниже toturial, он предоставляет формулу для решения этой проблема в деталях.https://www.extendoffice.com/excel/formulas/excel-sum-if-cell-equals-value-does-not-equal.html
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места