Excel规划求解.docx
- 文档编号:6476393
- 上传时间:2023-01-06
- 格式:DOCX
- 页数:12
- 大小:529.86KB
Excel规划求解.docx
《Excel规划求解.docx》由会员分享,可在线阅读,更多相关《Excel规划求解.docx(12页珍藏版)》请在冰豆网上搜索。
Excel规划求解
Excel2007“规划求解”
你的工资是每月3000元,工资系数(即工资增加率)是0.12,那么你的工资应该是多少?
在Excel2007中,这是很简单的。
表示如图1:
图1
图1中只有B3:
D4共6个单元格有数据,另外没有数据。
其中只有D4单元格(黄色的那个单元格)有公式,在编辑栏可以看到这个公式。
结果为3360元。
假设新工资要为3660元(即D4单元格为3360元),基础工资3000元不变,那么系数C4单元格为多少?
执行 数据---分析---规划求解,如图2:
图2
可以看到,没有约束条件,单击“求解”即可以得出答案。
如图3:
图3
可以看出,D4单元格(黄色),是目标单元格,是包含公式的。
C4单元格(灰色),是可变单元格,是一个数值,没有公式。
求解过程:
规划求解就是给C4单元格一个值0.12,看D4单元格等于3360,把它和结果3660比较,计算机发现它不是答案,返回给C4赋予一个新值0.121,结果D4单元格为3363,不对,计算机又给C4赋予另一值,这样不断尝试,直到得到符合计算机要求的答案出现,才停止尝试。
其实这个过程对人来说是很漫长和枯燥的,但是对计算机是非常快速和高效的,一下就完成了。
这是规划求解采用的基本计算方法,它就是“牛顿逼近法”。
可以看出这个过程实际是一个公式,从它的结果---y,去反方向推测它的变量---x为多少的过程。
这个例子告诉我们,使用“规划求解”必须首先确定“目标单元格”。
即对话框中第1个项,如图4:
图4
即黄色的那个单元格,它里面一定要包含公式。
把它设置为黄色,这是我的习惯,可以让自己清楚知道“包含公式的单元格”,并且看一眼就能发现。
你可以根据自己的习惯,设置一种自己喜欢的颜色填充。
其次,确定“可变单元格”,如图5,用红线圈住的部分:
图5
可以看出,可变单元格C4,灰色的那个单元格,没有公式,是一个普通的数值。
在求解过程中,Excel会不断改变C4单元格的值,直到满足要求为止。
我用灰色表示,是我的习惯,关键是让自己能一眼发现它。
excel2007要求可变单元格最多为200个。
再看看D4单元格的公式为=B4*(1+C4),能从这个公式里面发现C4单元格(一定能发现可变单元格),即灰色的那个单元格。
如果你把公式中C4单元格用数值0.12代替它,再运行规划求解,你会发现,无法得出答案。
但是如果你把公式中B4单元格用3000去替换,一样能采用”规划求解“得出正确答案。
可以得到这样一个结论:
目标单元格(黄色的)和可变单元格(灰色的)是通过目标单元格的公式联系在一起的。
就像函数y=f(x)一样,目标单元格就是y,可变单元格就是x,而=f(x)就是公式。
你懂了“目标单元格”和“可变单元格”,那么“规划求解”就已经知道了大部分了。
我认为这是“规划求解”最重要和基础的东西
实验案例:
用Excel建立最优订货批量模型.
1.基本数据。
假设某企业有四种存货需要采购,供应商也规定了各种存货的数量折扣,各种存货的基本数据如图1所示。
图1
2.最优订货批量求解分析区域的公式定义。
在计算分析区域分别定义采购成本、储存成本、订货成本、总成本、综合成本、最佳订货次数、最佳订货周期和经济订货量占用资金的公式。
定义方法是先定义B列的公式,然后复制到其他单元格,如图2所示。
图2
3.约束条件。
供应商提供的条件是:
甲、乙、丙、丁的订货批量分别为不小于400、350、500、和300。
根据以上条件,我们可以利用Excel提供的规划求解工具计算各种存货的最优批量。
操作如下:
1.选择[工具]/<规划求解>命令,弹出规划求解参数对话框,如图3。
(如果在[工具]菜单下没有“规划求解”命令,可以执行[工具]菜单下<加载宏>命令,从弹出的对话框中选择“规划求解”后即可。
)
2.设定规划求解参数。
如图3所示。
图3
3.求解。
当目标单元格、可变单元格、约束条件不变时,无论基础数据如何改变,都不需要修改上述设置,直接进行求解。
单击<求解>按钮,即可得出各种存货的最优批量,如图4所示。
图4
通过求解,求出了每种存货的最优批量,并自动计算出最优订货批量下的总成本、每年最佳订货次数和最佳订货周期等,同时,丰规划求解结果中,还提供了敏感性分析报告、运算结果报告和限制区域报告等,供企业了解经济订货批量求解的过程和结果。
在模型中,总成本与各要素之间建立了动态链接。
当企业财务政策发生变化,如存货年需要量改变,或经济条件发生变化,如每次订货变动成本、单位储存成本或单价等发生变化,我们只需改变基本数据区的各项数据,使用规划求解功能,最优订货批量模型即可迅速计算出相应的结果。
这跟高数中多元函数条件极值的问题相似。
第十节 回归与规划求解
回归分析在试验设计数据处理有非常重要的作用,例如正交设计、均匀设计、配方设计、复合设计都需要通过回归分析来寻找因素与响应变量间的关系,而EXCEL的数据分析工具库中就提供了回归分析的工具。
通过回归分析,一般会得到因素与响应变量间的拟合方程,那么怎样根据拟合方程来确定最优试验条件呢?
这时就可以使用数据分析工具库中的规划求解工具。
回归分析
在进行回归分析前,须将试验结果数据按规定的格式输入EXCEL表格中。
现假设为了解某指标Y与因素X1、X2之间的影响关系,进行了10次试验,试验数据结果输入EXCEL表中如图10-1。
图 10-1
试验数据输入完成后,在数据分析工具库中选择回归,出现如图10-2的对话框,对话框内容如下:
图 10-2
1. Y值输入区域:
选择响应变量试验结果数据所在的区域,可以包含标志。
2. X值输入区域:
选择因素取值数据所在的区域,可以包含标志。
.
3. 如果选择数据时包含了标志则选择标志复选框。
4. 如果强制拟合线通过坐标系原点则选择常数为零复选框。
5. 置信度:
分析置信度,一般选择95%。
6. 输出选项:
根据需要选择分析结果输出的位置。
7. 残差选项:
根据需要可选择分析结果中包含残差和标准残差以及残差图与线形拟合图。
8. 如果希望输出正态概率图则选择响应得复选框。
为了分析图10-1中的数据,按图10-2的内容输入对话框中,分析的数据结果如图10-3、10-4,图形结果如图10-5、10-6所示。
图 10-3
图 10-4
图 10-5
图 10-6
规划求解
假设现经试验设计及数据分析后得出,某试验指标Y与变量X1、X2之间的回归拟合方程为Y=150+20X1+30X2-46X1*X2-13X12-35X22,且拟合很好,现需确定最优的试验条件。
根据要求用极值法确定最优条件,可得方程组如下:
20-46X2-26X1=0
30-46X1-70X2=0
首先在EXCEL工作表中设计输入如图10-7的内容,其中C3=B3,B2=20-46*D3-26*D2,B3==30-46*D2-70*D3,D2、D3为求解值放置的单元格。
其中C3=B3的意义是选择B3中的公式为目标函数,另外的函数为约束条件。
图 10-7
选择工具菜单栏内的规划求解,出现如图10-8的对话框,对话框内容为:
1. 设置目标单元格,此例选择C3,并选择条件,本例为等于0。
2. 可变单元格:
放置解得单元格。
3. 约束:
编辑求解约束,单击添加,出现如图10-9的对话框,在对话框内可输入约束条件,输入后选择确定。
4. 可根据需要选择选项按钮,可对多项参数予以设定。
图 10-8
图 10-9
根据示例数据将各项输入对话框后确定,将出现求解结果如图10-10及敏感性报告结果如图10-11。
图 10-10
图10-11
运用规划求解还可以计算其它次元的方程,这里就不一一介绍。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 规划 求解
![提示](https://static.bdocx.com/images/bang_tan.gif)