Загрузка...Выделение текста в ячейке excel
Выделение ячеек c ТЕКСТом с применением Условного форматирования в EXCEL
Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.
Эта статья создана для пользователей, уже уверенно применяющих Условное форматирование. Для тех, кто только начинает использовать этот инструмент, рекомендуется ознакомиться с базовой статьей по этой теме Условное форматирование в MS EXCEL .
Исходная таблица с текстовыми значениями
Пусть в диапазоне А9:A17 имеется список с перечнем инструментов (см. файл примера ). Список содержит как ячейки с одним словом, так и текстовые строки (несколько строк, разделенных пробелами).
Задача 1 (поиск значений в списке, совпадающих с единственным критерием)
Выделим ячейки, содержащих значения, удовлетворяющих 1 критерию (без учета РЕгиСТра ). Критерий введем в ячейку B 6 . Кроме того, зададим 4 Типа поиска:
- Точно совпадает (выделение ячеек, содержащих значения в точности совпадающих с искомым текстом);
- Содержит (выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки);
- Начинается (выделение ячеек, которые содержат искомый текст в начале строки);
- Заканчивается (выделение ячеек, которые содержат искомый текст в конце строки).
В качестве примера Критерия используем текст « Дрель », который введем в ячейку B 6 . Естественно, для критерия можно использовать любой другой текст.
Решение
Выбор Типа поиска организуем с помощью Элементов управления формы Группа и Переключатель. Свяжем все Переключатели с ячейкой B9 . Теперь при выборе Типа поиска Точно совпадает в этой ячейке будет содержаться число 1, при выборе Содержит — 2, Начинается — 3, Заканчивается — 4 (см. файл примера Лист 1 критерий (текст) ).
Настроим Условное форматирование для выделения ячеек, удовлетворяющих критерию. Для этого:
- выделите ячейки, в которые введен перечень инструментов ( А9:A17 );
- вызовите инструмент Условное форматирование ( Главная/ Стили/ Условное форматирование/ Создать правило )
- выберите Использовать формулу для определения форматируемых ячеек;
- в поле « Форматировать значения, для которых следующая формула является истинной » введите =ВЫБОР($B$9;$A$6=A9;ПОИСК($A$6;A9);ПОИСК($A$6;A9)=1;$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)))
- выберите требуемый формат, например, красный цвет фона .
Если в качестве примера Критерия используется текст « Дрель », а Тип поиска выбран Точно совпадает (1), то красным фоном выделится только ячейка A14 , содержащая значение в точности совпадающее с искомым текстом.
Разберем работу формулы из вышеуказанного правила Условного форматирования .
Функция ВЫБОР() позволяет в одной формуле совместить 4 формулы для всех Типов поиска:
- Точно совпадает: $A$6=A9 ;
- Содержит: ПОИСК($A$6;A9) ;
- Начинается: ПОИСК($A$6;A9)=1 ;
- Заканчивается: $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) .
В зависимости от значения в ячейке B9 , связанной с Переключателями, функция ВЫБОР() возвращает соответствующую формулу.
Функция ПОИСК() пытается найти первое вхождение Критерия в каждом значении перечня и, в случае успеха, возвращает начальную позицию найденного значения. В противном случае возвращается ошибка #ЗНАЧ!
Формула ПОИСК($A$6;A9) возвращает номер позиции текстовой строки из ячейки А9 , с которой начинается текст, указанный в критерии (если он найден).
Например, для ячейки А14 формула ПОИСК($A$6;A14) вернет 1, а для А15 формула ПОИСК($A$6;A15) вернет 8 и обе ячейки будут выделены. Если в ячейке не содержится искомый текст, то формула возвращает ошибку и ячейка не выделяется.
Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка содержащая критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е. текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).
Формула $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) возвращает правую часть каждого значения перечня длиной равной критерию, т.е. в нашем случае 5 (т.к. в критерии «Дрель» 5 букв), а затем сравнивает ее с критерием. Если оба выражения совпадают, то это означает, что значение перечня заканчивается на текст, указанный в Критерии.
Примечание : Обратите внимание, что будет выделена ячейка со словом Мотодрель , как ячейка заканчивающаяся на критерий. Но, если нужно найти ячейки содержащие слово Дрель (т.е. текст, отделенный от других слов пробелами), то нужно обратиться к решению Задачи 2 (см. ниже).
Если критерий содержится в начале текстовой строки, то ПОИСК() вернет 1. Следовательно, формула ПОИСК($A$6;A9)=1 вернет ИСТИНА только для ячеек начинающихся с искомого текста.
Совет : Для настройки поиска ячеек, значения которых удовлетворяют критерию с учетом РЕгиСТРА, читайте статью Выделение ячеек c ТЕКСТом с применением Условного форматирования (с учетом РЕгиСТРА) .
Задача 2 (поиск СЛОВ в списке, совпадающих с единственным критерием)
Определить, есть ли слово в списке несколько сложнее, чем определить содержится ли в списке некий текст. Разница в этих подходах проявляется при наличии в списке слов, содержащих искомый текст, но не совпадающих с ним. Например, слово в списке Мотодрель содержит текст Дрель, но слово Дрель не содержится в этой ячейке.
Чтобы выделить ячейки в списке, которые содержат слово Дрель, нужно воспользоваться формулами в файле примера на Листе «1 критерий (слово)».
Понятно, что нужно выделить ячейки, в которых:
- точно содержится слово Дрель ИЛИ;
- текстовая строка начинается со слова Дрель ИЛИ;
- текстовая строка заканчивается словом Дрель ИЛИ;
- слово Дрель содержится в середине текстовой строки (критерий обрамлен пробелами).
В принципе можно записать одну большую формулу и не использовать дополнительные столбцы (C:F)
=ИЛИ(ИЛИ(ЕСЛИОШИБКА(A9=$A$6;0));ИЛИ(ЕСЛИОШИБКА(ПОИСК(» «&$A$6&» «;A9);0));ИЛИ(ЕСЛИОШИБКА(ПОИСК($A$6&» «;ЛЕВСИМВ(A9;ДЛСТР($A$6)+1));0));ИЛИ(ЕСЛИОШИБКА(» «&$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)+1);0)))
Задача 3 (поиск нескольких слов в списке)
Рассмотрим списки, элементами которых являются отдельные слова (не фразы). Выделять будем только ту ячейку, которая содержит любое из слов-критериев (см. файл примера Лист Неск критериев (слово точно) ).
Это можно сделать несколькими способами.
Вариант с использованием Массива констант и имени. В этом случае в правиле Условного форматирования нужно ввести формулу =ИЛИ($A2=Слова) , где Слова — это Имя массива констант .
Этот способ не очень удобен, т.к. при изменении критериев придется изменять массив констант в Диспетчере имен.
Вариант с использованием именованного диапазона. Формула =ИЛИ($B2=Слова_в_диапазоне) аналогична предыдущей, но имя Слова_в_диапазоне теперь ссылается на диапазон ячеек листа (см. статью Именованный диапазон ). При добавлении новых слов в ячейки в столбце F, придется переопределять именованный диапазон.
Вариант с использованием динамического диапазона . Динамический диапазон позволяет добавлять новые слова-критерии в столбец F и при этом не требуется модифицировать формулы и имена.
Вариант без использования имен . Формула =ИЛИ($D2=»слово1″;$D2=»слово2″;$D2=»слово3″) позволяет отказаться от использования имен. Платой за это станет необходимость изменения правила Условного форматирования при каждом изменении слов-критериев.
Задача 4 (поиск нескольких слов в списке, общий случай)
Рассмотрим списки, элементами которых являются фразы. Выделять будем только ту ячейку, которая содержит любое из слов-критериев: в начале, в середине или конце фразы или если фраза целиком совпадает с одним из слов-критериев (см. файл примера Лист Неск критериев (слово) ).
Слово Разряд в списке не встречается (склонения не считаются). А вот слово Техник встречается как в начале фраз, так и в середине и конце.
Не смотря на то, что формулы похожи на те, что использовались в Задаче 2, они от них принципиально отличаются, т.к. являются формулами массива .
Как выделить произвольное слово в ячейке в Excel?
Узнаем как можно выделить произвольное слово в ячейке (первое, последнее или n-ое по порядку) в Excel в двух вариациях: в виде сложной формулы и пользовательской функции.
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Задача выделения конкретных (по порядку) слов из предложения имеет достаточно широкое применение при структурировании и анализе данных, например, выделение части артикула из кода товара, имени или фамилии из полной записи ФИО и т.д.
Если данные представлены в однородном виде (к примеру, нужно выделить первое/второе/…/последнее слово в каждой ячейке столбца, где все данные содержат одинаковое количество слов), то можно воспользоваться инструментом Текст по столбцам и получить разделенные слова в соседних столбцах.
Не будем подробно останавливаться на этом способе, так как применять его не всегда можно (данные в этом случае имеют специфичную форму) и удобно (при разделении по столбцам добавляются новые столбцы, что изменяет структуру документа).
Изучим более универсальный способ, который будет работать для любого типа данных.
Однако перед решением задачи сначала найдем ответ на достаточно простой и очевидный вопрос.
Что в предложениях отделяет одно слово от другого?
В большинстве современных языков для разделения слов используется символ пробела, поэтому в первую очередь алгоритм будет строиться на поиске пробела, а далее мы уже будем выделять нужное слово.
Пойдем по порядку.
Выделение первого слова в ячейке в Excel
C помощью функции НАЙТИ найдем позицию первого пробела, а затем воспользуемся функцией ЛЕВСИМВ, которая возвращает указанное количество символов с начала текста (как раз до первого найденного пробела):
Вместо ЛЕВСИМВ можно воспользоваться функцией ПСТР, которая более универсальна и позволяет вернуть заданное количество символов начиная с указанной позиции, а не только с начала строки (в данном случае с 1, так как мы выделяем именно первое слово):
Недостаток такого способа в том, что если в ячейке содержится ровно 1 слово, то она вернет значение ошибки, так как пробелов в нем нет:
Поэтому дополнительно добавим проверку на ошибку — в случае если во фразе нет пробелов, то возвращаем исходный текст:
Выделение последнего слова в ячейке в Excel
С поиском последнего слова все несколько сложнее — мы не знаем сколько их во фразе, поэтому для начала определим количество пробелов в предложении. В этом нам помогут функции ПОДСТАВИТЬ с помощью которой мы сначала удалим все пробелы во фразе, и ДЛСТР, которая покажет символьную длину фразы.
Таким образом, разность длины исходной фразы и фразы без пробелов даст нам итоговое количество пробелов в ячейке.
Далее вновь воспользуемся функцией ПОДСТАВИТЬ и заменим последний пробел (его порядковый номер мы уже знаем) на специальный символ, который точно не встретится в предложении (обычно используются символы #, ^ и т.п.).
После чего функцией ПРАВСИМВ (возвращает указанное количество символов с конца текста) выделяем все символы от специального символа (в данном случае функцией НАЙТИ ищем позицию символа #) до конца предложения:
Как и в предыдущем варианте, если во фразе всего 1 слово, то формула выдаст ошибку, поэтому для корректной работы здесь также нужно будет добавить аналогичную проверку, что и в примере выше.
Теперь перейдем к самому сложному варианту — выделению произвольного по порядку слова.
Выделение n-го слова в ячейке в Excel
Воспользуемся следующим приемом — продублируем все пробелы между словами очень большое количество раз (заведомо больше длины фразы, например, 1 000 раз), после чего функцией ПСТР выделяем 1 000 символов умноженные на n (номер нужного слова), а затем функцией ПРАВСИМВ возвращаем только последнюю 1 000 символов (в котором как раз содержится искомое слово).
Далее удаляем все лишние пробелы и получаем:
Иначе говоря, алгоритм следующий — мы разбиваем предложение на блоки, состоящие из одного слова предложения и большого количества пробелов, а затем вытаскиваем нужный блок и удаляем оттуда лишние пробелы.
Если же мы хотим достать другое слово, то вместо *4 в примере выше, нужно просто поставить другой номер (1 для первого слова, 2 для второго и т.д.), а если указанный номер больше количества слов в предложении, то в качестве ответа будет получено последнее слово.
Также преимуществом данной формулы является ее универсальность, потому что ее можно использовать для любого по порядку слова — от первого до последнего, к тому же не нужно дополнительно делать проверку на длину фразы.
Пользовательская функция
Как видим, полученная формула получилась несколько громоздкой по размеру, поэтому для удобства дополнительно реализуем пользовательскую функцию с тем же функционалом.
Перейдем в редактор Visual Basic (быстрый переход комбинацией клавиш Alt + F11), создаём новый модуль (Insert -> Module) и вставляем туда код функции:
Условное форматирование по части текста в ячейке Excel
У нас имеется данные для анализа производимой продукции. Нам нужно автоматически выделить все изделия, которые были выпущены в 2006 году. К сожалению, в таблице недостает еще одного столбца содержащего значения года производства каждого изделия. Но видно, что в фрагменте идентификатора (ID) изделия указывается год производства. В таком случае нам нужно выполнить поиск по части текста в Excel. А потом мы будем использовать условное форматирование с текстовыми функциями в формуле. Благодаря этому молниеносно реализуем решение задачи.
Пример таблицы производимой продукции:
Чтобы на основе идентификатора выделить изделия 2006-го года выпуска выполним шаги следующих действий:
- Выделите диапазон ячеек A2:A10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
- Выберите: «Использовать формулу для определения форматируемых ячеек».
- Чтобы выполнить поиск части текста в ячейке Excel, введите формулу: =ПСТР(A2;5;4)=»2006″
- Нажмите на кнопку «Формат», чтобы задать красный цвет заливки для ячейки. И нажмите ОК на всех открытых окнах.
Экспонированные цветом изделия 2006-го года выпуска:
Далее разберем принцип действия формулы и ее модификации схожими текстовыми функциями.
Функция ПСТР и ее пример в использования условном форматировании
Чтобы легко понять, как удалось экспонировать цветом определенные значения с помощью условного форматирования разберем этапы действий в двух словах. Сначала мы извлекаем часть текста, а потом сравниваем его с требуемым значением. Но как из ячейки извлечь часть текста в Excel? Обратим внимание на функцию =ПСТР() в формуле правила. Данная функция возвращает часть строки, взятой с каждой ячейки столбца A, что указано в первом аргументе. Ссылка в первом аргументе должна быть относительной, так как формула применяется к каждой ячейке столбца A. Во втором аргументе функции указывается номер символа исходного текста, с которого должен начаться отрезок строки. В третьем аргументе указывается количество символов, которые нужно взять после определенного (во втором аргументе) символа исходного текста. В результате функция =ПСТР() возвращает только часть текста длинной 4 символа взятого начиная с 5-ой буквы в каждой ячейки из столбца А. Это «2005» или «2006». После функции стоит оператор сравнения к значению строки «2006». Если ячейка содержит такую часть текста значит ей будет присвоен новый формат.
Аналогичным способом можно использовать и другие текстовые функции в условном форматировании. Например, с помощью функции =ПРАВСИМВ() мы можем экспонировать цветом определенную группу товаров из партии C. Для этого нужно использовать формулу:
Здесь все просто функция позволяет выбрать часть текста из ячейки Excel, начиная с правой стороны исходного текста. Количество необходимых символов указано во втором аргументе функции ПРАВСИМВ. После чего все что возвращает функция сравнивается с значением строки «C».
Во всех выше описанных примерах очень легко применять текстовые функции в условном форматировании так как длина строк в исходных данных одинаковая. Но что, если у нас в исходных данных разного типа индикаторы с разной длинной символов, а нам все еще нужно выделять 2006-й год или группу «C».
В решении данной задачи нам поможет дополнительная текстовая функция в формуле =НАЙТИ(). В первом случаи формула будет выглядеть так:
Для удобного выделения идентификаторов с разной длинной текстовой строки товаров из группы «C» используем такую формулу:
Функция =НАЙТИ() выполняет поиск фрагмента текста в ячейке Excel. Потом возвращает номер символа, в котором был найдет текст, который задан в первом аргументе. Во втором аргументе указываем где искать текст. А третий аргумент – это номер позиции с какого символа вести поиск в исходном тексте. Третий аргумент позволяет нам смещаться по строке. Например, если в идентификаторе 2 раза используется символ «C». В таком случае третий аргумент пользователь задает в зависимости от ситуации.
Так как функция возвращает нужное число мы прекрасно используем ее в качестве аргументов для других функций (ПСТР и ПРАВСИМВ).
Выделение текста в ячейке excel
| Мир MS Excel » Вопросы и решения » Вопросы по VBA » Выделение заданного фрагмента текста в ячейке (Макросы Sub) |
Выделение заданного фрагмента текста в ячейке
| igrtsk | Дата: Пятница, 29.11.2013, 10:11 | Сообщение № 1 | |||||||||||||||||
Ответить | ||||||||||||||||||
|
