基于EXCEL VBA的浮点金额偏差处理方法

使用Excel进行财务报表处理时，会和数据频繁地打交道，有时数据精度要求很高，如计算利息、股票价格等；有时数据精度要求较低，如报价、资产评估等。因为会产生四舍五入的误差，所以使用二进制无法准确地表示十进制小数[1]60-61。在精度要求较高时，这样的误差累积是不可忽视的。在Excel中，当Currency和Decimal的数据类型使用整数时，只有按照比例划分才能避免这种误差。这种方法的原理是：任何十进制整数都用准确的二进制表示，都可以表示为2的n次幂的组合，而非整数则将小数变成整数后再存放为二进制形式，并使用相应的倍数相乘，以消除小数点[2]76-78。

一、实例分析

本文以某单位的集资财务报表为例。该工作表显示了一个集资项目的款项和利息信息，目的是方便给集资人支付利息(参见表1)。

步骤一 对于单位为万元的金额表，仅仅使用两位小数来表示需要很高精度的利息项是很不准确的。可以选中所有“年利息”项并右击，在弹出的菜单中单击“设置单元格格式”选项；然后在弹出的“单元格格式”对话框中选择“数字”页面，选中“分类”组合框中的“货币”项，并将“小数位数”设置为8，最后单击“确定”保存并结束设置[3]69-71。通过上述设置，显示年利息金额的所在列就保留了小数点后的8位数。

对学生的评估主要是改革传统的学生成绩以期末考试为主，平时考试成绩为辅的评价方法，采用阶段评价、过程评价与目标评价相结合。理论与实践一体化，以实际参与教学任务为主的评价模式。包括学生自评、小组内互评、组间互评、教师评语、校内专家评语，得出综合成绩。

 

表1 某单位集资工作表 (单位：万元)

  

集资号本金日利息年收益3537894625.000.003 41.243537894710.000.001 40.51353789485.000.000 70.263537894915.000.002 10.77353789506.000.000 80.293537895135.000.004 81.75353789528.000.001 10.403537895312.000.001 60.583537895410.000.001 40.51353789555.000.000 70.26

步骤二 在工作表上新建一个“按钮”控件，并把它命名为“模糊计数”。然后，在“设计模式”下双击该按钮控件，进入“Visual Basic编辑器”。编辑该“按钮”控件的单击响应代码如下：

Option ExplicitPrivate Sub CommandButton1_Click()

Dim TempI As Integer

Dim TempJ As Integer

Dim dblSum As Double

For TempJ =3 T0 12 Step 1dblSum=Cells (TempJ, 2).Value

For TempI =1 T0 365 Step 1

dblSum= dblSum+ Cells (TempJ, 3).Value

Next TempI

Cells(TempJ, 4).Value = dblSum - Cells(TempJ, 2).Value

Next TempJEnd sub

图1、2a、3a：引自Sear ing H.New Amer ican ar t museums[M].Cal if or nia:Univ of Cal if or nia Pr ess,1982；

代码编辑完毕后，保存并退出“Visual Basic编辑器”。然后单击“模糊计数”按钮，即可将显示利息金额列。在利息金额列上设置全部只保留小数点后2位为有效数字，并把其他位设置为0(参见表2)。

 

表2 使用“模糊计数”显示的某单位集资工作表 (单位：万元)

  

集资号本金日利息年收益3537894625.000.003 41.240 000 003537894710.000.001 40.510 000 00353789485.000.000 70.260 000 003537894915.000.002 10.770 000 00353789506.000.000 80.290 000 003537895135.000.004 81.750 000 00353789528.000.001 10.400 000 003537895312.000.001 60.580 000 003537895410.000.001 40.510 000 00353789555.000.000 70.260 000 00

步骤三 在工作表的空白区域再新建一个“按钮”控件，并命名为“精确计数”。 在“设计模式”下双击该按钮控件，进入“Visual Basic编辑器”。编辑该“按钮”控件的单击响应代码如下：

1928年的《中国教育辞典》：“教育的定义有广义、侠义两种。广义说，凡足以影响人类身心之种种活动，都可以叫教育；狭义上说，用一定的方法实现之改善目的者，可称为‘教育’。”1930年的《教育大辞书》：“广而言之，凡足以感化身心之影响，俱得云教育，只称其结果，不计其方法；狭而言之，则惟具有目的，出以一定方案者，始云教育。”1985年的《中国大百科全书·教育》：“从广义上说，凡是增进人们的知识和技能，影响人们的思想品德的活动，都是教育；狭义的教育主要指学校教育，其含义是教育者根据一定社会或阶级的要求，有目的、有计划地对受教育者地身心施加影响，把他们培养成为一定社会阶级所需要的人的活动。”

Private Sub CommandButton2_Click()

Dim TempI As Integer

Dim TempJ As Integer

Dim sngSum As SingleDim x As Single

For TempJ = 3 To 12 Step 1

sngSum = Cells(TempJ, 2).Value

For TempI = 1 To 265 Step 1

sngSum = sngSum + Cells(TempJ, 3).Value

Next TempI

Cells(TempJ, 4).Value = sngSum - x

Next TempJEnd Sub

编辑代码完毕后，保存并退出“设计模式”。然后单击“精确计数”按钮，即可将利息的金额列全部显示出来，包括预先设定的所有有效数字。这里的显示位数为在“单元格格式”对话框设定的8位(参见表3)。

 

表3 使用“精确计数”显示的某单位集资工作表 (单位：万元)

  

集资号本金日利息年收益3537894625.000.003 41.241 292 953537894710.000.001 40.510 997 77353789485.000.000 70.255 498 893537894915.000.002 10.766 496 66353789506.000.000 80.292 048 453537895135.000.004 81.751 594 54353789528.000.001 10.401 349 073537895312.000.001 60.584 096 913537895410.000.001 40.510 997 77353789555.000.000 70.255 498 89

二、总结与提高

Currency数据类型可以用做存放任何小数位不超过4位的数值，在保存时把数值乘以10 000，以消除小数点[4]103-104。然后，在存储区中作为整数存放为二进制形式。这样就消除了小数作为二进制浮点数存放时产生的舍入误差。在使用时，将其除以10 000即可。Decimal数据类型使用了12个字节，用来存放数据，目的是将Currency数据类型的优势放大，达到比Currency数据类型更大的精度。Decimal数据类型的数值范围是变化的，但取决于所要的小数位数精度。小数位越多，可用的范围越小。小数位最大时可达到28位。这时的小数点以左的取值范围只能从-8～8。

参考文献：

[1] 王建华.Excel 2010 VBA 在职工退休审批管理中的应用[J].电脑编程技巧与维护,2015(6).

[2] 孙敬杰.Excel入门与实例演练[M].北京:中国青年电子出版社,2008.

[3] 蒋 勇.基于Excel VBA的计算机理论知识考试系统设计[J].信息与电脑:理论版,2010(3).

[4] 魏汪洋.Excel VBA语法速查手册[M].北京:化学工业出版社,2011.