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

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

Перейти к содержанию

Search for:

Главная » Уроки MS Excel » Работа со списками данных

Автор Антон Андронов На чтение 3 мин Опубликовано 10.01.2015

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

При работе с большим количеством данных в Excel, иногда достаточно трудно отыскать какую-то конкретную информацию.

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

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

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

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

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

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

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

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

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

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

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.gcflearnfree.org/office2013/excel2013/7/full
Автор/переводчик: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

118139 13.04.2017 Скачать пример

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

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

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

где

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылки по теме

• Зачистка текста от лишних пробелов, непечатаемых символов и т.д.
  
• Как подсчитать количество слов в ячейке
• Преобразование чисел-как-текст в полноценные числа

 

Excel 2016: использование поиска и замены

560d890a369ca18833b60c20 563d118dca7fac0d9c7b3f7f

Урок 10.

Использование функции «Найти и заменить»

/en/excel2016/working-with-multiple-worksheets/content/

Введение

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

Дополнительно: загрузите нашу рабочую тетрадь.

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

Чтобы найти содержимое:

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

1. На вкладке Home щелкните команду Найти и выбрать , затем выберите Найти в раскрывающемся меню.
2. Найти и заменить Появится диалоговое окно. Введите содержимое , которое вы хотите найти. В нашем примере мы введем название отдела.
3. Щелкните Найти далее . Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана .
4. Нажмите Найти далее , чтобы найти другие экземпляры, или Найти все , чтобы просмотреть все экземпляры поискового запроса.
5. Когда вы закончите, нажмите Закрыть , чтобы закрыть диалоговое окно «Найти и заменить».

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

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

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

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

1. На вкладке Home щелкните команду Найти и выбрать , затем выберите Заменить в раскрывающемся меню.
2. Появится диалоговое окно Найти и заменить . Введите текст, который вы хотите найти, в поле Find what: .
3. Введите текст, который вы хотите заменить, в поле Заменить на: , затем нажмите Найти далее .
4. Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана .
5. Просмотрите текст, чтобы убедиться, что вы хотите его заменить.
6. Если вы хотите заменить его, выберите один из вариантов заменить . Выбор Заменить заменит отдельные экземпляры , а Заменить все заменит все экземпляры текста во всей книге. В нашем примере мы выберем этот вариант, чтобы сэкономить время.

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

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

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

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

Вызов!

1. Откройте нашу рабочую тетрадь.
2. Щелкните вкладку Challenge в левом нижнем углу рабочей книги.
3. Кристал Льюис вышла замуж и сменила фамилию на Тейлор. Используйте Найти и заменить , чтобы изменить фамилию Кристал с Льюис на Тейлор . Будьте осторожны, только меняют фамилию Кристал!
4. Найдите и замените Bio на Biology . Будьте осторожны не , чтобы изменить основные биомедицинской инженерии!
5. Используйте Найти и заменить все , чтобы заменить Физика на Физика .
6. Когда вы закончите, ваш рабочий лист должен выглядеть так:

Продолжать

Предыдущий: Работа с несколькими листами

Далее:Проверка орфографии

/en/excel2016/checking-spelling/content/

Получите больше от функции «Найти и заменить» в Excel

Посмотрите видео — полезные примеры использования функции «Найти и заменить» в Excel

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

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

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

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

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

#1 Чтобы изменить ссылки на ячейки с помощью Excel «Найти и заменить»

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

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

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

Вот шаги для этого:

1. Выберите ячейки с формулой, в которой вы хотите заменить ссылку. Если вы хотите заменить весь рабочий лист, выберите весь рабочий лист.
2. Перейти на главную -> Найти и выбрать -> Заменить (Сочетание клавиш — Control + H) .
3. В диалоговом окне «Найти и заменить» используйте следующие данные:
   • Найдите что: $A$1 (ссылка на ячейку, которую вы хотите изменить).
   • Заменить на: $B$1 (новая ссылка на ячейку).
4. Нажмите «Заменить все».

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

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

#2 Поиск и замена форматирования в Excel

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

Вот шаги для этого:

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

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

#3 Чтобы добавить или удалить разрыв строки

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

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

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

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

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

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

Вот шаги, чтобы удалить все разрывы строк сразу:

1. Выберите данные, из которых вы хотите удалить разрывы строк.
2. Перейти на главную -> Найти и выбрать -> Заменить (сочетание клавиш — Control + H).
3. В диалоговом окне «Найти и заменить»:
   • Что найти: нажмите Control + J (вы можете не видеть ничего, кроме мигающей точки).
   • Заменить на: Символ пробела (нажмите пробел один раз).
4. Нажмите «Заменить все».

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

#4 Удаление текста с помощью подстановочных знаков

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

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

1. Выберите данные
2. Перейти на главную страницу -> Найти и выбрать -> Заменить (сочетание клавиш — Control + H )
3. В диалоговом окне «Найти и заменить»:
   • Найти что:  (*)
     Примечание.