excel比较并合并工作表 _知识经验

有两个工作表，均含有相同的数据，但最后一列名称和产品的数量不同，如下图1和图2所示。

文章插图
图1

文章插图
图2
现在需要将这两个工作表合并，保留最后一列且添加一列用来存放两个工作表最后一列数据之差，如下图3所示。

文章插图
图3
这里使用VBA来解决。
由于我们要使用Dictionary对象，因此先要设置相应对象库的引用。首先，打开VBE编辑器，单击菜单“工具——引用”，找到并选取“Microsoft Scripting Runtime”前的复选框，如下图4所示。

文章插图
图4
编写代码如下：
Sub CombineSheets()
‘声明变量
‘用于存储工作表Sheet1中的数据
Dim dic1 As Scripting.Dictionary
‘用于存储工作表Sheet2中的数据
Dim dic2 As Scripting.Dictionary
‘工作表Sheet1
Dim wks1 As Worksheet
‘工作表Sheet2
Dim wks2 As Worksheet
‘工作表Sheet3
Dim wks3 As Worksheet
‘工作表中数据的最后一行
Dim lngLastRow As Long
Dim i As Long
Dim j As Long
Dim var As Variant
‘入库数量
Dim dblImport As Double
‘出库数量
Dim dblExport As Double
Dim rng1 As Range
Dim rng2 As Range
‘赋值工作表对象
Set wks1 = Sheets(“Sheet1”)
Set wks2 = Sheets(“Sheet2”)
Set wks3 = Sheets(“Sheet3”)
‘初始化字典对象
Set dic1 = New Scripting.Dictionary
Set dic2 = New Scripting.Dictionary
‘填充字典dic1
lngLastRow = wks1.Range(“A” &Rows.Count).End(xlUp).Row
Set dic1 =DicData(wks1.Range(“A1:E” & lngLastRow), 2, True)
‘填充字典dic2
lngLastRow = wks2.Range(“A” &Rows.Count).End(xlUp).Row
Set dic2 = DicData(wks2.Range(“A1:E”& lngLastRow), 2, True)
‘将数据输入到工作表Sheet3
wks3.Rows(“2:” &Rows.Count).Clear
i = 1
‘遍历字典dic1
For Each var In dic1.Keys
dblImport = 0
‘取第5列中的入库数据并求和
For Each rng1 In dic1.Item(var).Rows
dblImport = dblImport +rng1.Cells(5)
Next rng1
‘输出数据到相应的单元格
i = i + 1
For Each rng2 Indic1.Item(var).Rows(1).Cells
wks3.Cells(i, rng2.Column) = rng2
Next rng2
wks3.Cells(i, 5) = dblImport
wks3.Cells(i, 1) = i – 1
Next var
For Each var In dic2.Keys
dblExport = 0
‘取第5列中的出库数据并求和
For Each rng1 In dic2.Item(var).Rows
dblExport = dblExport +rng1.Cells(5)
Next rng1
‘输出数据到相应的单元格中并计算出入库差
lngLastRow = wks3.Range(“A”& Rows.Count).End(xlUp).Row
For j = 2 To lngLastRow
If dic2.Item(var).Cells(1, 2) =wks3.Cells(j, 2) Then
wks3.Cells(j, 6) = dblExport
wks3.Cells(j, 7).Formula =”=” & _
wks3.Cells(j, 5).Address& “-” & _
wks3.Cells(j, 6).Address
Exit For
End If
Next j
Next var
End Sub
‘使用指定区域的数据填充字典
Function DicData(rngInput AsRange, _
ColIndex As Long, _
blnHeaders As Boolean) AsScripting.Dictionary
Dim i As Long
Dim cell As Range
Dim rng As Range
Dim rngTemp As Range
Dim dic As Scripting.Dictionary
Dim strVal As String