excel规划求解技巧
wps表格规划求解
竭诚为您提供优质文档/双击可除wps表格规划求解篇一：excel规划求解功能操作说明excel规划求解功能操作说明以microsoftexcel20xx为例，说明使用excel的求解线性规划问题功能的使用方法。
一、加载规划求解功能1.点击【工具】按钮，在下拉菜单中选择【加载宏】功能。
2.在弹出的【可加载宏】选项卡中勾选【规划求解】，点击确定按钮。
此时，【工具】下拉菜单中增加规划求解功能，表示加载成功。
二、构造表格excel表格并填入各项数据以教材18页【例题2-8】为例，构造表格如下：1.录入约束条件系数约束条件（1）为5x1+x2-x3+x4=3，则在约束系数的第一行的x1,x2,x3,x4,x5，限制条件，常数b列下分别录入5,1，-1,1,0，=，3如下图所示。
约束系数区的第二行录入约束条件（2）的系数、限制符号及常数b，即-10,6,2,0,1，=，2；约束系数区的第三行录入约束条件（3）（x1≥0）的系数、限制符号及常数b，即1,0,0,0,0，≥，0；约束系数区的第四行录入约束条件（4）（x2≥0）的系数、限制符号及常数b，即0,1,0,0,0，≥，0；约束系数区的第五行录入约束条件（5）（x3≥0）的系数、限制符号及常数b，即0,0,1,0,0，≥，0；约束系数区的第六行录入约束条件（6）（x4≥0）的系数、限制符号及常数b，即0,0,0,1,0，≥，0；约束系数区的第七行录入约束条件（7）（x5≥0）的系数、限制符号及常数b，即0,0,0,0,1，≥，0。
如下图所示。
2.录入目标函数系数目标函数为maxz=4x1-2x2-x3，则在目标函数的x1,x2,x3,x4,x5列下分别录入4，-2，-1,0,0，如下图所示。
3.录入约束条件的计算公式双击约束条件（1）行的“总和”单元格，录入以下内容：“=b3*b12+c3*c12+d3*d12+e3*e12+F3*F12”说明：录入的内容即是约束条件（1）的计算公式，其中“b3*b12”代表5x1;“c3*c12”代表1x2；“d3*d12”代表-1x3；“e3*e12”代表1x4；“F3*F12”代表0x5。
Excel求解LP
Excel 补充讲义第一部分：用Excel 求解LP(线性规划)问题用Excel 提供的“规划求解”功能可以解决许多求极值、解方程的问题，现在主要介绍解线性规划问题。
以解教材第二章末习题第3b 题为例。
Min f = 4x 1+6x 2s.t. 3x 1 - x 2 ≥ 6x 1 + 2x 2 ≤ 107x 1 - 6x 2 ＝ 4x 1 , x 2 ≥ 0一、检查是否加载了宏“规划求解”？即查看Excel 窗口的“工具”菜单下是否有“规划求解”菜单条？如尚未加载，有两种方法解决：1、 找到与你的微机中已装的Office 版本一致的Office 安装盘。
单击Excel 窗口的“工具”→“加载宏”，在所弹出的“加载宏”对话框中选“规划求解”，单击“确定”(如下左图1)。
2、 可以先找Office 安装盘中的一个名为“Slover ”的子目录，把它拷贝到你的微机中如C 盘根目录下，Excel 窗口的“文件”→“打开”→“C:\Slover\Slover.xla ”，在警告框中选“启用宏”(如下右图2)。
二、输入系数：在单元格A2:D4中分别输入两个不等式约束的系数与常数项,在单元格A5:B5中分别输入目标函数的两个系数,在单元格A1:B1中任意输入两个数分别作为决策变量x 1,x 2的值(如右图3，C 列暂空) 。
三、在C2单元格中输入“=A2*A$1+B2*B$1”，并复制到C3、C4、C5中，使它们分别变为 “=A3*A$1+B3*B$1” 、“=A4*A$1+B4*B$1”和 “=A5*A$1+B5*B$1”。
四、选中单元格C5后）菜单“工具”→“规划求解”，弹出“规划求解参数”对话框如图4，逐一填充各栏中的空白：①在“设置目标单元格”栏后的空白中填入$C$5，并选中“最小”；②在“可变单元格(B)”栏后的空白中填入$A$1:$B$1；③光标指向“约束”栏，按“添加”，出现“添加约束”对话框（如下图5），依次填入约束关系，每输完一条，按“添加”，输入所有约束条件后，按“确定”，又退回到图6状态，在图6中可以选“更改”、“删图3.输入系数图1.加载宏图2.启用宏图7.对话框“规划求解结果”图6.填入了参数的对话框“规划求解参数” 除”、“全部重设”来编辑约束条件及其他设置。
实验五_运用Excel规划求解进行最优投资组合的求解
实验报告证券投资学院名称专业班级提交日期评阅人 ____________评阅分数 ____________实验五：运用Excel规划求解进行最优投资组合的求解【实验目的】1、理解资产组合收益率和风险的计算方法.熟练掌握收益率与风险的计算程序；2、进一步理解最优投资组合模型.并据此构建多项资产的最优投资组合；【实验条件】1、个人计算机一台.预装Windows操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、matlab或者Excel软件。
【知识准备】理论知识：课本第三章收益与风险.第四章投资组合模型.第五章 CAPM实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章.第四章.第五章【实验项目内容】请打开参考《金融建模—使用EXCEL和VBA》电子书第四章相关章节（4.3）完成以下实验A．打开“实验五组合优化.xls”.翻到“用规划求解计算最优组合”子数据表；B．调用规划求解功能进行求解。
点击“工具”在下拉菜单点击“规划求解”.如没有此选项说明需要加载规划求解后才能使用.如何加载见实验补充文档“EXCEL规划求解功能的安装”。
C．D．在规划求解选项卡里面选择“选项”.再选择“非负”再运行一次.比较两次返回的投资比例值的正负。
在实验报告中记录两次得到的最优投资组合.并说明投资比例是负值说明什么？E．（选做）借助连续调用规划求解的VBA过程生成有效组合以及资本市场线。
参考实验参考电子书《金融建模—使用EXCEL和VBA》电子书第四章P83F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？【实验项目步骤与结果】A.B．使用规划求解C．投资比例为负值说明该证券风险远远大于其收益率.已经不适合投资。
F．对比可卖空和不可卖空的有效前沿图试对比说明其不同？通过可卖空和不可卖空有效前沿图的对比可以看到.在相同风险的时候可卖空的情况下期望回报要比不可卖空的情况要高.并且随着风险的增加可卖空曲线的期望回报增加程度明显比不可卖空曲线要大。
excel-ch19 规划求解
19-2 規劃求解三要素
規劃求解前需先將題問定義清楚，才不會假設錯誤，而
導致錯誤的答案。 1.首先，規劃的目標要明確，是求目標的最大值(利潤)， 或求目標的最小值(如成本)或某特定的值。 2.變數儲存格：我們希望這些儲存格能找到一組最佳組 合，來達成上述的規劃目標 3.限制條件：我們希望變數在是某些限制之下，可達到 的是最佳結果。
6
19-3實例應用-續
因執行規劃求解後，它會自動蓋掉原始檔案的變數資料，所以
我們還是複製一份原來的工作表來前後比較。 選取工讀生值班表工作表、按右鍵，叫出快顯功能表，選擇 [移動或複製] ，利用建立副表的方式插在Sheet2之前。
7
19-3實例應用-續
Step8：重新命名為「最佳工讀生值班表」，利用這
個複製的工作表來作規劃求解。 執行[工具/規劃求解] 。
8
19-3實例應用-續
設定目標儲存格為「D19」，即每週薪資費用，等於「最
小值」。而變數儲存格設定範圍，D6~D12儲存格看看真 正需要多少工讀生，如何將人數安排到符合需求的最小值。 [新增]限制式，每梯次輪班的工讀生人數都得大於等於0。
9
19-3實例應用-續
再新增一個限制式，每梯次輪班的工讀生人數都得是
整數，與上一個限制式合併起來的意思，就是每梯次 輪班的工讀生人數都必須是正整數或0。 [ 再新增一個限制式，每一天的工讀生總數至少必須大 於或等於我們評估每一天需求的工讀生總數，按[確定] 鈕。
10
19-3實例應用-續
回到[規劃求解參數]交談窗，按[選項]。 開啟[規劃求解選項]交談窗，找出一組最佳解，可能存在，但有時可
能不存在，當不存在時，反覆計算是無意義的。我們可在這邊設定 [最長運算]時間，及[反覆運算]次數等，在工讀生輪班這種題目，勾 選[採用線性模式]會較快。 按[確定]回上螢幕。
01-第一章 用Excel求解规划问题
第一章 用Excel求解规划问题
用Excel进行灵敏ห้องสมุดไป่ตู้分析
回顾上节的例子： 回顾上节的例子： 某工厂要生产两种产品：门和窗； 某工厂要生产两种产品：门和窗； 每扇门需要在车间 加工 小时，车间3加工 小时； 加工3小时 每扇门需要在车间1加工 小时，车间 加工 小时； 车间 加工1小时 每扇窗需要在车间 加工 小时，车间 加工 小时； 加工2小时 每扇窗需要在车间2加工 小时，车间3加工 小时； 车间 加工2小时 车间用于生产这两种产品的时间：车间 为 小时 车间2为 小时， 车间用于生产这两种产品的时间：车间1为4小时，车间 为12 小时，车间 为 小时 小时； 小时，车间3为18小时； 每扇门的利润300元，窗的利润500元； 元 窗的利润 每扇门的利润 元 如何确定产品周生产计划，使总利润最大？ 如何确定产品周生产计划，使总利润最大？ 最优解： 最大利润3600 3600。 最优解：x1=2; x2=6, 最大利润3600。
第一章 用Excel求解规划问题
用Excel进行灵敏度分析
那么，考虑以下的问题： 那么，考虑以下的问题： 如果门的利润由300提高到400，最优解是否改变？ 如果门的利润由300提高到400，最优解是否改变？对总利润 300提高到400 有怎样的影响？ 有怎样的影响？ 如果车间1的可用工时增加2个小时，总利润是否变化？ 如果车间1的可用工时增加2个小时，总利润是否变化？如何变 化，最优解是否发生变化？ 最优解是否发生变化？ 如果车间2更新工艺，生产一扇窗由以前的2小时下降到1小时， 如果车间2更新工艺，生产一扇窗由以前的2小时下降到1小时， 最优解如何变化？ 最优解如何变化？ 如果工厂新增加用电限制，是否会改变原来的最优方案？ 如果工厂新增加用电限制，是否会改变原来的最优方案？ 。。。
用Excel软件求解规划的方法
Microsoft Excel软件是当今十分流行的功能 Excel软件是当今十分流行的功能 强大操作方便的软件。在Microsoft Excel软 强大操作方便的软件。在Microsoft Excel软 件中，具有规划求解功能。如图1 件中，具有规划求解功能。如图1，在工具 菜单下，一般有“规划求解” 菜单下，一般有“规划求解”项，若未有， 则应先运行“加载宏” 则应先运行“加载宏”项目把其安装上。
图8
此时按“求解”按钮即可获得结果如图9 此时按“求解”按钮即可获得结果如图9。
图9
这时从A6至E9处可读出模型的最优解为: 这时从A6至E9处可读出模型的最优解为: x11=25000、 x20=14000、x30 =21000、x11=25000、 x20=14000、 x21=16000、y1=1,其余变量均为0。再从F14 x21=16000、y1=1,其余变量均为0。再从F14 处读出模型的最优值为2384095。 处读出模型的最优值为2384095。
图7
进入“规划求解”界面。“ 进入“规划求解”界面。“设置目标单元 格”处输入“F14”,然后选“最小值”，再 处输入“F14” 然后选“最小值” 在“可变单元格”处输入“A6:E9”,在“约 可变单元格”处输入“A6:E9” 束”处添加12个约束：⑴“A8：E8>=0”、 处添加12个约束：⑴“A8：E8>=0” ⑵“A9=1”、⑶“B9：E9＝二进制”、⑷ A9=1”、⑶“B9：E9＝二进制” “A10=35000”、⑸“B10=0”、⑹ “C10=0”、 A10=35000”、⑸“B10=0” C10=0” ⑺ “D10=0”、⑻ “E10=0”、⑼“F6=G6”、 D10=0” E10=0”、⑼“F6=G6” ⑽ “F7=G7”、⑾ “F8=G8”、⑿ “F9=1”。 F7=G7” F8=G8” F9=1” 最后，规划求解参数界面如图8 最后，规划求解参数界面如图8。再在 “选项”中选择“采用线性模型”。 选项”中选择“采用线性模型”
EXCEL求解线性规划问题
约束右端值降低15时，目旳函数值旳变化量。
解：（1）最优解为x1=0, x2=12.4, x3=9.5
（2） x1旳目旳系数降低5，占允许降低旳百分比=5/∞=0%，x2 旳目旳系数增长4,占允许增长旳百分比=4/7.8=51.2%。
变化旳百分比和为51.2%，没有超出100%，所以最优解不变。
（3）第一资源约束右端值增长30，占允许增长旳30 /∞=0%， 第二资源约束右端值增长4 ，占允许增长旳4/15=26.7%，
•初值和终值分别指 单元格在此次求解 前旳数值和求解后 旳数值。
敏感性分析报告（1）
可变单元格中 • “单元格”指决策变量所在单元格旳地址 • “名字”是决策变量旳名称 • “终值”是决策变量旳终值,即最优值 • “递减成本”指最优解中档于0旳变量，相应旳目旳函数中旳系数
增长或降低多少，最优解不再为0 • “目旳式系数”目旳函数中旳系数，为已知条件 • “允许旳增量”与“允许旳减量”表达目旳函数中旳系数在增量
（1）引用旳类型
三种类型 ：
相对引用、 绝对引用、混合引用
（2） 相对引用
格式： A3 、B6
使用相对引用后，系统将会记住建立公式旳单元格和被 引用旳单元格旳相对位置，在复制这个公式时，新旳公式单 元和被引用旳单元依然保持这种相对位置。
（3）绝对引用 格式：$a$3 $d$5
绝对引用是指被引用旳单元与引用旳公式单元旳位置 关系是绝正确，不论将这个公式复制到任何单元，公式所 引用旳还是原来单元格旳数据。
2) 在弹出旳对话框中旳“可用加载宏”列表框 中,选定待添加旳加载宏“规划求解”选项旁 旳复选框,然后单击“拟定”.单击“拟定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
规划求解_精品文档
“规划求解”是 Excel 中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格(被称为目标单元格)中公式(公式:单元格中的一系列值、单元格引用、名称或者运算符的组合，可生成新的值。
公式总是以等号( =)开始)的最优值。
“规划求解”将对直接或者间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。
“规划求解”通过调整所指定的可更改的单元格 (可变单元格) 中的值，从目标单元格公式中求得所需的结果。
在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件(约束条件：“规划求解”中设置的限制条件。
可以将约束条件应用于可变单元格、目标单元格或者其它与目标单元格直接或者间接相关的单元格。
而且约束条件可以引用其它影响目标单元格公式的单元格。
使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或者最小值。
)Microsoft Excel 的“规划求解”工具取自德克萨斯大学奥斯汀分校的 Leon Lasdon 和克里夫兰州立大学的 Allan Waren 共同开辟的 Generalized Reduced Gradient(GRG2)非线性最优化代码。
线性和整数规划问题取自 Frontline Systems 公司的 John Watson 和 Dan Fylstra 提供的有界变量单纯形法和分支边界法。
安装 office 的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。
下面是加载“规划求解”宏的步骤：(1)在“工具”菜单上，单击“加载宏”。
(2)在弹出的对话框中的“可用加载宏”列表框中，选定待添加的加载宏“规划求解”选项旁的复选框，然后单击“确定”。
单击“确定”以后，“工具”菜单下就会浮现一项“规划求解”命令。
如果需要其他功能，也可以用鼠标勾选。
注意:加载的宏越多， Excel 启动的时候就会越慢，所以应工具需要选择。
利用Excel求解线性规划问题
利用Excel求解线性规划问题线性规划问题的求解有很多方法，也有很多工具。
比如常用的Matlab、Lingo，记得参加数学建模的时候就是用的Lingo解决线性规划问题的。
本文主要讲解如何使用Excel求解线性规划问题，Excel本身是没有计算线性规划问题能力的，因此我们首先要加载相应的宏定义。
一、加载宏定义（不同版本的加载方式有所不同）：Excel 2003：单击“工具”菜单，然后单击“加载宏”，选择“规划求解”点击确定。
Excel 2007：方法一：用快捷键。
先按Alt+T，再按I键，即可打开加载宏对话框。
方法二：单击“Office按钮→Excel 选项→加载项”，确保“管理”右侧下拉列表中的选项是“Excel 加载项”，单击“转到”按钮即可。
Excel 2010：直接在功能区中选择“开发工具”选项卡，在“加载项”组中单击“加载项”命令，选择“规划求解”点击确定。
注意：如果功能区中没有“开发工具”选项卡，可以通过自定义功能区来显示“开发工具”选项卡：单击“文件→选项→自定义功能区”，然后在右侧区域中勾选“开发工具”并单击“确定”。
二、初始化数据（以Excel 2010为例，其他版本大同小异）：比如我们要计算的线性规划问题如下：那么，我们可以构造如下的表格数据。
其中，B2:F2为待求的值Xi，B3:F3为目标函数的系数，B4:F4、B5:F5、B6:F6为约束条件的系数。
在G3单元格中输入公式=$B$2*B3+$C$2*C3+$D$2*D3+$E$2*E3+$F$2*F3，并将鼠标放到单元格的右下角会变成黑色十字架，向下拖拽复制单元格公式到G4、G5、G6单元格。
然后，单击“数据”选项卡，单击“规划求解”打开“规划求解参数”对话框。
∙修改“设置目标”为$G$3,即最优解下目标函数的值z所在的单元格。
∙选择是求最大值，还是最小值。
∙“可变单元格”指的是最优解取值变量所在的单元格。
∙“遵守约束”指的是约束条件中对各变量的约束情况。
Excel 使用规划求解
Excel 使用规划求解单变量求解只能计算出某一个特定值，当要预测的问题含有多个变量或有一定取值范围时，应使用Excel提供的规划求解功能，来确定目标单元格的最优值。
“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。
其中，财务管理中涉及到很多的优化问题，如最大利润、最小成本、最优投资组合、目标规划、线性回归及非线性回归等等，均可用到规划求解。
1．安装规划求解加载项规划求解是一个加载宏的程序，在使用前应先确定该程序已经安装到计算机上。
如果还没有安装，用户可以单击Office按钮，并单击【Excel选项】按钮，在弹出的对话框中，选择【加载项】选项卡。
然后在【加载项】栏中选择【规划求解加载项】项，并单击【转到】按钮，如图8-8所示。
选择单击图8-Excel 设置加载项在弹出的【加载宏】对话框中，启用【规划求解加载项】复选框，单击【确定】按钮，即可安装。
单击选择9-9 加载规划求解项2．使用规划求解规划求解是一组命令的组成部分，也可以称为假设分析。
假设分析的过程是通过更改单元格中的值来查看这些更改对工作表中公式结果的影响。
例如，更改分期支付表中的利率可以调整支付金额。
规划求解的主要功能如下：●可以求出工作表上某个单元格（称为目标单元格）中公式的最优值。
●规划求解将对直接或间接与目标单元格中的公式相关的一组单元格进行处理。
●将调整所指定的变动单元格（称为可变单元格）中的值，从目标单元格公式中求得所指定的结果。
●可以应用约束条件来限制“规划求解”在模型中使用的值，而且约束条件可以引用并影响目标单元格公式的其他单元格。
例如，企业在某月份生产甲、乙两种产品，其有关资料如图8-10所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润。
选择【数据】选项卡，单击【分析】组中的【规划求解】按钮，弹出【规划求解参数】对话框。
然后在【设置目标单元格】文本框中，输入“$D$6”单元格；在【可变单元格】文本框中，输入“$C$8,$C$9”单元格，如图8-11所示。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
excel规划求解技巧
Excel是一款功能强大的办公软件，可以用于各种各样的数据分析和规划求解。
下面将介绍一些常用的Excel规划求解技巧。
1. 目标单元格设置
在Excel中进行规划求解时，首先要明确规划的目标是什么。
在工作表中选中目标单元格，然后点击工具栏中的“数据”选项卡，再点击“规划求解器”来确定规划的目标单元格和范围。
2. 约束条件设置
在进行规划求解时，通常还需要设置一些约束条件。
在工作表中选中约束条件的单元格，同样通过“数据”选项卡中的“规划求解器”设置约束条件的范围和限制条件。
3. 定义变量和约束条件
在规划求解中，通常需要定义一些变量和约束条件。
通过在工作表中建立一个台账来定义这些变量和约束条件，并在规划求解器中引用这些单元格。
4. 选择正确的规划方法
Excel的规划求解器提供了多种求解方法，包括线性规划、整数规划、非线性规划等。
在选择规划方法时，要根据具体的问题需求来决定。
5. 设置目标函数和约束条件
在规划求解器的设置中，需要将目标函数和约束条件输入进去。
选择正确的单元格来表示目标函数和约束条件，并在规划求解器中指定这些单元格。
6. 设置求解参数
在规划求解器中，还可以设置一些求解参数，如求解时间限制、容差等。
根据实际情况调整这些参数，以获得更加准确的结果。
7. 进行规划求解
设置好目标函数、约束条件和求解参数后，点击求解按钮开始进行规划求解。
Excel会自动寻找最优解，并将结果显示在相应的单元格中。
8. 分析结果
在得到规划求解的结果后，可以进行进一步的分析。
通过调整目标函数和约束条件的值，观察结果的变化，以便做出更好的决策。
9. 优化模型
在进行规划求解时，可能需要根据实际情况调整模型。
可以尝试改变目标函数或约束条件的形式，以达到更好的优化效果。
10. 使用宏和VBA
Excel中还可以使用宏和VBA编程来进行规划求解。
通过编写自定义的宏或VBA代码，可以实现更加复杂和灵活的规划求解。
总之，Excel是一款非常方便和实用的规划求解工具。
通过合理的设置和使用，可以在工作和学习中更加高效地进行规划和决策。
通过不断的实践和学习，可以进一步提升对Excel规划求解技巧的掌握和运用。