☰

如何比较两个 Excel 电子表格？

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet
如何比较两个 Excel 电子表格？

我想比较两个 Excel 电子表格

两者具有相同的列和顺序，但行不同。

我有一个有 1,000 行和 50 列的电子表格，其中包括每行的唯一 ID 和个人数据（地址、电话号码等）。

我想将它与一年前开采的相同基数进行比较。

我的目标是了解所有变化（例如，某人更改了地址）。

我尝试使用电子表格（如下所述：比较两个 excel 文件），但它不起作用，因为在我的“新”文件中我有新行，而旧文件中有一些行不在新文件中。

电子表格与 Excel 行号进行比较。是否可以与第一列（唯一 ID）进行比较？

这是一个非常简单的例子：https://filebin.net/g4w98251y9mfwug6

有两张表代表我的两个电子表格。

如您所见，两者都具有相同的列，但是：

  • 两张表上都有一些行，且没有变化（所有行都相同）
  • 两张表上都有行，但有变化（在我的示例中为橙色）
  • 有些行只存在于 12 月份的表中（我不需要识别这些行）
  • 有些行只出现在 1 月份的表中（在我的示例中为橙色）

橙色正是我想要的，但在这个例子中我可以手动完成：比较 12 月和 1 月。在实际电子表格中我无法手动完成，因为有太多更改、数据、行、列等，我将按月执行此操作。

只有一列永远不会改变：A列。

答案1

您的电子表格使用 50 列，这很方便，因为这意味着可以使用第 51、第 52 列……。使用“辅助列”可以相当轻松地解决您的问题，我们可以将其放入列 AZ（即第 52 列）。我假设您每张工作表的第 1 行包含标题（字 ID、、Name等Address），因此您无需比较它们（因为两张表中的列顺序相同）。我还假设ID（唯一标识符）位于列中 A。（如果不是，则答案变为小的有点复杂，但还是相当容易。）在单元格 AZ2（可用列，用于数据的第一行）中，输入

=B2&C2&D2&…&X2&Y2&Z2&AA2&AB2&AC3&…&AX2

B2列出从到 的 所有单元格 AX2。  &是文本连接运算符，因此如果B2包含Andy且C2包含New York，B2&C2则将计算为AndyNew York。同样，上述公式将连接全部一行的数据（不包括ID），给出的结果可能如下所示：

Andy纽约华尔街1342号投资银行家Elizabeth2cat大学学位加州大学洛杉矶分校…

该公式很长，输入起来很麻烦，但您只需输入一次（但在实际输入之前请参阅下面的注释）。我将其显示出来是AX2因为 Column AX是第 50 列。当然，公式应该涵盖除 之外的每个数据列ID。更具体地说，它应该包括您想要比较的每个数据列。如果您有一列用于人员年龄，那么该列对于每个人、每年都会（自动？）不同，并且您不希望报告该列。当然，包含连接公式的辅助列应该位于最后一个数据列的右侧某处。

现在选择单元格 AZ2，并将其向下拖动/填充至所有 1000 行。并在两个工作表上执行此操作。

最后，在您想要突出显示更改的工作表上（我猜，从您所说的内容来看，这是较新的工作表），选择您想要突出显示的所有单元格。我不知道这只是 Column  A，还是 Column  B，还是整行（即A直到 AX）。选择第 2 行到第 1000 行上的这些单元格（或您的数据最终可能到达的任何行），然后进入“条件格式”→“新规则...”，选择“使用公式确定要格式化的单元格”，然后输入

=IFERROR(VLOOKUP($A2,'December 2017'!$A$2:$AZ$1000,52,FALSE), "") <> $AZ2

进入“格式化此公式为真时的值框”。这ID将从当前（“2018 年 1 月”）工作表的当前行（在单元格中 $A2）获取值，在上一个（“2017 年 12 月”）工作表的列中搜索 A该值，从该行获取连接的数据值并将其与此行上的连接数据值进行比较。（当然AZ是辅助列， 52是列号辅助列，1000是“2017 年 12 月”工作表上包含数据的最后一行 - 或稍高一些；例如，您可以输入1200而不必担心是否准确。）然后单击“格式”并指定所需的条件格式（例如，橙色填充）。

我做了一个只有几行和几列数据的示例，其中 Column 中有辅助列 H：

  上月/上年（2017 年 12 月）表

  当前月份/年份（2018 年 1 月）表，其中的变化以橙色突出显示

请注意，安迪 (Andy) 的行是橙色，因为他从纽约搬到了洛杉矶；而黛布拉 (Debra) 的行也是橙色，因为她是新来的。

笔记：  如果一行可能在两个连续的列中有像the和这样的值react，并且这可能会在下一年变为 和there，act则不会将其报告为差异，因为我们只是比较连接的值，并且 ( thereact) 在两个工作表上是相同的。如果您对此感到担心，请选择一个不太可能出现在您的数据中的字符（例如|），并将其插入字段之间。因此您的辅助列将包含

=B2&"|"&C2&"|"&D2&"|"&…&"|"&X2&"|"&Y2&"|"&Z2&"|"&AA2&"|"&AB2&"|"&AC3&"|"&…&"|"&AX2

产生的数据可能如下所示：

安迪|纽约|华尔街 1342 号|投资银行家|伊丽莎白|2|猫|大学学位|加州大学洛杉矶分校|…

并且会报告更改，因为the|react ≠  there|act。您可能应该担心这一点，但是，根据您的列的实际情况，您可能有理由相信这永远不会成为问题。

一旦完成此操作，您就可以隐藏辅助列。

答案2

转到“视图”选项卡，单击“新窗口”。转到新窗口，然后单击“并排”。您可以通过单击“同步滚动”来同步滚动。查看附图： 并排显示 Excel 工作表

同步滚动功能可让您同时滚动不同的工作表。切换按钮可让您同时查看两个工作表，并且仅滚动一个工作表。

答案3

好的，那么您可以做以下两件事之一。

1.- OOTB 解决方案是转到“数据”->“数据工具”->“删除（这是西班牙语的直接翻译）重复项”。这样，您必须附加两个表，最终只会得到已更改的数据和唯一值。因此，要过滤掉CountIf您的唯一 ID 上的使用，请取消选择已计为 1 的内容。按唯一 ID 排序，您将清楚地看到已更改的内容。

2.-使用此公式

IF(Index("rangetoreturn";Match("CellofUniqueID";"Rangeofuniqueidtolookinto";0))<>"OneOftheCellthatcontainsthesameasRangeToReturn";"Info Has Changed";"InfoHasNotChanged")

此公式可以向右向下拖动。因此，您将得到一个包含 if 条件的表格，其行数与“CellofUniqueID”表格相同。“Info Has Changed”的位置将告诉您另一个表格中的哪个单元格已更改其内容。因此，在新工作表中使用此公式并构建一个新表格。然后查看过滤器并取消选择“InfoHasNotChanged”（或您想要使用的任何词）。

“rangetoreturn”和“Rangeofuniqueidtolookinto”来自一个表（或工作表），而“CellofUniqueID”来自另一个表。

干杯

相关内容