如何用Excel进行线性规划求解

利用Excel求解线性规划问题

线性规划问题的求解有很多方法，也有很多工具。比如常用的Matlab、Lingo，记得参加数学建模的时候就是用的Lingo解决线性规划问题的。本文主要讲解如何使用Excel求解线性规划问题，Excel本身是没有计算线性规划问题能力的，因此我们首先要加载相应的宏定义。一、加载宏定义（不同版本的加载方式有所不同）： Excel 2003：单击“工具”菜单，然后单击“加载宏”，选择“规划求解”点击确定。 Excel 2007：方法一：用快捷键。先按Alt+T，再按I键，即可打开加载宏对话框。方法二：单击“Office按钮→Excel 选项→加载项”，确保“管理”右侧下拉列表中的选项是“Excel 加载项”，单击“转到”按钮即可。 Excel 2010：直接在功能区中选择“开发工具”选项卡，在“加载项”组中单击“加载项”命令，选择“规划求解”点击确定。注意：如果功能区中没有“开发工具”选项卡，可以通过自定义功能区来显示“开发工具”选项卡：单击“文件→选项→自定义功能区”，然后在右侧区域中勾选“开发工具”并单击“确定”。二、初始化数据（以Excel 2010为例，其他版本大同小异）：比如

我们要计算的线性规划问题如下：那么，

怎么利用Excel求解线性规划的详细步骤

利用线性回归方法求解生产计划

方法一：

1、建立数学模型:

设变量：设生产拉盖式书桌x台，普通式书桌y台，可得最大利润 确定目标函数及约束条件 目标函数：maxP 115x 90y

约束条件：10x 20y 200 .....................⑴ 4x 16y 128 .....................⑵ 15x 10y 220 .....................⑶ x,y 0 ..........................⑷ 2、在Excel中求解线性规划

首先，如图1所示，在Excel工作表格输入目标函数的系数、约束方程的系数和右端常数项：

图1

将目标方程和约束条件的对应公式输入各单元格中 F2=MMULT（B6:C6,F6:F7)； F3=MMULT（B3:C3,F6:F7)； F2=MMULT（B4:C4,F6:F7)； F2=MMULT（B5:C5,F6:F7)；

怎么利用Excel求解线性规划的详细步骤

出现图2样式：

图2

线性规划问题的电子表格模型建好后，即可利

机械工程学院工业工程专业

学号： 姓名：

线性规划问题建模与求解

一．实验目的

1． 掌握线性规划问题建模基本方法。

2． 熟练应用Excel“规划求解”功能对线性规划问题进行建模与求解。

3．掌握线性规划问题的对偶理论和灵敏度分析。

二．实验设备 硬件：PC机。

软件：Microsoft Excel。

三．实验内容

1．建立线性规划问题的数学模型。

2．利用Excel“规划求解”功能对线性规划问题进行建模与求解。 3．根据实验优化结果，进行灵敏度及经济分析。

四．实验步骤

某出版单位有4500个空闲的印刷机时和4000个空闲的装订工时，拟用于下列4种图书的印刷和装订。已知各种书每册所需的印刷和装订工时如表2所示。

表2 印刷和装订工时数据表

工 序 书 印刷 装订 预期利润（千元/千册） 问：

①该出版单位为了实现利润最大化，如何安排4种图书的生产？ ②该单位是否愿意出50元的加班费，让工人加班1小时？

③由于管理工作的进步，使得第1种产品成本每件下降0.2元，此时得最优生产方案是否有变化，总利润是多少？

④出版第2种书的方案之一是降低成本，若第2种书的印刷加装订成本合计每册6元，则第2种书的成本为多少时，

运筹学EXCEL操作介绍

使用Excel解线性规 划问题2010年10月15日

参考书目：

《Excel数据建模与应用》,第4章，清华大学出版社 《数据、模型与决策》第二章附录，机械工业出版社

运筹学EXCEL操作介绍

一个简单的例子某工厂计划生产两种产品，利润分别为2和3,已知生产单 位产品所需的设备台时和A、B两种原材料的消耗，如表

设备 原材料A 原材料B

产品1 1 4 0

产品2 2 0 4

8台时 16KG 12KG

目标是不超过资源限制的情况下，确定两产品产量，得 到最大利润。

运筹学EXCEL操作介绍

建立数学公式(步骤一)在工作表的顶部输入数据 确定每个决策变量所对应 的单元格位置 选择单元格输入公式，找 到目标函数的值 确定约束单元格输入公式， 计算每个约束条件左边的 值 确定约束单元格输入公式， 可采用 ‘复制粘贴’ 或 ‘直 计算每个约束条件右边的 接输入’ 的方式导入数据。 值

运筹学EXCEL操作介绍

建立数学公式(步骤二)在工作表的顶部输入数据 确定每个决策变量所对应 的单元格位置 选择单元格输入公式，找 到目标函数的值 选择一个单元格输入公式， 计算每个约束条件左边的 值 选择一个单元格输入公式， 计算每个约束条件右边的 值

图中，规定B1

有用的Excel进行数据处理

Excel 培训

北京京东方光电科技有限公司 Excel Seminar主讲人：乐健 2007年6月25日

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之一：F4功能：(举例)插入多个新工作表办法一：

办法二：

之后不断重复直到达到目的

之后重复按下F4点评：此快捷键可重复上一步骤

点评：最传统。。。

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之一：F4功能：锁定单元格按下F4 键

锁 定 前

锁 定 后

下 拉

下 拉

注：也可以手工加上 “$”3

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之二：F2功能：插入多个新工作表 办法一： 办法二： 按下F2

双击单元格直接编辑

点评：最直接，但我们的原则是两 只手都要用，毕竟在你打字时来回 拿鼠标并不是一件愉快的事

点评：鼠标用得越少，说明你 的Excel 能力就越强

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之三：Ctrl +1功能：设置单元格格式办法一：右键-〉设置单元格格式 办法二：按下Ctrl +1即可

这个快捷键被我评为最常用快捷键之首位，很遗憾的是，很多人 宁可用办法一，也不愿伸出自己的左手5

有用的

线性规划中的整点问题求解方法

线性规划是运筹学的一个重要分支，在实际生活中有着广泛的应用。新教材中增加了线性规划的内容，充分体现了数学的实际应用，发展了学生的数学应用意识。由于实际问题中线性规划问题的最优解多为整数解，也是学生学习线性规划的难点，因而求线性规划的整数最优解的方法就显得尤为重要了。但教材中对此类问题却一带而过，对于具体的验算过程并没有作必要的描述，以致学生在解题过程中对于具体的验算过程掌握还不够清晰。

例1：

要将两种大小不同的的钢板截成A、B、C三种规格，每张钢板可同时截得三种规格的小钢板的块数如表所示，今需要A、B、C三种规格的成品分别为15，18，27

且使所用钢板张数最少。

解：设需要截第一种钢板x张，第二

2x y 15 x 2y 18

张钢板y张，则 x 3y 27，作出可行

x 0 y 0

域（如图所示）,目标函数为z x y出在一组平行直线x y t中（t为参数）经过可行域内的点且和原点距离最近的直线，此直线经过直线x 3y 27和直线2x y 15的交点A(,于

1839572

)，直线方程为x y 11，由5555

18391839

和都不是整数，而最优解(x,y)中，x，y必须都是整数，所以可行域内点 A(,)5555

有用的Excel进行数据处理

Excel 培训

北京京东方光电科技有限公司 Excel Seminar主讲人：乐健 2007年6月25日

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之一：F4功能：(举例)插入多个新工作表办法一：

办法二：

之后不断重复直到达到目的

之后重复按下F4点评：此快捷键可重复上一步骤

点评：最传统。。。

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之一：F4功能：锁定单元格按下F4 键

锁 定 前

锁 定 后

下 拉

下 拉

注：也可以手工加上 “$”3

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之二：F2功能：插入多个新工作表 办法一： 办法二： 按下F2

双击单元格直接编辑

点评：最直接，但我们的原则是两 只手都要用，毕竟在你打字时来回 拿鼠标并不是一件愉快的事

点评：鼠标用得越少，说明你 的Excel 能力就越强

有用的Excel进行数据处理

Excel 培训之—快捷键 快捷键之三：Ctrl +1功能：设置单元格格式办法一：右键-〉设置单元格格式 办法二：按下Ctrl +1即可

这个快捷键被我评为最常用快捷键之首位，很遗憾的是，很多人 宁可用办法一，也不愿伸出自己的左手5

有用的

实验二 应用LINGO、MATLAB软件求解非线性规划

一.实验目的

1. 对实际问题进行数学建模，并学会用数学软件Matlab或运筹软件Lindo/Lingo对问题进行求解；

2. 学会建立M文件，并学会用Matlab的软件包内部函数求解非线性规划问题。

二.实验内容

1.写出下属问题的数学模型（LINGO）

将机床用来加工产品A，6小时可加工100箱。若用机床加工产品B，5小时可加工100箱。设产品A和产品B每箱占用生产场地分别是10和20个体积单位，而生产场地（包括仓库）允许15000个体积单位的存储量。机床每周加工时数不超过60小时。产品A生产x1（百箱）的收益为（60-5x1）x1元，产品B生产x2（百箱）的收益为（80-4x2）x2元，又由于收购部门的限制，产品A的生产量每周不能超过800箱，试制定周生产计划，使机床生产获最大收益。

2.用数学软件求解下列问题:(MATLAB) （1） minf??x1?2x2?x12?x22

minf??x1?2x2?s..t2x12?3x2?61212x1?x2221212（2）

x1?4x2?5x1,x2?0x1?3,x2?6

三. 模型建立

1、设生产A产品为x1百箱，生产B产品为x2

实验二 应用LINGO、MATLAB软件求解非线性规划

一.实验目的

1. 对实际问题进行数学建模，并学会用数学软件Matlab或运筹软件Lindo/Lingo对问题进行求解；

2. 学会建立M文件，并学会用Matlab的软件包内部函数求解非线性规划问题。

二.实验内容

1.写出下属问题的数学模型（LINGO）

将机床用来加工产品A，6小时可加工100箱。若用机床加工产品B，5小时可加工100箱。设产品A和产品B每箱占用生产场地分别是10和20个体积单位，而生产场地（包括仓库）允许15000个体积单位的存储量。机床每周加工时数不超过60小时。产品A生产x1（百箱）的收益为（60-5x1）x1元，产品B生产x2（百箱）的收益为（80-4x2）x2元，又由于收购部门的限制，产品A的生产量每周不能超过800箱，试制定周生产计划，使机床生产获最大收益。

2.用数学软件求解下列问题:(MATLAB) （1） minf??x1?2x2?x12?x22

minf??x1?2x2?s..t2x12?3x2?61212x1?x2221212（2）

x1?4x2?5x1,x2?0x1?3,x2?6

三. 模型建立

1、设生产A产品为x1百箱，生产B产品为x2

实验报告

证券投资

学院名称 专业班级 提交日期

评阅人 ____________ 评阅分数 ____________

实验五：运用Excel规划求解进行最优投资组合的求解

【实验目的】

1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序； 2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合；

【实验条件】

1、个人计算机一台，预装Windows操作系统和浏览器； 2、计算机通过局域网形式接入互联网； 3、matlab或者Excel软件。

【知识准备】

理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：《金融建模—使用EXCEL和VBA》电子书 第三章，第四章，第五章 【实验项目内容】

请打开参考《金融建模—使用EXCEL和VBA》电子书 第四章相关章节（4.3）完成以下实验

A． 打开“实验五组合优化.xls”，翻到“用规划求解计算最优组合”子数据表； B． 调用规划求解功能进行求解。

点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL规划求解功能的安装”。

C．

D．在规划求解选项卡里面选择“选