Не учитывать регистр vba excel

Обновлено: 22.01.2025

В VBA Excel для работы с регулярными выражениями используется библиотека «Microsoft VBScript Regular Expression».

Создание объекта RegExp

Ранняя привязка

Обычно рекомендуется использовать объекты с ранней привязкой, так как у них выше быстродействие, а также при написании и редактировании кода доступны подсказки в виде листа свойств-методов, появляющегося автоматически или вызываемого, при необходимости, сочетанием клавиш Ctrl+Пробел.

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

Для осуществления ранней привязки необходимо подключить к проекту VBA ссылку на библиотеку «Microsoft VBScript Regular Expression», для чего в редакторе VBA выбираем Tools — References…

В открывшемся окне «References» находим строку «Microsoft VBScript Regular Expression 5.5» (если у вас ее нет, то строку «Microsoft VBScript Regular Expression 1.0»), отмечаем ее галочкой и нажимаем «ОК».

Готово — ссылка добавлена.

Создание объекта RegExp с ранней привязкой:

Поздняя привязка

Позднее связывание заключается в присвоении нового экземпляра объекта RegExp переменной, объявленной как Object, с помощью функции CreateObject.

Создание объекта RegExp с поздней привязкой:

Свойства и методы объекта RegExp

Свойства объекта RegExp

Свойство	Описание	Значение по умолчанию
Global	Определяет продолжительность поиска: False — до первого совпадения True — по всему тексту	False
IgnoreCase	Определяет чувствительность к регистру символов: False — учитывать регистр True — не учитывать регистр	False
Multiline	Определяет структуру объекта: False — однострочный True — многострочный	False
Pattern	Строка, используемая как шаблон	Пустая строка

Свойства объекта RegExp доступны для чтения и записи.

Методы объекта RegExp

Метод	Синтаксис	Описание
Execute	Execute(myStr) myStr — строка для поиска	Возвращает коллекцию найденных по шаблону подстрок в виде агрегатного объекта
Replace	Replace(myStr,myRep) myStr — строка для поиска myRep — строка для замены	Возвращает строку, в которой найденные по шаблону вхождения в исходной строке заменены на указанную подстроку.
Test	Test(myText) myText — строка для проверки	Возвращает булево значение как результат проверки соответствия строки шаблону

Свойства объектов Match и Matches Collection

Метод Execute объекта RegExp возвращает агрегатный объект Matches Collection, который содержит коллекцию объектов Match, представляющих все совпадения, найденные механизмом регулярных выражений, в том порядке, в котором они присутствуют в исходной строке. Если совпадений нет, метод возвращает объект Matches Collection без членов.

Свойства объекта Matches Collection

Свойство	Описание
Count	Количество объектов Match, содержащихся в объекте Matches Collection
Item	Индекс члена коллекции от нуля до значения свойства Count минус 1

Свойства объекта Matches Collection доступны только для чтения.

Свойства объекта Match

Свойство	Описание
FirstIndex	Позиция в исходной строке, где произошло совпадение, причем первая позиция в строке равна нулю
Length	Длина совпавшей подстроки
Value	Найденная подстрока (является свойством по умолчанию)

Свойства объекта Match доступны только для чтения.

Символы и метасимволы

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

Метасимвол (сочетание символов)	Значение
\	После этого знака метасимвол обозначает сам себя, а некоторые символы приобретают другое значение
^	Начало строки
$	Конец строки
?	Ни одного или один любой символ
*	Ни одного или несколько любых символов
+	Один или несколько любых символов
.	Любой символ, кроме знака «новая строка»
—	Определяет интервал символов
\|	Знак «или»
	Точное количество символов, стоящих перед
	Количество от n до m символов, стоящих перед
[abc]	Любой из указанных символов
[^abc]	Любой из неуказанных символов
[a-z]	Любой символ из диапазона
[^a-z]	Любой символ, не входящий в диапазон
\b	Конец слова
\B	Не конец слова
\d	Цифра
\D	Не цифра
\w	Любая буква, цифра или знак подчеркивания
\W	Не буква, не цифра и не знак подчеркивания
\s	Пробел
\S	Не пробел

В таблицу не включены редко используемые сочетания, ознакомиться с которыми можно в справочной системе разработчика. А примеры использования метасимволов в шаблонах очень хорошо представлены на этом ресурсе в разделе 4. Метасимволы.

Все подстановочные функции Excel из категории Ссылки и массивы (Lookup and Reference) , такие как ВПР (VLOOKUP), ГПР (HLOOKUP) , ПОИСКПОЗ (MATCH) не учитывают регистр символов (т.е. разницу между прописными и строчными) при поиске данных. Таким образом, при использовании, например, функции ВПР для поиска суммы соответствующей клиенту с кодом Smb3 в данной таблице:

. в итоге получим 38, а не 56 - ибо функция не видит разницы между smb3 и Smb3 и выводит первое встретившееся значение из таблицы.

Что же делать, если нужно находить значение точно, с учетом совпадения регистра символов? Ответ - нужна небольшая формула массива вместо ВПР.

Совет: если вы раньше не особо встречались с формулами массива, то очень рекомендую сходить и почитать вот эту статью, чтобы получить о них общее представление. Если тема знакомая, то читаем дальше.

Решение

Формула, которая нам нужна выглядит следующим образом:

Напомню, что в конце ввода этой формулы нужно нажать не привычный Enter , а сочетание Ctrl + Shift + Enter , чтобы ввести ее не как обычную формулу, а как формулу массива. Как видно из примера, эта формула находит правильное значение 56, то есть, фактически, различает строчные и прописные символы в написании кода, в отличие от классической функции ВПР (VLOOKUP).

Как это работает

Чтобы проще и нагляднее объяснить, как именно работает эта формула массива, лучше всего воспользоваться одним из инструментов пошаговой отладки сложных формул в Microsoft Excel - кнопкой Вычислить формулу (Evaluate formula) . Найти её можно на вкладке Формулы (Formulas) в группе Зависимости формул (Formula auditing) .

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

Итак, поехали - первый этап. Функция СОВПАД (EXACT) проверяет точное совпадение двух текстовых строк с учетом регистра и выдает на выходе ИСТИНА или ЛОЖЬ в зависимости от результата. В нашем случае мы подсунули этой функции не две ячейки для сравнения, как обычно, а массив ячеек (A4:A9), каждая из ячеек которого по очереди будет сравниваться с ячейкой D4, т.е. с нашим поисковым значением. После первого нажатия на кнопку Вычислить мы как раз увидим, как эти данные подставляются в формулу:

После второго нажатия на кнопку Вычислить мы увидим результат сравнения - массив . Видно, что точное совпадение (ИСТИНА) произошло только на пятом элементе массива:

Следующий этап. Функция СТРОКА (ROW) делает совсем простую, по-сути, вещь - выдает номер строки для текущей ячейки. Здесь же мы даем ей в качестве аргумента не одну ячейку, а массив (B4:B9), поэтому получаем на выходе набор номеров строк для каждой ячейки массива, т.е. :

Затем эти два массива попарно умножаются друг на друга, давая нам на выходе массив , т.к. ЛОЖЬ (FALSE) в понимании Excel равносильно нулю, а ИСТИНА (TRUE) - единице:

Чтобы получить порядковый номер строки с нужными нам данными внутри таблицы - вычитаем из номера строки на листе (8) номер строки начала таблицы, который определяет функция СТРОКА(B3):

Таким образом, мы получаем в итоге число 5 - номер строки в нашей таблице, где находится точное совпадение с искомым значением из D4 с учетом регистра. Осталось извлечь данные из нужной ячейки столбца по вычисленному номеру строки. Это делает функция ИНДЕКС (INDEX) , первый аргумент которой - это массив наших значений (B4:B9), а второй - номер строки в этом массиве (5), из которой мы хотим извлечь нужное нам значение:

Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.

Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.

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

Прежде, чем начнём, обратите внимание на следующие моменты:

Точно в этих списках совпадает только один адрес - "Пушкино, Набережная ул., д.61". Все остальные адреса различаются с большей или меньшей степенью разброса.
В некоторых адресах переставлены местами слова - например "Ульяновск, Лермонтова ул., д.63" и "улица Лермонтова д.63, г. Ульяновск".
В некоторых не хватает части данных - например, нет города в "Сиреневая ул. д.90" во второй таблице.
Где-то город с "г.", а где-то без. С улицами - аналогично.
Есть адреса уникальные и совершенно ни на что не похожие и ни с чем не совпадающие (Париж и Рио-де-Жанейро в конце каждого списка).
Есть адреса с орфографическими ошибками или опечатками внутри слов (Чилябинск, Козань...)

Отдельно хочу отметить проблему с Санкт-Петербургом - этот город может быть записан кучей разных способов. Чтобы учесть этот момент при связывании нам придется заранее сделать специальную таблицу преобразований. Колонки в этой таблице должны строго называться From и To и содержать все возможные варианты наименований (столбец From) и их правильные аналоги (столбец To):

Шаг 1. Грузим исходные данные в Power Query

Сначала, само-собой, нужно загрузить все наши три исходные таблицы в Power Query. Сделать это можно несколькими способами (именованный диапазон, область печати, лист целиком), но самым удобным будет, наверное, преобразование в "умные таблицы" с помощью сочетания клавиш Ctrl + T или командой Главная - Форматировать как таблицу (Home - Format as Table) .

По умолчанию, каждая умная таблица получает стандартное имя а-ля Таблица1,2. что можно, при желании изменить (но я здесь не буду).

После этого созданную "умную таблицу" можно легко залить в Power Query с помощью кнопки Из таблицы (From Table) на вкладке Данные (Data) или на вкладке Power Query (если у вас версия Excel 2010-2013 и вы установили Power Query как отдельную надстройку):

В открывшемся окне редактора запросов Power Query можно, в приципе, "допилить" наши данные при необходимости и затем сохранить полученную таблицу как подключение через Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close&Load - Close&Load to. ) :

И выбрать в следующем окне опцию Только создать подключение (Only create connection) :

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

Всё. Самая скучная часть - позади. Теперь переходим, непосредственно, к слиянию.

Шаг 2. Выполняем объединение

На вкладке Данные (Data) или на вкладке Power Query выбираем команду Получить данные / Создать запрос - Объединить - Объединить (Get Data / New Query - Combine queries - Merge) :

Откроется окно слияния:

В этом окне нужно:

1. Выбрать в выпадающих списках Таблицы 1 и 2, которые мы хотим объединить.

2. Выделить в обеих таблицах столбцы, по которым мы связываем наши списки (колонки Адрес и Место, соответственно).

3. Чтобы увидеть потом не только совпадения, но и отличия и ясно понимать что именно мы нашли, а что нет - выбрать тип соединения Полное внешнее (Full Outer).

4. Включить (самое главное!) флажок Использовать нечеткие соответствия для слияния (Use fuzzy matching to perform the merge) . Именно он заставляет Power Query искать не только точные совпадения, но и приблизительные.

Под ссылкой Параметры нечеткого соответствия (Fuzzy matching options) скрывается целый блок дополнительных настроек для нечеткого слияния:

Порог подобия (Similarity Threshold) - дробный коэффициент (от 0 до 1), определяющий, насколько строгого соответствия вы требуете при сборке. При значении этого коэффициента равном единице, Power Query будет искать, фактически, только точные совпадения. При значениях близких к нулю сильно возрастает вероятность ошибки. Имеет смысл путем 2-3 попыток подобрать максимально большое значение (т.е. максимально строгий поиск), но при котором находятся все (или большинство) результатов.
Игнорировать регистр (Ignore case) - по умолчанию Power Query учитывает регистр при поиске, т.е. различает Москва и МОСКВА, например. Включение этого флажка позволяет избавиться от регистрочувствительности при слиянии.
Сопоставление путем объединения текстовых фрагментов (Match by combining text parts) - в переводе на человеческий язык означает, что при поиске соответствий будет производится проверка на переставление слов внутри текста (помните Ульяновск и ул.Лермонтова?)
Если одному адресу в первой таблице соответствуте несколько похожих адресов во второй (это особенно актуально при низких значениях порога подобия), то можно ограничить количество найденных вариантов - за это отвечает параметр Максимальное число совпадений (Maximum number of matches) .
Чтобы учесть разные варианты написания Санкт-Петербурга - укажем нашу третью таблицу как Таблицу преобразования (Transformation Table) .

Выполнив все настройки, нажмём на ОК и развернём в появившемся окне Power Query вторую таблицу с помощью кнопки в шапке (флажок Использовать исходное имя столбца как префикс можно снять):

В результате получим что-то похожее на:

Как видите, все адреса нашли свои аналоги, кроме уникальных Парижа и Рио-де-Жанейро, в паре с которыми появились ячейки с null, т.е. пустотой.

Шаг 3. Пишем свою М-функцию подобия

В принципе, на этом можно было бы и остановиться, но, вот, лично меня во всей этой истории смущает один момент: как определить, насколько хорошо Power Query нашёл соответствие для каждого адреса? Представьте, что вам нужно объединить подобным образом таблицы по несколько тысяч строк - вероятность ошибки при таком объеме данных уже ощутимая. Как понять, где Power Query отработал нечёткое слияние хорошо (текст совпадает почти точно), а где стоит проверить совпадение вручную и, возможно, внести правки?

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

К сожалению, я не нашел в Power Query встроенных инструментов для подобного :( Однако, мы можем своими силами реализовать похожую штуку, написав собственную функцию подобия двух текстовых строк на встроенном в Power Query языке М (за идею огромное спасибо и поклон в пояс Андрею VG с нашего форума).

1 . На вкладке Данные выбираем команду Получить данные / Создать запрос - Из других источников - Пустой запрос (Get Data / New Query - From other sources - Blank query) .

2 . В открывшемся окне редактора запросов жмем на Главной (Home) или на вкладке Просмотр (View) кнопку Расширенный редактор (Advanced Editor) .

3 . В появившемся окне удаляем всё, что там есть по-умолчанию и копируем-вставляем туда М-код нашей функции:

Выглядеть это всё должно, в итоге, вот так:

Если интересны детали, то эта функция:

переводит обе текстовых строки в заглавные буквы функцией Text.Upper, чтобы избежать регистрочувствительности
разбирает исходные строки на отдельные символы функциями Text.ToList
ищет количество совпадений символов функциями List.Intersect и List.Count и помещает его в переменную matching_chars
вычисляет среднюю длину исходных текстовых строк с помощью функций Text.Length и помещает результат в переменную average_length
делит число совпадений на среднюю длину, чтобы получить коэффициент подобия

Само-собой, эта логика отличается от той, что использует Power Query при поиске соответствий (а как именно это делает Power Query - знают только разработчики в Microsoft). Однако, в подавляющем большинстве реальных случаев, наша функция со своей задачей отлично справляется - проверено на опыте.

После нажатия на Готово в правой панели окна Power Query можно переименовать нашу функцию, дав ей более наглядное имя (например, КоэфПодобия вместо Запрос1).

Теперь осталось применить её к нашим данным. Выберем на вкладке Добавление столбца команду Вызвать настраиваемую функцию (Add Column - Invoke Custom Function) и введём ее аргументы в открывшемся окне:

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

Щёлкнув правой кнопкой мыши по заголовку получившегося столбца, можно выбрать команду Заменить ошибки (Replace Errors) и легко заменить получившиеся Error в Париже и Рио-де-Жанейро на нули. Ну, а затем отсортировать нашу таблицу по убыванию по столбцу коэффициентов и выгрузить обратно в Excel уже знакомой командой Главная - Закрыть и загрузить (Home - Close&Load) :

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

P.S. А у меня в Excel такого нет!

Для всех, кто после прочтения этой статьи немедленно рванёт в свой Excel проверять наличие нечёткого поиска в Power Query, ещё разок хочу уточнить:

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

VBA функции работы со строками

ASC (строковое значение) – применяется для получения числового ASCII кода заданного символа. В качестве параметра можно передать как целую строку (проверка произойдет для первого символа в строке), так и один символ или пробел.

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

InStr (строка1, строка2, [старт]) – предназначена для поиска последовательности знаков (строка2) в заданной строке (строка1). Можно также задать начальную позицию для поиска вхождения (необязательный параметр “старт”).

InStrRev (строка1, строка2, [старт]) – аналог предыдущей функции, но поиск происходит в обратном порядке.

Left (строка, длина) – позволяет вернуть заданное количество знаков (параметр “длина”) для выбранной строки (параметр “ строка ”), начиная слева.

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

Mid (строка, длина, старт) – позволяет вернуть заданное количество знаков (параметр “длина”) для выбранной строки, начиная с заданной позиции (параметр “старт”).

Len (строка) – определяет численное количество символов в строке.

LCase (строка) – предназначена для преобразования всех символов в переданной строке в нижний регистр.

UCase (строка) – предназначена для преобразования всех символов в переданной строке в верхний регистр.

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

RSet (строка, длина) – логика работы та же, что и для предыдущей vba функции работы со строками, но процесс происходит справа.

LTrim (строка) – удалить пробелы слева в строке

RTrim (строка) – удалить пробелы справа в строке

Trim (строка) – удалить пробелы справа и слева в строке

Replace () – замена в строке одних символов на другие

Space (количество_пробелов) – формирование строки из заданного количества пробелов

String (количество, символ) – формирование строки из заданного количества прописанных символов

Spc () – применяется для формирования вывода данных в консольном окне командной строки. Происходит размножение пробелов, учитывая ширину командной строки.

StrComp (строка1, строка2) – сравнение двух строк, вернет 0 при равенстве, и -1 в противном случае.

StrConv (строка, метод, [LCID]) – данная функция vba является универсальной, так как позволяет преобразовать заданную строку в различные вариации (верхний и нижний регистр, кодировка, перевод байт).

Строка – собственно, строковое значение, над которым нужно провести преобразование.
Метод – задает метод преобразования, данный параметр может принимать следующие значения:

vbUpperCase – перевод в верхний регистр, значение 1
vbLowerCase – перевод в нижний регистр, значение 2
vbPropperCase – перевод первых символов всех слов в верхний регистр, а остальные символы – в нижний, значение 3.
vbWide – применяется для кодировки восточных языков. Перевод символов в один байт с двухбайтовых. Значение 4.
vbNarrow – аналог предыдущему параметру, но тут происходит перевод двух байт в один, значение 8.
vbKatakana – происходит преобразование символов алфавита Хирагана в символы для алфавита Катана, применим для японского языка, значение 16.
vbHiragana – аналог предыдущего параметра, но тут происходит преобразования вида Катана в Хирагана, значение 32. VBA строковые функции
vbUnicode – перевод заданной строки в Unicode кодировку, значение 64.
vbFormatUnicode – перевод из кодировки Unicode в стандартную, значение 128.

LCID – параметр не является обязательным, (vba строковые функции) он задает идентификатор региональных настроек. По умолчанию значение LocaleId, то есть, то значение, что установлено в панели управления.

StrReverse (строка) – инвертировать расположение знаков в строке, проще говоря, “отзеркаливает”.

Tab () – позволяет размножить символы табуляции в заданном количестве. Помимо этого, для вставки табуляции применяется именованная константа vbTab.

Аналогичные VBA строковые функции используются и в языке VBScript сервера сценариев Windows Script Host.

Спасибо за внимание. Автор блога Владимир Баталий

Читайте также: