在Excel中实现含累加式单元格的求和统计功能

摘要：该文从校运会比赛成绩统计的需求出发，提出并解决了对含有某种特殊累加式单元格的自动求和问题。最后将解决问题的方案推广到适应一般累加式的场合。

关键词：累加式；求和；函数嵌套；VBA编程

中图分类号：TP312文献标识码：A文章编号：1009-3044(2011)29-7187-02

The Realization of the Summation Statistical Functions Involving Accumulative Type Cell in Excel

LIU Bu-xing

(Information Engineering Department of Changsha Electric Power Technical College, Changsha 410131, China)

Abstract: This article puts and resolves the automatic summation problem according to the requirement of the college athletic game scores statistic which involve special accumulation polynomial cells. Spread the solution to the general accumulative type occasion.

Key words: accumulation polynomial; summation; function nested; VBA programming

1 问题由来

校运会竞赛规则规定：每一个比赛项目取前1至6名，并依次获得7、5、4、3、2、1分。在成绩统计中，一个班级可能在同一个比赛项目中有多人获得名次，从而在同一个项目有多次得分记录。例如某班在男子100米比赛中取得了第1名和第5名，要正确直观地体现这种情况，就在相应单元格中就出现了累加式“7+2”。

这种累加式在Excel中是作为字符串来处理的，不能按通常的数据处理功能对其进行自动统计求和。

如果将累加式用相应公式代替，如“=7+2”，则既保留了得分记录，又不影响自动求和功能，但遗憾的是这样做在相应的单元格中显示的是公式计算结果，而非公式本身（如显示“9”，而不是“7+2”），虽然选中单元格可以在地址栏中看到对应公式，但表格已失去直观、公示的作用。

如何既保留累加式的直观性，又能对其进行自动求和统计，这是本文要讨论的问题。

2 解决方案

为使问题的讨论更具一般性，我们设有m支参赛队参加n个项目的比赛。设第i个项目取前ki名（i=1,2,…,n）。各名次记分为一个1位正整数，记：

M=max{ki|i=1,2,…,n}

为讨论方便，不失一般性，我们取m=3，n=2，自动求和列（总分）位于项目n之后。表中输入数据中的累加项数不能超过M，累加数为1位正数。如表1。

方案一：函数嵌套

由于累加式实质上是字符串，因此问题的关键是将该字符串原本要表达的值算出来。根据比赛的评分规则可知，累加式中各项为1位正整数，不超过6项（空单元格及只有一个1位数的单元格视为累加式的特例）。这就为定位累加式中的数字提供的依据，因此可以根据项数，将各数字提取并转化为数值后再加起来，从而得到单元格内累加式的值。

如B2单元格是不超2个数相加的累加式，则它可能所含数字的个数可能为0、1或2。因此其值可以表示为：

IF(LEN(B2)=1,B2,IF(LEN(B2)=3,VALUE(MID(B2,1,1))+VALUE(MID(B2,3,1)),0))（1）

如要将B2单元格适用场合扩充为不超3个数相加的累加式，则它可能所含数字的个数可能为0、1、2或3。因此只要将其恰好含3个数值的IF函数由式1中相关部分递推地写出来，并用它取代式1中的0，即得嵌套结果：

IF(LEN(B2)=1,B2,IF(LEN(B2)=3,VALUE(MID(B2,1,1))+VALUE(MID(B2,3,1)),IF(LEN(B2)=5,VALUE(MID(B2,1,1))+VALUE(MID(B2,3,1))+VALUE(MID(B2,5,1)),0))) （2）

一般地，记某单元格cell不超过k-1项累加式的值为 ，则该单元格不超过k项累加式的值为

F(k,0)=F(k-1,g(k))（3）

其中：

（4）

随着累加式项数的增多，对应的嵌套IF函数将迅速膨胀。对于校运会而言，各输入单元格按F(4,0)计算应该够用了，如要做到万无一失，就要按F(6,0)计算（这意味着包含了所有的可能性，甚至某参赛队囊括某比赛项目全部名次的情形）。此时函数的嵌套层次已达到7级，这是Excel所允许的最高嵌套级数了[1]。这些函数看起来虽然复杂，其实是同一嵌套过程循环操作的结果，写的过程比看懂它更容易。

求得了计算区域内任意一个单元格的累加式的函数表达式后，用一定的复制方式即可得出计算区域内该行所有单元格中累加式的函数表达式，从而得出该行之和。再通过纵向复制即可完成全部求和统计。

此方案的特点是使用过程公示性能好：所有具有累加式的单元格都以最直观的方式显示着累加过程，而对应的总分统计量则随累加式的变化而正确更新。不足之处是受到嵌套级数的限制，累加式中最多只能有6个数相加。

方案二：VBA编程

本方案对单元格的处理原理与方案一相同，但由于采用VBA编程，更具有如下优点：

1) 不受累加式中项数的限制；

2) 代码执行效率高；

3) 计算区域更新灵活方便（只需修改起始行、列号及终止行、列号）。

程序主要由三重循环构成，里层循环While…Wend求出累加式单元格的值；第二层循环For…next j对同一行各单元格的值求和，实现该行总分计算；外层循环While…next i完成各行的统计。

Sub add()

' 快捷键: Ctrl+e

i0 = 3 '起始行号

j0 = 2 '起始列号

m = 4 '终止行号

n = 3 '终止列号

For i = i0 To m

s = 0

For j = j0 To n

addstr = Cells(i, j)

While Len(addstr) > 0

s = s + Int(Left(addstr, 1))

If Len(addstr) = 1 Then

addstr = Right(addstr, Len(addstr) - 1)

Else

addstr = Right(addstr, Len(addstr) - 2)

End If

Wend

Next j

Cells(i, n + 1) = s

Next i

End Sub

3 算法延伸

前述方案一可以说是为校运会统分“量身定做”，也仅能适应于这种场合。方案二虽然不受累加式中项数的限制，但如果累加数的位数不止1位，或者出现负数，它就无能为力了（会导致错误结果）。

现对方案二作出改进，使之新增以下功能：

1) 累加数不受位数限制；

2) 累加数可以为负数（即允许扣分）

在内层循环While…Wend 之前添两句：term = 0（存放单元格的值）及 sign = 1（存放正负符号），并将内层循环While…Wend修改如下：

While Len(addstr) > 0

If Asc(addstr) >= 48 And Asc(addstr)

term = term * 10 + Int(Left(addstr, 1))

If Len(addstr) = 1 Then

If sign = 1 Then

s = s + term

Else

s = s - term

End If

End If

addstr = Right(addstr, Len(addstr) - 1)

ElseIf Asc(addstr) = 43 Then '遇到“+”

If sign = 1 Then

s = s + term

Else

s = s - term

End If

sign = 1

term = 0

addstr = Right(addstr, Len(addstr) - 1)

ElseIf Asc(addstr) = 45 Then '遇到“-”

If sign = 1 Then

s = s + term

Else

s = s - term

End If

sign = -1

term = 0

addstr = Right(addstr, Len(addstr) - 1)

Else

Cells(i, n + 1) = "本行有输入错误，请检查。" '遇到其它字符

Exit Sub

End If

Wend

表5是改进后VBA编程方案的一个应用。在这里，累加项的项数、每项的位数及正负符号都没有限制。

参考文献：

[1] 关于函数内部的嵌套函数Microsoft Excel帮助文件[Z].

[2] Visual Basic For Application帮助文件[Z].