Google Sheets需求：提取唯一名称及对应ID（含无ID场景）

需求说明 #

• 返回每个名称-ID组合的唯一实例
• 若名称无对应ID，仅保留该名称的唯一实例
• 若名称重复但ID不同，保留每个名称与不同ID的组合
• 禁止使用辅助列

当前使用的公式无法提取无ID的唯一名称：

=UNIQUE(QUERY({'Mar 2017'!A5:B;'Apr 2017'!A5:B;'May 2017'!A5:B;'Jun 2017'!A5:B}, "SELECT Col1, Col2 WHERE Col2 IS NOT NULL ORDER BY Col1 ASC"))

解决方案 #

通过合并两类查询结果，同时覆盖有ID的组合和无ID的名称，再完成去重排序：

=UNIQUE(SORT({
  QUERY({'Mar 2017'!A5:B;'Apr 2017'!A5:B;'May 2017'!A5:B;'Jun 2017'!A5:B}, "SELECT Col1, Col2 WHERE Col2 IS NOT NULL"),
  QUERY({'Mar 2017'!A5:B;'Apr 2017'!A5:B;'May 2017'!A5:B;'Jun 2017'!A5:B}, "SELECT Col1, '' WHERE Col2 IS NULL")
}, 1, TRUE))

公式解析 #

• 第一个QUERY：筛选并提取所有存在ID的名称-ID组合
• 第二个QUERY：筛选出无ID的名称，将B列设为空字符串，保证与第一部分结果的列数统一
• {}：将两个查询结果合并为一个二维数组
• SORT：按名称（第一列）升序排列最终结果
• UNIQUE：去除重复项，确保无ID的名称仅保留唯一实例，有ID的组合也只保留唯一匹配

内容的提问来源于stack exchange，提问作者David J. Myers