05.01.2021
Регулярные выражения, – это конечно хорошо и даже прекрасно. Ниже как раз даются примеры использования шаблонов для нахождения из строки той или иной информации. Практически весь текст статьи является лишь компиляцией информации из интернет. Я лишь записал примеры сначала 4-х видео, а затем информацию от Николая Павлова, материал сайта которого мне особенно нравится.
Я попробовал применить регулярные выражения для выделения городов, деревень и сёл из имеющихся у меня примеров адресов. Всё работает быстро и достаточно хорошо, даже замечательно.
Но вот мне надо выделить улицу, до и квартиру. И здесь полный облом и апломб.
Выручает как раз применение макросов и VBA. Есть одно но. Надо иметь базу краев, областей, городов и других населённых пунктов, улиц, которые встречаются в адресах. Если адресов 500 или 1000, то это легко сделать, тем более, что для отдельных районов такие псевдобазы или списки можно найти. Часть недостающей информации можно выделить из имеющихся уже адресов даже вручную.
Выделяем населенный пункт, а перед этим район и область, если они есть, затем выделяем улицу. Дом и квартиру можно найти исходя из того, что они часто полностью числовые, находятся после улицы. Квартира находится аналогично. В черновую мой макрос работает и неплохо. Я проверял на выборке из 500 адресов. Возникает иногда проблема с селами или деревнями, если нет улиц. Но она решается добавления села в улицы. Желательно кроме наименований иметь также и поле расшифровки, указывающей на тип области, населённого пункта или улицы. Что это? Край или республика. Город, село или деревня. Улица, проспект, переулок или тупик и т.д. может возникать и конфликт названий, если в базе они разного типа. Но это всё решаемо. Ниже материал от 21 декабря 2020 года.
Освой самостоятельно регулярные выражения. 10 минут на урок by Форта Бен (z-lib.org)
Для работы с регулярными выражениями в Excel есть специальная библиотека RegExp.
Эту библиотеку можно подключить через Tools -> Reference Это применяется при раннем связывании. При этом программа будут выдавать подсказки в коде.
Можно сразу получить новый объект типа RegExp.
- Sub Regulyrki()
- Dim ObjRegExp As New RegExp
- End Sub
Или так:
- Sub Regulyarka
- ‘Задаем переменную для хранения регулярного выражения
- Dim myRegExp As RegExp
- Set myRegExp = New RegExp
- End Sub
Рассмотрим программу
- Sub Regulyrki()
- Dim ObjRegExp As New RegExp
- ‘Dim ObjRegExp as Object
- Dim Str$
- Dim i%
- Str = “Паша маШа саша юля таня каша” ‘ Строка в которой мы будем искать соответствие
- ‘Set ObjRegExp=createObject(“VBScript.RegExp”) ‘Это нужно для позднего связывания
- With ObjRegExp
- .Pattern = “.аша” ‘Шаблон поиска в строке
- .Global = True ‘ Найти все вхождения при True. Если будет False, то ищется лишь ‘первое вхождение в строку
- .IgnoreCase = True ‘Игнорировать регистр. По умолчанию всегда False
- .MultiLine = False ‘ однострочный текст
- ‘ MsgBox .Test(Str) ‘Проверка на соответствие шаблону (т.е. что-то найдется, если True)
- ‘MsgBox .Replace(Str, “WWW”) ‘Возможная замена найденного текста по шаблону
- With .Execute(Str) ‘Возвращает коллекцию найденных значений
- For i = 0 To .Count – 1 ‘ перебор элементов коллекции
- MsgBox .Item(i).Value ‘Вывод найденного элеиента
- ‘MsgBox .Item(i).FirstIndex’ Вывод первого символа найденной подстроки в строке-оригинале
- ‘MsgBox .Item(i).Length ‘Вывод длины найденной подстроки
- Next
- End With
- End With
- End Sub
Точка . – означает любой символ кроме символа начала новой строки
В нашем случае, если же шаблон установить как .Pattern=”\..аша”, то ничего не найдется. Левый слэш защищает следующий элемент за ним, то есть точку. Поэтому в исходной тексте будет искаться точка, затем любой символ, а затем группа букв аша. У нас ни одного слова с точки не начинается. При этом команда Msgbox Test(Str) выдаст False.
Если в шаблоне поиска убрать \., то найдутся все 4 слова с аша на конце. ==> Паша маШа саша каша. Регистр поиска мы игнорировали, поэтому нашлись все слова поиска.
Смотрите видео 114-117 на сайте VEBACXEL. Видео содержит немного рекламы.
https://www.youtube.com/watch?v=vqdUqlHBSzQ
Давайте заменим слово саша на саша1, т.е. добавим ещё один символ.
Для поиска этого нового слова изменим шаблон на «.аша.» и запустим макрос.
Выведутся слова Паша, саша1, маШа, а слово каша будет не найдено.
Почему? Дело в том, что сейчас мы шаблоном ищем уже 5 символов. В слове каша же их только 4. Но почему нашлись слова Паша, саша1 и маШа. Слово саша1 понятно. В нем 5 символов, а для поиска слов Паша и маШа пятым символом оказался пробел между словами. Этого нет у слова каша.
Если регистр сделать критичным, то не выведется и слова маШа.
Напоминаю, что точка как символ защищается слева как щитом левым слэшом.
Перед организацией поиска (выборки) в строке можно сначала запустить тестирование командой Test(Str) и его результат использовать для продолжения выборки или отказа от поиска.
Найденные по шаблону символы можно всегда командой Replace заменить на другой набор символом. Это удобно особенно для обработки HTML кода, например замены адреса сайта на якорь <A HREF….>
Очень часто в шаблонах используется метасимволы, например диапазона или перечисления, заключаемого в квадратные скобки.
Если .Pattern=”[Пк]аша”, то найдутся лишь слова Паша и каша.
Пусть .Pattern=”.аша[0-9]”. В строке поиска сделаем замены Паша1 и маша2.
Эти слова только и найдутся. Изменим слова саша на саша12. Тогда выведутся слова Паша, саша1, маша2. Но у нас же саша12. Как показать это слово именно так? В этом поможет метасимвол вопросительный знак -?.
? делает необязательным символы перед ним.
Но слово саша12 опять не найдется. Выведется вновь саша1.
Заменим искомую строку вновь. Паша0 маша1 саша юля таня каша и
.Pattern=”.аша0?”, т.е. 0 может быть или отсутствовать.
Выведутся тода слова Паша0 маша саша каша, но не маша1. Опять ошибка поиска.
Заменим шаблон на .Pattern=”.аша[0-9]?” или .Pattern=”[пмск].аша[0-9]?”
После такой замены найдутся все слова с аша: – Паша0 маша1 саша каша.
Если сделать регистр критичным, то во втором случае можем потерять слово Паша0.
Поиск всех цифр от 0 до 9 можно заменить на метасимвол \d. Для удобства просмотра шаблона его можно также заключить в квадратные скобки.
Что делать с шаблоном поиска, чтобы всё таки найти и слово саша12?
Надо просто изменить шаблон .Pattern=”[пмск].аша[0-9]?” или .Pattern=”.аша[0-9]?”, заменив ? на символ +.Слово саша12 найдется, но зато слово каша потеряется. Символ + определяет наличие не менее одного символа. В нашем случае цифры от 0 до 9. Символ же * говорит о том, что цифр может и не быть.
+ работает с одним символом, а * с группой символов. После замены найдутся все 4 слова, содержащие аша. Буквы поиска также можно указать в виде диапазона, например в виде [а-яА-Я]. С диапазонами надо быть также осторожными, чтобы не захватить лишние символы из шестнадцетиричного представления символов. Например, нельзя задать в шаблоне диапазон как
[A-z]. Помимо всех латинских букв мы захватим поиском и не нужные нам символы, что может привести и к ошибке поиска лишней информации.
Отступление от темы.
То, что изложено выше, это текстовая часть видео 114 и 115. Автора четко этого видео я не определил.
Я как-то писал раньше, что современная молодежь очень часто является самоуверенной, и ей кажется, что она может легко решить любую проблему. Это и хорошо, и плохо. Был у меня один из подчинённых, который когда-то работал с 3D MAX. Он готовил титры для телевидения. Потом ему пришлось заняться САПР. Посмотрел он на работу системы Solid Works и Unigraphics и решил, что он сделает что-то подобное, используя свои знания 3D MAX. Это было проявлением полного непонимания в проектировании таких систем, незнания основ математики и т.д. Это было профанацией.
Другой молодой специалист решил в свободное время заняться парсингом сайтов, используя свои знания языка Си. Первое своё задание – создание таблицы он сделал с нуля через пару месяцев, хотя в начале считал, что это для него элементарно. Но сделал, понял, как делать парсинг. И это хорошо.
Сейчас предлагается услуга по парсингу любых сайтов. ДЛя этого, скорее всего, и применяются регулярные выражения.
К теме регулярных выражений я сам обращался неоднократно. Связанный много лет по работе со школами, я всегда удивлялся как они безобразно ведут списки, особенно, с адресами и заполняют любые таблицы.
Прекрасно извлекать ту информацию, которая изначально хорошо отформатирована при вводе. С адресами такая вещь не пройдет. Их каждый может заполнить как ему вздумается. По правилам русского языка после каждой точки или запятой надо ставить пробел. Я предпочитаю же ставить пробел лишь после запятой. Надо знать правила написания сокращений типов населённых пунктов, улиц,переулков и т.п., а также номеров квартир и корпусов в домах.
Сокращения могут пользователи поставить как впереди наименования, так и после через один или несколько пробелов, могут переставлять части адреса местами и т.д.
Очевидно, что в таком случае просто применять регулярные выражения невозможно. Нужно ещё и анализировать текст или до, или после применения регулярных выражений. Макросы это позволяют. Во всяком случае, я часто проводил анализ введённых адресов без применения этих выражений вообще. Но регулярные выражения – это круто. Это прекрасный метод поиска и вычленения частей текста для его организации.
Конец отступления.
Продолжим рассмотрение применения регулярных выражений (Видео – 116).
В наборе символов можно применить символ ^. Этот символ в Excel означает возведение в степень.
В шаблоне регулярных выражений же он означает отрицание. Символ ^ можно вставлять только внутри группы,
ограниченной квадратными скобками в отличие от символов ?, +, *. В начале шаблона этот символ означает начало строки, а символ $ – конец строки.
Пусть для нашего примера нам надо найти все слова, оканчивающиеся на аша, но не начинающиеся на п или м.
Тогда шаблон будет задан так .Pattern=”[^пм]аша”. При запуске макроса выведутся лишь слова саша и каша.
Изменим слово саша на саша-9, а каша на каша-b.
Применим шаблон .Pattern=”[^пм]аша-[0-9]”. Мы сумеем найти лишь слово саша-9. Если поставить в группе цифр отрицание [^0-9],
то найдется лишь слово каша-b.
Набор цифр можно заменить на метасимвол \d, а отрицание наличия цифр на метасимвол \D.
Т.е. \d – содержит цифры, а \D – не содержит цифры.
Пусть внутри нашего поискового набора слов появились два электронных адреса. Оба содержат символ @, а также могут
содержать латинские буквы и цифры, причем во всех частях электронного адреса.
Введем адреса qwerty@uit.ru и hard@ady.com в наш текст поиска. Создадим шаблон для вытаскивания адресов из текста.
.Pattern=”[A-Za-z0-9_]+\@[A-Za-z0-9_]+.[A-Za-z0-9_]”. Макрос найдет эти адреса.
Набор [A-Za-z0-9_] можно заменить на метасимвол \w. , но это справедливо лишь для латинского алфавита.
Тогда шаблон можно сократить и .Pattern=”\w+\@\w.\w” Запись сократилась на много.
Метасимвол \W соответственно отрицает наличие буквенно-цифровых символов (латинский шрифт).
Рассмотрим дополнительные метасимволы.
Пусть строка Str=”саша маша дмитрий юля таня”.
Добавим в эту строку ряд чисел 1999, 2000, 87.
Тогда шаблон .Pattern=”\d+” найдет все 3 числа в строке.
Как быть, когда надо выдернуть из строки числа с определённым количеством цифр?
Например, из номера телефона или из сумм.
Можно заменить шаблон на .Pattern=”\d\d\d\d”. Найдутся именно числа из четырех цифр
или выведутся на экран только 4 цифры, когда их больше.
Запись можно сократить .Pattern=”\d{4}”.
Если нам надо найти 3 или 4 цифры, то .Pattern=”\d{3,4}”.
Если надо найти более 3 цифр, то .Pattern=”\d{4,}”.
Интервалы можно задавать и для наборов букв, чтобы выводились слова
с определенным количеством букв или части слов.
.Pattern=”[а-я]+” ищет 1 и более символов.
.Pattern=”[а-я]{3,}” ищет слова с 3 и более буквами. Максимум не ограничен.
.Pattern=”\d{4,}” находит числа с 4-мя и более цифрами.
.Pattern=”[а-я]+\d{0,1}” Найдет слова с одной цифрой сзади или без неё.
Если добавить саша234. То чтобы найти и его, надо задать
.Pattern=”[а-я]+\d{0,}”
{0,1} соответствует ?
{1,} соответствует +
{0,} соответствует *
{3,} соответствует более 2 символов.
Пусть строка представляет набор тегов HTML.
Str=”<p> саша </p><p>маша78879 дмитрий8978 <p/>юля932 таня1323″.
Применим шаблон для выделения тегов .Pattern=”<p>.*</p>”
Но шаблон найдет не то, что нам нужно –
<p> саша </p><p>маша78879 дмитрий8978 <p/>
Выделения тегов не получилось. Для правильной работы шаблона придумали кванторы.
Они делятся на ленивые и жадные. В нашем случае сработала жадность. Выборка задаётся
ленивыми кванторами +?, *? или {}?/
Зададим шаблон .Pattern=”<p>.*?”</p>”. Отдельные теги выберутся.
То же произойдёт, если задать *? или {1,}?.
******************************************************
Если данные для выборки хорошо отформатированы, то для показа работы, применения регулярных выражений прекрасно подходит материал с сайта Николая Павлова.
Этот пример Excel из текста статьи Николая Павлова по регулярным выражениям с моими добавлениями (комментариями) из его статьи Анализ текста регулярными выражениями (RegExp) в Excel