Макрос найти и заменить в excel
Range.Replace – это метод, который находит по шаблону подстроку в содержимом ячеек указанного диапазона, заменяет ее на другую подстроку и возвращает значение типа Boolean.
Метод имеет некоторые особенности, которые заключаются в следующем:
- при присвоении булева значения, возвращаемого методом 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.
Работа метода в VBA Excel
Исходная таблица для всех примеров:
Пример 1
Примеры записи строк кода с методом Range.Replace и поиском по частичному совпадению подстроки с содержимым ячейки:
Хорошо видно, что один и тот же город или компания тут присутствуют в разношёрстных вариантах, что, очевидно, будет создавать массу проблем при работе с этими таблицами в будущем. И если чуть подумать, то можно найти массу примеров похожих задач из других областей.
А теперь представьте, что к вам такие кривые данные попадают регулярно, т.е. это не единичная история из серии "исправил вручную, забыл", а проблема на регулярной основе и в большом количестве ячеек.
Что же делать? Не заменять же вручную 100500 раз кривой текст на правильный через окошко "Найти и заменить" или нажимая Ctrl + H ?
Первое, что приходит в голову в подобной ситуации - произвести массовую замену по составленному заранее справочнику соответствия неправильных и правильных вариантов - вроде такого:
К сожалению, при очевидной распространенности подобной задачи, в Microsoft Excel не существует простых встроенных способов для её решения. Для начала, давайте разберёмся, как это делать формулами, без привлечения "тяжелой артиллерии" в виде макросов на VBA или Power Query.
Случай 1. Массовая полная замена
Начнём с относительно простого случая - ситуации, когда нужно заменить старый кривой текст на новый полностью.
Предположим, что у нас есть две таблицы:
В первой - исходные разномастные названия компаний. Во второй - справочник соответствия. Если находим в названии компании в первой таблице любое слово из столбца Найти, то нужно полностью заменить это кривое название на правильное - из столбца Заменить второй таблицы-справочника.
- Обе таблицы преобразованы в динамические ("умные") с помощью сочетания клавиш Ctrl + T или командой Вставка - Таблица (Insert - Table) .
- На появившейся вкладке Конструктор (Design) первой таблице присвоено имя Данные , а второй таблице-справочнику - Замены .
Чтобы объяснить логику формулы зайдём чуть издалека.
Взяв в качестве примера первую компанию из ячейки A2 и забыв временно про остальные компании, попробуем определить какой именно вариант из столбца Найти там встречается. Для этого выделим любую пустую ячейку в свободной части листа и введём туда функцию НАЙТИ (FIND) :
Эта функция определяет входит ли заданная подстрока (первый аргумент - все значения из столбца Найти) в исходный текст (первая компания из таблицы данных) и должна вывести на выходе либо порядковый номер символа, начиная с которого текст был найден, либо ошибку если подстрока не обнаружена.
Хитрость тут в том, что поскольку первым аргументом мы указали не одно, а несколько значений - эта функция будет возвращать в качестве результата тоже не одно значение, а массив из 3 элементов. Если у вас не последняя версия Office 365 с поддержкой динамических массивов, то после ввода этой формулы и нажатия на Enter вы этот массив увидите прямо на листе:
Пугаться не стоит :) На самом деле наша формула работает и увидеть весь массив результатов всё равно можно, если выделить введённую функцию в строке формул и нажать клавишу F9 (только не забудьте потом нажать Esc , чтобы вернуться обратно к формуле):
Полученный массив результатов означает, что в исходном кривом названии компании (ГК Морозко ОАО) из всех значений в столбце Найти нашлось только второе (Морозко) , причём начиная с 4-го по счёту символа.
Теперь добавим к нашей формуле функцию ПРОСМОТР (LOOKUP) :
У этой функции три аргумента:
- Искомое значение - можно использовать любое достаточно большое число (главное, чтобы оно превышало длину любого текста в исходных данных)
- Просматриваемый_вектор - тот диапазон или массив, где мы ищем искомое значение. Здесь это введённая ранее функция НАЙТИ, возвращающая массив
- Вектор_результатов - диапазон, откуда мы хотим вернуть значение, если искомое значение найдено в соответствующей ячейке. Здесь это правильные названия из столбца Заменить нашей таблицы-справочника.
Главная и неочевидная фишка тут в том, что функция ПРОСМОТР при отсутствии точного совпадения всегда ищет ближайшее наименьшее (предыдущее) значение. Поэтому, указав в качестве искомого значения любое здоровенное число (например 9999), мы заставим ПРОСМОТР находить ячейку с ближайшим наименьшим числом (4) в массиве и выдавать соответствующее ей значение из вектора результатов, т.е. правильное название компании из столбца Заменить.
Второй нюанс заключается в том, что, технически, наша формула является формулой массива, т.к. функция НАЙТИ возвращает в качестве результатов не одно, а массив из трёх значений. Но поскольку функция ПРОСМОТР поддерживает массивы "из коробки", то нам не придётся вводить эту формулу как классическую формулу массива - с помощью сочетания клавиш Ctrl + Shift + Enter . Достаточно будет простого Enter .
Вот и всё. Надеюсь вы ухватили логику.
Осталось перенести готовую формулу первую ячейку B2 столбца Исправлено - и наша задача решена!
Само-собой, с обычными (не умными) таблицами эта формула тоже замечательно работает (только не забудьте про клавишу F4 и закрепление соответствующих ссылок):
Случай 2. Массовая частичная замена
Этот случай чуть похитрее. Снова имеем две "умных" таблицы:
Принципиальное отличие тут в том, что нужно заменять только фрагмент исходных данных - например, в первом адресе неправильный "С-Петербург" на правильный "Санкт-Петербург" , оставив остальную часть адреса (индекс, улицу, дом) в исходном виде.
Готовая формула будет выглядеть так (для удобства восприятия я разделил её на насколько строк с помощью Alt + Enter ):
Основную работу здесь выполняет стандартная Excel'евская текстовая функция ПОДСТАВИТЬ (SUBSTITUTE) , у которой 3 аргумента:
- Исходный текст - первый кривой адрес из столбца Адрес
- Что ищем - тут мы используем трюк с функцией ПРОСМОТР(LOOKUP) из предыдущего способа, чтобы вытащить значение из столбца Найти, которое входит как фрагмент в кривой адрес.
- На что заменить - аналогичным образом находим соответствующее ему правильное значение из столбца Заменить.
Вводить эту формулу с Ctrl + Shift + Enter здесь тоже не нужно, хотя она и является, по-сути, формулой массива.
-
Функция ПОДСТАВИТЬ является регистрочувствительной, поэтому "Спб" в предпоследней строке так и не нашлось в таблице замен. Для решения этой проблемы можно либо использовать функцию ЗАМЕНИТЬ (REPLACE) , либо предварительно привести обе таблицы к одному регистру.
Не идеально и, местами, громоздко, но гораздо лучше, чем однообразная замена вручную, правда? :)
В следующей статье разберёмся, как реализовать подобную массовую подстановку с помощью макросов и Power Query.
Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.
Способ 1. Создание макросов в редакторе Visual Basic
Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.
- В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
- В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)
:
К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:
-
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
Обычный макрос, введенный в стандартный модуль выглядит примерно так:
Давайте разберем приведенный выше в качестве примера макрос Zamena:
С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на VBA в частности, сложновато будет сообразить какие именно команды и как надо вводить, чтобы макрос автоматически выполнял все действия, которые, например, Вы делаете для создания еженедельного отчета для руководства компании. Поэтому мы переходим ко второму способу создания макросов, а именно.
Способ 2. Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операци, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы:
- Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
- Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
- Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо:
- в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
- в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)
Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:
- Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
- Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
- Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .
Запуск и редактирование макросов
Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов
Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:
Кнопка на панели инструментов в Excel 2003 и старше
Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :
Перетащите ее к себе на панель инструментов и затем щелкните по ней правой кнопкой мыши. В контекстом меню можно назначить кнопке макрос, выбрать другой значок и имя:
Кнопка на панели быстрого доступа в Excel 2007 и новее
Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :
Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:
Кнопка на листе
Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :
Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.
Создание пользовательских функций на VBA
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).
Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:
Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :
После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:
Replace – это функция, которая возвращает строку, полученную в результате замены одной подстроки в исходном строковом выражении другой подстрокой указанное количество раз.
Если замену подстроки необходимо осуществить в диапазоне ячеек, функцию Replace следует применить к значению каждой ячейки заданного диапазона. Проще замену в диапазоне ячеек произвести с помощью метода Range.Replace.
Синтаксис и параметры
Replace(expression, find, replace, [start], [count], [compare])
- expression – исходное строковое выражение, содержащее подстроку, которую необходимо заменить;
- find – искомая подстрока, подлежащая замене;
- replace – подстрока, заменяющая искомую подстроку;
- start – порядковый номер символа исходной строки, с которого необходимо начать поиск, часть строки до этого номера обрезается, по умолчанию равен 1 (необязательный параметр);
- count – количество замен подстроки, по умолчанию выполняется замена всех обнаруженных вхождений (необязательный параметр);
- compare – числовое значение, указывающее вид сравнения (необязательный параметр).
Сокращенный синтаксис функции Replace с необязательными параметрами по умолчанию:
Replace(expression, find, replace)
Параметр compare
Константа Значение Описание vbUseCompareOption -1 используется параметр, заданный оператором Option Compare vbBinaryCompare 0 выполняется двоичное сравнение vbTextCompare 1 применяется текстовое сравнение По умолчанию используется двоичное (бинарное) сравнение. При таком сравнении буквенные символы в нижнем и верхнем регистрах различаются. Если необходимо провести замену подстроки независимо от регистра букв, используйте значение параметра compare – vbTextCompare (1).
Вам может быть легко найти и заменить символ или слово в активном рабочем листе, но как насчет поиска и замены на нескольких листах или во всей книге? Следуя этому руководству, вы найдете уловки.
Найдите и замените на нескольких листах или всей книге с помощью удобного Kutools for Excel
Поиск и замена на нескольких листах или во всей книге с помощью функции поиска и замены
Найти и заменить несколько листов acroos
1. Нажмите Ctrl нажмите клавишу, чтобы выбрать несколько листов, которые вы хотите найти и одновременно заменить. Смотрите скриншот:
Наконечник: Удержание Ctrl клавишу, вы можете выбрать несколько несмежных листов, щелкая каждый лист в Вкладка листа бар; Держа Shift клавишу, вы можете выбрать несколько соседних листов, щелкнув первый лист и последний в Вкладка листа бар.
2. Затем нажмите Ctrl + H , чтобы открыть Найти и заменить диалоговое окно, затем введите текст, который хотите заменить, в Найти то, что текстовое поле, и текст необходимо заменить на в Заменить текстовое окно. Смотрите скриншот:
3. Нажмите Заменить все. И выскакивает dialpg, чтобы сказать вам, сколько замен было сделано. Смотрите скриншот:
4. Нажмите OK > Закрыть закрыть диалоги. Теперь тексты заменены.
Найти и заменить во всей книге.
1. Нажмите Ctrl + H для открытия Найти и заменить диалоговом окне введите текст, который вы хотите заменить в Найти то, что текстовое поле, и текст необходимо заменить на в Заменить текстовое окно. Смотрите скриншот:
2. Нажмите Доступные опции чтобы развернуть диалоговое окно, затем выберите Workbook в В раскрывающийся список. Смотрите скриншот:
3. Нажмите Заменить все кнопка. Затем появится новый диалог, в котором будет показано, сколько замен было произведено.
4. Нажмите OK > Закрыть закрыть дэйлоги. Затем все указанные вами тексты были заменены во всей книге.
Найдите и замените на нескольких листах или всей книге с помощью удобного Kutools for Excel
Работы С Нами Kutools for Excel, вы можете найти и заменить слово на листах и во всей книге на Навигация панель.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Откройте книгу и щелкните Кутулс > Навигация Показать Навигация панель, затем щелкните Найти и заменить вкладку, чтобы перейти к Найти и заменить раздел. Смотрите скриншот:
2. в Найти и заменить раздел:
(1) Выберите Замените вкладку и введите текст, который хотите заменить, в поле Найти то, что текстовое поле, а текст, которым нужно заменить, в Заменить текстовое поле, выберите диапазон шкалы, который вы хотите найти, и замените текст в В пределах раздел, затем нажмите Заменить все чтобы заменить их. Если вы просто хотите найти текст в книгах, щелкните Найти все кнопку, все тексты будут перечислены. Смотрите скриншот:
(2) Если вы хотите найти и заменить на выбранных листах, активном листе, активной книге или выделенном фрагменте, щелкните значок В в раскрывающемся списке выберите нужный масштаб. см. скриншоты:
Наконечник: Если вы не можете узнать В выпадающий список и Книги список, нажмите кнопку "Развернуть", чтобы развернуть расширенный Найти и заменить параметры. См. Снимок экрана ниже:
Работы С Нами Kutools for ExcelАвтора Навигация панели, вы можете переключаться между двумя листами, перечислять все листы, столбцы и имена, добавлять изображения или формулы, которые вы обычно используете, в избранное и т. д. Для получения дополнительных сведений о панели навигации, Пожалуйста, нажмите здесь.
Примечание: По умолчанию ярлыки для включения Kutools ' Найти и заменить диалог Победа + W, но если вы знакомы с ярлыком Ctrl + F, вы можете перейти к настройке, чтобы проверить Нажатие Ctrl + F, чтобы применить поиск и замену Kutools вариант, а затем при нажатии Ctrl + F, это Найти и заменить появляется диалоговое окно.
Читайте также: