关于怎么用Excel的规划求解来安排人手

写在前面：本帖写给对庄园人力资源配置的优化有所追求（又跟我一样有空），同时爱好或者有意用Excel来统计和记录主神皇冠游戏内容进程的同好们（假如有的话），做Excel和分享的过程都是我的快乐源泉，希望能够遇到同好能够在技术上互相启发。也希望游戏乐趣来源不同的大家能从求同存异的理念出发看待本帖，那么不胜感激。

（20220917）更新效果图

更新点：

1.增加了辅助行，优化了最末行的函数subtatol在表格变化时（主要是新增成员后）的自动更新，同时起到辅助HLOOKUP函数返回工作去向的作用。也就是在辅助行上插入新的庄园人手时，不必更新末行总计的公式（但是末列总计仍需要手动更新，暂时没想到好的自动更新的路数）

2.增加了工作地点自动显示的功能，以及对应帮助检索的条件格式。这样子在安排人手的时候更便于查看。

新增功能涉及知识点：Hlookup函数 ，offset函数，条件格式，数据验证（可有可无）。

自动返回工作地构建：

1.建立辅助行，即在求解表可变范围和庄园工作人数求和行中间插入新行。在求解表新行中粘贴与首行对应的庄园建筑名称，在面板表新行中各数据单元格输入0。其他格姓名、出生、年龄参照庄园成员格式。

2.HLOOKUP函数构建：从P2输入=HLOOKUP(1,B2:$G$16,N,FALSE)，填充到最后辅助行，然后逐格修改N，N代指辅助行在函数内涉及的区域（BX:$G$16）的第N行，将各单元格N修改为数值函数即可正常运行。

3.条件格式构建：作用是在Q1输入“矿场”时，对应在矿场工作的员工姓名会显示对应条件格式。设计公式型函数在前面更新里有说明。在Q列新建公式型条件格式=offset(Q2,0,-1,1,1)=$Q$1，然后设置自定义格式，应用即可。

R列则是=offset(Q2,0,-2,1,1)=$Q$1,都是对应指向自动更新所返回的P列。S列则是-3。反正也没人看，没写细，需要的话可以企鹅534881770要excel原文件或详询。

———————————————

（20220903）先上效果图

规划求解的求解思路是使（每个建筑产量加成的和）最大化。其中图左求解表是进行规划求解的变量表格，图右面板表是录入（庄园特性和成长潜力）较高的面试者的产量加成面板，两表主数据具有对应关系。

实践意义：打发时间，以及自主学习带来的快乐。

设计到的Excel处理包括：规划求解，条件格式，和各种函数公式。

函数涉及：sumproduct、sum、offset。

可能需要涉及的前置外在条件如

（1）有Excel或者wps的电脑/其他设备

（2）将“规划求解”加载项添加到工具栏

（3）安排庄园人手时要人口普查或者每个潜在员工成年后登记到表格

（4）不同员工的名字具有辨识度

（5）时间

需要截图中的原本文件的可以+企鹅534881770直接索取以及咨询（毕竟这个东西讲起来还是挺复杂的，不如大家直接看源文件省事）

可能涉及的基础理论或公式

（1）实际产出效率受工人因素加成的公式，此处不明，简单将员工特性的产量加成与潜力转化为对应建筑的产量加成相加。

建表步骤：

1.信息录入

潜在工人信息录入面板表，内容包括:姓名，出生时间（用来求出年龄，根据年龄找人），对应庄园建筑的加成（特性加成+潜力加成）。格式参考上文截图右边面板表。

2.规划求解表格制作

其成表如下图：

相对于面板表，求解表增加的工作总数列和工作人数行是作为规划求解的限制条件。因为一个人不能再两个庄园工作，一个庄园能容纳的专职工人也有上限。需要输入内容如下，工作总数列在H2格输入=sum(B2:G2)，然后向下填充到最后一个工人的那一行H16，工作人数行在B17格输入=subtotal(9,B2:B16)，然后向右填充到最后一个庄园建筑那一列G17，剩下表格的最右下角H17，要输入=sumproject（B2:G16,K2:P16）,函数内的两个区域，也就是（求解表中的空白区域和面板标准中的特性加成值区域）大小要一致。

（其中有一些可以参考的小技巧：建议人口按从上到下，从年轻到年老排列，以便插入新行时从第二行插入，subtotal函数随之变化，不用更新；录入面板表时较低的加成值可以忽略填0）

3.规划求解

首先要在Excel加载规划求解这个功能，才能使用。“文件”—“选项”—“加载项”—选中规划求解加载项—“转到”—选中规划求解项—“确定”，不同版本的方法可能不一样，大家可以自行摸索或者百度哈

加载完成后应该是在“数据”选项卡可以找到“规划求解”，点进去截图如下

如图目标设置为求解表的右下角单元格H17（sumprojet函数的表格）

选择求“最大值”，可变单元格设置为求解表里的空白区域（B2:G16），约束点右边添加进行添加。假如你的庄园等级是3，专职工人人数是2，那么设置代表各建筑岗位人数的“工作人数行”B17:G17"<=2（根据大家各自的专职工人数或者工人上限设置）。设置代表工作总数的工作总数列“H2:H16”<=1。而设置可变单元格“B2:H16”int整数即可，如下图，点确定。最后求解方法下拉选择单纯线性求和，点求解，点确定。

得出解如下图

得出解但是很难解读，所以做一个条件格式，来高亮所需数据帮助解读。

4.条件格式

选中B列到G列，“开始”选项卡—“条件格式”—“突出显示单元格规则”—“等于”—输入1，确定。求解表的高亮做好了如图，面表板的高亮复杂一点涉及到函数offset。

选中K到P列，“开始”选项卡—“条件格式”—“突出显示单元格规则”—“其他规则”—选中使用公式确定要设置格式的单元格—输入=OFFSET(K2,-1,-9,1,1)=1如图

(PS:其中函数中的“-9”代表面板表左移九列时，和求解表一一对应，如果真有朋友进行尝试，途中又进行了自己的再创造，可能得改变这个函数，欢迎留言讨论)，格式主要是设置大家喜欢的填充颜色和字体格式，我设置的是亮蓝色，得出结果就和开头的结果图一致。图就不重复放了

5.排班

最后就是看图排班，因为每次重新排班，可能会有不同建筑间的人员流动。相应的有些建议。

1.排班之前把所有建筑工人卸下。然后重新安排

2.根据年龄找特定的队员是我目前所知效率最好的庄园找人方式了如图。ps：如果还有不知道点右上角切换到年龄找人的话。

大概是这样。如果反响好的话，考虑分享一个前期可用的起名方式给大家，主要就是用不同的姓和名和前缀代表不同的血统、纯度和潜力。