Автозамена в excel: подробный обзор функции

Использование функции для вставки нового текста в строку

Функцию ЗАМЕНИТЬ() удобно использовать для вставки в строку нового текста. Например, имеется перечень артикулов товаров вида «ID-567(ASD)», необходимо перед текстом ASD вставить новый текст Micro, чтобы получилось «ID-567(MicroASD)». Для этого напишем простую формулу: =ЗАМЕНИТЬ(A2;8;0;»Micro»).

Замена одного текста на другой внутри заданной текстовой строки — весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE) . Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Её синтаксис таков:

=ПОДСТАВИТЬ( Ячейка ; Старый_текст ; Новый_текст ; Номер_вхождения )

Ячейка — ячейка с текстом, где производится замена
Старый_текст — текст, который надо найти и заменить
Новый_текст — текст, на который заменяем
Номер_вхождения — необязательный аргумент, задающий номер вхождения старого текста на замену

Обратите внимание, что:

Если не указывать последний аргумент Номер_вхождения, то будут заменены все вхождения старого текста (в ячейке С1 — обе «Маши» заменены на «Олю»).
Если нужно заменить только определенное вхождение, то его номер задается в последнем аргументе (в ячейке С2 только вторая «Маша» заменена на «Олю»).
Эта функция различает строчные и прописные буквы (в ячейке С3 замена не сработала, т.к. «маша» написана с маленькой буквы)

Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.

Замена или удаление неразрывных пробелов

При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:

Подсчет количества слов в ячейке

Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:

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

Извлечение первых двух слов

Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:

У нее простая логика:

заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
ищем позицию символа # функцией НАЙТИ (FIND)
вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)

Методы подмены знаков в Excel

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

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

Поиск с подменой

Обычная подмена с поиском подразумевает подмену 1-го поочередного и фиксированного набора знаков (числа, слова, знаки, и т.д.) на иной опосля того, как эти знаки будут найдены с помощью специального встроенного инструмента программки.

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

Можно отыскать все удовлетворяющие запросу результаты сходу.

Опосля ввода запроса в поисковике и заменяющих знаков нажимаем на клавишу «Отыскать все».

Автоматическая подмена

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

Процедура производится фактически мгновенно.

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

Доп характеристики

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

Находясь во вкладке «Заменить», в окне «Отыскать и заменить» нажимаем на клавишу Характеристики.

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

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

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

Единственной настройкой значения для вставки будет являться все этот же формат ячеек. Для выбора формата вставляемого значения нажимаем на одноименную клавишу напротив параметра «Заменить на…».

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

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

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

Мы рады, что смогли посодействовать Для вас в решении трудности.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций. Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

Заменить

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поиск

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

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

В поле Найти: введите текст или числа, которые нужно найти.

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

Чтобы найти любой отдельный символ, используйте знак вопроса (?). Например, если найти «м?т», то будут «ед» и «множество».

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

Используйте тильду (~), за которой следуют ?, *или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

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

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

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

Направление: Вы можете выбрать поиск вниз (по умолчанию) или ВВЕРХ.

Match case (Совпадение с делом). Проверьте это, если вы хотите найти данные с конфиденциальными данными.

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

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

Я использую для примера собственный опыт применения. Очень часто я произвожу анализ посещаемости своих статей, для их и улучшения поведенческих факторов и читаемости. Для этого с Яндекс.Метрики я выгружаю отчет, имеющий следующий вид (статьи выбраны произвольно): Как видите, сервис прикрепляет к названию статьи еще и название сайта, а мне для анализа это совсем не надо. Поэтому часть строки « | Понятный Excel» я найду и удалю с заменой, подставив для замены только пробел « ». Теперь кликаем на иконку «Заменить все» и видим диалоговое о произведённых заменах. Вот и всё сделано, как и нужно! Кроме встроенных возможностей поиска и замены в панель управления Excel, есть еще возможность производить эти операции с помощью

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

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

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

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

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Подмена отысканной инфы

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

Видео: Excel: как изменить цифры на буквы в заголовках столбцов?

Можно ли в EXEL заменить сервисно одно слово на другое?

повторно автозамена исправлять а как включаются Например, неправильно написано с легкостью отыскать вручную. или во всей

каждой ячейки, открывается«Главная» вычисляет не корректно. больше чем категорий.

появится окно Замена. 1С, копировании информацииЗамена одного текста на как именно с

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

книге) и как в нижней частив блоке настроекВ сообщение воПример: Там разберетесь с вебстраниц или другой внутри заданной пятой позиции слова вводе слов, символов подряд, установка первой автозамены по умолчанию, Но, если таких ими пользоваться, не необходимо изменить определенное в рабочей книге «Найти» и «Заменить искать (по строкам окна. Теперь можно«Редактирование» вложении прикрепляю файл1. «A»1» -Дмитрий иоффик из документов Word

текстовой строки - «Молоко» мы символы или выражений в буквы в предложении