Макрос найти и заменить в excel

Обновлено: 08.07.2024

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

При записи макроса все действия в записи макроса записываются Visual Basic для приложений (VBA) коде. Эти действия могут включать ввод текста или чисел, выбор ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов или даже импорт данных из внешнего источника, скажем, Microsoft Access. Visual Basic Приложение (VBA) — это подмножество мощного Visual Basic программирования, которое входит в большинство Office приложений. Хотя VBA позволяет автоматизировать процессы как в приложениях, так и между Office, необязательно знать код VBA или программирование на компьютере, если оно делает то, что вам нужно.

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

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки "Разработчик".

Запись макроса

Перед записью макросов полезно знать следующее:

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

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

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

Чтобы записать макрос, следуйте инструкциям ниже.

На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

Нажмите ALT+T+M+R.

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

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции "Отменить" в данном экземпляре Excel.

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

Хотя поле "Описание" является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

Чтобы начать запись макроса, нажмите кнопку ОК.

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

На вкладке Разработчик в группе Код нажмите кнопку Остановить запись .

Нажмите ALT+T+M+R.

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Сведения о параметрах безопасности макросов и их значении.

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

С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

Щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому вы хотите назначить существующий макрос, и выберите пункт Назначить макрос.

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

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

Вы можете назначать макросы формам и элементам ActiveX на листе.

Узнайте, как включать и отключать макросы в файлах Office.

Открытие редактора Visual Basic

Нажмите клавиши ALT+F11.

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.

Запись макроса

Перед записью макросов полезно знать следующее:

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

Перейдите в Excel > параметры. > ленты & панель инструментов.

В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

Чтобы записать макрос, следуйте инструкциям ниже.

На вкладке Разработчик нажмите кнопку Запись макроса.

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Как правило, макрос сохраняется в расположении Эта книга, но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите Личная книга макроса. При выборе личнойкниги макроса Excel создает скрытую личную книгу макроса (PERSONAL.XLSB), если она еще не существует, и сохраняет макрос в этой книге. Книги в этой папке открываются автоматически при Excel, и любой код, сохраненный в личной книге макроса, будет указан в диалоговом окну Макрос, которое объясняется в следующем разделе.

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш, которые еще не назначены другим командам, так как они будут переопределять совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

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

Чтобы начать запись макроса, нажмите кнопку ОК.

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

На вкладке Разработчик щелкните Остановить запись.

Работа с макросами, записанными в Excel

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

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Узнайте, как включать и отключать макросы в Excel для Mac.

Назначение макроса объекту, фигуре или графическому элементу

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

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

Вы можете назначать макросы формам и элементам ActiveX на листе.

Открытие редактора Visual Basic

На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.

Узнайте, как найти справку по элементам Visual Basic.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Найти и заменить на верхний индекс функцией Найти и заменить

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

1. Выберите диапазон, в котором вы хотите найти и заменить работу, и нажмите Ctrl + H для открытия Найти и заменить Диалог.

2. в Найти и заменить диалоговое окно, нажмите Заменять вкладка, а затем введите текст, который хотите найти, в Найти то, что текстовое окно. Смотрите скриншот:

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

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

Наконечник: Но с помощью этого метода вы можете указать только номер 2, нажав 0178, и 3 на 0179.

Найти и заменить надстрочным индексом на VBA

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

1. Нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модуль, и вставьте ниже код VBA в скрипт.

VBA: найти и заменить на надстрочный формат

3. Нажмите F5 нажмите клавишу, чтобы запустить код, и появится диалоговое окно, напоминающее вам о выборе диапазона. Смотрите скриншот:

4. Нажмите OK, конкретный символ в определенной строке был преобразован в формат надстрочного индекса в выделенном фрагменте. Смотрите скриншот:

Чаевые: в коде " YL3 ”- это строка, из которой вы хотите найти, и (1, 1) указывает на преобразование первого символа строки, если вы хотите преобразовать первые два символа, вы можете изменить его как (1, 1), если вы хотите преобразовать 2 символ из второго символа строки, вы можете изменить его как ( 1, 2).

Метод имеет некоторые особенности, которые заключаются в следующем:

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

Синтаксис и параметры метода

Синтаксис

Синтаксис при замене подстроки и присвоении переменной возвращаемого значения типа Boolean:

variable = expression.Replace(What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat])

Синтаксис при замене подстроки без присвоения переменной возвращаемого значения:

expression.Replace What, Replacement, [LookAt], [SearchOrder], [MatchCase], [MatchByte], [SearchFormat], [ReplaceFormat]

variable – переменная (тип данных — Boolean);
expression – выражение, возвращающее объект Range.

Параметры

Параметр	Описание
What	Искомая подстрока или шаблон*, по которому ищется подстрока в диапазоне ячеек. Обязательный параметр.
Replacement	Подстрока, заменяющая искомую подстроку. Обязательный параметр.
LookAt	Указывает правило поиска по полному или частичному вхождению искомой подстроки в текст ячейки: 1 (xlWhole) – поиск полного вхождения искомого текста; 2 (xlPart) – поиск частичного вхождения искомого текста. Необязательный параметр.
SearchOrder	Задает построчный или постолбцовый поиск: 1 (xlByRows) – построчный поиск; 2 (xlByColumns) – постолбцовый поиск. Необязательный параметр.
MatchCase	Поиск с учетом или без учета регистра: 0 (False) – поиск без учета регистра; 1 (True) – поиск с учетом регистра. Необязательный параметр.
MatchByte	Способы сравнения двухбайтовых символов: 0 (False) – двухбайтовые символы сопоставляются с однобайтовыми эквивалентами; 1 (True) – двухбайтовые символы сопоставляются только с двухбайтовым символами. Необязательный параметр.
SearchFormat	Формат поиска. Необязательный параметр.
ReplaceFormat	Формат замены. Необязательный параметр.

* Смотрите знаки подстановки для шаблонов, которые можно использовать в параметре What.

Поиск и замена выставлены объектами Поиска и Замены. Объект Find доступен в объекте Selection и объекте Range. Действие поиска немного отличается в зависимости от того, имеется ли доступ к объекту Find из объекта Selection или объекта Range.

Поиск текста и его выбор

Если объект Find имеет доступ к объекту Selection, выбор меняется при обнаружении критериев поиска. В следующем примере выбирается следующее появление слова "Hello". Если конец документа достигнут до того, как будет найдено слово "Hello", поиск будет остановлен.

Объект Find содержит свойства, которые относятся к параметрам в диалоговом окне Найти и заменить. Можно установить отдельные свойства объекта Find или использовать аргументы с методом Execute, как показано в следующем примере.

Поиск текста без изменения выбора

Если объект Find имеет доступ к объекту Range, выбор не меняется, но диапазон переопределяется при обнаружении критериев поиска. В следующем примере обнаруживается первое появление слова "синий" в активном документе. Если операция поиска будет успешной, диапазон будет переопределяться и к слову "синий" применяется смелый форматирование.

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

Использование объекта Replacement

Объект Replacement представляет критерии замены для операции поиска и замены. Свойства и методы объекта Replacement соответствуют параметрам в диалоговом окне Найти и заменить (Изменить меню).

Объект Replacement доступен в объекте Find. В следующем примере все вхождения слова "привет" заменяется на "hello". Выбор изменяется при обнаружении критериев поиска, так как объект Find имеет доступ к объекту Selection.

В следующем примере удаляется жирный форматирование в активном документе. Свойство Bold — true для объекта Find и False для объекта Replacement. Чтобы найти и заменить форматирование, установите поиск и замените текст пустыми строками ("") и установите аргумент Format метода Execute к True. Выбор остается неизменным, так как объект Find имеет доступ к объекту Range (свойство Content возвращает объект Range).

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Читайте также: