Foreversoft.ru

IT Справочник
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel сравнение строк

Строковые операторы VBA и операторы сравнения

Операторы сравнения

ОператорСинтаксисОписание
=A = BРавенство: Если А равно В, то — True. Иначе — False
Если А меньше В, то — True. Иначе — False
Если А меньше или равно В, то — True. Иначе — False
>A > BБольше: Если А больше В, то — True. Иначе — False
>=A >= BБольше или равно: Если А больше или равно В, то — True. Иначе — False
<>A <> BНе равно: Если А не равно В, то — True. Иначе — False

Обычно операции сравнения используются в операторах организации циклов для принятия какого-либо решения о дальнейшем ходе выполнения операций.

Результатом любой операции сравнения является значение типа Boolean: True, False.

Если оба операнда в выражении сравнения имеют один и тот же тип данных, VBA выполняет простое сравнение для этого типа.

Если оба операнда в выражении сравнения имеют определенные типы и эти типы не являются совместимыми, VBA выдает сообщение об ошибке несовпадения типов.

Если один или оба операнда в выражении сравнения являются переменными типа Variant, VBA пытается преобразовать тип Variant в какой-либо совместимый тип.

Сравнение строк

При сравнении строк операторами отношения, VBA сравнивает каждую строку слева направо посимвольно.

В VBA одна строка равна другой только, когда обе строки содержат точно такие же символы в точно таком же порядке и обе строки имеют одну и ту же длину. Например, строки «абвгд» «абвгд » » абвгд» не равны между собой, т.к. VBA не игнорирует начальные или конечные символы пробела при сравнении строк.

Следует быть внимательным при сравнении строк переменной длины.

Двоичное и текстовое сравнение строк

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

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

Т.к. буквы верхнего регистра имеют меньшие двоичные номера, буквы верхнего регистра располагаются в алфавитном порядке перед буквами нижнего регистра. Поэтому при двоичном сравнении строк, строка «АБВ» будем меньше строки «абв».

При текстовом сравнении строк VBA не использует двоичный эквивалент символов, и не «различает» верхнего и нижнего регистра. В текстовом сравнении строка «абв» равна строке «АБВ».

Для выбора метода сравнения строк используется директива Option Compare

Option Compare [Text | Binary]

Данная директива должна находиться в области объявления модуля.

Конкатенация строк

Присоединение одной строки к другой называется конкатенацией строк.

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

Оператор конкатенации (&)

Оператор (&)в VBA используется только для конкатенации строк.

Операнд_1 & Операнд_2 [& Операнд_3..]

Операнд_N — любое допустимое строковое или численное выражение (которое преобразуется в строковое).

Тип данных результата конкатенации строк — String.

Если операнд в выражении конкатенации строк имеет значение Empty или Null, VBA интерпретирует этот операнд как строку нулевой длины (строка не содержащая символов).

Обратите внимание! Символ (&) операции конкатенации обязательно необходимо отделять пробелом от имени переменной, т.к. в противном случае VBA может интерпретировать этот символ как символ определения типа Long.

Оператор сложения в конкатенации строк

Для конкатенации строк можно также использовать оператор (+).

Этот оператор имеет такой же синтаксис и требования, как и оператор (&). Однако следует понимать, что в VBA основное предназначение оператора (+) — это арифметическое сложение. Поэтому, чтобы избежать двусмысленности чтения программного кода, для конкатенации строк настоятельно рекомендуется использовать именно оператор (&).

Приоритеты выполнения операций

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

При вычислении сложных выражений VBA следует следующим правилам:

  • Части выражения, заключенные в круглые скобки, всегда вычисляются в первую очередь;
  • Конкретные операции выполняются в зависимости от иерархии операторов (таблица ниже);
  • При равенстве иерархии операторов, они вычисляются слева направо.
ОператорКомментарии
^Возведение в степень, высший приоритет
Унарный минус
* /Умножение и деление имеют равные приоритеты
MOD
+ —Сложение и вычитание имеют равные приоритеты
&Конкатенация строк выполняется после арифметических операций перед операциями сравнения и логическими операциями
= ><>Все операции сравнения имеют равные приоритеты и выполняются слева направо. Для группирования операций надо пользоваться круглыми скобками
NOT
AND
OR
XOR
EQV
IMP

В начало страницы

В начало страницы

VBA Операторы сравнения

пример

знакназваниеОписание
=РавноВозвращает True если левый и правый операнды равны. Обратите внимание, что это перегрузка оператора присваивания.
<>Не равенВозвращает True если левый и правый операнды не равны.
>Лучше чемВозвращает True если левый операнд больше правого операнда.
Меньше, чемВозвращает True если левый операнд меньше правого операнда.
>=Больше или равноВозвращает True если если левый операнд больше или равен правому операнду.
Меньше или равноВозвращает True если левый операнд меньше или равен правому операнду.
IsСправочный капиталВозвращает значение True если ссылка на левый объект — это тот же экземпляр, что и ссылка на правый объект. Он также может использоваться с Nothing (ссылка на нулевой объект) с обеих сторон. Примечание. Оператор Is попытается принудить оба операнда к Object перед выполнением сравнения. Если какая-либо сторона является примитивным типом или Variant , который не содержит объект (либо не-объектный подтип, либо vtEmpty ), сравнение приведет к ошибке времени выполнения 424 — «Требуется объект». Если любой операнд принадлежит другому интерфейсу одного и того же объекта, сравнение вернет True . Если вам нужно проверить справедливость как экземпляра, так и интерфейса, ObjPtr(left) = ObjPtr(right) используйте ObjPtr(left) = ObjPtr(right) .

Заметки

Синтаксис VBA позволяет «цепочки» операторов сравнения, но в целом эти конструкции следует избегать. Сравнение всегда выполняется слева направо только на 2 операндах за раз, и каждое сравнение приводит к Boolean . Например, выражение .

. может быть прочитан в некоторых контекстах как проверка того, является ли b между a и c . В VBA это оценивается следующим образом:

Любой оператор сравнения, кроме Is использоваться с Object в качестве операнда будет выполняться на возвращаемом значении Object «s члена по умолчанию . Если объект не имеет члена по умолчанию, сравнение приведет к ошибке времени выполнения 438 — «Объект не поддерживает его свойство или метод».

Если Object не инициализирован, сравнение приведет к ошибке времени выполнения 91 — «Объектная переменная или С заблокированной переменной блока».

Если литерал Nothing используется с любым оператором сравнения, отличным от Is , это приведет к ошибке компиляции — «Недопустимое использование объекта».

Если Object по умолчанию Object является другой Object , VBA будет постоянно вызывать элемент по умолчанию каждого последующего возвращаемого значения до тех пор, пока не будет возвращен примитивный тип или не будет поднята ошибка. Например, предположим, что у SomeClass есть член по умолчанию Value , который является экземпляром ChildClass с членом ChildValue по ChildValue . Сравнение.

. будет оцениваться как:

Если либо операнд является числовым, а другой операндом является String или Variant подтипа String , будет выполнено числовое сравнение. В этом случае, если String не может быть отнесено к числу, результатом сравнения будет ошибка времени выполнения 13 — «Несоответствие типа».

Если оба операнда представляют собой String или Variant подтипа String , сравнение строк будет выполняться на основе параметра сравнения параметров модуля кода. Эти сравнения выполняются по характеру по характеру. Обратите внимание, что символьное представление String содержащей число, не совпадает с сопоставлением числовых значений:

По этой причине убедитесь, что переменные String или Variant передаются в числа перед выполнением численных сравнений неравенства.

Если один из операндов — это Date , то числовое сравнение по базовому двойному значению будет выполняться, если другой операнд является числовым или может быть преобразован в числовой тип.

Если другой операнд представляет собой String или Variant подтипа String который может быть перенесен в Date с использованием текущего языкового стандарта, String будет передана в Date . Если он не может быть применен к Date в текущей локали, результатом сравнения будет ошибка времени выполнения 13 — «Несоответствие типа».

Следует соблюдать осторожность при сравнении значений Double или Single и Booleans . В отличие от других числовых типов ненулевые значения нельзя считать True из-за поведения VBA в продвижении типа данных сравнения с использованием числа с плавающей точкой в Double :

Как сравнить две целые строки в листе

8 Vicky [2013-10-16 08:06:00]

Я новичок в VBA. У меня есть работа в руке, чтобы улучшить производительность кода VBA. Чтобы улучшить производительность кода, я должен прочитать целую строку и сравнить ее с другой строкой. Есть ли способ сделать это в VBA?

vba excel-vba excel

7 ответов

24 Решение Tim Williams [2013-10-16 09:01:00]

  • a является просто сокращением для Application , чтобы упростить чтение кода ниже
  • ActiveSheet.Rows(1).Value возвращает 2-мерный массив с размерами (от 1 до 1, от 1 до <количество столбцов на листе>)
  • Мы хотели бы сконденсировать массив выше в одно значение с помощью Join() , поэтому мы можем сравнить его с другим массивом из второй строки. Однако Join() работает только с массивами с 1-D, поэтому мы запускаем массив дважды через Application.Transpose() . Примечание: если вы сравнивали столбцы вместо строк, вам понадобится только один проход через Transpose().
  • Применение Join() к массиву дает нам одну строку, где исходные значения ячейки разделяются «нулевым символом» ( Chr(0) ): мы выбираем это, так как оно вряд ли будет присутствовать в любом из значений ячейки.
  • После этого мы теперь имеем две регулярные строки, которые легко сравниваются

Примечание. Как отмечал Рифафи в комментариях, Transpose() не может обрабатывать массивы с более чем ок. 65 000 элементов, поэтому вы не можете использовать этот подход для сравнения двух целых столбцов в версиях Excel, где листы имеют больше, чем это количество строк (например, любая не древняя версия).

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

Для вашего конкретного примера, вот два способа.

Нечувствительность к регистру:

Чувствительность к регистру:

Ниже приведены обобщенные функции для сравнения любых двух смежных диапазонов.

Нечувствительность к регистру:

Чувствительность к регистру:

ОК, это должно быть довольно быстро: минимальное взаимодействие между Excel UI и VBA (где и находится значительная часть медленности жизни). Предположим, что рабочие листы имеют похожие макеты из $A$1 и что мы будем пытаться сопоставить общую область UsedRange для двух листов:

1 chiliNUT [2013-10-16 08:32:00]

1 PaulG [2015-04-30 13:10:00]

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

Не считайте это так же быстро, как метод транспонирования x2, но он более гибкий. Вызов столбца занимает немного больше времени, так как есть 1M элементов для сравнения!

0 q3kep [2015-02-18 16:52:00]

= EXACT (B2; D2) и перетащить, лучший вариант для меня.

Я поставлю ответ на кувалду-к-трещине, для полноты, потому что вопрос: «Эти два диапазона одинаковы?» появляется как неисследованный компонент всех остальных, сравнивая мои диапазоны, а затем делайте это сложное дело. «.

Ваш вопрос — простой вопрос о небольших диапазонах. Мой ответ для больших; но вопрос хороший, и хорошее место для более общего ответа, потому что он прост и ясен: и «Различия в этих диапазонах?» и «Кто-то вмешался в мои данные? относятся к большинству пользователей Excel.

Большинство ответов на типичные вопросы «сравнить мои строки» — это чтение и сравнение по ячейкам в VBA. Простота этих ответов заслуживает похвалы, но этот подход очень медленно выполняется на больших наборах данных, потому что:

  1. Чтение диапазона по одной ячейке за раз очень медленно;
  2. Сравнение значений пара-по-паре неэффективно, особенно для строк, когда количество значений попадает в десятки тысяч,

Точка (1) важна: для VBA требуется один и тот же промежуток времени, чтобы выбрать одну ячейку, используя var = Range(«A1») , как и для получения всего диапазона за один раз, используя var = Range(«A1:Z1024») .

. И каждое взаимодействие с листом занимает в четыре раза больше времени, чем сравнение строк в VBA, и в двадцать раз длиннее, чем сравнение десятичных знаков с плавающей запятой; и это, в свою очередь, в три раза больше, чем целочисленное сравнение.

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

Это в Office 2010 и 2013 (я их тестировал); для более старой версии Excel вы увидите время между 1/50 th и 1/500 th секунды, для каждого взаимодействия VBA с ячейкой или диапазоном клетки. Это будет способ медленнее, потому что, как в старых, так и в новых версиях Excel, действия VBA по-прежнему будут в одноразрядном количестве микросекунд: ваш код будет работать как минимум в сто раз быстрее, и вероятно, в тысячи раз быстрее, если вы избегаете чтения по ячейкам со страницы в более старых версиях Excel.

Вы заметите, что этот образец кода является общим, для двух диапазонов одного размера, взятых из любого места — даже из отдельных книг. Если вы сравниваете два соседних столбца, загрузка одного массива из двух столбцов и сравнение IF arrX(i, 1) <> arrX(i,2) Then будет сокращать вдвое время выполнения.

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

Использование хэш-функции для сравнения значений двух больших диапазонов

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

Если вы неоднократно сравниваете диапазоны с копией «reference», вы можете сохранить хеш-ссылку «reference», и это уменьшит рабочую нагрузку.

Есть некоторые быстрые и надежные хэширующие функции, и они доступны в Windows как часть API безопасности и криптографии. Есть небольшая проблема в том, что они работают на строках, и у нас есть массив для работы; но вы можете легко найти быструю функцию «Join2D», которая получает строку из 2D-массивов, возвращаемых свойством range .Value2 .

Таким образом, быстрая функция сравнения для двух больших диапазонов будет выглядеть так:

Я обернул хэш Windows System.Security MD5 в этой функции VBA:

Существуют и другие варианты VBA, но никто не знает о байт-массиве/строчном кадре — они не эквивалентны, они идентичны — поэтому каждый кодирует ненужные преобразования типов.

Быстрая и простая функция Join2D была размещена Диком Kusleika на ежедневной дозе Excel в 2015 году:

Если вам нужно вырезать пустые строки перед проведением сравнения, вам понадобится Функция Join2D, которую я опубликовал в StackOverflow еще в 2012 году.

Наиболее распространенное применение этого типа хеш-сравнения — для управления таблицами — мониторинг изменений — и вы увидите Range1.Formula вместо Range1.Value2 : но ваш вопрос касается сравнения значений, а не формулы.

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

Сравнение текста по части предложения

Довольно часто возникает проблема сравнения двух строк(ячеек) по части текста. Если точнее — по совпадению слов. Чем больше слов в двух строках совпадает — тем больше они считаются похожими. Так, к примеру текст «Защитная пленка iPhone» и текст «Защитная пленка для Samsung GalaxyII» совпадут только на 40%, а «шла маша по шоссе» и «маша по шоссе шла» — на 100%.
Я не имею ввиду сейчас случаи вроде двух строк: «пр и вет» и «пр е вет». Для подобного сравнения можно написать решения различные, но скорость их выполнения как правило оставляет желать лучшего, да и точность такого сравнения тоже не на высоте, если не использовать всевозможные справочники
На деле подобная задача встречается достаточно часто и предположу, что данная статья может быть полезна очень многим. Итак, как ни жаль, но подобную задачу невозможно решить без применения Visual Basic for Applications(VBA). Решение, которое я предложу — функция пользователя. Поэтому прежде чем его использовать настоятельно рекомендую прочесть следующие статьи:

Option Explicit Option Compare Text ‘————————————————————————————— ‘ Procedure : CompareTxt ‘ DateTime : 10.03.2015 22:46 ‘ Author : The_Prist(Щербаков Дмитрий) ‘ WebMoney — R298726502453; Яндекс.Деньги — 41001332272872 ‘ http://www.excel-vba.ru ‘ Purpose : Сравнивает две строки по совпадению отдельных слов. Выводит процент, саму строку и номер строки ‘ s1 — исходный текст(ссылка на ячейку или текст) ‘ mass — диапазон значений для сравнения с исходным текстом(ссылка на ячейку или текст) ‘ sDelim — разделитель слов в тексте. По умолчанию пробел ‘ lFstLast — указатель, выводить первое или последнее подходящее совпадение. ‘ По умолчанию 0(последнее максимально совпадающее). ‘ Если указать 1 — будет выбрано первое подходящее(в котором совпадают все слова) ‘ lShowAllInfo — указатель на результат. Допускается четыре значения: ‘ -1 — показывается вся информация: Процент совпадения строк, Найденное значение, ‘ Номер строки в указанном диапазоне в которой найдено значение ‘ 1 — Выводится только процент совпадения строк ‘ 2 — выводится только значение ‘ 3 — выводится только номер строки с найденным значением ‘ По умолчанию применяется -1(вся информация) ‘ Синтаксис: ‘ =CompareTxt(A1;B1:B100) — с разделителем по умолчанию ‘ =CompareTxt(A1;B1:B100;»-«) — с разделителем короткое тире(-) ‘ =CompareTxt(A1;B1:B100;»-«;;2) — с разделителем короткое тире(-) и выводом только значения ‘————————————————————————————— Function CompareTxt(s1 As String, mass As Range, Optional sDelim As String = » «, Optional lFstLast As Long = 0, Optional lShowAllInfo As Long = -1) Dim as1, as2, l1 As Long, l2 As Long, lr As Long Dim asStr2 Dim s As String, s2 As String, lp, lTmpCom As Long, lResCom As Long Dim lResR As Long, sResS As String, v as1 = Split(s1, sDelim) asStr2 = mass.Value If Not IsArray(asStr2) Then ReDim asStr2(1 To 1, 1 To 1): asStr2(1, 1) = mass.Value For lr = 1 To UBound(asStr2, 1) as2 = Split(asStr2(lr, 1), sDelim) lResCom = 0 For l1 = LBound(as1) To UBound(as1) s = as1(l1) For l2 = LBound(as2) To UBound(as2) If as2(l2) = s Then lResCom = lResCom + 1 Exit For End If Next l2 Next l1 If lTmpCom = (UBound(as1) + 1) Then Exit For End If End If Next lr v = (lTmpCom / (UBound(as1) + 1)) * 100 Select Case lShowAllInfo Case -1 CompareTxt = «Процент совпадения: » & v & «; Значение: » & sResS & «; Строка в массиве mass: » & lResR Case 1 ‘только процент CompareTxt = v Case 2 ‘только значение строки CompareTxt = sResS Case 3 ‘только номер строки CompareTxt = lResR End Select End Function

Данный код необходимо вставить в стандартный модуль книги(выше я привел ссылки на статьи, чтобы более точно понять куда и как вставить). Функция ищет указанное значение( s1 ) в массиве значений( mass ) и выводит максимально подходящее значение. Максимально подходящее, естественно, полное совпадение — то, которое совпадает на 100%. Если же полного совпадения среди значений массива( mass ) не будет найдено, то будет выведено значение с максимальным процентом совпадения. В таких случаях всегда можно указать последним аргументом( lShowAllInfo ) -1 или 3, чтобы посмотреть номер строки в указанном диапазоне( mass ) и сверить уже глазами подходит это значение или нет.
Синтаксис:
=CompareTxt(A1;B1:B100) — с разделителем по умолчанию
=CompareTxt(A1;B1:B100;»-«) — с разделителем короткое тире(-)
=CompareTxt(A1;B1:B100;»-«;;2) — с разделителем короткое тире(-) и выводом только значения
Аргументы:
s1 — исходный текст(ссылка на ячейку или текст)
mass — диапазон значений для сравнения с исходным текстом(ссылка на ячейку или текст)
sDelim — разделитель слов в тексте. По умолчанию пробел.
lFstLast — указатель, выводить первое или последнее подходящее совпадение. По умолчанию 0(последнее максимально совпадающее). Если указать 1 — будет выбрано первое подходящее(в котором совпадают все слова)
lShowAllInfo — указатель на результат. Допускается четыре значения:

  • -1 — показывается вся информация: Процент совпадения строк, Найденное значение, номер строки в указанном диапазоне в которой найдено значение
  • 1 — Выводится только процент совпадения строк
  • 2 — выводится только значение
  • 3 — выводится только номер строки с найденным значением. По умолчанию применяется -1(вся информация)

Ниже функция в файле с примерами использования:

Tips_Macro_ComparePart.xls (50,5 KiB, 2 336 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Читать еще:  Основные типы данных в excel
Ссылка на основную публикацию
Adblock
detector