Excel制表技巧(49)函数应用.doc

Excel制表技巧(49)函数应用 ????????用EXCEL实现产销平衡下成本最优化 ???????在人们的实践中，经常出现各种运输活动。譬如，粮棉钢煤等物资从全国各生产基地运到各个消费地区；或者某厂的原材料从仓库运往各个生产车间；或各车间的产成品又分别运往成品仓库等等。这些运输活动一般都有若干个发货地点，简称产地；有若干个收货地点，简称销地；各产地各有一定的可供货量，简称产量；各销地各有一定的需求量，简称销量。那么，运输问题就是要在买足各销地的需求与产地产量平衡的前提下，如何组织调运才能使总的运输费用达到最低。本文通过实例运用Excel的规划求解功能进行运输问题的分析。? ????例：某地区有A1，A2，A3三座铁矿，每天要把生产的铁矿石运往B1，B2，B3，B4四个炼铁厂。各矿的产量、各厂的销量（百元/天）以及各厂矿间的运价如表1所示。问应如何组织调运才能达到产销平衡并使总运费最少？??? 表1??某地区厂矿运输问题的数据 ????解：运用Excel的规划求解进行管理优化分析的步骤如下： ????一、根据题意，设置本问题的决策变量和目标函数 ????设：Xij为每天从Ai矿运往Bj厂的矿石数量（百吨），Y为总运费，由表1及变量可以得出总运费Y=6X11+3X12+2X13+5X14+7X21+5X22+8X23+4X24+3?X31+2X32?+9X33+7X34? ????则本问题的目标函数为求minY ?二、根据题意及决策变量与目标函数得出本问题的线性规划模型? ????目标函数：?min?Y=?6X11+3X12+2X13+5X14+7X21+5X22+8X23+4X24+3?X31+2X32?+9X33+7X34? ????约束条件：X11+?X12+?X13+?X14=5?（满足A1矿的产量）???????????????????X21+?X22+?X23+?X24=2（满足A2矿的产量）???????????????????X31+?X32+?X33+?X34=3（满足A3矿的产量）???????????????????X11+?X21+?X31?=2（满足B1厂的需求量）???????????????????X12+?X22?+X32?=3（满足B2矿的需求量）???????????????????X13+?X23?+X33?=1（满足B3矿的需求量）???????????????????X14+?X24?+X34?=4（满足B4矿的需求量）???????????????????Xij?=0(i=1,2,3,j=1,2,3,4)(决策变量非负约束) ????三、根据上述约束条件构建Excel模型，如下图? ? 图2 ????其中单元格B4：E4分别为决策变量X11，X12，X13，X14?所在单元格；B6：E6分别为决策变量X21，X22，X23，X24?所在单元格；B8：E8分别为决策变量X31，X32，X33，X34?所在单元格；B11单元格为实际运价所在单元格，其公式“==SUMPRODUCT(B3:E3,B4:E4)+SUMPRODUCT(B5:E5,B6:E6)+SUMPRODUCT(B7:E7,B8:E8)”?。。 ??????四、根据上述规划模型进行规划求解参数设置，如下图? ? 图3 ????五、规划求解结果 ? 图4 ????通过上表可以看出，在满足产销平衡要求的前提下，从A1矿向B2厂运2百吨的矿石，向B3厂运1百吨的矿石，向B4厂2百吨矿石；从A2矿向B4厂运2百吨矿石；从A3矿向B1厂运2百吨矿石，向B2厂运1百吨矿石，才能使总运价最低，总运价最低为3400元。 ????备注：随着各矿、厂的资源或生产能力的变化，以及外界的运输价格等发生了变化，本题只需要把模型中的相关数据作一些修改就可以满足产销的动态平衡的基础上，实现成本最小化。 ???????尝试采用Excel探究自由落体运动的实现过程 ??????“探究自由落体运动”是通过打点计时器记录下重物自由下落的运动轨迹，根据其仅受重力作用，故猜想自由落体运动是最简单的变速运动即匀变速直线运动。作为一个让中学生自己探究的活动，如何才能做到既科学严谨又操作简单快捷地验证该猜想呢？笔者尝试采用Excel来帮助学生完成这个任务，下面是具体的实现过程。? 设计验证思路我们利用打点计时器记录自由落体运动轨迹时，得到的轨迹如图1所示。如何通过纸带的信息验证自由落体运动是匀变速直线运动呢？实验验证的方法很简单：通过测量出纸带上各点的位移，只要证明纸带上各点的位移与对应时间的平方成正比，或证明各时刻的速度均匀增加，便可证明自由落体运动是匀变速直线运动。 输入实验数据在Excel软件工作簿的某一列（本文为了方便介绍称此列为第一列）的单元