Время прочтения: 7 мин.

Зачастую, при работе с многочисленными выгрузками в MS Excel нам приходится обрабатывать, фильтровать и буквально «вытаскивать» интересующие нас и так необходимые в повседневной работе данные. Степень трудоемкости таких «вытаскиваний» варьируется от «выбрать все желтые и красные строчки, потому что остальные – это не наши» до «посчитать для всех клиентов с ФИО Иванов Иван Иванович, у которых в день было больше трех операций и которые обслуживались в ВСП на территории ГОСБ, сумму их вкладных операций». В соответствии с поставленной перед нами задачей, мы выбираем инструмент для ее решения.

Но что делать, если стандартными средствами MS Excel поставленную задачу не решить. Одним из наиболее подходящих инструментов в данной ситуации является применение «регулярных выражений» (regular expressions). Проще говоря, регулярные выражения — это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст. Но, к сожалению, MS Excel, не имеет поддержки данного инструмента по умолчанию, тем не менее, это легко исправить вручную, проделав ряд следующих операций (нижеследующая настройка производится для MS Excel 2016, для MS Excel 2010 процедура аналогична):

  1. Необходимо убедиться в том, что в MS Excel включена вкладка «Разработчик» и не запрещено применение макросов:

Если вкладка отсутствует, ее необходимо активировать следующим образом:

а)      С помощью перехода в меню «Файл»-> «Параметры», отрываем окно «Параметры Excel»:

б)      На вкладке «Настроить ленту» нужно выбрать «Все вкладки» и поставить отметку в строке «Разработчик», после чего нажать «ОК»:

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

г)       В открывшемся окне нужно проверить, включены ли макросы, и нажать «ОК»:

2. Перейти в «Microsoft Visual Basic for Applications», нажав кнопку «Visual Basic» во вкладке «Разработчик» или же с помощью сочетания клавиш Alt+F11

3. В открывшемся окне, необходимо последовательно перейти в меню «Инструменты» («Tools») -> «Ссылки» («References»):

4. В окне «Ссылки» («References») нужно проставить отметку в строке Microsoft VBScript Regular Expressions 5.5, после чего нажать «ОК»:

5. В окне «Microsoft Visual Basic for Applications» создать новый модуль с помощью команд «Вставка» («Insert») -> «Модуль» («Module»):

6. В открывшемся редакторе кода, необходимо написать следующую функцию:

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

Результатом же будет искомая подпоследовательность, либо сообщение об ошибке в случае отсутствия таковой.

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

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

Метасимвол Описание
 . Точка — обозначает любой символ в шаблоне на указанной позиции (кроме знака новой строки \n).
 \s Любой знак пробела, а именно: пробел, табуляция и перенос строки.
 \S Противоположный по смыслу вариант предыдущего шаблона, то есть любой символ, не выглядящий как пробел.
 \d Цифровой символ, то есть любая цифра.
 \D Нецифровой символ — любой символ кроме цифры.
 \w Любой символ латиницы (a-z, A-Z), цифра (0-9) или знак подчеркивания (_).
 \W Анти-вариант предыдущего, то есть не латиница, не цифра и не подчеркивание.
[символы] В квадратных скобках можно указать один или несколько символов, разрешенных на указанной позиции в тексте. Например, тр[ае]к будет соответствовать любому из слов: трак или трек. Также можно не перечислять символы, а задать их диапазоном через дефис, таким образом, вместо [АБВГД] можно написать [А-Д], или вместо [12345] ввести [1-5]. Например, для обозначения всех символов кириллицы можно использовать шаблон [а-яА-ЯёЁ].
[^символы] Символ ^ придает набору символов в квадратных скобках обратный смысл — на указанной позиции в тексте будут разрешены все символы, кроме перечисленных. Так, шаблон [^дп]уть найдет суть или муть, но не дуть или путь, например.
 | Логический оператор ИЛИ для проверки по любому из альтернативных критериев. Например, (ТБ|ГОСБ|ВСП) будет искать в тексте любое из указанных слов. Обычно набор вариантов заключается в скобки.
 ^ Позиция начала строки
 $ Позиция конца строки
 \b Граница слова, то есть позиция между словом и пробелом
 \B Позиция, отличная от границы слова

Если мы ищем определенное количество символов, например, пятизначные номера ВСП или двадцатизначные номера счетов, то на помощь нам приходят квантификаторы — специальные выражения, задающие количество вхождений искомых знаков. Квантификатор применяется к предыдущему символу:

Квантификатор Описание
 ? Ноль или одно вхождение. Например, \d? будет означать одна любая цифра или ее отсутствие.
 + Одно или более вхождений. К примеру, \s+ означает один или более пробелов.
 * Ноль или более вхождений. Так [A—Z]* — как отсутствие, так и наличие любого количества прописных символов латиницы.
{число} или
{число1,число2}		 Этот квантификатор задает строго определенное вхождение символа. Например, \d{10} означает строго десять цифр, а \d{3,5} — от трех до пяти цифр.

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

К примеру, рассмотрим задачу поиска в строке ИНН:*

Как видим, теперь и 10-значные, и 12-значные ИНН извлекаются корректно. Рассмотрим шаблон более подробно: \b(\d{10}|\d{12})\b. В начале и в конце шаблона мы видим добавившиеся символы \b – они означают края слова и применяются для того, чтобы вместо ИНН из текста не тянулась часть более длинного ОГРН или еще какой-либо последовательности цифр. Также, мы видим, что уже знакомый нам шаблон \d{10} переместился в скобки и получил альтернативный шаблон \d{12} – это сделано для того, чтобы наряду с 10-значными ИНН, производился поиск и 12-значных.

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

В данном случае мы применяем шаблон вида [А-ЯЁ]+\s[А-ЯЁ]+\s[А-ЯЁ]+, который состоит из трех шаблонов [А-ЯЁ]+, соединенных между собой символами \s. Здесь все просто – [А-ЯЁ]+ это любой заглавный символ кириллицы с квантификатором +, то есть встречающийся от одного раза до бесконечности. Это, как раз, и будет либо фамилией, либо именем, либо отчеством клиента. Соединительные символы \s означают пробелы между членами ФИО.

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