vba 2 张表中存在的 Excel 数据比较的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11284115/
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
Best method of Excel Data Comparison present in 2 sheets
提问by logan
Possible Duplicate:
Excel macro to match and lineup rows
可能的重复：
Excel 宏以匹配和排列行
I have an Excel workbook with 2 Sheets with same set of columns but data arranged in different way. I need to compare Sheet 2 against Sheet 1 with the use of one or more Key column and identify unmatched records.
我有一个 Excel 工作簿，其中包含 2 张具有相同列集但数据以不同方式排列的工作表。我需要使用一个或多个 Key 列将 Sheet 2 与 Sheet 1 进行比较，并确定不匹配的记录。
Data in sheet1:
sheet1中的数据：
UserId Name Salary DeptId DeptName Location
1 Loga 2000 1 HR Chennai
2 Mano 1500 2 PM Mumbai
3 Raj 2500 5 GM Delhi
Data in sheet1:
sheet1中的数据：
UserId Name Salary DeptId DeptName Location
2 Mano 1500 2 PM Mumbai
3 Raj 2500 5 GM Delhi
first I need to match records based on UserId and DeptId, if matches in both the sheet Compare Salary -> if salary matches store the record with UserId as Salary Matched. Similarly if UserId and DeptId matches in both the sheet then compare location -> if matches then store record with userid as location matched if not report as particular UserID not matched.,
首先，我需要根据 UserId 和 DeptId 匹配记录，如果在比较薪水表中匹配 -> 如果薪水匹配，则将记录与 UserId 存储为匹配薪水。同样，如果 UserId 和 DeptId 在两个工作表中都匹配，则比较位置 -> 如果匹配，则将用户 ID 的记录存储为匹配的位置，如果不报告为特定的 UserID 不匹配。，
I am planning to use HLookUp in VBA Macro for comparing but seems lengthy process when no.of rows increases and reduces performance as well. is there any suggestion available ?
我计划在 VBA 宏中使用 HLookUp 进行比较，但当行数增加并降低性能时，过程似乎很漫长。有什么建议吗？
采纳答案by Ould Abba
there is a method that i use always, its hard-coded you can optimize it according to your example. Sub findMatch()
有一种我总是使用的方法，它是硬编码的，您可以根据您的示例对其进行优化。子 findMatch()
Dim i As Integer
Dim j As Integer
Dim UserID As Integer
Dim UserID2 As Integer
Dim DeptID As Integer
Dim DeptID2 As Integer
Dim sal As Integer
Dim Salary2 As Integer
Dim Location As String
Dim Location2 As String
Dim rows1 as Integer
Dim rows2 as Integer
rows1=Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row 'rows count in sheet1
rows2=Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row 'rows count in sheet2
For i = 1 To rows1
UserID = Workbooks("yourWorkBook").Worksheets("sheet1").Cells(i, "A").Value)
'yourWorkBook is the name of the Access document
DeptID = Workbooks("yourWorkBook").Worksheets("sheet1").Cells(i, "D").Value)
Salary = Workbooks("yourWorkBook").Worksheets("sheet1").Cells(i, "C").Value)
Location = Workbooks("yourWorkBook").Worksheets("sheet1").Cells(i, "F").Value)
for j= 1 to rows2
UserID2 = Workbooks("yourWorkBook").Worksheets("sheet2").Cells(j, "A").Value)
DeptID2 = Workbooks("yourWorkBook").Worksheets("sheet2").Cells(j, "D").Value)
Salary2 = Workbooks("yourWorkBook").Worksheets("sheet2").Cells(j, "C").Value)
Location2 = Workbooks("yourWorkBook").Worksheets("sheet2").Cells(j, "F").Value)
If (UserID=UserID2) and (DeptID=DeptID2) Then
If Salary=Salary2 then 'userID, DeptID and Salary match
'you create manually another sheet (sheet3) in wich you will store the desired data
lstSalRow = Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Row 'Getting the count of rows in the sheet
'inserting after the last row
Worksheets("sheet3").Cells(lstSalRow+1, "A").Value=UserID 'Storing UserID
Worksheets("sheet3").Cells(lstSalRow+1, "B").Value=Salary 'Storing salary
Elseif strcmp(Location, Location2)=0 then 'userID, deptID and Location match
'Location matched : you can create another sheet (sheet4) in wich you will store the desired data
lstLocRow = Worksheets("sheet4").Cells(Rows.Count, "A").End(xlUp).Row
Worksheets("sheet4").Cells(lstLocRow+1, "A").Value=UserID
Worksheets("sheet4").Cells(lstLocRow+1, "B").Value=Location
Else 'only userID and DeptID match
'do other actions
End If
End If
next j
next i
End Sub
I hope it helps.
我希望它有帮助。
回答by ApplePie
I see multiple solutions. The easiest but not cleanest solution is to create a key using UserId and Dept-Id. A simple concatenation would do the job. For example, adding a G column (assuming UserId is in A and so on..) you could do: =$A2 & "-" & $D2Then use a simple lookup function to see if that unique ID you created is present in both sheets, for example : =IF(ISERROR(VLOOKUP($G2, Sheet2!$G$2:$G$3, 1, 0)), 0, 1)(sorry if the function names are incorrect, I am translating from french). Then do whatever you want with this data.
我看到了多种解决方案。最简单但不是最干净的解决方案是使用 UserId 和 Dept-Id 创建一个密钥。一个简单的串联就可以完成这项工作。例如，添加一个G柱（假设用户ID是在A等..）你可以做：=$A2 & "-" & $D2然后使用简单的查找功能以查看是否创建的唯一ID是存在于两片材，例如：=IF(ISERROR(VLOOKUP($G2, Sheet2!$G$2:$G$3, 1, 0)), 0, 1)（抱歉，如果该函数名称不正确，我是从法语翻译的）。然后对这些数据做任何你想做的事情。
