Excel中SUMPRODUCT与OFFSET动态公式求和范围异常问题求助

问题根源 #

你当前公式里的MATCH(1,P:P,0)会返回整个P列中第一个值为1的行号（比如表头在第1行时，第一个1在第2行，就始终返回2），无法定位当前行所在分组的起始位置，导致SUMPRODUCT只能计算固定范围，而非当前组从起始到当前行的区间。

解决方案 #

需要先定位当前行所在分组的起始行（即当前行上方最后一个P值为1的行，若当前行P值为1则起始行就是自身），再基于这个起始行计算区间内的SUMPRODUCT。

通用公式（兼容多数Excel版本） #

=SUMPRODUCT(OFFSET(W$1,LOOKUP(2,1/(P$1:P1=1),ROW(P$1:P1))-1,0,ROW()-LOOKUP(2,1/(P$1:P1=1),ROW(P$1:P1))+1),OFFSET(AK$1,LOOKUP(2,1/(P$1:P1=1),ROW(P$1:P1))-1,0,ROW()-LOOKUP(2,1/(P$1:P1=1),ROW(P$1:P1))+1))/AK2

简化版（Excel 365/2021及以后，用LET减少重复计算） #

=LET(
    start_row, LOOKUP(2,1/(P$1:P1=1),ROW(P$1:P1)),
    SUMPRODUCT(INDEX(W:W,start_row):W2,INDEX(AK:AK,start_row):AK2)/AK2
)

公式说明 #

1. LOOKUP(2,1/(P$1:P1=1),ROW(P$1:P1))：找到当前行上方最后一个P值为1的行号，作为分组起始行。原理是1/(P$1:P1=1)会在P值为1的位置生成1，其他位置生成错误值，LOOKUP会忽略错误值并找到最后一个1对应的行号。
2. INDEX(W:W,start_row):W2：取分组起始行到当前行的W列数据范围（替代易失的OFFSET，计算更稳定）。
3. SUMPRODUCT：计算该区间内W列与AK列对应值的乘积之和，再除以当前行的AK值，符合你“每组从起始到当前行计算，组末时计算完整区间”的需求。

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