使用VBA比较两个excel表格

提问于 2024-04-18T23:08:35+08:00

浏览次

0

我在两张表的A列中都有一个地址列表 . sheet1和sheet2

我只是想知道是否有一种有效的方法来比较两张数据并执行以下操作;

从sheet1中删除不在sheet2中的地址 .

将sheet2中不在sheet1中的地址添加到sheet1的末尾 .

我最初的意图是循环，但由于每张纸上大约有10000个地址，因此显然效率不高 .

1 回答

0
是的有 - Microsoft Query (SQL in Excel).

你可以从 Data->From Other Sources->Microsoft Query 访问它

SQL在比较/合并/更新多个数据源方面非常出色 . 请参阅我的blog post here的此部分 . Microsoft Query也将比任何VBA宏快得多 .

您的问题的一个示例：

Reduce first Worksheet to items that are in both Worksheets
```
SELECT * FROM [Sheet1$] AS S1 INNER JOIN [Sheet2$] AS S2 on S1.Address=S2.Address
```
Select new addresses
```
SELECT S2.Address FROM [Sheet2$] AS S2 LEFT JOIN [Sheet1$] AS S1 on S1.Address=S2.Address
WHERE IsNull(S1.Address)
```
Join both
```
SELECT * FROM [Sheet1$] AS S1 INNER JOIN [Sheet2$] AS S2 on S1.Address=S2.Address
UNION ALL
SELECT S2.Address FROM [Sheet2$] AS S2 LEFT JOIN [Sheet1$] AS S1 on S1.Address=S2.Address
WHERE IsNull(S1.Address)
```
回复于 2024-04-18T23:08:35+08:00

相关问题