尝试比较两个Excel报表的标题更改 [英] Trying to compare two Excel reports for heading changes

问题描述

我使用 Excel 2010 ，需要将上一个报表中的报表列标题与最新报告进行比较，以确定报表布局中的更改（有100个列，并且报告更改周期性发生） ）

现在，我将新的报告立即粘贴到旧报告的下面，并附上相关数据，然后隐藏旧报告中的数据，以便我可以看到只需在旧报告和新报告中的列标题，然后对两行标题数据进行手动比较，以确保它们没有更改。

是否有感谢您的帮助。

解决方案

p>您可以使用ARRAY FORMULA来比较两个范围。

  = AND（Sheet1！A1：ZZ1 = Sheet2！A1：ZZ1）
  

，并按住Ctrl键，进入enter。如果两个范围内的所有单元都相同，则返回TRUE，否则返回FALSE。 （简体 - 感谢@pnuts！）

如果您想知道WHAT单元格不匹配，可以使用条件格式：select 所有单元格在您要检查的标题行中，然后

1. 单击条件格式设置 - >新建规则 - >使用一个公式，以确定要格式化哪些单元格


2. 在公式框中，键入 = Sheet1！A1& Sheet2！A1 （注意 - 如果您单击单元格以选择它们，Excel将添加 $ 符号。您必须删除这些或这不会工作选择要匹配的格式，例如格式 - >填充红色背景）


3. 点击各种确定按钮关闭对话框

现在所有不匹配的标题将是红色 - 容易看到，不需要仔细的逐个细胞比较。

I work with Excel 2010 and need to compare the report column headings in the previous report to the latest report to identify changes in the report layout (there are 100+ columns and and report changes occur periodically).

Right now I paste the new report immediately below the old report with its associated data and then hide the data in the old report so that I can see just the column headings in both the old and new reports and then do a manual eyeball compare of the two rows of heading data to make sure they have not changed.

Is there an easier way to do it?

Thanks for your help.

解决方案

You can use an ARRAY FORMULA to compare two ranges.

=AND(Sheet1!A1:ZZ1=Sheet2!A1:ZZ1)

and hold down ctrl-shift as you hit "enter". If all cells in the two ranges are the same, it will return "TRUE" - otherwise it will return "FALSE". (Simplified - thanks to @pnuts!)

If you want to know WHAT cells are not matching, you could use conditional formatting: select all the cells in the header row that you want to check, then

1. Click "conditional formatting"-->New Rule-->"Use a formula to determine which cells to format"
2. In the formula box, type =Sheet1!A1 <> Sheet2!A1 (note - if you click on cells to select them, Excel will add $ signs. YOU MUST REMOVE THOSE or this will not work properly for all but the first cell in the header)
3. Select formatting you want for "non-matching" cells (e.g. Format-->Fill "red background")
4. Click on various "OK" buttons to dismiss the dialogs

Now all the mismatched headings will be red - easy to see, no need for careful cell-by-cell comparison.

这篇关于尝试比较两个Excel报表的标题更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！