Как проверить, содержит ли ячейка одно из нескольких заданных значений в Excel?
Во многих бизнес-, аналитических или ревизионных сценариях у вас может быть список текстовых строк в столбце A, и вам нужно будет проверить, содержит ли каждая ячейка хотя бы одно из заданных значений — например, перечисленных в диапазоне D2:D7. Это особенно важно при работе с данными опросов, журналами или списками товаров: необходимо быстро определить, включают ли записи какие-либо релевантные ключевые слова, артикулы или запрещённые термины. Если ячейка содержит хотя бы один элемент из вашего списка, вы можете настроить Excel так, чтобы он возвращал ИСТИНА; в противном случае — ЛОЖЬ, как показано на снимке экрана ниже. В этой статье описаны практичные способы проверки, содержит ли ячейка одно из нескольких значений из другого диапазона, с рекомендациями подходов для разных версий Excel и различных задач пользователей.
Проверка, содержит ли ячейка одно из нескольких значений из списка с помощью формул
Чтобы проверить, содержит ли ячейка хотя бы одно текстовое значение из другого диапазона, используйте формулу массива — она отлично справляется даже с большими объёмами данных. Этот метод особенно удобен, когда нужны логические результаты ()ИСТИНА/ЛОЖЬ) для дальнейшего применения в других формулах, фильтрации или логических проверках. Приём работает в большинстве современных версий Excel.
Введите приведённую ниже формулу в любую пустую ячейку (например, в B2 рядом с исходными данными), а затем перетащите маркер заполнения вниз, чтобы применить её к остальным ячейкам по мере необходимости. Формула вернёт значение ИСТИНА, если ячейка содержит хотя бы одно из текстовых значений из вашего ограниченного диапазона, и ЛОЖЬ — в противном случае. См. снимок экрана:
Советы и примечания:
- Эта формула выполняет проверку без учёта регистра. Если необходимо учитывать регистр, воспользуйтесь вспомогательным столбцом или комбинируйте формулы более сложными способами.
- Чтобы вместо значений ИСТИНА/ЛОЖЬ получить результат «Да» или «Нет», используйте следующую изменённую формулу:
- D2:D7 — это диапазон ваших значений («список»); A2 — ячейка для проверки.
- Обратите внимание на пустые ячейки и нетекстовые данные: функция ПОИСК требует корректного текстового ввода, а пустые значения могут неожиданно вернуть «ИСТИНА».
Отображение совпадений, если ячейка содержит одно из нескольких значений из списка с помощью формул
Иногда бывает полезнее отображать, какие именно значения из списка действительно встречаются в каждой ячейке, а не просто показывать результат ИСТИНА/ЛОЖЬ. Например, при сканировании описаний товаров или комментариев на наличие конкретных ключевых слов вы можете захотеть получить все найденные значения для дальнейшего анализа или отчёта. Для этого можно использовать следующую формулу, чтобы отобразить все совпадающие значения через запятую, как показано здесь:
Введите эту формулу в пустую ячейку (например, B2), и она перечислит все значения из D2:D7, найденные в A2, разделённые запятыми:
Примечание: здесь D2:D7 — это диапазон значений для поиска, а A2 — ячейка, в которой выполняется поиск.
После ввода формулы нажмите Ctrl + Shift + Enter. Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим строкам, как показано на снимке экрана с результатами:
- Функция TEXTJOIN доступна только в 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- Нажмите Формат, задайте нужное форматирование (например, цвет заливки) и нажмите ОК.
Теперь все ячейки, содержащие любой элемент из списка 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек