Найти и заменить в excel функция: Поиск или замена текста и чисел на листе

Содержание

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

Поиск

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

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

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

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).
- Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».
- Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».
- Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».
Нажмите кнопку «Найти все»или «Найти далее», чтобы выполнить поиск.

Совет: При нажатиикнопки «Найти все» будут перечислены все вхождения условия, которые вы ищете, и щелкнув определенное вхождение в списке, будет выбрана его ячейка. Чтобы отсортировать результаты поиска по запросу «Найти все», щелкните заголовок столбца.
Нажмите кнопку «>>» для дальнейшего определения поискового запроса.
- В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».
- Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните «Формулы», «Значения», «Заметки»или «Примечания».
  
  Примечание: Формулы,значения,заметки и примечания доступны только на вкладке «Найти». На вкладке «Заменить» доступны только формулы.
- Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).
- Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».
Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку «Формат» и сделайте выбор в диалоговом окне «Найти формат».

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

Заменить

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на home >редактирование> Найти & Выберите > Replace.

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

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).
- Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».
- Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».
- Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».

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

Совет: При нажатиикнопки «Заменить все» все вхождения ищемого условия заменяются, а замена обновляется по одному вхождению за раз.
Нажмите кнопку «>>» для дальнейшего определения поискового запроса.
- В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».
- Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните «Формулы», «Значения»,
  «Заметки»или «Примечания».
  
  Примечание: Формулы,значения,заметки и примечания доступны только на вкладке «Найти». На вкладке «Заменить» доступны только формулы.
- Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).
- Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».
Если вы хотите найти текст или числа с определенным форматированием, нажмите кнопку «Формат» и сделайте выбор в диалоговом окне «Найти формат».

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

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

окно «& заменить». Второй вариант — использование панели поиска на ленте.

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

Поиск и параметры

Чтобы найти что-либо, нажмите CTRL+Fили перейдите на home >Find & Select > Find.

В поле «Найти:» введите текст или числа, которые нужно найти.
Нажмите кнопку «Найти далее», чтобы выполнить поиск.
При необходимости можно дополнительно определить поиск.
- В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».
- Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните «Формулы», «Значения», «Заметки»или «Примечания».
  
  Примечание: Формулы,значения,заметки и примечания доступны только на вкладке «Найти». На вкладке «Заменить» доступны только формулы.
- Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).
- Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».

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

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

Чтобы заменить текст или числа, нажмите CTRL+Hили перейдите на home >Find & Select > Replace.

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

Совет: При нажатиикнопки «Заменить все» все вхождения ищемого условия заменяются, а замена обновляется по одному вхождению за раз.
При необходимости можно дополнительно определить поиск.
- В пределах: Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».
- Поиск: Вы можете выбрать поиск по строкам (по умолчанию) или по столбцам.
- Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).
- Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти:».

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

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

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

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).
- Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».
- Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».
- Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».
Нажмите клавишу RETURN.
Чтобы найти следующий экземпляр ищите элемент, щелкните в поле поиска и нажмите кнопку RETURNили в диалоговом окне «Найти» нажмите кнопку «Найти далее».

Совет: Поиск можно отменить, нажав клавишу ESC.
Чтобы задать дополнительные параметры поиска, щелкните увеличительное стекло и выберите «Поиск на листе» или «Поиск в книге». Вы также можете выбрать параметр «Дополнительные параметры», чтобы запустить диалоговое окно «Найти».

Щелкните любую ячейку для поиска на всем листе или выберите определенный диапазон ячеек.
Нажмите +F, чтобы развернуть область поиска, а затем в поле поиска введите текст или число, которое нужно найти.

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).
- Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».
- Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».
- Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».
В поле поиска щелкните значок лупы и выберите команду Заменить.
В поле Заменить на введите замещающие символы.

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

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

Совет: Отмените поиск, просто нажав клавишу ESC.
Чтобы задать дополнительные параметры поиска, щелкните увеличительное стекло и выберите «Поиск на листе» или «Поиск в книге». Вы также можете выбрать параметр «Дополнительные параметры», чтобы запустить диалоговое окно «Замена».

Поиск

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

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

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

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).
- Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».
- Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».
- Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».
Нажмите кнопку «Найти далее»или «Найти все», чтобы выполнить поиск.

Совет: При нажатиикнопки «Найти все» будут перечислены все вхождения условия, которые вы ищете, и щелкнув определенное вхождение в списке, будет выбрана его ячейка. Чтобы отсортировать результаты поиска по запросу «Найти все», щелкните заголовок столбца.
Щелкните > параметры поиска, чтобы при необходимости определить дополнительные параметры поиска.
- В пределах: Чтобы найти данные в пределах определенного выделения, выберите «Выделение». Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».
- Направление: Вы можете выбрать поиск в области «Вниз» (по умолчанию) или «Вверх».
- Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).
- Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти».

Заменить

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

Советы: В условиях поиска можно использовать подделавные знаки: вопросии(?),звездочки(*), тильды(~).
- Используйте знак вопроса (?), чтобы найти любой символ — например, если найти «г?д», то будут «ы» и «множество».
- Звездочка (*) используется для поиска любого количества символов. Например, если в этом примере будут найти «г*д», то будут «грусть» и «начато».
- Воспользуйтесь тильдом (~), за которым следует ?, * или ~, чтобы найти вопросии, звездочки или другие символы тильды, например fy91~? находит «фг91?».
В поле «Заменить на:» введите текст или числа, которые нужно использовать для замены текста поиска.
Нажмите кнопку «Заменить» или «Заменить все».

Совет: При нажатиикнопки «Заменить все» все вхождения ищемого условия заменяются, а замена обновляется по одному вхождению за раз.
Щелкните > параметры поиска, чтобы при необходимости определить дополнительные параметры поиска.
- В пределах: Чтобы найти данные в пределах определенного выделения, выберите «Выделение». Чтобы найти данные на листе или во всей книге, выберите «Лист»или «Книга».
- Направление: Вы можете выбрать поиск в области «Вниз» (по умолчанию) или «Вверх».
- Совмещите case ( проверьте это, если вы хотите найти данные с конфиденциальной конфиденциаляцией).
- Содержимому ячеек соответствует все содержимое: проверьте это, если вы хотите найти ячейки, содержащие только символы, которые вы введите в поле «Найти».

Примеры работы с текстовой функцией ЗАМЕНИТЬ в Excel

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

Как работает функция ЗАМЕНИТЬ в Excel?

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

Функция заменить в Excel и примеры ее использования

Создадим на листе рабочей книги табличного процессора Excel табличку со словами, как показано на рисунке:
Далее на листе рабочей книги подготовим область для размещения нашего результата – полученного слова «молоток», как показано ниже на рисунке. Установим курсор в ячейке А6 и вызовем функцию ЗАМЕНИТЬ:
Заполняем функцию аргументами, которые изображены на рисунке:

Выбор данных параметров поясним так: в качестве старого текста выбрали ячейку А2, в качестве нач_поз установили число 5, так как именно с пятой позиции слова «Молоко» мы символы не берем для нашего итогового слова, число_знаков установили равным 2, так как именно это число не учитывается в новом слове, в качестве нового текста установили функцию ПРАВСИМВ с параметрами ячейки А3 и взятием последних двух символов «ок».

Далее нажимаем на кнопку «ОК» и получаем результат:

Как заменить часть текста в ячейке Excel?

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

Создадим на листе рабочей книги Excel табличку со фамилией и именем, как показано на рисунке:
Далее на этом же листе рабочей книги подготовим область для размещения нашего результата – измененных фамилий и имен. Заполним ячейки двумя типами формул как показано на рисунке:

Скачать примеры с текстовой функцией ЗАМЕНИТЬ в Excel

Обратите внимание! Во второй формуле мы используем оператор «&» для добавления символа «а» к мужской фамилии, чтобы преобразовать ее в женскую. Для решения данной задачи можно было бы использовать функцию =СЦЕПИТЬ(B3;»а») вместо формулы =B3&»а» – результат идентичный. Но сегодня настоятельно рекомендуется отказываться от данной функции так как она имеет свои ограничения и более требовательна к ресурсам в сравнении с простым и удобным оператором амперсанд.

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

Замена символов в Microsoft Excel

Смотрите также которые принимают знаки любой символ). И чем пробелов (при текста (в ячейке Excel не работают «Формулы» заходим в выделенные объекты, нажимаем с формулами и удалить лишние пробелы.кнопку «Заменить», все возможности функции ячеек, среди которых

строке формул при быстрота и удобство.

Способы замены символов в Excel

Для замены содержимого найдены при помощиБывают ситуации, когда в подстановки (ВПР, СЧЁТЕСЛИ, естественно, при попытке условии, что нет С1 — обе формулы. О причинах раздел «Текстовые», выбираем на кнопку «Скрыть не нужно перебирать Читайте об этомто выйдет окно «Найти и заменить» будет производиться поиск выделении ячейки. Кроме Главный минус состоит ячейки жмем на специального встроенного инструмента документе нужно заменить

СУММЕЛИ и т. заменить только звездочку лишних пробелов). Соответственно, «Маши» заменены на и способах исправить функцию «ЗАМЕНИТЬ». все». Чтобы показать

Поиск с заменой

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

Excel заменят ВСЕ формула для расчета «Олю»). это, читайте вДиалоговое окно заполнили все скрытые объекты, таблицы. Теперь можно лишние пробелы в открытое на закладкекак найти и заменить этого достаточно просто путем установки или
вы должны быть«Заменить»Кликаем по кнопке группу символов) на есть но. Некоторые данные в ячейке. будет простой:Если нужно заменить только статье «Не работают так. нажимаем на кнопку поменять эти формулы Excel» тут. «Заменить». Рассмотрим пример
слово, цифры, как выделить нужный диапазон снятия галочек, можно уверены, что введенные.«Найти и выделить» другой. Причин может формулы этот знак Я имею ввидуЕсли предполагается, что в определенное вхождение, то формулы в Excel».В строке «Старый текст» «Показать все».
или ссылки вФункция «Перейти» этих функций. заменить точку за вручную. указать, учитывать ли символы нуждаются вЧтобы продолжить поиск данных,, которая располагается во быть множество, начиная воспринимают как текст обычную замену —
ячейке могут находиться его номер задаетсяЗамена одного текста на указываем ячейку, вЧтобы удалить выделенные формулах, т. д.- можно быстро

Как в Excel заменить запятую в Excel,

Не забываем в поля при поиске регистр замене во всех опять жмем на вкладке
от банальной ошибки, сразу. К ним (Excel 2003: Правка-Заменить; и лишние пробелы, в последнем аргументе другой внутри заданной которой нужно заменить объекты, нажимаем клавишу Не убирая выделение перейти в нужное запятую на точку. как найти ячейки «Найти» и «Заменить букв, искать ли
ячейках. Если в кнопку«Главная» и, заканчивая переделкой относится функция НАЙТИ. Excel 2007-2010: вкладка

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

то формула будет (в ячейке С2 текстовой строки - часть данных, текста. «Delete». ячеек, нажимаем на место таблицы. ВызватьУ нас такая с формулами, примечаниями,

на…» вписать соответствующие

точное соответствие в предыдущих способах была«Найти далее»в блоке настроек шаблона или удалениемВ данной функции Главная-Найти и выделить-Заменить) чуть посложнее, но только вторая «Маша» весьма частая ситуация«Нач_поз» — сДругой способ выделения кнопку «Найти» и окно функции «Перейти» таблица. как выделить сразу

 значения. Когда все яч

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

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

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

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

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

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

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

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

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

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

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

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

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

Найти и заменить в excel формула Excelka.ru

Функция ЗАМЕНИТЬ() в MS EXCEL

Функция ЗАМЕНИТЬ( ) , английский вариант REPLACE(), замещает указанную часть знаков текстовой строки другой строкой текста. «Указанную часть знаков» означает, что нужно указать начальную позицию и длину заменяемой части строки. Функция используется редко, но имеет плюс: позволяет легко вставить в указанную позицию строки новый текст.

Синтаксис функции

ЗАМЕНИТЬ(исходный_текст;нач_поз;число_знаков;новый_текст)

Исходный_текст — текст, в котором требуется заменить некоторые знаки.
Нач_поз — позиция знака в Исходном_тексте, начиная с которой знаки заменяются текстом новый_текст.
Число_знаков — число знаков в Исходном_тексте, которые заменяются текстом новый_текст.
Новый_текст — текст, который заменяет знаки в Исходном_тексте.

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

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

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

Пусть в ячейке А2 введена строка Продажи (январь). Чтобы заменить слово январь, на февраль, запишем формулы:

=ЗАМЕНИТЬ(A2;10;6;»февраль»)
=ПОДСТАВИТЬ(A2; «январь»;»февраль»)

т.е. для функции ЗАМЕНИТЬ() потребовалось вычислить начальную позицию слова январь (10) и его длину (6). Это не удобно, функция ПОДСТАВИТЬ() справляется с задачей гораздо проще.

Кроме того, функция ЗАМЕНИТЬ() заменяет по понятным причинам только одно вхождение строки, функция ПОДСТАВИТЬ() может заменить все вхождения или только первое, только второе и т.д.
Поясним на примере. Пусть в ячейке А2 введена строка Продажи (январь), прибыль (январь). Запишем формулы:
=ЗАМЕНИТЬ(A2;10;6;»февраль»)
=ПОДСТАВИТЬ(A2; «январь»;»февраль»)
получим в первом случае строку Продажи (февраль), прибыль (январь), во втором — Продажи (февраль), прибыль (февраль).
Записав формулу =ПОДСТАВИТЬ(A2; «январь»;»февраль»;2) получим строку Продажи (январь), прибыль (февраль).

Кроме того, функция ПОДСТАВИТЬ() чувствительна к РЕгиСТру. Записав =ПОДСТАВИТЬ(A2; «ЯНВАРЬ»;»февраль») получим строку без изменений Продажи (январь), прибыль (январь), т.к. для функции ПОДСТАВИТЬ() «ЯНВАРЬ» не тоже самое, что «январь».

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

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

Примеры работы с текстовой функцией ЗАМЕНИТЬ в Excel

Как работает функция ЗАМЕНИТЬ в Excel?

Функция заменить в Excel и примеры ее использования

Создадим на листе рабочей книги табличного процессора Excel табличку со словами, как показано на рисунке:
Далее на листе рабочей книги подготовим область для размещения нашего результата – полученного слова «молоток», как показано ниже на рисунке. Установим курсор в ячейке А6 и вызовем функцию ЗАМЕНИТЬ:
Заполняем функцию аргументами, которые изображены на рисунке:

Далее нажимаем на кнопку «ОК» и получаем результат:

Как заменить часть текста в ячейке Excel?

Создадим на листе рабочей книги Excel табличку со фамилией и именем, как показано на рисунке:
Далее на этом же листе рабочей книги подготовим область для размещения нашего результата – измененных фамилий и имен. Заполним ячейки двумя типами формул как показано на рисунке:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функции замены в Экселе: ПОДСТАВИТЬ, ЗАМЕНА

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

Функция ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;[номер вхождения]).

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

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

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

Примеры применения функции ПОДСТАВИТЬ

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

Функция ЗАМЕНИТЬ(Исходный текст; Начальная позиция; Количество знаков; Новый текст)

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

Исходный текст – строка или ссылка, в которой нужно произвести замену
Начальная позиция – порядковый номер первого символа для замены
Количество знаков – количество символов, которые нужно заменить, начиная с начальной позиции
Новый текст – строка, которую нужно вставить вместо исходной

Например, запишем формулу: =ЗАМЕНИТЬ(A1;79;4;«2016»)

Она заменит в тексте ячейки А1 символы №79-82 (4 шт) на строку «2016».

Функция ЗАМЕНИТЬ в Эксель

Комбинируйте функции замены с функциями ДЛСТР, ПОИСК, НАЙТИ и другими, чтобы добиться наилучших результатов. Изучите правила написания формул, использования функций, чтобы правильно применять описанные функции.

В следующем посте мы рассмотрим преобразование числа в текст и наоборот. А пока жду ваших вопросов и комментариев!

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

В этом курсе:

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

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

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

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

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

Вопросительный знак заменяет один любой знак. Например, если ввести г?д, то будут найдены слова «гад», «гид» и «год».

Совет: Для поиска на листе звездочек, вопросительных знаков и знаков тильды (

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

? . Для ввода тильды нажмите клавиши SHIFT +

, где клавиша тильда обычно находится над клавишей TAB.

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

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

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

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

Для поиска данных в строках или столбцах выберите в поле Просматривать вариант по строкам или по столбцам.

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

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

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

Для поиска ячеек, содержащих только символы, введенные в поле Найти, установите флажок Ячейка целиком.

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

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

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

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

Совет: Для поиска на листе звездочек, вопросительных знаков и знаков тильды (

? . Для ввода тильды нажмите клавиши SHIFT +

, где клавиша тильда обычно находится над клавишей TAB.

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

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

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

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

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

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

Для поиска ячеек, содержащих только символы, введенные в поле Найти, установите флажок Ячейка целиком.

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

Для поиска текста или чисел на листе также можно использовать функции поиска и поиска .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Функция заменить в excel Excelka.ru

Функция ЗАМЕНИТЬ() в MS EXCEL

Синтаксис функции

ЗАМЕНИТЬ(исходный_текст;нач_поз;число_знаков;новый_текст)

Функция ЗАМЕНИТЬ() vs ПОДСТАВИТЬ()

Пусть в ячейке А2 введена строка Продажи (январь). Чтобы заменить слово январь, на февраль, запишем формулы:

=ЗАМЕНИТЬ(A2;10;6;»февраль»)
=ПОДСТАВИТЬ(A2; «январь»;»февраль»)

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

Функции замены в Экселе: ПОДСТАВИТЬ, ЗАМЕНА

Функция ПОДСТАВИТЬ(Исходный текст; Что искать; На что заменить;[номер вхождения]).

Примеры применения функции ПОДСТАВИТЬ

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

Функция ЗАМЕНИТЬ(Исходный текст; Начальная позиция; Количество знаков; Новый текст)

Исходный текст – строка или ссылка, в которой нужно произвести замену
Начальная позиция – порядковый номер первого символа для замены
Количество знаков – количество символов, которые нужно заменить, начиная с начальной позиции
Новый текст – строка, которую нужно вставить вместо исходной

Например, запишем формулу: =ЗАМЕНИТЬ(A1;79;4;«2016»)

Она заменит в тексте ячейки А1 символы №79-82 (4 шт) на строку «2016».

Функция ЗАМЕНИТЬ в Эксель

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры работы с текстовой функцией ЗАМЕНИТЬ в Excel

Как работает функция ЗАМЕНИТЬ в Excel?

Функция заменить в Excel и примеры ее использования

Создадим на листе рабочей книги табличного процессора Excel табличку со словами, как показано на рисунке:
Далее на листе рабочей книги подготовим область для размещения нашего результата – полученного слова «молоток», как показано ниже на рисунке. Установим курсор в ячейке А6 и вызовем функцию ЗАМЕНИТЬ:
Заполняем функцию аргументами, которые изображены на рисунке:

Далее нажимаем на кнопку «ОК» и получаем результат:

Как заменить часть текста в ячейке Excel?

Создадим на листе рабочей книги Excel табличку со фамилией и именем, как показано на рисунке:
Далее на этом же листе рабочей книги подготовим область для размещения нашего результата – измененных фамилий и имен. Заполним ячейки двумя типами формул как показано на рисунке:

Использование функций ЗАМЕНИТЬ и ЗАМЕНА Excel

В руководстве объясняются функции ЗАМЕНИТЬ и ЗАМЕНА в Excel с примерами использования. Узнайте, как использовать функцию REPLACE с текстовыми строками, числами и датами, а также как вложить несколько функций REPLACE или SUBSTITUTE в одну формулу.

На прошлой неделе мы обсудили различные способы использования функций НАЙТИ и ПОИСК в ваших таблицах Excel. Сегодня мы более подробно рассмотрим две другие функции для замены текста в ячейке на основе ее местоположения или замены одной текстовой строки другой на основе содержимого.Как вы уже догадались, я говорю о функциях ЗАМЕНИТЬ и ЗАМЕНА в Excel.

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

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

REPLACE (old_text, start_num, num_chars, new_text)

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

Old_text — исходный текст (или ссылка на ячейку с исходным текстом), в котором нужно заменить некоторые символы.
Start_num — позиция первого символа в old_text, который вы хотите заменить.
Num_chars — количество символов, которое нужно заменить.
New_text — текст замены.

Например, чтобы заменить слово « sun » на « son », можно использовать следующую формулу:

= ЗАМЕНИТЬ («солнце», 2, 1, «о»)

И если вы поместите исходное слово в какую-либо ячейку, скажем A2, вы можете указать соответствующую ссылку на ячейку в аргументе old_text:

= ЗАМЕНИТЬ (A2, 2, 1, «о»)

Примечание. Если аргумент start_num или num_chars отрицательный или нечисловой, формула Excel Replace возвращает #VALUE! ошибка.

Использование функции ЗАМЕНИТЬ в Excel с числовыми значениями

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

= ЗАМЕНИТЬ (A2, 7, 4, «2016»)

Обратите внимание, что мы заключаем «2016» в двойные кавычки, как обычно с текстовыми значениями.

Аналогичным образом можно заменить одну или несколько цифр в номере. Например:

= ЗАМЕНИТЬ (A4, 4, 4, "6")

И снова вы должны заключить заменяющее значение в двойные кавычки («6»).

Примечание. Формула REPLACE в Excel всегда возвращает текстовую строку , а не число. На скриншоте выше обратите внимание на выравнивание по левому краю возвращенного текстового значения в B2 и сравните его с выровненным по правому краю исходным числом в A2.И поскольку это текстовое значение, вы не сможете использовать его в других вычислениях, если не преобразуете его обратно в число, например, умножив на 1 или используя любой другой метод, описанный в разделе Как преобразовать текст в число.

Использование функции ЗАМЕНИТЬ в Excel с датами

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

Например, у вас есть дата в A2, скажем, 1 октября-14, и вы хотите изменить « Oct » на « Nov ». Итак, вы пишете формулу REPLACE (A2, 4, 3, «ноя»), которая сообщает Excel, что нужно заменить 3 символа в ячейках A2, начиная с 4 ^{-го символа}… и получили следующий результат:

Почему? Потому что «01-Oct-14» — это только визуальное представление базового серийного номера (41913), который представляет дату. Итак, наша формула Replace изменяет последние 3 цифры в приведенном выше серийном номере на « Nov » и возвращает текстовую строку «419Nov».

Чтобы функция Excel REPLACE правильно работала с датами, вы можете сначала преобразовать даты в текстовые строки с помощью функции ТЕКСТ или любого другого метода, продемонстрированного в разделе Как преобразовать дату в текст в Excel. Кроме того, вы можете встроить функцию ТЕКСТ непосредственно в аргумент old_text функции REPLACE:

= ЗАМЕНИТЬ (ТЕКСТ (A2, «дд-ммм-гг»), 4, 3, «ноя»)

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

= ДАТА ЗНАЧЕНИЕ (ЗАМЕНИТЬ (ТЕКСТ (A2, «дд-ммм-гг»), 4, 3, «ноя»))

Вложенные функции REPLACE для выполнения нескольких замен в ячейке

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

Рассмотрим следующий пример. Предположим, у вас есть список телефонных номеров в столбце A в формате «123456789», и вы хотите, чтобы они больше походили на номера телефонов, добавив дефисы. Другими словами, ваша цель — превратить «123456789» в «123-456-789».

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

= ЗАМЕНИТЬ (A2,4,0, "-")

Результат приведенной выше формулы замены будет следующим:

Хорошо, а теперь нам нужно вставить еще один дефис в позицию 8 ^th. Для этого поместите указанную выше формулу в другую функцию Excel REPLACE.Точнее, вы встраиваете его в аргумент old_text другой функции, чтобы вторая функция REPLACE обрабатывала значение, возвращаемое первой REPLACE, а не значение в ячейке A2:

= ЗАМЕНИТЬ (ЗАМЕНИТЬ (A2,4,0, «-»), 8,0, «-»)

В результате вы получите номера телефонов в желаемом формате:

Аналогичным образом можно использовать вложенные функции REPLACE, чтобы текстовые строки выглядели как даты, добавив косую черту (/), где это необходимо:

= (REPLACE (REPLACE (A2,3,0, "/"), 6,0, "/"))

Кроме того, вы можете преобразовать текстовые строки в реальные даты, заключив приведенную выше формулу REPLACE в функцию DATEVALUE:

= ДАТА ЗНАЧЕНИЕ (ЗАМЕНИТЬ (ЗАМЕНИТЬ (A2,3,0, "/"), 6,0, "/"))

И, естественно, вы не ограничены в количестве функций, которые можно вложить в одну формулу (современные версии Excel 2010, 2013 и 2016 позволяют использовать в формуле до 8192 символов и до 64 вложенных функций).

Например, вы можете использовать 3 вложенные функции REPLACE, чтобы число в A2 отображалось как дата и время:

= REPLACE (REPLACE (REPLACE (REPLACE (A2,3,0, «/»), 6,0, «/»), 9,0, «»), 12,0, «:»)

Замена строки, которая отображается в разных позициях в каждой ячейке

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

Предположим, у вас есть список адресов электронной почты в столбце A. И название одной компании изменилось с «ABC» на, скажем, «BCA». Итак, вам необходимо соответствующим образом обновить адреса электронной почты всех клиентов.

Но проблема в том, что имена клиентов имеют разную длину, и поэтому вы не можете точно указать, где начинается название компании.Другими словами, вы не знаете, какое значение указать в аргументе start_num функции REPLACE в Excel. Чтобы узнать это, используйте функцию Excel НАЙТИ, чтобы определить позицию первого символа в строке «@abc»:

= НАЙТИ ("@ abc", A2)

Затем укажите указанную выше функцию НАЙТИ в аргументе start_num вашей формулы REPLACE:

= ЗАМЕНИТЬ (A2, НАЙТИ ("@ abc", A2), 4, "@bca")

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

Как видно на следующем снимке экрана, в формуле нет проблем с поиском и заменой старого текста новым. Однако, если заменяемая текстовая строка не найдена, формула возвращает # ЗНАЧ! ошибка:

И мы хотим, чтобы формула возвращала исходный адрес электронной почты вместо ошибки. Итак, давайте заключим нашу формулу НАЙТИ И ЗАМЕНИТЬ в функцию ЕСЛИОШИБКА:

= ЕСЛИ ОШИБКА (ЗАМЕНИТЬ (A2, НАЙТИ ("@ abc", A2), 4, "@bca"), A2)

И эта улучшенная формула работает отлично, не так ли?

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

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

Синтаксис функции ПОДСТАВИТЬ в Excel следующий:

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

Первые три аргумента являются обязательными, а последний — необязательным.

Текст — исходный текст, в котором нужно заменить символы. Может быть предоставлено как тестовая строка, ссылка на ячейку или результат другой формулы.
Old_text — символы, которые нужно заменить.
New_text — новый символ (символы) для замены old_text.
Instance_num — вхождение old_text, которое вы хотите заменить. Если опущено, каждое вхождение старого текста будет заменено новым текстом.

Например, все приведенные ниже формулы заменяют «1» на «2» в ячейке A2, но возвращают разные результаты в зависимости от того, какое число вы указали в последнем аргументе:

= ЗАМЕНА (A2, «1», «2», 1) — Заменяет первое вхождение «1» на «2».

= ЗАМЕНА (A2, «1», «2», 2) — заменяет второе вхождение «1» на «2».

= ЗАМЕНА (A2, «1», «2») — заменяет все вхождения «1» на «2».

Примечание. Функция ЗАМЕНИТЬ в Excel чувствительна к регистру . Например, следующая формула заменяет все экземпляры прописной буквы «X» на «Y» в ячейке A2, но не заменяет никакие экземпляры строчной буквы «x».

Заменить разные символы одной формулой (Вложенная ЗАМЕНА)

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

Предположим, у вас есть текстовая строка вроде « PR1, ML1, T1 » в ячейке A2, где «PR» означает «Проект», «ML» означает «Milestone», а «T» означает «Задача». состоит в замене трех кодов на полные имена. Для этого вы можете написать 3 разные формулы ЗАМЕНЫ:

= ЗАМЕНА (A2, «PR», «Проект»)

= ЗАМЕНА (A2, «ML», «Веха»)

= ЗАМЕНА (A2, «T», «Задача»)

А затем вложите их друг в друга:

= ЗАМЕНА (ЗАМЕНА (ПОДСТАВИТЬ (A2, «PR», «Проект»), «ML», «Веха»), «T», «Задача»)

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

Excel ЗАМЕНА по сравнению с Excel ЗАМЕНА

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

SUBSTITUTE заменяет один или несколько экземпляров данного символа или текстовой строки. Итак, если вы знаете текст, который нужно заменить, используйте функцию ЗАМЕНА в Excel.
REPLACE изменяет символы в указанной позиции текстовой строки.Итак, если вы знаете позицию заменяемого символа (ов), используйте функцию ЗАМЕНИТЬ в Excel.
Функция ЗАМЕНА в Excel позволяет добавить необязательный параметр (instance_num), который указывает, какое вхождение old_text должно быть изменено на new_text.

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

Скачать рабочую тетрадь

Примеры формул ЗАМЕНИТЬ и ЗАМЕНА

Вас также может заинтересовать

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

Найдите

Чтобы найти что-то, нажмите Ctrl + F или перейдите на Домашняя страница > Редактирование > Найти и выбрать > Найти .

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

В поле Find what: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле Find what: , а затем выберите из списка последний элемент поиска.
Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.
- Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
- Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».
Щелкните Найти все или Найти следующий , чтобы начать поиск.
Совет: Когда вы щелкаете Найти все , будут перечислены все вхождения критерия, который вы ищете, и щелчок по конкретному вхождению в списке выберет его ячейку.Вы можете отсортировать результаты поиска Find All , щелкнув заголовок столбца.
Щелкните Параметры >> , чтобы при необходимости определить параметры поиска:
- Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .
- Поиск: Можно выбрать поиск По строкам (по умолчанию) или По столбцам .
- Искать в: Для поиска данных с конкретными подробностями в поле щелкните Формулы , Значения , Примечания или Комментарии .
  Примечание: Формулы , Значения , Примечания и Комментарии доступны только на вкладке Найти ; только Формулы доступны на вкладке Заменить .
- Match case — отметьте это, если вы хотите искать данные с учетом регистра.
- Соответствовать всему содержимому ячеек. — Установите этот флажок, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или числа с определенным форматированием, нажмите Формат , а затем сделайте свой выбор в диалоговом окне Найти формат .
Совет: Если вы хотите найти ячейки, которые просто соответствуют определенному формату, вы можете удалить любые критерии в поле Найти , а затем выбрать конкретный формат ячейки в качестве примера. Щелкните стрелку рядом с полем Формат , нажмите Выбрать формат из ячейки , а затем щелкните ячейку с форматированием, которое вы хотите найти.

Заменить

Чтобы заменить текст или числа, нажмите Ctrl + H или перейдите на страницу Home > Редактирование > Найти и выбрать > Заменить .

В поле Find what: введите текст или числа, которые вы хотите найти, или щелкните стрелку в поле Find what: , а затем выберите из списка последний элемент поиска.
Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.
- Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
- Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».

Щелкните Заменить все или Заменить .
Совет: Когда вы нажимаете Заменить все , каждое вхождение критерия, который вы ищете, будет заменено, а Заменить будет обновлять по одному вхождению за раз.
Щелкните Параметры >> , чтобы при необходимости определить параметры поиска:
- Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .
- Поиск: Можно выбрать поиск По строкам (по умолчанию) или По столбцам .
- Искать в: Для поиска данных с конкретными подробностями в поле щелкните Формулы , Значения , Примечания или Комментарии .
  Примечание: Формулы , Значения , Примечания и Комментарии доступны только на вкладке Найти ; только Формулы доступны на вкладке Заменить .
- Match case — отметьте это, если вы хотите искать данные с учетом регистра.
- Соответствовать всему содержимому ячеек. — Установите этот флажок, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или числа с определенным форматированием, нажмите Формат , а затем сделайте свой выбор в диалоговом окне Найти формат .
Совет: Если вы хотите найти ячейки, которые просто соответствуют определенному формату, вы можете удалить любые критерии в поле Найти , а затем выбрать конкретный формат ячейки в качестве примера.Щелкните стрелку рядом с полем Формат , нажмите Выбрать формат из ячейки , а затем щелкните ячейку с форматированием, которое вы хотите найти.

Существует два различных метода поиска или замены текста или чисел на Mac. Первый — использовать диалоговое окно «Найти и заменить ». Второй — использовать строку поиска на ленте.

Чтобы что-то найти, нажмите Ctrl + F или перейдите на Home > Find & Select > Find .

В поле Find what: введите текст или числа, которые вы хотите найти.
Щелкните Найти далее , чтобы начать поиск.
При необходимости вы можете определить дальнейший поиск:
- Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .
- Поиск: Можно выбрать поиск По строкам (по умолчанию) или По столбцам .
- Искать в: Для поиска данных с конкретными подробностями в поле щелкните Формулы , Значения , Примечания или Комментарии .
  Примечание: Формулы , Значения , Примечания и Комментарии доступны только на вкладке Найти ; только Формулы доступны на вкладке Заменить .
- Match case — отметьте это, если вы хотите искать данные с учетом регистра.
- Соответствовать всему содержимому ячеек. — Установите этот флажок, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

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

Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».

Чтобы заменить текст или числа, нажмите Ctrl + H или перейдите на страницу Home > Найдите и выберите > Заменить .

В поле Найдите введите текст или числа, которые хотите найти.
В поле Заменить на введите текст или числа, которые вы хотите использовать для замены текста поиска.
Щелкните Заменить или Заменить все .
Совет: Когда вы нажимаете Заменить все , каждое вхождение критерия, который вы ищете, будет заменено, а Заменить будет обновлять по одному вхождению за раз.
При необходимости вы можете определить дальнейший поиск:
- Внутри: Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .
- Поиск: Можно выбрать поиск По строкам (по умолчанию) или По столбцам .
- Match case — отметьте это, если вы хотите искать данные с учетом регистра.
- Соответствовать всему содержимому ячеек. — Установите этот флажок, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».

Щелкните любую ячейку, чтобы выполнить поиск по всему листу, или выберите определенный диапазон ячеек для поиска.
Пресс + F или щелкните значок, чтобы развернуть панель поиска, затем в поле поиска , введите текст или число, которое хотите найти.
Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.
- Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
- Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».
Нажмите клавишу Возврат .
Чтобы найти следующий экземпляр искомого элемента, щелкните в поле поиска и нажмите Вернуть или в диалоговом окне Найти щелкните Найти следующий .
Совет: Вы можете отменить текущий поиск, нажав ESC .
Чтобы указать дополнительные параметры поиска, щелкните увеличительное стекло, а затем щелкните Поиск на листе или Поиск в книге . Вы также можете выбрать опцию Advanced , которая запустит диалоговое окно Find .

Щелкните любую ячейку, чтобы выполнить поиск по всему листу, или выберите определенный диапазон ячеек для поиска.
Пресс + F, чтобы развернуть панель поиска, затем в поле поиска , введите текст или число, которое хотите найти.
Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.
- Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
- Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».
В поле поиска щелкните увеличительное стекло, а затем щелкните Заменить .
В поле « Заменить на » введите символы замены.
Чтобы заменить символы в поле Найти поле , оставьте поле Заменить на пустым.
Щелкните Найти далее .
Чтобы заменить выделенное вхождение, нажмите Заменить .
Чтобы заменить все вхождения символов на листе без предварительного просмотра, нажмите Заменить все .
Совет: Отмените текущий поиск, просто нажав ESC .
Чтобы указать дополнительные параметры поиска, щелкните увеличительное стекло, а затем щелкните Поиск на листе или Поиск в книге .Вы также можете выбрать опцию Advanced , которая запустит диалоговое окно Replace .

Найдите

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

В поле Find what: введите текст или числа, которые вы хотите найти.
Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.
- Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
- Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».
Щелкните Найти далее или Найти все , чтобы запустить поиск.
Совет: Когда вы щелкаете Найти все , будут перечислены все вхождения критерия, который вы ищете, и щелчок по конкретному вхождению в списке выберет его ячейку.Вы можете отсортировать результаты поиска Find All , щелкнув заголовок столбца.
Щелкните > Параметры поиска , чтобы при необходимости определить параметры поиска:
- Внутри: Для поиска данных в определенном выборе выберите Выбор . Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .
- Направление: Вы можете выбрать для поиска Вниз, (по умолчанию) или Вверх, .
- Match case — отметьте это, если вы хотите искать данные с учетом регистра.
- Соответствовать всему содержимому ячеек. — Установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Заменить

В поле Find what: введите текст или числа, которые вы хотите найти.
Советы: Вы можете использовать подстановочных знаков — вопросительный знак (? ), звездочку ( * ), тильду ( ~ ) — в критериях поиска.
- Используйте вопросительный знак (?), Чтобы найти любой одиночный символ — например, s? T находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов — например, s * d находит «грустный» и «начатый».
- Используйте тильду (~), за которой следует?, * Или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например, fy91 ~? находит «fy91?».
В поле Заменить на: введите текст или числа, которые нужно использовать для замены текста поиска.
Щелкните Заменить или Заменить все .
Совет: Когда вы нажимаете Заменить все , каждое вхождение критерия, который вы ищете, будет заменено, а Заменить будет обновлять по одному вхождению за раз.
Щелкните > Параметры поиска , чтобы при необходимости определить параметры поиска:
- Внутри: Для поиска данных в определенном выборе выберите Выбор . Для поиска данных на листе или во всей книге выберите Лист или Рабочая книга .
- Направление: Вы можете выбрать для поиска Вниз, (по умолчанию) или Вверх, .
- Match case — отметьте это, если вы хотите искать данные с учетом регистра.
- Соответствовать всему содержимому ячеек. — Установите этот флажок, если хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Используйте встроенные функции Excel для поиска данных в таблице или диапазоне ячеек

Сводка

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

Создайте образец рабочего листа

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

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

	A	B	С	D	E
1	Имя	Отдел	Возраст		Найти значение
2	Генри	501	28		Мэри
3	Стандарт	201	19
4	Мэри	101	22
5	Ларри	301	29

Определения терминов

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

Срок	Определение	Пример
Массив таблиц	Вся таблица поиска	A2: C5
Lookup_Value	Значение, которое нужно найти в первом столбце Table_Array.	E2
Lookup_Array -или- Lookup_Vector	Диапазон ячеек, содержащий возможные значения поиска.	A2: A5
Col_Index_Num	Номер столбца в Table_Array, для которого должно быть возвращено соответствующее значение.	3 (третий столбец в Table_Array)
Массив результатов -или- Результат_Вектор	Диапазон, содержащий только одну строку или столбец. Он должен быть того же размера, что и Lookup_Array или Lookup_Vector.	C2: C5
Range_Lookup	Логическое значение (ИСТИНА или ЛОЖЬ).Если ИСТИНА или опущено, возвращается приблизительное совпадение. Если ЛОЖЬ, он будет искать точное совпадение.	ЛОЖЬ
Top_cell	Это ссылка, на основе которой вы хотите основать смещение. Top_Cell должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае СМЕЩЕНИЕ возвращает # ЗНАЧ! значение ошибки.
Offset_Col	Это количество столбцов слева или справа, на которые должна ссылаться верхняя левая ячейка результата. Например, «5» в качестве аргумента Offset_Col указывает, что левая верхняя ячейка в ссылке находится на пять столбцов справа от ссылки. Offset_Col может быть положительным (что означает справа от начальной ссылки) или отрицательным (что означает слева от начальной ссылки).

Функции

ПРОСМОТР ()

Функция ПРОСМОТР находит значение в одной строке или столбце и сопоставляет его со значением в той же позиции в другой строке или столбце.

Ниже приведен пример синтаксиса формулы ПРОСМОТР:

= ПРОСМОТР (Lookup_Value, Lookup_Vector, Result_Vector)

Следующая формула находит возраст Марии в образце рабочего листа:

= ПРОСМОТР (E2, A2: A5, C2: C5)

Формула использует значение «Мэри» в ячейке E2 и находит «Мэри» в векторе поиска (столбец A).Затем формула соответствует значению в той же строке в векторе результатов (столбец C). Поскольку «Мэри» находится в строке 4, ПРОСМОТР возвращает значение из строки 4 в столбце C (22).

ПРИМЕЧАНИЕ: Функция ПРОСМОТР требует, чтобы таблица была отсортирована.

Для получения дополнительных сведений о функции ПРОСМОТР щелкните следующий номер статьи в базе знаний Microsoft:

Как использовать функцию ПРОСМОТР в Excel

ВПР ()

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

Ниже приведен пример синтаксиса формулы VLOOKUP :

= ВПР (Lookup_Value, Table_Array, Col_Index_Num, Range_Lookup)

Следующая формула находит возраст Марии в образце рабочего листа:

= ВПР (E2, A2: C5,3, ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит «Мэри» в крайнем левом столбце (столбец A).Затем формула соответствует значению в той же строке в Column_Index. В этом примере в качестве Column_Index (столбец C) используется «3». Поскольку «Мэри» находится в строке 4, ВПР возвращает значение из строки 4 в столбце C (22).

Для получения дополнительных сведений о функции VLOOKUP щелкните следующий номер статьи в базе знаний Microsoft:

Как использовать VLOOKUP или HLOOKUP, чтобы найти точное совпадение

ИНДЕКС () и ПОИСКПОЗ ()

Вы можете использовать функции ИНДЕКС и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании ПРОСМОТР или ВПР .

Ниже приведен пример синтаксиса, который объединяет INDEX и MATCH для получения тех же результатов, что и LOOKUP и VLOOKUP в предыдущих примерах:

= ИНДЕКС (массив_таблицы, ПОИСКПОЗ (значение_просмотра, массив_просмотров, 0), индекс_столбца)

Следующая формула находит возраст Марии в образце рабочего листа:

= ИНДЕКС (A2: C5, ПОИСКПОЗ (E2, A2: A5,0), 3)

Формула использует значение «Мария» в ячейке E2 и находит «Мэри» в столбце A.Затем она сопоставляет значение в той же строке в столбце C. Поскольку «Мэри» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

ПРИМЕЧАНИЕ. Если ни одна из ячеек в Lookup_Array не соответствует Lookup_Value («Мэри»), эта формула вернет # N / A.
Для получения дополнительных сведений о функции INDEX щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Microsoft:

Как использовать функцию ИНДЕКС для поиска данных в таблице

СМЕЩЕНИЕ () и ПОИСКПОЗ ()

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

Ниже приведен пример синтаксиса, который объединяет OFFSET и MATCH для получения тех же результатов, что и LOOKUP и VLOOKUP :

= СМЕЩЕНИЕ (верхняя_ячейка, ПОИСКПОЗ (Lookup_Value, Lookup_Array, 0), Offset_Col)

Эта формула находит возраст Марии в образце рабочего листа:

= СМЕЩЕНИЕ (A1; ПОИСКПОЗ (E2; A2: A5,0), 2)

Формула использует значение «Мария» в ячейке E2 и находит «Мэри» в столбце A.Затем формула соответствует значению в той же строке, но в двух столбцах справа (столбец C). Поскольку «Мария» находится в столбце A, формула возвращает значение в строке 4 столбца C (22).

Для получения дополнительных сведений о функции OFFSET щелкните следующий номер статьи базы знаний Microsoft:

Как использовать функцию СМЕЩЕНИЕ

Формула Excel: найти и заменить несколько значений

По сути, формула использует функцию ЗАМЕНА для выполнения каждой подстановки с этим основным шаблоном:

«Текст» — это входящее значение, «найти» — это текст, который нужно искать, а «заменить» — это текст, которым нужно заменить.Текст, который нужно найти и заменить, хранится в таблице справа в диапазоне E5: F8, по одной паре на строку. Значения слева находятся в именованном диапазоне «найти», а значения справа — в именованном диапазоне «заменить». Функция ИНДЕКС используется для получения как текста «найти», так и текста «заменить» следующим образом:

  INDEX (find, 1) // первое "найти" значение
ИНДЕКС (replace, 1) // первое "заменить" значение

Итак, чтобы выполнить первую замену (ищите «красный», замените на «розовый») мы используем:

Всего мы выполняем четыре отдельных замены, и каждая последующая ЗАМЕНА начинается с результата предыдущей ЗАМЕНЫ:

  = ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (B5, ИНДЕКС (найти, 1), ИНДЕКС (заменить, 1)), ИНДЕКС (найти, 2), ИНДЕКС (заменить, 2)), ИНДЕКС (найти, 3), ИНДЕКС (заменить, 3)), ИНДЕКС (найти, 4), ИНДЕКС (заменить, 4))

Разрывы строк для удобства чтения

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

  =
ЗАМЕНА(
ЗАМЕНА(
ЗАМЕНА(
ЗАМЕНА(
B5,
ИНДЕКС (найти; 1); ИНДЕКС (заменить; 1)),
ИНДЕКС (найти; 2); ИНДЕКС (заменить; 2)),
ИНДЕКС (найти; 3); ИНДЕКС (заменить; 3)),
ИНДЕКС (найти, 4), ИНДЕКС (заменить, 4))

Строка формул в Excel игнорирует лишние пробелы и разрывы строк, поэтому приведенную выше формулу можно вставить напрямую:

Кстати, есть сочетание клавиш для разворачивания и сворачивания строки формул.

Больше замен

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

Другое применение

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

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

На главную »Basic-Excel» Excel-Найти-и-заменить

Basic Excel Найти

Чтобы найти вхождение определенного значения в таблице Excel:

Щелкните значок Найдите и выберите значок (который находится в группе «Редактирование» на вкладке Home на ленте Excel), затем выберите параметр F ind… (см. Справа вверху).
Обратите внимание, что для этого используется сочетание клавиш Ctrl + F.
Вам будет представлено диалоговое окно Excel «Найти и заменить» с выбранной вкладкой «Найти», как показано ниже:
В диалоговом окне:
- Введите текстовое или числовое значение, которое вы хотите найти, в поле Fi n d what: ;
- Нажмите кнопку F ind Next .

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

Excel Найти все

Если вы хотите найти всех вхождений определенного значения, вы можете нажать кнопку F i nd All в диалоговом окне «Найти и заменить». Это вызывает список всех вхождений вашего поискового значения, как показано справа вверху.

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

Базовый поиск и замена в Excel

Чтобы заменить одно или несколько экземпляров определенного значения в электронной таблице Excel:

Нажмите кнопку Найти и выбрать (которая находится в группе Редактирование вкладки Home ), затем выберите опцию R eplace… (см. Справа вверху).
Обратите внимание, что для этого используется сочетание клавиш Ctrl + H.
Вам будет представлено диалоговое окно Excel «Найти и заменить» с выбранной вкладкой «Заменить», как показано ниже:
В диалоговом окне:

Excel Заменить все

Если вы уверены, что хотите заменить все вхождения «найти текст» на «заменить текст» (без проверки каждого экземпляра по отдельности), просто щелкните Заменить A ll кнопку в диалоговом окне.

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

Команда «Найти» в Excel может обрабатывать следующие подстановочные знаки:

? — соответствует любому одиночному символу
* — соответствует любой последовательности символов

(обратите внимание, что если вы действительно хотите найти символ? Или *, вам нужно ввести символ ~ перед этим символом в поиске).

Например:

Условие поиска «a * e» найдет все ячейки, содержащие букву «a», за которой следует «e», с любой комбинацией символов, попадающей между «a» и «e». .
Условие поиска «a? E» найдет все ячейки, содержащие букву «a», за которой следует любой одиночный символ, а затем буква «e».

Найти в указанном диапазоне ячеек

Если вы хотите, чтобы команда поиска Excel выполняла поиск только в определенном диапазоне ячеек, выберите диапазон, в котором вы хотите выполнить поиск, перед выполнением поиска (или поиска и замены).

Например, в приведенной выше таблице справа выбран диапазон A2 — A6. Следовательно, поиск по текстовой строке «Smith» найдет эту текстовую строку в ячейках A2 и A5, но , а не , найдет текстовую строку «Smith», которая встречается в ячейке A8.

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

Команда «Найти» в Excel может быть уточнена с помощью ряда параметров, которые можно отобразить, нажав кнопку Op t ion >> в диалоговом окне «Найти и заменить».

При нажатии на кнопку Op t ion >> диалоговое окно расширяется, как показано ниже:

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

Каждая из опций обсуждается ниже:

Wit h in: Option

Excel Find Wit h in: Опция позволяет пользователю решить, должна ли команда «Найти» выполнять поиск только в текущем активном листе. , или во всей текущей книге.

S earch: Option

Excel Find S earch: Option позволяет пользователю выбрать порядок, в котором Excel выполняет поиск на листе.

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

L ок в: Опция

L ок в: Опция позволяет пользователю решать, что искать.Возможные варианты:

Формулы — Если ячейка содержит формулу, поиск будет выполняться по тексту, составляющему формулу, а не по результату формулы.
Значения (недоступно на вкладке «Заменить») — Если ячейка содержит формулу, будет выполняться поиск по результату формулы (но не по самой формуле).
Комментарии (недоступно на вкладке «Заменить») — ищутся только комментарии к ячейкам; Любое другое содержимое ячейки игнорируется.

Match c ase Опция

Опция Match c ase позволяет пользователю запрашивать регистр символов в команде Find.

Если для параметра Match c ase установлено значение , а не отмечен (параметр по умолчанию), команда «Найти» в Excel не чувствительна к регистру .
Если Match c ase опция отмечена как , то команда «Найти» в Excel учитывает регистр .

Сопоставить всю ячейку C o ntents Опция

Опция Match Complete Cell C o ntents опция позволяет пользователю запрашивать, должна ли команда «Найти» соответствовать какой-либо части содержимого ячейки или должна соответствовать только в том случае, если «Найти текст» равно всему содержимому ячейки .

Если для параметра Соответствие всей ячейке C o ntents установлено значение , а не отмечен (параметр по умолчанию), команда «Найти» в Excel находит любые экземпляры «найти текст», которые встречаются в любой части ячейки.
Если для параметра Соответствие всей ячейке C o ntents установлен флажок , команда «Найти» в Excel находит только те ячейки, для которых все содержимое ячейки соответствует «тексту поиска».

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

Также в диалоговом окне Excel «Найти и заменить» вы увидите кнопку для m at … . Это позволяет вам указать формат, который вы хотите найти, и (необязательно) заменить формат.

Обратите внимание, что если вы укажете стиль форматирования и как значение «найти текст», команда «Найти» найдет только те ячейки, которые соответствуют стилю форматирования и указанному «найти текст».

Как удалить стиль форматирования в Excel Найти и заменить

Если вы хотите удалить ранее указанный стиль форматирования из диалогового окна «Найти и заменить», щелкните стрелку сбоку от Для м at .. . и выберите опцию Clea r Найдите формат (см. Ниже).

Дополнительные сведения о команде Excel «Найти и заменить» можно найти на веб-сайте поддержки Microsoft Office.

Вернуться к Basic Excel Страница
Вернуться к ExcelFunctions.net Домашняя страница

Команды поиска и замены Excel

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

Найти все и удалить строки

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

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

Выберите столбец B, чтобы команда «Найти» была ограничена этим столбцом. (ПРИМЕЧАНИЕ: вы можете выбрать столбцы B и C, чтобы найти бумагу в любом из этих столбцов. Или выделите одну ячейку на листе, чтобы найти бумага в любом месте листа.)
Чтобы открыть диалоговое окно «Найти», нажмите Ctrl + F (или щелкните значок ленты На вкладке «Главная» нажмите «Найти и выбрать», а затем нажмите «Найти»)
В поле Найти введите слово бумага — нас это не касается о верхнем или нижнем регистре, поэтому слово можно набирать во всех нижних чехол
Нажмите кнопку «Найти все», и ниже появится список ячеек. кнопку «Найти все» с выбранным первым элементом.Эта ячейка выбрано на листе

Чтобы выбрать все элементы в списке, нажмите Ctrl + A — все перечисленные ячейки также будут выбраны на листе

Закройте диалоговое окно поиска
Не снимая выделения с ячеек, щелкните вкладку Главная на ленте, и щелкните стрелку для команд Удалить
Щелкните Удалить строки листа

Все строки для выбранных ячеек удаляются без подтверждения. сообщение.
ПРИМЕЧАНИЕ: вы можете отменить действие сразу после удаления строк, если вы передумали удалять их.

Видео: поиск всех и удаление строк

В этом видео вы увидите, как выбрать несколько строк, содержащих похожие данные, и удалите их все одновременно, Команды «Все» и «Удалить строки листа».

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

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

Загрузить образец файла

Скачать образец Найти и заменить книгу

Учебники по функциям Excel

Функция SUM

Функция ПОИСКА

ВПР

ИНДЕКС / МАТЧ

Функция СЧЕТ

КОСВЕННАЯ

Не пропустите наши советы по Excel

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