vba 使用公共列合并两个excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8458724/
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
Merge two excel files using a common column
提问by Indy
I have two excel sheets. I have to merge the two such that the values in one match with the other. For eg.
我有两张excel表。我必须合并两者，使一个中的值与另一个匹配。例如。
The first excel, the 2nd excel
1 t 1 tes1
2 5 3 tes3
3 t 4 tes4
4 g
Notice that in the first column of the 2nd excel, 2 is missing, so I want the first excel to look like this,
请注意，在第二个 excel 的第一列中，缺少 2，因此我希望第一个 excel 看起来像这样，
1 tes1 t
2 5
3 tes3 t
4 tes4 g
I am new to excel. Any help on this will be highly appreciated.
我是新手。对此的任何帮助将不胜感激。
采纳答案by Nicola Cossu
Sub left_join()
Dim res As Variant
Dim i As Long, lastUsedRowSh1 As Long, lastUsedRowSh2 As Long
Dim cell As Range
Sheets(3).Cells.ClearContents
Sheets(1).Range("a:b").Copy Destination:=Sheets(3).Range("a1")
Sheets(3).Columns(2).Insert Shift:=xlToRight
lastUsedRowSh1 = Sheets(1).Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
lastUsedRowSh2 = Sheets(2).Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
i = 1
For Each cell In Sheets(1).Range("a1:a" & lastUsedRowSh1)
On Error Resume Next
res = Application.WorksheetFunction.VLookup(cell.Value, Sheets(2).Range("a1:b" & lastUsedRowSh2), 2, 0)
If Err.Number = 0 Then
Sheets(3).Range("b" & i).Value = res
i = i + 1
Else
i = i + 1
End If
Next cell
End Sub
You can even solve with a simple formula.
你甚至可以用一个简单的公式来解决。
Foglio1
Foglio1
A B
1 t
2 5
3 t
4 g
Foglio2
Foglio2
A B
1 tes1
3 tes3
4 tes4
Foglio3
Foglio3
Copy the content of Foglio1 in Foglio3, then run this formula
将 Foglio1 的内容复制到 Foglio3 中，然后运行这个公式
=IF(ISERROR(VLOOKUP(Foglio1!A1,Foglio2!$A:$B,2,0))=TRUE,"",VLOOKUP(Foglio1!A1,Foglio2!$A:$B,2,0))
and drag it down. Regards.
并将其拖下。问候。
