Excel-在持续新增的资料中找出各项最后一个数值(SUMPRODUCT,OFFSET)

在 Excel 中输入资料时（参考下图），常常会需要不断的增加资料，公式该如何处理？而在下图的清单里，其中有多个料号，每个料号有各自的前期剩余，如何计算每个料号的本期剩余？

【公式设计与解析】

1. 计算前期剩余

单元格B7：

=OFFSET($E$1,SUMPRODUCT(MAX(($A$2:A6=A7)*ROW($E$2:E6)))-1,0)

复制单元格B7，贴至单元格B7:B29。

(1) $A$2:A6=A7

在 SUMPRODUCT 函数中找出符合条件的阵列（由单元格A2起始至单元格A6的范围中和单元格A7相同者），传回 TRUE／FALSE 阵列。

(2) ($A$2:A6=A7)*ROW($E$2:E6)

利用 ROW 函数传回单元格E2:E6中每个单元格的列号（本例：2～6），运算子「*」在计算过程中，相当于执行逻辑 AND 运算，并且会将 TRUE／FALSE 阵列转换为 1／0 阵列。

(3) MAX(($A$2:A6=A7)*ROW($E$2:E6))

在 SUMPRODCUT 函数中，利用 MAX 函数找出第(2)式传回的运算结果之最大值，即为最大的列号，也就是符合料号的最后一个单元格。

(4) OFFSET($E$1,SUMPRODUCT(第(3)式)-1,0)

将第(3)式传回的列号代入 OFFSET 函数中传回对应的单元格内容。

2. 计算本期剩余

单元格H2：

=OFFSET($E$1,SUMPRODUCT(MAX(($A$2:$A$29=G2)*ROW($E$2:$E$29)))
-1,0)

复制单元格H2，贴至单元格H2:H6。

公式运算原理同「1. 计算前期剩余」。注意其单元格范围的差异！

3. 持续新增资料但不改公式

因为该资料清单会不断的新增，所以只要在第30列(浅绿色)上按右键，新增一列即可复制公式并新增内容，但是公式中的单元格范围会自动调整，使用者不同再更改。