如何处理报表中的舍位平衡-创新互联
在报表的数据统计中，常常会根据精度呈现或者单位换算等要求，需要对数据执行四舍五入的操作，这种操作称为舍位处理。简单直接的舍位处理有可能会带来隐患，原本平衡的数据关系可能会被打破。
为了保证报表中数据关系的正确，就需要调整舍位之后的数据，使得数据重新变得平衡，这样的调整就叫做舍位平衡。在这里我们就讨论一下如何利用集算器来处理舍位平衡问题。
舍位处理往往会采取四舍五入计算，这时就会产生误差，而如果报表中有这些数据的合计数值，那么舍位时产生的误差就会积累，有可能导致舍位过的数据与其合计值无法匹配。例如，保留一位小数的原始的数据是4.5+4.5=9.0，而四舍五入只保留整数部分后，平衡关系就变为5+5=9了，看上去明显是荒谬的。在这样的情况下，需要在保持合计值正确的条件下，调整非合计数据舍位后的结果，使得数据关系重新平衡，例如调整为4+5=9。这个简单的例子就是典型舍位平衡。
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() |
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可以确认，结果调整，数据舍位后重新达成了平衡。
2. 双向舍位平衡
如果数据在行向和列向两个方向同时需要计算合计值，同时还需要计算所有数据的总计值，这种情况下处理舍位平衡时就复杂得多了。此时处理舍位平衡时，不仅要求最终的总计值准确，同时行向和列向计算的合计值也要与对应行、列的数据平衡，这种情况下的舍位平衡称为双向舍位平衡。如在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)步处理。
另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
当前文章：如何处理报表中的舍位平衡-创新互联
转载源于：http://hbruida.cn/article/isoje.html
