Excel中SUMPRODUCT函数单元格引用问题：横向复制行号递减

核心思路 #

横向复制公式时，Excel会自动让列引用递增（比如D→E→F），但我们需要让每右移1列，所有行号都减1。不用纠结INDIRECT（这个函数是文本转引用，嵌套起来容易出错还拖速度），直接用动态引用函数把行号和列的偏移量绑定就行。

方案一：用OFFSET快速实现（新手友好） #

假设你初始公式放在F34单元格，把公式改成下面这样，然后直接向右拉就行：

=IFERROR(SUMPRODUCT(OFFSET(AY34:AY44,-(COLUMN()-COLUMN(F34)),COLUMN()-COLUMN(F34)),OFFSET(D34:D44,-(COLUMN()-COLUMN(F34)),COLUMN()-COLUMN(F34)))/SUM(OFFSET(D34:D44,-(COLUMN()-COLUMN(F34)),COLUMN()-COLUMN(F34))),1)

人话解释： #

• COLUMN()：获取当前单元格的列号，比如F列是6，拉到G列就变成7
• COLUMN()-COLUMN(F34)：计算当前单元格相对于初始单元格的偏移列数——初始是0，右移1列就是1，再右移就是2
• OFFSET(原区域, 行偏移, 列偏移)：
  • 行偏移填负的偏移列数：每右移1列，就把原区域向上挪1行（负数代表向上）
  • 列偏移填偏移列数：每右移1列，就把原区域向右挪1列（正数代表向右），刚好和Excel自动变列的逻辑匹配

方案二：用INDEX更高效（非易失性，不卡表） #

如果你的表格数据多，用OFFSET会让Excel变慢，推荐用INDEX，公式如下（同样假设初始单元格是F34）：

=IFERROR(SUMPRODUCT(INDEX($AY:$AY,34-(COLUMN()-COLUMN(F34))):INDEX($AY:$AY,44-(COLUMN()-COLUMN(F34))),INDEX($D:$D,34-(COLUMN()-COLUMN(F34))):INDEX($D:$D,44-(COLUMN()-COLUMN(F34))))/SUM(INDEX($D:$D,34-(COLUMN()-COLUMN(F34))):INDEX($D:$D,44-(COLUMN()-COLUMN(F34)))),1)

人话解释： #

• INDEX($AY:$AY, 行号)：精准定位AY列的某一行
• 34-(COLUMN()-COLUMN(F34))：初始行号是34，每右移1列，行号就减1（因为偏移列数加1，34减去这个数就行）
• 用两个INDEX分别圈出区域的开头和结尾行，拼起来就是你要的动态范围，拉的时候列自动变、行自动减

为啥你之前的INDIRECT写法没用？ #

你写的SUMPRODUCT(INDIRECT("A1"):AY43,E33:E43)有两个问题：

1. INDIRECT("A1")返回的是AZ33单元格，和后面的AY43不是同一列，Excel认不出这种跨列的奇怪范围
2. 就算你写成INDIRECT("AZ33:AZ43")，列也没法自动跟着复制递增，不如上面的方法灵活

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