excel - 如何按百分比分配总数，其中每个单元格都是整数，并且它们的总和恰好等于原始总数？

Question

      Date
Nov-11     Dec-11
50         102

   Percent
0          0
0          34
34         33
33         33
33         0

  Apportion
0          0
0          35
17         34
16         34
16         0

     Sum
49         103

第一列的 5 个百分比对应于将上面的 50 分成分配标题下 5 个单元格中的整数部分。其中的结果来自简单的 Round(x*%,0)。我希望下面的所有值的总和正好等于第一列中的 50。这可以通过更改“分配”部分中任意数量的非零值来完成。

如果可以用公式来做到这一点，我还不知道如何做。我尝试的每件事都是循环引用。问题是任何百分比都可能为 0，因此我不能只创建一个用于解释下溢/溢出的存储桶。

如果需要更多信息，请告诉我。这是我第一次在SO上问问题。我无法附上图片，所以我尝试用代码绘制上面的表格。

Answer 1

这是实现目标的不同方式。

这显示了工作表的外观，除了 (1) B 列和 D 列将被隐藏，以及 (2) 值不会像我所显示的那样左对齐。

     |A         |B         |C         |D         |
 1   |Date      |          |          |          |
 2   |Nov-11    |          |Dec-11    |          |
 3   |50        |          |102       |          |
 4   |          |          |          |          |
 5   |Percent   |          |Percent   |          |
 6   |0         |          |0         |          |
 7   |0         |          |34        |          |
 8   |34        |          |33        |          |
 9   |33        |          |33        |          |
10   |33        |          |0         |          |
11   |          |          |          |          |
12   |Apportion |          |Apportion |          | 
13   |0         |0.00      |0         |0.00      |
14   |0         |0.00      |35        |0.32      |
15   |17        |0.00      |33        |-0.66     |
16   |17        |0.50      |34        |0.34      |
17   |16        |          |0         |          |
18   |          |          |          |          |
19   |Sum       |          |Sum       |          |
20   |          |          |          |          |
21   |50        |          |102       |          |

以下单元格包含所示公式:

A13  =ROUND(IF(A6=0,0,A$3*A6/100)-B12,0)
A14  =ROUND(IF(A7=0,0,A$3*A7/100)-B13,0)
A15  =ROUND(IF(A8=0,0,A$3*A8/100)-B14,0)
A16  =ROUND(IF(A9=0,0,A$3*A9/100)-B15,0)
A17  =ROUND(IF(A10=0,0,A$3*A10/100)-B16,0)
A21  =SUM(A13:A17)
B13  =A13-IF(A6=0,0,A$3*A6/100)
B14  =A14-IF(A7=0,0,A$3*A7/100)
B15  =A15-IF(A8=0,0,A$3*A8/100)
B16  =A16-IF(A9=0,0,A$3*A9/100)

公式必须输入到A13和B13中，然后向下复制。注意 B17 不需要公式，但 B17 没有被引用，因此即使有公式也没关系。

将 A13:B17 复制到 C13:D17

B13 包含已添加到 A13 以使其成为整数的金额。在对 A14 进行舍入之前，先从 A14 中减去 B13。依此类推。结果是所有舍入都在后面的行中用完。

我以前使用过这种技术，我的客户对结果很满意。如果不使用 VBA，您不会得到比这更好的结果。