При работе с данными Excel может потребоваться проверить, содержит ли ячейка определённый текст, чтобы найти ключевые слова, отфильтровать записи или классифицировать информацию. Например, можно определить товары, помеченные как отсутствующие на складе, или выделить ячейки, содержащие определённые термины.
Это руководство объясняет, как проверить, содержит ли ячейка определённый текст в Excel, используя SEARCH, FIND, COUNTIF, условное форматирование и автоматизацию на Python с помощью Free Spire.XLS for Python.
Проверка, содержат ли ячейки определённый текст, с помощью SEARCH и ISNUMBER
В Excel комбинация SEARCH и ISNUMBER — распространённый способ проверить, содержит ли ячейка определённый текст. Эта формула оценивает по одной ячейке за раз, поэтому её нужно применить к вспомогательному столбцу и протянуть вниз.
Например, если описания товаров хранятся в столбце H, введите формулу в I2:
=ISNUMBER(SEARCH("Out of stock",H2))
Затем скопируйте формулу вниз, чтобы применить ту же проверку к остальным строкам диапазона.
Формула возвращает TRUE для строк, где соответствующая ячейка содержит out of stock, и FALSE для строк без этого ключевого слова.
Зачем использовать SEARCH вместе с ISNUMBER?
Функция SEARCH находит позицию определённого текста внутри ячейки. Когда она находит совпадение, она возвращает начальную позицию текста. Например, =SEARCH("stock","Out of stock") возвращает 9, потому что stock начинается с девятого символа.
Если искомый текст отсутствует, SEARCH возвращает ошибку #VALUE!. Объединив её с ISNUMBER, вы можете преобразовать этот результат в простое значение TRUE/FALSE:
- Когда SEARCH возвращает число, ISNUMBER возвращает TRUE.
- Когда SEARCH возвращает ошибку, ISNUMBER возвращает FALSE.
Этот метод хорошо работает, когда нужен понятный результат совпадения для каждой строки, например для создания фильтров, добавления условного форматирования или пометки записей для дальнейшей обработки.
Поиск с учётом регистра с помощью FIND и ISNUMBER
В некоторых ситуациях важны заглавные и строчные буквы. Это часто встречается при работе с кодами товаров, внутренними идентификаторами или категориями, где разный регистр представляет разные значения.
Функция FIND работает аналогично SEARCH, но выполняет поиск с учётом регистра. Объединив FIND с ISNUMBER, вы можете проверить, содержит ли ячейка определённый текст, сохраняя различия в регистре.
Например, предположим, что столбец статуса товара содержит значения, такие как "out of stock" и "Out of stock". Если вы хотите определить только ячейки с точным написанием Out of stock, можно использовать следующую формулу во вспомогательном столбце и применить её ко всему диапазону данных:
=ISNUMBER(FIND("Out of stock",H2))
Эта формула возвращает TRUE, когда соответствующая ячейка содержит "Out of stock", и FALSE, когда она содержит "out of stock" или другой текст. В отличие от SEARCH, FIND различает заглавные и строчные буквы, что делает её подходящей для проверок текста с учётом регистра.
SEARCH против FIND
| Метод | С учётом регистра | Поддерживает подстановочные знаки | Лучше всего использовать для |
|---|---|---|---|
| ISNUMBER(SEARCH(...)) | Нет | Да | Общий поиск по ключевым словам |
| ISNUMBER(FIND(...)) | Да | Нет | Поиск с учётом регистра |
Проверка, содержит ли ячейка определённый текст, с помощью COUNTIF и подстановочных знаков в Excel
Если вы предпочитаете более короткую формулу, COUNTIF предоставляет простой способ проверить, содержит ли ячейка определённый текст. Этот метод полезен, когда вы уже знакомы с формулами критериев Excel и хотите избежать объединения нескольких функций.
В отличие от SEARCH, COUNTIF может использовать подстановочные знаки, такие как *, для сопоставления текстовых шаблонов. Это делает его удобным для простых проверок по ключевым словам.
Введите следующую формулу в ячейку вспомогательного столбца:
=COUNTIF(H2,"Out of stock")>0
Формула возвращает TRUE, когда ячейка содержит "out of stock", и FALSE, когда указанный текст не найден.
Как работает звёздочка (*)
В COUNTIF звёздочка (*) — это подстановочный знак, представляющий любое количество символов. Разместив звёздочку до и после искомого текста, вы позволяете Excel сопоставить ключевое слово в любом месте внутри ячейки.
Например, использование stock в качестве шаблона поиска совпадёт с ячейками, содержащими только stock, а также с более длинными фразами, такими как "out of stock" или "low stock warning". Это происходит потому, что подстановочные знаки позволяют любому тексту появляться до или после ключевого слова.
Совет: если вам нужно подсчитать ячейки, содержащие текст, а не просто проверить совпадение, см. наше руководство по подсчёту ячеек с текстом в Excel.
Проверка нескольких ключевых слов с логикой OR в Excel
В некоторых случаях может потребоваться проверить, содержит ли ячейка одно из нескольких ключевых слов. Например, при анализе статусов товаров вы можете захотеть определить позиции, которые либо отсутствуют на складе, либо имеют плохие показатели продаж. Функция OR позволяет объединить несколько проверок текста в одной формуле. Аналогично предыдущим методам.
Введите следующую формулу в ячейку вспомогательного столбца:
=OR(ISNUMBER(SEARCH("out of stock",H2)),ISNUMBER(SEARCH("Poor-selling",H2)))
Формула возвращает TRUE, когда соответствующий статус содержит либо "out of stock", либо "Poor-selling". Она возвращает FALSE для обычных статусов или других значений.
Выделение ячеек, содержащих определённый текст, с помощью условного форматирования
Использование вспомогательного столбца — практичный способ проверить, содержат ли ячейки определённый текст, но для отображения результатов требуется дополнительный столбец. Если вы хотите сделать совпадающие записи более заметными без изменения структуры данных, условное форматирование — лучший вариант.
С помощью условного форматирования Excel может автоматически выделять ячейки, соответствующие определённому правилу. Используя формулу на основе SEARCH, вы можете выделять совпадающие ячейки при каждом изменении данных.
Выполните следующие шаги, чтобы применить условное форматирование:
- Шаг 1: Выберите целевой диапазон, например H2:H15.
- Шаг 2: Перейдите в Главная > Условное форматирование > Создать правило.
- Шаг 3: Выберите Использовать формулу для определения форматируемых ячеек.
- Шаг 4: Введите формулу =ISNUMBER(SEARCH("Out of stock",H2)).
- Шаг 5: Нажмите Формат..., выберите стиль форматирования и затем нажмите ОК, чтобы применить правило.
Автоматическая проверка, содержит ли ячейка определённый текст, с помощью Python
Методы на основе формул хорошо работают при обработке отдельных электронных таблиц, но ручное добавление формул или правил форматирования может стать неэффективным при работе с повторяющимися отчётами или большим количеством файлов Excel.
В автоматизированных рабочих процессах разработчики могут использовать Python для программного применения той же логики проверки текста. С помощью Free Spire.XLS for Python вы можете создавать, редактировать и форматировать книги Excel, не полагаясь на Microsoft Excel.
Что такое Free Spire.XLS for Python?
Free Spire.XLS for Python — это библиотека Python для работы с книгами Excel. Она поддерживает распространённые операции с электронными таблицами, такие как создание файлов, чтение и редактирование листов, применение формул, добавление условного форматирования и преобразование документов Excel.
Используя эту библиотеку, разработчики могут автоматизировать повторяющиеся задачи с электронными таблицами, такие как применение правил форматирования на основе текста к нескольким отчётам.
Пошаговая реализация на Python
Следующий пример показывает, как загрузить книгу Excel и автоматически применить правило условного форматирования на основе того, содержат ли ячейки определённую фразу.
Шаг 1: Установка библиотеки
Установите пакет с помощью pip:
pip install Spire.Xls.Free
Шаг 2: Применение условного форматирования с помощью Python
Следующий скрипт загружает файл Excel и выделяет ячейки, содержащие out of stock.
from spire.xls import Workbook, Color
file_path = "input/sales report.xlsx"
output_path = "output/Highlighted.xlsx"
# Load the Excel workbook
workbook = Workbook()
workbook.LoadFromFile(file_path)
sheet = workbook.Worksheets[0]
# Target the desired cell range
range_data = sheet.Range["H2:H15"]
# Add an expression-based conditional formatting rule
rule = range_data.ConditionalFormats.AddCondition()
rule.FormatType = ConditionalFormatType.Formula
rule.FirstFormula = '=ISNUMBER(SEARCH("Out of stock", H2))'
# Apply background color for matching cells
rule.BackColor = Color.get_LightPink()
# Save output and release resources
workbook.SaveToFile(output_path)
workbook.Dispose()
Распространённые ошибки и FAQ
Вопрос 1: Почему моя формула возвращает ошибку #VALUE!?
Если вы используете SEARCH или FIND отдельно, и указанный текст отсутствует в ячейке, Excel возвращает ошибку #VALUE!. Если вам нужно определить, существует ли текст, используйте ISNUMBER, чтобы преобразовать результат поиска в TRUE или FALSE.
Вопрос 2: В чём разница между проверкой определённого текста и ISTEXT()?
Функция ISTEXT проверяет, содержит ли ячейка текстовые данные, поэтому она помогает отличить текстовые значения от чисел, дат и других типов данных. Например, =ISTEXT(A1) возвращает TRUE, когда ячейка содержит текст, и FALSE, когда она содержит число или другой тип данных.
Однако ISTEXT не может проверить, содержит ли ячейка определённый текст. Если вам нужно искать конкретное слово или фразу, используйте вместо этого SEARCH, FIND или COUNTIF.
Вопрос 3: Как проверить, содержит ли ячейка частичный текст?
Чтобы проверить, содержит ли ячейка часть более длинной текстовой строки, используйте SEARCH с ISNUMBER или COUNTIF с подстановочными знаками. Например, =ISNUMBER(SEARCH("stock",A1)) возвращает TRUE, когда ячейка содержит stock в любом месте своего текста, включая фразы, такие как "out of stock" или "low stock". Вы также можете использовать =COUNTIF(A1,"stock")>0 для более короткой формулы на основе подстановочных знаков.
Подводя итог
Проверка того, содержит ли ячейка определённый текст, — распространённая задача при обработке данных в Excel. В большинстве случаев ISNUMBER(SEARCH()) обеспечивает гибкое решение, тогда как FIND() полезна, когда нужно различать заглавные и строчные буквы. Если вы предпочитаете более короткую формулу, COUNTIF() предлагает простой подход на основе подстановочных знаков.
Для повторяющихся рабочих процессов с электронными таблицами автоматизация на Python с помощью Free Spire.XLS for Python может помочь программно применять те же правила проверки текста и форматирования, снижая объём повторяющейся ручной работы.