查找单元格中的更改 - Excel 文件

发布于 2024-12-08 11:49:18 字数 251 浏览 0 评论 0原文

我有 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技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

风苍溪 2024-12-15 11:49:18

这可能不是有效的方式(尽管可以在几秒钟内处理 25k 个单元),但它的简单性足以弥补它的不足。

这将查看 Sheet2 中的每个单元格,并将其与指定文件的 Sheet1 中相同地址的单元格中的值进行比较。如果不同,Sheet2 中的单元格将突出显示为黄色。

Sub FindDifferences()

Application.ScreenUpdating = False
Dim cell As Range
Dim wkb1 As Workbook
Dim wks1 As Worksheet

Set wkb1 = Workbooks.Open(Filename:="C:\MyBook.xls")
Set wks1 = wkb1.Worksheets("Sheet1")

For Each cell In ThisWorkbook.Sheets("Sheet2").UsedRange
    If cell.Value <> wks1.Cells(cell.Row, cell.Column).Value Then
        cell.Interior.Color = vbYellow
    End If
Next

wkb1.Close
Application.ScreenUpdating = True
End Sub

注意
您可以轻松地对其进行定制,通过简单地删除 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.

Sub FindDifferences()

Application.ScreenUpdating = False
Dim cell As Range
Dim wkb1 As Workbook
Dim wks1 As Worksheet

Set wkb1 = Workbooks.Open(Filename:="C:\MyBook.xls")
Set wks1 = wkb1.Worksheets("Sheet1")

For Each cell In ThisWorkbook.Sheets("Sheet2").UsedRange
    If cell.Value <> wks1.Cells(cell.Row, cell.Column).Value Then
        cell.Interior.Color = vbYellow
    End If
Next

wkb1.Close
Application.ScreenUpdating = True
End Sub

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... to Sheets("Sheet1").Cells...

装纯掩盖桑 2024-12-15 11:49:18

您可以使用这个在线网站 - xlcomparator.net(点击右上角的标志查看英文版本) 。

或者尝试这个软件:http://www.formulasoft.com/excel-compare.html

或者尝试这种宏(检查第一列)并根据您的需要进行调整:

sub compare()
   Application.ScreenUpdating = False
   Dim coll1 As New Collection, coll2 As New Collection
   Dim cell1 As Range, cell2 As Range
   Dim Element1 As Object, Element2 As Object

 Workbooks("workbook1.xls").Activate
   For Each Cellule1 In Range("a:a")

  coll1.Add cell1
   Next Cellule1

 Workbooks("workbook2.xls").Activate
   For Each cell2 In Range("a:a")

  coll2.Add cell2
   Next cell2
   For Each Element1 In coll1
     For Each Element2 In coll2
      If Element1 <> Element2 Then
         Element1.Font.Color = vbRed
  Else
         Element1.Font.Color = vbBlack
         Exit For
      End If
     Next Element2
   Next Element1

 Application.ScreenUpdating = True
 end sub

来源 - 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:

sub compare()
   Application.ScreenUpdating = False
   Dim coll1 As New Collection, coll2 As New Collection
   Dim cell1 As Range, cell2 As Range
   Dim Element1 As Object, Element2 As Object

 Workbooks("workbook1.xls").Activate
   For Each Cellule1 In Range("a:a")

  coll1.Add cell1
   Next Cellule1

 Workbooks("workbook2.xls").Activate
   For Each cell2 In Range("a:a")

  coll2.Add cell2
   Next cell2
   For Each Element1 In coll1
     For Each Element2 In coll2
      If Element1 <> Element2 Then
         Element1.Font.Color = vbRed
  Else
         Element1.Font.Color = vbBlack
         Exit For
      End If
     Next Element2
   Next Element1

 Application.ScreenUpdating = True
 end sub

Source - excelabo, a french website

冷︶言冷语的世界 2024-12-15 11:49:18

另外两个选项:

  1. Spreadsheet Advantagehttp://www.spreadsheetadvantage.com/,您可以在其中获得 30 天的免费试用版

这是我最喜欢的工具,因为它还提供了行和列对齐选项,以确保在运行比较输出之前两张表按行和列呈现相同要突出显示的代码任何差异

  1. Myrna Lawson 的compare.xla 插件(免费)可在Chip Pearson 的网站上获取http://www.cpearson.com/Zips/Compare.zip

Two further options:

  1. Spreadsheet Advantage, http://www.spreadsheetadvantage.com/, where you can get a free 30 day trial

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

  1. Myrna Lawson's compare.xla addin (free) available at Chip Pearson's site http://www.cpearson.com/Zips/Compare.zip
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文