Как в таблице excel выделить по одному слову

Выделение содержимого ячеек в Excel

В этом курсе:

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

В Excel можно выделять содержимое ячеек, строк или столбцов.

Примечание: Если лист защищен, возможность выделения ячеек и их содержимого может быть недоступна.

Выделение ячеек

Чтобы выделить ячейку, щелкните ее. Для перехода к ячейке и ее выделения также можно использовать клавиатуру.

Чтобы выделить диапазон ячеек, выделите ячейку, а затем нажмите левую кнопку мыши и перетащите указатель.

Для этого также можно использовать SHIFT+клавиши со стрелками.

Чтобы выделить несмежные ячейки и диапазоны ячеек, выберите их, удерживая нажатой клавишу CTRL.

Выделение строк и столбцов

Выберите букву в верхней части столбца, чтобы выделить его целиком. Можно также щелкнуть любую ячейку в столбце и нажать клавиши CTRL+ПРОБЕЛ.

Выберите номер строки, чтобы выделить ее целиком. Можно также щелкнуть любую ячейку в строке и нажать клавиши SHIFT+ПРОБЕЛ.

Чтобы выделить несмежные строки или столбцы, выберите их номера, удерживая нажатой клавишу CTRL.

Выделение таблицы, списка или листа

Чтобы выделить список или таблицу, щелкните любую содержащуюся в них ячейку и нажмите клавиши CTRL+A.

Чтобы выделить весь лист, нажмите кнопку выделить все в верхнем левом углу.

Примечание: В некоторых случаях выделение одной ячейки может привести к выбору нескольких смежных ячеек. Советы о том, как устранить эту проблему, см. в публикации сообщества под названием Как предотвратить одновременное выделение нескольких ячеек в Excel?.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Как выделить произвольное слово в ячейке в 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) и вставляем туда код функции:

Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL

Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.

Эта статья создана для пользователей, уже уверенно применяющих Условное форматирование. Для тех, кто только начинает использовать этот инструмент, рекомендуется ознакомиться с базовой статьей по этой теме Условное форматирование в MS EXCEL.

Исходная таблица с текстовыми значениями

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

Задача 1 (поиск значений в списке, совпадающих с единственным критерием)

Выделим ячейки, содержащих значения, удовлетворяющих 1 критерию (без учета РЕгиСТра). Критерий введем в ячейку B6. Кроме того, зададим 4 Типа поиска:

• Точно совпадает (выделение ячеек, содержащих значения в точности совпадающих с искомым текстом);
• Содержит (выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки);
• Начинается (выделение ячеек, которые содержат искомый текст в начале строки);
• Заканчивается (выделение ячеек, которые содержат искомый текст в конце строки).

В качестве примера Критерия используем текст «Дрель», который введем в ячейку B6. Естественно, для критерия можно использовать любой другой текст.

Выбор Типа поиска организуем с помощью Элементов управления формы Группа и Переключатель. Свяжем все Переключатели с ячейкой 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 вернет ИСТИНА только для ячеек начинающихся с искомого текста.

Как вытащить число или часть текста из текстовой строки в Excel

Сегодня мы с вами рассмотрим весьма распространённую ситуацию, возникающую в работе экономиста связанную с анализом данных.

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

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

И здесь, очень многое зависит, насколько эффективно организована работа, насколько экономист владеет своим основным прикладным инструментом – программой Excel, знает ее возможности и эффективные приемы обработки информации. Ведь одну и туже задачу можно решать разными способами, затрачивая разное количество времени и усилий.

Рассмотрим конкретную ситуацию. Вам нужно подготовить отчёт в разрезе, который нельзя получить в бухгалтерской программе. Вы выгрузили в Excel отчет по проводкам (оборотно-сальдовую ведомость, карточку счета и т.д. – не суть важно) и видите, что для нормальной фильтрации данных или создания сводной таблицы для анализа данных у вас не хватает одного признака (аналитики, разреза, субконто и т.д.).

Критически взглянув на таблицу, вы видите, что необходимый вам признак операции находиться тут же в таблице, но не в отдельной ячейке, а внутри текста. Например, код филиала в наименовании документа. А вам как раз надо подготовить отчет по поставщикам в разрезе филиалов, т.е. по двум признакам, один из которых отсутствует в приемлемом для дальнейшей обработки информации виде.

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

Вся трудность, в том чтобы извлечь код из текстовой строки.

Возможна ситуация, когда этот код находиться всегда в начале текстовой строки или всегда в конце.

В этом случае, мы можем извлекать код или часть текста при помощи функций ЛЕВСИМВ и ПРАВСИМВ, которые возвращают заданное количество знаков соответственно с начала строки или с конца строки.

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Количество_знаков — необязательный аргумент. Количество символов, извлекаемых функцией ЛЕВСИМВ (ПРАВСИМВ).

«Количество_знаков» должно быть больше нуля или равно ему. Если «количество_знаков» превышает длину текста, функция ЛЕВСИМВ (ПРАВСИМВ) возвращает весь текст. Если значение «количество_знаков» опущено, оно считается равным 1.

Зная количество знаков, которые содержит код, мы легко извлечем необходимые символы.

Сложнее если нужные нам символы находятся в середине текста.

Извлечь число, текст, код и т.д. из середины текстовой строки может функция ПСТР, возвращает заданное число знаков из строки текста, начиная с указанной позиции.

=ПСТР(текст; начальная_позиция; количество_знаков)

Текст – обязательный аргумент. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция – обязательный аргумент. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Количество_знаков – обязательный аргумент. Указывает, сколько знаков должна вернуть функция ПСТР.

Самый простой случай – если код находиться на одном и том же месте от начала строки. Например, у нас наименование документа начинается всегда одинаково «Поступление товаров и услуг ХХ ….»

Наш признак «ХХ» — код филиала начинается с 29 знака и имеет 2 знака в своем составе.

В нашем случае формула будет иметь вид:

Однако не всегда все так безоблачно. Предположим, мы не можем со 100% уверенностью сказать, что наименование документа у нас во всех строках будет начинаться одинаково, но мы точно знаем, что признак филиала закодирован в номере документа следующим образом:

Первый символ – первая буква в наименовании филиала, второй символ – это буква Ф (филиал) и далее следует пять нулей «00000». Причем меняется только первый символ — первая буква наименования филиала.

Обладая такими существенными знаниями, мы можем смело использовать функцию ПОИСК, которая находит нужный нам текст в текстовой строке и возвращают начальную позицию нужного нам текста внутри всей текстовой строки.

=ПОИСК(искомый_текст; текст_для_поиска; [нач_позиция])

Искомый_текст – обязательный аргумент. Текст, который требуется найти.

Просматриваемый_текст – обязательный аргумент. Текст, в котором нужно найти значение аргумента искомый_текст.

Нач_позиция – необязательный аргумент. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.

Функция ПОИСК не учитывает регистр. Если требуется учитывать регистр, используйте функцию НАЙТИ.

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Обозначив меняющийся первый символ знаком вопроса (?), мы можем записать итоговую формулу для выделения кода филиала в таком виде:

Эта формула определяет начальную позицию кода филиала в наименовании документа, а затем возвращает два знака кода, начиная с найденной позиции.

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

Как правильно выделять текст в таблицах Excel 2010

В ячейку Excel вы можете поместить текст. Текст можно отформатировать, то есть задать для него шрифт, стиль, разбить на абзацы, разукрасить… Конечно, в отличие от программы Word, Excel предназначена для расчетов и разукрашивание текста скорее побочная возможность программы. Но тем не менее возможности такие есть.

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

• Выделение с помощью мышки. Подводим указатель мыши к началу нужного места, щелкаем левой кнопкой и, не отпуская ее, тащим выделение до конца строки, а потом вниз, до конца этого самого места. Текст будет выделяться голубым цветом. Отпустили кнопку — выделение закончилось. Если вы ошиблись, то щелкните кнопкой мышки еще раз или нажмите любую стрелочку управления курсором (←, →, ↑ или ↓): выделение пропадет и вы сможете попробовать снова. Чтобы выделить отдельное слово, щелкните левой кнопкой мышки на нем два раза.
• Выделение с помощью клавиш. Поставьте курсор в начало той части текста, которую вы хотите выделить, нажмите клавишу Shift, держите ее, а стрелочками управления курсором выделяйте текст. Если вы будете нажимать стрелочки ↑ и ↓, то выделение будет бежать по строкам, а если ← и →, то по буквам строки. Существует несколько сочетаний клавиш для выделения, если поставить курсор в середину строки:
  • Shift+Home — выделение от курсора до начала строки;
  • Shift+End — выделение от курсора до конца строки.

Что можно сделать с выделенным текстом? С него можно снять выделение. Для этого нужно щелкнуть кнопкой мышки на любом месте документа или нажать любую клавишу управления курсором. Но это, как вы догадываетесь, не все.

Предположим, вам нужно быстро перейти к ячейкам, содержащим определенный текст, или выбрать их на огромном листе, как вы можете быстро выбрать ячейки с определенным текстом в Excel? Вы можете быстро выбрать ячейки, содержащие определенный текст, в Excel следующим образом.

Использование функции поиска для выбора ячеек, содержащих определенный текст

. Kutools for Excel для выбора ячеек, содержащих определенный текст

Использование функции поиска для выбора ячеек, содержащих определенный текст

Например, у меня есть рабочий лист, содержащий данные студентов, теперь я хочу выбрать все ячейки, содержащие конкретное имя студента. Использование функции поиска в Excel для выбора ячейки, содержащей определенный текст, следующим образом:

1. Нажмите Главная > Найти и выбрать > НайтиИ Найти и заменить диалоговое окно появится. Введите нужный текст в поле Найти то, что раскрывающийся список. Смотрите скриншот:

2. Затем нажмите Найти все все необходимые тексты перечислены в следующем поле.

3. Затем нажмите Ctrl + , чтобы выбрать все значения в поле. Нажмите Закрыть , весь нужный текст выделен в диапазоне. Видно скриншоты:

Работы С Нами Найти функция, вы можете просто выбрать только ячейки, если вы хотите выбрать целые строки с определенным текстом, метод не будет работать.

. Kutools for Excel для выбора ячеек, содержащих определенный текст

Работы С Нами Выбрать определенные ячейки of Kutools for Excel, вы можете выбрать как ячейки, так и целые строки с определенными значениями на листе.

После установки Kutools for Excel, пожалуйста, сделайте следующее:(Бесплатная загрузка Kutools for Excel Сейчас!)

1. Выберите диапазон, в котором вы хотите найти конкретный текст.

2. Нажмите Кутулс > Выберите Инструменты > Выбрать определенные ячейки, см. снимок экрана:

3. в Выбрать определенные ячейки диалоговом окне укажите тип выбора что вам нужно, и выбирайте Комплект из Конкретный тип раскрывающийся список, затем введите значение, которое вы хотите выбрать. Смотрите скриншот:
Функции: Если вы хотите сопоставить все содержимое ячейки, выберите равным из раскрывающегося списка Конкретный тип.

4. Затем нажмите OK or Применить. Появится диалоговое окно, напоминающее количество выбранных ячеек, закройте его, и весь указанный текст будет выбран.

Заметки:

1. С Конкретный тип раскрывающийся список, вы можете выбрать любые другие критерии, которые соответствуют вашим потребностям.

2. С помощью этого инструмента вы можете выбирать ячейки, целые строки или целые столбцы, которые вам нужны. Если вы проверите Весь ряд, результат будет таким, как показано ниже:

3. С помощью этого инструмента вы также можете выбрать два конкретных текста одновременно. Проверьте Or указать, чтобы выбрать ячейки, содержащие один из двух конкретных текстов, отметьте И, означает выбрать ячейки, содержащие два конкретных текста. 

Вас может заинтересовать это:

Связанная статья:

Как выбрать ячейки по определенным критериям в 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, они от них принципиально отличаются, т.к. являются

формулами массива

.

Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке

​Смотрите также​ адрес своей таблицы.​ контекстном меню команду​ например «столбец» или​ Private Sub Worksheet_SelectionChange(ByVal​ заливки для ячеек,​Форматирование для строки будет​ который вы хотите​в поле стили​ языке. Эта страница​ и не использовать​Дрель (т.е. текст,​

​ 2, Начинается -​​ CTRL + A,​​Мы стараемся как​ последнего посещения каждого​ цвет, не смотря​Если значение в ячейке​ Закройте редактор Visual​

Задача1 — текстовые значения

​Исходный текст (Source Code).​ «строка»​ Target As Range)​ например – зеленый.​ применено только в​ использовать в группе​ ячеек.​ переведена автоматически, поэтому​ дополнительные столбцы (C:F)​ отделенный от других​ 3, Заканчивается -​ чтобы выбрать таблицу​

Решение1

​ можно оперативнее обеспечивать​ сотрудника. Например, сотрудник​ на то, что​​ удовлетворяет определенному пользователем​​ Basic и вернитесь​

​Должно открыться окно​​адрес ячейки, для которой​​ Dim WorkRange As​ И нажмите на​ том случаи если​Цвета темы​На листе выделите ячейки​ ее текст может​=ИЛИ(ИЛИ(ЕСЛИОШИБКА(A9=$A$6;0));​ слов пробелами), то​

​ 4 (см. файл​ данных в таблицу​​ вас актуальными справочными​​ Козлов первый раз​ ранее мы установили​ условию, то с​ в Excel. Чтобы​ редактора Visual Basic.​ мы хотим определить​

• ​ Range If Target.Cells.Count​ всех открытых окнах​ формула возвращает значения​или​ или диапазоны ячеек,​​ содержать неточности и​​ИЛИ(ЕСЛИОШИБКА(ПОИСК(» «&$A$6&» «;A9);0));​ нужно обратиться к​ примера Лист 1​ целиком, или нажмите​
• ​ материалами на вашем​
• ​ поехал на конференцию​
• ​ красный фон через​
• ​ помощью Условного форматирования​

​ использовать добавленные макросы,​​ Скопируйте в него​ значение этого параметра​ > 1 Then​ кнопку ОК.​ ИСТИНА. Принцип действия​

​Стандартные цвета​ которые нужно выделить.​ грамматические ошибки. Для​ИЛИ(ЕСЛИОШИБКА(ПОИСК($A$6&» «;ЛЕВСИМВ(A9;ДЛСТР($A$6)+1));0));​ решению Задачи 2​ критерий (текст)).​ кнопку большинство верхнюю​ языке. Эта страница​

​ 24.07.2009, а последний​ меню Главная/ Цвет​

​ можно выделить эту​​ нажмите сочетание клавиш​ этот текст этого​Хитрость в том, что​ Exit Sub ‘если​Все транзакции, проводимые во​ формулы следующий:​.​ Выделение ячеек?​ нас важно, чтобы​ИЛИ(ЕСЛИОШИБКА(» «&$A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)+1);0))​ (см. ниже).​Настроим Условное форматирование для​

Как это работает?

​ левую ячейку в​ переведена автоматически, поэтому​ раз — 18.07.2015.​ заливки.​ ячейку (например, изменить​ALT+F8​ простого макроса:​ второй аргумент не​ выделено больше 1​ вторник или в​Первая функция =СЦЕПИТЬ() складывает​

​Чтобы применить последний выбранный​На вкладке​ эта статья была​)​Формула $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)) возвращает правую​

​ выделения ячеек, удовлетворяющих​ таблице и нажмите​ ее текст может​Сначала создадим формулу для​В файле примера для​ ее фон). В​и действуйте аналогично​Private Sub Worksheet_SelectionChange(ByVal​ является обязательным. Если​ ячейки — выходим​ среду выделены цветом.​ в один ряд​ цвет текста, нажмите​Главная​ вам полезна. Просим​​Рассмотрим списки, элементами которых​​ часть каждого значения​ критерию. Для этого:​ клавиши CTRL +​ содержать неточности и​ условного форматирования в​ пояснения работы механизма​ этой статье пойдем​

​ способу 1.​ Target As Range)​ он не указан,​ If Coord_Selection =​У вас большой монитор,​

​ все символы из​ кнопку​в группе​

Рекомендации

​ вас уделить пару​ являются отдельные слова​ перечня длиной равной​выделите ячейки, в которые​​ SHIFT + END.​​ грамматические ошибки. Для​ столбцах В и​​ выделения строк, создана​​ дальше — будем​Excel MVP Jan Karel​

​ ActiveCell.Calculate End Sub​ то берется текущая​ False Then Exit​ но таблицы, с​ только одной строки​Цвет шрифта​Стили​ секунд и сообщить,​ (не фразы). Выделять​

​ критерию, т.е. в​ введен перечень инструментов​Нажмите клавиши CTRL +​ нас важно, чтобы​ E. Если формула​​ дополнительная таблица с​​ выделять всю строку​ Pieterse родом из​​Теперь при изменении выделения​​ активная ячейка.​ Sub ‘если выделение​​ которыми вы работаете​​ таблицы. При определении​

Задача2 — Даты

​.​щелкните нового пользовательского​ помогла ли она​ будем только ту​

​ нашем случае 5​ (​ A, два раза,​ эта статья была​ вернет значение ИСТИНА,​ формулой =$C7=$E$9 из правила​ таблицы, содержащую эту​ Нидерландов раздает у​ будет запускаться процесс​Вторая составляющая этого способа​

​ выключено — выходим​ — еще больше.​ условия форматирования все​Чтобы использовать цвет, отличающийся​ стиля ячейки, которые​ вам, с помощью​ ячейку, которая содержит​ (т.к. в критерии​

​А9:A17​ чтобы выделить таблицу​ вам полезна. Просим​ то соответствующая строка​

​ Условного форматирования для​​ ячейку.​ себя на сайте​ пересчета формулы с​ — условное форматирование.​ Application.ScreenUpdating = False​ И, пробегая взглядом​

​ ссылки указываем на​ от имеющихся стандартных​ вы создали.​ кнопок внизу страницы.​ любое из слов-критериев​ «Дрель» 5 букв),​);​ целиком, включая заголовки​

​ вас уделить пару​ будет выделена, если​ зеленого цвета. Формула​Пусть в диапазоне​ бесплатную надстройку​ функцией​

​ Эта крайне полезная​

​ Set WorkRange =​​ по экрану в​​ первую строку таблицы.​ цветов или цветов​Примечание:​ Для удобства также​ (см. файл примера​ а затем сравнивает​вызовите инструмент Условное форматирование​ таблицы.​

​ секунд и сообщить,​​ ЛОЖЬ, то нет.​​ введена в верхнюю​А6:С16​FollowCellPointer​ЯЧЕЙКА​ функция Excel позволяет​ Range(«A6:N300») ‘адрес рабочего​ поисках нужной информации,​Абсолютные и относительные адреса​ темы, нажмите кнопку​ Стили ячеек с настраиваемым​ приводим ссылку на​ Лист Неск критериев (слово​ ее с критерием.​ (Главная/ Стили/ Условное​​Выделяем ячейки, содержащие искомый​​ помогла ли она​

​В столбце D создана​ левую ячейку и​​имеется таблица с​​(36Кб), которая решает ту​в условном форматировании​ автоматически форматировать ячейки,​ диапазона, в пределах​​ всегда есть шанс​​ ссылок в аргументах​Другие цвета​ отображаются в верхней​ оригинал (на английском​ точно)).​ Если оба выражения​ форматирование/ Создать правило)​ текст. Рассмотрим разные​ вам, с помощью​ формула массива =МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7, которая​ скопирована вниз и​

​ перечнем работ, сроками​​ же задачу, отрисовывая​​ и заливаться текущая​ если они удовлетворяют​ которого видно выделение​

excel2.ru

Выделение строк и столбцов в таблице Excel

​ «соскользнуть» взглядом на​​ функций позволяют нам​, а затем в​ части списка стилей​ языке) .​Это можно сделать несколькими​ совпадают, то это​выберите Использовать формулу для​ варианты: выделение ячеек,​ кнопок внизу страницы.​ определяет максимальную дату​ вправо.​ выполнения и статусом​ с помощью макросов​ строка и столбец.​ заданным условиям. Если​ Intersect(WorkRange, Union(Target.EntireColumn, Target.EntireRow)).Select​ соседнюю строчку и​ распространять формулу на​ диалоговом окне​ ячеек. Если вы​В отличие от других​

​ способами.​ означает, что значение​ определения форматируемых ячеек;​ содержащих значения в​ Для удобства также​ для определенного сотрудника.​Как видно из рисунка,​ их завершения (см.​ графические линии-стрелки для​

support.office.com

Выделение ячеек c ТЕКСТом с применением Условного форматирования в MS EXCEL

​Примечание​выберите требуемый формат, например,​ начале, в конце​ диапазонов в таблице​ то формула значительно​В формуле использована относительная​ работа не начата,​ попробовать точно стоит.​ ячейками.​ через условное форматирование:​ именно в пределах​ себя деревянную линейку,​Обе выше описанные функции​выберите нужный цвет.​ из первого стили​

​ или отдельных частей​ формулу =ИЛИ($A2=Слова), где​: Обратите внимание, что​ красный цвет фона​ или середине строки.​ так же, как​ упростится =$B7=МАКС($B$7:$B$16) и формула​ ссылка на строку​ то строку будем​ Качаем архив, распаковываем​

Исходная таблица с текстовыми значениями

​Нет риска удалить целую​​Выделяем нашу таблицу, т.е.​​ этого диапазона и​ чтобы приложить ее​ работают внутри функции​Чтобы изменить формат, нажмите​ шесть ячеек в​ данных в ячейке.​ Слова — это Имя​ будет выделена ячейка​

Задача 1 (поиск значений в списке, совпадающих с единственным критерием)

​ .​ Также научимся выделять​ выбрать их на​ массива не понадобится.​ ($C7, перед номером​​ выделять красным, если​​ на диск и​ строку и столбец​

• ​ те ячейки, в​ будет работать наше​ к строке на​ =ЕСЛИ() где их​
• ​ кнопку​ списке, можно щелкнуть​Тем не менее, можно​ массива констант.​ со словом Мотодрель,​
• ​Протестируем:​ ячейку, в случае​ листе, но выбор​
• ​Теперь выделим все ячейки​ строки нет знака​ работа еще не​

​ устанавливаем надстройку:​ с данными при​ которых в будущем​ выделение. Затем закройте​​ мониторе. Технологии будущего!​​ результаты сравниваются между​Полужирный​ этот стиль ячеек,​

Решение

​ имитировать основные положения​Этот способ не очень​ как ячейка заканчивающаяся​Если в качестве примера​ если ее значение​ строк и столбцов​​ таблицы без заголовка​​ $). Отсутствие знака​ завершена, то серым,​в Excel 2003 и​ случайном нажатии​ должно отображаться координатное​ редактор Visual Basic​А если при движении​ собой. Это значит,​,​ непосредственно в группе​ на ячейку в​

​ удобен, т.к. при​ на критерий. Но,​ Критерия используется текст​

• ​ совпадает с одним из​ таблицы отличается от​ и создадим правило​​ $ перед номером​​ а если завершена,​
• ​ старше — через​Delete​ выделение.​
• ​ и вернитесь в​ активной ячейки по​
• ​ что в каждой​Курсив​Стили​ лист заполнить ячейки​
• ​ изменении критериев придется​ если нужно найти​ «Дрель», а Тип​

​ нескольких заданных значений.​

​ выбора листа строк​ Условного форматирования. Скопируем​ строки приводит к​ то зеленым. Выделять​ меню​.​В Excel 2003 и​​ Excel.​​ листу будет подсвечиваться​​ ячейке выделенного диапазона​​или​

​.​ цветом выделения. Быстрый​ изменять массив констант​

​ ячейки содержащие​ поиска выбран Точно​Эта статья создана для​ и столбцов.​

• ​ формулу в правило​
• ​ тому, что при​
• ​ строки будем с​
• ​Сервис — Надстройки -​

​Макросы используются минимально​ более старших версиях​​Нажмите сочетание клавиш​​ текущая строка и​ наступает сравнение значений​подчеркнуть​

​Выделите ячейку, к которой​ способ имитации выделенный​ в Диспетчере имен.​слово​ совпадает (1), то​ пользователей, уже уверенно​Чтобы выделить​ (ее не нужно​ копировании формулы вниз​

​ помощью правил Условного форматирования.​ Обзор (Tools -​Минусы этого способа:​​ открываем меню​​ALT+F8​ столбец? Своего рода​ в текущей строке​.​

​ применен подсветки, который​​ текст можно создать​​Вариант с использованием именованного​Дрель (т.е. текст,​​ красным фоном выделится​​ применяющих Условное форматирование.​Выполните указанное действие​ вводить как формулу​ на 1 строку​Создадим небольшую табличку со​ Add-Ins — Browse)​Формулу для условного форматирования​Формат — Условное форматирование​

​, чтобы открыть окно​​ координатное выделение примерно​ со значениями всех​Сочетание клавиш можно также​ вы хотите использовать.​ пользовательского стиля ячейки,​ диапазона.​ отделенный от других​​ только ячейка​​ Для тех, кто​Столбец таблицы с заголовком​ массива!).​ она изменяется на =$C8=$E$9,​ статусами работ в​в Excel 2007 и​

​ надо вводить вручную.​ — Формула (Format​ со списком доступных​ такого вида:​ строк таблицы.​ нажать сочетание клавиш​На вкладке «​ которые можно применить,​Формула =ИЛИ($B2=Слова_в_диапазоне) аналогична​ слов пробелами), то​A14​ только начинает использовать​ или без него​​Теперь предположим, что столбец​​ затем на =$C9=$E$9, потом​ диапазоне​

​ новее — через​​Нет быстрого способа включить-выключить​ — Conditional Formatting​ макросов. Макрос​Поудобнее, чем линейка, правда?​Как только при сравнении​ CTRL + B,​Главная​​ чтобы заполнить ячейки​​ предыдущей, но имя​ нужно обратиться к​, содержащая значение​ этот инструмент, рекомендуется​Щелкните верхний край заголовка​ с датами отсортировали​

​ на =$C10=$E$9 и т.д.​Е6:Е9​Файл — Параметры -​ такое форматирование -​ — Formula)​Selection_On​Есть несколько способов разной​ совпадают одинаковые значения​

​ CTRL + ли​
​» в группе​ цветом выделения. Затем​ Слова_в_диапазоне теперь ссылается​ решению Задачи 2​в точности совпадающее​ ознакомиться с базовой​ столбца или столбца​ и требуется выделить строки​

Задача 2 (поиск СЛОВ в списке, совпадающих с единственным критерием)

​ до конца таблицы​​.​​ Надстройки — Перейти​ оно включено всегда,​. В Excel 2007​, как нетрудно догадаться,​ сложности, чтобы реализовать​ (находятся две и​ или CTRL +​ «​ после применения этот​ на диапазон ячеек​ (см. ниже).​с искомым текстом.​​ статьей по этой​​ в таблице. Чтобы​​ у которых даты​​ (см. ячейки​Выделим диапазон ячеек​

​ — Обзор (File​ пока не будет​​ и новее -​​ включает координатное выделение​ такое. Каждый способ​ более одинаковых строк)​ U.​

​буфер обмена​ стиль ячеек для​

• ​ листа (см. статью​Если критерий содержится в​
• ​Разберем работу формулы из​ теме Условное форматирование в​
• ​ указать, что выделение​ посещения попадают в​
• ​G8G9G10​А7:С17​ — Excel Options​

​ удалено правило.​ жмем на вкладке​ на текущем листе,​ — со своими​

​ это приводит к​
​Достаточно часто рабочие таблицы​
​» дважды щелкните​
​ выделения ячеек можно​
​ Именованный диапазон). При​

Задача 3 (поиск нескольких слов в списке)

​ начале текстовой строки,​ вышеуказанного правила Условного​ MS EXCEL.​ столбца по щелчку​ определенный диапазон.​и т.д.). При​, содержащий перечень работ,​ — Add-Ins -​Золотая середина. Используем механизм​

​Главная (Home)​ а макрос​

​ плюсами и минусами.​ суммированию с помощью​​ Excel содержат повторяющиеся​Формат по образцу​ быстро скопировать выделение​ добавлении новых слов​ то ПОИСК() вернет​ форматирования.​

​Пусть в диапазоне​ появится стрелка выделения​Для этого используйте формулу =И($B23>$E$22;$B23​ копировании формулы вправо​ и установим через​

​ Go to -​ отслеживания выделения на​​кнопку​Selection_Off​ Давайте разберем их​ функции =СУММ() числа​ записи, которые многократно​и перетащите указатель​ данных в другие​ в ячейки в​ 1. Следовательно, формула​Функция ВЫБОР() позволяет в​

​А9:A17​ для следующих.​​Для ячеек​ или влево по​ меню Главная/ Цвет​ Browse)​ листе при помощи​Условное форматирование — Создать​

​- выключает его.​​ детально.​ 1 указанного во​ дублируются. Но не​ мыши через как​ ячейки с помощью​ столбце F, придется​ ПОИСК($A$6;A9)=1 вернет ИСТИНА​

Задача 4 (поиск нескольких слов в списке, общий случай)

​ одной формуле совместить​имеется список с​Примечание:​Е22Е23​ столбцам, изменения формулы​ заливки фон заливки​Приветствую. Такая беда. в​ макросов из способа-1​ правило (Conditional Formatting​ В этом же​Самый очевидный путь для​ втором аргументе функции​ всегда повторение свидетельствует​

​ много ячейки или​Формата по образцу​ переопределять именованный диапазон.​ только для ячеек​ 4 формулы для​ перечнем инструментов (см.​ Щелкнуть верхний край один​с граничными датами​

​ не происходит, именно​ красный (предполагаем, что​ одном столбце есть​ и добавим к​ — Create Rule)​ окне, нажав кнопку​ решения нашей проблемы​ =ЕСЛИ(). Функция СУММ​

excel2.ru

Выделение ячеек

​ об ошибке ввода​​ диапазоны ячеек, которые​.​Вариант с использованием динамического​ начинающихся с искомого​ всех Типов поиска:​ файл примера). Список​ раз, выделяются данные​ (выделены желтым) использована​ поэтому цветом выделяется​ все работы изначально​ куча строк типа​ нему безопасное выделение​и выбираем тип​Параметры (Options)​ «в лоб» -​ позволяет сложить одинаковые​ данных. Иногда несколько​ нужно выделить.​Если вы хотите сделать​ диапазона​ текста.​

​Точно совпадает: $A$6=A9;​ содержит как ячейки​ в столбец; Дважды​ абсолютная адресация $E$22 и $E$23.​ вся строка.​ находятся в статусе​проводка купить​ цветом с помощью​

​ правила​можно назначить этим​ нам нужен макрос,​ строки в Excel.​ раз повторяющиеся записи​Когда все будет готово,​ определенные данные в​. Динамический диапазон позволяет​Совет:​Содержит: ПОИСК($A$6;A9);​ с одним словом,​ щелкнув его выделяется​ Т.к. ссылка на​В случае затруднений можно​ Не начата).​проводка оптом​ условного форматирования из​​Использовать формулу для определения​​ макросам сочетания клавиш​

​ который будет отслеживать​Если строка встречается в​ с одинаковыми значениями​ нажмите кнопку​ ячейке выделения, можно​ добавлять новые слова-критерии​Для настройки поиска​

Создайте стиль ячеек для выделения ячеек

1. ​Начинается: ПОИСК($A$6;A9)=1;​​ так и текстовые​​ весь столбец таблицы.​​ них не должна​​ потренироваться на примерах,​

   ​Убедимся, что выделен диапазон​​проводка цена​

   • ​ способа-2.​​ форматируемых ячеек (Use​​ для удобного запуска.​​ изменение выделения на​ таблице только один​

   • 

2. ​ были сделаны намеренно.​​Формат по образцу​​ отобразить эти данные​ в столбец F​

   ​ ячеек, значения которых​​Заканчивается: $A$6=ПРАВСИМВ(A9;ДЛСТР($A$6)).​​ строки (несколько строк,​​Можно также щелкнуть любую​

3. ​ меняться в правилах УФ​​ приведенных в статье Условное​​ ячеек​

4. ​и так 5​​Откройте лист со таблицей,​​ formula)​​Плюсы этого способа:​​ листе и выделять​ раз, то функция​ Тогда проблема может​еще раз или​​ в другой цвет​​ и при этом​

5. ​ удовлетворяют критерию с​​В зависимости от значения​​ разделенных пробелами).​ ячейку столбца и​​ для всех ячеек​​ форматирование в MS​

   ​А7:С17 А7​ тысяч раз в​​ в которой хотите​​Вводим формулу для нашего​относительная простота реализации​

6. ​ целую строку и​ =СУММ() вернет значение​ возникнуть при обработке,​ нажмите клавишу ESC,​

7. ​ шрифта или в​​ не требуется модифицировать​​ учетом РЕгиСТРА, читайте​​ в ячейке​​Выделим ячейки, содержащих значения,​ нажмите клавиши CTRL​ таблицы.​

   ​ EXCEL.​​должна быть активной​ разных вариациях. мне​ получить такое координатное​ координатного выделения:​выделение — операция безобидная​ столбец для текущей​​ 1, а целая​​ поиске данных или​ чтобы отключить его.​ другом формате.​ формулы и имена.​ статью Выделение ячеек​B9​ удовлетворяющих 1 критерию​​ + ПРОБЕЛ или​​Для ячейки​

Используйте формат по образцу для применения выделенный текст в другие ячейки

1. ​Прием с дополнительной таблицей можно​ ячейкой). Вызовем команду​ надо взять оттуда​

2. ​ выделение. Щелкните правой​​=ИЛИ(ЯЧЕЙКА(«строка»)=СТРОКА(A2);ЯЧЕЙКА(«столбец»)=СТОЛБЕЦ(A2))​​ и никак не​ ячейки. Также желательно​​ формула возвращает –​​ анализе в такой​​В ячейке выделите данные,​ ​Выберите​Вариантбез использования имен​ c ТЕКСТом с​, связанной с Переключателями,​ (без учета РЕгиСТра).​

3. ​ щелкните первую ячейку​В22​​ применять для тестирования​​ меню Условное форматирование/​ ВСЕ ключи со​ кнопкой мыши по​

Отображение определенных данных в другой цвет шрифта или в другом формате

1. ​=OR(CELL(«row»)=ROW(A1),CELL(«column»)=COLUMN(A1))​ изменяет содержимое или​ иметь возможность при​ ЛОЖЬ (ведь 1​ таблице. Чтобы облегчить​

   ​ которые нужно отобразить​

   ​Главная​	​. Формула =ИЛИ($D2=»слово1″;$D2=»слово2″;$D2=»слово3″) позволяет​
   ​ применением Условного форматирования​	​ функция ВЫБОР() возвращает​ Критерий введем в​ в столбце таблицы​использована смешанная адресация​ любых формул Условного форматирования.​
   ​ Создать правило /​	​ словом опт, например,​ ярлычку листа и​Эта формула проверяет,​ форматирование ячеек листа,​ необходимости включать и​ не является больше​
   ​ себе работу с​	​ в другой цвет​>​ отказаться от использования​ (с учетом РЕгиСТРА).​ соответствующую формулу.​ ячейку​ и нажмите клавиши​ $B23, т.е. ссылка​При вводе статуса работ​

2. ​ Использовать формулу для​​ и выделить в​​ выберите в контекстном​​ не совпадает ли​​ все остается как​ отключать эту функцию,​

   • ​ чем 1).​ такими таблицами, рекомендуем​ или в другом​​новые стили ячеек​ ​ имен. Платой за​Определить, есть ли​Функция ПОИСК() пытается найти​​B6​​ CTRL + SHIFT​​ на столбец В​​ важно не допустить​

   • ​ определения форматируемых ячеек.​ соседний столбецлист. а​ меню команду​​ номер столбца каждой​ ​ есть​

   • ​ чтобы такое крестообразное​Если строка встречается в​ автоматически объединить одинаковые​ формате.​​.​​ это станет необходимость​слово​​ первое вхождение Критерия​​. Кроме того, зададим​​ + Стрелка вниз.​​ не должна меняться​​ опечатку. Если вместо​​в поле «Форматировать значения,​

   • ​ потом еще какое-нибудь​Исходный текст (Source Code).​​ ячейки в таблице​ ​Минусы этого способа:​​ выделение не мешало​ ​ таблице 2 и​​ строки в таблице​ ​Выделение данных в ячейке​

     ​Примечания:​ изменения правила Условного​в списке несколько​ в каждом значении​ 4 Типа поиска:​Примечание:​

support.office.com

Как найти одинаковые строки в Excel и выделить их цветом

​ (для этого стоит​ слово Завершен​ для которых следующая​ слово, типа производитель​Должно открыться окно​ с номером столбца​такое выделение некорректно работает​ нам вводить, например,​ более раза формула​ Excel, выделив их​Чтобы выделить содержимое ячейки​ ​ форматирования при каждом​ сложнее, чем определить​ перечня и, в​Точно совпадает (выделение ячеек,​ Один раз клавиши CTRL​ перед В знак​а​ формула является истинной»​ и опять туда​

Как объединить одинаковые строки одним цветом?

​ редактора Visual Basic.​ текущей ячейки. Аналогично​ в том случае,​ формулы, а работало​ будет возвращать значение​

1. ​ цветом.​Выполните следующие действия​Если вы не видите​ изменении слов-критериев.​ содержится ли в​ случае успеха, возвращает​ содержащих значения в​ + ПРОБЕЛ выделяются​ $), а вот​, например, пользователь введет​
2. ​ нужно ввести =$C7=$E$8​ же. слов немного,​ Скопируйте в него​ со столбцами. Таким​ если на листе​
3. ​ только тогда, когда​ ИСТИНА и для​
4. ​Чтобы найти объединить и​В ячейке​Стиля ячейки​Рассмотрим списки, элементами которых​ списке некий текст.​ начальную позицию найденного​ точности совпадающих с​

​ данные в столбце;​ ссылка на строку​ Завершен​ (в ячейке​

​ поэтому мне надо​

Как выбрать строки по условию?

​ этот текст этих​ образом закрашенными окажутся​ есть объединенные ячейки​ мы просматриваем список​ проверяемой строки присвоится​ выделить одинаковые строки​

​Дважды щелкните ячейку, а​, нажмите кнопку Дополнительные​ являются фразы. Выделять​ Разница в этих​ значения. В противном​ искомым текстом);​ два раза клавиши​ должна меняться в​

​о​Е8​ скрипт хотя бы​ трех макросов:​ только те ячейки,​

​ — выделяются сразу​ в поисках нужной​ новый формат, указанный​ в Excel следует​

​ затем протащите указатель​рядом с коллекцией​ будем только ту​ подходах проявляется при​ случае возвращается ошибка​Содержит (выделение ячеек, которые​ CTRL + ПРОБЕЛ​ зависимости от строки​, то Условное форматирование не​находится значение В​ на единичное действие.​

​Dim Coord_Selection As​ у которых либо​ все строки и​ информации. Это приводит​ пользователем в параметрах​ выполнить несколько шагов​ мыши, чтобы охватить​ стилей ячеек.​ ячейку, которая содержит​ наличии в списке​ #ЗНАЧ!​ содержат искомый текст​

​ выделяет весь столбец​ таблицы (иначе все​ сработает.​ работе). Обратите внимание​ через поиск нашел​ Boolean Sub Selection_On()​ номер столбца, либо​ столбцы, входящие в​ нас к трем​

​ правила (заливка ячеек​ простых действий:​ содержимое ячейки, которое​В поле​ любое из слов-критериев:​ слов, содержащих искомый​Формула ПОИСК($A$6;A9) возвращает номер позиции​ в начале, в​ таблицы.​ значения дат будут​

Как найти и выделить дни недели в датах?

​Чтобы исключить некорректный ввод​ на использоване смешанных​ только советы использовать​ Coord_Selection = True​ номер строки совпадает​ объединение​ макросам (выделения, включения​ зеленым цветом).​Выделите весь диапазон данных​ требуется выделить.​Имя стиля​ в начале, в​ текст, но не​ текстовой строки из​ конце или середине​

1. ​Строка таблицы​ сравниваться с датой​ используйте идеи из​ ссылок;​
2. ​ фильтр и какой​ End Sub Sub​ с текущей ячейкой.​если случайно нажать клавишу​ и выключения), которые​
3. ​Допустим таблица содержит транзакции​ табличной части A2:F18.​
4. ​В строка формул​введите имя нового​ середине или конце​ совпадающих с ним.​ ячейки​ строки);​Щелкните левой границы строки​

​ из​ статьи Ввод данных​нажать кнопку Формат;​

exceltable.com

Координатное выделение

​ то бешеный скрипт,​ Selection_Off() Coord_Selection =​ А это и​ Delete, то очистится​ нужно будет добавить​ с датами их​ Начинайте выделять значения​Щелкните ячейку, а затем​ стиля ячейки.​ фразы или если​ Например, слово в​А9​Начинается (выделение ячеек, которые​ таблицы. Чтобы указать,​В23​ из списка значений.​выбрать вкладку Заливка;​ который вешает эксель​ False End Sub​

​ есть крестообразное координатное​ не только активная​ в модуль листа.​ проведения. Необходимо найти​ из ячейки A2,​ протащите указатель мыши,​Совет:​

​ фраза целиком совпадает​

​ списке Мотодрель содержит​, с которой начинается​ содержат искомый текст​ что при щелчке​).​ Часть1. Выпадающий список.​выбрать серый цвет;​

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

​ — создает кучу​ Private Sub Worksheet_SelectionChange(ByVal​ выделение, которого мы​ ячейка, а вся​Откройте лист со таблицей,​ одну из них,​ так чтобы после​ чтобы охватить содержимое​ Например введите​ с одним из​текст​ текст, указанный в​ в начале строки);​ выделяется строка появится​Таким образом, правило УФ​ В файле примера​Нажать ОК.​ новых листов.​ Target As Range)​ хотим добиться.​ выделенная область, т.е.​ в которой хотите​ но неизвестны все​ выделения она оставалась​ ячейки, которое требуется​выделения​

​ слов-критериев (см. файл​Дрель, но​ критерии (если он​Заканчивается (выделение ячеек, которые​ стрелка выделения для​ например для ячейки​ для ввода статусов​ВНИМАНИЕ​​насчет фильтра. проставляю​​ Dim WorkRange As​Нажмите кнопку​ удалятся данные из​ получить такое координатное​ детали. Известно только,​

​ активной как показано​ выделить в строке​.​ примера Лист Неск критериев​слово​ найден).​ содержат искомый текст​ следующих.​А27​ работ использован аналогичный​: Еще раз обращаю​ условие в нем,​ Range, CrossRange As​Формат (Format)​ всей строки и​ выделение. Щелкните правой​ что транзакция проведена​ ниже на рисунке.​ формул.​Нажмите кнопку​ (слово)).​Дрель не содержится​Например, для ячейки​ в конце строки).​Щелкните первую ячейку в​будет выглядеть =И($B27>$E$22;$B27А27 будет​ Выпадающий список.​ внимание на формулу =$C7=$E$8.​ что то он​ Range Set WorkRange​и задайте цвет​ столбца​

​ кнопкой мыши по​ во вторник или​ И выберите инструмент:​С помощью клавиатуры​Формат​Слово Разряд в списке​ в этой ячейке.​А14​В качестве примера Критерия​

​ строке таблицы и​​ выделена, т.к. в​​Чтобы быстро расширить правила​ Обычно пользователи вводят =$C$7=$E$8,​ выделяет и все,​​ = Range(«A7:N300») ‘адрес​​ заливки.​Этот способ хотя и​ ярлычку листа и​ в среду. Чтобы​​ «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».​​Нажмите клавишу F2, чтобы​.​ не встречается (склонения​​Чтобы выделить ячейки в​​формула ПОИСК($A$6;A14) вернет​ используем текст «Дрель»,​ нажмите клавиши CTRL​

​ этой строке дата​

• ​ Условного форматирования на​
• ​ т.е. вводят лишний​ копирую я либо​ рабочего диапазона с​Все почти готово, но​ имеет пару недостатков,​ выберите в контекстном​

​ облегчить себе поиск,​

• ​В появившемся окне «Создание​ открыть ячейку для​В диалоговом окне​ не считаются). А​ списке, которые содержат​ 1, а для​ который введем в​ + SHIFT +​
• ​ из​ новую строку в​ символ доллара.​ весь столбец, либо​ таблицей If Target.Count​ остался один нюанс.​ мне представляется весьма​ меню команду​

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

​ выделим цветом все​ правила форматирования» выберите​ редактирования, с помощью​Формат ячеек​ вот слово Техник​слово​А15​ ячейку​ Стрелка вправо.​

​В27​ таблице, выделите ячейки​Нужно проделать аналогичные действия​ несколько строчек (хотя​ > 1 Then​ Дело в том,​ изящным. Реализовать что-либо,​Исходный текст (Source Code).​ даты этих дней​ опцию: «Использовать формулу​

• ​ клавиш со стрелками​на вкладке​ встречается как в​
• ​Дрель, нужно воспользоваться​формула ПОИСК($A$6;A15) вернет​B6​

​Все строки и столбцы​попадает в указанный​ новой строки (​ для выделения работ​ выбираю 20, например)​ Exit Sub If​

​ что Excel не​ используя только встроенные​Должно открыться окно​ недели (вторник, среда).​ для определения форматированных​ расположите точку вставки​Заливка​ начале фраз, так​ формулами в файле​ 8 и обе​. Естественно, для критерия​ таблицы​ диапазон (для ячеек​

1. ​А17:С17​ в статусе Завершена.​Полосатый жираф алик​ Coord_Selection = False​ считает изменение выделения​
2. ​ средства Excel, минимально​ редактора Visual Basic.​ Для этого будем​​ ячеек».​ и нажмите клавиши​выберите цвет, который​ и в середине​​ примера на Листе​ ячейки будут выделены.​ можно использовать любой​​Щелкните в левом верхнем​​ из столбца А​​) и нажмите сочетание​ Формула в этом​: Пришли мне эту​​ Then WorkRange.FormatConditions.Delete Exit​ изменением данных на​​ влезая в программирование​ Скопируйте в него​ использовать условное форматирование.​
3. ​В поле ввода введите​ SHIFT + клавиша​
   ​ вы хотите использовать​
   ​ и конце.​
   
   ​ «1 критерий (слово)».​ Если в ячейке​ другой текст.​ углу таблицы. Чтобы​ выделение все равно​ клавиш​ случае будет выглядеть​ колонку. Нужно в​ Sub End If​ листе. И, как​ на VBA -​ этот текст этих​Выделите диапазон данных в​ формулу: 1′ class=’formula’>​ со стрелкой, чтобы​ для выделения и​Не смотря на то,​
4. ​Понятно, что нужно выделить​​ не содержится искомый​​Выбор Типа поиска организуем​ указать, что щелкнув​

​ будет производиться в​CTRL+D​ как =$C7=$E$9, а​ формате Excel 2003.​ Application.ScreenUpdating = False​ следствие, не запускает​ высший пилотаж ;)​ трех макросов:​ таблице A2:B11 и​Нажмите на кнопку формат,​ выделить содержимое.​ нажмите кнопку​ что формулы похожи​ ячейки, в которых:​ текст, то формула​ с помощью Элементов​ выделяются данные в​ зависимости от содержимого​. Правила Условного форматирования будут​ цвет заливки установите​​ Я напишу туда​​ If Not Intersect(Target,​ пересчет формул и​Способ основан на использовании​Dim Coord_Selection As​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​

​ чтобы задать цвет​На вкладке​ОК​

​ на те, что​точно содержится слово Дрель​ возвращает ошибку и​ управления формы Группа​​ таблицу целиком появится​​ столбца В из​ скопированы в строку​ зеленый.​

​ пару формул -​

• ​ WorkRange) Is Nothing​ перекраску условного форматирования​
• ​ функции ЯЧЕЙКА (CELL),​ Boolean ‘глобальная переменная​ форматирование»-«Создать правило».​
• ​ заливки для ячеек,​Главная​.​ использовались в Задаче​​ ИЛИ;​​ ячейка не выделяется.​
• ​ и Переключатель. Свяжем​

​ стрелка выделения для​

• ​ той же строки​17 ​
• ​В итоге наша таблица​ там поймешь, как​ Then Set CrossRange​ только при изменении​ которая может выдавать​

Способ 3. Оптимальный. Условное форматирование + макросы

​ для вкл/выкл выделения​В появившемся окне «Создание​ например – зеленый.​в группе​Нажмите кнопку​ 2, они от​текстовая строка начинается со​Примечание​ все Переключатели с​

​ следующих.​ — в этом​таблицы.​ примет следующий вид.​ сделать аналогичные выборки.​ = Intersect(WorkRange, Union(Target.EntireRow,​ положения активной ячейки.​ массу различной информации​​ Sub Selection_On() ‘макрос​​ правила форматирования» выберите​ И нажмите на​Шрифт​ОК​ них принципиально отличаются,​

​ слова Дрель ИЛИ;​: Обратите внимание, что​ ячейкой​Щелкните левый верхний угол​ и состоит «магия» смешанной​Предположим, что ведется журнал​Примечание​Алексей матевосов (alexm)​ Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add​ Поэтому добавим в​ по заданной ячейке​ включения выделения Coord_Selection​ опцию: «Использовать формулу​ всех открытых окнах​выполните одно из​, чтобы закрыть диалоговое​ т.к. являются формулами​текстовая строка заканчивается словом​ будет выделена ячейка​B9​ таблицы два раза,​ адресации $B23).​ посещения сотрудниками научных​: Условное форматирование перекрывает​: Можно автофильтром​ Type:=xlExpression, Formula1:=»=1″ CrossRange.FormatConditions(1).Interior.ColorIndex​ модуль листа простой​ — высоту, ширину,​ = True End​

​ для определения форматированных​ кнопку ОК.​ указанных ниже действий.​ окно «​ массива.​ Дрель ИЛИ;​ со словом Мотодрель,​. Теперь при выборе​​ чтобы выделить таблицу​​А для ячейки​ конференций (см. файл примера​

Способ 4. Красивый. Надстройка FollowCellPointer

​ обычный формат ячеек.​Ставите условие «Содержит»​ = 33 Target.FormatConditions.Delete​ макрос, который будет​ номер строки-столбца, числовой​​ Sub Sub Selection_Off()​​ ячеек».​В результате выделились все​Чтобы изменить цвет текста,​стиль​Примечание:​слово Дрель содержится в​

​ как ячейка содержащая​ Типа поиска Точно​ целиком, включая заголовки​В31​ лист Даты).​ Поэтому, если работа​

• ​ — «Оптом»​ End If End​ это делать. Щелкните​​ формат и т.д..​ ‘макрос выключения выделения​В поле ввода введите​
• ​ строки, которые повторяются​ щелкните стрелку рядом​​».​Мы стараемся как​ середине текстовой строки​ критерий. Но, если​ совпадает в этой​ таблицы.​правило УФ будет выглядеть =И($B31>$E$22;$B31В31​

planetaexcel.ru

Выделение строк с определенным словом в Excel

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

​Custom​​ материалами на вашем​
​ одну большую формулу​слово​
​ выборе Содержит -​ и нажмите клавиши​Примечание:​

​ дату первого и​