vba 比较两张excel表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3051610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare two excel sheets
提问by 001
How do I compare two excel sheet and determine which column is missing?
如何比较两个 Excel 表并确定缺少哪一列？
(I would like to compare a list of countries from sheet A with sheet B, then mark which country is missing)
（我想比较表 A 和表 B 中的国家/地区列表，然后标记缺少哪个国家/地区）
Note: They are in random order.
注意：它们的顺序是随机的。
回答by Ben McCormack
You can use the VLOOKUPfunction in an Excel worksheet to help finding "missing" data in a different sheet. For example, take the following two worksheets:
您可以VLOOKUP在 Excel 工作表中使用该函数来帮助查找不同工作表中的“缺失”数据。例如，采用以下两个工作表：
Sheet1
------
A B C
1 aa
2 bb
3 cc
4 dd
.
.
Sheet2
------
A B C
1 aa
2 bb
3 dd
Add the following formula to cell B1in Sheetand drag the formula down through cell B4:
将以下公式添加到单元格B1中，Sheet然后将公式向下拖动到单元格中B4：
=IF(ISERROR(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)),"MISSING FROM OTHER SHEET","")
Sheet1should indicate items that are missing from the other sheet in column B, like so:
Sheet1应该指出列中其他工作表中缺少的项目B，如下所示：
Sheet1
------
A B C
1 aa
2 bb
3 cc MISSING FROM OTHER SHEET
4 dd
回答by Fionnuala
You can use ADO with Excel
您可以在 Excel 中使用 ADO
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer
''This is not the best way to refer to the workbook
''you want, but it is very conveient for notes
''It is probably best to use the name of the workbook.
strFile = ActiveWorkbook.FullName
''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
''Late binding, so no reference is needed
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
''Query example:
strSQL = "SELECT Country " _
& "FROM [Sheet1$] a " _
& "WHERE Country NOT IN " _
& "SELECT Country FROM [Sheet2$]"
''Open the recordset for more processing
''Cursor Type: 3, adOpenStatic
''Lock Type: 3, adLockOptimistic
''Not everything can be done with every cirsor type and
''lock type. See http://www.w3schools.com/ado/met_rs_open.asp
rs.Open strSQL, cn, 3, 3
If rs.Count>0 Then
MsgBox rs.GetString
End If
It is also possible to quickly write the recordset to a sheet with CopyFromRecordset.
还可以使用 CopyFromRecordset 将记录集快速写入工作表。
回答by kikito
The solution varies depending on the number of rows involved and the number of times you need to do this, and how you want the information to be presented.
解决方案取决于所涉及的行数和您需要执行此操作的次数，以及您希望如何显示信息。
If you don't have a lot of countries and you need to do this only once, the fastest solution is:
如果您没有很多国家，并且只需执行一次，最快的解决方案是：
- Copy both columns into a temporary sheet.
- Sort both columns alphabetically.
- Manually go through them and spot the differences.
- 将两列复制到临时表中。
- 按字母顺序对两列进行排序。
- 手动检查它们并发现差异。
If you need to do this just once, but there are lots of countries, the vlookup option is the fastest one.
如果您只需要执行一次此操作，但有很多国家/地区，那么 vlookup 选项是最快的选项。
If you need to repeat this procedure lots of times, and you need use that list somewhere (i.e. in other sheet) then you can use a more convoluted solution, involving two additional columns with lookups, and pivot tables. But at that point I'd look at moving it to something more manageable, like a small database.
如果您需要多次重复此过程，并且需要在某处（即在其他工作表中）使用该列表，那么您可以使用更复杂的解决方案，包括带有查找的两个附加列和数据透视表。但那时我会考虑将其移至更易于管理的地方，例如小型数据库。
回答by Má?a - Stitod.cz
I found, that in some versions of excel is feature for comparing files - but commonly is disabled, however instaled. And one day I found by chance how use it.
我发现，在某些版本的 excel 中具有比较文件的功能 - 但通常被禁用，无论安装如何。有一天我偶然发现了如何使用它。
If you have instaled TortoiseSVN (really): - select excel files to compare in your file manager - open context menu, then select TortoiseSVN > Diff - it opens excel in "comparing mode"
如果您安装了 TortoiseSVN（真的）： - 在文件管理器中选择要比较的 excel 文件 - 打开上下文菜单，然后选择 TortoiseSVN > Diff - 它会在“比较模式”中打开 excel
