查找两个 Excel 文件之间的更改(新行、删除的行、更新的行)

查找两个 Excel 文件之间的更改(新行、删除的行、更新的行)

oldSheet我们有一个包含两张表(和)的 Excel 文件,newSheet它们都包含以下列(A:“条形码”,B:“价格”,C:“折扣”)

newSheet是的更新版本,oldSheet这意味着已添加新行/产品(新条形码),更新了某些条形码的价格和/或折扣,删除了行/条形码(barcode在新工作表中找不到旧工作表)。

我想创建第三张表,名为输出表它结合了 oldSheet 和 newSheet,同时根据 oldSheet 中的条形码突出显示已删除的行红色的,已添加到 newSheet 的新条形码绿色的,修改后的行(oldSheet 和 newSheet 中的条形码相同),但pricediscount列已被修改黄色的

由于我的帐户是新帐户,我无法发布图片,因此我添加了链接

旧表: oldSheet截图

新表: newSheet截图

outputSheet 想要像这样的东西: 输出SheetScreenshot

红色行:在 中找到oldSheet但不在 中的条形码newSheet

黄色行:两张表上都有条形码,但价格或折扣值不同。

绿色行: 已添加到 的新条形码newSheet,并且 中不存在oldSheet

只要显示添加、删除和修改的行/产品,顺序并不重要

答案1

使用“数据”>“获取和转换数据”>“从表/范围”在两个表上创建查询。

我将它们命名为“旧”和“新”,以便在 Power Query 编辑器中看到以下内容:

在此处输入图片描述

现在在 Power Query 中,使用“主页”>“合并”>“合并查询”>“合并查询为新查询”并按如下方式配置:

在此处输入图片描述

然后,点击“新建”列右上角的双箭头:

在此处输入图片描述

接受默认值,例如:

在此处输入图片描述

现在使用添加列>自定义列,如下所示: 在此处输入图片描述

公式如下:

Replacer.ReplaceValue([barcode],null,[new.barcode])

现在选择 barcode 和 new.barcode,右键单击并删除这些列。将 barcode.1 重命名为 barcode 并将该列拖到最左侧。

使用以下公式添加一个名为“status”的新自定义列:

if [price] = null then "New" else 
if [new.price] = null then "Removed" else
if [new.price]<>[price] or [new.quantity]<>[quantity] then 
"Updated" else "No change"

现在删除价格和数量列(选择、右键单击、删除),然后将 new.price 和 new.quantity 分别重命名为价格和数量。

此时你应该有这个:

在此处输入图片描述

使用“主页”>“关闭并加载”将数据放回工作簿。

使用表格设计>表格样式将查询结果的表格样式更改为“无”。

选择所有数据行:

在此处输入图片描述

使用主页>样式>条件格式>新规则>使用公式确定要格式化的单元格,为状态列中的每个有趣的值创建三个规则:

在此处输入图片描述

特别注意单元格引用中的 $。

每次似乎都要经历很多步骤...但是,如果您保存此查询并确保在收到更新时“新”数据和“旧”数据始终位于相同的命名表中,则您只需刷新此查询即可查看哪些行是新的,删除的或更改的。

如果我遗漏了任何内容,请适当地编辑/插入步骤到查询中。

相关内容