查找单元格中的更改 - Excel 文件
我有 2 个 Excel 文件,每个文件都有很多数据。两个文件中的数据结构完全相同,但由于数据来自两个不同时间,因此值可能已更改。
基本上我想找到某种方法来自动比较两个文件的每个单元格中的值,并突出显示文件 #2 中已更改值的单元格。
请分享您的想法!
示例:
文件 1 :
a / 1 / 2
文件 2 :
a / 1 / 8
(/ - 表示新单元格)
I have 2 excel files with a lot of data in each. The data is structured exactly the same in both files but the values might have changed as the data is from two different times.
Basically I want to find some way to automatically compare values in each cell for the two files and highlight the cells that have changed values in file #2.
Kindly share your ideas!
Example:
File 1 :
a / 1 / 2
File 2 :
a / 1 / 8
(/ - indicates new cell)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
这可能不是最有效的方式(尽管可以在几秒钟内处理 25k 个单元),但它的简单性足以弥补它的不足。
这将查看 Sheet2 中的每个单元格,并将其与指定文件的 Sheet1 中相同地址的单元格中的值进行比较。如果不同,Sheet2 中的单元格将突出显示为黄色。
注意:
您可以轻松地对其进行定制,通过简单地删除 wkb1 和 wks1 变量并将
wks1.Cells...
更改为Sheets("Sheet1").Cells
来比较同一文件中的 2 个工作表。代码>...This may not be the most efficient way (can handle 25k cells in a few seconds, though), but it more than makes up for it in simplicity.
This will look at every cell in Sheet2 and compare it against the value in the cell at the same address in Sheet1 of the file you specify. If it's different, the cell in Sheet2 is highlighted yellow.
Note:
You could easily tailor this to compare 2 sheets in the same file by simple removing the wkb1 and wks1 variables and changing
wks1.Cells...
toSheets("Sheet1").Cells
...您可以使用这个在线网站 - xlcomparator.net(点击右上角的标志查看英文版本) 。
或者尝试这个软件:http://www.formulasoft.com/excel-compare.html
或者尝试这种宏(检查第一列)并根据您的需要进行调整:
来源 - excelabo,一个法国人网站
You can use this online website - xlcomparator.net (click on the flag on the top right for an english version).
Or try this software: http://www.formulasoft.com/excel-compare.html
Or try this kind of macro (that check the first column) and adapt it to your needs:
Source - excelabo, a french website
另外两个选项:
这是我最喜欢的工具,因为它还提供了行和列对齐选项,以确保在运行比较输出之前两张表按行和列呈现相同要突出显示的代码任何差异
Two further options:
This is my favourite tool as it also offers a row and column alignment option to ensure both sheets are presented indentically by row and column, before running the compare outputs code to highlight any differences