Excel规划求解在数学建模中的应用

Excel规划求解在数学建模中的应用

作者：丁婧琦李静丁亚军

来源：《电脑知识与技术》2017年第17期

摘要：该文介绍了数学建模的步骤和线性规划求解手工计算的过程。当模型复杂、变量和约束条件较多时，计算工作量较大，且更容易出错。该文通过实例探讨，应用Excel的规划求解功能可直观、简捷地解决线性规划、定值和方程组问题等的求解。

关键词：数学建模；Excel；规划求解

中图分类号：TP393 文献标识码：A 文章编号：1009-3044（2017）17-0092-02

高中数学必修5教科书中介绍了简单线性规划问题。线性规划能解决生产成本控制、产品利润最大化、运输线路最佳选择、经济管理等方面的实际问题。解决问题的根本在于建立相应的数学模型，其步骤为：

①根据问题所要达到的目标，确定决策变量。

②由决策变量和所要达到目标之间的函数关系确定目标函数。

③由决策变量所受的限制条件确定决策变量中所要满足的约束条件。

教科书中通过具体实例介绍了线性规划问题的图解法，线性规划求解的解题思路可归纳为画、移、求、答，具体过程为：①画：画出约束条件（不等式组）所确定的平面区域和目标函数所表示的平行直线系中的任意一条直线。②移：将直线平行移动，以确定最优解所对应的点的位置。③求：通过解方程组求出最优坐标，代人目标函数求出最优解。④答：作出答案。

学校要求我们进行研究性学习，运用所学线性规划知识解决一些实际问题。在实际问题解决过程中，计算过程繁琐、计算量较大。作者对人教版数学必修5中所介绍的Excel规划求解进行研究学习，发现其不仅能解决线性规划问题，还能解决非线性规划、定值问题和方程组求解问题等。

1 Excel规划求解简介

“规划求解”是Excel规划求解加载宏的简称。借助“规划求解”可对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。在模型求解过程中，可以对“规划求解”中的可变单元格数值应用约束条件，约束条件可通过“规划求解”中的限制条件来设置，约束条件可以引用其他影响目标单元格公式的单元格。使用“规划求解”可通过更改其他单元格来确定某个单元格的最大值或最小值。规划求解可以用来解决线性规划与非线性规划优化问题。

2 Excel规划求解操作步骤

利用Excel规划求解分析工具求解问题的步骤如下：

①建立问题的数学模型。

②根据数学模型规划建立Excel工作表。

③设置规划求解参数。参数包括：设置可变单元格（确定决策变量）；设置目标单元格（建立目标函数）；设置约束条件（约束条件可以用线性等式或不等式表示，还有非负约束（≥）和整数约束（int））。

④单击“求解”按钮，进行求解。

3 Excel规划求解实例

3.1定值问题求解

通程商场国庆节搞购物满1000元送1000元购物券的欢乐购活动，小丽看中了商品A（17元），商品B（28元），商品C（53元），商品D（65元），想选这些商品各若干件（>=3）刚好拼凑1000元以便领取1000元购物券，琢磨了很长时间，没有成功。现在我们通过计算机来帮助小丽解决其烦恼，其过程如下：

①先建立数学模型（17*a+28*b+53*c+65*d=1000），在Ex-cel工作表中建立如图1所示的表格。

②在D2单元格中输入公式“=B2*C2+B3*C3+B4*C4+B5*C5”（将C2：C5单元格作为未知数a，b，e，d）。

③在“数据”选项卡中单击“规划求解”选项，打开如图2所示的对话框。在对话框中设置目标为$D$2，单击目标值，在文本框内输入1000。在可变单元格中输入$C$2：$C$5，单击“添加”按钮，添加两个约束条件：$C$2：$C$5=整数，$C$2：$C$5>=3。

④单击“求解”按钮，即可得到如图3所示的结果。

3.2利润最大化问题

某工厂想生产拉盖式书桌和普通式书桌各若干张，每张拉盖式书桌需用松木10根、雪松4根、枫木15根，普通式书桌需用松木20根、雪松16根、枫木10根。现有松木200根、雪松128根、枫木220根，每张拉盖式书桌利润为115元，每张普通式书桌的利润为90元，问怎样生产才能使得工厂的利润最大？

分析：建立数学模型。设生产拉盖式书桌x台，普通式书桌y台，总利润为Z元。目标函数为：z max=115x+90y。

求解过程如下：

①根据数学模型在Excel工作表中建立如图4所示的表格，B2、C2单元格为两种书桌生产数量。

②分别在E2、E3、E4单元格中输入公式“=B2*B6+C2"C6”、“=B3*B6+C3*C6”、

“=B4*B6+C4*C6”，在B7单元格中输入目标函数公式"=B5*B6+C5*C6”。

③在“数据”选项卡中单击“规划求解”选项，打开规划求解参数对话框。在对话框中设置目标为$B$7，选择最大值，将可变单元格设置为$B$6：$C$6，添加约束条件：$E$2

④单击“求解“按钮，即可得到如图5所示的结果。

3.3方程组求解问题

利用Excel规划求解也可以解方程组。下面以"Excel规划求解”解方程组为例来介绍其操作过程。

过程如下：

①根据方程组规划Excel工作表如图6所示，A2：A4单元格对应变量x、y，C2：C4单元格对应于三个方程的值。

②分别在C2：C4单元格中输入公式“=A2+2*A3-4*A4”、“=5*A2-A3-3*A4”、“=6*A2+A3-A4”。

③在“数据”选项卡中单击“规划求解”选项，打开规划求解参数对话框。在对话框中设置目标为$C$2，选择目标值，在文本框内输入9，将可变单元格设置为SA$2：$A$4，添加约束条件：$C$3=7，$C$4=22。

④单击“求解”按钮，即可得到如图7所示的结果。

4结束语

通过本次研究性学习，了解了Excel规划求解不仅能解决线性规划问题，还能解决非线性规划、定值问题和方程组求解问题等。在探究实验过程中，充分体验了“从实际问题到数学问题”的建构过程，“从具体到一般”的抽象思维过程。学会了分析问题方法，提高了利用计算机