比较Excel中的两列和输出匹配结果
我不完全确定如何使用宏在VBA中编写它 .
我有一个工作簿，Compare.xls . 其中有两张，Sheet1和Sheet2 .
基本上我试图采取：
Sheet1
Date ID Other Sub Chan
10000 100 Repeat X 30
10000 101 Repeat X 40
Sheet2
ttc event Chan
XYZ L 30
XYZ L 40
XYZ L 6
从这些数据中，我需要将Sheet1中的Chan列与Sheet2中的匹配记录Chan记录进行比较，并将两张表中的连接数据输出到新工作表中 .
示例输出将是：
Date ID Other Sub Chan ttc
10000 100 Repeat X 30 xyz
关于我如何向前推进的片段的任何建议？
回答(2)
2 years ago
您的请求可以通过简单的 VLOOKUP 函数处理：
步骤1：在Sheet2中，复制并在工作表的开头放置列C（也就是“Chan”列） . 现在你的Sheet2数据应该是这样的
Chan ttc event
30 XYZ L
40 XYZ L
6 XYZ L
Step2：在Sheet1中，将一列（应该是列F）添加到Sheet1的末尾，并将其命名为“ttc”（因为这是您要从Sheet2中查找的内容） . 现在，您的Sheet1数据应该看起来像
Date ID Other Sub Chan ttc
10000 100 Repeat X 30
10000 101 Repeat X 40
步骤3：在Sheet1的F列中输入以下函数
=VLOOKUP(E2,Sheet2!$A$2:$C$4,2,)
输入此公式后，结果将立即显示
Explanation: the Excel Vlookup function takes the following four arguments, which are
separated with a comma:
1st argument is the cell (E2) containing the value in Sheet 1 to look for
2nd argument contains the range of data to look into (which resides in Sheet2 and the
cell range A2 through C4 is where the data resides.
NOTE1: the VLOOKUP function requires the 1st column of Sheet2 to be the column
to look into
NOTE2: we don't need to include the 1st row containing the header
NOTE3: the dollar signs represent absolute cell range so that when you copy it
down to other rows below them, they don't change (i.e., your data range
in Sheet2 is always the same
3rd argument represents the column # in Sheet2 to return if there's a match.
NOTE: column 1 starts with column A of Sheet2
4th argument is left blank
步骤4：将此公式复制到下面列F的所有其他行注意：后续行应具有公式
=VLOOKUP(F2,Sheet2!$A$2:$C$4,2,)
=VLOOKUP(G2,Sheet2!$A$2:$C$4,2,) if you have 3 rows in Sheet1
=VLOOKUP(H2,Sheet2!$A$2:$C$4,2,) if you have 4 rows in Sheet1
等等...
2 years ago
刚注意到你想要使用VBA - 不太清楚为什么用内置公式可以很容易地做到这一点？
因为你有点倒退使用
VLLOKUP可能不是最好的选择尝试使用INDEX和MATCH...这是一个简化版本......