Найти и заменить в Excel

Обновлено: 21.11.2024

В прошлом месяце один из моих коллег получил набор данных в Excel и ломал голову, чтобы очистить его. Так как я был единственным, кто был в офисе в тот предрассветный час, он спросил меня, могу ли я помочь. Я использовал простую технику, используя функцию «Найти и заменить» в Excel, и все его данные были чистыми и отполированными. Он поблагодарил меня, собрал вещи и ушел из офиса.

Он поблагодарил меня, собрал вещи и вышел из офиса.

Функция «Найти и заменить» в Excel очень эффективна, если вы знаете, как ее лучше всего использовать.

Использование ПОИСК и ЗАМЕНА в Excel (4 примера)

Найти и заменить в Excel можно сэкономить много времени, а это самое главное в наши дни.

В этом блоге я поделюсь четырьмя замечательными советами, которыми поделился с сотнями коллег в своем офисе. Ответ всегда один и тот же: «Хотел бы я знать это раньше. Это могло бы избавить меня от каторжных работ».

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

Вот где Excel «Найти и заменить» пригодится. Он может легко найти ссылку на ячейку во всех формулах на листе (или в выбранных ячейках) и заменить ее другой ссылкой на ячейку.

Например, предположим, что у вас есть огромный набор данных с формулой, в которой $A$1 используется в качестве одной из ссылок на ячейки (как показано ниже). Если вам нужно заменить $A$1 на $B$1, вы можете сделать это с помощью функции «Найти и заменить» в Excel.

Вот как это сделать:

Это мгновенно обновит все формулы с новой ссылкой на ячейку.

Обратите внимание, что это изменит все экземпляры этой ссылки. Например, если в формуле есть ссылка $A$1 два раза, оба экземпляра будут заменены на $B$1.

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

Вот как это сделать:

Выберите ячейки, для которых вы хотите найти и заменить форматирование. Если вы хотите найти и заменить определенный формат на всем листе, выберите весь лист.
Перейти на главную –> Найти и выбрать –> Заменить (сочетание клавиш – Control + H).
Нажмите кнопку «Параметры». Это расширит диалоговое окно и покажет вам дополнительные параметры.
Нажмите кнопку «Найти формат». Появится раскрывающийся список с двумя вариантами — «Формат» и «Выбрать формат из ячейки».
- Вы можете либо вручную указать формат, который хотите найти, нажав кнопку «Формат», либо выбрать формат в ячейке на листе. Чтобы выбрать формат из ячейки, выберите параметр «Выбрать формат из ячейки», а затем щелкните ячейку, из которой вы хотите выбрать формат.
- После того как вы выберете формат в ячейке или укажете его вручную в диалоговом окне форматирования ячеек, вы увидите это в виде предварительного просмотра слева от кнопки форматирования.
Теперь вам нужно указать нужный формат вместо того, который был выбран на предыдущем шаге. Нажмите кнопку «Заменить форматом». Появится раскрывающийся список с двумя вариантами — «Формат» и «Выбрать формат из ячейки».
- Вы можете указать его вручную, нажав кнопку "Формат", или выбрать существующий формат на листе, щелкнув ячейку, в которой он есть.
- После того как вы выберете формат в ячейке или укажете его вручную в диалоговом окне форматирования ячеек, вы увидите это в виде предварительного просмотра слева от кнопки форматирования.
Нажмите кнопку "Заменить все".

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

Что вы делаете, когда вам нужно перейти на новую строку в ячейке Excel.

Вы нажимаете Alt + Enter.

И что вы делаете, когда хотите отменить это?

Вы удаляете его вручную... не так ли?

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

Хорошая новость: вам не нужно делать это вручную. В программе «Найти и заменить» в Excel есть классная хитрость, с помощью которой это можно сделать в мгновение ока.

Вот как удалить все разрывы строк сразу:

И Вух!Это волшебным образом удалит все разрывы строк с вашего листа.

Это сэкономило мне часы. Я получил список, как показано ниже, и мне пришлось удалить текст между скобками.

Если у вас большой набор данных, удаление скобок и текста между ними может занять несколько часов. Но функция «Найти и заменить» в Excel может сделать это менее чем за 10 секунд.

Надеюсь, эти советы будут вам полезны. Если есть какие-то другие хитрости, которые помогли вам сэкономить время, поделитесь ими с нами!!

Вам также могут понравиться следующие советы и руководства по Excel:

Иногда вам нужно найти слово или число где-то в электронной таблице, но вы не знаете, где оно находится. Команды Excel «Найти и заменить» сканируют рабочий лист на наличие меток и значений всего несколькими щелчками мыши.

Найти текст

Функция поиска позволяет очень легко находить определенные слова и значения на листе.

Нажмите кнопку "Найти и выбрать" на вкладке "Главная".
Выберите "Найти".

Нажмите Ctrl + F.

Откроется вкладка "Найти" диалогового окна "Найти и заменить".

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

Нажмите "Найти все", чтобы найти местоположение всех экземпляров одновременно.

Заменить текст

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

Нажмите кнопку "Найти и выбрать" на вкладке "Главная".
Выберите «Заменить».

Нажмите Ctrl + H.

Откроется вкладка "Заменить" диалогового окна "Найти и заменить".

Выбран первый экземпляр.

Заменить все: замена всех вхождений элемента.
Заменить: заменить выбранный элемент.
Найти все: найти все экземпляры элемента.
Найти далее: найти следующий экземпляр элемента.

Расширенный поиск и замена

Вы можете использовать параметры поиска Excel, чтобы изменить способ поиска Excel в книге.

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

Расширенные параметры поиска и замены

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

БЕСПЛАТНЫЙ краткий справочник

Бесплатно для распространения с нашей благодарностью; мы надеемся, что вы рассмотрите наше платное обучение.

Алан Мюррей

Алан Мюррей
Писатель

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

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

Найти и заменить текст и числа в Excel

При работе с большими электронными таблицами часто требуется найти конкретное значение. К счастью, функция «Найти и заменить» упрощает эту задачу.

Выберите столбец или диапазон ячеек, которые вы хотите проанализировать, или щелкните любую ячейку, чтобы выполнить поиск по всему листу. Нажмите Главная > Найти и выделить > Найти или нажмите сочетание клавиш Ctrl+F.

Введите текст или число, которое вы хотите найти, в текстовое поле «Найти».

Нажмите «Найти далее», чтобы найти первое вхождение значения в области поиска; снова нажмите «Найти далее», чтобы найти второе вхождение, и т. д.

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

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

Если вы хотите изменить вхождения значения на что-то другое, нажмите на вкладку «Заменить». Введите текст или число, которое вы хотите использовать в качестве значения замены, в текстовом поле «Заменить на».

Нажмите «Заменить», чтобы изменить каждое вхождение по одному, или нажмите «Заменить все», чтобы изменить все вхождения этого значения в выбранном диапазоне.

Изучите дополнительные параметры

Найти и заменить имеет расширенные функции, о которых многие пользователи не знают. Нажмите кнопку «Параметры», чтобы развернуть окно и просмотреть их.

Одним из действительно полезных параметров является возможность переключаться с просмотра активного рабочего листа на рабочую книгу.

Нажмите стрелку списка "В пределах", чтобы изменить это на Рабочая книга.

К другим полезным параметрам относятся флажки "Учитывать регистр" и "Учитывать все содержимое ячейки".

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

Изменить формат значений

Вы также можете найти и заменить форматирование значений.

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

Нажмите "Главная" > "Найти и выбрать" > "Заменить", чтобы открыть диалоговое окно "Найти и заменить".

Нажмите кнопку "Параметры", чтобы развернуть параметры поиска и замены.

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

Нажмите кнопку "Формат" рядом с текстовыми полями "Найти" и "Заменить на", чтобы установить форматирование.

Укажите форматирование, которое вы хотите найти или заменить.

Предварительный просмотр форматирования отображается в окне "Найти и заменить".

Продолжите с любыми другими параметрами, которые вы хотите установить, а затем нажмите «Заменить все», чтобы изменить все вхождения форматирования.

Использование подстановочных знаков

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

В функции "Найти и заменить" можно использовать два подстановочных знака. Знак вопроса и звездочка. Знак вопроса (?) используется для поиска одного символа. Например, Ал?н найдет «Алан», «Ален» и «Алан».

Звездочка (*) заменяет любое количество символов. Например, y* найдет «да», «да», «да-с-с» и «угу».

В этом примере у нас есть список имен, за которым следует идентификатор в столбце A нашей электронной таблицы. Они имеют следующий формат: Алан Мюррей — 5367.

Мы хотим заменить все вхождения идентификатора ничем, чтобы удалить их. У нас останутся только имена.

Нажмите "Главная" > "Найти и выбрать" > "Заменить", чтобы открыть диалоговое окно "Найти и заменить".

Введите «– *» в текстовое поле «Найти» (до и после дефиса есть пробелы). Оставьте текстовое поле «Заменить на» пустым.

Нажмите «Заменить все», чтобы изменить электронную таблицу.

› Как использовать панель навигации в Microsoft Excel
› Как удалить пробелы в Microsoft Excel
› Как восстановить метки панели задач в Windows 11
› Как установить Google Play Маркет в Windows 11
› Что означает XD и как вы его используете?
› Почему прозрачные чехлы для телефонов желтеют?
›5 шрифтов, которые следует прекратить использовать (и лучшие альтернативы)
› Почему не было Windows 9?

поиск меню

Урок 10. Использование поиска и замены

Введение

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

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

Чтобы найти контент:

В нашем примере мы будем использовать команду «Найти», чтобы найти определенный отдел в этом списке.

Вы также можете получить доступ к команде "Найти", нажав Ctrl+F на клавиатуре.

Нажмите "Параметры", чтобы просмотреть критерии расширенного поиска в диалоговом окне "Найти и заменить".

Чтобы заменить содержимое ячейки:

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

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

Содержимое выбранной ячейки будет заменено.

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

Читайте также: