Shulou(Shulou.com)06/03 Report--

In the data statistics of the report, it is often necessary to round the data according to the requirements of precision presentation or unit conversion, which is called rounding processing. Simple and direct placement processing may bring hidden dangers, and the original balanced data relationship may be broken.

In order to ensure the correctness of the data relationship in the report, it is necessary to adjust the data after rounding to make the data re-balanced. This adjustment is called rounding balance. Here we will discuss how to use the aggregator to deal with the problem of rounding off balance.

Rounding calculation is often used in rounding processing, which will produce errors, and if there is a total value of these data in the report, then the error will accumulate, which may cause the rounded data to not match its total value. For example, the raw data that holds one decimal place is 4.5-4.5-9.0, while rounding only keeps the integer portion, the balance becomes 5-5-9, which seems obviously absurd. In such cases, it is necessary to adjust the results of non-aggregate data rounding while keeping the total values correct, so that the data relations are rebalanced, for example, to 4-5-9. This simple example is a typical bargain balance.

1. One-way balance

If in the data statistics, each data is only used for one total, then when dealing with the rounding balance, we only need to adjust the data used according to the error of the total value, which is a relatively simple case. For example:

A

B

C

one

[1.48,0,1.42,0.32,6.48,0.98,1.39]

= A1.sum ()

two

= A1. (round (~))

= round (B1)

= A2.sum ()

Some data is stored in the sequence of A1, and their total values are calculated in B1. The results are as follows:

Now, round up the data and re-count. In A2, each data in the sequence is rounded by round function to get a new sequence. In B2, the results in B1 are rounded, which is the result that should be obtained after data rounding. In C2, it is simply summed up with rounded data. The results in A2PowerB2 and C2 are as follows:

Obviously, due to the accumulation of errors after rounding, the data is no longer balanced, and after rounding the original data, the sum has changed from 12 to 10. So, can you change the total directly to 10? This is not possible, because it will make the final result completely inconsistent with the real value. Therefore, in order to ensure that the balance can still be maintained after the placement, the results of each original data should be changed respectively.

The total error after giving up the position is called "balance difference". The balance is actually the process of eliminating the balance difference. There are many rules for dealing with position balance, so let's study them separately:

(1) organize the balance difference into the first data. That is:

A

B

C

one

[1.48,0,1.42,0.32,6.48,0.98,1.39]

= A1.sum ()

two

= A1. (round (~))

> A2 (1) + = round (B1)-A2.sum ()

= A2.sum ()

In B2, the balance difference is converted to the first data after the rounding. After finishing, the total value after the rounding balance treatment is recalculated in C2. The results in A2 and C2 are as follows:

The rule of handling this kind of balance is the simplest. However, after the placement, the first data has changed from 1.48 to 3, which is obviously offset a lot, so this processing is not reasonable, especially in the case of a lot of data, the balance difference may also accumulate very large. as a result, the first data produces a very absurd offset result.

(2) according to the "minimum adjustment value", the balance difference is shared and adjusted to the data with larger absolute value.

The so-called minimum adjustment value is the unit value of the minimum precision after rounding, for example, when rounding, the minimum precision is a bit, and the minimum adjustment value is 1 or-1. If the total value becomes smaller after rounding, the data needs to be increased, and the minimum adjustment value is 1; if the total value becomes larger after rounding, the data needs to be reduced, and the minimum adjustment value is-1. The adjustment is only for data with larger absolute values, so that their relative deviations are relatively small. Specifically adjust several numbers, that is, the total value deviation divided by the minimum adjustment value.

Under this rule, the rounding balance of the previous question is treated as follows:

A

B

C

one

[1.48,0,1.42,0.32,6.48,0.98,1.39]

= A1.sum ()

two

= A1. (round (~))

= round (B1)-A2.sum ()

= sign (B2)

three

= A1.psort@z (abs (~))

> abs (B2) .run (A2 (A3 (#)) + = C2)

= A2.sum ()

Because it is only a rounding operation, the minimum adjustment calculated in C2 is the positive or negative deviation of the total value.

In A3, a sort is made according to the absolute value of the original data from large to small, and the result is the sorted sequence number.

B3 is the most important, because it is only a rounding operation, so the absolute value of the deviation in B2 will be adjusted by several numbers. In this cycle, the minimum adjustment value is assigned in turn according to the absolute value of the original value.

C3 revalidates the total value of the adjusted A2.

After adjustment, the results in A2 and C3 are as follows:

In this scheme, the balance difference is shared by multiple data, and choosing the data with the largest absolute value will minimize the relative change of the data. Among the results, 1.48 was changed to 2 after 6.48, and 7 after 6.48, and the result of adjusting the balance was still ideal.

This scheme needs to sort the data by absolute value, and the execution efficiency is not very good, especially in the case of large amount of data, sorting will take more time.

(3) the balance difference is shared by the data that is not zero according to the minimum adjustment value.

In the previous scheme of adjusting the position balance, the error is shared by some data with the largest absolute value. In practice, in order to improve efficiency and reduce sorting operations, it can be appropriately simplified and shared by the data in the first few places in order. Considering that 0 does not cause an error when rounding, and if you modify the 0 in the data, the change will be more obvious, so the 0 in the original data will be left unchanged during adjustment.

Under this rule, the rounding balance of the previous question is treated as follows:

A

B

C

one

[1.48,0,1.42,0.32,6.48,0.98,1.39]

= A1.sum ()

two

= A1. (round (~))

= round (B1)-A2.sum ()

= sign (B2)

three

= A2.pselect@a (~! = 0)

> abs (B2) .run (A2 (A3 (#)) + = C2)

= A2.sum ()

The sequence number of the non-zero member in the original data is selected in A3, and the data is shared sequentially when the data is adjusted in B3. After adjustment, the results in A2 and C3 are as follows:

Among the results, 1.48 was changed to 2 after 1.42, and then to 2. The result of adjusting balance was reasonable. At the same time, this scheme avoids sorting operations and is more efficient, so this rule of rounding off balance is the most commonly used.

When dealing with one-way rounding balance, it is not only the case of summing a set of sequences. More often, it is the sum of a batch of data, such as the sequence table stored in the following 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

Now, we need to count the total sales of each employee in 4 months, taking thousands of yuan as the unit, and deal with the balance. The code is as follows:

A

B

C

D

one

= file ("SalesRecord.txt") .import@t ()

= A1.derive (Jan+Feb+Mar+Apr:Sum)

= B1.derive ()

two

> 5. (C1.field (# + 1) C1.field (# + 1). (round (~ / 1000)

= C1.derive (Jan+Feb+Mar+Apr:Sum2)

three

For B2

> func (A5Magna A3)

four

= B2.derive (Jan+Feb+Mar+Apr:Sum3)

five

Func

= A5.Sum-A5.Sum2

= abs (B5)

= sign (B5) * 1

six

For C5

= A5.field (1+B6)

> A5.field (1 million B6, C6 million D5)

Execute the code step by step. After reading the sequence table in A1, add the total field Sum to B1. The result is as follows:

Copy the above order table in C1, and perform the rounding calculation of the 2nd to 6th fields in the order table to thousands in B1. At this point, because the rounding calculation may upset the balance, add another field Sum2 to B2 to calculate the total value of 4 months after rounding. The sequence table in B2 is as follows:

As you can see, at this point, there is a difference between the Sum and Sum2 fields, indicating that the rounding balance needs to be adjusted. Although the data in the post-calculation table needs to be adjusted here, each data is only used to calculate the total of employees, so it still belongs to one-way rounding balance.

The subroutine in A5 is used to deal with the rounding balance of a record. The balance difference is calculated in B5 and the minimum adjustment value is calculated in C5. In a loop in B6, the balance difference is split into the first few data in the record, which is handled simply and does not determine whether the data is non-zero.

In A3, loop the records in table B2 and adjust the rounding balance respectively. After the adjustment, add the Sum3 field to A4 to verify the rounding balance result. The result in A4 is as follows:

Comparing Sum with Sum3, we can confirm that the result is adjusted and the data is rebalanced after rounding.

two。 Two-way offset balance

If the data needs to calculate the total values in both the row and column directions, and the total values of all data need to be calculated at the same time, it is much more complicated to deal with rounding balance. When dealing with the rounding balance, not only the final total value is required to be accurate, but also the total value of the row direction and column direction calculation should be balanced with the corresponding row and column data. in this case, the rounding balance is called two-way rounding balance. For example, in the SalesRecord.txt data, the total monthly sales need to be counted again, as follows:

A

B

C

one

= file ("SalesRecord.txt") .import@t ()

> A1.insert (0, "Total")

> 4. (A1.m (- 1) .field (# + 1 dint A1.field (# + 1) .to (, 5) .field ()

two

= A1.derive (Jan+Feb+Mar+Apr:Sum)

= A2.derive ()

> 5. (B2.field (# + 1) B2.field (# + 1). (round (~ / 1000)

three

= B2.derive (Jan+Feb+Mar+Apr:Sum2)

= A3.derive (Sum2-Sum:Diff)

> B3.insert (0, "Total2")

four

> 5. (B3.m (- 1) .field (# + 1, A3.field (# + 1) .to (, 5) .field ())

> B3.insert (0, "Diff")

> 5. (B3.m (- 1) .field (# + 1) B3 (6) .field (# + 1)-B3 (7) .field (# + 1))

Read the sequence table in A1 and add a record in B1 to calculate the total monthly sales in D1. After adding a field to A2 to calculate the total sales of each employee, as well as the total value, the results are as follows:

In C2, according to the summary data above, the result is rounded to thousands of yuan. Then, according to the data after rounding up, add the field Sum2 in A3 to calculate the total value of employees after rounding up, and add the field Diff total balance difference in B3. Finally, two more records are added to calculate the total value of each month, as well as the balance difference. After the calculation is completed, the results in B3 are as follows:

It can be seen that when the horizontal and vertical summaries are done respectively, the balance problem that needs to be solved after giving up the position is much more complicated. At this time, the modification of any one of the rounding data will affect the total calculation of both horizontal and vertical directions, which is called bi-directional rounding balance. In the above calculation, there are some balance differences related only to the aggregate values, such as the rightmost balance difference in the Total line, which is only related to the sum of each month, which is called the aggregate balance difference. In the two-way rounding balance table, there are only one horizontal and one vertical total balance difference. Other balance differences are related to specific data, such as the balance difference at the bottom of the Feb this month, which is called the non-aggregate balance difference.

Let's start with some relatively simple cases to study the two-way rounding balance:

(1) the horizontal and vertical non-aggregate balance difference symbols are the same. Such as the following:

1.44

1.35

2.79

1.2

0

1.2

2.64

1.35

3.99

The above table stores the initial data of two rows and two columns, and calculates the total value of each row and column, as well as the total value of all the data. This data is rounded and the balance difference for each row / column is calculated. The result is as follows:

one

one

three

+ 1

one

0

one

three

one

four

+ 1

The "non-aggregate balance difference" here refers to the balance difference involving the original data, where the aggregate data and the total value do not need to be adjusted. As you can see, at this time, the total values of row 1 and column 1 are unbalanced, and the total value is 1 larger than the sum of the rounding data. in this case, you only need to adjust the data at the intersection and add or subtract the minimum adjustment value according to the balance difference symbol. The specific operation is to balance the intersection, that is, the data rounding result of row 1 and column 1 by + 1, and the result is as follows:

two

one

three

one

0

one

three

one

four

(2) the two non-aggregate balance difference symbols in the same direction are opposite. Such as the following:

1.44

1.55

2.99

1.2

0.85

2.05

2.64

2.4

5.04

Round these data and calculate the balance difference for each row / column, and the results are as follows:

one

two

three

one

one

two

three

two

five

+ 1

-1

In this case, there is still no need to adjust the total value. Since the balance difference in the first column and the second column is one positive and one negative, you only need to select a row of data with a balance difference of 0, and add or subtract the minimum adjustment value according to the symbol of the balance difference, respectively. If you select the first row, the rounding result of the first column is + 1, and the rounding result of the second column is-1, and the balance can be obtained. The result is as follows:

two

one

three

one

one

two

three

two

five

(3) the sign of an aggregate balance difference is opposite to that of a non-aggregate balance difference in the other direction. Such as the following:

1.44

1.55

2.99

1.2

0.97

2.17

2.64

2.52

5.16

Round these data and calculate the balance difference for each row / column, and the results are as follows:

one

two

three

one

one

two

three

three

five

-1

+ 1

In this case, it means that the aggregate data at the intersection needs to be adjusted, only the aggregate data at the intersection need to be adjusted, and the minimum adjustment value needs to be added or subtracted according to the symbol of the total balance difference. Here, the total result of column 1 is modified, and according to the horizontal total balance difference, the balance can be obtained by-1. The result is as follows:

one

two

three

one

one

two

two

three

five

(4) A certain aggregate balance difference has the same symbol as the non-aggregate balance difference in the same direction. Such as the following:

1.48

one

2.48

2.11

1.01

3.12

3.59

2.01

5.6

Round these data and calculate the balance difference for each row / column, and the results are as follows:

one

one

two

two

one

three

four

two

six

+ 1

+ 1

In this case, the total balance difference in the column direction is the same as the balance difference sign in the other column, in which case, you can choose one row of data with a balance difference of 0 and adjust the data of these two columns at the same time. If you select row 1, that is, adjust row 1, column 1, and the total values of row 1 at the same time, you can balance them by + 1 respectively, and the result is as follows:

two

one

three

two

one

three

four

two

six

(5) the symbol of the total balance difference in both directions is the same. Such as the following:

1.44

1.99

3.43

1.6

0.48

2.08

3.04

2.47

5.51

Round these data and calculate the balance difference for each row / column, and the results are as follows:

one

two

three

two

0

two

three

two

six

+ 1

+ 1

At this point, only the aggregate data affects the balance of the results. In this case, you can choose a non-aggregate value, add or subtract the minimum adjustment value according to the symbol of the total balance difference, and also adjust the horizontal and vertical aggregate value of this data. In the above example, you can select any data, such as the value of row 2, column 2, and add it to + 1 according to the balance difference, and the total values of row 2 and column 2 at the same time, so that a balance can be achieved, as follows:

one

two

three

two

one

three

three

three

six

Because it is optional data, there are other processing methods, such as selecting the data modification in row 1 and column 2, you can also achieve a balance, and the results are as follows:

one

three

four

two

0

two

three

three

six

When dealing with two-way rounding balance, only the above five cases can adjust the balance. For other cases, it shows that the calculation is wrong and the balance can not be achieved through one adjustment. However, in the actual processing, the above situation is often mixed. Therefore, you can first deal with case (1), where all non-aggregate row and column balance differences are the same, and then deal with case (2) to eliminate the balance differences of different symbols in non-aggregate rows / columns. After all adjustments are completed, the balance difference between non-aggregate rows and non-aggregate columns can only be one symbol. At this time, the cases (3) and (4) are processed, and the balance difference of the non-aggregate row / column and the balance difference of the total row / column are eliminated. Finally, if the balance difference between the rows / columns in the two directions has not been eliminated, it will be handled in accordance with the situation in (5). In this way, you can complete the two-way rounding balance processing for the general table.

Going back to the sales data table at the beginning of this section, the following code deals with the rounding balance:

A

B

C

D

E

one

= file ("SalesRecord.txt") .import@t ()

> A1.insert (0, "Total")

> 4. (A1.m (- 1) .field (# + 1 dint A1.field (# + 1) .to (, 5) .field ()

two

= A1.derive (Jan+Feb+Mar+Apr:Sum)

= A2.derive ()

> 5. (B2.field (# + 1) B2.field (# + 1). (round (~ / 1000)

three

= B2.derive (Jan+Feb+Mar+Apr:Sum2)

= A3.derive (Sum-Sum2:Diff)

> B3.insert (0, "Total2")

four

> 5. (B3.m (- 1) .field (# + 1 dint A3.field (# + 1). To (, 5) .field ())

> B3.insert (0, "Diff")

> 5. (B3.m (- 1) .field (# + 1) B3 (6) .field (# + 1)-B3 (7) .field (# + 1))

five

= B2.len ()

= B2.fno ()

= B3. (Diff) .to (, A5)

= B3.m (- 1). Array (). To (2m B5)

six

For A5-1

For B5-2

For C5 (A6) * D5 (B6) > 0

= sign (C5 (A6))

> func (A26 ~ ~ B2 (A6), B6 ~ 1 ~ ~ D6)

seven

> C5 (A6)-= D6

> D5 (B6)-= D6

eight

For A5-2

For A5-1-A8

For C5 (A8) * C5 (A8+B8) func (A26 recordB2 (A8), E8 record1 D8)

> func (A26 ~ B2 (A8+B8), E8 ~ 1 ~ D8)

ten

> C5 (A8)-= D8

> C5 (A8+B8) + = D8

eleven

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)

thirteen

> D5 (A11)-= D11

> D5 (A11+B11) + = D11

fourteen

If C5 (A5)! = 0

For B5-2

For C5 (A5) * D5 (B14) func (A26 recorder B2 (A5), B14)

fifteen

> C5 (A5)-= D14

> D5 (B14) + = D14

sixteen

If D5 (B5-1)! = 0

For A5-1

For C5 (B16) * D5 (B5-1) func (A26 recorder B2 (B16), B5 D16)

seventeen

> D5 (B5-1)-= D16

> C5 (B16) + = D16

eighteen

If C5 (A5)! = 0

For A5-1

For C5 (A5) * C5 (B18) > 0

= sign (C5 (A5))

= D5.pselect (~ = 0)

nineteen

> func (A26 ~ B2 (B18), E18 ~ 1 ~ ~ D18)

> func (A26 ~ B2 (A5), E18 ~ 1 ~ ~ D18)

twenty

> C5 (A5)-= D18

> C5 (B18)-= D18

twenty-one

If D5 (B5-1)! = 0

For B5-2

For D5 (B21) * D5 (B5-1) > 0

= sign (D5 (B5-1))

= C5.pselect (~ = 0)

twenty-two

> func (A26 ~ ~ B2 (E21), B5 ~ ~ D21)

> func (A26 ~ B2 (E21), B21 ~ 1 ~ D21)

twenty-three

> D5 (B5-1)-= D21

> D5 (B21)-= D21

twenty-four

If C5 (A5) * D5 (B5-1) > 0

> func (A26 ~ ~ B2 (1), 2 ~ ~ C _ 5 (A5))

> func (A26 ~ ~ B2 (1), B5 ~ ~ C _ 5 (A5))

> func (A26 ~ ~ B2 (A5), 2 ~ ~ C _ 5 (A5))

twenty-five

> C5 (A5) = 0

> D5 (B5-1) = 0

twenty-six

Func

twenty-seven

= A26.field (B26)

> A26.field (B26, B27 and C26)

The program is more complex, the following is a brief description of the function. The subroutine at A26 is used to modify a record in the sequence table, adding the data of its specified location to the required adjustment value. Because the first column in the sequence table used for calculation is Name, it is not actually involved in the calculation, so it is skipped when collating the data. The horizontal and vertical balance difference sequences were obtained in C5 and D5, respectively. In lines 6 and 7, the loop deals with case (1), changing the rounding result at the intersection if the balance difference symbols in the two directions are the same. In lines 8-13, deal with case (2) in both horizontal and vertical cases, and modify the rounding results in these two rows / columns if the two balance difference symbols in the same direction are opposite. Lines 14-17, deal with case (3). When the aggregate balance difference is opposite to the non-aggregate balance difference sign in the other direction, adjust the aggregate result at the intersection. In rows 18-23, deal with case (4), when the total balance difference is the same as the non-aggregate balance difference in the same direction, modify the data in these two rows / columns. Finally, in rows 24 and 25, determine whether there are still two aggregate balance differences after the previous modification is completed, adjust the rounding result of the first data accordingly, and adjust the total values of row 1 and column 1 at the same time.

After the two-way rounding balance process is complete, you can view the final result in B2:

During the operation, the process is as follows:

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

one

Flora

8.0

12.0

90.0

64.0

174.0

0

Total

115.0

258.0

207.0

328.0

909.0

one

Diff

0

-1

-1

0

0

After performing the processing in step (1) and eliminating the non-aggregate balance difference with the same symbol in different directions, the results are as follows:

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

one

Flora

8.0

12.0

90.0

64.0

174.0

0

Total

115.0

258.0

207.0

328.0

909.0

one

Diff

0

0

0

0

0

In this example, after performing step (1) processing, there is no sign opposite to the non-aggregate balance difference, and step (2) processing is not required. In the step (3) process, it also does not exist to find out whether the aggregate balance difference is opposite to the non-aggregate balance difference sign in the other direction.

In step (4), find the case where the total balance difference is the same as the non-aggregate balance difference in the same direction, and the processing result is as follows:

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

At this time, all the balance differences have become 0, indicating that the calculation in all directions has been restored and the balance processing has been completed. If it is still not balanced, step (5) processing needs to be further performed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.