Найти и заменить в Excel
Поиск и замена данных — одна из наиболее часто используемых операций в Excel. Им пользуются даже новички. На ленте есть большая кнопка.
Команда поиска предназначена для автоматического обнаружения ячеек, содержащих нужную комбинацию символов. Данные можно искать в определенном диапазоне, на всем листе или даже во всей книге. Если активна только одна ячейка, по умолчанию поиск выполняется на всем листе. Если вам нужно найти значение в диапазоне ячеек Excel, вам нужно сначала выбрать этот диапазон.
Затем вызовите Main Editing Find и выберите Find (кнопка на изображении выше). Поиск также можно включить с клавиатуры, используя комбинацию клавиш Ctrl + F. Откроется диалоговое окно с названием «Найти и заменить.
Одно поле содержит информацию (комбинацию символов), которую вы хотите найти. Если вы не используете подстановочные знаки или так называемые подстановочные знаки (см. Ниже), Excel будет искать точное совпадение указанных символов. Есть два варианта просмотра результатов поиска: просмотреть все результаты сразу — кнопка «Найти все»; или выводить найденные значения по одному — кнопка «Найти далее.
После запуска поиска Excel быстро и быстро сканирует содержимое листа (или указанного диапазона) на наличие желаемой комбинации символов. Если такая комбинация обнаружена, если вы нажмете кнопку «Найти все», Excel загрузит все найденные ячейки.
Если вы выберете значение в нижней части окна и затем нажмете Ctrl + A, все совпадающие ячейки будут выбраны в диапазоне поиска.
Если поиск запускается с помощью кнопки «Найти далее», Excel выделит ближайшую ячейку, соответствующую поисковому запросу. Когда вы снова нажимаете «Найти далее» (или Enter на клавиатуре), выбирается следующая ближайшая ячейка (соответствует вашим параметрам поиска) и т.д. После выбора последней ячейки Excel переходит к верхней и начинает заново. Именно здесь большинство пользователей получают знания о восстановлении данных в Excel.
Поиск нестрогого соответствия символов
Иногда пользователь не знает точной комбинации желаемых символов, что сильно усложняет поиск. Данные могут также содержать различные опечатки, лишние пробелы, сокращения и т.д., что вносит дополнительную путаницу и делает поиск практически невозможным. Или может произойти обратная ситуация: слишком много ячеек соответствует заданной комбинации, и цель поиска не достигается снова (кому нужно найти 100500+ ячеек?).
Подстановочные знаки (wildcards) очень помогают в решении этих проблем, они сообщают Excel о сомнительных местах. Различные символы могут быть скрыты под символами подстановки, и Excel видит только их относительное положение в поисковой фразе. Таких подстановочных знаков два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» («?» — неизвестный персонаж).
Итак, если в большой клиентской базе вам нужно найти человека по имени Иванов, поиск может вернуть несколько десятков значений. Это явно не то, что вам нужно. Можно добавить имя в поиск, но его можно вводить разными способами: И. Иванов, И. Иванов, Иван Иванов, И. И. Иванов и т.д. Используя подстановочные знаки, вы можете установить известную последовательность символов независимо от что там посередине. В нашем примере просто введите и * Иванов, и Excel найдет все параметры, перечисленные выше, для записи имени конкретного человека, игнорируя всех П. Иванов, А. Иванов и так далее. Секрет в том, что символ «*» сообщает Excel, что под ним можно скрыть любой символ в любом количестве, но нужно искать, что соответствует «и» + что-то еще + «Иванов». Этот прием значительно увеличивает эффективность поиска, поскольку позволяет оперировать неточными критериями.
Если понять требуемую информацию очень сложно, можно использовать несколько звездочек одновременно. Итак, в списке из 1000 позиций по поисковой фразе они говорят * s * m * start быстро нахожу позицию «Мол-ко д / сн мак. GARNIER Basic care д / сушка / чув. 200мл» (это сокращенное название от «Garaner Makeup Removal Milk Basic Care…»). При этом само собой разумеется, что поиск ни к чему не привел бы по словосочетанию «молоко» или «средство для снятия макияжа». Часто бывает достаточно ввести первые буквы искомых слов (которые, вероятно, присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.
Есть еще один подстановочный знак: «?» Под ним можно спрятать только неизвестный символ. Например, указав критерий поиска 1 × 6, Excel найдет все ячейки, содержащие последовательность 106, 116, 126, 136 и т.д. И если вы укажете 1 × 6, он найдет ячейки, содержащие 1006, 1016, 1106, 1236, 1486 и так далее. Таким образом, шутник «?» накладывает более строгие ограничения на поиск, который учитывает количество пропущенных символов (равное количеству «?»).
Если вы допустили ошибку, вы можете попробовать изменить поисковую фразу, поменяв местами известные символы, сократив их, добавив новые подстановочные знаки и т.д. Однако это далеко не все нюансы исследования. Бывают ситуации, когда вы в упор смотрите на нужную ячейку, но поиск почему-то не находит.
Продвинутый поиск
Мало кто обращается к кнопке «Параметры» в диалоговом окне «Найти и заменить». Но тщетно. В нем спрятано много полезных вещей, помогающих решать задачи поиска. После нажатия на кнопку «Параметры» добавляются дополнительные поля, которые еще больше углубляют и расширяют условия поиска.
Благодаря дополнительным параметрам поиск в Excel может засиять новыми красками в прямом смысле этого слова. Таким образом, вы можете искать не только определенное число или текст, но и формат ячейки (заполненный определенным цветом, с заданными границами и т.д.).
После нажатия кнопки «Формат» появляется диалоговое окно ячейки знакомого формата, только на этот раз мы не создаем, а ищем желаемый формат. Кроме того, вы не можете установить формат вручную, но можете выбрать один из доступных, используя соответствующую команду Выбрать формат из ячейки:
Поэтому можно найти, например, все объединенные ячейки, что очень проблематично сделать другим способом.
Поиск формата — это хорошо, но чаще нужно искать конкретные значения. И здесь Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.
Первый раскрывающийся список «Поиск» предлагает вам ограничить поиск листом или развернуть его до всей книги.
По умолчанию (если вы не вводите параметры) поиск происходит только на активном листе. Чтобы повторить поиск на другом листе, необходимо повторить все действия. А если таких листов много, восстановление данных может занять много времени. Однако, если вы выберете элемент «Книга», он будет искать все листы активной книги одновременно. Преимущества очевидны.
Список «Обзор» с раскрывающимися вариантами по строке или столбцу, похоже, пережил старые версии, когда поиск занимал много ресурсов и времени. Сейчас это не актуально. В общем не пользуюсь.
Следующий раскрывающийся список содержит отличные возможности поиска формул, значений и даже примечаний. По умолчанию Excel ищет формулы или, если их нет, содержимое ячеек. Например, если вы ищете фамилию Иванов, и эта фамилия является результатом формулы (скопированной с соседнего листа), поиск ничего не даст, потому что в ячейке нет списка символов, которые вы ищете. По той же причине невозможно найти число, являющееся результатом функции. Поэтому бывает, что вы смотрите прямо на ячейку, видите нужное значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените этот параметр на значения, и поиск будет выполняться на основе того, что отражено в ячейке, независимо от содержимого. Например, если ячейка содержит результат вычисления 1/6 (как значение, а не как формулу), а формат отражает только 3 десятичных разряда (например, 0,167), поиск «167» символов при выборе параметра Формулы не найдут эту ячейку (фактическое содержимое ячейки — 0,16666…), и если вы выберете значение, поиск будет успешным (найденные символы соответствуют тому, что отображается в ячейке). И последний пункт в этом списке — Заметки. Поиск ведется только по заметкам. Это может очень помочь, т.к заметки часто прячутся.
В диалоговом окне поиска есть еще два флажка для чувствительности к регистру и для всей ячейки. По умолчанию Excel игнорирует верхний регистр, но вы можете сделать «Иванов» и «Иванов» разными. Поле «Вся ячейка» также может быть очень полезным, если вы ищете ячейку не с указанным фрагментом, а полностью состоящую из нужных вам символов. Например, как найти ячейки, содержащие только 0? Обычный поиск не будет работать, так как будут отображаться и 10, и 100. Но если вы поставите галочку в поле Whole Cell, все пойдет как по маслу.
Поиск и замена данных
Данные обычно ищут по какой-то причине, но с какой-то целью. Эта цель часто состоит в том, чтобы заменить желаемую комбинацию (или формат) на другую. Чтобы найти и заменить одни значения в выбранном диапазоне Excel другими, в окне «Найти и заменить» выберите вкладку «Замена». Или сразу же выберите на ленте команду «Редактировать» на главной странице и выберите «Заменить.
еще удобнее использовать комбинацию горячих клавиш Найти и заменить в Excel — Ctrl + H.
Диалоговое окно расширится на одно поле с указанием новых символов, которые будут вставлены вместо найденных.
По аналогии с простым поиском вы также можете изменить формат.
Кнопка «Заменить все» позволяет заменить один символ другим за один раз. После замены Excel появляется информационное окно с количеством сделанных замен. Кнопка «Заменить» позволяет заменять ячейку после каждого щелчка. Если функция «Найти и заменить в Excel» не работает, попробуйте изменить параметры поиска.
Наконец, давайте взглянем на отличный трюк с поиском и заменой. Многие знают, что разрыв строки можно вставить в ячейку с помощью комбинации Alt + Enter.
Как быстро убрать все разрывы строк? Обычно это делается вручную. Однако умное использование поиска и замены сэкономит вам много времени. Вызываем команду найти и заменить с помощью комбинации Ctrl + H. Теперь в строке поиска нажимаем Ctrl + J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.
Щелкните ОК. Все разрывы строк заменены пробелами.
Функция поиска и замены при правильном использовании заменяет рабочее время неопытного пользователя. Я настоятельно рекомендую использовать все вышеперечисленное. Если что-то не ищется в ваших данных или наоборот, дает вам слишком много дополнительных ячеек, попробуйте уточнить поиск, используя подстановочные знаки «*» и «?» или пользовательские параметры поиска. Важно понимать, что если вы ничего не нашли, это не значит, что этого там нет.
Теперь вы знаете, как искать в Excel по столбцу, строке, любому диапазону, листу или даже книге.