Excel练习——使用模拟分析表制作炮弹射表ˋ

昨天吃完晚饭，闲极无聊，随手用Excel写了一个抛物线弹道的计算公式。然后算了一下炮弹在不同抛射角度下的射程和最大射高。So easy，学过矢量分解和牛二定律的高一学生就能搞定，如附图1： 算完以后，很有成就感。就去冲了个凉。回来以后坐在电脑前继续欣赏自己的计算结果，忽然感觉就不对劲了。这个数值怎么感觉有点大呢？850的初速，最大射程居然去到了70多公里，最大射高居然也去到了30多公里。如果这个数是真的，那研究个毛的电磁炮和防空导弹啊？弄些二战时期的的88炮都可以秒天秒地秒空气了嘛。不对不对，一定在什么地方出了问题。 想了想，应该是遗漏了空气阻力的影响。于是万能的度娘贴心的为我送上了空气阻力计算公式，如附图2： 然后好玩的事情来了，我发现空气阻力与炮弹飞行速度的平方成正比。炮弹速度与炮弹的减速度有关，而炮弹减速度由于炮弹收到的空气阻力有关。更分特的是，这个空气阻力的方向是时刻变化着的，与炮弹飞行方向相反——这是要布列微分方程求积分的节奏啊！！！当年高数课上打瞌睡的学渣流下了悔恨的泪水。。。 还好车到山前必有路。既然咱不会积分，咱就勤快点，多算几个数好了。用Excel，使用迭代大法。列下一个包含50多万条公式的表格后，终于得到了考虑空气阻力的弹道模型，如附图3： 好不容易把空气阻力搞定了，赶紧的用这个模型来算算这回炮弹能够打多远。因为空气阻力同炮弹的形状大小 ，为了简便，就假设咱弄到了一颗5kg重的球形铁炮弹，一样以850的初速，75的仰角打出去（PS：咱也不知道这是啥炮，这么高的初速，这么大的仰角，却只发射一枚球形铁炮弹）。炮弹划出一道弯弯的弧线，准确的命中了3512.99米处的目标。漂亮。这个漂亮的弹道，快把这条弹道的各种参数给展示出来啊，如附图4： > 第一张图，横坐标轴是炮弹在水平方向飞过的距离；蓝线是炮弹的弹道。其弹道各点的高度在左边纵坐标轴上可以读出。红线是炮弹的飞行时间，炮弹飞到各位置所需的时间在右边纵坐标轴上可以读出。图上还列出了炮弹击中目标时的时间：炮弹出膛后52.43s > 第二张图，横坐标轴是炮弹的飞行时间，纵坐标是炮弹的飞行速度。图中蓝线是炮弹飞行速度值；灰线是炮弹飞行速度在水平方向上的分量；红线是炮弹飞行速度在竖直方向上的分量。当这个数为正数时，表示炮弹仍在向上飞行。当这个数为负数时，表示炮弹在向下飞行。当这个数为0时，表示炮弹飞到了弹道的最高点 > 第三张图，横坐标是炮弹的飞行时间，纵坐标是炮弹的减速度。其中蓝线是炮弹减速度在竖直方向上的分量，其值可以在左边坐标轴上读出；红线是炮弹减速度在水平方向上的分量，其值可以在右边坐标轴上读出。值得提一嘴的是，从这三张图可以看出，炮弹在出膛的瞬间，其减速度高达400+m/s2。在出膛1s后，其速度就降到了550m/s左右。可怕的阻力！ 到现在为止，这个炮弹的弹道模型就已经做出来了，完美！但一旦吃得太饱啊，人就发油胀。（袁老说：怪我咯[可怜]）。看着这个模型，就想整点幺蛾子。这个模型一次只能计算一个出膛速度和一个抛射角度下的弹道。那么如果我想知道在不同出膛速度和不同抛射角度下的射程，有什么更好的办法吗？因为几个角度和速度排列组合起来，如果都是一个一个手工输入的话，那个工作量大得吓人。作为一个处处追求以最小的投入取得最大成果的无敌懒鬼的我，实在不喜欢这种解决方法。有没有更好的方法呢？我找啊找啊，“数据”——“模拟分析”——“模拟运算表”，对！就是你了。 于是，一个二变量的模拟运算表就被设计出来了。这张表涵盖了出膛速度为50~850m/s，抛射角度为1~90度范围内各种组合的最大射程。为了方便查找，使用颜色梯度对数据进行了标识：颜色越红，表示射程越远；颜色越蓝，表示射程越近，如附图5： 有这这张表，那对于射程范围内的任何一个目标，就可以找到一个最合适的出膛速度以及抛射角度的组合，使炮弹准确的砸到它的上面。 够了吗？肚子里还有一点食没有消完的我说：不够。我还想看看能不能得到更多的信息。比如说，最大射程以及对应的抛射角度同出膛速度的关系曲线。这个倒是比较容易，因为这些信息都已经包含在之前那张模拟运算表中了，只要用合适的函数把它们找出来就好了。只是在这里被查找的数列位于对应数列的右侧，因此不能使用vlookup函数，而要用index+match的组合函数来实现这个查找功能。最后得到了不同出膛速度下的最大射程以及对应的抛射角度曲线，如附图6： 图中，横坐标轴是炮弹出膛速度，蓝线是炮弹最大射程，其值在左侧纵坐标轴上读出；红线是最大射程时对应的抛射角度，其值在右侧坐标轴上读出。从图中可以看出，随着出膛速度的增加，起最大射程对应的抛射角度在逐渐降低。但这个角度在37~38度附近似乎又一条渐近线的存在。 好了，本来只是想随手写个抛物线给公式玩玩，没想到竟然扯出了这么一大串的无聊玩意。看来真的是袁老让我吃得太饱了。这时，我又看到模型中其它几个变量也开始跃跃欲试了，像空气密度（与温度以及气压有关，可以做高原射表），炮弹重量，炮弹形状等等。不过算到这里，肚子里的食已经消得差不多了。时间也不早了，赶紧睡觉去了。不然明天上班就得腾云驾雾了。。。