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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:12:14  来源:igfitidea点击:

What's the best way to compare two sheets in an Excel workbook

excelvba

提问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:

这是你要做的:

  1. Create a new worksheet (Sheet3).

  2. Set it up to look like this:

    alt text http://img16.imageshack.us/img16/2451/consolidationsheet.jpg

  3. 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),""))
    
  4. Drag the formulas in D2:H2 down as far as you need to cover all the data for sheets 1 and 2.

  5. Select all the data in columns G & H (including the headings).

  6. Do Insert > PivotTable and click OK.

  7. Click the Pivot Table and drag []Itemto the Row Labels box and []QTYto the Values box.

  1. 创建一个新的工作表 (Sheet3)。

  2. 将其设置为如下所示:

    替代文字 http://img16.imageshack.us/img16/2451/consolidationsheet.jpg

  3. 以下是您需要的公式(将每个公式粘贴到正确的单元格中):

    注意:前两个是“数组公式”——粘贴公式后,双击单元格并按 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),""))
    
  4. 将 D2:H2 中的公式向下拖动,直至覆盖工作表 1 和 2 的所有数据。

  5. 选择 G 和 H 列中的所有数据(包括标题)。

  6. 执行插入 > 数据透视表,然后单击确定。

  7. 单击数据透视表并拖动[]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 函数从相反的集合中查找数据。