用Excel解决简单线性规划问题

1、用EXCEL求最值华东师范大学03级教育硕士江苏省溧阳市戴埠高级中学潘晓春摘要)介绍了用Excel软件的规划求解功能解决一些常见的求最值问题的方法。主要从一元函数的最 值、线性规划和二元函数的最值三个方面去进行探讨。关键词)Excel规划求解最值最值问题是生产、科学研究和日常生活中常遇到的一类特殊的数学问题，是高中数学的一个 重点,它涉及到高中数学知识的各个方面，解决这类问题往往需要综合运用各种技能。Excel软 件中的规划求解功能将为这类问题的解决提供了一个很有效的方法，而且适用范围较广，具有 很强的实用性。用Excel解线性规划，必须在Excel系统中加载“规划求解”项目，如果没有，可以启

2、动 Excel软件，进入Excel用户界面，然后使用“工具”菜单下“加载宏”菜单项的“规划求解” 子项，则可完成“规划求解”项的加载。本文将从以下三个方面来介绍用Excel中的规划求解功能进行最值的求解。一、一元函数的最值求函数的最值是高中数学中的一类常见问题，也是高中数学中的一个重点和难点问题，运 用Excel中的规划求解功能能够很快捷地进行求解。例1. 求函数y = x2 -2x + 2 +、jx2 - 10 x + 29的最小值建立规划求解方案与求解的的步骤如下:在Excel工作中表选定B1单元 中的数据作为自变量x，在B2单元格中 输入目标函数公式 =SQRT(B1*B1 -2*B1+

3、2)+SQRT(B1*B1-10*B1+29)”；选中B2，然后进入菜单栏上的 “工具” |“规划求解.”,在对话框中输入如下内容(如图1):将“设置目标单元格” 设置成“$B$2”，并设置成最小值；可变单元格设置成“$B$1”，单击求解；得出如下内容(如图2)：单元格$B$1的值为2.333333,单元格$B$2的值为5,所以当时，f (x) =5min运用这一方案，可以解决一元函数的的最值，也可以解决一元函数给定区间内的最值问题。例2.求函数f (x)= 3x +12 (x e 1,8)上的最值 x建立规划求解方案与求解的的步骤如下：(1)在Excel工作表中选定B1单元中的数据作为自变量

4、x，在B2单元格中输入目标函数公式 =3*B1+12/B1”；（2）选中B2，然后进入菜单栏上的“工具”|“规划求解.”,在对话框中输入如下内容（如图 3）:将“设置目标单元格”设置成“$B$2”，并设置成最小值；可变单元格设置成“$B$1”；添加图4图3约束条件 “ $B$1=1”；单击 求解；图4图3（4）得出如下f (x) . =12内容（如图4）：单元格$B$1的值为2, 单元格$B$2的值为12f (x) . =12二、线性规划线性规划是高中数学中的一个重要内容，求线性目标函数在线性约束条件下的最大值或最 小值的问题。高中对线性规划问题的解决一般都是采用图解法，这里将运用Excel中

5、的规划求 解来解决。例3.设z = 600 x +1000y，式中变量x、y满足下列条件10 x + 4y 3005x + 4 y 2004x + 9y 360，求z的最大值x z 0y z 0建立规划求解方案与求解的的步骤如下:5（1）在Excel工作表中 选定B8单元格中的数据作 为x，B9单元格中的数据作5为y，在单元格B2:D7分别输入目标函数和约束条件 的系数，在单元格E3中输入 =B3*$B$8+C3*$B$9”，并用填充柄拉至E7,在B10图图6单元格中输入目标函数公、图6式 =B2*B8+C2*B9”；（2）选中B10，然后进入菜单栏上的“工具”1“规划求解”, 在对话框中输入

6、如下内容（如图5）:将“设置目标单元格”设置 成“$8$10”，并设置成最大值;可变单元格设置成“$B$8:$B$9”； 添加约束条件 $E$3=$D$3 ； $E$4=$D$4； $E$5=$D$6； $E$7=$D$7”；单击求解；（4）得出如下内容（如图6）：单元格$B$10的值为41931， 单元格$B$8的值为12.414，单元格$B$9的值为34.483，所以当 x = 12.414，y = 34.483 时，Zmax= 41931。图7如果在例3中添加 x, ye Z时，我们只须在上 面的求解中添加约束条件: “$B$8 =整数；$B$9 =整 数”即可（如图7）,从而 可以得到

7、（如图8）单元格 $B$10的值为41600,单元 格$B$8的值为11,单元格 $B$9的值为35，所以当图7x = 11, y = 35 时，z = 41600。规划求解不仅能解决两个变量的线性规划问题（包括整数 解），还可以解决两个以上（最多可以有两百个）的变量的线性 规划问题，解决问题的方法与两个变量的方法一样。三、二元函数的最值二元函数的最值在高中数学中一般都是利用函数的几何意 义，通过数形结合的方法来进行解决的，这要求学生有较强的构 造能力。而使Excel的规划求解功能，求二元函数的最值就没有必要明确函数的几何意义，具 有很强的实际应用价值。例4.已知实数x, y满足25 +苜=0

8、，求x2 + 3y的最大值建立规划求解方案与求解的的步骤如下:（1）在Excel工作中表选定B1 单元格中的数据作为自变量x，选定B2单元格中的数据作为自变量y，在B3单元格中输入目标函数公式 “=B1i2+3*B2”；大值；可变单元格设置成“ $B$1:（2）选中B3,然后进入菜单栏上 的“工具” |“规划求解.”,在对话框中 输入如下内容（如图1）:将“设置目标 单元格”设置成“ $B$3”，并设置成最大值；可变单元格设置成“ $B$1:$B$2”；添加约束条件$B$4=1”，单击求解；（3）得出如下内容（如图2）：单元格$B$1的值为4.854，单元格$B$2的值为0.96，单元格$B$3的值为26.44，所以当x = 4.854，y = 0.96时， （x 2 + 3 y ） = 26.44max本文给出了用规划求解在解决一元函数的最值、线性规划和二元函数的最值问题方面的一 般方法及技巧，方法简捷、精度较高，但对于有无穷多最优解的问题，该方法只能给出其中的一 个解，这一点在使用时