一个 Excel 函数实例

一个 Excel 函数实例

最近遇到一个需求，有和下面类似的一张表，最左列的序号不管是否进行记录都会更新，右侧的数据只有我统计的时候才会更新，但是每次我统计之后都需要计算比上一次统计时的数值增加了多少，最简单的方法当然是=单元格-单元格这种方式，但是有一个问题，这些公式毫无规律可循，很多时候并不能直接填充，不能一条公式拉到底，作为一个懒人，这实在是……

Google 了一下，获取行或列中最后一个非空单元格数值，可以用这样一条公式：LOOKUP(9E+307,A1:A100)（9E+307一般用来Excel中最大的数值），于是小改一下，再加上求增加了多少（注意这个是放在 B3 单元格对应的单元格里的）：

B3-LOOKUP(9E+307,B$1:B2)

下拉之后就出现了第二个问题，有一些没有做统计的单元格，也会出现有数值的情况（如上图），同时新增合计那边一直有数值，这个时候需要再加个 IF （注意这个是放在 B3 单元格对应的单元格里的）：

IF(B3<>"",B3-LOOKUP(9E+307,$B$1:B2),"")

然后新增合计列有出现了新的问题，之前使用的是=C3+E3这个样子的公式，但是由于前文中给出的公式中在未进行统计的行的运算结果是空，用单元格+单元格的方式会报错，于是改用 SUM

SUM(C3,E3)

之后就剩下两个问题了，自动获取最后更新时间并且填写在 I1 单元格，最后更新时间就是最后添加的数据在 A 列的对应日期，首先要确定 B 列最后一个有数值的单元格的位置，下面这个公式可以确定 B 列最后一个单元格的行数（样表输出数值为 15）

MATCH(9E+307,B:B)

之后我们需要将 A 列这个列值和 15 这个行数合并在一起，这个时候用 INDIRECT：

INDIRECT("A"&MATCH(9E+307,B:B))

当然你也需要将 I1 单元格格式设置成日期，和 A 列一样。至于 I2 的更新间隔，可以用上面提到的几个函数组合一下

MATCH(E9+307,B:B)-MATCH(E9+307,B$1:INDIRECT("B"&(MATCH(E10+307,B:B)-1)))-1&"天"

稍微解释一下：

• MATCH(E9+307,B:B)：获取 B 列最后一个非空单元格的行值
• MATCH(E10+307,B:B)-1：获取 B 列最后一个非空单元格的上一行的行值
• INDIRECT("B"&(MATCH(E10+307,B:B)-1))：用 INDIRECT 构成 B 列最后一个非空单元格的上一个单元格的位置
• MATCH(E9+307,B$1:INDIRECT("B"&(MATCH(E10+307,B:B)-1)))：获取 B 列最后一个非空单元格上的最后一个单元格，即 B 列倒数第二个非空单元格的行值
• 最后就是做一个减法而已，不用说了

最后放一个 GIF 测试一下效果，注意右边的时间和更新间隔会自动更新。