☰

如何比较两列中匹配和不匹配的值并以我想要的格式获取它？

tag-icon • tag-icon microsoft-excel worksheet-function vba power-query
如何比较两列中匹配和不匹配的值并以我想要的格式获取它？

如何找到两列之间匹配和不匹配的值并按照以下方式格式化？

输入：

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        |       |         |          | b      |
| b        |       |         |          | c      |
| c        |       |         |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

输出：

| expected | match | forward | backward | actual |
|----------|-------|---------|----------|--------|
| a        | b     | a       | r        | b      |
| b        | c     | d       | s        | c      |
| c        |       | e       |          | r      |
| d        |       |         |          | s      |
| e        |       |         |          |        |

forward- 出现在expected但不出现在actual（SQL left outer join）

backward- 出现在actual但不出现在expected（SQL right outer join）

expected是我从SQL查询中得到的结果。在很多情况下，我都没有actualRDBMS 中的列，因此我必须使用 excel 进行比较。我通常可以使用它进行比较，VLOOKUP但它很耗时，而且它不会提供我想要的格式。我想要一个可以完成的解决方案重要的格式如上。

我愿意接受建议。

答案1

布局

假设您的信息如上所示，您可以在 C3、D3 和 E3 中分别使用以下三个公式：

C3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($B$3:$B$7,$F$3:$F$7)>0),ROW(A1))),"")

D3
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$3:$B$7)/(COUNTIF($C$3:$C$7,$B$3:$B$7)=0),ROW(A1))),"")

E3
=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$3:$F$7)/((COUNTIF($C$3:$C$7,$F$3:$F$7)=0)*($F$3:$F$7<>"")),ROW(A1))),"")

将公式复制到第 7 行，您将得到以下内容：

波克

注意： AGGREGATE对函数 15 执行类似数组的计算。因此，不要在函数中使用完整的列引用AGGREGATE。将其限制在接近数据集的范围。否则，您的计算机可能会因对空白单元格进行大量计算而陷入瘫痪。

答案2

对于 Power Query，您的关键转换将是合并查询。它类似于 SQL 连接并使用类似的术语。第一步是为预期的和实际的列。对于匹配我将使用 Join Type = Inner 构建查询。对于向前列表我将使用 Join Type = Left Anti 构建另一个查询，然后再构建一个落后使用右反对。

按照您呈现的方式将所有内容整合在一起会有点麻烦（如果真的有必要的话），但我想您可以向每个查询添加一个索引，并在索引列上使用合并连接（连接类型=左外部）以从每行的每个查询中获取单个结果。

相关内容