excel-各种规划需求运算表
Excel高级应用：Excel的规划求解功能
Excel的规划求解功能目录•引例•EXCEL中的规划求解工具•线性规划求解方法•对偶问题与影子价格•线性规划的敏感度分析•整数规划求解•非线性规划求解•目标规划问题求解•综合运用引例•生产两种风机（风机A和风机B）。
•生产风机A，需要工时3小时，用电4千瓦，钢材9吨；•生产风机B，需要工时7小时，用电5千瓦，钢材5吨。
•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。
•假设，两种产品的单位利润分别为200万元和210万元。
怎样安排两种产品的生产量，所获得的利润最大？规划求解就是用来解决这类问题的，其实就像是在做应用题，设未知数，然后写函数。
规划求解的第一步也是将所描述的问题数学化，模型化。
接下来按照解题格式来做一下上面的应用题。
引例•生产两种风机（风机A和风机B）。
生产风机A，需要工时3小时，用电4千瓦，钢材9吨；生产风机B，需要工时7小时，用电5千瓦，钢材5吨。
•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。
•假设，两种产品的单位利润分别为200万元和210万元。
怎样安排两种产品的生产量，所获得的利润最大？规划求解的第一步也是将所描述的问题数学化，模型化。
解：设风机A产量为x，风机B产量为y，最大利润为Pmax•x,y>=0•3x+7y<=300•4x+5y<=250•9x+5y<=420•Pmax=200x+200y引例•生产两种风机（风机A和风机B）。
生产风机A，需要工时3小时，用电4千瓦，钢材9吨；生产风机B，需要工时7小时，用电5千瓦，钢材5吨。
•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。
•假设，两种产品的单位利润分别为200万元和210万元。
怎样安排两种产品的生产量，所获得的利润最大？规划求解的第二步也是将数学模型，输入Excel表格，构建关系引例规划求解的第二步也是将数学模型，输入Excel表格，构建关系，并将约束条件输入规划求解参数表引例通过规划求解功能，找到答案引例•1939年，前苏联科学家康托洛维奇总结了他对生产组织的研究，写出了《生产组织与计划中的数学方法》一书，是线性规划应用于工业生产问题的经典著作。
运筹学数学excel操作实例
根据对上述建模过程的总结，在电子表格中建立线性规划模型的步骤可归纳如下：
回忆例2-1某制药厂的生产计划问题，其求解结果如图13-8所示，即生产4公斤药品Ⅰ和2公斤药品Ⅱ，总利润为1400元.但该最优解是在假设所有的模型参数都准确的前提下做出的，在此基础上，管理层如果进一步考虑下列问题：
图13-11右下部分的“规划求解”对话框显示了求解时应注意的问题：求目标单元格的最大值（利润最大）；约束为设备的实际使用时间小于等于设备的可用时间及实际总业务量小于等于总业务提供量的限制.
打开“选项”对话框，仍选择“采用线性模型”和“假定非负”，回到“规划求解”并按“求解”按钮，得到问题的最优方案为：每月X线及CT检查的业务量分别为1320人次和480人次，磁共振业务量为0，即不必购买该设备；按最优方案安排业务每月可获利55200元.
图13-10的右半部分显示了“规划求解”对话框及“选项”对话框的内容.该问题的目标是所用的胶管原料的总根数最少，因此设置目标单元格为I12等于最小值.由于实际获得的材料数量必须满足需求量的要求，考虑到最优方案（各种截法的某一组合）不一定能使截出的三种材料数量恰好等于需要的数量，而某种材料超过需求量是允许的，故在添加约束时可设置实际截得的数量大于等于需求量，即I9：I12>=K9：K12（本题中，该约束取“>=”和“=”的结果是相同的）；又由于截出的各种材料数量均为整数，因此约束中应包括决策变量取整数的限制，即C13:H13=整数.
用excel解决整数规划问题
实验二Ｅｘcel解决整数规划问题一、问题的提出某公司拟用集装箱托运甲、乙两种货物,这两种货物每件的体积、重量、获得利润以及托运所受限制如下表所示:二模型得出分析：这个问题是一个整数规划问题, 故应该确定决策变量、目标函数及约束条件。
设X1,X2分别为甲乙两种货物托运的件数,显然,X1,X2是非负的整数,这是一个纯整数规划问题，根据问题的要求可知对于货物总体积的托运限制最大不得超过1365立方英尺,故应有约束条件:195 Ｘ１+２7３Ｘ２≦1365对于货物总重量的托运限制为最大不得超过140千克,故应有约束条件为:4X1+40Ｘ２≦１40同时有：Xｉ≥0，ｉ=1,2希望货物托运的配置,使得可获得利润最大,即求W=2Ｘ1+3X2 的最大值由分析可得如下模型:ＭaxＷ=2X1+3X2 (所获利润最大）约束条件如下195 X1+2７3 X２≦13654X1+40X2≦１40X i≥0, ｉ＝1,2Ｘ1≦4三、模型求解1．建立规划求解工作表（如下图所示）⑴．在可变单元格（B４:C４)中输入初始值（１，1）⑵．在上图有关单元格输入如下公式单元格地址公式Ｃ６ =B２*B4+Ｃ2*C４Ｃ７ =B3＊B4+C3＊C４C8 =B5*Ｂ4+C5*C4⑶.求最佳组合解：①．选取[工具]→［规划求解…]出现如下对话窗：②．在“设置目标单元格”窗口，输入C8。
③．选定“最大值”选项。
④．在可变单元格中输入B4：C4。
⑤．选取“添加”，出现“添加约束”窗口,在“添加约束”窗口输入:单元格引用位置运算符号约束值B4：Ｃ4 int单击“添加”,再输入以下约束条件:B4：C４ >= 0单击“添加”,再输入以下约束条件：B４ >＝ 4单击“添加”，再输入以下约束条件：C6 <= １365单击“添加”,再输入以下约束条件：C7 ＜= １40,单击“确定”⑥在“规划求解参数”窗口，选择“求解。
”⑦选择“确定”,（计算结果如下表所示）⑧在“规划求解结果”对话框中选定保存“规划求解结果”，单击“确定”。
EXCEL求解规划问题
第二节 使用Excel 求解线性规划一、典型案例案例 求解线性规划问题：12312312312123m ax 21226..390,0,0z x x x x x x x x x s t x x x x x =-+++≤⎧⎪+-≤⎪⎨-+≤⎪⎪≥≥≥⎩二、解决方案Excel 具有强大的规划求解功能，可以解决最多有200个变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的线性规划与非线性规划问题．因此，可通过Excel 的规划求解功能实现问题的求解。
三、Excel 演算步骤第一步：启动Excel ，在工作表中的A1，A2，A3，A10，E3，F3单元格中分别输入文字“目标函数系数”，“决策变量”，“约束条件”，“目标函数值”，“约束条件左端的值”，“约束条件右端的值”；在B1，C1，D1单元格中输入目标函数的系数1，-2，1，在B4，C4，D4单元格中输入第一个约束条件的系数1，1，1；同理，在相应单元格中输入其他约束条件的系数与约束条件右端的值，如下图9-1所示：图9-1 数据输入第二步：计算约束条件左端的值和目标函数值．因为约束条件左端的值等于约束条件的系数乘以相应的决策变量，所以在E4单元格中输入公式“=B4*B2+C4*C2+D4*D2”，在 E5单元格中输入公式“=B5*B2+C5*C2+D5*D2”，依次类推在E9单元格中输入公式“=B9*B2+C9*C2+D9*D2”；目标函数的值等于目标函数系数乘以决策变量，从而在D10单元格中输入公式“= B1*B2+C1*C2+ D1*D2”，如图9-2所示．图9-2 计算约束条件左端的值和目标函数值事实上，在计算约束条件左端值时，只需要在E4单元格中输入公式“=B4*$B$2+C4*$C$2+D4*$D$2”，然后单击E4单元格，将鼠标至于E4单元格右下角，当光标变为小黑十字时拖曳至E9单元格即可．第三步：单击【工具】菜单中的【规划求解】命令，在弹出的规划求解对话框中输入各项参数．(1)设置目标单元格和可变单元格在“规划求解参数”对话框中选中“最大值”前的单选按钮，设置目标单元格为“$D$10”，可变单元格为“$B$2:$D$2”，如图9-3所示．图9-3“规划求解参数”对话框(2)添加约束条件单击【规划求解参数】对话框中的【添加】按钮，打开【添加约束】对话框，单击单元格引用位置文本框，然后选定工作表中的E4单元格，则在文本框中显示“$E$4”，选择“<=”约束条件；单击约束值文本框，然后选定工作表中的F4单元格，如图9-4所示．图9-4 “添加约束”对话框图中所示约束条件表示12312x x x++≤，依此类推，把所有约束条件都添加到【规划求解参数】对话框的【约束】列表框中．注意：如果在【工具】菜单中没有见到【规划求解】命令，则要单击【工具】【加载宏】命令，在弹出的【加载宏】对话框中的列表框中，选定【规划求解】选项前的复选框．第四步：在【规划求解参数】对话框中单击【求解】按钮，弹出图9-5所示的【规划求解结果】对话框，选中【保存规划求解结果】单选按钮．图9-5 “规划求解结果”对话框第五步：在【规划求解结果】对话框中，单击【确定】按钮，工作表中就显示出规划求解的结果，如图9-6所示．图9-6 结果显示如果要生成运算结果报告，可在【规划求解】对话框中选择【报告】列表框中的【运算结果报告】．单击【确定】按钮，则产生如图9-7所示的运算结果报告表，在该表中对约束条件和结果作出了详细的说明．图9-7 运算结果报告表从图9-6或图9-7可以很容易看出，当变量1236,0,6x x x ===时，目标函数的最大值为m ax 12z =．。
Excel单变量与规划求解模拟运算表
求在符合饲料营养成份要求的前提下，如何配合这四种饲料，使饲料配合的费用
最低。
3
数学模型
根据以上资料，设四种原料的配比比例分别为X1、X2、X3、X4， 可列线性规划模型如下： 25%*X1＋8%*X2＋20%*X3＋7%*X4≥15%（蛋白质约束条件） 2%*X1＋1%*X2＋8%*X3＋5%*X4≥4.5%(脂肪约束条件) 10%*X1＋5%*X2＋1%*X3＋40%*X4≥30%（淀粉约束条件） 2%*X1＋40%*X2＋0.5%*X3＋6%*X4≤10%（纤维素约束条 件） X1≥0，X2≥0，X3≥0，X4≥0（非负条件） Z=500X1＋50X2＋350X3＋450X4（目标函数值最小） EXCEL操作演示
6
四、模拟运算表
Excel作为一个电子表格其作用不仅仅是数据的电子化存储及排序和检 索，它还有另外一项很重要的功能，那就是数据分析功能，这里用 得最多的就是模拟运算表。 例如：要制作一个数据分析表，要求显示公式中某个值的变化将如何影 响公式的结果。模拟运算表提供了一种快捷手段，它可以通过一步操作 计算出多种情况下的值；同时它还是一种有效的方法，可以查看和比 较由工作表中不同变化所引起的各种结果。 例.银行存款年利率为5%,存入10000元,存10年后本息合计多少元?
有15％，B（脂肪）至少含有4.5％，C（淀粉）至少含有30％，D（纤维素）不得
超过10％。所能提供的原料有四种，甲（花生饼），每吨单价500元，含有各种 营养成份分别为A25％、B2％、C10％、D2％；乙原料（花生秧），每吨单价为50
Excel规划求解功能的使用教程
Excel规划求解功能的使用教程Excel中经常需要使用到规划求解功能进行求解，规划求解功能具体该如何使用呢?下面是店铺带来的关于Excel规划求解功能的使用教程，希望阅读过后对你有所启发!Excel规划求解功能的使用教程：规划求解使用步骤1：安装规划求解：规划求解是Excel的一个插件，需要安装。
打开新建文档左上角OFFICE按钮——Excel选项——自定义——从下列位置选择命令(所有命令)——加载宏——添加——确定。
点击“加载宏”工具，弹出【加载宏】对话框，勾选“分析工具库“和”规划求解加载项“，点击”确定“。
随即弹出Microsoft Office Excel对话框，点击”是“。
开始安装。
规划求解使用步骤2：创建表格，如下。
单击“数据“工具栏，选择”规划求解“，随即弹出【规划求解参数】对话框，在【设置目标单元格】中输入“$B$12”;在【可变单元格】中输入“$C$3：$C$5”，单击“添加”按钮，弹出【添加约束】对话框，在【单元格引用位置】输入“$B$10”，在其右侧的下拉列表中选择【<=】，在【约束值】中输入“$B$7”。
规划求解使用步骤3：单击“添加”按钮，继续添加约束条件。
使用相同方式，再添加4个约束条件。
规划求解使用步骤4：约束条件添加完毕，单击“确定”按钮，返回【规划求解参数】对话框，此时可发现在【约束】列表中显示出了添加的所有约束条件，然后单击“选项”按钮。
随即弹出【规划求解选项】对话框，选中“采用线性模型”和“假定非负”，其余保持默认设定，单击“确定”。
返回【规划求解参数】对话框，单击“求解”按钮。
规划求解使用步骤5：随即弹出返回【规划求解结果】对话框，提示已经找到一解满足条件，同时在工作表中显示出计算结果，用户可以看到各种产品的售出数量以及“最大利润”的数值。
在【规划求解结果】对话框中的“报告”中选择“运算结果报告”，单击“确定”，返回工作表中，此时系统自动地在工作簿中插入一个《运算结果报告1》工作表，并显示出结果报告。
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的规划求解
Excel的函数公式一、在EXCEL中如何从一列数据中找出某些数的和等于一个数字1、首先我们在D3单元格输入一个求和公式：=SUMPRODUCT(A2:A14*B2:B14)2、然后在D4单元格输入一个求差公式：=D1-D3。
3、然后，选择【数据】-【规划求解】。
4、选择【目标单元格】为D4，选择【值】处输入0。
5、点击选择按钮选择【可变单元格】6、区域为B2:B14（即A列数据对应B列区域）。
7、点击【添加】【约束条件】8、具体按下图设置。
9、点击【求解】按钮开始计算求解。
10、运算结束后弹出如下对话框，选择【保存规划求解结果】11、这时B列数值为1对应A列数据之和就等于14。
方法二：规划求解可以用规划求解，以下图中的A1:A20数据为例。
假设要在A1:A20中找出某些数的和等于200，操作步骤如下：步骤1：在C1单元格输入公式=SUMPRODUCT(A1:A20,B1:B20)如下图步骤2：选定C1单元格，数据>>>规划求解，“设置目标”会自动设置为C1单元格，到：选择“目标值”，并在右侧文本框中输入固定的数字200，鼠标放在”通过更改可变单元格“框中，并选择B1:B20，Excel将自动输入单元格地址，再单击“遵守约束”右侧的“添加”按钮，如下图：步骤3：在”单元格引用“用鼠标选择B1:B20单元格，中间的下拉框中选择”bin“，右侧框中将自动显示”十进制“，再单击”确定“按钮，如下图步骤4：通过上步操作后，”遵守约束“列表框中就增加了一个约束”$B$1:$B$20 = 二进制“，单击”求解“按钮，如下图步骤5：单击”确定“按钮，结果如下图所示，B列结果为1的表示对应A列的数字相加的和为C1的值200。
知识扩展：1、如果数据菜单没有”规划求解“命令，开发工具>>>加载项，勾选”规划求解加载项“，再单击”确定“按钮，如下图：2、如果连”开发工具“菜单都没有，操作如下：文件>>>Excel选项>>>自定义功能区>>>勾选”开发工具“，再单击”确定“按钮，如下图。