SUMPRODUCT(ABS())嵌套INDIRECT实现员工组合计算时报#VALUE错误

计算目标 #

沿用SUMPRODUCT(ABS())计算差值绝对值总和的公式逻辑，加入INDIRECT函数，实现自动遍历所有员工两两组合、批量计算对应结果的目标。

表格基础结构 #

• 第2行存储全量员工名单
• A列存储和第2行完全相同的全量员工名单

报错现象 #

在B3单元格输入下列公式时，返回#VALUE错误：

=SUMPRODUCT(ABS(INDIRECT("Sheet1!J"&ROW(A3)-1&":BB"&ROW(A3)-1)-INDIRECT("Sheet1!J"&COLUMN(B2)&":BB"&COLUMN(B2))))

报错核心诱因 #

• 引用维度不对齐：两个INDIRECT生成的引用范围无法做逐元素减法运算。第一个引用是通过ROW(A3)-1计算行号，拼接出J列到BB列的单行横向区间；第二个引用拼接行号时误用了COLUMN(B2)返回的列序号，把列坐标的值直接当做行号使用，生成的引用范围和第一个引用的范围无法形成位置一一对应的匹配关系，直接触发值类型不匹配报错。
• 引用逻辑错位：原公式设计时，第二个INDIRECT本意是匹配第2行的员工表头区间，但实际拼接出来的行号是列的序号值，完全偏离了第2行的表头位置，无法和对应员工的数据形成匹配计算。

内容的提问来源于stack exchange，提问作者Ricky Drennan