Найти и заменить в эксель: Поиск или замена текста и чисел на листе

ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

В этой статье описаны синтаксис формулы и использование ЗАМЕНИТЬ и ЗАМЕНИТЬБ  в Microsoft Excel.

Описание

Функция ЗАМЕНИТЬ заменяет указанное число символов текстовой строки другой текстовой строкой.

Функция ЗАМЕНИТЬ заменяет часть текстовой строки, соответствующую заданному числу байтов, другой текстовой строкой.

Важно: 

• Эти функции могут быть доступны не на всех языках.

• org/ListItem»>

  Функция ЗАМЕНИТЬ предназначена для языков с однобайтовой кодировкой, а ЗАМЕНИТЬБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.

• Функция ЗАМЕНИТЬ всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

• Функция ЗАМЕНИТЬБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ЗАМЕНИТЬБ считает каждый символ за один.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)

ЗАМЕНИТЬБ(стар_текст;начальная_позиция;число_байтов;нов_текст)

Аргументы функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ описаны ниже.

• Стар_текст    Обязательный. Текст, в котором требуется заменить некоторые символы.

• Начальная_позиция    Обязательный. Позиция символа в старом тексте, начиная с которого требуется выполнить замену новым текстом.

• Число_знаков    Обязательный. Число символов в старом тексте, которые требуется ЗАМЕНИТЬ новым текстом.

• Число_байтов    Обязательный. Число байтов старого текста, который требуется ЗАМЕНИТЬБ новым текстом.

• Нов_текст    Обязательный. Текст, который заменит символы в старом тексте.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные
абвгдеёжзий
2009
123456
Формула	Описание (результат)	Результат
=ЗАМЕНИТЬ(A2;6;5;»*»)	Заменяет пять знаков последовательности абвгдеёжзий одним знаком *, начиная с шестого знака (е).	абвгд*й
=ЗАМЕНИТЬ(A3;3;2;»10″)	Заменяет последние два знака (09) числа 2009 на 10.	2010
=ЗАМЕНИТЬ(A4;1;3;,»@»)	Заменяет первые три знака последовательности 123456 одним знаком @.	@456

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

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

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

Далее вызываем Главная → Редактирование → Найти и выделить → Найти (кнопка с рисунка выше). Поиск также можно включить с клавиатуры комбинацией клавиш Сtrl+F. Откроется диалоговое окно под названием Найти и заменить.

В единственном поле указывается информация (комбинация символов), которую требуется найти. Если не использовать подстановочные символы или т.н. джокеры (см. ниже), то Excel будет искать строгое совпадение заданных символов. Для вывода результатов поиска предлагается два варианта: выводить все результаты сразу – кнопка Найти все; либо выводить по одному найденному значению – кнопка Найти далее.

После запуска поиска программа Excel быстро-быстро просматривает содержимое листа (или указанного диапазона) на предмет наличия искомой комбинации символов. Если такая комбинация обнаружена, то в случае нажатия кнопки Найти все Excel вываливает все найденные ячейки.

Если в нижней части окна выделить любое значение и затем нажать Ctrl+A, то в диапазоне поиска будут выделены все соответствующие ячейки.

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

Поиск нестрогого соответствия символов

Иногда пользователь не знает точного сочетания искомых символов что существенно затрудняет поиск. Данные также могут содержать различные опечатки, лишние пробелы, сокращения и пр., что еще больше вносит путаницы и делает поиск практически невозможным. А может случиться и обратная ситуация: заданной комбинации соответствует слишком много ячеек и цель поиска снова не достигается (кому нужны 100500+ найденных ячеек?).

Для решения этих проблем очень хорошо подходят джокеры (подстановочные символы), которые сообщают Excel о сомнительных местах. Под джокерами могут скрываться различные символы, и Excel видит лишь их относительное расположение в поисковой фразе. Таких джокеров два: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «?» (один «?» – один неизвестный символ).

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И. И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы 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,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

В диалоговом окне поиска есть еще две галочки Учитывать регистр и Ячейка целиком. По умолчанию Excel игнорирует регистр, но можно сделать так, чтобы «иванов» и «Иванов» отличались. Галочка Ячейка целиком также может оказаться весьма полезной, если ищется ячейка не с указанным фрагментом, а полностью состоящая из искомых символов. К примеру, как найти ячейки, содержащие только 0? Обычный поиск не подойдет, т.к. будут выдаваться и 10, и 100. Зато, если установить галочку Ячейка целиком, то все пойдет, как по маслу.

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

Данные обычно ищутся не просто так, а для каких-то целей. Такой целью часто является замена искомой комбинации (или формата) на другую. Чтобы найти и заменить в выделенном диапазоне Excel одни значения на другие, в окне Найти и заменить необходимо выбрать вкладку Замена. Либо сразу выбрать на ленте команду Главная → Редактирование → Найти и выделить → Заменить.

Еще удобнее применить сочетание горячих клавиш найти и заменить в Excel – Ctrl+H.

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

По аналогии с простым поиском, менять можно и формат.

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

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.

А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Жмем Ok. Все переносы строк заменились пробелами.

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

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

Поделиться в социальных сетях:

Функция ЗАМЕНА

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

В этой статье описаны синтаксис формулы и использование функции ПОДСТАВИТЬ в Microsoft Excel.

Описание

Заменяет старый_текст новым_текстом в текстовой строке. Используйте ПОДСТАВИТЬ, если вы хотите заменить определенный текст в текстовой строке; используйте REPLACE, если вы хотите заменить любой текст, который встречается в определенном месте в текстовой строке.

Синтаксис

ПОДСТАВИТЬ(текст, старый_текст, новый_текст, [номер_экземпляра])

Синтаксис функции ПОДСТАВИТЬ имеет следующие аргументы:

• org/ListItem»>

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

• Старый_текст     Обязательный. Текст, который вы хотите заменить.

• New_text     Обязательный. Текст, которым вы хотите заменить old_text.

• Номер_экземпляра     Необязательно. Указывает, какое вхождение old_text вы хотите заменить новым_текстом. Если вы укажете instance_num, будет заменен только этот экземпляр old_text. В противном случае каждое вхождение old_text в тексте заменяется на new_text.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового рабочего листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете настроить ширину столбцов, чтобы увидеть все данные.

Данные
Данные о продажах	<
1 квартал 2008 г.	<
1 квартал 2011 г.	<
Формула	Описание (Результат)	Результат
=ЗАМЕНИТЬ(A2, «Продажи», «Стоимость»)	Заменяет себестоимость продаж (данные о затратах)	Данные о затратах
=ЗАМЕНИТЬ(A3, «1», «2», 1)	Заменяет первый экземпляр «1» на «2» (2-й квартал 2008 г. )	2 квартал 2008 г.
=ЗАМЕНИТЬ(A4, «1», «2», 3)	Заменяет третий экземпляр «1» на «2» (1 квартал 2012 г.)	1 квартал 2012 г.

См. также

ЗАМЕНИТЬ, ЗАМЕНИТЬB функции

Функция ТРИМ

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

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

Поиск текста

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

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

   Нажмите Ctrl + F .

   Появится вкладка «Найти» диалогового окна «Найти и заменить».

3. Введите текст, который вы хотите найти, в поле Найти .
4. Щелкните Найти далее .

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

5. Нажмите кнопку Find Next еще раз, чтобы перейти к другим вхождениям.

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

6. Нажмите Закройте , когда закончите.

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

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

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

   Нажмите Ctrl + H .

   Появится вкладка «Заменить» диалогового окна «Найти и заменить».

3. Введите текст, который хотите заменить, в Найти что коробка.
4. Введите замещающий текст в поле Заменить на .
5. Нажмите кнопку Найти далее .

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

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

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

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

1. Нажмите кнопку Найти и выбрать .
2. Выбрать Найти или Заменить .
3. Нажмите кнопку Опции .

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

4. Завершите поиск/замену, как обычно.
5. Выберите нужные расширенные параметры поиска и замены.

Дополнительные параметры поиска и замены

Найти опцию	Описание
В пределах	Выберите, следует ли выполнять поиск только на текущем листе или во всей книге.
Поиск	Поиск по строкам (слева направо, затем сверху вниз) или столбцам (сверху вниз, затем слева направо).
Посмотреть в	Укажите, какие типы данных следует искать, например, формулы, значения или комментарии.
Коробка спичек	Поиск точно так же, как текст, введенный в текстовое поле. Добавить комментарий Отменить ответ Ваш адрес email не будет опубликован. Обязательные поля помечены * Комментарий * Имя * Email * Сайт