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

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

Функция ЕСЛИ СОДЕРЖИТ

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ» , чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова , или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

Такой функционал возможно получить с помощью сочетания двух обычных стандартных функций – ЕСЛИ и СЧЁТЕСЛИ .

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

У нас есть множество строк с документами Реализации и Возвратов .

Все документы имеют свое наименование за счет уникального номера .

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

Выражение должно быть универсальным , для того, чтобы обрабатывать новые добавляемые данные .

Для того, чтобы это сделать, необходимо:

    Начинаем с ввода функции ЕСЛИ (вводим «=» , набираем наименование ЕСЛИ , выбираем его из выпадающего списка, нажимаем fx в строке формул).

В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ() , выделяем его и нажимаем 2 раза fx.



Далее в открывшемся окне аргументов функции СЧЁТЕСЛИ в поле «Критерий» вводим кусок искомого наименования *реализ* , добавляя в начале и в конце символ * .

Такая запись даст возможность не думать о том, с какой стороны написано слово реализация (до или после номера документа), а также даст возможность включить в расчет сокращенные слова «реализ.» и «реализац.»

  • Аргумент «Диапазон» — это соответствующая ячейка с наименованием документа.
  • Далее нажимаем ОК , выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

  • В Значение_если_истина вводим « Реализация », а в Значение_если_ложь – можно ввести прочерк « — »
  • Далее протягиваем формулу до конца таблицы и подключаем сводную.


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

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

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

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

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

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

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

    • ищутся значения в точности совпадающие с критерием;
    • ищутся значения содержащие критерий;
    • ищутся значения с учетом РЕгиСТра.
    Читать еще:  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», но данные строки разрознены по всей таблице. Как эти строки сделать в последовательности?

    Читать еще:  Как в excel 2010 отобразить скрытые строки

    Например есть 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. Нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.
    Читать еще:  В excel замена символа в строке

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

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

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

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

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

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

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

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

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

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

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

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

  • Ссылка на основную публикацию
    Похожие публикации
    ×
    ×
    Adblock
    detector