为了适应公司新战略的发展，保障停车场安保新项目的正常、顺利开展，特制定安保从业人员的业务技能及个人素质的培训计划excel,规划求解的报告中国矿业大学矿业工程学院实验报告课程名称采矿信息技术姓名*班级采矿*班学号成绩教师一、实验题目用Excel规划求解工具完成线性规划、0-1规划、以及用拉格朗日乘数法求解巷道最优断面。具体题目中的数值见实验3成果Excel。二、实验目的Excel的主要功能是表格处理。通过本次实验，巩固和掌握Excel软件的基本知识和基本操作，特别是数据库的基本操作等常用工具。学会用Excel求解各类规划问题。三、实验内容用Excel规划求解工具完成线性规划、0-1规划各一题；用拉格朗日乘数法求解巷道最优断面。四、实验步骤线性规划的解法例F(x)?4X1?2X2?MAX?X?X?2?12?2X1?3?Xi?0解：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。约束条件：?X1?8/3X2?44）光标放在D12上，单击“工具规划求解”菜单，出现“规划求解参数”对话框。在“设置目标单元格”中输入d12在“等于”中选“最大值”(自动默认)在“可变单元格”中选“B12:C12”单击“添加”按钮，出现“添加约束”对话框，?在此框中“单元格引用位置”输入B12，?在运算符选择框中选“=”，?在“约束值”中输入4-8/3*c12，单击“添加”按钮，在“单元格引用位置”中输入b12，在“约束值”中输入2-c12，再次单击“添加”按钮，在“单元格引用位置”中输入b12，在“约束值”中输入，在“单元格引用位置”中输入b12，在运算符选择框中选=，在约束值中输入0，在“单元格引用位置”中输入c12，在运算符选择框中选=，在约束值中输入0，见图2-3。?单击“确定”按钮，返回“添加约束”对话框5）单击“求解”按钮，在“线性1”页的B12、C12、D12中分别出现、76）同时出现“规划求解结果”对话框，选“保存规划求解结果(默认)”，在“报告”拦中选“运算结果报告”后，单击“确定”按钮见图2-4。7）在Excel中自动增加一页“运算结果报告1”，报告中记录了运算日期、目标单元格、可变单元格、约束等内容。8）保存、打印运算结果，见图2-5。按照以上步骤套用到自己所做的题目得到实验3结果第一题，详细见实验3成果Excel0-1规划解法用Excel软件求解基本过程:1）打开Excel,Sheet5空白页，命名为“0-1规划”2）在B5-B10中分别输入目标函数F(X)及约束条件G(X)的表达式3）分别在B11、C11、D11、11中输入X1、X2、X3、F(X),在B12、C12、D12、E12中分别输入1、1、1、=2*b12-2*c12+4*d12,此时E12中显示出5，如图2-10。4）光标放在E12上，单击“工具规划求解”菜单，出现“规划求解参数”对话框在“设置目标单元格”中输入E12。在“等于”中选“最大值”(自动默认)。在“可变单元格”中选“B12:D12”如图2-11。单击“添加”按钮，出现“添加约束”对话框，如图2-12。在此框中“单元格引用位置”输入B12，选“=0，=0，=0，=，int和bin五个选项，根据该约束函数所在约束方程的情况选择，其中int和bin分别用于说明整型变量和01型变量。选择完成后，如果还有约束条件未设定，就点击“添加”按钮，重复以上步骤设定约束条件，设定完所有约束条件后，点击确定完成约束条件设定，回到规划求解参数设定对话框。设定算法细节：点击规划求解参数设定对话框中的“选项”按钮，出现如下规划求解选项对话框。该对话框为使用者提供了在一些可供选择的常用算法。主要是供高级用户使用，初学者不必考虑这些选择。选择完成后点击确定按钮回到规划求解参数设定对话框求解模型：完成以上设定后，点击规划求解参数设定对话框中的“求解”按钮，将出现如下求解结果对话框。根据需要选择右边列出的三个报告中的一部分或全部，然后点击确定按钮就可以在Excel内看到求解报告。四、用规划求解加载宏求解数学规划举例下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。例1公司通常需要确定每月生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束：?产品组合使用的资源不能超标。?对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费。下面，我们来考虑让某医药公司的最优产品组合问题。该公司有六种可以生产的药品，相关数据如下表所示。设该公司生产药品16的产量分别为x1,x2,规划模型为,x6，则最优产品组合的线性maxz?6x1?6x1?5x2?4x3?3x4?4500?x1?960?x2?928?x?1041s.t?3?x4?977?x5?1084?x6?1055?xj?0,1?j?6?下面用规划求解加载宏来求解这个问题：首先，如下如所示，在Excel工作表内输入目标函数的系数、约束方程的系数、右端常数项；其次，选定目标函数单元、可变单元、约束函数单元，定义目标函数、约束函数利用Excel进行规划求解Excel具有规划求解的基本功能，包括线性规划和非线性规划。对于常规的线性规划问题，Excel就可以给出求解结果。对于比较复杂的问题，那就需要用到较难掌握的数学软件如Matlab了。不过，大多数规划问题Mathcad即可完成所赋予的任务。利用Excel求解规划问题有些“罗嗦”，但也不难掌握。下面以几个简单的实例说明其应用方法，希望各位能够举一反三，将其推广到多变量的情形。【例1】设有一位个体户制杯者，有两副模具，分别用来生产果汁杯和鸡尾酒杯。有关生产情况的各种数据资料见下表。*注：定点量为每周生产的最大数量。若每周工作不超过50小时，且拥有储藏量为140m3的仓库。问：该个体户如何安排工作时间才能使得每周的收益最大？若每周多干1小时，收益增大多少？通过加班加点达到的收益极限是多少？解：这个例子取自一本面向中学生的知识读物，是一个最大收益问题，可以建立模型如下：Maxf(x)?600x1?400x2?6x1?5x2?50?10x1?20x2?x1?6?x?0,x?02?1显然，约束条件中的第三个式子x16可以表作1*x1+0*x26，从而有如下矩阵?600c?400?6?x1?A?10x?，?x2?15?50?20，b?140?0?6?容易看到，上述模型表为矩阵形式便是：目标函数为Maxf(x)?cx?600T?x1?400?x2?约束条件为?6?Ax?10?1?x?x?1?x2?5?50?20?b?140?0?6?0下面是利用Excel求解规划结果的详细步骤：第一步，录入数据，定义有关单元格在Excel中，将有关数据资料按一定的规范录入，最好按照资料表格录入。其中单元格B3、B4中的数值为预设的迭代初始值，当然可以设为其他数值。图1录入数据，预设迭代初始值接着是定义单元格，方法与步骤如下：定义目标函数在B1单元格中输入公式“=F3*B3+F4*B4”，回车，这相当于建立目标函数公式f(x)?600x1?400x2定义约束条件在C6单元格中输入公式“=C3*B3+C4*B4”，回车；在D6单元格中输入公式“=D3*B3+D4*B4”，回车；在E6单元格中输入“=E3*B3+E4*B4”，回车。如果想一步到位，则可在C6单元格中输入公式“=$B$3*C3+$B$4*C4”，回车以后，用鼠标指向C6单元格的右下角，揿住左键，右拖至E6单元格。这几步相当于输入约束条件左半边?6x1?5x2?10x1?20x2?6x?0x2?1定义完毕以后，数据表给出了基于初始值结果。当然，如果初始值的设置不同，结果也会不同，但不影响最终求解答案。图2定义过单元格后的数据表第二步，规划选项沿着主菜单的“工具规划求解”路径打开“规划求解参数”对话框，进行如下设置：将光标置入“设置目标单元格”对应的空白栏中，再用鼠标选中B1单元格，这相当于将目标函数公式导入。在下面的最大值、最小值等选项中，默认“最大值(M)”因为本题是寻求最大收益。将光标置于“可变单元格”对应的空白栏中，用鼠标选中B3:B4单元格，这相当于令B3为x1，B4为x2。图3规划求解参数对话框接下是添加约束条件：点击图3中的添加(A)按钮，弹出“添加约束”对话框，将光标置于“单元格引用位置”对应的空白栏，用鼠标选中C6单元格；中间的小于等于号不变；再将光标置于“约束值”对应的单元格，用鼠标选中C5单元格。点击“添加”或“确定”按钮。这一步相当于表达式6x1?5x2?50图4添加约束第一步再次点击图3中的添加按钮，分别在有关位置设置D6单元格，小于等于号=，以及0。确定。这一步相当于公式x2?0图8添加约束第五步全部设置完毕以后，对话框的各项内容如下。如果打开“选项”对话框，还有更多的参数可以设置，不过对于简单的规划求解，那些选项暂时用不到。图9设置完毕以后的规划求解参数对话框第三步，输出结果在图9所示的对话框中，点击“求解”按钮，随机弹出“规划求解结果”选项框。若想知道详细的求解情况，可以选中“报告(R)”中的三个报告名称。图10规划求解结果对话框点击图10所示的“确定”按钮，立即得到求解结果：图11规划求解结果图12运算结果报告目的-通过该培训员工可对保安行业有初步了解，并感受到安保行业的发展的巨大潜力，可提升其的专业水平，并确保其在这个行业的安全感。