oldSheet
我们有一个包含两张表(和)的 Excel 文件,newSheet
它们都包含以下列(A:“条形码”,B:“价格”,C:“折扣”)。
这newSheet
是的更新版本,oldSheet
这意味着已添加新行/产品(新条形码),更新了某些条形码的价格和/或折扣,删除了行/条形码(barcode
在新工作表中找不到旧工作表)。
我想创建第三张表,名为输出表它结合了 oldSheet 和 newSheet,同时根据 oldSheet 中的条形码突出显示已删除的行红色的,已添加到 newSheet 的新条形码绿色的,修改后的行(oldSheet 和 newSheet 中的条形码相同),但price
或discount
列已被修改黄色的
由于我的帐户是新帐户,我无法发布图片,因此我添加了链接
旧表: 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 分别重命名为价格和数量。
此时你应该有这个:
使用“主页”>“关闭并加载”将数据放回工作簿。
使用表格设计>表格样式将查询结果的表格样式更改为“无”。
选择所有数据行:
使用主页>样式>条件格式>新规则>使用公式确定要格式化的单元格,为状态列中的每个有趣的值创建三个规则:
特别注意单元格引用中的 $。
每次似乎都要经历很多步骤...但是,如果您保存此查询并确保在收到更新时“新”数据和“旧”数据始终位于相同的命名表中,则您只需刷新此查询即可查看哪些行是新的,删除的或更改的。
如果我遗漏了任何内容,请适当地编辑/插入步骤到查询中。