Выделение содержимого ячеек в Excel
В этом курсе:
- Вставка и удаление строк и столбцов
Статья - Выделение содержимого ячеек в Excel
Статья - Закрепление областей окна для блокировки строк и столбцов
Статья - Скрытие и отображение строк и столбцов
Статья - Фильтр уникальных значений или удаление повторяющихся значений
Статья - Разделение текста на столбцы с помощью мастера распределения текста по столбцам
Статья - Создание списка последовательных дат
Статья
Выделение содержимого ячеек в Excel
В Excel можно выделять содержимое ячеек, строк или столбцов.
Примечание: Если лист защищен, возможность выделения ячеек и их содержимого может быть недоступна.
Выделение ячеек
Чтобы выделить ячейку, щелкните ее. Для перехода к ячейке и ее выделения также можно использовать клавиатуру.
Чтобы выбрать диапазон, выберем ячейку, а затем, нажимая левую кнопку мыши, перетащите указатель мыши над другими ячейками.
Для этого также можно использовать SHIFT+клавиши со стрелками.
Чтобы выделить несмежные ячейки и диапазоны ячеек, выберите их, удерживая нажатой клавишу CTRL.
Выделение строк и столбцов
Выберите букву в верхней части столбца, чтобы выделить его целиком. Можно также щелкнуть любую ячейку в столбце и нажать клавиши CTRL+ПРОБЕЛ.
Выберите номер строки, чтобы выделить ее целиком. Можно также щелкнуть любую ячейку в строке и нажать клавиши SHIFT+ПРОБЕЛ.
Чтобы выделить несмежные строки или столбцы, выберите их номера, удерживая нажатой клавишу CTRL.
Выделение таблицы, списка или листа
Чтобы выделить список или таблицу, щелкните любую содержащуюся в них ячейку и нажмите клавиши CTRL+A.
Чтобы выбрать весь таблицу, нажмите кнопку Выбрать все в левом верхнем углу.
Примечание: В некоторых случаях выделение одной ячейки может привести к выбору нескольких смежных ячеек. Советы о том, как устранить эту проблему, см. в публикации сообщества под названием Как предотвратить одновременное выделение нескольких ячеек в Excel?.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Выделение ячеек c ТЕКСТом с применением Условного форматирования в EXCEL
history 16 апреля 2013 г.
- Группы статей
- Поиск ТЕКСТовых значений
- Условное форматирование
- Условное Форматирование Текстовых значений
Выделяем ячейки, содержащие искомый текст. Рассмотрим разные варианты: выделение ячеек, содержащих значения в точности совпадающих с искомым текстом; выделение ячеек, которые содержат искомый текст в начале, в конце или середине строки. Также научимся выделять ячейку, в случае если ее значение совпадает с одним из нескольких заданных значений.
Эта статья создана для пользователей, уже уверенно применяющих Условное форматирование. Для тех, кто только начинает использовать этот инструмент, рекомендуется ознакомиться с базовой статьей по этой теме Условное форматирование в 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, они от них принципиально отличаются, т.к. являются формулами массива .