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

Как проверить, содержит ли ячейка одно из нескольких значений в Excel?

Предположим, у вас есть список текстовых строк в столбце A, теперь вы хотите проверить каждую ячейку, если она содержит одно из нескольких значений на основе другого диапазона D2: D7. Если он содержит какой-либо конкретный текст в D2: D7, он будет отображать True, в противном случае он покажет False, как показано на следующем снимке экрана. В этой статье я расскажу о том, как определить ячейку, если она содержит одно из нескольких значений в другом диапазоне.


Проверить, содержит ли ячейка одно из нескольких значений из списка с формулами

Чтобы проверить, содержит ли содержимое ячейки какое-либо из текстовых значений в другом диапазоне, вам могут помочь следующие формулы, сделайте следующее:

Введите приведенную ниже формулу в пустую ячейку, в которой вы хотите найти результат, например B2, затем перетащите дескриптор заполнения вниз к ячейкам, которые вы хотите применить к этой формуле, и если ячейка имеет какое-либо из текстовых значений в другой конкретный диапазон, он получит True, в противном случае он получит False. Смотрите скриншот:

=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0

Советы:

1. Если вы хотите использовать «Да» или «Нет» для обозначения результата, примените следующую формулу, и вы получите следующий результат по мере необходимости, см. Снимок экрана:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No")

2. В приведенных выше формулах D2: D7 - это конкретный диапазон данных, на основе которого вы хотите проверить ячейку, и A2 это ячейка, которую вы хотите проверить.


Отображение совпадений, если ячейка содержит одно из нескольких значений из списка с формулами

Sotimes, вы можете проверить, содержит ли ячейка значение в списке, а затем вернуть это значение, если несколько значений совпадают, то все совпадающие значения в списке отображаются, как показано на скриншоте ниже. Как вы могли бы решить эту задачу в Excel?

Чтобы отобразить все совпадающие значения, если ячейка содержит один из конкретных текстов, примените следующую формулу:

=TEXTJOIN(", ", TRUE, IF(COUNTIF(A2, "*"&$D$2:$D$7&"*"), $D$2:$D$7, ""))

Внимание: В приведенной выше формуле D2: D7 - это конкретный диапазон данных, на основе которого вы хотите проверить ячейку, и A2 это ячейка, которую вы хотите проверить.

Затем нажмите на Shift + Ctrl + Enter вместе, чтобы получить первый результат, а затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, см. снимок экрана:

Советы:

Вышеупомянутая функция TEXTJOIN доступна только для Excel 2019 и Office 365, если у вас более ранние версии Excel, вы должны применить следующую формулу:

=IFERROR(INDEX($D$2:$D$7, SMALL(IF(COUNTIF($A2, "*"&$D$2:$D$7&"*"), MATCH(ROW($D$2:$D$7), ROW($D$2:$D$7)), ""), COLUMNS($F$1:F1))), "")

Внимание: В приведенной выше формуле D2: D7 - это конкретный диапазон данных, на основе которого вы хотите проверить ячейку, и A2 это ячейка, которую вы хотите проверить.

Затем нажмите на Shift + Ctrl + Enter вместе, чтобы получить первый результат, а затем перетащите ячейку формулы в правую сторону, пока не отобразится пустая ячейка, а затем продолжайте перетаскивать дескриптор заполнения вниз в другие ячейки, и все соответствующие значения будут отображены, как показано ниже:


Выделите совпадения, если ячейка содержит одно из нескольких значений из списка с удобной функцией

Если вы хотите выделить определенный цвет шрифта для совпадающих значений, если ячейка содержит одно из нескольких значений из другого списка, в этом разделе я представлю простую функцию, Отметить ключевое слово of Kutools for Excel, с помощью этой утилиты вы можете сразу выделить в ячейках одно или несколько ключевых слов.

Примечание:Чтобы применить эти Отметить ключевое слово функции, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените функции.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс > Текст > Отметить ключевое слово, см. снимок экрана:

2. В Отметить ключевое слово диалоговом окне выполните следующие действия:

  • Выберите диапазон данных, в котором вы хотите выделить совпадающие тексты из Диапазон текстовое окно;
  • Выберите ячейки, содержащие ключевые слова, которые вы хотите выделить на основе, вы также можете ввести ключевые слова вручную (через запятую) в поле Ключевое слово текстовое поле.
  • Наконец, вы должны указать цвет шрифта для выделения текста галочкой Отметить цвета ключевых слов опцию. 

3, Затем нажмите Ok все соответствующие тексты были выделены, как показано на скриншоте ниже:


Больше относительных статей:

  • Сравните две или более текстовых строк в Excel
  • Если вы хотите сравнить две или более текстовых строк на листе с учетом регистра или без учета регистра, как показано на следующем скриншоте, в этой статье я расскажу о некоторых полезных формулах, которые помогут вам справиться с этой задачей в Excel.
  • Если ячейка содержит текст, отобразить в Excel
  • Если у вас есть список текстовых строк в столбце A и строка ключевых слов, теперь вам нужно проверить, появляются ли ключевые слова в текстовой строке. Если ключевые слова появляются в ячейке, отображая ее, если нет, отображается пустая ячейка, как показано на следующем снимке экрана.
  • Найти и заменить несколько значений в Excel
  • Обычно функция «Найти и заменить» может помочь вам найти определенный текст и заменить его другим, но иногда вам может потребоваться найти и заменить несколько значений одновременно. Например, чтобы заменить весь текст «Excel» на «Excel 2019», «Outlook» на «Outlook2019» и так далее, как показано на скриншоте ниже. В этой статье я представлю формулу решения этой задачи в Excel.

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

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (50)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Что делать, если столбец «конкретный текст» содержит фразы? Например "удалить дубли" "преобразовать текст" "найти поперек" как тогда проверить?
Этот комментарий был сведен к минимуму модератором на сайте
Вместо True или False я хочу напечатать название найденного текста, как мне это сделать?
Этот комментарий был сведен к минимуму модератором на сайте
проверьте формулу, данную skyyang... Это хорошо работает
Этот комментарий был сведен к минимуму модератором на сайте
Очень полезная формула.
Можем ли мы получить это значение, найденное в результате поиска? (т.е. я хочу проверить, находится ли поле адреса, содержащее 4-значный почтовый индекс, в пределах диапазона, если да, то показать значение 4-значного почтового индекса.)
Давид
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Дэвид,
Моя формула массива может помочь вам:
=IF(SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))>0,INDEX($D$2:$D$4,SUMPRODUCT(IF(ISNUMBER(SEARCH($D$2:$D$4,A2)),ROW($D$2:$D$4)-ROW($D$2)+1,0))))
После вставки формулы нажмите одновременно клавиши Shift + Ctrl + Enter.
Эта формула несколько сложна, измените ссылки на ячейки в соответствии с вашими потребностями.
Пожалуйста, попробуйте, надеюсь, это поможет вам!
Этот комментарий был сведен к минимуму модератором на сайте
Удивительно, он работает именно так, как мне нужно. Спасибо, Чемпионы.
Этот комментарий был сведен к минимуму модератором на сайте
Рад помочь вам!
Этот комментарий был сведен к минимуму модератором на сайте
привет, не могли бы вы объяснить, как вы получили эту функцию выше? Я пытаюсь сделать то же самое, но не могу правильно изменить ссылки на ячейки.
Этот комментарий был сведен к минимуму модератором на сайте
Да, пожалуйста, мне это тоже нужно. Я не понимаю, почему $D$2 заблокирован?
Этот комментарий был сведен к минимуму модератором на сайте
Это потрясающе! Однако у меня есть одна большая проблема: что делать, если в моем списке «искать» D2: D4 есть пустые ячейки. Для меня это разрушает формулу, и я просто получаю «ЧИСЛО» в нескольких местах. СПАСИБО!
Этот комментарий был сведен к минимуму модератором на сайте
работает ли эта формула, когда есть пара слов в ячейках поиска и пара слов в поиске в ячейках? в основном у меня есть списки ингредиентов, и я хочу найти какой-то конкретный ингредиент в этом списке, и конкретный ингредиент может состоять из нескольких слов.
Этот комментарий был сведен к минимуму модератором на сайте
у меня есть список ингредиентов в столбце А, в каждой ячейке есть все ингредиенты продукта, так что это не просто одно слово, в столбце D у меня есть список ингредиентов, которые я пытаюсь найти в столбце А, как мне это сделать? эта формула не работает на 100% для этого
благодаря
Этот комментарий был сведен к минимуму модератором на сайте
Я и еще одна девушка пытались понять это в течение 2 дней из-за проблемы с данными, которую мы должны решить. Спасибо за это выражение. Это именно то, что нам нужно.
Этот комментарий был сведен к минимуму модератором на сайте
Может ли список в столбце D состоять из 15000 слов, а формула остается такой же, как указано выше? Я получаю ожидаемые результаты всего с несколькими значениями, но в тот момент, когда список достигает тысяч, все это показывает #REF.
Этот комментарий был сведен к минимуму модератором на сайте
скайян,

Не знаю, прочтете ли вы это когда-нибудь, но большое вам спасибо!

Ты гений!!!
Этот комментарий был сведен к минимуму модератором на сайте
Как бы я это сделал, если бы в столбце D были пустые ячейки?
Этот комментарий был сведен к минимуму модератором на сайте
hi
см. прикрепленный файл, я пытаюсь выяснить, существует ли какой-либо ингредиент из столбца D в столбце A, и отобразить любой ингредиент, найденный в столбце B, возможно ли это?
Этот комментарий был сведен к минимуму модератором на сайте
первое вложение не загрузилось, см. вложение ниже
Этот комментарий был сведен к минимуму модератором на сайте
감사합니다 정말 진심으로 감사드립니다
한달여간을 네이버에서만 찾다가 구글을 통해 찾으니 이렇게 바로 정확한 정보가 Ꜩ네낤요 릕다요 릕다요 릕매
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо, это сработало.
Этот комментарий был сведен к минимуму модератором на сайте
유용한 자료 감사합니다!
Этот комментарий был сведен к минимуму модератором на сайте
используется =СУММПРОИЗВ(--IЧИСЛО(ПОИСК(список,A2))) вместе с условным форматированием, чтобы помочь сравнить значения одного столбца с другим. отлично работает с Excel 2007. ломал голову несколько дней.
Этот комментарий был сведен к минимуму модератором на сайте
ДА, НЕТ

중복되는 값 배열일 경우 배열로 B열 C열......... 기재는 가능한가요
Этот комментарий был сведен к минимуму модератором на сайте
Любой способ вернуть текст, который соответствует ячейке, а не да или нет?
Этот комментарий был сведен к минимуму модератором на сайте
Тот же вопрос; Можем ли мы вернуть текст, который был сопоставлен, а не да/нет?
Этот комментарий был сведен к минимуму модератором на сайте
Можно ли изменить результат этой формулы с просто «ИСТИНА», «ЛОЖЬ», «Да» или «Нет» на значение конкретного текста?


спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Нильс,
Если вы хотите использовать другой текст вместо «Да» или «Нет», вам просто нужно заменить «Да», «Нет» другими текстами, попробуйте следующую формулу:
=ЕСЛИ(СУММПРОИЗВ(--IЧИСЛО(ПОИСК($D$2:$D$6,A2))),"ваш конкретный текст1","ваш конкретный текст2")

Пожалуйста, попробуйте, надеюсь, это поможет вам!
Этот комментарий был сведен к минимуму модератором на сайте
Джейн спросила ниже: «Тот же вопрос. Можем ли мы вернуть текст, который был сопоставлен, а не да/нет?» - тоже интересует ответ
Этот комментарий был сведен к минимуму модератором на сайте
=ЕСЛИ(СУММПРОИЗВ(--IЧИСЛО(ПОИСК($D$2:$D$6,A2))),"Да","Нет")
Этот комментарий был сведен к минимуму модератором на сайте
Привет,
Я попробовал эту формулу, но она показывает ИСТИНА для всего?
Этот комментарий был сведен к минимуму модератором на сайте
Пожалуйста проверьте
Этот комментарий был сведен к минимуму модератором на сайте
Формула неверна. Он показывает "true" для любого значения
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Дд,
Формула хорошо работает на моем листе Excel, можете ли вы привести свой лист проблем в виде скриншота здесь?
Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
эй, у меня такая же проблема. Он возвращает true каждый раз, даже если он должен быть False. Это формула:
=СУММПРОИЗВ(--ЧИСЛО(ПОИСК($AH$2:$AH$122;C2)))>0

$AH$2:$AH$12 — список слов
C2 — это текст из твита.

возвращает true, даже если твит не содержит ни одного слова из списка
Этот комментарий был сведен к минимуму модератором на сайте
именно то, что я искал! отлично работает
Этот комментарий был сведен к минимуму модератором на сайте
Идеальный!! Сложный спрос соответствует сочетанию нескольких хорошо известных формул
Этот комментарий был сведен к минимуму модератором на сайте
Это было очень полезно, но мне просто не хватает последней части.
Как расширить IF(СУММПРОИЗВ(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Да","Нет"), чтобы также проверить чувствительность к регистру значений в списке?
заранее спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Скотт,
Чтобы проверить значения с учетом регистра, примените следующую формулу:
=ЕСЛИ(СУММПРОИЗВ(--ЧИСЛО(НАЙТИ($D$2:$D$6,A2))),"Да","Нет")

Пожалуйста, попробуйте, спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Привет. Что, если бы мне нужно было искать в строке ключевые слова, а не только в ячейке, т.е. A2 - P2, а не только A2 в этой формуле? =ЕСЛИ(СУММПРОИЗВ(--IЧИСЛО(ПОИСК($D$2:$D$6,A2))),"Да","Нет")
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, Марго,
Чтобы применить эту формулу к данным строки, вам просто нужно заменить ссылку на одну ячейку ссылкой на строку, как показано ниже:
=ЕСЛИ(СУММПРОИЗВ(--IЧИСЛО(ПОИСК($R$2:$R$6,A2:P2))),"Да","Нет")
Пожалуйста, попробуйте, надеюсь, это поможет вам!
Этот комментарий был сведен к минимуму модератором на сайте
Можно ли это сделать при частичном совпадении ключевых слов? Этот метод является только точным соответствием
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL