Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Поиск и выделение конкретных значений
Опубликовано 13.04.2013 Автор Ренат Лотфуллин
Все мы знаем, как пользоваться инструментом “Найти и заменить» в Excel, чтобы найти определенное значение в книге. Иногда возникает необходимость просто выделить значения, которые нашел Excel.
В нашем примере фрагмент выгруженного отчета из системы управления производством, в котором система отделила тысячный разряд точкой “.”. Нам необходимо найти и выделить все значения, в которых присутствует точка.
Для начала воспользуемся инструментом «Найти и заменить», чтобы определить все ячейки с вхождением точки в значение. Для этого перейдем по вкладке Главная -> Редактирование –> Найти и выделить -> Найти. В появившемся диалоговом окне, ставим точку в поле поиска и жмем Найти далее.
При открытом диалоговом окне, нажмите Ctrl+A на клавиатуре. Это действие приведет не только к выделению всего списка найденных значений в диалоговом окне, но и выделит все найденные ячейки на листе.
Пока все необходимые ячейки выделены, вы можете залить их каким-нибудь цветом, для придания эффекта.
Данный подход также применим, когда вам необходимо найти и выделить все вешние ссылки, находящиеся в ячейках книги. Для этого, вместо того чтобы искать точку, вам требуется найти квадратную скобку “[“.
Почему открывающуюся квадратную скобку? Потому что Excel использует квадратные скобки [] для указания источника данных внешних ссылок.
Обратите внимание, что в этом случае Excel не найдет внешние ссылки, спрятанные в объектах или именованных диапазонах.
Вам также могут быть интересны следующие статьи
- Инструмент Найти и заменить в Excel
- Как отфильтровать список элементов из длинного-длинного списка
- Импорт данных с Excel книги, находящейся на веб-сайте
- Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
- Формулы массивов в Excel — синтаксис формул массивов и массивов констант
- SkyDrive Excel или Как организовать одновременную работу нескольких людей в одном файле
- Именованные диапазоны в Excel — несколько трюков использования
- Гиперссылка в Excel — создание, изменение и удаление
- Проверка данных с помощью выпадающего списка в ячейке
- Полезные сочетания клавиш в Excel, без которых я не могу обойтись
Рубрика: Ввод данных | Метки: Найти и заменить | 6 комментариев | Permalink
6 комментариев
Как таким же способом выделить все строки с этим значением?
Таким же способом строки не выделить, необходим отдельный макрос
Это не нахождение значения, это нахождение ячейки содержащей значение.
Может есть инструментарии(кроме макроса) находить символы и выделять их отдельно от ячейки? Например «0»
Здравствуйте,
Я смотрел Ваше руководство по вопросу найти определенное слово в Excel. У меня возникла одна задача и есть к Вам вопрос. А можно это слово которое ищешь по тексту в Excel затем, как нашел все варианты, заменить их на такое же самое слово, но только что бы оно было полужирным курсивом выделено по всей таблице. Я пытался так сделать с помощью найти и заменить, но оно заменяет на жирный формать весь текст, а не заданое слово. Что Вы мне посоветуете? Спасибо Вам зарание
Gunter O’Dimm
У меня был случай, когда мне дали базу емайлов, 300000 шт чето там.. и там были и жмаилы и рамблеры и яндексы и все.. и надо было отфильтровать из этого всего только яндексы. Делал так. поскольку в базе все адреса были выписаны в столбик, загрузил этот столбик в эксиль. 1. выделяем весь столбик. 2. ставим ему условное форматирование — красной заливкой.. в углу еще свободную ячейку тож красным заливаем, и под ней одну заливаем зеленым. 3.потом нажимаем контрл Ф, и переходим в замены, и пишем что найти надо *@yandex.ru и заменить на — поле оставляем пустым. нажимаем -параметры- -формат- и -выбрать формат из ячейки-. тыкаем в ту самую зеленую ячейку в углу. все адреса яндекса позеленели. потом жмем снова контрл ф, -замена-. Найти оставляем пустым, НО формат — выбрать формат из ячейки — тыкаем в в том уголке на красную ячейку. в -заменить на- поле должно быть пустым и справа -формат не задан- должно быть… если все правильно сделали — все емаилы удалятся — останутся только одни яндексы… ну а потом прост снимаем выделения эти цветные все и копипастим в ворд или нотепад куда над там и усё!)
Как при поиске в excel подсветить ячейку цветом
Когда нажимаем Ctrl+F и ищем в колонке нужную ячейку она выделяется неотчетливо на общем фоне.
Есть ли возможность с помощью настроек, макросов или надстроек сделать выделение ярче?
Изменено: Exhaust_ — 27.05.2013 20:11:01
Сообщений: 60949 Регистрация: 14.09.2012
Контакты см. в профиле
22.05.2013 23:43:13
Посмотрите координатное выделение в Приемах и поищите по старому форуму аналогичную тему.
Пользователь
Сообщений: 3289 Регистрация: 22.12.2012
Win7 MSO 2013
23.05.2013 01:22:25
Гиперссылка (для разнообразия)
файл с дополнениями в сообщении ниже
Изменено: Nic70y — 23.05.2013 21:18:59 ( удалил файл )
Пользователь
Сообщений: 137 Регистрация: 17.03.2013
23.05.2013 10:17:47
Наверное я не совсем понятно описал ситуацию
Фишка в особом выделении найденой ячейки
Теряюсь как сделать, может в настройках что то задается
В сложных примерах используются такие ходы с окраской
http://www.planetaexcel.ru/techniques/3/58/
Но разобрать это дело мне не по зубам.
ПС: А вообще круто было бы выделить все подходящие значения сразу
покупка
Поиск и выделение результатов поиска в Excel
В Excel вы можете использовать функцию «Найти и заменить», чтобы найти определенное значение, но знаете ли вы, как выделить результаты поиска после поиска? В этой статье я расскажу о двух различных способах облегчения поиска и выделения результатов поиска в Excel.
Искать и выделять результаты поиска по коду VBA
В Excel есть код VBA, который может помочь вам найти значение и затем выделить результаты поиска.
1. Включите лист, который вы хотите найти, и выделите результаты, нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте приведенный ниже код в новый модуль.
VBA: выделение результатов поиска
Sub FindRange() 'UpdatebyExtendoffice20190813 Dim xRg As Range Dim xFRg As Range Dim xStrAddress As String Dim xVrt As Variant xVrt = Application.InputBox(prompt:="Search:", Title:="www.extendoffice.com") If xVrt <> "" Then Set xFRg = ActiveSheet.Cells.Find(what:=xVrt) If xFRg Is Nothing Then MsgBox prompt:="Cannot find this value", Title:="www.extendoffice.com" Exit Sub End If xStrAddress = xFRg.Address Set xRg = xFRg Do Set xFRg = ActiveSheet.Cells.FindNext(After:=xFRg) Set xRg = Application.Union(xRg, xFRg) Loop Until xFRg.Address = xStrAddress If xRg.Count > 0 Then xRg.Interior.ColorIndex = 8 xRsp = MsgBox(prompt:="Do you want to cancel highlighting?", Title:="www.extendoffice.com", Buttons:=vbQuestion + vbOKCancel) If xRsp = vbOK Then xRg.Interior.ColorIndex = xlNone End If End If End Sub
3. Нажмите F5 нажмите клавишу, затем появится окно поиска, в котором вы можете ввести указанное вами значение.
4. Нажмите OK, совпадающие результаты были выделены цветом фона. Кроме того, появляется диалоговое окно с вопросом, отменить ли выделение. Нажмите OK чтобы отменить выделение и закрыть диалоговое окно, нажмите Отмена чтобы сохранить выделение и закрыть диалог.
Внимание:
1. Если подходящего значения не найдено, появится диалоговое окно с напоминанием.
2. Этот VBA работает для всего активного листа и нечувствителен к регистру.
Вы хотите повысить зарплату и много времени проводить с семьей?
Office Tab Повышает вашу эффективность на 50% в Microsoft Office прямо сейчас
Невероятно, но работать с двумя или более документами проще и быстрее, чем над одним.
По сравнению с известными браузерами инструмент с вкладками в Office Tab является более мощным и эффективным.
Сократите для вас сотни щелчков мыши и набора текста с клавиатуры каждый день, попрощайтесь с рукой мыши.
Если вы обычно работаете с несколькими документами, Office Tab будет большой экономией времени для вас.
30 — дневная бесплатная пробная версия , кредитная карта не требуется.
Поиск и выделение результатов поиска с помощью условного форматирования
В Excel функция условного форматирования также может автоматически выделять результаты поиска.
Предположим, что данные и окно поиска отображаются, как показано на скриншоте ниже, теперь выполните следующие действия:
1. Выберите диапазон, в котором нужно выполнить поиск, и нажмите Главная > Условное форматирование > Новое правило.
2. в Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. в Выберите тип правила раздел, затем перейдите в текстовое поле под Формат значений, где эта формула истинна, напишите = И ($ E $ 2 <> «», $ E $ 2 = A4) .
E2 — это ячейка, в которую вы поместите поисковое значение, а A4 — это первая ячейка в диапазоне, в котором вы выполняете поиск.
3. Нажмите Формат кнопку, перейдите к Формат ячеек диалога под Заполнять на вкладке выберите нужный цвет. Нажмите OK > OK чтобы закрыть диалоги.
С этого момента, когда вы вводите ключевое слово в ячейку E2, результаты поиска будут автоматически выделяться в указанном диапазоне.
Ищите и выделяйте результаты поиска с помощью удобного инструмента
Если вы хотите найти два или более значений и один раз выделить результаты поиска, Отметить ключевое слово особенность Kutools for Excel мог бы оказать вам услугу.
1. Выберите диапазон данных для поиска, щелкните Кутулс > Текст > Отметить ключевое слово.
2. Во всплывающем диалоговом окне введите значения, которые вы хотите найти, и разделите их запятыми в Ключевое слово текстовое окно. Затем выберите Отметить параметры и цвет шрифта, как вам нужно. Нажмите Ok.
Если вы хотите искать значение с учетом регистра, отметьте С учетом регистра флажок.
Тогда совпавшие результаты будут выделены другим цветом шрифта.
Для Отметить ключевое слово функция, вы также можете найти часть строки в диапазоне. Предположим, что поисковые ячейки содержат мяч или прыжок, введите мяч, прыжок в Ключевое слово текстовое поле, затем укажите настройки и нажмите Ok.
Файл примера
Другие операции (статьи), связанные с форматированием Conditioanl
Подсчет / суммирование ячеек по цветам с условным форматированием в Excel
Теперь это руководство расскажет вам о некоторых удобных и простых методах быстрого подсчета или суммирования ячеек по цвету с условным форматированием в Excel.
Создать диаграмму с условным форматированием в Excel
Например, у вас есть таблица оценок для класса, и вы хотите создать диаграмму для цветных оценок в разных диапазонах. В этом руководстве представлен метод решения этой задачи.
Гистограмма с накоплением условного форматирования в Excel
В этом руководстве показано, как создать столбчатую диаграмму с условным форматированием, как показано на скриншоте ниже, шаг за шагом в Excel.
Условное форматирование строк или ячеек, если два столбца равны в Excel
В этой статье я представляю метод условного форматирования строк или ячеек, если два столбца равны в Excel.
Применить условное форматирование для каждой строки в Excel
Иногда может потребоваться применить условное форматирование для каждой строки. Кроме многократной установки одних и тех же правил для каждой строки, есть несколько уловок для решения этой задачи.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Избранные и быстро вставляйте формулы , Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF .
- Группировка сводной таблицы по номер недели, день недели и другое . Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя .
Больше информации. Полнофункциональная 30-дневная бесплатная пробная версия . Покупка .
Office Tab — приносит интерфейс с вкладками в Office и значительно упрощает вашу работу
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Выделение ячеек 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, они от них принципиально отличаются, т.к. являются формулами массива .