Проверка данных Excel с использованием регулярных выражений (Regex)
В этом руководстве показано, как выполнять проверку данных в Excel с помощью регулярных выражений с помощью пользовательской функции RegexMatch.
Когда дело доходит до ограничения пользовательского ввода на листах Excel, проверка данных незаменима. Хотите разрешить только числа или даты в данной ячейке? Или ограничить текстовые значения определенной длиной? Или, может быть, запретить время за пределами заданного диапазона? Нет проблем, все это можно легко сделать с предустановленными или пользовательскими критериями проверки. Но что, если я хочу разрешить только действительные адреса электронной почты или строки, соответствующие определенному шаблону? Увы, это невозможно. Вы говорите регулярное выражение? Хм… это может сработать!
Как выполнить проверку данных Excel с помощью регулярных выражений
К сожалению, ни одна из встроенных функций Excel не поддерживает регулярные выражения, и проверка данных не является исключением. Чтобы иметь возможность проверять ввод в ячейку с помощью регулярных выражений, вам необходимо сначала создать пользовательскую функцию Regex. Еще одна сложность заключается в том, что пользовательские функции VBA не могут быть переданы для проверки данных напрямую — вам понадобится посредник в виде именованной формулы.
Учитывая вышеизложенное, давайте кратко наметим шаги, которые необходимо предпринять для проверки данных в Excel с использованием регулярных выражений:
- Создайте пользовательскую функцию Regex, которая проверяет, соответствует ли входное значение регулярному выражению.
- Определите имя для формулы Regex.
- Настройте правило проверки данных на основе именованной формулы.
- Скопируйте параметры проверки в любое количество ячеек.
Звучит как план? Попробуем реализовать это на практике!
Проверка данных Excel с использованием пользовательских регулярных выражений
В этом примере рассматривается очень распространенный случай — как разрешить только значения определенного шаблона.
Предположим, вы храните на листе несколько кодов SKU и хотите быть уверены, что в список попадут только те коды, которые соответствуют заданному шаблону. При условии, что каждый SKU состоит из 2 групп символов, разделенных дефисом, первая группа включает 3 заглавные буквы, а вторая группа – 3 цифры, вы можете идентифицировать такие значения, используя приведенное ниже регулярное выражение.
Шаблон: ^[A-Z]{3}-\d{3}$
Обратите внимание, что начало (^) и конец ($) строки привязаны, поэтому в ячейку нельзя вводить никакие символы, кроме шаблона.
1. Добавьте пользовательскую функцию сопоставления регулярных выражений.
Начните со вставки функции RegExpMatch в книгу. Код уже написан нашими гуру Excel, поэтому вам просто нужно скопировать его со страницы, указанной выше, и вставить в редактор VBA.
Вот синтаксис функции для справки:
RegExpMatch(текст, шаблон, [match_case])
Где:
- Текст (обязательно) – исходная строка (в нашем контексте – проверенная ячейка).
- Шаблон (обязательно) – регулярное выражение для сопоставления.
- Учитывать регистр (необязательно) – тип соответствия. ИСТИНА или опущено — с учетом регистра; FALSE – без учета регистра.
Кончик. Если вы являетесь пользователем нашего 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) и абсолютная ссылка для шаблон ($А$2).
Учитывая, что наша формула предназначена для проверки номеров SKU, мы назовем ее соответствующим образом: Validate_SKU.
Важная заметка! При определении формулы дважды проверьте, что первый аргумент относится к текущей выбранной ячейке, иначе формула не будет работать. Например, если на листе выделена ячейка A1, поместите A1 в первый аргумент (согласно нашим рекомендациям); если выбрано B2, то используйте B2 в качестве первого аргумента, и так далее. На самом деле не имеет значения, какую конкретную ссылку вы используете, если она соответствует выбранной в данный момент ячейке.
Пошаговые инструкции см. в разделе Как создать именованную формулу в Excel.
3. Настройте проверку данных
Выберите первую ячейку для проверки (в нашем случае A5) и создайте пользовательское правило проверки данных на основе именованной формулы. Для этого сделайте следующее:
- Нажмите Данные вкладка > Проверка данных.
- в Разрешать раскрывающемся списке выберите Пользовательский.
- Введите приведенную ниже формулу в соответствующее поле.
=Validate_SKU
- Снимите флажок «Игнорировать пустое поле», иначе ваше правило не будет работать.
При желании вы можете ввести собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных в ячейку.
Если вы чувствуете, что вам нужны подробные шаги, вот вам: Как настроить пользовательскую проверку данных в Excel.
4. Скопируйте проверку данных в другие ячейки
Чтобы скопировать настройки проверки в другие ячейки, вам нужно сделать следующее:
- Выберите ячейку с проверкой данных и нажмите Ctrl + C, чтобы скопировать ее.
- Выберите другие ячейки, которые вы хотите проверить, щелкните их правой кнопкой мыши, выберите «Специальная вставка» и выберите параметр «Проверка».
- Нажмите ХОРОШО.
Дополнительную информацию можно найти в разделе Как скопировать проверку данных.
Теперь каждый раз, когда кто-то пытается ввести недопустимый SKU в любую из проверенных ячеек, будет появляться следующее предупреждающее сообщение:
Проверка электронной почты с помощью Regex
Чтобы выполнить проверку электронной почты, вы начинаете с написания регулярного выражения, которое соответствует адресу электронной почты.
Шаблон: ^[\w\.\-]+@[A-Za-z0-9]+[A-Za-z0-9\.\-]*[A-Za-z0-9]+\.[A-Za-z]{2,24}$
Подробное объяснение синтаксиса см. в разделе Regex для сопоставления действительных адресов электронной почты.
А теперь укажите критерии проверки, выполнив уже знакомые шаги:
- Введите приведенное выше регулярное выражение в B2.
- Выберите ячейку A1 и определите имя, называемое Подтвердить адрес электронной почты что относится к:
=RegExpMatch(Лист1!A1, Лист1!$B$2)
- Для ячейки B5 примените пользовательскую проверку данных, используя приведенную ниже формулу. Важно, чтобы опция «Игнорировать пустые» не была выбрана.
=Validate_Email
Кроме того, вы можете настроить собственное сообщение об ошибке, предлагающее пользователю ввести действительный адрес электронной почты.
- Скопируйте правило в ячейки ниже.
Если адрес электронной почты, который вы вводите в проверенную ячейку, не соответствует шаблону регулярного выражения, появится следующее предупреждение:
Проверка паролей с помощью регулярных выражений
При использовании регулярного выражения для проверки пароля первое, что нужно решить, это то, что именно должно проверять ваше регулярное выражение. Вот несколько примеров, которые могут направить вас на верный путь.
Пароль должен состоять не менее чем из 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,}$
Установив шаблон, вы можете перейти к настройке проверки данных:
- Введите регулярное выражение пароля в C2.
- Выберите ячейку A1 и создайте именованную формулу с именем Validate_Password:
=RegExpMatch(Лист1!A1, Лист1!$C$2)
- Для ячейки C5 создайте пользовательское правило проверки с приведенной ниже формулой. Не забудьте снять флажок Игнорировать пустые поля.
=Проверить_Пароль
- Скопируйте правило в любое количество ячеек.
Теперь вы можете смело добавлять новые пароли в список. Если входная строка не соответствует регулярному выражению, следующее предупреждение напомнит вам, какие типы значений принимаются:
Проверка данных регулярных выражений не работает
Если проверка данных регулярных выражений не работает в вашем Excel, скорее всего, это происходит по одной из следующих причин.
Отсутствует функция RegExpMatch
Перед применением проверки данных обязательно вставьте код функции RegExpMatch в свою книгу.
Неверное регулярное выражение
Чтобы убедиться, что ваше регулярное выражение работает должным образом, вы можете ввести формулу RegExpMatch в какую-либо ячейку и изучить результаты. Дополнительные сведения см. в разделе Сопоставление регулярных выражений Excel с примерами.
Для анализа и отладки ваших регулярных выражений вы можете использовать бесплатные онлайн-сервисы тестирования регулярных выражений, такие как RegEx101 или же RegExr.
Неверно названная формула
Очень распространенной причиной сбоя проверки данных является именованная формула Regex, ссылающаяся на неправильную ячейку. Во всех примерах мы рекомендовали определить формулу, относящуюся к A1:
=RegExpMatch(A1, регулярное выражение)
Это работает, только если ячейка A1 активна при определении имени и используется относительная ссылка (без знака $).
Идея состоит в том, что относительная ссылка, указанная в формуле (A1), будет автоматически изменяться в зависимости от относительного положения проверенной ячейки. Другими словами, ячейка A1 выбрана просто для удобства и единообразия. Фактически, вы можете выбрать ячейку B1 и обратиться к B1, выбрать ячейку C1 и обратиться к C1 и так далее. Главное, чтобы ячейка, на которую делается ссылка, была активной ячейкой.
Чтобы проверить правильность именованной формулы, выберите любую ячейку на листе, откройте Диспетчер имен и посмотрите, на какую ячейку указывает формула. Если он относится к текущей выбранной ячейке, формула верна. В противном случае вы должны изменить ссылку в первом аргументе.
На снимке экрана ниже выбрана ячейка A7, что означает, что именованная формула должна иметь A7 в первом аргументе. Второй аргумент ($A$2) относится к регулярному выражению — эта ссылка должна оставаться постоянной, поэтому она блокируется знаком $.
Игнорировать выбранный пустой параметр
При настройке пользовательского правила проверки данных важно снять флажок Игнорировать пустые поля. В противном случае правило не будет работать по следующей причине:
Если совпадение не найдено, функция RegExpMatch возвращает FALSE. С Игнорировать пустое выбрана опция, FALSE приравнивается к пустому значению и игнорируется.
Альтернативное решение явно указывает, что формула должна возвращать TRUE:
=RegExpMatch(…)=ИСТИНА
Вот как выполнить проверку данных в Excel с помощью регулярных выражений. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Примеры проверки данных регулярных выражений (файл .xlsm)
