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

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

псевдокод:

sheet1_row1=read row1 from sheet1 sheet2_row1=read row1 from sheet2 if sheet1_row1 = sheet2_row1 then print "Row contains same value" else print "Row contains diff value" end if 

8 Solutions collect form web for “Как сравнить две целые строки в листе”

 Sub checkit() Dim a As Application Set a = Application MsgBox Join(a.Transpose(a.Transpose(ActiveSheet.Rows(1).Value)), Chr(0)) = _ Join(a.Transpose(a.Transpose(ActiveSheet.Rows(2).Value)), Chr(0)) End Sub 

Что происходит:

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

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

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

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

Без учета регистра:

 MsgBox [and(1:1=2:2)] 

Деликатный случай:

 MsgBox [and(exact(1:1,2:2))] 

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

Без учета регистра:

 Public Function RangesEqual(r1 As Range, r2 As Range) As Boolean RangesEqual = Evaluate("and(" & r1.Address & "=" & r2.Address & ")") End Function 

Деликатный случай:

 Public Function RangesEqual(r1 As Range, r2 As Range) As Boolean RangesEqual = Evaluate("and(exact(" & r1.Address & "," & r2.Address & "))") End Function 

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

 Public Sub CompareSheets(wks1 As Worksheet, wks2 As Worksheet) Dim rowsToCompare As Long, colsToCompare As Long rowsToCompare = CheckCount(wks1.UsedRange.Rows.Count, wks2.UsedRange.Rows.Count, "Row") colsToCompare = CheckCount(wks1.UsedRange.Columns.Count, wks2.UsedRange.Columns.Count, "Column") CompareRows wks1, wks2, rowsToCompare, colsToCompare End Sub Private Function CheckCount(count1 As Long, count2 As Long, which As String) As Long If count1 <> count2 Then Debug.Print "UsedRange " & which & " counts differ: " _ & count1 & " <> " & count2 End If CheckCount = count2 If count1 < count2 Then CheckCount = count1 End If End Function Private Sub CompareRows(wks1 As Worksheet, wks2 As Worksheet, rowCount As Long, colCount As Long) Debug.Print "Comparing first " & rowCount & " rows & " & colCount & " columns..." Dim arr1, arr2 arr1 = wks1.Cells(1, 1).Resize(rowCount, colCount).Value arr2 = wks2.Cells(1, 1).Resize(rowCount, colCount).Value Dim rIdx As Long, cIdx As Long For rIdx = LBound(arr1, 1) To UBound(arr1, 1) For cIdx = LBound(arr1, 2) To UBound(arr1, 2) If arr1(rIdx, cIdx) <> arr2(rIdx, cIdx) Then Debug.Print "(" & rIdx & "," & cIdx & "): " & arr1(rIdx, cIdx) & " <> " & arr2(rIdx, cIdx) End If Next Next End Sub 
 Match = True Row1length = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column Row2length = Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column If Row1length <> Row2length Then 'Not equal Match = False Else For i = 1 To Row1length If Worksheets("Sheet1").Cells(1, i),Value <> Worksheets("Sheet2").Cells(1, i) Then Match = False Exit For End If Next End If If Match = True Then Debug.Print "match" Else Debug.Print "not match" End If 

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

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

 Option Explicit Public Sub Test() 'Check two columns Debug.Print DataAreasAreSame(Columns("a"), Columns("b")) 'Check two rows Debug.Print DataAreasAreSame(Rows(1), Rows(2)) End Sub Public Function DataAreasAreSame(ByVal DataArea1 As Range, ByVal DataArea2 As Range) As Boolean Dim sFormula As String sFormula = "=SUM(If(EXACT(" & DataArea1.Address & "," & DataArea2.Address & ")=TRUE,0,1))" If Application.Evaluate(sFormula) = 0 Then DataAreasAreSame = True End Function 

= 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 и 1/500 тыс. секунд, для каждого взаимодействия VBA с ячейкой или диапазоном ячеек. Это будет медленнее, потому что, как в старых, так и в новых версиях Excel, действия VBA по-прежнему будут в одноразрядном количестве микросекунд: ваш код будет работать как минимум в сто раз быстрее и, вероятно, в тысячи раз быстрее, если вы избегаете чтения по ячейкам со страницы в старых версиях Excel.

 arr1 = Range1.Values arr2 = Range2.Values 
' Consider checking that the two ranges are the same size ' And definitely check that they aren't single-cell ranges, ' which return a scalar variable, not an array, from .Value2
' WARNING: THIS CODE WILL FAIL IF YOUR RANGE CONTAINS AN ERROR VALUE
For i = LBound(arr1, 1) To Ubound(arr1, 2)
For j = LBound(arr1, 2) To Ubound(arr1, 2)
If arr1(i, j) <> arr2(i, j) Then bMatchFail = True Exit For End If
Next j
If bMatchFail Then Exit For
Next i
Erase arr1 Erase arr2

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

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

Что мы делаем:

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

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

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

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

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

 Public Function RangeCompare (Range1 как Excel.Range, Range2 As Excel.Range) AS Boolean
 'Возвращает TRUE, если диапазоны идентичны.
 «Эта функция чувствительна к регистру.
 «Для диапазонов с менее чем 1000 ячейками сравнение по ячейкам выполняется быстрее 
'ПРЕДУПРЕЖДЕНИЕ. Эта функция не работает, если ваш диапазон содержит значения ошибок.
RangeCompare = False
Если Range1.Cells.Count <> Range2.Cells.Count Then RangeCompare = False ElseIf Range1.Cells.Count = 1 затем RangeCompare = Range1.Value2 = Range2.Value2 еще RangeCompare = MD5 (Join2D (Range1.Value2)) = MD5 (Join2D (Range2.Value2)) Endif
Конечная функция

Я завершил hash-код Windows System.Security MD5 в этой функции VBA:

 Открытая функция MD5 (arrBytes () как байт) As String
 'Возвращает хеш MD5 для любой строки 
'Автор: Найджел Хеффернан Excellerando.Blogspot.com
«Обратите внимание на тип каламбура: вы можете передать строку, нет преобразования типа или литья ', потому что строка хранится как массив байтов, и VBA распознает это.
oMD5 As Object 'Установить ссылку на mscorlib 4.0 для использования раннего связывания

Dim HashBytes () как байт Dim i As Integer

Установите oMD5 = CreateObject («System.Security.Cryptography.MD5CryptoServiceProvider») HashBytes = oMD5.ComputeHash_2 ((arrBytes))
Для i = LBound (HashBytes) для UBound (HashBytes) MD5 = MD5 & Right ("00" и Hex (HashBytes (i)), 2) Следующий i

Установите oMD5 = Nothing ", если вы делаете это повторно, объявляйте на уровне модуля и сохраняете Стереть HashBytes

Конечная функция

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

Быстрая и простая функция Join2D была опубликована Диком Куслайкой в ​​Daily Dose of Excel в 2015 году:

 Public Function Join2D(ByVal vArray As Variant, Optional ByVal sWordDelim As String = " ", Optional ByVal sLineDelim As String = vbNewLine) As String Dim i As Long, j As Long Dim aReturn() As String Dim aLine() As String ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1)) ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2)) For i = LBound(vArray, 1) To UBound(vArray, 1) For j = LBound(vArray, 2) To UBound(vArray, 2) 'Put the current line into a 1d array aLine(j) = vArray(i, j) Next j 'Join the current line into a 1d array aReturn(i) = Join(aLine, sWordDelim) Next i Join2D = Join(aReturn, sLineDelim) End Function 

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

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

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

Excel 2016 имеет встроенную функцию TEXTJOIN

https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Глядя на @Tim Williams, отвечайте и используя эту новую функцию (которая не имеет предела строки 65536):

 Sub checkit() MsgBox WorksheetFunction.TextJoin(Chr(0), False, ActiveSheet.Rows(1).Value) = _ WorksheetFunction.TextJoin(Chr(0), False, ActiveSheet.Rows(2).Value) End Sub 

Написано как функция:

 Public Function CheckRangeValsEqual(ByVal r1 As Range, ByVal r2 As Range, Optional ByVal strJoinOn As String = vbNullString) As Boolean CheckRangeValsEqual = WorksheetFunction.TextJoin(strJoinOn, False, r1.Value) = _ WorksheetFunction.TextJoin(strJoinOn, False, r2.Value) End Function 
  • Скопируйте данные из другой рабочей книги через VBA
  • Самый быстрый способ удалить строки, которые нельзя захватить с помощью SpecialCells
  • Назначить инициированный код нескольким изображениям - Excel VBA
  • Прокручивать файлы в папке с помощью VBA?
  • Может ли VBA достичь Экземпляры Excel?
  • Как добавить подпись по умолчанию в Outlook
  • Должен ли я использовать ключевое слово Call в VB / VBA?
  • Захват выходного значения из команды оболочки в VBA?
  • Загрузите файл csv в массив VBA, а не в Excel Sheet
  • Использование VBA для получения расширенных атрибутов файла
  • Разделить строку на массив символов?
  • Давайте будем гением компьютера.