下载文档原格式
用Excel求解线性规划问题实验（实验题目在最后）一、Excel函数使用Excel求解线性规划问题时，SUMPRODUCT函数可以大大降低资料录入工作量，提高工作效率。
计算数组或向量的乘积时，使用SUMPRODUCT 函数，格式如下：SUMPRODUCT(数组1,数组2,…,数组n)其中2≤n≤30，即最多可以使用30个数组参数，返回值为n个数组对应元素乘积之和。
以图1为例，在单元格D1中输入公式=SUMPRODUCT(A1:B1,A2:B2,A3:B3)得到111（相当于A1*A2*A3 + B1*B2*B3 = 1*2*3 + 3*5*7 = 111）。
在单元格D2中输入公式=SUMPRODUCT(A1:C1,A2:C2)得到53(相当于A1*A2 + B1*B2 + C1*C2 = 1*2 + 3*5 + 4*9 = 53)。
图1. 乘积和(SUMPRODUCT函数)计算结果11二、求解实例1. 问题描述与模型建立某玩具厂生产猫和龟两种玩具，制造一个玩具猫可获利30元，制造一个玩具龟可获利20元。
制造一个猫需要2小时机工和1小时手工；制造一个龟需要1小时机工和1小时手工。
在一周内，机工不能超过100h ，手工不能超过80h ，猫的产量不能超过45个。
求产品的最佳生产量和最大利润。
设1x 为一周内猫的生产量，2x 为一周内龟的生产量。
可建立如下线性规划模型：⎪⎩⎪⎨⎧≤≤+≤++=458010022030max 1212121x x x x x x x g2. 数据录入（1）启动Excel ，建立如图2所示的Excel 工作表，输入系数矩阵A 到区域C2：D4；输入约束常数b 到区域F2：F4；输入目标系数到区域C5：D5。
（2）指定单元格C6和D6存储变量1x 和2x 的值，称之为可变单元格。
在可变单元格中输入数字1表示给定初始值121==x x ，但并非一定这样；若这两个单元格不输入内容，Excel 将按0处理，不影响求解。
用EXCEL求解线性规划
要用EXCEL求解线性规划问题，需要遵循以下步骤：
1. 给定问题中的约束条件和目标函数。
2. 打开EXCEL，建立一个新的工作表。
3. 在工作表中输入问题的约束条件和目标函数。
在输入目标函数时，需要将所有项移动到等号左侧，使它成为一个线性方程。
需要注意将不等式约束条件转化为等式约束条件，可以通过添加松弛变量来实现。
4. 使用EXCEL的“规划”工具，在工具栏中点击“数据”-“分
析”-“规划器”，打开“规划器”。
5. 在“规划器”中，选择需要优化的目标单元格，在“约束条件”
中输入所有约束条件所在的单元格，设置变量单元格的范围。
6. 可以在“选项”中添加其他约束条件。
例如，可以设定变量的整数或二元特性等。
7. 单击“求解”按钮，EXCEL将自动求解最优解，并输出最优
值和变量值。
需要注意的是，线性规划问题求解的结果是一个数值，而不是图形。
因此，需要谨慎分析问题以确保从数值结果中得到了正确的结论。
实验一、用Excel求解线性规划模型线性规划问题用手工求解工作量很大，而且没有较高的数学基础很难理解其计算过程和方法，但是借助Excel“规划求解”工具，就能轻而易举地求得结果。
Excel最多可解200个变量、600个约束条件的问题。
下面我们以一实例介绍利用Excel规划求解工具怎样快速解决具体的经济决策问题。
一、实验目的1、掌握如何建立线性规划模型。
2、掌握用Excel求解线性规划模型的方法。
3、掌握如何借助于Excel对线性规划模型进行灵敏度分析，以判断各种可能的变化对最优方案产生的影响。
4、读懂Excel求解线性规划问题输出的运算结果报告和敏感性报告。
二、实验内容1、[工具][规划求解]命令规划求解加载宏是Excel的一个可选安装模块，在安装Excel时，只有在选择“完全/定制安装”时才可选择装入这个模块。
在安装完成进入Excel后还要用[工具][加载宏]命令选中“规划求解”，以后在[工具]菜单下就增加了一条[规划求解]命令。
使用[规划求解]命令的一般步骤为：第一步：在选取[工具][规划求解]命令后，弹出图1所示“规划求解参数”对话框，其中各选项说明如表1。
图1“规划求解参数”对话框选项名说明设置目标单元格选取计算问题的目标函数，并含有计算公式的单元格等于按问题目标进行选择。
如利润问题，选取“最大值”可变单元格决策变量所在各单元格、不含公式，可以有多个区域或单元格约束增加、修改、删除各个约束等式或不等式，一个一个地与图2切换填入或修改添加选择后弹出图2所示对话框更改选择后弹出图3所示对话框删除删除所选定的约束条件选项决定采用线性模型还是非线性模型求解约束条件中的单元格引用位置，可从键盘直接录入，也可用鼠标拖放选取。
图2图3第二步：完成图1所示的一切填入项目后，单击“选项”按钮，在弹出的“规划求解选项”对话框中若是线性模型则选取“采用线性规模”选项按钮，再单击“确定”按钮回到图1。
图4第三步：在图1中单击“求解”按钮，经计算完成后弹出“规划求解结果”对话框（图5）。
中国矿业大学矿业工程学院实验报告课程名称采矿信息技术姓名 ******* 班级采矿 ***班学号 ********** 日期 2013年12月成绩教师胡国忠教授一、 实验题目用Excel 规划求解工具完成线性规划、0-1规划、以及用拉格朗日乘数法求解巷道最优断面。
具体题目中的数值见实验3成果Excel 。
二、 实验目的Excel 的主要功能是表格处理。
通过本次实验，巩固和掌握Excel 软件的基本知识和基本操作，特别是数据库的基本操作等常用工具。
学会用Excel 求解各类规划问题。
三、 实验内容用Excel 规划求解工具完成线性规划、0-1规划各一题；用拉格朗日乘数法求解巷道最优断面。
四、 实验步骤线性规划的解法 例约束条件：解：1）打开Excel, Sheet1空白页，命名为“线性1”。
图2-22）在B5---B9中分别输入目标函数F(X)及约束条件G(X)的表达式，见图2-1。
3）分别在B11、C11、D11中输入X1、X2、F(X), 在B12、C12、D12中分别输入1、1、=4*b12+2*c12, 此时D12中显示出6，见图2-2。
4）光标放在D12上，单击“工具\规划求解”菜单， 出现“规划求解参数”对话框。
（1）在“设置目标单元格”中输入d12 （2）在“等于”中选“最大值”(自动默认) （3）在“可变单元格”中选“B12:C12”（4）单击“添加”按钮，出现“添加约束”对话框，• 在此框中“单元格引用位置”输入B12，MAXX X x F ⇒+=2124)(⎪⎪⎩⎪⎪⎨⎧>=<=<=+<=+032243/812121i X X X X X X•在运算符选择框中选“《=”，•在“约束值”中输入4-8/3*c12，单击“添加”按钮，在“单元格引用位置”中输入b12，在“约束值”中输入2-c12，再次单击“添加”按钮，在“单元格引用位置”中输入b12，在“约束值”中输入1.5，在“单元格引用位置”中输入b12，在运算符选择框中选>=，在约束值中输入0，在“单元格引用位置”中输入c12，在运算符选择框中选>=，在约束值中输入0，见图2-3。
excel求解线性规划和灵敏度分析实训过程记录及学习收获线性规划是一种数学优化模型，用于对一组线性限制条件下的线性目标函数进行优化。
Excel 能够进行线性规划问题的求解和灵敏度分析，以下是实习过程的记录和收获总结：1. 实训任务我们的实训任务是一个有饲料限制的生产计划问题，其中需要决定生产哪些种类的产品、购买何种原材料、以及在何时生产这些产品，以使得利润最大化。
任务中给定了各种产品需要的原材料数量，各种原材料的数量与价格，及一些限制条件，例如生产时间，最小生产量等。
2. Excel求解线性规划问题Excel中求解线性规划问题的函数是“Solver”，首先需要打开Excel中的“数据”选项卡，然后在“分析”工具中找到“Solver”。
进入“Solver参数”对话框后，需要输入目标函数和限制条件，并且设置决策变量的可变性、约束条件的类型和数量。
最后根据需要设置求解的约束条件和目标函数的目标方向，点击“求解”即可。
在我们的实训任务中，我们首先需要设置约束条件，限制了各种产品需要的原材料数量，并且确保生产时间在规定范围内。
然后我们需要设置各个决策变量的可变性，例如选择生产哪些产品，购买何种原材料以及在何时生产这些产品等。
最后将目标函数设置为生产的利润最大化，并且设置约束条件为“>=0”，以确保决策变量的可行性。
点击“求解”即可得出最优解。
3. Excel灵敏度分析Excel的灵敏度分析功能可以帮助我们了解线性规划问题的各个变量对于目标函数的影响程度。
Excel中灵敏度分析的函数是“规划求解器的报告”，在对话框中选择“接受解决方案”，然后勾选“制作规划求解器报告”选项，即可生成报告。
在报告中，我们可以看到各个决策变量的最优解以及目标函数的最优值。
同时，报告中还包括影响目标函数的变量的“系数范围”和“变化量”，我们可以通过调整这些参数来预测目标函数的变化情况。
4. 学习收获通过这次实训，我学会了如何使用Excel求解线性规划问题以及如何进行灵敏度分析。
应用电子表格求解线性规划问题实验报告
应用电子表格求解线性规划问题实验报告
一、实验目的与要求：
1．会在Excel中建立选址问题相关模型；
2．熟练使用Excel求出选址问题的解；
3．理解求解选址问题中每一步的原理。
二、实验步骤与方法：
1．在Excel中加载规划求解工具；
2．在Excel中建立选址问题模型数据表；
3．确定需要做出的决策，并指定可变单元格和目标单元格，作好标识；
4．指出约束条件，并将以数据和决策表示的被限制结果放入输出单元格；
5．在“规划求解”对话框中点击“选项”按钮，选“采用线性模型和假定非负”；
6．回到“规划求解”对话框，点击“求解”按钮；
7．选择保存规划求解结果，点击右侧“运算结果报告”。
三、部分截图：。
运筹学实验报告册（适用于经济管理类专业）学号：姓名：专业：信息管理与信息系统实验一线性规划的Excel求解与软件求解一、实验目的熟悉Excel软件、管理运筹学软件，掌握线性规划的Excel求解和管理运筹学软件求解。
二、实验要求能识别线性规划有关问题并建立相应的线性规划模型，能写出线性规划的标准形式，理解线性规划解的概念，理解单纯形法原理。
三、实验原理及内容依据单纯形法求解原理及步骤，在Excel界面中输入数据，进行求解。
熟悉线性规划模型的建立过程，掌握数据整理与Excel规划求解的操作步骤。
线性规划模型的建立，数据的输入与求解是最基础的要求。
本节实验要求完成以下内容：1、线性规划模型的建立；2、Excel界面内数据的输入；3、利用Excel规划求解进行线性规划模型的求解。
四、实验步骤及结论分析1、某饲养场养动物出售，设每头动物每天至少需700g蛋白质、30g矿物质、100mg维生素。
现有五种饲料可供选用，各种饲料每kg营养成分含量及单价如表示。
饲料蛋白质（g）矿物质（g）维生素（mg）价格（元/kg）1 3 1 0.5 0.22 2 0.5 1.0 0.73 1 0.2 0.2 0.44 6 2 2 0.35 18 0.5 0.8 0.8（1）建立这个问题的线性规划模型Min f=0.2X1+0.7X2+0.4X3+0.3X4+0.8X5约束条件：3X1+2X2+X3+6X4+18X5>=700X1+0.5X2+0.2X3+2X4+0.5X5>=300.5X1+X2+0.2X3+2X4+0.5X5>=100X1,X2,X3,X4,X5>=0（2）对建立的模型进行Excel求解2、福安商场是个中型的百货商场，它对销售人员的需求经过统计分析如下所示：时间所需售货员人数星期日28人星期一15人星期二24人星期三25人星期四19人星期五31人星期六28人的两天是连续的，问应该如何安排售货人员的作息，既满足了工作需要，又是配备的售货人员的人数最少？（用管理运筹学软件求解）实验二 运输问题一、 实验目的熟悉Excel 软件，学会运输问题的Excel 求解与管理运筹学软件求解。
实验指导书《管理决策模型与方法》
实验1 EXCEL 线性规划实验
一、实验目的
1、掌握应用Excel软件求解线性规划问题；
2、掌握应用Excel软件对线性规划问题进行灵敏度分析；
3、掌握应用Excel软件求解整数规划问题；
4、掌握应用Excel软件求解0-1整数规划问题。
二、实验设备、仪器及所需材料
配置在Pentium Ⅲ，内存128M以上的电脑；装有Microsoft Windows操作系统及Microsoft Office 2003工作软件。
三、实验原理
“规划求解”是Microsoft Excel 中的一个加载宏，借助它可以求解许多运筹学中的数学规划问题。
安装Office 2003 的时候，系统默认的安装方式不会安装该宏程序，需要用户自己选择安装。
安装方法为：从Excel 菜单中选择“工具”→“加载宏”，打开如下对话框：
选择其中的“规划求解”后单击“确定”按钮，会出现提示：“这项功能目前尚未安装，是否现在安装?”，选择“是”，系统要你插入Office 的安装光盘，准备好后单击确定，很快就会安装完毕。
于是，你会发现在“工具”菜单下多出一个名为“规划求解”的子菜单，说明“规划求解”功能已经成功安装。
在EXCEl2007版本中，通过点击“office按钮”，“EXCEL选项”→“加载项”→转到“EXCEL
加载项”，然后加载【规划求解加载项】便可以加载规划求解的宏。
在EXCEl2010版本中，通过点击“文件”选项卡打开“Excel选项”对话框，单击左侧 “加载项”标签，在右侧单击“转到”按钮，打开“加载宏”对话框，勾选“规划求解加载项”复选框，单击“确定”按钮，即可在工具栏的“数据”选项卡中出现 “分析”选项组，上面就有了“规划求解”按钮。
利用“规划求解”功能，就可以进行线性规划问题的求解。
例如：用EXCEL 求解数学规划问题
12121212maxZ 2328416..4120, 0
x x x x x s t x x x =++≤⎧⎪≤⎪⎨
≤⎪⎪≥≥⎩
步骤：
1． 将模型中的目标函数和约束条件的系数输入到单元格中；为了使我们在操作过程中看得
更清楚，可以附带输入相应的标识符，并给表格加上边框。
如下图所示：
2．在E4 单元格（目标值）输入“=SUMPRODUCT($C$3:$D$3, C4:D4)”；其中，SUMPRODUCT 函数的功能是将数组间对应的元素相乘，并返回乘积之和，即SUMPRODUCT($C$3:$D$3, C4:D4)＝C3×C4＋D3×D4；$C$3:$D$3 表示这几个单元格为绝对引用。
3．用拖动的方式将E4 单元格的公式复制到E5～E7，如下图：
4．从“工具”选择“规划求解”，打开“规划求解参数”窗口。
Office 2007中，从“数据”选择“规划求解”，打开“规划求解参数”窗口。
5．将窗口中的目标单元格设为$E$4，可变单元格设为$C$3:$D$3，目标为求最大值，如下图：
6．单击“添加”按钮，打开“添加约束”对话框；将单元格引用位置设为$E$5:$E$7，
约束值设为$G$5:$G$7，不等式符号为<=，如下图：
7．选择“确定”返回“规划求解参数”窗口；单击“选项”按钮，弹出“规划求解选项”窗口，并选中其中的“采用线性模型”和“假定非负”两项，其余选项可保留默认值。
如下图：
8．选择“确定”按钮返回“规划求解参数”窗口；单击“求解”按钮，系统弹出
“规划求解结果”对话框，如下图：
9．将对话框右边“报告”下的全部内容选中（也可不选）后，单击“确定”按钮完成计算，结果如下图。
可知该问题的最优解为：x1＝4，x2＝2，max Z =14。
四、实验内容
（一）用EXCEL 求解线性规划问题
121212
1212maxZ 12102160131340..322600, 0
x x x x x x s t x x x x =++≤⎧⎪+≤⎪⎨
+≤⎪⎪≥≥⎩
（二）用EXCEL 求解线性规划问题，并进行灵敏度分析
1212121212maxZ 2030240240..250, 0
=++≤⎧⎪+≤⎪⎨
+≤⎪⎪≥≥⎩x x x x x x s t x x x x
（三）用EXCEL 求解整数规划问题
12
1212maxZ 320.5 4.5..2314
0, 1,2=++≤⎧⎪+≤⎨⎪≥=⎩i
x x x x s t x x x i 且为整数
（四）用EXCEL 求解0－1整数规划问题
如果每一个客户只需要一个项目负责人，那么怎么进行分配才能使项目负责人完成这三
（五）用EXCEL 求解下列资源分配问题
某昼夜服务的公交线路每天各时间段内所需司机人数如下：
班次 时间 所需人数 1 6：00～10：00
80
2 10：00～14：00 70
3 14：00～18：00 80
4 18：00～22：00 50
5 22：00～2：00 20 6
2：00～6：00
30
设司机在各时间段一开始时上班，并连续工作八小时，问该公交线路该怎样安排司机人数，既能满足工作需要，又配备最少司机？数学模型如下，求用EXCEL 求解。
1234561612233445
56minZ 807080..5020300, 1,2,3,4,5,6
=++++++≥⎧⎪
+≥⎪⎪+≥⎪
+≥⎨⎪+≥⎪⎪+≥⎪
≥=⎩i x x x x x x x x x x x x s t x x x x x x x i 且为整数
（六）请各位同学按照学号选做以下题目
要求：请至少完成与学号尾数相对应题号的题目。
选做其他题目，则按题目数量和
准确率进行加分。
123451234123512345
maxZ 523238..34=7,,,,0=++-++++=⎧⎪+++⎨⎪≥⎩x x x x x x x x x s t x x x x x x x x x
题目2：
12
1324125minZ 2 4 3..2 +80, 1,2,3,4,5
=--+=⎧⎪+=⎪⎨+=⎪⎪≥=⎩i x x x x x x s t x x x x i
题目3：
121212212maxZ 2 + 21.. 30, 0
=-+≥⎧⎪-+≥⎪⎨≤⎪⎪≥≥⎩x x x x x x s t x x x
题目4：
12
1212maxZ 40909756..72070
01,2=++≤⎧⎪+≤⎨⎪≥=⎩i
x x x x s t x x x i 且都为整数,
12
1212maxZ 58 6..5945
0, 1,2=++≤⎧⎪+≤⎨⎪≥=⎩i
x x x x s t x x x i 且为整数
题目6：
1231231231223
maxZ 435323235..2 2 428
=0 1 1,2,3
=-++-≤⎧⎪
++≤⎪⎪-≤⎨⎪+≤⎪⎪=⎩i x x x x x x x x x s t x x x x x i 或，
题目7：
由甲、乙、丙、丁四人去完成A 、B 、C 、D 四项工作，每人做且做其中一项工作，每人完成各项工作的工时由下表给出。
怎样指派工作，才能使四个人完成四个项目的总时间最
题目8：
由甲、乙、丙、丁四人去完成A 、B 、C 、D 四项工作，每人做且做其中一项工作，每人完成各项工作的工时由下表给出。
怎样指派工作，
才能使四个人完成四个项目的总时间最少。
请用excel 求解该问题。
12124123
23
maxZ 321032 +252 ..20, 1,2,3,4, =--+⎧
+=⎪⎪
⎪-+=⎨⎪
≥=⎪⎪⎩i x x x x x x x x s t x i x x 取整数
题目10：
1234123412341234minZ 25+3+44+0243+44..+1=01 1,2,3,4=+-++≥⎧⎪-++≥⎪⎨+-≥⎪⎪=⎩i x x x x x x x x x x x x s t x x x x x i 或，
五、实验报告要求
1、封面：写明实验的名称，班级、姓名及实验时间。
2、实验报告：按统一格式，采用统一报告纸。
报告内容应包括实验名称、目的、原理、内容、实验成果和实验心得等。
实验成果是指能否理解Excel 函数，并应用Excel 求解线性规划问题。
3、备注：实验成果和实验心得请手写。
