Excel содержит ли ячейка часть текста

Теперь мы можем работать и сводить данные только по документам реализации исключая возвраты . При дополнении таблицы новыми данными, остается только протягивать строку с нашим выражением и обновлять сводную таблицу.

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

Есть ли слово в списке MS EXCEL

Найдем слово в диапазоне ячеек, удовлетворяющее критерию: точное совпадение с критерием, совпадение с учетом регистра, совпадение лишь части символов из слова и т.д.

Пусть Список значений, в котором производится поиск содержит только отдельные слова (см. столбец А на рисунке ниже).

Совет: О поиске слова в списках, состоящих из текстовых строк (т.е. в ячейке содержится не одно слово, а несколько, разделенных пробелами) можно прочитать в статье Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL.

Задачу поиска текстового значения в диапазоне ячеек можно разбить на несколько типов:

• ищутся значения в точности совпадающие с критерием;
• ищутся значения содержащие критерий;
• ищутся значения с учетом РЕгиСТра.

Ищутся значения в точности соответствующие критерию

Это простейший случай. Здесь можно использовать формулу наподобие нижеуказанной
=СЧЁТЕСЛИ($A$5:$A$11;»яблоки»)

Формула возвращает количество найденных значений, соответствующих критерию (см. файл примера ).

Ищутся значения содержащие часть текстовой строки

Типичный вопрос для этого типа поиска: Есть ли в Списке слово со слогом МА?

Для ответа на вопросы такого типа требуется задать в качестве критерия часть текстового значения. Например, для отбора всех ячеек, содержащих все склонения слова яблоко (яблоку, яблоком, яблока и пр.) можно использовать критерии с подстановочным знаком * (звездочка). Для этого нужно использовать конструкцию *яблок*.

Типичная формула =СЧЁТЕСЛИ($A$5:$A$11;»*МА*»)

Ищутся значения с учетом РЕгиСТрА

Учет регистра приводит к необходимости создания сложных формул или использования дополнительных столбцов. Чаще всего используются формулы на основе функций учитывающих регистр НАЙТИ() , СОВПАД() .

Формула массива =ИЛИ(СОВПАД(«яблоки»;A5:A11)) дает ответ на вопрос есть ли такой элемент в списке.

СОВЕТ:
Идеи о поиске также можно посмотреть в статье Поиск текстовых значений в списках. Часть1. Обычный поиск.

Как выделить ячейку, которая не содержит конкретного текста из любого столбца

У меня есть файл excel с 2 листами excel. Листы называются первыми и вторыми.

имя 1-го листа: сначала

имя первого листа: первый

имя второго листа: второй

где is моя кошка

. На первом листе, если какое-либо животное не содержится во втором листе, оно должно быть выделено. «улитка» должна быть выделена на листе животных

. Для этого я использовал формулу поиска excel. Я перехожу к условному форматированию и использую формулу для определения, какие ячейки нужно форматировать. Я реализовал код

= NOT (ISNUMBER (SEARCH (‘Sheet 1’! $ A: $ A, $ A: $ A))) Результат состоит в том, что все животные выделены

То, что я пытаюсь сделать, это то, что если животное не найдено ни в одной из столбцов, оно будет подсвечено. Однако это не сработает. Вы можете исправить эту проблему?

Когда ячейка содержит ошибку, подобную #VALUE! , это не текст с фразой «VALUE»; не то, что вы можете (или должны) искать таким образом. Это своего рода заполнитель, показывающий, где ошибка.

Чтобы определить, использует ли формула или функция ошибку, используйте ISERROR или IFERROR .

Например, если вы хотите вернуть Not Found , если ваша формула выдает ошибку, вы можете использовать:

Я предпочитаю VLOOKUP для поиска совпадений.

Например, вы можете ввести ячейку B2 на листе First :

. а затем заполнить или скопировать формулу до ячейки A7 .

Если a совпадающая фраза найдена в Second , тогда она покажет эту фразу, иначе она вызовет ошибку.

На этот раз с использованием ISERROR (а также IF ) в качестве примера вы можете отобразить, было ли совпадение, вместо этого, используя эту формулу в ячейке B2 на листе First :

. и затем заполните или скопируйте формулу до ячейки A7 .

[/g3]

Как в excel строки, содержащие текст «N», сделать последовательно?

Есть документ excel. Там много строк. В некоторых строках, в определённой ячейке совпадает слово, скажем — «TheWord», но данные строки разрознены по всей таблице. Как эти строки сделать в последовательности?

Например есть 10 строк со словом «Table» в ячейке, а есть 10 слов со словом «Street», причем в одной ячейке два этих слова встречаться не могут. Так вот, эти строки расположены чередой (сначала строка со словом Table, затем со словом Street, затем снова Table и т.д.). Нужно чтобы сначала шли 10 строк со словом Table, а затем 10 строк со словом Street.

• Вопрос задан более трёх лет назад
• 2718 просмотров

Реализация может быть различной.
Зависит, в частности, от того, сколько в таблице столбцов, в которых нужно искать слово — один или более; сколько искомых слов (например, если их много, то IF использовать будет неудобно, а то и невозможно). Предположим, что столбец один, и это столбец А, а искомых слов — два, Table и Street.

Создайте дополнительный столбец B с формулой, например,

Протяните ее вниз параллельно исходным данным. Тогда в столбец B будут выбраны ключевые слова TableStreet для каждой строки, и other, если строка не содержит ни одного ключевого значения.
Теперь можно выделить столбцы A и B, и отсортировать по значениям столбца B.

Если ключевых слов больше, то ввиду ограничения числа вложенности функции IF такой подход не пойдет. Тогда лучше использовать вариант с функцией CHOOSE:

Работает так же — протягиваете, сортируете по второму столбцу.

Каждое новое ключевое слово добавляете к первому аргументу CHOOSE как элемент
+ISNUMBER(SEARCH(«keyword_n+1»;A1))*[index+1]
где keyword_n+1 — иcкомое ключевое слово, а [index+1] — следующий по порядку индекс. В итоге первый аргумент CHOOSE сводится к числовому значению, равному индексу искомого элемента*. В конце формулы идет перечень значений, выдаваемых по этому индексу. Новое ключевое слово как текстовую строку добавляете туда в конец.

Внимание, в отличие от первого способа, при отсутствии в тексте строки ключевых слов выдает ошибку «#VALUE!» (вместо «other», как в предыдущем примере).

* Корректно работает при условии, что в строке не могут встречаться более одного ключевого слова одновременно. Если у вас будет строка, где есть и Street, и Table, получится фигня. Это же касается и предыдущего способа.

Условное форматирование по части текста в ячейке Excel

У нас имеется данные для анализа производимой продукции. Нам нужно автоматически выделить все изделия, которые были выпущены в 2006 году. К сожалению, в таблице недостает еще одного столбца содержащего значения года производства каждого изделия. Но видно, что в фрагменте идентификатора (ID) изделия указывается год производства. В таком случае нам нужно выполнить поиск по части текста в Excel. А потом мы будем использовать условное форматирование с текстовыми функциями в формуле. Благодаря этому молниеносно реализуем решение задачи.

Пример таблицы производимой продукции:

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

1. Выделите диапазон ячеек A2:A10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Выберите: «Использовать формулу для определения форматируемых ячеек».
3. Чтобы выполнить поиск части текста в ячейке Excel, введите формулу: =ПСТР(A2;5;4)=»2006″
4. Нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.

Экспонированные цветом изделия 2006-го года выпуска:

Далее разберем принцип действия формулы и ее модификации схожими текстовыми функциями.

Функция ПСТР и ее пример в использования условном форматировании

Чтобы легко понять, как удалось экспонировать цветом определенные значения с помощью условного форматирования разберем этапы действий в двух словах. Сначала мы извлекаем часть текста, а потом сравниваем его с требуемым значением. Но как из ячейки извлечь часть текста в Excel? Обратим внимание на функцию =ПСТР() в формуле правила. Данная функция возвращает часть строки, взятой с каждой ячейки столбца A, что указано в первом аргументе. Ссылка в первом аргументе должна быть относительной, так как формула применяется к каждой ячейке столбца A. Во втором аргументе функции указывается номер символа исходного текста, с которого должен начаться отрезок строки. В третьем аргументе указывается количество символов, которые нужно взять после определенного (во втором аргументе) символа исходного текста. В результате функция =ПСТР() возвращает только часть текста длинной 4 символа взятого начиная с 5-ой буквы в каждой ячейки из столбца А. Это «2005» или «2006». После функции стоит оператор сравнения к значению строки «2006». Если ячейка содержит такую часть текста значит ей будет присвоен новый формат.

Аналогичным способом можно использовать и другие текстовые функции в условном форматировании. Например, с помощью функции =ПРАВСИМВ() мы можем экспонировать цветом определенную группу товаров из партии C. Для этого нужно использовать формулу:

Здесь все просто функция позволяет выбрать часть текста из ячейки Excel, начиная с правой стороны исходного текста. Количество необходимых символов указано во втором аргументе функции ПРАВСИМВ. После чего все что возвращает функция сравнивается с значением строки «C».

Во всех выше описанных примерах очень легко применять текстовые функции в условном форматировании так как длина строк в исходных данных одинаковая. Но что, если у нас в исходных данных разного типа индикаторы с разной длинной символов, а нам все еще нужно выделять 2006-й год или группу «C».

В решении данной задачи нам поможет дополнительная текстовая функция в формуле =НАЙТИ(). В первом случаи формула будет выглядеть так:

Для удобного выделения идентификаторов с разной длинной текстовой строки товаров из группы «C» используем такую формулу:

Функция =НАЙТИ() выполняет поиск фрагмента текста в ячейке Excel. Потом возвращает номер символа, в котором был найдет текст, который задан в первом аргументе. Во втором аргументе указываем где искать текст. А третий аргумент – это номер позиции с какого символа вести поиск в исходном тексте. Третий аргумент позволяет нам смещаться по строке. Например, если в идентификаторе 2 раза используется символ «C». В таком случае третий аргумент пользователь задает в зависимости от ситуации.

Так как функция возвращает нужное число мы прекрасно используем ее в качестве аргументов для других функций (ПСТР и ПРАВСИМВ).

Похожие статьи

В excel удалить пустые строки в таблице

Как в эксель объединить текст из нескольких ячеек в одну

Автовыравнивание высоты строк в excel

Фиксировать строку в excel