Excel动态求和之（一） _ 瞿四美的专家文章

专家文章

总裁汇——股权投融资平台

免费注册立即体验

Excel动态求和之（一） 2009-06-08

标签： Excel动态求和

　　在用SUM函数求和时，引用的区域一般是从现有数据的首行到尾行，如下图：

J6的公式是“=SUM(J7:J11)”，这是我们通常情况下设置的求和公式，其本身没有问题。但是，当我们在第12行添加数据时，问题就出现了：必须人工修改公式中最后一行的行标，将“11”改为“12”，即=SUM(J7:J12)。实际工作中这种操作非常繁琐，有没有一种更“智能化”的函数，当B12有数据录入时，求和公式即自行调整最后一行的行标，而无需人工修改公式呢？答案是肯定的。

试试把J6的公式改为“=SUM(OFFSET(J7,,,COUNTA($B:$B),))”，然后在B12中输入“通讯费”、在J12中输入“10000”，看看J6的结果是否已自动加上第12行的数据了？

公式中用到了OFFSET函数，EXCEL帮助中对该函数的解释是：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。

该函数的用法：OFFSET(reference,rows,cols,height,width)。OFFSET函数有5个参数，其含义分别是：

Reference是偏移量参照系的引用区域，即参照坐标位置，必须为对单元格或相连单元格区域的引用，不可省略，否则，返回错误值 #VALUE!。本公式中，是以J7为参照坐标，从J7开始计算偏移量。

Rows 相对于偏移量参照系（参照坐标位置）上（下）偏移的行数。正数表示向下偏移的行数，负数表示向上偏移的行数。比如本例中，OFFSET(J7,2,)表示以J7为起点，向下偏移2行，即偏移到J9，其结果便是J9的值。但是，本例的需求是，从J7开始往下求整个列区域的和，而不是求某单个单元格的和，因此无需使用Rows参数，忽略。

Cols 相对于偏移量参照系（参照坐标位置）左（右）偏移的行数。正数表示向右偏移的列数，负数表示向左偏移的列数。比如本例中，OFFSET(J7,,2)表示以J7为起点，向右偏移2列，即偏移到L7，其结果便是L7的值。同样，本例也无需使用Cols参数，忽略。

Height 高度，即所要返回的引用区域的行数，Height 必须为正数。本例中，OFFSET(J7,,,5,)表示，从J7开始，引用J7:J11共5个单元格区域。如果引用的是横向列区域，则忽略本参数。

Width 宽度，即所要返回的引用区域的列数，Width 必须为正数。由于本例引用的是纵向行区域，因此可以忽略本参数。

综上所述，“=SUM(OFFSET(J7,,,COUNTA($B:$B),))”的含义是，对J7以下、B列不为空的连续行区域求和。条件是，B6以下的有数据行的单元格必须不为空。