我只能回答你的部分问题： comparing two columns and detecting that they differ .
在上面的_142440的答案中，你有一个很好的教程答案 .
但是，这对大型数据集执行速度非常慢，因为：
一次读取一个单元格的范围非常慢;
逐个比较值是低效的，特别是对于字符串，当值的数量达到数万时，
点（1）是重要的一点：VBA使用 var = Range("A1") 获取单个单元需要相同的时间，因为它使用 var = Range("A1:Z1024") 一次性获取整个范围;并且与工作表的每次交互所花费的时间是VBA中字符串比较的四倍，并且比浮点小数之间的比较长二十倍;反过来，这比整数比较长三倍 .
对于每个VBA与细胞或细胞范围的相互作用，'s in Office 2010 and 2013 (I tested them); for older version of Excel, you'11将看到引用的时间在1/50和1/500秒之间 . 因为在旧版本和新版本的Excel中，VBA操作仍将是一位数的微秒数，因此这样做会慢一点：你的代码运行速度至少要快一百倍，可能要运行数千次更快，如果您避免在旧版本的Excel中从工作表中逐个单元格读取 .
arr1 = Range1.Values
arr2 = Range2.Values
' Consider checking that the two ranges are the same size
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
你'll notice that this code sample is generic, for two ranges of the same size taken from anywhere - even from separate workbooks. If you'比较两个相邻的列，加载一个包含两列的单个数组并比较 IF arrX(i, 1) <> arrX(i,2) Then 将使运行时减半 .
你的下一个挑战只有在你从大范围内获取数以万计的 Value 时才有意义：对于任何小于此值的扩展答案，没有性能提升 .
Public Function RangeCompare(Range1 as Excel.Range, Range2 As Excel.Range) AS Boolean
' Returns TRUE if the ranges are identical.
' This function is case-sensitive.
' For ranges with fewer than ~1000 cells, cell-by-cell comparison is faster
' WARNING: This function will fail if your range contains error values.
RangeCompare = False
If Range1.Cells.Count <> Range2.Cells.Count Then
RangeCompare = False
ElseIf Range1.Cells.Count = 1 then
RangeCompare = Range1.Value2 = Range2.Value2
Else
RangeCompare = MD5(Join2D(Range1.Value2)) = MD5(Join2D(Range2.Value2))
Endif
End Function
我在这个VBA函数中包装了Windows System.Security MD5哈希： ``
Public Function MD5(arrBytes() As Byte) As String
' Return an MD5 hash for any string
' Author: Nigel Heffernan Excellerando.Blogspot.com
' Note the type pun: you can pass in a string, there's no type conversion or cast
' because a string is stored as a Byte array and VBA recognises this.
oMD5 As Object 'Set a reference to mscorlib 4.0 to use early binding
Dim HashBytes() As Byte
Dim i As Integer
Set oMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
HashBytes = oMD5.ComputeHash_2((arrBytes))
For i = LBound(HashBytes) To UBound(HashBytes)
MD5 = MD5 & Right("00" & Hex(HashBytes(i)), 2)
Next i
Set oMD5 = Nothing ' if you're doing this repeatedly, declare at module level and persist
Erase HashBytes
End Function
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
Sub columnCompare()
Dim sh1 As Worksheet, sh2 As Worksheet, r1 As Range, r2 As Range
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set r1 = sh1.Range("A1")
Set r2 = sh2.Range("A1")
While r1 <> "" And r2 <> ""
If r1 = r2 Then r2.Offset(0, 1) = r1
Set r1 = r1.Offset(1, 0)
Set r2 = r2.Offset(1, 0)
Wend
End Sub
3 回答
我只能回答你的部分问题： comparing two columns and detecting that they differ .
在上面的_142440的答案中，你有一个很好的教程答案 .
但是，这对大型数据集执行速度非常慢，因为：
一次读取一个单元格的范围非常慢;
逐个比较值是低效的，特别是对于字符串，当值的数量达到数万时，
点（1）是重要的一点：VBA使用
var = Range("A1")获取单个单元需要相同的时间，因为它使用var = Range("A1:Z1024")一次性获取整个范围;并且与工作表的每次交互所花费的时间是VBA中字符串比较的四倍，并且比浮点小数之间的比较长二十倍;反过来，这比整数比较长三倍 .因此，如果您一次性读取整个范围，并且在VBA中处理
Range.Value2数组，那么您的代码可能快四倍，并且可能快一百倍 .对于每个VBA与细胞或细胞范围的相互作用，'s in Office 2010 and 2013 (I tested them); for older version of Excel, you'11将看到引用的时间在1/50和1/500秒之间 . 因为在旧版本和新版本的Excel中，VBA操作仍将是一位数的微秒数，因此这样做会慢一点：你的代码运行速度至少要快一百倍，可能要运行数千次更快，如果您避免在旧版本的Excel中从工作表中逐个单元格读取 .
如此大的收益 - 用户可感知的间隔 - 在单个“命中”中获取范围，然后在VBA中对阵列的每个项目执行比较 .
你'll notice that this code sample is generic, for two ranges of the same size taken from anywhere - even from separate workbooks. If you'比较两个相邻的列，加载一个包含两列的单个数组并比较
IF arrX(i, 1) <> arrX(i,2) Then将使运行时减半 .你的下一个挑战只有在你从大范围内获取数以万计的 Value 时才有意义：对于任何小于此值的扩展答案，没有性能提升 .
我们正在做的是：
使用哈希函数比较两个大范围的值
这个想法非常简单，虽然基础数学对于非数学家来说非常具有挑战性：我们不是一次比较一个值，而是运行一个数学函数，将值“散列”为一个简短的标识符，以便于比较 .
如果您要将范围与“引用”副本进行比较，则可以存储“引用”哈希值，这会将工作量减半 .
有一些快速可靠的散列函数，它们在Windows中作为安全和加密API的一部分提供 . 它们运行在字符串上有一个小问题，我们有一个数组可以工作;但是你可以很容易地找到一个快速'Join2D'函数，它从一个范围的
.Value2属性返回的2D数组中获取一个字符串 .因此，两个大范围的快速比较功能将如下所示： ``
我在这个VBA函数中包装了Windows System.Security MD5哈希： ``
还有其他的VBA实现，但似乎没有人知道字节数组/字符串类型双关语 - 它们不相同，它们是 identical - 所以每个人都编码不必要的类型转换 .
2015年，快速简单的Join2D功能为posted by Dick Kusleika on Daily Dose of Excel：
如果在进行比较之前需要删除空白行，则需要Join2D function I posted in StackOverflow back in 2012 .
这种类型的哈希比较最常见的应用是电子表格控制 - change monitoring - 你会看到
Range1.Formula而不是Range1.Value2：但你的问题是关于比较值，而不是公式 .假设您的值位于名为
Sheet1和Sheet2的工作表的A列中 . 然后，您可以将以下公式放入Sheet2Sheet2并向下拖动以覆盖所需范围：=IF(Sheet1!A1=Sheet2!A1,Sheet2!A1,"")或者，如果您更愿意使用VBA，请将此代码放入模块中：
如果我理解正确，这就是你想要的，
Sheet1
Sheet2
在
B2中输入以下公式sheet2并向下拖动，如图所示，