如何在Excel动态数组中自动计算对角线合计（固定7行）

需求说明 #

在固定7行、列数可变的动态数组中，计算「外协缺货（Subcontract out of Stock）」的每列值，规则如下：

• 每列值为当前列当前行数值 + 前一列下一行数值（存在则累加） + 前两列下两行数值（存在则累加） + … + 前6列下6行数值（存在则累加）
• 最终结果需取反

用户现有实现公式 #

以下是已实现需求的公式，通过MAKEARRAY配合OFFSET逐个计算每列的合计值（已补充需求要求的取反操作）：

=MAKEARRAY(1,COLUMNS(C130#),
   LAMBDA(r,c,
      - (OFFSET(C130,r-1,c-1)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),1,-1)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),2,-2)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),3,-3)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),4,-4)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),5,-5)),0)+
      IFERROR(N(OFFSET(OFFSET(C130,r-1,c-1),6,-6)),0))
   )
)

简化优化方案 #

可以用SUMPRODUCT结合序列运算简化公式，避免重复的OFFSET调用，同时保持对列数变化的自动适配：

=MAKEARRAY(1,COLUMNS(C130#),LAMBDA(r,c,-SUMPRODUCT(N(OFFSET(C130,r-1,c-1,7-SEQUENCE(7),-SEQUENCE(7,1,0,-1))),--(SEQUENCE(7)<=c))))

核心逻辑说明：

• SEQUENCE(7)生成1到7的序列，用来控制偏移的行数和列数
• OFFSET函数生成对应偏移方向的单元格区域
• --(SEQUENCE(7)<=c)过滤掉超出当前列数的无效偏移项
• SUMPRODUCT完成累加后取反，得到最终结果

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