excel - 基于当前统计信息的准确，实时的配电预测更新解决方案

Question

作为我当前项目的一部分，我需要为以下各项找到一个漂亮的Excel解决方案：有一个在线游戏，人们可以在这里获得经验（类似于SO代表），并在获得足够的经验后立即进入下一个级别（再次，这是非常像SO特权）。当然，每个人都需要不同的时间：有些人在第一周获得了足够的经验，其他人则需要5周才能通过该水平。统计信息如下：

Week from join: Week 1  Week 2  Week 3  Week 4  Week 5
Level up:       5%      15%     40%     30%     10%

就是说，如果在这个星期我们有一些人加入了游戏，那么所有的人将在5周后进入一个新的水平（有些人会放弃并离开，但为简单起见，我不将该条件包括在条件列表中） ）。

对于每场比赛，当前的球员人数都很重要。为了保持游戏的流行性和趣味性，每个级别上必须有最少数量的玩家。显然，这种价值正在不断变化：新人来了，而其他人则进入了新的高度。但是-这就是行动开始的地方！ -当前玩家的最低价值不是恒定的，可能会因许多因素而有所警惕。为简单起见，这意味着在未来的10-15周内，应该每周有一组预计的最少玩家数量（这是基于收集的统计数据，但是对于给定的任务，这是已知的一组）。

到目前为止，图片看起来像这样：



虽然最左边的列是每周加入游戏的人数，但每周下方的黑体字数是本周进入下一级别的人数。

有了新来者的数量，计算每周总的最终水平提升是微不足道的。相反的事情并非那么容易（嗯，至少对我而言）-这是一个问题：如何预测每周所需的新移民人数（最左边的一栏）-获得所需的人数下周的下一个级别（底部的粗体行）？

到目前为止，我仍然无法找到合适且准确的解决方案2周。我尝试了所有可能的选项-从复杂的＆数组公式到Solver插件，但没有运气：我总是比方程式拥有更多的未知数。但是，我很确定这不是一个不可能完成的任务：对于某些解决方案，Solver可以提供与期望值相当接近的数字，但不能为每个期望值集提供数字。

一些假设和（可能）重要条件：


虽然应该没有问题，但是实际的每周分配%%%的数字会根据需要通过数据库查询和进一步的Excel处理进行更新。
到目前为止，从数据库获得的实际新人值当然被视为方程的已知值。显然，它们无法更改，例如如果实际上有一个星期有多少人加入了游戏，那将影响未来几周实现总体目标的计算预测。
在特定时间段内，实际传递到下一个级别的玩家总数应保持不变：如果我们希望从1月开始的每个冬季工作周中，至少有100人参加该游戏，那么我们有8个完整的星期，因此总共有800个游戏玩家。但是，如果在1月份实际上每周有80名新玩家-这意味着2月份会辛苦工作。）当然，“检查点”小计每10周计算一次。无论如何，任何低值都必须在接下来的几周内进行补偿。
反之亦然：如果我们的新移民人数超过给定一周的预期人数，则在下个星期我们可能会吸引更少的球员来达到下一个小计目标。
玩家的流动是无限的-图片样本仅出于清晰和更好地理解的目的而给出。将来可能会根据需要计算所需的值-预测模式是可重复的。如果这对解决方案很重要-请放宽10星期。
纯配方溶液是可取的-甚至非常复杂。但是，VBA仍然是可接受的选项。在任何情况下，准确的实时/按需更新计算值绝对比简单但半手动或近似的解决方案好。


至于我在这里的每个问题-我都不要求 less than desired解决方案，但是非常感谢您提供正确的指导或宝贵的建议。我故意不将此问题发布到 https://stats.stackexchange.com/上，它更像是编程问题，因为理论显然很简单……或者可能太简单了，所以我看不出很明显的解决方案。

请随时通过评论要求提供任何其他详细信息或解释：尽管我尝试尽可能简洁明了，但对我而言并不重要的某些方面实际上对于准确的解决方案至关重要。

样本文件（带有上面的样本）： https://www.dropbox.com/s/q3bnagyet86i34r/StatPrognosis01.xlsx

Answer 1

不需要VBA或复杂的数组公式-您只需将一些简单的数学运算转换为公式即可：

主要思想是，您可以通过“逆转”计算来确定每周的数量。逻辑如下：（注意：基于屏幕截图的单元格引用，即黄色5％为B2）：


如果一个星期的“加入者”中有5％达到下一个级别，并且第1周（总体）应该有10个人参加，那么第一周需要10/5％= 200个人加入。
作为公式，这意味着：B5：=B12，A5：=B5/B2
在这里，您可以计算出从第一周开始的入职者的分布情况（即第5行的其余部分没有变化）
在第2周，您希望35个人成为下一个级别。从上一步开始，您知道从第1周加入者起，您就有30个人达到了该级别。因此，第2周参加者必须有5个人。当它们再次产生该波的5％时，总波必须为5/5％= 100
在公式中：C6：=C12-C5，A6：=C6/B2
...


现在，您不必为每个星期创建一个单独的公式，而可以使用一些INDEX函数对此进行概括。为此，我将您的示例扩展为星期数，同时包含两个标题列和行：



要计算每个“每周连接波”的数量，即范围C5：O17，请使用以下公式：

= IF（$ A5> C $ 4 + N（“这波浪中没有人加入：”），0，
IF（C $ 4 = $ A5 + N（“新浪潮的第一周：”），
IF（$ A5 = 1 + N（“本周必须非常期待第一波浪：”），C18，
N（“所有其他星期，这必须是预期的数字减去
前几周的“升级”：“）+（C $ 18-SUM（C4：C $ 5））
），
N（“对于加入后的任何一周，请应用每周的升级率
总人数：“）+ INDEX（$ C $ 2：$ O $ 2，C $ 4- $ A5 + 1）* $ B5）
）


对于每周所需的木匠人数，即范围B5：B17：

= INDEX（$ C5：$ O5，$ A5）/ $ C $ 2


这是更新的solution file！