半自动化的目标责任成本造假思路与一个成功的解法，还有一些优化方案。

：今夜月色真美1
：风也温柔！

0.起因

起因是最近不忙，然后加之前段时间研究了selenium自动化浏览器的操作，所以自动化工作的想法又一次搬上了台面，

1.思路

1.1 数据处理

1.前置清单数据处理之前出过一篇教程，点击这里查看
2.利用xlwings代替VBA去进行数据处理
2.1目前xlwings需要自己输入的数据量还是很大，后期我会优化到利用excel作为前端数据交互页面

3.目前碰到的问题（也是我耗时最多的部分）
3.1 a方案我尝试用随机数去暴力撞数，结果很不理想，执行效率虽然高，但是随机范围太大，如果找到自己需要的数，需要控制好随机的范围。由于实在计算不出 ，测试两天后放弃。我只用了random，当然有想法的可以尝试用fake库来解题
3.2 b方案是利用算法自己去算，但遇到的问题也不少。最好是就是浮点数的计算问题，后来我引入numpy但计算依旧很缓慢，尤其是使用while和for嵌套循环后执行效率很低下。

b方案下失败的代码合集；

3.3 c方案是目前的半自动方案，依靠人工控制好不重要的人工费、机械费、专业分包、以及其他费用等输出。然后利用excel单变量求解解出最后的材料费。
关于excel单变量求解功能在目标成本文件编制中的应用我写过一篇文章可以看着：点击这里查看

这个方案的问题就是人工部分还是有的，无法做到完全自动化，而且单变量求解过程是类似穷举法的规则，只不过我们只穷举材料费部分的值，其他的值我们先给定。最后再分配。

1.2 关联docx

1.在研究关联docx中其实很搞笑，我最开始想利用word中的域代码来关联excel中的指定单元格的值。
word:link域代码官方文档
实际在使用过程报错很频繁，故放弃。

2.之后本来想利用python-docx，但由于之前使用过python-docx的经验，如果你用特殊字符替换文本的方式，python-docx的逻辑是基于段落的，也就是你要处理一大堆的段落，本质上不够方便。

3…最后我在网上冲浪的时候看到docx-maimerge这个库，这个库是利用域代码的功能，然后利用docx模板文件来对应域代码的字段输出文件。这是目前比较靠谱的方案。

1.3 联动浏览器自动输入

这部分内容目前处于构想阶段，毕竟PM系统的自动化脚本我还在慢慢摸索，可以说的事，selenium是比较靠谱的方案。
之前帮同事处理PM数据汇总的时候我发现selenium才能完全应对PM系统后端一堆的JS。

2.半自动化成果展示

这里还是的说明xlwings这个库的厉害的地方，在于它能实时的与excel做交互，而且编程的过程很自由，你可以用excel本身的公式和python代码的优势联合进行编程，这就给你的解题带来了极高的宽容度，最开始我在使用pandas以及numpy的时候发现倒来倒去，虽然他们的计算效率很高，但实际上我们编制的表格不需要那么彻底的计算效率，我们只需要联动excel自有的功能，比如公式，比如VBA，然后在加上python左右一种锦上添花的作用。
接下来将展示整个运行的过程，将编制的文件的时间极大缩小。

2.1 跑起来吧，成本编制。

1. 展示基本的数据模板状态

• 蓝色部分表示由代码自动运行的部分
• 黄色部分表示自己需要在表上手动操作的部分
• 其余白色部分已经由excel的公式形成了强大的链接
  
  
  这里需要手动计算的主要原因是需要手动分配一下周转材料费，需要分配到方木模板钢管的比例值而已。
  
  
  
  
  

2. 运行过程（控制：税赋率和利润率）

看过前面的模板之后，其实代码的部分需要输入的内容基本也就理解了。

本质上我只是把需要输入到excel中内容转移到代码上，后期我会修改成excel的交互表单，就是你在excel中填就行，交互上会更方便。

接下来我运行脚本，蓝色部分的内容将自动计算完毕。代码的逻辑我就不解释了，我会放在下面大家有兴趣自己去研究。我这个解法其实很傻缺。
简单的说就是，人工费我直接固定等于了清单人工费，然后措施费和机械费我按照自己设定利润空间下浮，剩下的材料费用来控制目标成本比较关注的利润率和赋税率。

当蓝色部分计算完毕时候，我们会发现我们控制的值：税负率和利润率，远远没有达到我们想要的值。

税赋率9.64%，而且黄色部分的材料费并没有分配完毕。

利润率高达77%并不是我们想要的，而且材料费还缺失

3. 手动操作的部分（做好7.1表）

由于还存在材料费未分配，所以我们需要推算材料的值。

3.1 先推最终的目标成本值

由于利润率的限制，我们的成本值其实是固定，那么假设我们把计算的利润率定在5.6%。只有就能的出汇总表中目标责任成本的目标值其实是？如下图所示。

接下来我们需要利用excel的单变量求解功能，并利用材料费中税率最高的值来推断。为什么要用最高，因为最高的值意味着他是这个工程极限值，成本税越高代表抵扣越多，所以纳税最少。如果极限值都够不着税赋率的话，那么数据可能需要重新审定。
利用单变量求解的操作过程如图：

3.2 之后推算税赋率

由于之前我们计算出最大值的目标成本值是这么多，所以我们还需要看税负率和不合规。很显然，0.21%是不合规的，所以我们需要进行分配。
这个分配的过程其实就是穷举，你仔细观察材料费的值，可能也就13%和3%的税率比较多，比较少见的9%和6%，那么最大值的13%明显可以继续向3%分配，假设13%和3%的55开这部分的值会怎样，或者37开会怎样，这些你都可以去试。
这里我用成功的方案，13的分配69%,3的分配31%。

这里你可以发现虽然税赋率达标了，但利润率差了点，这里我们对数据可以在进行微调，利润率低说明成本高了。我们可以降低，但降低哪个值呢，13%还是3%,我这里推荐降低3%的，因为思路上你降低的税越小的，税赋率变化越小。
这里我选择降低0.05%的3%的值，即分配30.5%的值给3% 、分配69%的值给13%。

这下子数据就达标了，至此文件基本编制结束。

3.联动docx的效果

联动docx我利用的是docx-mailmerge这个库，语法简单，但在这里效果很好。
这个库的好处在于你可以把现成的文件当作模板，然后在需要插入指定的数据的地方用域代码做标记。
我们把公司提供的文件作为模板文件，并插入mergefiled的域代码。
接下来用mailmerge模块的与之联动，最后输出的文件就是我们想要的格式。

利用这个东西其实剩下的成本管理书以及施工前策划商务部分的内容都可以类似的同步过去。

就写先这么多把，睡觉了。