☰

如何在 Excel 中自动生成一个表中存在而另一个表中缺少的行的列表？

tag-icon • tag-icon microsoft-excel
如何在 Excel 中自动生成一个表中存在而另一个表中缺少的行的列表？

在我的场景中，我从外部系统获取数据，然后在 Excel 中使用该数据进行扩充。源系统会定期更新，因此我将源数据分开，并使用 vlookup 映射字段以供参考。

源数据也有新条目，

我想要的是让 excel 以某种方式生成工作表中没有的所有新条目的列表。并且无需将任何内容复制粘贴到外部工作表中即可完成此操作。

我非常确定我可以使用 power query 或一些新的 excel 功能（例如数组）来做到这一点，但想要一些指针。

工作台

ID 类型 (Lkup) 颜色 (Lkup) 增强数据
ID1 宠物猫 蓝色的 喜欢盒子
ID2 宠物狗 绿色的 嚼球

源表

ID 类型 颜色
ID1 宠物猫 蓝色的
ID2 宠物狗 绿色的
ID3 宠物金丝雀 黄色的

差异表

ID 类型 颜色
ID3 宠物金丝雀 黄色的

那么，如何在 Excel 中自动生成 Diff 表？编辑：请注意，在示例中，ID 后只有一列，而在实际数据中有多个表。

答案1

Excel 365/2021

=FILTER(range, ISNA(VLOOKUP(source, working, 1, FALSE)))
  • 如果 VLOOKUP 未能从中1找到查找值（VLOOKUP 将返回）或者如果 VLOOKUP 成功，ISNA 将用 填充数组的每一行。sourceworking#N/A0
  • 然后range根据该 1 和 0 数组进行过滤。
  • 目标过滤器range可以包含比更多的列，source但它们必须具有匹配的行数。
  • 公式不会告诉你相反的情况（值在working但不在source）
  • 公式不比较ID
# range E2:F4        # source (F2:F4)  # working (B2:B3)
{"ID1","Pet Cat";    {"Pet Cat";       {"Pet Cat";
 "ID2","Pet Dog";     "Pet Dog";        "Pet Dog"}
 "ID3","Pet Canary"}  "Pet Canary"}  


=FILTER(range,ISNA(VLOOKUP(F2:F4,working,1,FALSE)))

=FILTER({"ID1","Pet Cat"; "ID2","Pet Dog"; "ID3","Pet Canary"},
   ISNA(VLOOKUP(
      {"Pet Cat"; "Pet Dog"; "Pet Canary"},
      {"Pet Cat"; "Pet Dog"},
      1, FALSE)))

=FILTER({"ID1","Pet Cat"; "ID2","Pet Dog"; "ID3","Pet Canary"},
        {0; 0; 1})

={"ID3","Pet Canary"}

答案2

我一直使用 Power Query 来执行此操作。

用一个查询来引用您的工作表，用另一个查询来引用源数据。在我的情况下，通常在单独的 CSV 文件中，下载到已知文件夹，然后 PQ 只需从文件夹中的最新 CSV 中获取数据，其中名称具有一些可识别的模式（例如以“源数据”开头）。

然后使用“合并”命令在 ID 列上创建一个新表作为这些表之间的连接，并选择连接类型“反连接”（左或右取决于您选择表的方式）。我会先选择源，这样它就“在左边”，因为这是您想要的结果数据。丢弃合并操作中的额外列。整理所有数据类型，删除任何不需要的列，按要求排序，然后加载数据以仅查看源中但不在工作表中的行。

适当格式化新表格（白色文本放在深红色填充的标题上，因为这是例外表格？）。我喜欢在工作页面上添加条件行数，这样可以吸引人们的注意，告诉他们“有 X 行新数据需要查看”

如果您需要更多“分步说明”，请告诉我。谷歌搜索 Power Query Anti-join 可能会产生不错的结果。

相关内容