如何基于同一标识符跨多工作表高效返回多行多值

Excel 端方案 #

Excel 365/2021及以上版本 #

• 优先使用FILTER函数，可直接按条件筛选全部匹配项，自带数组溢出能力无需拖拽公式，计算效率远高于传统嵌套函数组合。
  公式示例：假设报表页A列为唯一标识符，需拉取Sheet2中A列匹配的所有B列值，在报表页B2输入如下公式即可自动返回所有匹配结果：
  =FILTER(Sheet2!$B:$B, Sheet2!$A:$A=$A2, "无匹配数据")
  如需横向返回多个匹配值到不同列，嵌套TRANSPOSE即可：
  =TRANSPOSE(FILTER(Sheet2!$B:$B, Sheet2!$A:$A=$A2, "无匹配数据"))
• 性能优化提示：不要直接引用整列，将范围缩小到实际数据区域（例如Sheet2!$A$1:$A$6000而非Sheet2!$A:$A），数千行场景下几乎无计算延迟。

Excel 2016版本 #

• 无原生FILTER函数，推荐使用INDEX+AGGREGATE组合，比传统INDEX+SMALL+IF组合容错性更高、计算速度更快，无需按Ctrl+Shift+Enter触发数组计算。
  公式示例：横向返回匹配值，在报表页B2输入：
  =IFERROR(INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$A:$A)/(Sheet2!$A:$A=$A2),COLUMN(A1))),"")
  公式向右拖拽即可依次返回第2、第3个匹配值，向下拖拽即可适配所有唯一标识符。

Google Sheets 端方案 #

• 原生支持FILTER函数，用法和Excel 365完全一致，支持数组自动溢出，适配数千行数据无压力。
• 跨多工作表汇总的复杂场景可使用QUERY函数，批量拉取多列匹配值效率更高：
  示例公式，拉取Sheet2中A列匹配A2的所有B、C列数据：
  =QUERY(Sheet2!$A:$C, "SELECT B,C WHERE A = '"&$A2&"'", 1)

通用性能优化技巧 #

• 所有数据源的匹配列优先做升序排序，查找效率可提升30%以上
• 数据量超过1万行时，建议将静态数据源转换为超级表（Excel）或命名区域（Google Sheets），引用范围可自动适配数据增减，同时提升公式计算效率
• 避免在同一工作表内使用数百个以上的数组类公式，计算完成后可将固定结果粘贴为值，减少实时计算压力

内容的提问来源于stack exchange，提问作者CS Grant