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

Содержание

Замена значений (Power Query) — Служба поддержки Майкрософт

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Еще…Меньше

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

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

Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> изменить. Дополнительные сведения см. в этойExcel.
Выберите столбец с текстовым типом данных.
Выберите Главная или Преобразовать>Заменить значение. Появится диалоговое окно Замена значений.
В поле Значение для поиска введите ищите значение.
В поле Заменить на введите заменяемую величину.
В области Дополнительные параметрысделайте одно или несколько из следующих действий: По умолчанию, определенные

текстовые значения заменяются. Чтобы заменить все значение ячейки, выберите Со всем содержимым ячейки.

Специальные символы Чтобы обработать специальные символы, выберите Вставить специальные знаки, а затем выберите один из следующих следующую из списков: Tab, Возвратакаретки ,Лента строки, Возврат каретки и Поток строки и Неразбиение пробела.
Нажмите ОК.

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

Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> изменить. Дополнительные сведения см. в этойExcel.
Выберите столбец с числом, датой или временем или логическим типом данных.
Выберите Главная или Преобразовать> Заменить значение. Появится диалоговое окно Замена значений.
В поле Значение для поиска введите ищите значение.
В поле Заменить на введите заменяемую величину.
org/ListItem»>
Нажмите ОК.

См. также

Справка по Power Query для Excel

Добавление и изменение типов данных

Замена значений и ошибок (docs.com)

Массовая замена текста формулами

22091 11.08.2020 Скачать пример

Предположим, что у вас имеется список, в котором с разной степенью «пряморукости» записаны исходные данные — например, адреса или названия компаний:

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

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

Что же делать? Не заменять же вручную 100500 раз кривой текст на правильный через окошко «Найти и заменить» или нажимая Ctrl+H?

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

К сожалению, при очевидной распространенности подобной задачи, в Microsoft Excel не существует простых встроенных способов для её решения. Для начала, давайте разберёмся, как это делать формулами, без привлечения «тяжелой артиллерии» в виде макросов на VBA или Power Query.

Случай 1. Массовая полная замена

Начнём с относительно простого случая — ситуации, когда нужно заменить старый кривой текст на новый

полностью.

Предположим, что у нас есть две таблицы:

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

Для удобства:

Обе таблицы преобразованы в динамические («умные») с помощью сочетания клавиш Ctrl+T или командой Вставка — Таблица (Insert — Table).
На появившейся вкладке Конструктор (Design) первой таблице присвоено имя Данные, а второй таблице-справочнику — Замены.

Чтобы объяснить логику формулы зайдём чуть издалека.

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

Найти там встречается.

Для этого выделим любую пустую ячейку в свободной части листа и введём туда функцию НАЙТИ (FIND):

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

Хитрость тут в том, что поскольку первым аргументом мы указали не одно, а несколько значений — эта функция будет возвращать в качестве результата тоже не одно значение, а массив из 3 элементов. Если у вас не последняя версия Office 365 с поддержкой динамических массивов, то после ввода этой формулы и нажатия на Enter вы этот массив увидите прямо на листе:

Если же у вас предыдущие версии Excel, то после нажатия на Enter мы увидим только первое значение из массива результатов, т.е. ошибку #ЗНАЧ! (#VALUE!).

Пугаться не стоит 🙂 На самом деле наша формула работает и увидеть весь массив результатов всё равно можно, если выделить введённую функцию в строке формул и нажать клавишу F9(только не забудьте потом нажать Esc, чтобы вернуться обратно к формуле):

Полученный массив результатов означает, что в исходном кривом названии компании (ГК Морозко ОАО) из всех значений в столбце Найти нашлось только второе (Морозко), причём начиная с 4-го по счёту символа.

Теперь добавим к нашей формуле функцию ПРОСМОТР (LOOKUP)

У этой функции три аргумента:

Искомое значение — можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
Просматриваемый_вектор — тот диапазон или массив, где мы ищем искомое значение. Здесь это введённая ранее функция НАЙТИ, возвращающая массив {#ЗНАЧ!:4:#ЗНАЧ!}
Вектор_результатов — диапазон, откуда мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Здесь это правильные названия из столбца Заменить нашей таблицы-справочника.

Главная и неочевидная фишка тут в том, что функция ПРОСМОТР при отсутствии точного совпадения всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например 9999), мы заставим

ПРОСМОТР находить ячейку с ближайшим наименьшим числом (4) в массиве {#ЗНАЧ!:4:#ЗНАЧ!} и выдавать соответствующее ей значение из вектора результатов, т.е. правильное название компании из столбца Заменить.

Второй нюанс заключается в том, что, технически, наша формула является формулой массива, т.к. функция НАЙТИ возвращает в качестве результатов не одно, а массив из трёх значений. Но поскольку функция ПРОСМОТР поддерживает массивы «из коробки», то нам не придётся вводить эту формулу как классическую формулу массива — с помощью сочетания клавиш Ctrl+Shift+Enter. Достаточно будет простого Enter.

Вот и всё. Надеюсь вы ухватили логику.

Осталось перенести готовую формулу первую ячейку B2 столбца Исправлено — и наша задача решена!

Само-собой, с обычными (не умными) таблицами эта формула тоже замечательно работает (только не забудьте про клавишу F4 и закрепление соответствующих ссылок):

Случай 2. Массовая частичная замена

Этот случай чуть похитрее. Снова имеем две «умных» таблицы:

Первая таблица с криво записанными адресами, которые нужно исправить (я назвал её Данные2). Вторая таблица — справочник, по которому нужно произвести частичную замену подстроки внутри адреса (я назвал эту таблицу Замены2).

Принципиальное отличие тут в том, что нужно заменять только фрагмент исходных данных — например, в первом адресе неправильный «С-Петербург» на правильный «Санкт-Петербург», оставив остальную часть адреса (индекс, улицу, дом) в исходном виде.

Готовая формула будет выглядеть так (для удобства восприятия я разделил её на насколько строк с помощью Alt+Enter):

Основную работу здесь выполняет стандартная Excel’евская текстовая функция ПОДСТАВИТЬ (SUBSTITUTE), у которой 3 аргумента:

Исходный текст — первый кривой адрес из столбца Адрес
Что ищем — тут мы используем трюк с функцией ПРОСМОТР (LOOKUP) из предыдущего способа, чтобы вытащить значение из столбца Найти, которое входит как фрагмент в кривой адрес.
На что заменить — аналогичным образом находим соответствующее ему правильное значение из столбца Заменить.

Вводить эту формулу с Ctrl+Shift+Enter здесь тоже не нужно, хотя она и является, по-сути, формулой массива.

И хорошо видно (см. ошибки #Н/Д на предыдущей картинке), что такая формула, при всей её элегантности, обладает и парой недостатков:

Функция ПОДСТАВИТЬ является регистрочувствительной, поэтому «Спб» в предпоследней строке так и не нашлось в таблице замен. Для решения этой проблемы можно либо использовать функцию ЗАМЕНИТЬ (REPLACE), либо предварительно привести обе таблицы к одному регистру.
Если текст изначально правильный или в нём нет ни одного фрагмента на замену (последняя строка), то наша формула выдает ошибку. Этот момент можно нейтрализовать перехватом и заменой ошибок с помощью функции ЕСЛИОШИБКА (IFERROR):
Если в исходном тексте встречается сразу несколько фрагментов из справочника, то наша формула заменяет только последний (в 8-й строке Лиговский «проспект» заменился на «пр-т», а вот «С-Пб» на «Санкт-Петербург» уже нет, т.к. «С-Пб» стоит выше в справочнике). Эту проблему можно решить повторным прогоном нашей же формулой, но уже по столбцу Исправлено:

Не идеально и, местами, громоздко, но гораздо лучше, чем однообразная замена вручную, правда? 🙂

P.S.

В следующей статье разберёмся, как реализовать подобную массовую подстановку с помощью макросов и Power Query.

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

Как работает функция ПОДСТАВИТЬ (SUBSTITUTE) для замены текста
Поиск точного совпадения текста с помощью функции СОВПАД (EXACT)
Поиск и подстановка с учётом регистра (регистрочувствительный ВПР)

Функции REPLACE, REPLACEB — служба поддержки Microsoft

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

В этой статье описаны синтаксис формулы и использование REPLACE и REPLACEB . функция в Microsoft Excel.

Описание

REPLACE заменяет часть текстовой строки в зависимости от заданного количества символов другой текстовой строкой.

REPLACEB заменяет часть текстовой строки в зависимости от указанного вами количества байтов другой текстовой строкой.

Важно:

Эти функции могут быть доступны не на всех языках.
REPLACE предназначен для использования с языками, использующими однобайтовый набор символов (SBCS), тогда как REPLACEB предназначен для использования с языками, использующими двухбайтовый набор символов (DBCS). Настройка языка по умолчанию на вашем компьютере влияет на возвращаемое значение следующим образом:
.

REPLACE всегда считает каждый символ, будь то однобайтовый или двухбайтовый, равным 1, независимо от настройки языка по умолчанию.
org/ListItem»>
REPLACEB считает каждый двухбайтовый символ равным 2, если вы разрешили редактирование языка, поддерживающего DBCS, а затем установили его в качестве языка по умолчанию. В противном случае REPLACEB считает каждый символ равным 1.

Языки, поддерживающие DBCS, включают японский, китайский (упрощенный), китайский (традиционный) и корейский.

Синтаксис

ЗАМЕНИТЬ (старый_текст, начальный_номер, число_символов, новый_текст)

ЗАМЕНИТЬB(старый_текст, начальный_номер, число_байтов, новый_текст)

Синтаксис функций REPLACE и REPLACEB имеет следующие аргументы:

Старый_текст Обязательный. Текст, в котором вы хотите заменить некоторые символы.
org/ListItem»>
Start_num Обязательно. Позиция символа в old_text, которую вы хотите заменить на new_text.
Num_chars Обязательно. Количество символов в old_text, которые вы хотите заменить REPLACE на new_text.
Num_bytes Обязательно. Количество байтов в old_text, которые вы хотите заменить REPLACEB на new_text.
New_text Обязательный. Текст, который заменит символы в old_text.

Пример

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

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

Наиболее эффективное использование поиска и замены в Excel

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

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

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

Как использовать функцию поиска в Excel

Ниже вы найдете обзор возможностей поиска в Excel, а также подробные инструкции по использованию этой функции в Microsoft Excel 365, 2021, 2019, 2016, 2013, 2010 и более ранних версиях. .

Поиск значения в диапазоне, на листе или в книге

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

Для начала выберите диапазон ячеек для поиска. Чтобы выполнить поиск по всему рабочему листу, щелкните любую ячейку на активном листе.
Откройте диалоговое окно Excel «Найти и заменить », нажав сочетание клавиш Ctrl + F. Либо перейдите на вкладку Главная > Редактирование группы и нажмите Найти и выбрать > Найти …
В поле Найти введите искомые символы (текст или число) и нажмите Найти все или Найти следующее .

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

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

Поиск в Excel — дополнительные параметры

Для точной настройки поиска щелкните Параметры в правом углу диалогового окна Excel Найти и заменить , а затем выполните одно из следующих действий:

Для поиска для указанного значения на текущем рабочем листе или всей рабочей книге выберите Sheet или Workbook в Within .
Для поиска в активной ячейке слева направо (построчно) выберите По строкам в Поиск Для поиска сверху вниз (по столбцам) выберите По столбцам.
Для поиска среди определенного типа данных выберите Формулы , Значения или Комментарии в Поиск в .
Для поиска с учетом регистра установите флажок Проверка регистра .
Для поиска ячеек, содержащих только те символы, которые вы ввели в поле Найти , выберите значок Совпадение со всем содержимым ячейки .

Совет. Если вы хотите найти заданное значение в диапазоне, столбце или строке, выберите этот диапазон, столбец (столбцы) или строку (строки) перед открытием Найти и заменить в Excel. Например, чтобы ограничить поиск определенным столбцом, сначала выберите этот столбец, а затем откройте диалоговое окно «Найти и заменить ».

Поиск ячеек с определенным форматом в Excel

Чтобы найти ячейки с определенным форматированием, нажмите сочетание клавиш Ctrl + F, чтобы открыть В диалоговом окне «Найти и заменить » нажмите «Параметры» , затем нажмите кнопку «Формат … » в правом верхнем углу и определите свой выбор в диалоговом окне Excel «Найти формат ».

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

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

Поиск ячеек с формулами в Excel

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

Выберите диапазон ячеек, в которых вы хотите найти формулы, или щелкните любую ячейку на текущем листе, чтобы выполнить поиск по всему рабочему листу.
Щелкните стрелку рядом с Найти и выбрать , а затем щелкните Перейти к специальному . Кроме того, вы можете нажать F5, чтобы открыть диалоговое окно Перейти к , и нажать кнопку Special… в левом нижнем углу.
В диалоговом окне Перейти к специальному выберите Formulas , затем установите флажки, соответствующие результатам формулы, которые вы хотите найти, и нажмите OK:
- Числа — поиск формул, возвращающих числовые значения, включая даты.
- Текст — поиск формул, возвращающих текстовые значения.
- Логика — найти формулы, которые возвращают логические значения ИСТИНА и ЛОЖЬ.
- Ошибки — найдите ячейки с формулами, которые приводят к ошибкам, таким как #Н/Д, #ИМЯ?, #ССЫЛКА!, #ЗНАЧ!, #ДЕЛ/0!, #NULL! и #ЧИСЛО!.

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

Совет. Чтобы быстро найти все ячейки с формулами , независимо от результата формулы, нажмите Найти и выбрать > Формулы .

Как выбрать и выделить все найденные записи на листе

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

Excel отобразит список найденных объектов, и вы щелкните любое вхождение в списке (или просто щелкните в любом месте области результатов, чтобы переместить туда фокус) и нажмите сочетание клавиш Ctrl + A. Это выберет все найденные вхождения как на Найти и заменить диалоговое окно и на листе.

Как только ячейки выбраны, вы можете выделить их, изменив цвет заливки.

Как использовать Заменить в Excel

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

Замена одного значения другим

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

Выберите диапазон ячеек, в которых вы хотите заменить текст или числа. Чтобы заменить символы на всем листе, щелкните любую ячейку на активном листе.
Нажмите сочетание клавиш Ctrl + H, чтобы открыть вкладку Заменить в диалоговом окне Excel Найти и заменить .
Либо перейдите на вкладку Главная > Редактирование и нажмите Найти и выбрать > Заменить …
Если вы только что использовали функцию поиска Excel, просто переключитесь на вкладку Заменить .
В поле Найти введите значение для поиска, а в поле Заменить на введите значение для замены.
Наконец, нажмите либо Заменить , чтобы заменить найденные вхождения по одному, либо Заменить все , чтобы поменять местами все записи одним махом.

Наконечник. Если что-то пошло не так и вы получили результат, отличный от ожидаемого, нажмите кнопку Отменить или нажмите Ctrl + Z, чтобы восстановить исходные значения.

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

Заменить текст или число ничем

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

Как найти или заменить разрыв строки в Excel

Чтобы заменить разрыв строки пробелом или любым другим разделителем, введите символ разрыва строки в Найдите, что подал, нажав Ctrl + J. Этот ярлык является управляющим кодом ASCII для символа 10 (разрыв строки или перевод строки).

После нажатия Ctrl + J поле Find what на первый взгляд будет выглядеть пустым, но при ближайшем рассмотрении вы заметите маленькую мерцающую точку, как на скриншоте ниже. Введите символ замены в поле Заменить на , например пробел и нажмите Заменить все .

Чтобы заменить какой-либо символ на разрыв строки, сделайте наоборот — введите текущий символ в поле Найти что , а разрыв строки (Ctrl+J) в Заменить на .

Как изменить форматирование ячеек на листе

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

Откройте вкладку Заменить диалогового окна Excel «Найти и заменить» и щелкните Параметры
Рядом с Найдите поле , щелкните стрелку кнопки Формат , выберите Выбрать формат из ячейки и щелкните любую ячейку с форматом, который вы хотите изменить.
Рядом с полем Заменить на либо нажмите кнопку Формат … и установите новый формат в диалоговом окне Excel Заменить формат ; или щелкните стрелку кнопки Формат , выберите Выбрать формат из ячейки и щелкните любую ячейку с нужным форматом.
Если вы хотите заменить форматирование всей книги , выберите Рабочая книга в поле Внутри . Если вы хотите заменить форматирование только на активном листе, оставьте выбор по умолчанию ( Лист) .
Наконец, нажмите кнопку Заменить все и проверьте результат.

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

Excel Найти и заменить с помощью подстановочных знаков

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

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

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

Как найти и заменить подстановочные знаки в Excel

Если вам нужно найти фактические звездочки или вопросительные знаки на листе Excel, введите перед ними символ тильды (~). Например, чтобы найти ячейки, содержащие звездочки, введите ~* в поле Найти что . Чтобы найти ячейки, содержащие вопросительные знаки, используйте ~? в качестве критерия поиска.

Вот как вы можете заменить все вопросительные знаки (?) на листе другим значением (в данном примере это число 1):

Как видите, Excel успешно находит и заменяет подстановочные знаки как в тексте, так и в числовых значениях.

Совет. Чтобы найти символы тильды на листе, введите двойную тильду (~~) в поле Найти что .

Ярлыки для поиска и замены в Excel

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

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

Ctrl+F — Excel Ярлык Find , который открывает вкладку Find в Find & Replace
Ctrl+H — Excel Заменить Ярлык, который открывает вкладку Заменить в Найти и заменить
Ctrl+Shift+F4 — найти предыдущее вхождение искомого значения.
Shift+F4 — найти следующее вхождение искомого значения.
Ctrl+J — найти или заменить разрыв строки.

Поиск и замена во всех открытых книгах

Как вы только что видели, функция поиска и замены в Excel предоставляет множество полезных опций. Однако он может выполнять поиск только в одной книге за раз. Чтобы найти и заменить во всех открытых книгах, вы можете использовать надстройку Advanced Find and Replace от Ablebits.

Следующие Расширенный поиск и замена 9Функции 0192 делают поиск в Excel еще более мощным:

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

Чтобы запустить надстройку «Расширенный поиск и замена», щелкните ее значок на ленте Excel, которая находится на Ablebits Utilities 9Вкладка 0192 > Поиск группы . Кроме того, вы можете нажать Ctrl + Alt + F или даже настроить его на открытие знакомым сочетанием клавиш Ctrl + F.

Откроется панель «Расширенный поиск и замена», и вы выполните следующие действия:

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

Кроме того, у вас есть следующие параметры:

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

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

Если вы хотите попробовать расширенный поиск и замену на листах Excel, вы можете загрузить ознакомительную версию ниже.

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.