Поиск и замена данных в excel

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Инструмент Найти и заменить в Excel

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

Чтобы воспользоваться диалоговым окном Найти и заменить, выделите диапазон ячеек, в котором вы хотите произвести поиск (если будет выделена только одна ячейка, Excel будет искать во всем листе). Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Найти (или нажмите сочетание клавиш Ctrl + F).

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

Поиск информации

Введите искомую информацию в поле Найти и укажите требуемые параметры поиска:

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

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

Замена информации

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

Щелкните по кнопке Найти далее, чтобы Excel выделил первую совпавшую ячейку, затем Заменить для замены. После щелчка, Excel выделит следующую совпавшую ячейку. Чтобы пропустить замещение, щелкните Найти далее. Для замены всех элементов без проверки, щелкните Заменить все. Если замещение пошло не по плану, вы можете отменить действие с помощью сочетания клавиш Ctrl + Z.

Поиск форматов

Помимо текста вы можете найти ячейки отформатированные определенным образом. Как вариант, вы также можете заменить форматирование ячейки на другое. Предположим, что необходимо определить все ячейки с жирным текстом и заменить их на жирный и курсив. Перейдите по вкладке Главная в группу Редактирование, выберите Найти и выделить -> Заменить (или нажмите сочетание клавиш Ctrl + H). В появившемся диалоговом окне перейдите во вкладку Заменить и щелкните по кнопке Параметры, чтобы отобразить расширенные опции замены. Щелкните по верхней кнопке Формат.

В появившемся диалоговом окне, перейдите во вкладку Шрифт и выберите Полужирное начертание. Щелкните ОК.

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

Щелкните по кнопке Заменить все, Excel заменит текст с жирным начертанием на жирный курсив.

Хитрости использования Найти и заменить Excel

Замена переноса строки (Alt+Enter) в ячейке

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

Для этого вызываем диалоговое окно Найти и заменить сочетанием клавиш Ctrl+H. В поле Найти вводим Ctrl+J (внешне поле останется пустым), в поле Заменить на вводим Пробел (или любой другой символ).

Щелкаем Заменить все и, о чудо, все наши каретки превратились в пробелы!

Вам также могут быть интересны следующие статьи

14 комментариев

Ренат, огромное спасибо за «Ctrl+J » — я так долго искала, как указать знак переноса в «Найти». Не поленилась написать сообщение, чтобы сказать еще раз спасибо ))

Согласен, хороший трюк, сам пользуюсь)

Спасибо огромное. Ctrl+j — это меша круто! !

Как найти и заменить масив данных к примеру из базы в 200 000 вычистить одно

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

Как сократить число в excel

Как найти и заменить массив данных, к примеру из базы в 200 000 вычистить одной операцией 2 000 контактов?

Спасибо большое!
Нигде больше не мог найти описание, что это именно Ctrl+J!

Ctrl+J спас меня.

Добрый день! А не подскажете ли, как найти (и выделить) пустой текст в ячейке?
Объясню, чего хочу. В одном файле формулой обрабатываются штрих-коды, в текстовом формате, так как код больше 13 символов. При копировании в другой файл на месте отсутствующих штрих-кодов вставляется пустой текст «». О не видим и в ячейке никак не отображается. Для выгрузки в 1С нужно такие ячейки очистить.

Всем привет, такие вопросы по Эксель:
1. ФИО в нашей базе записано в одну стороку, как разбить на три отдельных, не потеряв данные.
2. Телефон в столбце, имеет разные форматы, пример: 375 29 123456, 8029-223456, 375-29-123456. Как привести в один правильный порядок: +37529123456.
Спасибо за ответы.

Андрей, нужно выделить столбец с ФИО, нажать вкладку «данные» на ПИ, «текст по столбцам», «фиксированной ширины», несколько раз «далее», готово!!

Здравствуйте,
Я смотрел Ваше руководство по вопросу найти определенное слово в Excel. У меня возникла одна задача и есть к Вам вопрос. А можно это слово которое ищешь по тексту в Excel затем, как нашел все варианты, заменить их на такое же самое слово, но только что бы оно было полужирным курсивом выделено по всей таблице. Я пытался так сделать с помощью найти и заменить, но оно заменяет на жирный формать весь текст, а не заданое слово. Что Вы мне посоветуете? Спасибо Вам зарание

У меня нормально всё получается, специально проделала эту операцию ))

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

д.д. как переставитьместами, что бы не перепечатывать, заранее спасибо!
262-о на О-262 и.тд.
263-о
264-о
265-о
266-о
267-о
268-о
269-о
270-о
271-о
271-о-а
272-о
273-о
274-о
275-о
276-о
277-о
278-о
279-о
280-о
281-о
282-о
283-о
284-о
285-о
286-о
287-о
288-о
289-о
290-о
291-о
291-о-а
292/1-о
292-о
293-о
294-о
295-о
296-о
297-о
297/1-о
298-о
298-а-о
299-о
300-о
301-о
302-о
303-о
304-о
305-о
306-о
307-о
308-о
309-о
310-о
311-о
312-о
313-о
314-о
315-о
316-о
317-о
318-о
319-о
320-о
321-о
322-о
323-о
324-о
326-о
325-о
327-о
329-о
330-о
331-о
332-о
333-о
334-о
335-о
336-о
337-о
338-о
339-о
340-о
342-о
343-о
344-о
345-о
347-о
348-о
349-о

Замена по списку

Данная функция является частью надстройки MulTEx

Описание, установка, удаление и обновление
Полный список команд и функций MulTEx
Часто задаваемые вопросы по MulTEx
Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/Диапазоны —Диапазоны —Замена по списку

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

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

Для этого выгружается отдельный список соответствия товара артикулу:

В данном случае стандартная замена данных(Ctrl+H) больше похожа на испытание. Это сколько раз надо сделать замену, чтобы заменить каждый артикул? Наименований товаров могут быть сотни, а то и тысячи. Можно также прибегнуть к различным хитростям вроде вытягивания последнего слова(чтобы получить артикул), но ведь может быть и так, что артикул расположен где-то внутри ячейки, а не последним словом(например, » Аккумулятор для ноутбуков 25011445, БУ » или еще хуже — несколько разных артикулов в одной ячейке: » Аккумулятор для ноутбуков 25011445, 25011446, 25011447, 25011448 «).
Вот здесь в самый раз пригодится команда Замена по списку. Она на основании списка соответствия может заменить все найденные значения артикулов на наименования за пару минут и без особых усилий. При этом замену можно сделать не только в отдельных ячейках, но и на всем листе, во всей книге или во всех открытых книгах.

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

Как упорядочить таблицу в excel

Список соответствий находится в ячейках( [Замена.xls]Соответствия!A2:B8 )- диапазон из двух столбцов, содержащий значения для поиска и замены. Не должен содержать заголовков, иначе текст заголовков будет воспринят как значения для поиска и замены.

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

Произвести замену:
в этом блоке можно выбрать где конкретно производить замену

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

Производить замену по части ячейки — если установлено, то каждое из искомых значений списка будет просматриваться по части ячейки. Например, если в ячейке текст » Аккумулятор для ноутбуков 25011445 «, то в данном случае артикул является частью другого текста ячейки. И если галочку не установить — то значение артикула » 25011445 » не будет заменено, т.к. к сравнению будет принят весь текст ячейки — » Аккумулятор для ноутбуков 25011445 «.
Для чего это нужно: логично, что в большинстве случаев замену необходимо производить именно по части текста ячейки, но бывают случаи, когда необходимо заменять именно весь текст. Например, если в ячейке содержится только наименование товара(» Аккумулятор для ноутбуков Samsung R590-JS03 «) и его надо заменить на конкретный артикул. Если сделать по части ячейки, то в этом случае может получиться так, что отдельно будет заменена только часть текста » Аккумулятор для ноутбуков «, а остальное уже не попадет под замену. Такая замена будет некорректна с точки зрения требуемого результата.

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

Легко догадаться, что команду можно использовать в любой ситуации как упрощенный словарь-переводчик. В одном столбце английские слова, в другом русские; в одном число — в другом его пропись; в одном код цвета — в другом его наименование; и т.д.

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

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

Корреляционное поле в excel

Поиск и замена в Excel

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

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

Поиск данных в ячейках Excel

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

Если перед использованием команды Найти Вы выделите одну ячейку, то Excel будет выполнять поиск во всем листе. А если диапазон ячеек, то только внутри этого диапазона

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

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

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

Замена содержимого ячейки в Excel

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

На вкладке Главная нажмите команду Найти и выделить, а затем из раскрывающегося списка выберите пункт Заменить.
Появится диалоговое окно Найти и заменить. Введите текст, который Вы ищете в поле Найти.
Введите текст, на который требуется заменить найденный, в поле Заменить на. А затем нажмите Найти далее.
Если значение будет найдено, то содержащая его ячейка будет выделена.
Посмотрите на текст и убедитесь, что Вы согласны заменить его.
Если согласны, тогда выберите одну из опций замены:
- Заменить: исправляет по одному значению зараз.
- Заменить все: исправляет все варианты искомого текста в книге. В нашем примере мы воспользуемся этой опцией для экономии времени.

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

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

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

Поиск или замена текста и чисел на листе

В этом курсе:

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

Поиск

Чтобы найти какой-либо элемент, нажмите клавиши CTRL + Fили перейдите на вкладку главная > редактирование> найти & выберите > найти.

Примечание: В приведенном ниже примере мы настроили кнопку » параметры >> » для отображения всего диалогового окна «найти». По умолчанию отображаются скрытые параметры.

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

Советы: Вы можете использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (

) — в критериях поиска.

Используйте вопросительный знак (?), чтобы найти любой одиночный символ (например, «s», «Кот» и «Set».

Используйте звездочку (*), чтобы найти любое количество символов (например, s * d — «Sad» и «запущено»).

, чтобы найти вопросительные знаки, звездочки или другие знаки, например ан 91

? Поиск слова «Ан 91?».

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

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

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

Вне Для поиска данных на листе или во всей книге выберите лист или Книга.

См Вы можете выбрать вариант поиска по строкам (по умолчанию) или по столбцам.

Поиск: Чтобы найти данные с определенными подробностями, выберите в поле формулы, значения, заметкиили Примечания.

Примечание: Формулы, значения, заметки и Примечания доступны только на вкладке » Поиск «. на вкладке заменить доступны только формулы .

С учетом регистра . Установите этот флажок, если вы хотите искать данные с учетом регистра.

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

Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку Формати выберите необходимые параметры в диалоговом окне Найти формат .

Совет: Чтобы найти ячейки, точно соответствующие определенному формату, можно удалить все условия в поле Найти, а затем выбрать ячейку с нужным форматированием в качестве примера. Щелкните стрелку рядом с кнопкой Формат, выберите пункт Выбрать формат из ячейки, а затем щелкните ячейку с форматированием, которое требуется найти.

Заменить

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

Советы: Вы можете использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (

) — в критериях поиска.

Используйте вопросительный знак (?), чтобы найти любой одиночный символ (например, «s», «Кот» и «Set».

Используйте звездочку (*), чтобы найти любое количество символов (например, s * d — «Sad» и «запущено»).

, чтобы найти вопросительные знаки, звездочки или другие знаки, например ан 91

? Поиск слова «Ан 91?».

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

Нажмите Заменить все или Заменить.

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

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

Вне Для поиска данных на листе или во всей книге выберите лист или Книга.

См Вы можете выбрать вариант поиска по строкам (по умолчанию) или по столбцам.

С учетом регистра . Установите этот флажок, если вы хотите искать данные с учетом регистра.

Существует два различных способа поиска и замены текста или чисел на компьютере Mac. Первый — Использование диалогового окна найти & заменить . Вторая — использовать строку поиска на ленте.

Диалоговое окно «Поиск» & «заменить»

Быстрый и эффективный поиск и замена символов в программе Excel

Введение

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

Иногда пользователям необходимо заменить одинаковые данные по всему документу

Чтобы активировать диалоговое окно, позволяющее находить и менять, например, запятую на точку, предварительно нужно обозначить диапазон ячеек. В выделенном фрагменте будет производиться непосредственный поиск. Юзер должен знать, что в случае выделения одной-единственной ячейки, инструмент программы проверит на совпадение с введённым элементом весь лист. Чтобы воспользоваться особенностями программы Excel и сделать замену (например, поменять запятую на точку), в разделе «Главная» нужно перейти в подкатегорию «Редактирование» и нажать «Найти и выделить». Вызвать диалоговое окно поможет совместное нажатие на клавиши Ctrl и F.

Открытое перед юзером окно позволяет эксплуатировать в работе команды: «Найти» (поиск фрагмента текста) и «Заменить». Перейти в расширенный режим просмотра файла поможет кнопка «Параметры».

Инструменты поиска

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

В первую очередь необходимо указать место, которое можно задать в категории «Искать», в зависимости от указанного диапазона поиск может производиться как на определённом листе, так и во всей книге.

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

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

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

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

Инструменты замены

В программе Excel достаточно просто сделать замену одного символа на другой (поменять запятую на точку), для этого следует открыть раздел замены в категории «Найти и заменить». Посредством поиска в окно ввести запятую (символ или элемент, который впоследствии будет изменён). В разделе «Заменить» ввести точку или букву, имеется ввиду тот текст, который появится на месте изменённого.

Символы поменяются после нажатия на кнопку «Далее». Инструментарий софта покажет пользователю ячейки, в которых необходимо будет произвести замещение. По клику Excel сможет пройти весь документ, поэтапно выделяя каждое совпадение. В случае если в каком-то месте нет нужды менять символы (запятую на точку), нужно пропустить замещение, попросту кликнув на «Далее». Если юзеру не нужно проверять каждое совпадение, можно прибегнуть к помощи кнопки «Заменить всё». Отменить действие поможет совместное нажатие на клавиши Ctrl и Z.

Форматы приложения

Кроме данных, которые могут меняться местами, инструментарий программного продукта, предложенного фирмой Microsoft, позволяет определить в тексте места, которые имеют нестандартное форматирование. Естественно, посредством набора команд можно одно форматирование сделать совершенно другим. К примеру, данные с курсивом следует дополнительно отметить жирным шрифтом. Для этого в категории «Главная»необходимо определить подразделы «Редактирование» и «Найти и выделить», способные произвести необходимую замену.

Заключение

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

голоса

Рейтинг статьи