![如何比较两个 Excel 电子表格?](https://linux22.com/image/1551718/%E5%A6%82%E4%BD%95%E6%AF%94%E8%BE%83%E4%B8%A4%E4%B8%AA%20Excel%20%E7%94%B5%E5%AD%90%E8%A1%A8%E6%A0%BC%EF%BC%9F.png)
我想比较两个 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
:
请注意,安迪 (Andy) 的行是橙色,因为他从纽约搬到了洛杉矶;而黛布拉 (Debra) 的行也是橙色,因为她是新来的。
笔记: 如果一行可能在两个连续的列中有像the
和这样的值react
,并且这可能会在下一年变为 和there
,act
则不会将其报告为差异,因为我们只是比较连接的值,并且 ( thereact
) 在两个工作表上是相同的。如果您对此感到担心,请选择一个不太可能出现在您的数据中的字符(例如|
),并将其插入字段之间。因此您的辅助列将包含
=B2&"|"&C2&"|"&D2&"|"&…&"|"&X2&"|"&Y2&"|"&Z2&"|"&AA2&"|"&AB2&"|"&AC3&"|"&…&"|"&AX2
产生的数据可能如下所示:
安迪|纽约|华尔街 1342 号|投资银行家|伊丽莎白|2|猫|大学学位|加州大学洛杉矶分校|…
并且会报告更改,因为the|react
≠ there|act
。您可能应该担心这一点,但是,根据您的列的实际情况,您可能有理由相信这永远不会成为问题。
一旦完成此操作,您就可以隐藏辅助列。
答案2
转到“视图”选项卡,单击“新窗口”。转到新窗口,然后单击“并排”。您可以通过单击“同步滚动”来同步滚动。查看附图:
同步滚动功能可让您同时滚动不同的工作表。切换按钮可让您同时查看两个工作表,并且仅滚动一个工作表。
答案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”来自另一个表。
干杯