Excel电子表格在数学规划中应用.docVIP

Excel电子表格在数学规划中应用【摘 要】利用Excel的“规划求解”工具可以很好地解决包括线性规划和非线性规划在内的数学规划计算问题。本文以实际生产计划的规划问题为例介绍了在Excel中实现“规划求解”的具体步骤，建立数学规划模型的基本原则以及在表格中输入数据应注意的问题。 【关键词】Excel；规划求解；线性规划；规划模型 在实际工作中，如：生产与销售、人员调度、材料调配、运输管理等问题涉及很多的因素、无数方案需要就算、比较和分析，希望能够合理利用有限的人力、物力、财力等资源，得到最佳的经济效果，包括达到产量最高、利润最大、成本最小、资源消耗最少等目标。这些问题都属于数学规划研究的内容，具体包括：线性规划、非线性规划、多目标规划、动态规划、组合优化等，被广泛应用到各个领域中。除了一些专业化的软件如Matlab、LINDO等，Excel的规划求解工具是一个不需用户编程即可实现对数据进行因果分析的有效而简洁的工具。 规划问题归结起来一般分为两类：一是确定了某个任务，研究如何使用最少的人力、物力、财力完成它；二是研究如何在已有人力、物力、财力的有限资源下获得最大的收益。 1.规划模型的建立 在进行规划求解时首先要将实际问题数学化、模型化，也就是将实际问题用一组决策变量、一组用不等式或等式表示的约束条件以及目标函数来表示，这是求解规划问题的关键。其中，决策变量是指每个规划问题都有一组需要求解的未知数（x1，x2，…，xn），这组决策变量的一组确定值就代表一个具体的规划方案；约束条件是指对于规划问题的决策变量通常都有一定的限制条件，通常是用包含决策变量的不等式或等式来表示；目标函数是指每个问题都有一个明确的目标，通常是用与决策变量有关的表达式表示。 从问题提出到模型建立一般经历的过程为：寻求决策，即应回答的问题是什么；确定决策变量；确定优化目标，用决策变量表示的利润、成本等；寻找约束条件，决策变量之间、决策变量与常量之间的关系；构成数学模型，将目标以及约束放在一起，写成数学表达式。 实例：某厂制定下一年度生产计划。按照合同，第1季度到第4季度需要分别向客户供货80、60、60、90。该厂季度最大生产能力为150，生产费用为：，x为季度生产的数量。从函数可以看出生产规模越大，平均生产费用越低，若生产数量大于交货数量，多余部分可以下季度交货，但需支付每个15元的存储费用。如何安排各季度的产量，既保证完成供货合同，同时又使得各种费用总体最少？ Step1：需要回答的问题是什么？ A.各季度的产量如何安排？B.费用是多少？ Step2：决策变量？ 各个季度的产量，分别用x1，x2，x3和x4表示。 Step3：目标函数 目标：费用最小，总费用为生产费用与存储费用之和。 Step4：约束条件 交货数量约束与生产能力约束 Step5：数学模型 其中y为实际生产数量与交货数量的差。 2.“规划求解”工具的安装及数据、公式的输入 默认安装Office办公软件时，不加载规划求解工具，使用前必须进行加载。操作步骤有：Step1：打开“加载宏”对话框。单击菜单“工具”，“加载宏”，系统弹出“加载宏”对话框。Step2：选择“规划求解”复选框，单击“确定”按钮。安装完后，在“工具”菜单中会出现“规划求解”命令。 建立好规划模型后，就可以将规划好的模型基本数据和公式输入到工作表中，具体步骤如下： Step1：输入有关参数。在工作表B4：B7单元格区域输入1到4季度的应交货数量。在C4：C7单元格区域存放需要计算求解的各季度生产数量，可填入初始值与应交货数量相同。在G4：G7单元格区域输入生产能力限制数量。 Step2：建立计算公式。在D4单元格建立1季度生产费用公式：=80+98*C4-0.12*C4，自动填充到D5至D8单元格中。在E4单元格建立1季度存储数量公式：=C4-B4，在E5单元格建立第2季度存储数量公式：=E4+C5-B5，即等于1季度的存储数量加上2季度的生产数量减去2季度的应交货数量，并填充到E6和E7中，计算3季度和4季度存储数量。在F4单元格中输入1季度仓储费用公式：=15*E4，并通过鼠标拖拽方式填充到F5至F7中。在H4单元格中建立1季度可交货数量的公式：=C4。在H5单元格为2季度可交货数量：=E4+C5，即1季度存储量加2季度生产数量。将其公式填充到H6、H7单元格中，计算3季度和4季度可交货数量。在B8：F8单元格中输入计算应交货数量、生产数量、生产费用、存储费用合计的公式。最后在B2单元格中输入计算目标函数的公式：=D8+F8，即生产费用和存储费用总和。 从初始方案即按照应交货数量安排