用EXCEL求解线性规划

用EXCEL 求最值

华东师范大学03级教育硕士

江苏省溧阳市戴埠高级中学 潘晓春

〔摘要〕

介绍了用Excel 软件的规划求解功能解决一些常见的求最值问题的方法。主要从一元函数的最值、线性规划和二元函数的最值三个方面去进行探讨。 〔关键词〕

Excel 规划求解 最值

最值问题是生产、科学研究和日常生活中常遇到的一类特殊的数学问题,是高中数学的一个重点,它涉及到高中数学知识的各个方面,解决这类问题往往需要综合运用各种技能。Excel 软件中的规划求解功能将为这类问题的解决提供了一个很有效的方法，而且适用范围较广，具有很强的实用性。

用Excel 解线性规划,必须在Excel 系统中加载“规划求解”项目,如果没有,可以启动Excel 软件,进入Excel 用户界面,然后使用“工具”菜单下“加载宏”菜单项的“规划求解”子项,则可完成“规划求解”项的加载。

本文将从以下三个方面来介绍用Excel 中的规划求解功能进行最值的求解。

一、 一元函数的最值

求函数的最值是高中数学中的一类常见问题，也是高中数学中的一个重点和难点问题，运用Excel 中的规划求解功能能够很快捷地进行求解。

例1．

求函数y =

建立规划求解方案与求解的的步骤如下： （1）在Excel 工作中表选定B1单元中的数据作为自变量x ，在B2单元格中输入目标函数公式“=SQRT(B1*B1 -2*B1+2)+SQRT(B1*B1-10*B1+29)”；

（2）选中2B ,然后进入菜单栏上的“工具”|“规划求解…”,在对话框中输入如下内容(如图1) :将“设置目标单元格”设置成“$B$2”，并设置成最小值；可变单元格设置成“$B$1”，单击求解； （3）得出如下内容（如图2）：单元格$B$1的值为2.333333，单元格$B$2的值为5，所以当 2.333333x =时，()min 5f x =

运用这一方案，可以解决一元函数的的最值，也可以解决一元函数给定区间内的最值问题。

例2.求函数()12

3f x x x

=+[]()1,8x ∈上的最值

建立规划求解方案与求解的的步骤如下：

（1）在Excel 工作表中选定1B 单元中的数据作为自变量x ，在2B

单元格中输入目标函数

图 2

图 1

公式“=3*B1+12/B1”；

（2）选中2B ,然后进入菜单栏上的“工具”|“规划求解…”,在对话框中输入如下内容(如图3) :将“设置目标单元格”设置成“$B$2”，并设置成最小值；可变单元格设置成“$B$1”；添加

约束条件“$B$1<=8”和“$B$1>=1”；单击

求解；

（4）得出如下

内容（如图4）：单元格$B$1的值为2，单元格$B$2的值为12，所以当2x =时，

()min 12f x =

二、 线性规划

线性规划是高中数学中的一个重要内容，求线性目标函数在线性约束条件下的最大值或最小值的问题。高中对线性规划问题的解决一般都是采用图解法，这里将运用Excel 中的规划求解来解决。

例3.设6001000z x y =+，式中变量x 、y 满足下列条件

104300542004936000

x y x y x y x y +≤⎧⎪+≤⎪⎪

+≤⎨⎪≥⎪≥⎪⎩，求z 的最大值 建立规划求解方案与求解的的步骤如下： （1）在Excel 工作表中选定B8单元格中的数据作为x ，B9单元格中的数据作

为y ，在单元格B2:D7分别输入目标函数和约束条件的系数，在单元格E3中输入“=B3*$B$8+C3*$B$9”，

并用填充柄拉至E7，在B10单元格中输入目标函数公式“=B2*B8+C2*B9”；

（2）选中B10 ,然后进入菜单栏上的“工具”|“规划求解…”,在对话框中输入如下内容(如图5) :将“设置目标单元格”设置成“$B$10”，并设置成最大值；可变单元格设置成“$B$8:$B$9”；添加约束条件“$E$3<=$D$3；$E$4<=$D$4；$E$5<=$D$5；$E$6>=$D$6；$E$7>=$D$7”；单击求解；

（4）得出如下内容（如图6）：单元格$B$10的值为41931，单元格$B$8的值为12.414，单元格$B$9的值为34.483，所以当12.414x =，34.483y =时，max 41931z =。

图 3

图 4

图 5 图 6

如果在例3中添加,x y Z ∈时，我们只须在上面的求解中添加约束条件：“$B$8＝整数；$B$9＝整数”即可（如图7），从而可以得到（如图8）单元格$B$10的值为41600，单元格$B$8的值为11，单元格$B$9的值为35，所以当

11x =，35y =时，max

41600z =。

规划求解不仅能解决两个变量的线性规划问题（包括整数解），还可以解决两个以上（最多可以有两百个）的变量的线性规划问题，解决问题的方法与两个变量的方法一样。

三、 二元函数的最值

二元函数的最值在高中数学中一般都是利用函数的几何意义，通过数形结合的方法来进行解决的，这要求学生有较强的构

造能力。而使Excel 的规划求解功能，求二元函数的最值就没有必要明确函数的几何意义，具有很强的实际应用价值。

例4.已知实数,x y 满足22

02516

x y +

=，求23x y +的最大值 建立规划求解方案与求解的的步骤如下： （1）在Excel 工作中表选定B1单元格中的数据作为自变量x ，选定B2单元格中的数据作为自变量y ，在

B3单元格中输入目标函数公式“=B1^2+3*B2”；

（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，所以当 4.854x =，0.96y =时，

()

2

max

326.44x

y +=

本文给出了用规划求解在解决一元函数的最值、线性规划和二元函数的最值问题方面的一般方法及技巧,方法简捷、精度较高,但对于有无穷多最优解的问题，该方法只能给出其中的一个解,这一点在使用时应注意。

图 7

图 8 图 9 图 10