提问人：Alex H 提问时间：11/15/2023 最后编辑：Alex H 更新时间：11/17/2023 访问量：46
如何仅使用整数自动调整值范围以尽可能接近目标值
How to automatically adjust a range of values to get as close to a target value as possible using only integers
问：
我在 Excel 中有一个值范围，这些值是一系列简单计算的输出。然后将这些值输入到一个只接受整数的在线系统中（是的，这是一个愚蠢的系统，我无法更改它）。财务团队目前手动调整输出，直到总值尽可能接近，他们可以用整数将其转换为正确的值，然后输入该值。我想知道在 VBA 中是否有一种自动化的方法可以做到这一点。类似于目标寻求，但接受你可能无法用整数满足确切的数字，只是试图找到最接近的数字。我还需要尽量使当前数字尽可能接近“真实”值。
我最初的想法是遍历每个数字，对每个数字尝试 +1 和 -1。存储每个的输出，计算出哪个结果最接近答案，存储并重复。不过，这感觉不是很有效。有更好的主意吗？
下图 1：天数是感兴趣的列。$B/$F = $H。这些值必须是整数。计算后，总计需要尽可能接近此值
如果将绿色值四舍五入到最接近的整数，则会得到总和的方差。
在不对数字的正确值进行太多调整的情况下，如何计算出与总方差最小的数字组合？
谢谢
编辑：基于以下求解器解决方案的附加约束。必须是自动化的，所以它的白痴证明（我可以做自动化，只是在寻找最好的方法）
答：
1赞
taller
11/15/2023
#1
Solver是适用于您的方案的有效工具。
- 虽然它不能保证最佳解决方案（最小方差），但结果应该是令人满意的。
- 您可以根据需要调整 I 列和 J 列中公式中的偏移量;它们表示四舍五入天数的下限和上限。
- 通过扩大舍入天数的范围，您可能会获得更好的结果，但请注意，运行求解器需要更多时间。
- 使用我 PC 上的当前设置大约需要 90 秒。
Microsoft 文档：
评论
0赞
Alex H
11/15/2023
一个很好的解决方案，因为我以前从未使用过求解器，这绝对有效......但是，我没有提到的一个限制是我想将其添加到业务级模板中 - 因此假设它将被不知道如何使用 Solver 的人使用。在 VBA 中是否有可能，这样我就可以自动化它并使其证明白痴？谢谢
1赞
TehDrunkSailor
11/17/2023
#2
摘要：合并具有相同每日费率的作业。如果无法做到这一点，则您处于最佳（最小方差）解决方案。
这与其说是编程问题，不如说是数学问题，我当然无法给出正式的证明。但考虑一下四舍五入。您可以向上舍入，也可以向下舍入。这意味着，您可以将四舍五入差值添加到方差中，也可以将舍入差值添加到方差值中。四舍五入到最接近的整数（就像您当前所做的那样）也可以视为选择将最小方差添加到运行方差总计的值。每次四舍五入一个数字时，方差都会增加。因此，减少增加方差的唯一方法是减少舍入次数。
我能看到实现这一目标的唯一方法是将计算中共享相同费率的所有工作的总人工成本结合起来。这将需要更少的舍入，因为您现在舍入一次，而不是与共享相同比率的单个作业一样多。这将减少方差。
考虑每日费率为 233.33 的工作。如果将这些工作的总劳动力成本相加，则可以得到：
57,441.97
对于此计算，我们假设这是它自己的工作。现在，将其除以 233.33 的汇率，您得到天数：
246.18
这四舍五入为：
246.00 Note that we've just rounded once for all of those combined jobs!
Calculating the labor costs based on these rounded values:
(246.00 x 233.3) + (33.00 x 70.6) + (135.00 x 53.7) = 66,971.1
Which has a variance of:
67,063.17 - 66,971.1 = 92.07
评论