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

在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函数将迅速膨胀。对于校运会而言，各