介绍Excel的一种奇葩玩法

1.   前言

工具服务于设计目标，工欲善其事必先利其器，在解决问题时多一种选择不是坏事，何况这个工具还很简单。

这里介绍的，是利用Excel的行数足够大（2016是100多万行）以及公式下拉方便的特性，把一些连续性计算问题，分解计算，得到一个近似结果的一种方法，截个图，给个大体印象（蓝色线条是义彦的生命值、橙色线条是章鱼的生命值的变化）：

2.   实际问题

海边突然跳出了一只章鱼在卡波依村里捣乱，造成了不小的麻烦，为了保护村庄，勇者义彦前去与章鱼战斗。章鱼的生命值为4000点，每秒输出伤害为20点。义彦的生命值只有2000点，每秒输出伤害为20点，显而易见义彦不会是章鱼的对手。

（1）村民们决定留下来帮助义彦，他们无法直接参与战斗，但可以为义彦祈祷。在祈祷的作用下，义彦的输出能力随着生命值的减少而上升，生命减少1%，输出能力就增加1%（也就是说，濒死情况下会增加100%的输出能力）。而章鱼的输出能力则随着生命值的减少而下降，生命每减少1%，输出能力下降1%（也就是说，濒死状态下输出能力为0）

请问：在祈祷的作用下，义彦能否战胜章鱼？在战斗结束之后，胜利者还能剩下多少生命？

（2）章鱼跳进了海里，一阵波涛汹涌之后，巨大的章鱼王出现了，章鱼王的生命值高达40000，使用单体伤害技能，每秒输出伤害为50。义彦现在已经完全恢复了。但村民开始逃亡，义彦不再获得祈祷的作用。义彦决定找几个志同道合的伙伴，如果伙伴的生命和输出能力都与义彦相同。

请问：至少需要几个伙伴，才能确保战胜章鱼王？

3.   背景

这个问题的解答，可以使用兰彻斯特平方律，列出微分方程组，计算后得到结果。兰彻斯特相关可在GAD搜索相关标题。这里只介绍如何用excel得到近似结果。

4.   解答步骤

打开excel

1:

在B1~E1分别输入xhp、yhp、xdps、ydps，分别代表义彦的生命、章鱼的生命、义彦的dps和章鱼的dps。并在B2~E2，输入相应的数值，如图：

2:

在A2~A152输入0~150，代表时刻

3:

输入公式：

·    在B3输入，=B2-E2，意思很简单，当前时刻，义彦的生命值xhp=上一个时刻的生命值减去上一个时刻章鱼的攻击力

·    同样在C3输入公式， =C2-D2。

·    在D3输入公式，=D$2*(1+($B$2-B3)/$B$2)，表示义彦每减少1%的生命值，就增加1%的攻击力。

·    同样的在E3输入公式，=E$2*(c3/$c$2)，表示章鱼的攻击力随生命值的降低而减少。

·    选中B3~E2，鼠标放到绿色选中框的右下角，当鼠标变为黑十字时双击鼠标，完成公式的下拉填充

·    然后得到0~150的各时刻义彦和章鱼的生命值及攻击力，可以看到在第136个时刻(战斗135秒后)，章鱼死了，义彦还剩下524的血量（解方程计算的结果是535.968，时间也是经过135秒，提高精度，可以把时间的间隔再减小，公式中是以1秒的间隔计算的）

·    对B，C列数据绘图，可以观察义彦和章鱼生命变化趋势。

·    同样的做法，略加改变，也可以通过图形观察的方式得到问题2的答案（n>=4，感谢Clown的指出，这个错误的答案是按章鱼4000血算的，纠正为40000血量的话，应为n>=10）

5.   该方法的推广：

在结合图表后，该方法可以实现更复杂的功能：

1.excel国际象棋打谱复盘，见excelhome链接：http://club.excelhome.net/thread-749124-1-1.html(这个例子的主要亮点在于动态图表，没有它，本文介绍的方法无法起飞)

2.不依赖VBA，纯函数实现战斗模拟器（难度比写VBA大），这个例子告诉我们，再复杂的问题都可以用函数来解决：

上图是12年写的3x4自动对战的战斗模拟器逻辑比常规的九宫格对战更复杂 —— 每各回合结束后，各行单位会轮换——请脑补2排火枪手，A排开火射击完毕退回后方装填弹药，B排上前射击，如此往复。类型不符，这个模拟器无法上传。。模拟器做的很丑，反正关注点也不在这里。