![查找具有相同行的两个不同的 Excel 工作表](https://linux22.com/image/1335987/%E6%9F%A5%E6%89%BE%E5%85%B7%E6%9C%89%E7%9B%B8%E5%90%8C%E8%A1%8C%E7%9A%84%E4%B8%A4%E4%B8%AA%E4%B8%8D%E5%90%8C%E7%9A%84%20Excel%20%E5%B7%A5%E4%BD%9C%E8%A1%A8.png)
我有两个 Excel 表,它们的列名和格式都相同。并且它们有一行相同(可以用作主键)id
。我想得到它们的差异。
表格1:
id Name GPA
----+------+-------
1 | AA | 3
2 | BB | 2
3 | CC | 3
4 | DD | 1
表 2:
id Name GPA
----+------+-------
4 | DD | 2 (updated)
7 | YY | 2 (New)
1 | AA | 3 _
2 | DD | 2 (Updated)
结果表:
id Name GPA
----+------+-------
4 | DD | 2
7 | YY | 2
2 | DD | 2
我想仅使用更新和新行填充结果表。(我们可以使用主键识别行)
Excel 中是否有任何函数可以直接获取差异(VLOOKUP)?
如果没有的话,我如何在 Access 中编写查询来完成此操作?
答案1
Option Explicit
Sub PutChangedRecordsIntoSomewhere()
Dim rs As ADODB.Recordset
Set rs = FindChangedRecords(ThisWorkbook.Path & "\" & ThisWorkbook.Name)
Dim destSheet As Worksheet
Set destSheet = Sheets("Sheet3")
destSheet.Range("A2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
End Sub
Public Function FindChangedRecords(WorkbookPath As String) As ADODB.Recordset
Dim rst As New ADODB.Recordset
Dim cnx As New ADODB.Connection
Dim cmd As New ADODB.Command
'setup the connection
With cnx
On Error Resume Next
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source='" & WorkbookPath & "'; " & "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'"
.Open
If Err.Number <> 0 Then
MsgBox Err.Description
Set FindChangedRecords = Nothing
Exit Function
End If
On Error GoTo 0
End With
'setup the command
Set cmd.ActiveConnection = cnx
cmd.CommandType = adCmdText
cmd.CommandText = "Select s2.* " & _
"from [Sheet2$] s2 " & _
"left join [Sheet1$] s1 on s1.id = s2.id and s1.name = s2.name and s1.gpa = s2.gpa " & _
"where s1.id is null" '<-- change sheet2 to where your "table2" is
'<-- change sheet1 to where your "table1" is
rst.CursorLocation = adUseClient
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
'open the connection
rst.Open cmd
'disconnect the recordset
Set rst.ActiveConnection = Nothing
'cleanup
If CBool(cmd.State And adStateOpen) = True Then
Set cmd = Nothing
End If
If CBool(cnx.State And adStateOpen) = True Then cnx.Close
Set cnx = Nothing
'return the recordset object
Set FindChangedRecords = rst
End Function
答案2
假设您的数据在 Sheet1 上,附加的公式将为您提供新的/更新的结果,或者Blank
如果您将其复制到表 2 的右侧。
=IF(ISNA(MATCH(A2,Sheet1!$A:$A,0)),"New",IF(OR(VLOOKUP($A2,Sheet1!$A:$C,2,0)<>$B2,VLOOKUP($A2,Sheet1!$A:$C,3,0)<>$C2),"Updated",""))