Excel公式练习75: 计算与上一次的差值

本次的练习是:一个有趣的问题,在一张简单的表中追踪每日体重的增加或减少量。如下图1所示,只需计算当天与前一天的差额即可,在单元格D6中使用公式:

=C6-C5

或者加一个空单元格判断条件:

=IF(C6<>"",C6-C5,"")

下拉至单元格D13。

图1

然而,如果有几天忘记输入体重值了,仍然使用上面的公式会发生什么情况呢?如下图2所示。

图2

在空单元格之后的单元格D10中,结果明显不对,而正确的结果应该如下图3所示。

图3

因此,我们需要一种方法,能够跳过空单元格,计算出正确的值。

你的公式必须能够:

1.在单元格D6中输入后,可以通过拖动向下复制。

2.处理一个或多个空单元格

3.不允许删除空单元格行

4.不允许使用辅助列

先不看答案,自已动手试一试。

解决方案

此种情形可以使用LOOKUP函数来查找最后一个非空值。

在单元格D6中输入公式:

=IF(C6<>"",C6-LOOKUP(2,1/($C$5:C5<>""),$C$5:C5),"")

下拉至单元格D13。

上述公式中,在单元格D10中的LOOKUP函数部分:

LOOKUP(2,1/($C$5:C9<>""),$C$5:C9)

转换为:

LOOKUP(2,1/({TRUE;TRUE;TRUE;FALSE;FALSE}),$C$5:C9)

转换为:

LOOKUP(2, {1;1;1;#DIV/0!;#DIV/0!},$C$5:C9)

得到单元格C7中的值:

154