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

爱必应

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

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

 

【公式设计与解析】

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列(浅绿色)上按右键,新增一列即可复制公式并新增内容,但是公式中的单元格范围会自动调整,使用者不同再更改。

image

本文链接:,转发请注明来源!
VIP视频在线接解析

发表评论

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