vba 需要匹配不同工作表中的数据，如果匹配则替换另一个单元格中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21487988/
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
Need to match data in different sheets and replace data in another cell if it's a match
提问by user2777399
I need to match data in column A2 and so on in sheet 1 to data in column A2 and so on in sheet 2. If the data in column A of sheet 1 and sheet 2 match I need the data in column B & C of sheet 2 to replace data in column B & C of sheet 1. I know VERY little about doing this kind of stuff so any help would be GREATLY appreciated!
SHEET 1
我需要将工作表 1 中 A2 列中的数据与工作表 2 中 A2 列中的数据进行匹配。如果工作表 1 中 A 列和工作表 2 中的数据匹配，我需要工作表 B 和 C 列中的数据2 替换工作表 1 的 B 和 C 列中的数据。我对做这种事情知之甚少，因此非常感谢任何帮助！
第 1 页
A B C
2 12345 5.35 9.95
3 15874 4.22 10.99
4 11111 2.24 5.99
5 98745 5.33 9.95
6 88552 4.24 8.95
SHEET 2
第 2 页
A B C
2 11111 2.09 5.79
3 12345 5.11 9.89
4 88552 4.01 8.79
NEED SHEET 1 to change to
需要将表 1 更改为
A B C
2 12345 5.11 9.89
3 15874 4.22 10.99
4 11111 2.09 5.79
5 98745 5.33 9.95
6 88552 4.01 8.79
回答by Bigtree
On a third sheet use these functions
在第三张纸上使用这些函数
column a ='sheet1'!a2
column b =if('sheet1'!b2='sheet2'!b2,'sheet2'!b2,'sheet1'!b2)
column c =if('sheet1'!c2='sheet2'!c2,'sheet2'!c2,'sheet1'!c2)
Copy sheet3 and paste the values into sheet1. This should work unless column a does match. or if you need to do this dynamically. If column a doesn't match some vlookups can be thrown in.
复制 sheet3 并将值粘贴到 sheet1 中。这应该有效，除非 a 列匹配。或者如果您需要动态执行此操作。如果 a 列不匹配，则可以抛出一些 vlookups。
I your data does match in column a your formulas could look something like this.
我你的数据在列中匹配你的公式可能看起来像这样。
column a ='sheet1'!a2
column b =if(not(iserror(vlookup(a2,'sheet2'!a:c,2,false))),vlookup(a2,'sheet2'!a:c,2,false),vlookup(a2,'sheet1'!a:c,2,false))
column c =if(not(iserror(vlookup(a2,'sheet2'!a:c,3,false))),vlookup(a2,'sheet2'!a:c,3,false),vlookup(a2,'sheet1'!a:c,3,false))
回答by user1759942
this is too much to put in a comment so I'm putting it in answer.
评论太多了，所以我把它作为答案。
The way I under stand is, since sheet1 has more data, you only want to be doing this comparison when sheet1 and 2 both have data on the row. so If sheet1 has data in rows 1 - 100, and sheet2 has data in rows 1 to 50, you only want to be doing this comparison for the first 50 rows in sheet1 right?
我的理解是，因为 sheet1 有更多数据，所以你只想在 sheet1 和 2 都在行上有数据时进行这种比较。因此，如果 sheet1 在第 1 - 100 行中有数据，而 sheet2 在第 1 到 50 行中有数据，那么您只想对 sheet1 中的前 50 行进行此比较，对吗？
So, rather than copying and pasting formulas, what you can do is, in sheet1, find an unused column. go to the first row. in the formula box, type: "=if(" without the quotes. now, select the first cell to compare, according to your example you'd select sheet1, A2. then type "=" without quotes. then select the second cell you want to compare - in your example, sheet2, A2. then type "," without quotes. then select the cell from which you want the data if the first 2 cells are equal. in your example, sheet2 B2. then type "," without quotes, then select the cell with the data to use if the first 2 cells are not equal. in your example, sheet1 b2. then type ")" without the quotes.
因此，您可以做的不是复制和粘贴公式，而是在 sheet1 中找到一个未使用的列。转到第一行。在公式框中，键入：“=if(”不带引号。现在，选择要比较的第一个单元格，根据您的示例，您将选择 sheet1、A2。然后键入“=”不带引号。然后选择第二个单元格你想比较 - 在你的例子中，sheet2，A2。然后输入“，”不带引号。如果前两个单元格相等，然后选择你想要数据的单元格。在你的例子中，sheet2 B2。然后输入“， " 不带引号，然后如果前 2 个单元格不相等，则选择包含要使用的数据的单元格。在您的示例中，sheet1 b2。然后键入不带引号的“)”。
so using your example, the function would look similar to how bigtree had it: =if('sheet1'!A2='sheet2'!A2,'sheet2'!b2,'sheet1'!b2)
因此，使用您的示例，该函数看起来类似于 bigtree 的功能： =if('sheet1'!A2='sheet2'!A2,'sheet2'!b2,'sheet1'!b2)
so, this will compare sheet1 cell A2 with sheet2 A2 and if they are equal, the cell you put the formula in will the have the value of sheet2 b2, and if they are not equal, the cell with this formula will have the value of sheet1 B2. then, drag this formula down for however many rows you want, and the formula will adjust automatically for each row. you'll see the numbers increase. then, select the column with the formulas, right click and select copy, then right click and go to paste special, and click values. this will make it so the column contains only the values, not the formulas, now you can paste this data in column B sheet 1.
因此，这将比较 sheet1 单元格 A2 和 sheet2 A2，如果它们相等，则您放入公式的单元格将具有 sheet2 b2 的值，如果它们不相等，则具有此公式的单元格将具有值表1 B2。然后，将此公式向下拖动到所需的行数，公式将自动为每一行调整。你会看到数字在增加。然后，选择带有公式的列，右键单击并选择复制，然后右键单击并转到特殊粘贴，然后单击值。这将使该列仅包含值，而不包含公式，现在您可以将此数据粘贴到 B 列工作表 1 中。
you can reuse this formula for whatever columns, just follow the steps.
您可以对任何列重复使用此公式，只需按照步骤操作即可。
HTH, good luck!
HTH，祝你好运！
