Как проверить, содержит ли ячейка одно из нескольких значений в Excel?
Во многих бизнес-сценариях, аналитических или обзорных задачах вы можете иметь список текстовых строк в столбце A и захотеть проверить, содержит ли каждая ячейка любое из указанных значений, например, перечисленных в диапазоне D2:D7. Например, в данных опросов, журналах или списках продуктов важно определить, содержат ли записи соответствующие ключевые слова, коды продуктов или запрещенные термины. Если ячейка содержит любой из элементов вашего указанного списка, вы можете захотеть, чтобы Excel вернул Истина; в противном случае — Ложь, как показано на скриншоте ниже. В этой статье объясняются практические способы проверки, содержит ли ячейка одно из нескольких значений из другого диапазона, предлагая несколько подходов для разных версий Excel и потребностей пользователей.
Проверьте, содержит ли ячейка одно из нескольких значений из списка с помощью формул
Чтобы определить, содержит ли ячейка любое из текстовых значений из другого диапазона, вы можете использовать формулу массива, которая эффективно работает даже с большими наборами данных. Этот метод особенно полезен, когда вам нужны результаты в виде логических значений (Истина/Ложь) для использования в дальнейших формулах, фильтрации или логических тестах. Эта техника работает в большинстве современных версий Excel.
Введите следующую формулу в пустую ячейку (например, в ячейку B2 рядом с вашими исходными данными), затем перетащите маркер заполнения вниз, чтобы применить её к другим ячейкам по мере необходимости. Если ячейка содержит любое из текстовых значений в указанном диапазоне, она возвращает Истина; в противном случае возвращает Ложь. См. скриншот:
Советы и примечания:
- Эта формула выполняет проверку без учета регистра. Если требуется учет регистра, рассмотрите использование вспомогательного столбца или комбинируйте формулы более сложными способами.
- Чтобы адаптировать формулу для результата «Да» или «Нет» вместо Истина/Ложь, используйте следующую скорректированную формулу:
- D2:D7 — это ваш диапазон значений («список»); A2 — это ячейка для проверки.
- Будьте внимательны с пустыми ячейками или не текстовыми данными, так как функция ПОИСК требует допустимый текстовый ввод, а пустые значения могут привести к неожиданным результатам («Истина»).
Отобразите совпадения, если ячейка содержит одно из нескольких значений из списка с помощью формул
Иногда может быть более информативным отобразить, какие именно значения из списка действительно встречаются в каждой ячейке, а не просто показывать результат Истина/Ложь. Например, при сканировании описаний продуктов или комментариев для определенных ключевых слов вы можете захотеть вернуть все найденные значения для дальнейшего анализа или отчетности. Вы можете использовать следующую формулу, чтобы показать все совпадающие значения, разделенные запятыми, как показано здесь:
Введите эту формулу в пустую ячейку (например, B2), и она выведет все значения из D2:D7, найденные внутри A2, разделенные запятыми:
Примечание: Здесь D2:D7 — это набор значений для поиска, а A2 — это ячейка для поиска.
После ввода формулы нажмите Ctrl + Shift + Enter. Затем вы можете перетащить маркер заполнения вниз, чтобы применить формулу к другим строкам, как показано на скриншоте с результатами:
- Функция ТЕКСТ.СЦЕП доступна только в Excel 2019 и Office 365. В более ранних версиях Excel используйте следующую формулу массива, вводя её в пустую ячейку, а затем нажав Ctrl + Shift + Enter:
Перетащите формулу вправо, чтобы охватить столько столбцов, сколько возможно, чтобы захватить все возможные совпадения, а затем вниз для каждой строки. Лишние столбцы останутся пустыми там, где меньше совпадений. Этот формат особенно полезен, когда вам нужно, чтобы совпадения были перечислены в отдельных столбцах:
Если вы столкнетесь с ошибками, дважды проверьте диапазоны, убедитесь, что регион D2:D7 правильный, и подтвердите, что вы используете правильный разделитель для вашего региона (запятую или точку с запятой).
Выделите совпадения, если ячейка содержит одно из нескольких значений из списка с помощью удобной функции
Если вам нужно визуально выделить ключевые слова или фразы, которые соответствуют любому значению из вашего списка в каждой ячейке, это поможет сделать важные данные заметными для проверки или действий. Функция Отметить ключевые слова в Kutools для Excel предназначена для этого случая, позволяя быстро выделять любое вхождение ваших указанных слов в диапазоне данных без написания формул или VBA-кода. В больших таблицах или сложных наборах данных эта функция особенно полезна, когда ручная проверка невозможна.
После установки Kutools для Excel выполните следующие шаги:
1. Перейдите в Kutools > Текст > Отметить ключевые слова, чтобы запустить диалог, как показано:
2. В диалоге Отметить ключевые слова выполните следующее:
- Выберите целевой диапазон данных в поле Диапазон.
- Выберите или введите вручную ключевые слова в поле Ключевые слова (разделенные запятыми).
- Укажите цвет выделения с помощью параметра Цвет шрифта ключевого слова.
3. Нажмите OK. Все совпадающие слова в выбранном диапазоне будут выделены выбранным цветом шрифта:
- Эта функция напрямую изменяет формат отображения совпадающих ключевых слов, делая проверку или обмен результатами более интуитивными для команд, которые могут не понимать выходные данные формул.
- Особенно эффективна, когда список ключевых слов или диапазон текста очень большой, или вам нужно выделить несколько критериев одновременно.
- Всегда дважды проверяйте выбранный диапазон и записи ключевых слов перед подтверждением, чтобы избежать случайного выделения.
Проверьте, содержит ли ячейка одно из нескольких значений, используя Условное форматирование
Встроенное условное форматирование Excel является еще одним эффективным подходом для выделения ячеек, содержащих любое значение из вашего указанного списка. Это решение полезно для визуального выявления соответствующих строк с первого взгляда, особенно во время проверки данных, поиска ошибок или задач соответствия. В отличие от функции Отметить ключевые слова в Kutools, этот подход не требует дополнительных надстроек, что делает его доступным в стандартных установках Excel.
Вот как использовать условное форматирование с формулой:
- Выберите ячейки в вашем диапазоне данных (например, A2:A20), которые вы хотите отслеживать.
- Перейдите на вкладку Главная, нажмите Условное форматирование > Новое правило.
- В диалоговом окне Новое правило форматирования выберите Использовать формулу для определения форматируемых ячеек.
- Введите эту формулу, предполагая, что D2:D7 содержит ваши значения, а A2 — первая ячейка с данными:
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$7,A2)))>0- Нажмите Формат, установите желаемое форматирование (например, цвет заливки) и нажмите OK.
Теперь все ячейки, содержащие любой элемент из списка D2:D7, будут автоматически выделены.
- Этот метод динамичен: если вы обновите диапазон D2:D7, форматирование будет соответственно изменено.
- Условное форматирование только для просмотра: оно визуально выделяет ячейки, но не предоставляет результаты в отдельном столбце или для дальнейших расчетов.
- Формульное условное форматирование мощное, но с очень большими наборами данных производительность может замедлиться из-за повторяющихся вычислений.
- Обратите внимание, что функция ПОИСК не чувствительна к регистру. Чтобы сделать этот подход чувствительным к регистру, могут потребоваться более сложные техники или вспомогательные столбцы.
Больше связанных статей:
- Сравнение двух или более текстовых строк в Excel
- Если вы хотите сравнить две или более текстовые строки в рабочем листе с учетом регистра или без него, как показано на следующем скриншоте, в этой статье я расскажу о некоторых полезных формулах для решения этой задачи в Excel.
- Если ячейка содержит текст, тогда отобразить в Excel
- Если у вас есть список текстовых строк в столбце A и строка ключевых слов, теперь вам нужно проверить, появляются ли ключевые слова в текстовой строке. Если ключевые слова появляются в ячейке, они отображаются, если нет — отображается пустая ячейка, как показано на следующем скриншоте.
- Подсчитайте количество ключевых слов в ячейке на основе списка
- Если вы хотите подсчитать количество появлений ключевых слов в ячейке на основе списка ячеек, комбинация функций СУММПРОИЗВ, ЕЧИСЛО и ПОИСК может помочь вам решить эту проблему в Excel.
- Найти и заменить несколько значений в Excel
- Обычно функция Найти и заменить может помочь вам найти определенный текст и заменить его другим, но иногда вам может потребоваться найти и заменить несколько значений одновременно. Например, заменить весь текст «Excel» на «Excel2019», «Outlook» на «Outlook2019» и т.д., как показано на скриншоте ниже. В этой статье я представлю формулу для решения этой задачи в Excel.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек