Проверка данных с помощью регулярных выражений

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

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

К сожалению, ни одна из встроенных функций Excel не поддерживает регулярные выражения, и проверка данных не является исключением. Чтобы иметь возможность проверять ввод в ячейку с помощью регулярных выражений, вам необходимо сначала создать пользовательскую функцию Regex. Еще одна сложность заключается в том, что пользовательские функции VBA не могут быть переданы для проверки данных напрямую — вам понадобится посредник в виде пользовательской функции.

Учитывая вышеизложенное, давайте кратко наметим шаги, которые необходимо предпринять для проверки данных в Excel с использованием регулярных выражений:

1. Создайте пользовательскую функцию Regex, которая проверяет, соответствует ли входное значение регулярному выражению.
2. Определите имя для формулы Regex.
3. Настройте правило проверки данных на основе именованной формулы.
4. Скопируйте параметры проверки в нужные ячейки.

Попробуем реализовать это на практике.

Проверка данных Excel с использованием пользовательских регулярных выражений

В этом примере рассматривается очень распространенный случай — как разрешить только значения по определенному шаблону.

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

'^[A-ZА-Я]{3}-\d{3}$

Обратите внимание, что начало (^) и конец ($) строки зафиксированы в шаблоне, поэтому в ячейку нельзя вводить никакие символы, кроме тех, что находятся между ними.

1. Добавьте пользовательскую функцию сопоставления регулярных выражений.

Начните со вставки функции RegExpMatch в вашу рабочую книгу. 

Код уже имеется, поэтому вам просто нужно скопировать его и вставить в редактор VBA.

Public Function RegExpMatch(input_range As Range, pattern As String, Optional match_case As Boolean = True) As Variant
    Dim arRes() As Variant 'array to store the results
    Dim iInputCurRow, iInputCurCol, cntInputRows, cntInputCols As Long 'index of the current row in the source range, index of the current column in the source range, count of rows, count of columns
 
    On Error GoTo ErrHandl
 
    RegExpMatch = arRes
 
    Set regex = CreateObject("VBScript.RegExp")
    regex.pattern = pattern
    regex.Global = True
    regex.MultiLine = True
    If True = match_case Then
        regex.ignorecase = False
    Else
        regex.ignorecase = True
    End If
 
    cntInputRows = input_range.Rows.Count
    cntInputCols = input_range.Columns.Count
    ReDim arRes(1 To cntInputRows, 1 To cntInputCols)
 
    For iInputCurRow = 1 To cntInputRows
        For iInputCurCol = 1 To cntInputCols
            arRes(iInputCurRow, iInputCurCol) = regex.Test(input_range.Cells(iInputCurRow, iInputCurCol).Value)
        Next
    Next
 
    RegExpMatch = arRes
    Exit Function
    ErrHandl:
        RegExpMatch = CVErr(xlErrValue)
End Function

 Если у вас мало опыта работы с VBA, может оказаться полезным пошаговое руководство пользователя: Как вставить создать пользовательскую функцию в Excel .

Примечание. Чтобы функция работала, обязательно сохраните файл как  книгу с поддержкой макросов (.xlsm). Думаю, здесь может пригодиться инструкция Как правильно сохранить и применять пользовательскую функцию Excel.

Вот синтаксис функции для справки:

RegExpMatch(текст, шаблон, [match_case])

Где:

• текст (обязательно) — исходная строка (в нашем контексте — проверяемая ячейка).
• шаблон (обязательно) — регулярное выражение для сопоставления.
• match_case (необязательно) — тип соответствия. ИСТИНА или опущено — ввод символов с учетом регистра; ЛОЖЬ - без учета регистра.

Подсказка. Если вы являетесь пользователем Ultimate Suite , вы можете выполнять проверку данных регулярных выражений в Excel без добавления кода VBA в свои книги. Просто используйте пользовательскую функцию AblebitsRegexMatch , включенную в инструменты Regex.

2. Создайте именованную формулу

На целевом листе выберите ячейку A1 (независимо от ее содержимого и независимо от того, какую ячейку вы фактически собираетесь проверять), нажмите Ctrl + F3 (или меню Формулы – Задать имя), чтобы открыть диспетчер имен и определить имя для этой формулы:

=RegExpMatch(Лист1!A1; "^[A-ZА-Я]{3}-\d{3}$")

Или вы можете ввести регулярное выражение в какую-нибудь ячейку (в данном примере A2) и указать $A$2 во втором аргументе:

=RegExpMatch(Лист1!A1; Лист1!$A$2)

Чтобы формула работала правильно, обязательно используйте относительную ссылку для текстового аргумента (A1) и абсолютную ссылку для шаблона ($A$2).

Назовём нашу формулу проверки: Проверка_код .

Важное замечание! При определении формулы дважды проверьте, что первый аргумент относится к текущей выбранной ячейке, иначе формула не будет работать. Например, если на листе выделена ячейка A1, укажите A1 как первый аргумент (согласно нашим рекомендациям); если выбрано B2, то используйте B2 в качестве первого аргумента, и так далее. На самом деле не имеет значения, какую конкретную ссылку на ячейку вы используете, если она соответствует выбранной в данный момент ячейке.

3. Настройте проверку данных

Выберите первую ячейку для проверки (в нашем случае A5) и создайте пользовательское правило проверки данных на основе именованной формулы. Для этого сделайте следующее:

1. Перейдите на вкладку «Данные» > «Проверка данных» .
2. В раскрывающемся списке "Разрешить" выберите Пользовательский .
3. Введите приведенную ниже формулу в соответствующее поле.

=Проверка_код 

4. Снимите флажок «Игнорировать пустое поле», иначе ваше правило не будет работать.

При желании вы можете ввести собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных.

Если вы чувствуете, что вам нужны подробные шаги, вот подробная инструкция: Как настроить проверку данных в Excel.

4. Скопируйте проверку данных в другие ячейки

Чтобы скопировать настройки проверки в другие ячейки, вам нужно сделать следующее:

1. Выберите ячейку с проверкой данных и нажмите Ctrl + С , чтобы скопировать их.
2. Выделите другие ячейки, которые вы хотите проверить, щелкните их правой кнопкой мыши, выберите «Специальная вставка» и выберите параметр «Условия на значения».
3. Нажмите ОК .

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

Проверка адресов электронной почты с помощью регулярных выражений

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

'^[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}$

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

• Имя пользователя может содержать буквы, цифры, символы подчеркивания, точки и дефисы. Имея в виду, что \w соответствует любой букве, цифре или символу подчеркивания, мы получаем следующее регулярное выражение: [\w\.\-]+
• Доменное имя может состоять из прописных и строчных букв, цифр, дефисов (но не в первой или последней позиции) и точек (в случае субдоменов). Поскольку символы подчеркивания запрещены, вместо \w мы используем 3 разных набора символов: [A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9 ]+
• Домен верхнего уровня состоит из точки, за которой следуют прописные и строчные буквы. Он может содержать от 2 до 24 букв (самый длинный TLD из существующих в настоящее время): \.[A-Za-z]{2,24}

Примечание. Шаблон предполагает, что доменное имя содержит 2 или более буквенно-цифровых символа.

А теперь укажите критерии проверки, выполнив уже знакомые шаги:

1. Введите приведенное выше регулярное выражение в B2 на Лист2.
2. Выберите ячейку A1 и определите имя Проверка_email , которое относится к диапазону:

=RegExpMatch(Лист2!A1; Лист2!$A$2)

3. Для ячейки A5 примените пользовательскую проверку данных, используя приведенную ниже формулу. Важно, чтобы опция «Игнорировать пустые ячейки» не была выбрана.

=Проверка_email 

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

4. Скопируйте правило в ячейки ниже.

Если адрес электронной почты, который вы вводите в проверенную ячейку, не соответствует шаблону регулярного выражения, появится предупреждение, текст которого вы только что настроили (см. скриншот выше).

Проверка паролей с помощью регулярных выражений

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

Пароль должен состоять не менее чем из 6 символов и включать только буквы (заглавные или прописные) и цифры:

Шаблон: ^[A-Za-z0-9]{6,}$

Пароль содержит минимум  6 символов и хотя бы одну букву и одну цифру:

^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{6,}$

Пароль должен быть не менее чем из 6 символов и содержать как минимум одну заглавную букву, одну строчную букву и одну цифру:

^(?=.*[A-Z])(?=.*[a-z])(?=.*\d)[A-Za-z\d]{6,}$

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

^(?=.*[A-Za-z])(?=.*\d)(?=.*[@$!%*#?&_-])[A-Za-z\d@$!%*#?&_-]{6,}$

Выбрав подходящий шаблон, вы можете перейти к настройке проверки вводимых данных:

1. Запишите регулярное выражение для проверки пароля в A2.
2. Установите курсор в ячейку A1, нажмите CTRL+F3 и создайте именованную формулу с именем Validate_Password:

=RegExpMatch(Лист3!A1; Лист3!$A$2)

3. Для ячейки А5 создайте пользовательское правило проверки с приведенной ниже формулой. Не забудьте снять флажок Игнорировать пустые ячейки.

4. Скопируйте правило в любое количество ячеек.

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

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

Почему проверка данных при помощи регулярных выражений не работает?

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

Отсутствует функция RegExpMatch

Перед применением проверки данных обязательно вставьте код функции RegExpMatch в свою книгу.

Неверное регулярное выражение

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

Для анализа и отладки ваших регулярных выражений вы можете использовать бесплатные онлайн-сервисы тестирования регулярных выражений, такие как RegEx101 или RegExr .

Неверно записанная формула

Очень распространенной причиной сбоя проверки данных является именованная формула Regex, ссылающаяся на неправильную ячейку. Во всех примерах мы рекомендовали определить формулу, относящуюся к A1:

=RegExpMatch(A1; regex)

Это работает правильно, только если ячейка A1 была выбранапри определении имени и используется относительная ссылка (без знака $).

Идея состоит в том, что относительная ссылка, указанная в формуле (A1), будет автоматически изменяться в зависимости от относительного положения проверяемой ячейки. Другими словами, ячейка A1 выбрана просто для удобства и единообразия. Фактически, вы можете выбрать ячейку B1 и обратиться к B1, выбрать ячейку C1 и обратиться к C1 и так далее. Главное, чтобы ячейка, на которую делается ссылка, была в момент создания формулы активной ячейкой .

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

Игнорируются пустые ячейки

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

Если совпадение не найдено, функция RegExpMatch возвращает ЛОЖЬ. Если выбран параметр «Игнорировать пустые ячейки», значение ЛОЖЬ равно пустому и игнорируется.

Альтернативное решение явно указывает, что формула должна возвращать ИСТИНА:

=RegExpMatch(…)=ИСТИНА

Вот как можно выполнить проверку данных в Excel с помощью регулярных выражений. 

Я благодарю вас за чтение и вновь жду встречи с вами в нашем блоге!

Как извлечь строку из текста при помощи регулярных выражений

Удаление символов или текста при помощи регулярных выражений

RegEx в Excel: шпаргалка по синтаксису регулярных выражений

Как удалить пробелы и пустые строки в Excel с помощью Regex

Проверка данных в Excel: как сделать, использовать и убрать

Поиск и замена в Excel с помощью регулярных выражений

Как проверить правильность ввода данных в Excel?