信息技术应用 用Excel解线性规划问题举例教案教学设计导入设计

用Excel解线性规划问题举例

1．线性规划的一般定义

我们求解线性规划的思路是在满足所有约束条件的情况下，使我们所得出的目标函数可以求出到最优解.线性规划的一般定义如下： 对于一组变量xi(i1,2,3,L,n)，不仅要满足所有约束条件，而且使具有线性特征的目标函数得出最优解的一类优化性问题，称为线性规划问题.首先我们需要满足具有一定要求的约束条件，使用不等式或者等式来展示；其次要利用线性函数来描述我们的目标；最后尽量要有多个方案来选择，可以根据实际情况来找到最优方案. 线性规划的一般形式如下: max(或min)Zc1x1c2x2Lcnxn （1）

a11x1a12x2La1nxn(,)b1axaxLax(,)b2112222nn2Ms..taxaxLax(,)bmnnmm11m22x1,x2,Lxn()0(2)

其中（1）式为所求的目标函数，（2）式称为约束条件，xi(i1,2,3,L,n)称之为决策变量，ci(i1,2,3,L,n)

称之为目标函数的系数，bi(i1,2,3,L,m)称之为约束右端系数，aij(i1,2,L,m,j1,2,Ln)称之为约束系数. 2.用Excel中“规划求解”工具解线性规划问题

由普通高中人教A版必修5第3章第3节中，可知图解法求解含有两个变量x,y的线性规划问题，求解过程如下：首先作出约束条件表示的可行域（多条直线围成的平面区域），把目标函数zaxby整理成直线的斜截式方程yazx，运用数形结合的思想，当目标函数取可行域中的点时，把目标函bb数所表示的直线看成是斜率相同的一族平行直线（直线系），平移这些直线经过可行域，最先经过的可行域中的点或者最后经过的点，就是这条直线在y轴上的1

截距取最解的点，也是z取最值的点，即为最优解.图解法的特点是通过作图画出可行域和直线，过程清晰明了，易操作，简单易懂. 当决策变量较多时还可以通过单纯形方法进行求解，但存在的主要问题是运算量太大、计算过程太繁琐，还容易出现计算错误.如果运用Excel软件中的规划求解功能，就可以迅速并且准确无误的计算出结果. 例如：MinZ3x2yz2.5m2n

①

10.5x5.6y6.6z8.3m4.2n15.8(1)15x36.5y1.4z58.1m25.2n110.7(2)279x85.6y65z107m96n350(3)(4)0.6x1.4yz2m3n6360x30.4y28z49m30n101(5)

s..tx0(6)y0(7)z0(8)m0(9)n0(10)参考教材必修5第3章中信息技术应用阅读材料,本文用Microsoft Excel 2010工作表求解该线性规划问题,具体操作步骤如下: 1.进入Excel主界面后，点击左上角的“文件”选项，并在列表中点击“选项”,如图1所示.在弹出“Excel选项”界面后点击“加载项”，并在“管理”下拉列表中选择“Excel加载项”，然后再点击“转到”选项，如图2所示.在弹出“加载宏”对话框后,勾选“规划求解加载项”，再点击“确定”按钮，如图3所示，在菜单栏中的“数据”中将规划求解添加进来,如图4所示. 2.在工作表中一一输入变元、目标函数和五个约束条件,并将单元格B2到F2和G3分别作为变量x,y,z,m,n和最值Z的解,并在相应单元格中填入所需数据,如图5所示. 3.单元格G3中输入“=$B$2*B3+$C$2*C3+$D$2*D3+$E$2*E3+$F$2*F3”,表示3x2yz2.5m2n,在G4中输入“=$B$2*B4+$C$2*C4+$D$2*D4+$E$2*E4+ $F$2*F4”,表示10.5x5.6y6.6z8.3m4.2n,同理在单元格G5,G6,G7,G8中输入公式,如图6所示. 2

图

1选项界面

图

2加载项界面

3

图

3规划求解加载项界面

图

4添加规划求解命令到工具栏

图

5输入数据

图

6输入公式

4

4.点击单元格G3,点击“开发工具”,选中“规划求解加载项”,出现“规划求解参数”对话框,在“等于”栏中选择“最小值”,在“通过更改可变单元格”框中输入“$B$2:$F$2”,如图7所示.

图

7设置目标和可变单元格

5.点击约束条件中的“添加”,打开“添加约束对话框”,在“单元格引用位置”框中输入“$G$4”,在下拉式不等式符号列表中选择“>=”,在“约束:（N）”框中输入“$H$4”（表示约束条件10.5x5.6y6.6z8.3m4.2n15.8）,单击“添加”,同理实现其余约束条件的输入,即“$G$5>=$H$5”,“$G$6>=$H$6” ,“$G$7>=$H$7”,“$G$8>=$H$8”,如图8所示.

图

8输入约束条件

6.输入变元的要求“$B$2:$F$2=整数”（要达到此要求,需在下拉式比较符列表中选择“int”）,如图9所示.点击“确定”按钮后,结果如图10所示. 5

图

9各变量都为整数

图

10 所有线性约束条件界面

7.点击“求解”后得出计算结果，如图11所示.

图

11 求解结果界面

所以,当x0,y1,z1,m1,z1时，Z取到最小值，且Zmin7.5. 6

从以上求解过程可以看出，使用Excel表格求解线性规划问题,只需在Excel工作表相应位置输入相应数据,按照以上的操作步骤,就可以迅速的求解出结果. Excel表格对于中学生来讲比较熟悉,步骤比较简单、易懂,操作方便,是求解线性规划问题的首选工具.但是当问题较为复杂,数据较多时,要在单元格中一一进行输入，会比较耗时,工作量较大.使用Excel表格还可以解决多变量的线性规划问题和非线性规划问题,功能很多,有兴趣的同学可以参考Excel中的“规划求解”帮助菜单.

7