在报表的数据统计中,常常会根据精度呈现或者单位换算等要求,需要对数据执行四舍五入的操作,这种操作称为舍位处理。简单直接的舍位处理有可能会带来隐患,原本平衡的数据关系可能会被打破。
为了保证报表中数据关系的正确,就需要调整舍位之后的数据,使得数据重新变得平衡,这样的调整就叫做舍位平衡。在这里我们就讨论一下如何利用集算器来处理舍位平衡问题。
舍位处理往往会采取四舍五入计算,这时就会产生误差,而如果报表中有这些数据的合计数值,那么舍位时产生的误差就会积累,有可能导致舍位过的数据与其合计值无法匹配。例如,保留一位小数的原始的数据是4.5+4.5=9.0,而四舍五入只保留整数部分后,平衡关系就变为5+5=9了,看上去明显是荒谬的。在这样的情况下,需要在保持合计值正确的条件下,调整非合计数据舍位后的结果,使得数据关系重新平衡,例如调整为4+5=9。这个简单的例子就是典型舍位平衡。
如果在数据统计时,每个数据只用于一次合计,那么在处理舍位平衡时,只需要根据合计值的误差,调整使用的各项数据就可以了,这属于比较简单的情况。例如:
A | B | C | |
1 | [1.48,0,1.42,0.32,6.48,0.98,1.39] | =A1.sum() | |
2 | =A1.(round(~)) | =round(B1) | =A2.sum() |
A1的序列中存储了一些数据,在B1中计算了它们的合计值,结果如下:
现在,将数据取整,重新统计。A2中将序列中每个数据用round函数四舍五入取整,得到新的序列。在B2中则将B1中的结果取整,这是数据取整后应该获得的结果。在C2中则只是简单地用取整后的数据来求和。A2,B2和C2中结果分别如下:
显然,舍位后误差的累积导致数据不再平衡,将原始数据分别四舍五入后,总和由12变成了10。那么,能不能把合计数直接改为10呢?这是不行的,因为这样会使得最终结果与真实值完全不符。因此,为了保证舍位后仍然能够保持平衡关系,应该分别改变各个原始数据舍位后的结果。
舍位后总计产生的误差,称为“平衡差”,舍位平衡其实就是消除平衡差的过程。处理舍位平衡的规则有很多,下面我们分别进行研究:
(1) 将平衡差整理到第一个数据中。即:
A | B | C | |
1 | [1.48,0,1.42,0.32,6.48,0.98,1.39] | =A1.sum() | |
2 | =A1.(round(~)) | >A2(1)+=round(B1)-A2.sum() | =A2.sum() |
B2中,把平衡差折算到舍位后的第一个数据中。整理后,在C2中重新计算了舍位平衡处理后的合计值。A2和C2中的结果如下:
这种舍位平衡的处理规则最为简单。但是,舍位后第1个数据由1.48变为了3,明显偏移了很多,因此这样的处理不够合理,特别是在数据很多的情况下,平衡差也有可能会累积的很大,进而致使第1个数据产生非常荒谬的偏移结果。
(2) 将平衡差按照“最小调整值”,对绝对值比较大的数据进行分担调整。
所谓最小调整值,就是舍位后最小精度的单位值,例如在取整时,最小精度就是个位,最小调整值就是1或者-1。如果舍位后合计值变小,则需要将数据调大,那么最小调整值就是1;如果舍位后合计值变大,则需要将数据调小,最小调整值就是-1。而调整只针对绝对值比较大的数据,这样它们的相对偏差就会比较小。具体调整几个数,那就是合计值偏差除以最小调整值。
在这种规则下,前面问题的舍位平衡处理如下:
A | B | C | |
1 | [1.48,0,1.42,0.32,6.48,0.98,1.39] | =A1.sum() | |
2 | =A1.(round(~)) | =round(B1)-A2.sum() | =sign(B2) |
3 | =A1.psort@z(abs(~)) | >abs(B2).run(A2(A3(#))+=C2) | =A2.sum() |
因为只是取整操作,因此C2中计算的最小调整值就是合计值偏差的正负;
A3中,根据原始数据的绝对值从大到小做了一个排序,结果就是排序后的序号。
B3是最主要的,因为只是取整操作,所以B2中的偏差绝对值是多少,就调整几个数。以此循环,依照原始值的绝对值大小,依次分配最小调整值。
C3是对调整后的A2重新验证了合计值。
调整后,A2和C3中的结果如下:
在这种方案中,平衡差由多个数据分担,而选择绝对值最大的数据会使得数据的相对变动最小。在结果中,1.48舍位后变为了2,6.48舍位后变为了7,调整平衡的结果还是比较理想的。
这种方案需要将数据按绝对值排序,执行效率不是很好,特别是在数据量比较大的情况下,排序会耗费较多时间。
(3) 将平衡差按照最小调整值,由不为0的数据依次分担。
在上一种调整舍位平衡的方案中,将误差由绝对值最大的一些数据来分担。在实际操作中,为了提高效率,减少排序操作,就可以适当简化,改为由顺序排在前几位的数据来分担。考虑到在四舍五入时,0并不会产生误差,而且如果修改数据中的0,这样的变动会比较明显,因此在调整时将保留原始数据中的0不变。
在这种规则下,前面问题的舍位平衡处理如下:
A | B | C | |
1 | [1.48,0,1.42,0.32,6.48,0.98,1.39] | =A1.sum() | |
2 | =A1.(round(~)) | =round(B1)-A2.sum() | =sign(B2) |
3 | =A2.pselect@a(~!=0) | >abs(B2).run(A2(A3(#))+=C2) | =A2.sum() |
A3中选择出原始数据中非0成员的序号,在B3中调整舍位后数据时,按顺序分担。调整后,A2和C3中的结果如下:
在结果中,1.48舍位后变为了2,1.42舍位后变为了2,调整平衡的结果比较合理。同时这种方案避免了排序操作,效率较高,因此这种舍位平衡的规则最为常用。
在处理单向舍位平衡时,并非只有对一组序列求和的情况。更多的情况下,是对一批数据来求和,如下面的SalesRecord.txt中存储的序表:
Name Jan Feb Mar Apr Allen 26106 49637 27760 33829 Billy 56611 50588 54765 76072 Charlie 21249 96825 28645 55958 Daisy 3413 49069 6279 98247 Flora 7590 12072 90034 64252 |
现在,需要统计每位员工4个月的总销售额,统计时以千元为单位,并处理舍位平衡。代码如下:
A | B | C | D | |
1 | =file("SalesRecord.txt").import@t() | =A1.derive(Jan+Feb+Mar+Apr:Sum) | =B1.derive() | |
2 | >5.(C1.field(#+1,C1.field(#+1).(round(~/1000)))) | =C1.derive(Jan+Feb+Mar+Apr:Sum2) | ||
3 | for B2 | >func(A5,A3) | ||
4 | =B2.derive(Jan+Feb+Mar+Apr:Sum3) | |||
5 | func | =A5.Sum-A5.Sum2 | =abs(B5) | =sign(B5)*1 |
6 | for C5 | =A5.field(1+B6) | >A5.field(1+B6,C6+D5) |
分步执行代码,A1中读入序表后,在B1中添加合计字段Sum,结果如下:
在C1中将上面的序表复制,并在B1中将序表中的第2至第6个字段执行舍位计算到以千为单位。此时,有可能由于四舍五入计算破坏平衡,在B2中再添加一个字段Sum2,计算舍位后4个月的合计值。B2中的序表如下:
可以看到,在此时,Sum与Sum2字段是有区别的,说明需要调整舍位平衡。在这里虽然需要调整计算后序表中的数据,但是每个数据只用于计算员工合计,因此仍然属于单向舍位平衡。
A5中的子程序用来处理一条记录的舍位平衡,B5中计算平衡差,C5中计算出最小调整值。在B6中循环,将平衡差拆分到记录中前几个数据中,这里简单处理,并未判断数据是否非零。
在A3中,循环序表B2中的记录,分别调整舍位平衡。调整完毕后,在A4中再添加Sum3字段来验证舍位平衡结果,A4中结果如下:
对比Sum与Sum3可以确认,结果调整,数据舍位后重新达成了平衡。
如果数据在行向和列向两个方向同时需要计算合计值,同时还需要计算所有数据的总计值,这种情况下处理舍位平衡时就复杂得多了。此时处理舍位平衡时,不仅要求最终的总计值准确,同时行向和列向计算的合计值也要与对应行、列的数据平衡,这种情况下的舍位平衡称为双向舍位平衡。如在SalesRecord.txt的数据中,需要再统计每个月的总销售额,代码如下:
A | B | C | |
1 | =file("SalesRecord.txt").import@t() | >A1.insert(0,"Total") | >4.(A1.m(-1).field(#+1,A1.field(#+1).to(, 5).sum())) |
2 | =A1.derive(Jan+Feb+Mar+Apr:Sum) | =A2.derive() | >5.(B2.field(#+1,B2.field(#+1).(round(~/1000)))) |
3 | =B2.derive(Jan+Feb+Mar+Apr:Sum2) | =A3.derive(Sum2-Sum:Diff) | >B3.insert(0,"Total2") |
4 | >5.(B3.m(-1).field(#+1, A3.field(#+1).to(,5).sum())) | >B3.insert(0,"Diff") | >5.(B3.m(-1).field(#+1,B3(6).field(#+1)- B3(7).field(#+1))) |
A1中读入序表,并在B1中添加一条记录,用来在D1中计算各月总销售额。再在A2中添加字段计算每位员工的销售总额,以及总合计值后,结果如下:
在C2中根据上面的汇总数据,将结果舍位到以千元为单位。再根据舍位后的数据,在A3中添加字段Sum2计算舍位后的员工合计值,在B3中添加字段Diff员工合计的平衡差。最后,再添加2条记录,分别用来计算每个月的舍位合计值,以及平衡差。计算完成后,B3中结果如下:
可以看到,当横向和纵向分别做汇总时,舍位后需要解决的平衡问题就复杂得多了。此时修改任何一个舍位数据,都会同时影响横向和纵向两个方向的合计计算,这样的问题称为双向舍位平衡。在上面的计算中,有一些平衡差只与合计值相关,如Total这一行中最右侧的平衡差,只与各月的合计有关,这样的平衡差称为合计平衡差。在双向舍位平衡表中,只存在一横一纵两个合计平衡差。其它的平衡差都会和具体数据相关,如Feb这个月最下方的平衡差,这种平衡差称为非合计平衡差。
我们先从一些比较简单的情况开始研究双向舍位平衡:
(1)横向与纵向的非合计平衡差符号相同。如下面的情况:
1.44 | 1.35 | 2.79 |
1.2 | 0 | 1.2 |
2.64 | 1.35 | 3.99 |
上面的表格中,存储着2行2列的初始数据,同时计算出了各行各列的合计值,以及所有数据的总计值。下面将这些数据四舍五入取整,并计算每一行/列的平衡差,结果如下:
1 | 1 | 3 | +1 |
1 | 0 | 1 | |
3 | 1 | 4 | |
+1 |
这里的“非合计平衡差”是指涉及原始数据的平衡差,此时合计数据及总计值都不需要调整。可以看到,此时只是第1行和第1列的合计值不平衡,而且都是合计值比舍位数据的和大1,这种情况下,只需要调整交叉点处的数据,根据平衡差符号加减最小调整值即可。具体操作是把交叉点处,即第1行第1列的数据舍位结果+1,就可获得平衡,结果如下:
2 | 1 | 3 |
1 | 0 | 1 |
3 | 1 | 4 |
(2)同向的2个非合计平衡差符号相反。如下面的情况:
1.44 | 1.55 | 2.99 |
1.2 | 0.85 | 2.05 |
2.64 | 2.4 | 5.04 |
将这些数据四舍五入取整,并计算每一行/列的平衡差,结果如下:
1 | 2 | 3 | |
1 | 1 | 2 | |
3 | 2 | 5 | |
+1 | -1 |
这种情况下,仍然不需要调整总计值。由于第1列和第2列中的平衡差一正一负,只需要任选一行平衡差为0的数据,将这两列的数分别根据按平衡差的符号加减最小调整值。如选择第1行,将第1列的舍位结果+1,将第2列的舍位结果-1,就可获得平衡,结果如下:
2 | 1 | 3 |
1 | 1 | 2 |
3 | 2 | 5 |
(3)某个合计平衡差与另一方向的非合计平衡差符号相反。如下面的情况:
1.44 | 1.55 | 2.99 |
1.2 | 0.97 | 2.17 |
2.64 | 2.52 | 5.16 |
将这些数据四舍五入取整,并计算每一行/列的平衡差,结果如下:
1 | 2 | 3 | |
1 | 1 | 2 | |
3 | 3 | 5 | -1 |
+1 |
这种情况下,说明交叉点处的合计数据需要调整,只需要调整交叉点处的合计数据,根据合计平衡差的符号加减最小调整值。在这里,即修改第1列的合计结果,根据横向的合计平衡差,将其-1,即可获得平衡,结果如下:
1 | 2 | 3 |
1 | 1 | 2 |
2 | 3 | 5 |
(4)某个合计平衡差与同方向的非合计平衡差符号相同。如下面的情况:
1.48 | 1 | 2.48 |
2.11 | 1.01 | 3.12 |
3.59 | 2.01 | 5.6 |
将这些数据四舍五入取整,并计算每一行/列的平衡差,结果如下:
1 | 1 | 2 | |
2 | 1 | 3 | |
4 | 2 | 6 | |
+1 | +1 |
在这里是列向的合计平衡差与另一列的平衡差符号相同,在这种情况下,可以任选1行平衡差为0的数据,同时调整这2列的数据。如果选择第1行,即同时调整第1行第1列,以及第1行的合计值,将它们分别+1即可获得平衡,结果如下:
2 | 1 | 3 |
2 | 1 | 3 |
4 | 2 | 6 |
(5)两个方向合计平衡差的符号相同。如下面的情况:
1.44 | 1.99 | 3.43 |
1.6 | 0.48 | 2.08 |
3.04 | 2.47 | 5.51 |
将这些数据四舍五入取整,并计算每一行/列的平衡差,结果如下:
1 | 2 | 3 | |
2 | 0 | 2 | |
3 | 2 | 6 | +1 |
+1 |
此时,只有合计数据影响了结果的平衡。在这种情况下,可以任选一个非合计值,根据合计平衡差的符号加减最小调整值,同样调整这个数据的横向和纵向合计值。在上面例子中,可以任意选择1个数据,如第2行第2列的值,根据平衡差将它+1,同时将第2行以及第2列的合计值同时都+1,这样就可以获得平衡,如下:
1 | 2 | 3 |
2 | 1 | 3 |
3 | 3 | 6 |
由于是任选数据,也有其它的处理方式,如选择第1行第2列的数据修改,同样可以获得平衡,结果如下:
1 | 3 | 4 |
2 | 0 | 2 |
3 | 3 | 6 |
在处理双向舍位平衡时,只有上面的5种情况可以调整平衡。对于其它的情况,说明计算有误,是无法通过1次调整达成舍位平衡的。但是在实际处理中,上面的情况往往是混合出现的。因此,可以先处理第(1)种情况,即所有非合计行列平衡差符号相同的情况,再处理第(2)种情况,将非合计行/列中不同符号的平衡差消除。全部调整理完毕后,非合计行与非合计列的平衡差只能各为一种符号。此时再处理第(3)种和第(4)种情况,将非合计行/列的平衡差与合计行/列的平衡差配合消除。最后,如果两个方向行/列的平衡差仍未消除,再按照第(5)中情况处理。这样,就可以对一般性的表格完成双向舍位平衡处理了。
再回到这一节开始时的销售数据表,下面的代码将处理其中的舍位平衡:
A | B | C | D | E | |
1 | =file("SalesRecord.txt").import@t() | >A1.insert(0,"Total") | >4.(A1.m(-1).field(#+1,A1.field(#+1).to(, 5).sum())) | ||
2 | =A1.derive(Jan+Feb+Mar+Apr:Sum) | =A2.derive() | >5.(B2.field(#+1,B2.field(#+1).(round(~/1000)))) | ||
3 | =B2.derive(Jan+Feb+Mar+Apr:Sum2) | =A3.derive(Sum-Sum2:Diff) | >B3.insert(0,"Total2") | ||
4 | >5.(B3.m(-1).field(#+1,A3.field(#+1).to(,5).sum())) | >B3.insert(0,"Diff") | >5.(B3.m(-1).field(#+1,B3(6).field(#+1)- B3(7).field(#+1))) | ||
5 | =B2.len() | =B2.fno() | =B3.(Diff).to(,A5) | =B3.m(-1).array().to(2,B5) | |
6 | for A5-1 | for B5-2 | for C5(A6)*D5(B6)>0 | =sign(C5(A6)) | >func(A26,B2(A6),B6+1,D6) |
7 | >C5(A6)-=D6 | >D5(B6)-=D6 | |||
8 | for A5-2 | for A5-1-A8 | for C5(A8)*C5(A8+B8)<0 | =sign(C5(A8)) | =D5.pselect(~==0) |
9 | >func(A26,B2(A8),E8+1,D8) | >func(A26,B2(A8+B8),E8+1,-D8) | |||
10 | >C5(A8)-=D8 | >C5(A8+B8)+=D8 | |||
11 | for B5-3 | for B5-2-A11 | for D5(A11) * D5(A11+B11) < 0 | =sign(D5(A11)) | =C5.pselect(~==0) |
12 | >func(A26,B2(E11),A11+1,D11) | >func(A26,B2(E11),A11+B11+1,-D11) | |||
13 | >D5(A11)-=D11 | >D5(A11+B11)+=D11 | |||
14 | if C5(A5)!=0 | for B5-2 | for C5(A5)*D5(B14)<0 | =sign(C5(A5)) | >func(A26,B2(A5),B14+1,D14) |
15 | >C5(A5)-=D14 | >D5(B14)+=D14 | |||
16 | if D5(B5-1)!=0 | for A5-1 | for C5(B16)*D5(B5-1)<0 | =sign(D5(B5-1)) | >func(A26,B2(B16),B5,D16) |
17 | >D5(B5-1)-=D16 | >C5(B16)+=D16 | |||
18 | if C5(A5)!=0 | for A5-1 | for C5(A5)*C5(B18)>0 | =sign(C5(A5)) | =D5.pselect(~==0) |
19 | >func(A26,B2(B18),E18+1,D18) | >func(A26,B2(A5),E18+1,D18) | |||
20 | >C5(A5)-=D18 | >C5(B18)-=D18 | |||
21 | if D5(B5-1)!=0 | for B5-2 | for D5(B21)*D5(B5-1)>0 | =sign(D5(B5-1)) | =C5.pselect(~==0) |
22 | >func(A26,B2(E21),B5,D21) | >func(A26,B2(E21),B21+1,D21) | |||
23 | >D5(B5-1)-=D21 | >D5(B21)-=D21 | |||
24 | if C5(A5)*D5(B5 -1)>0 | >func(A26,B2(1),2,C5(A5)) | >func(A26,B2(1),B5,C5(A5)) | >func(A26,B2(A5),2,C5(A5)) | |
25 | >C5(A5)=0 | >D5(B5-1)=0 | |||
26 | func | ||||
27 | =A26.field(B26) | >A26.field(B26,B27+C26) |
程序比较复杂,下面简要说明一下功能。A26处的子程序用来修改序表中的1条记录,将其指定位置的数据加上所需的调整值。由于用于计算的序表中,第一列为Name,实际并不参与计算,因此整理数据时将其跳过。C5和D5中分别获得横向和纵向的平衡差序列。在第6、7行,循环处理第(1)种情况,如果两个方向的平衡差符号相同,改变交叉点处的舍位结果。在第8~13行,分横纵两种情况,处理第(2)种情况,如果同向的两个平衡差符号相反时,修改这两行/列中的舍位结果。第14~17行,处理第(3)种情况,当合计平衡差与另一方向的非合计平衡差符号相反时,调整交叉点处的合计结果。在第18~23行,处理第(4)种情况,当合计平衡差与同方向的非合计平衡差符号相同时,修改这两行/列中的数据。最后,在第24和25行,判断前面的修改完成后,是否仍然存在两个合计平衡差,此时相应调整第1个数据的舍位结果,同时调整第1行和第1列的合计值。
双向舍位平衡处理完成后,在B2中可以查看最终结果:
运算时,处理过程如下:
Name | Jan | Feb | Mar | Apr | Sum | Diff |
Allen | 26.0 | 50.0 | 28.0 | 34.0 | 137.0 | -1 |
Billy | 57.0 | 51.0 | 55.0 | 76.0 | 238.0 | -1 |
Charlie | 21.0 | 97.0 | 29.0 | 56.0 | 203.0 | 0 |
Daisy | 3.0 | 49.0 | 6.0 | 98.0 | 157.0 | 1 |
Flora | 8.0 | 12.0 | 90.0 | 64.0 | 174.0 | 0 |
Total | 115.0 | 258.0 | 207.0 | 328.0 | 909.0 | 1 |
Diff | 0 | -1 | -1 | 0 | 0 |
执行第(1)步处理,将不同方向上符号相同的非合计平衡差消除后,结果如下:
Name | Jan | Feb | Mar | Apr | Sum | Diff |
Allen | 26.0 | 49.0 | 28.0 | 34.0 | 137.0 | 0 |
Billy | 57.0 | 51.0 | 54.0 | 76.0 | 238.0 | 0 |
Charlie | 21.0 | 97.0 | 29.0 | 56.0 | 203.0 | 0 |
Daisy | 3.0 | 49.0 | 6.0 | 98.0 | 157.0 | 1 |
Flora | 8.0 | 12.0 | 90.0 | 64.0 | 174.0 | 0 |
Total | 115.0 | 258.0 | 207.0 | 328.0 | 909.0 | 1 |
Diff | 0 | 0 | 0 | 0 | 0 |
这个例子中,执行第(1)步处理后,并没有符号相反的非合计平衡差,不需执行第(2)步处理。在第(3)步处理中,查找合计平衡差是否与另一方向上的非合计平衡差符号相反的情况,同样不存在。
在第(4)步处理中,查找合计平衡差与同向的非合计平衡差符号相同的情况,处理结果如下:
Name | Jan | Feb | Mar | Apr | Sum | Diff |
Allen | 26.0 | 49.0 | 28.0 | 34.0 | 137.0 | 0 |
Billy | 57.0 | 51.0 | 54.0 | 76.0 | 238.0 | 0 |
Charlie | 21.0 | 97.0 | 29.0 | 56.0 | 203.0 | 0 |
Daisy | 4.0 | 49.0 | 6.0 | 98.0 | 157.0 | 0 |
Flora | 8.0 | 12.0 | 90.0 | 64.0 | 174.0 | 0 |
Total | 116.0 | 258.0 | 207.0 | 328.0 | 909.0 | 0 |
Diff | 0 | 0 | 0 | 0 | 0 |
此时,所有的平衡差已经都变为了0,说明各个方向上的计算已经恢复平衡,舍位平衡处理完成。如果仍未平衡,则需要进一步执行第(5)步处理。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。