Если постоянно приходится выполнять одно и то же действие или появляется потребность автоматизировать некоторые движения, то в Excel есть специальная функция – макросы. Это маленькие (или не очень) скрипты, позволяющие выполнять ряд действий за пользователя. Если правильно использовать макросы, то можно выполнять почти любое действие, даже то, которое не предусмотрено стандартным функционалом.
Самые простые макросы могут быть записаны с помощью специальной кнопки. Достаточно ее нажать и выполнить определенные действия. Но для более продвинутого уровня все же придется изучать язык программирования. Но на самом деле, это не так сложно. Главное – иметь в голове хорошую идею, как можно макрос применить.
Программирование макросов осуществляется с помощью языка VBA. И в нем есть одна из часто используемых функций – Replace.
Определение функции Replace, описание
С помощью функции Replace можно заменить всю строку или отдельную подстроку, используя в качестве ориентира определенный шаблон. Как результат, эта функция возвращает получившуюся строку. Причем замена может осуществляться определенное количество раз, и функция автоматически вносит необходимые коррективы.
С помощью этой функции можно осуществить автоматически сразу несколько действий:
- Найти определенную строку и подстроку, замена которой требуется.
- Заменить текст на тот, который был указан пользователем или был возвращен формулой.
- Найти следующий аналогичный фрагмент. И снова его заменить. И так такое количество раз, сколько нужно. Если конкретно, это количество записывается в параметре Count, который записывается далее.
Если необходимо осуществить замену внутри определенного диапазона, то нужно использовать функцию Replace применительно к каждой ячейке. Но если использовать объект Range, то можно значительно упростить процесс.
Если выражаться более просто, эта функция является заменой окна «Найти и заменить». Она позволяет автоматизировать этот процесс, например, основываясь на результатах вычислений или действиях, совершенных ранее. Например, можно запрограммировать макрос на то, чтобы он автоматически подгружал оценки учеников, и если новая оценка выше предыдущей, то вместо текста «учится плохо» писать строку «учится лучше». Таким образом, можно значительно превысить те возможности. которые используются в стандартной функции «Условное форматирование», поскольку такое сравнение реализовать стандартными средствами Эксель не так удобно, как макросами (хотя если заморочиться, то можно). Но есть и более сложные задачи, реализовать которые можно исключительно с помощью макросов.
Функцию «Найти и заменить» знают почти все пользователи Эксель. Она позволяет осуществить замену искомой строки один и более раз. Перед тем, как мы начнем рассматривать, как автоматизировать процесс, необходимо сперва разобраться в стандартном алгоритме действий пользователя, если он хочет заменить один текст на другой. Все потому, что нельзя рассматривать более продвинутые инструменты, не разобравшись толком в простых.
Итак, чтобы заменить определенный текст на какую-то строку без макроса, необходимо на вкладке «Главная» найти пункт «Редактирование». Там в группе «Редактирование» нужно найти такую кнопку. После этого откроется небольшое меню, в котором нужно будет выбрать пункт «Заменить».
Можно же значительно упростить себе жизнь, один раз запомнив комбинацию клавиш Ctrl + H.
После этого появится такое окно.
В нем мы видим две строки. В первой нужно указывать ту строку, которую программа должна найти. Во второй записывается тот текст, на который должна описанная выше строка замениться первая строка. Окно предусматривает также возможность настроить формат, если нажать на кнопку «Параметры».
Если необходимо осуществить замену во всех аналогичных местах, то это можно сделать с помощью кнопки «Заменить все». Если же нажимать просто кнопку «Заменить», то можно выбирать, какой фрагмент заменять, а какой – нет. Чтобы пропустить какое-то вхождение, нужно нажать кнопку «Найти далее», и делать это до тех пор, пока не будет найден тот вариант, который требует замены. После этого пользователь нажимает кнопку «Заменить».
Приятный бонус: с помощью комбинации клавиш Ctrl + Enter можно сделать искусственный разрыв строк, чтобы написать несколько строк в рамках одной ячейки.
При чем это к нашей теме? А отношение самое прямое. Как правило, чтобы удалить эти разрывы, пользователь вручную убирает соответствующие непечатаемые символы. Но это можно сделать проще, с помощью окна «Найти/Заменить». После этого в поле «Найти» указывается символ разрыва строки. Его можно ввести нажатием комбинации клавиши Ctrl + J. В соответствующем месте появится точка, которая обозначает этот непечатный символ.
После этого нам остается ввести символ, на который будет осуществляться замена. Поскольку перед нами стоит цель убрать все переносы строк, то логично их заменить на пробелы. Если этого не сделать, то символы могут просто слиться в одно большое слово, что нам не надо.
Видим, что после того, как мы нажали «ОК», три строки были слиты в одну, и при этом после каждого слова стоит пробел.
Если посмотреть на окно поиска и замены еще раз, то будет видно. что после нажатия на кнопку «Параметры» появляется еще некоторое количество параметров, дающих возможность выбрать область поиска, особенности просмотра и где осуществлять поиск нужных значений.
Также можно настроить отображение значений с учетом регистра (то есть, больших и маленьких букв), а также поставить флажок возле пункта «Ячейка целиком». Что касается формата, то он будет отображаться в специальном поле возле кнопки настройки.
Синтаксис функции Replace и параметры
В целом, синтаксис этой функции выглядит следующим образом.
Replace(expression, find, replace, [start], [count], [compare])
Давайте разберем аргументы этой функции более детально:
- Expression. Это изначальная строка, замена которой требуется.
- Find – это подстрока, входящая в состав строки верхнего уровня, в которой нужно осуществлять замену значения.
- Replace – это подстрока, которая заменяет ту подстроку, которая описана в предыдущем аргументе. Проще говоря, тот текст, на который требуется заменить.
- Start. Этот параметр необязательный. Характеризует порядковый номер символа строки, с которого макрос будет осуществлять поиск. Та часть строки, которая располагается до этого номера, просто не учитывается при замене.
- Count. Это количество итераций, в ходе которых будет осуществляться замена. Этот параметр также является необязательным.
- Compare. Это значение в числовом формате, которое используется для указания вида сравнения. Данный параметр также указывать необязательно.
Если опустить все необязательные аргументы, то синтаксис функции Replace будет выглядеть менее устрашающе.
Replace(expression, find, replace)
У многих людей возникает вопрос: чем отличается параметр от аргумента. Несмотря на то, что эти термины часто используют в качестве синонимов, это не совсем правильно. Аргумент функции – это фактически переменная, которая используется при вычислении. Параметр же – это значение, которое находится в переменной.
Параметр compare
Использование параметра compare имеет свои особенности. Нужно указывать не текстовое значение, а числовое, которое служит константой, обозначающей тип сравнения. Давайте рассмотрим их более подробно.
- -1. В этом случае в качестве аргумента используется то значение, которое определяется оператором Option Compare.
- 0. Это двоичное сравнение.
- 1 – текстовое сравнение.
Отличие между текстовым и двоичным сравнением заключается в том, что отличаются буквенные регистры. В первом случае они не учитываются, а во втором большие и маленькие буквы берутся в учет при поиске строк и их замене.
Бинарное сравнение используется по умолчанию. Следовательно, если не указывать этого аргумента в функции, то регистры будут учитываться. Если же нужно сделать так, чтобы программа их в учет не брала, то обязательно в качестве последнего параметра нужно указать единицу.
Возвращаемое значение
В программировании есть такое понятие, как тип данных. Поскольку таблицы Excel тоже являются программируемыми, то даже незнакомый с макросами человек понимает, что это такое. В языке VBA существует несколько основных типов данных. В нашем случае возвращается тип String.
Если значение аргумента Expression нулевое, то и строка вернется нулевая. Если же было в качестве значения Expression значение Null, то формула выдаст ошибку. Нужно учитывать еще некоторые моменты при использовании этой функции:
- Если в качестве аргумента Find вводится строка, длина которой нулевая, то функция вернет то же значение, которое введено в качестве параметра Expression. Технически это означает, что нет особой подстроки, которую нужно искать в рамках строки, которая передается аргументу Expression.
- Если длина параметра Replace нулевая, то тогда возвращается строка, соответствующая первому параметру с удаленной частью второго параметра. Технически это означает, что строка текста была заменена на пустое значение.
- Если в качестве параметра Start было передано число, превышающее длину искомой строки, то вернется пустая строка. Если же этому аргументу передать число 0, то поиск будет осуществляться после нулевого символа, то есть сразу. Поэтому возвращаться будет то значение, которое указано в качестве параметра Expression.
На эти моменты нужно обратить особое внимание и запомнить их перед тем, как использовать функцию Replace. Это здорово поможет в отладке написанных макросов, если знать эти особенности.
Пример №1
В этом примере имеется единственное вхождение искомой подстроки. И его нужно заменить.
Sub Primer1()
Dim a
a = «Сливочное масло»
a = Replace(a, «Сливочное», «Рыжиковое»)
MsgBox a
‘Результат: «Рыжиковое масло»
End Sub
Пример №2
Данный пример осуществляет замену нескольких вхождений подстроки.
Sub Primer2()
Dim a
a = «Идёт медведь, идёт лиса, идёт грач»
‘с параметром compare по умолчанию
a = Replace(a, «идёт», «бежит»)
MsgBox a
‘Результат:
‘Идёт медведь, бежит лиса, бежит грач
a = «Идёт медведь, идёт лиса, идёт грач»
‘с параметром compare=1(vbTextCompare)
a = Replace(a, «идёт», «бежит», , , 1)
MsgBox a
‘Результат:
‘бежит медведь, бежит лиса, бежит грач
End Sub
Пример №3
В этом случае осуществляется замена одной подстроки, на поиск начинается только с пятнадцатого символа.
Sub Primer3()
Dim a
a = «Идёт медведь, идёт лиса, идёт грач»
a = Replace(a, «идёт», «бежит», 15, 1)
MsgBox a
‘Результат:
‘бежит лиса, идёт грач
End Sub
Выводы
Видим, что функция Replace достаточно удобна в использовании. С ее помощью можно добиться максимальной гибкости, которая безусловно важна в работе с электронными таблицами. Она является тем кирпичиком, который превращает Excel из программы для составления больших баз данных в полноценный инструмент, который может все.
Функция «Найти и заменить» активно используется и дает возможность при правильном применении сэкономить огромное количество времени. И это если она используется сама по себе. Если же написать скрипт, то нужно один раз вложить время, чтобы потом его экономить.
Существует еще огромное количество функций, которые позволяют автоматизировать работу с текстом в Excel. Причем есть такие причудливые, например, как функция Space(), которая дает возможность получить строку, основываясь на количестве пробелов. Каждая функция может сочетаться с другими, что дает почти неограниченный простор возможностей.
При этом мы видим, что эта функция VBA не заменяет полностью всех возможностей Excel по замене строк. Так, не поддерживается настройка формата и ряд других возможностей. Тем не менее, это можно сделать с помощью других функций, поэтому нет необходимости такое большое количество аргументов использовать в рамках одной простой процедуры.
