vba 在 Excel 工作簿中比较两张工作表的最佳方法是什么
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2052653/
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
What's the best way to compare two sheets in an Excel workbook
提问by noel_g
Given I have the following
鉴于我有以下
<Sheet 1>
Item QTY
A 5
B 1
C 3
<Sheet 2>
Item QTY
A 15
B 4
C 1
D 8
What is the best way to generate a report showing the difference between sheet 1 and 2?
生成显示表 1 和表 2 之间差异的报告的最佳方法是什么？
Like
喜欢
<Difference>
Item QTY
A 10
B 3
C -2
D 8
采纳答案by Doc Brown
In Excel VBA, use a Dictionary. Use your items from one of the sheets as keys, QTY as values. Put the item/QTY pairs of sheet 1 into the dictionary, then run through the items of sheet 2 update the dictionary accordingly to get the differences in there. Finally, put the result into sheet 3.
在 Excel VBA 中，使用Dictionary。将其中一张工作表中的项目用作键，将数量用作值。将工作表 1 的项目/数量对放入字典，然后遍历工作表 2 的项目，相应地更新字典以获取其中的差异。最后，将结果放入sheet 3。
EDIT: here is a complete example in code (you have to set a reference to the Microsoft Scripting runtime to get it working this way):
编辑：这是一个完整的代码示例（您必须设置对 Microsoft 脚本运行时的引用才能使其以这种方式工作）：
Option Explicit
Sub CreateDiff()
Dim dict As New Dictionary
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Dim i As Long, v As String
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
Set sh3 = ThisWorkbook.Sheets("Sheet3")
For i = 2 To sh1.Cells.SpecialCells(xlCellTypeLastCell).Row
v = Trim(sh1.Cells(i, 1).Value)
dict(v) = -sh1.Cells(i, 2).Value
Next
For i = 2 To sh2.Cells.SpecialCells(xlCellTypeLastCell).Row
v = Trim(sh2.Cells(i, 1).Value)
If dict.Exists(v) Then
dict(v) = dict(v) + sh2.Cells(i, 2).Value
Else
dict(v) = sh2.Cells(i, 2).Value
End If
Next
For i = 0 To dict.Count - 1
v = dict.Keys(i)
sh3.Cells(i + 2, 1) = v
sh3.Cells(i + 2, 2) = dict(v)
Next
End Sub
回答by devuxer
You shouldn't need VBA for this.
您不应该为此需要 VBA。
Here's what you do:
这是你要做的：
Create a new worksheet (Sheet3).
Set it up to look like this:
alt text http://img16.imageshack.us/img16/2451/consolidationsheet.jpg
Here are the formulas you will need (paste each one into the proper cell):
Note:the first two are "array formulas" -- after you paste in the formula, double-click the cell and do Ctrl-Shift-Enter (braces {} should appear around the formula)
------------------------------------------------------------------------------ Cell Formula ------------------------------------------------------------------------------ B2 =SUM(IF(Sheet1!A:A="",0,1)) <-- array formula: use Ctrl-Shift-Enter instead of Enter B3 =SUM(IF(Sheet2!A:A="",0,1)) <-- array formula: use Ctrl-Shift-Enter instead of Enter D2 =IF(D1=D,2,IF(OR(D1=B,D1=""),"",D1+1)) E2 =IF(D2="",IF(D1="",IF(OR(E1=B,E1=""),"",E1+1),2),"") G2 =IF(D2<>"",INDEX(Sheet1!A:A,D2),IF(E2<>"",INDEX(Sheet2!A:A,E2),"")) H2 =IF(D2<>"",-INDEX(Sheet1!B:B,D2),IF(E2<>"",INDEX(Sheet2!B:B,E2),""))Drag the formulas in D2:H2 down as far as you need to cover all the data for sheets 1 and 2.
Select all the data in columns G & H (including the headings).
Do Insert > PivotTable and click OK.
Click the Pivot Table and drag
[]Itemto the Row Labels box and[]QTYto the Values box.
创建一个新的工作表 (Sheet3)。
将其设置为如下所示：
替代文字 http://img16.imageshack.us/img16/2451/consolidationsheet.jpg
以下是您需要的公式（将每个公式粘贴到正确的单元格中）：
注意：前两个是“数组公式”——粘贴公式后，双击单元格并按 Ctrl-Shift-Enter（大括号 {} 应出现在公式周围）
------------------------------------------------------------------------------ Cell Formula ------------------------------------------------------------------------------ B2 =SUM(IF(Sheet1!A:A="",0,1)) <-- array formula: use Ctrl-Shift-Enter instead of Enter B3 =SUM(IF(Sheet2!A:A="",0,1)) <-- array formula: use Ctrl-Shift-Enter instead of Enter D2 =IF(D1=D,2,IF(OR(D1=B,D1=""),"",D1+1)) E2 =IF(D2="",IF(D1="",IF(OR(E1=B,E1=""),"",E1+1),2),"") G2 =IF(D2<>"",INDEX(Sheet1!A:A,D2),IF(E2<>"",INDEX(Sheet2!A:A,E2),"")) H2 =IF(D2<>"",-INDEX(Sheet1!B:B,D2),IF(E2<>"",INDEX(Sheet2!B:B,E2),""))将 D2:H2 中的公式向下拖动，直至覆盖工作表 1 和 2 的所有数据。
选择 G 和 H 列中的所有数据（包括标题）。
执行插入 > 数据透视表，然后单击确定。
单击数据透视表并拖动
[]Item到行标签框和[]QTY值框。
That's it. The Pivot Table will contain a summary for each item. No item will be repeated, and no item will be left out. The "Sum of QTY" column will actually contain the difference (since the formula uses negative for all sheet 1 quantities).
就是这样。数据透视表将包含每个项目的摘要。不会重复任何项目，也不会遗漏任何项目。“数量总和”列实际上将包含差异（因为公式对所有工作表 1 的数量使用负数）。
回答by Fionnuala
One possibility is to use ADO
一种可能性是使用 ADO
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
''http://support.microsoft.com/kb/246335
strFile = Workbooks("Book1.xls").FullName
''Note HDR=Yes, the names in the first row of the range
''can be used.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT s2.Item, s2.Qty-IIf(s1.Qty Is Null,0,s1.Qty) FROM [Sheet2$] s2 " _
& "LEFT JOIN [Sheet1$] s1 ON s2.Item=s1.Item"
rs.Open strSQL, cn, 3, 3
Workbooks("Book1.xls").Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
回答by guitarthrower
Why use VBA? On Sheet 3 comparison sheet list all possible items from sheets 1 and 2 in column A then in Column B use the following formula. Starting in B2 then copy down.
为什么要使用 VBA？在表 3 比较表上，在 A 列中列出表 1 和 2 中所有可能的项目，然后在 B 列中使用以下公式。从 B2 开始，然后向下复制。
=if(iserror(vlookup(A2,Sheet2'$A$2:$B$5,2,false),0,vlookup(A2,Sheet2'$A$2:$B$5,2,false))-if(iserror(vlookup(A2,Sheet1'$A$2:$B$5,2,false),0,vlookup(A2,Sheet1'$A$2:$B$5,2,false))
=if(iserror(vlookup(A2,Sheet2'$A$2:$B$5,2,false),0,vlookup(A2,Sheet2'$A$2:$B$5,2,false))-if(iserror( vlookup(A2,Sheet1'$A$2:$B$5,2,false),0,vlookup(A2,Sheet1'$A$2:$B$5,2,false))
Change the table range as necessary.
根据需要更改表格范围。
回答by Aaron Hoffman
you could merge both sets of data onto a single sheet side-by-side (item1, qty, item2, qty) then use the VLOOKUP() excel function to find the data from the opposite set.
您可以将两组数据并排合并到一张纸上（item1、qty、item2、qty），然后使用 VLOOKUP() excel 函数从相反的集合中查找数据。
