用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）。

excel求解线性规划和灵敏度分析实训过程记录及学习收获线性规划是一种数学优化模型，用于对一组线性限制条件下的线性目标函数进行优化。

Excel 能够进行线性规划问题的求解和灵敏度分析，以下是实习过程的记录和收获总结：1. 实训任务我们的实训任务是一个有饲料限制的生产计划问题，其中需要决定生产哪些种类的产品、购买何种原材料、以及在何时生产这些产品，以使得利润最大化。

任务中给定了各种产品需要的原材料数量，各种原材料的数量与价格，及一些限制条件，例如生产时间，最小生产量等。

2. Excel求解线性规划问题Excel中求解线性规划问题的函数是“Solver”，首先需要打开Excel中的“数据”选项卡，然后在“分析”工具中找到“Solver”。

进入“Solver参数”对话框后，需要输入目标函数和限制条件，并且设置决策变量的可变性、约束条件的类型和数量。

最后根据需要设置求解的约束条件和目标函数的目标方向，点击“求解”即可。

在我们的实训任务中，我们首先需要设置约束条件，限制了各种产品需要的原材料数量，并且确保生产时间在规定范围内。

然后我们需要设置各个决策变量的可变性，例如选择生产哪些产品，购买何种原材料以及在何时生产这些产品等。

最后将目标函数设置为生产的利润最大化，并且设置约束条件为“>=0”，以确保决策变量的可行性。

点击“求解”即可得出最优解。

3. Excel灵敏度分析Excel的灵敏度分析功能可以帮助我们了解线性规划问题的各个变量对于目标函数的影响程度。

Excel中灵敏度分析的函数是“规划求解器的报告”，在对话框中选择“接受解决方案”，然后勾选“制作规划求解器报告”选项，即可生成报告。

在报告中，我们可以看到各个决策变量的最优解以及目标函数的最优值。

同时，报告中还包括影响目标函数的变量的“系数范围”和“变化量”，我们可以通过调整这些参数来预测目标函数的变化情况。

4. 学习收获通过这次实训，我学会了如何使用Excel求解线性规划问题以及如何进行灵敏度分析。

实验二线性规划模型及灵敏度分析（一）实验目的：掌握使用Excel软件进行灵敏度分析的操作方法。

（二）实验内容和要求：用Excel软件完成案例。

（三）实例操作：（1）建立电子表格模型；（2）使用Excel规划求解功能求解问题并生成“敏感性报告”；（3）结果分析：哪些问题可以直接利用“敏感性报告”中的信息求解，哪些问题需要重新规划求解，并对结果提出你的看法；（4）在Word文档中书写实验报告，包括线性规划模型、电子表格模型、敏感性报告和结果分析等。

案例1 市场调查问题某市场调查公司受某厂的委托，调查消费者对某种新产品的了解和反应情况。

该厂对市场调查公司提出了以下要求：（1）共对500个家庭进行调查；（2）在被调查家庭中，至少有200个是没有孩子的家庭，同时至少有200个是有孩子的家庭；（3）至少对300个被调查家庭采用问卷式书面调查，对其余家庭可采用口头调查；（4）在有孩子的被调查家庭中，至少对50%的家庭采用问卷式书面调查；（5）在没有孩子的被调查家庭中，至少对60%的家庭采用问卷式书面调查。

对不同家庭采用不同调查方式的费用如下表所示：市场调查费用表家庭类型调查费用（元）问卷式书面调查口头调查有孩子的家庭50 30没有孩子的家庭40 25问：市场调查公司应如何进行调查，使得在满足厂方要求的条件下，使得总调查费用最少？案例2 经理会议建议的分析某公司生产三种产品A1，A2，A3，它们在B1，B2两种设备上加工，并耗用C1，C2两种原材料，已知生产单位产品耗用的工时和原材料以及设备和原材料的每天最多可使用量如下表所示：生产三种产品的有关数据资源产品A1 产品A2 产品A3 每天最多可使用量设备B1(min) 1 2 1 430设备B2(min) 3 0 2 460原料C1(kg) 1 4 0 420原料C2(kg) 1 1 1 300每件利润(元) 30 20 50已知每天对产品A2的需求不低于70件，对A3不超过240件。

90. 如何在Excel中进行敏感性分析？90、如何在 Excel 中进行敏感性分析？在当今的数据驱动时代，Excel 作为一款强大的电子表格软件，被广泛应用于各种数据分析和决策支持场景。

敏感性分析作为一种重要的分析方法，可以帮助我们了解模型中输入变量的变化对输出结果的影响程度，从而为决策提供更可靠的依据。

接下来，让我们一起深入探讨如何在 Excel 中进行敏感性分析。

首先，我们需要明确敏感性分析的概念。

简单来说，敏感性分析就是研究当模型中的某个或某些输入变量发生变化时，输出结果会如何相应地改变。

这对于评估模型的稳定性和可靠性，以及识别关键的影响因素非常有帮助。

在 Excel 中进行敏感性分析，通常可以采用以下几种方法：一、数据表格法这是一种较为直观和简单的方法。

假设我们有一个销售预测模型，其中销售量、单价和成本是影响利润的主要因素。

我们可以在 Excel 中创建一个数据表，将这三个变量放在列标题上，然后在不同的行中输入它们可能的取值。

接着，通过公式计算出每个组合下的利润。

这样，我们就可以直观地看到不同变量取值对利润的影响。

例如，假设利润的计算公式为：利润＝（销售量单价）成本。

我们可以在 Excel 中输入如下公式：在 B2 单元格输入：＝B1C1 D1然后通过向下填充或复制公式，得到不同变量组合下的利润值。

通过观察这个数据表，我们可以快速了解每个变量对利润的影响程度，例如销售量增加 10%时利润的变化情况，或者单价降低 5%时利润的变化情况。

二、单变量求解当我们想要知道当输出结果达到某个特定值时，某个输入变量应该取什么值时，可以使用单变量求解功能。

比如，我们仍然以销售预测模型为例，已知当前的销售量、单价和成本，以及计算出的利润。

现在假设我们希望利润达到一个特定的目标值，比如 10000 元，然后想知道在这种情况下，单价应该调整为多少。

操作步骤如下：首先，在 Excel 中输入利润的计算公式，然后选择“数据”选项卡中的“假设分析”，再点击“单变量求解”。