Excel规划求解工具在多目标规划中的应用
EXCEL规划求解在多种产品本量利分析中的应用
EXCEL 规划求解在多种产品本量利分析中的应用张雅晖摘要：本量利分析是管理会计的基础内容，本文利用大家熟悉的EXCEL 电子表格软件，设计了多种产品条件下的本量利分析模板，并巧妙地借助于EXCEL 的规划求解功能，解决了多产品下本量利分析的复杂计算问题，提高了多产品下本量利分析决策的效率。
关键词：规划求解；多种产品；本量利分析；运用在管理会计应用中，多产品下的本量利分析涉及公式较多，计算较为麻烦，发生差错的可能性较大；虽然也有学者或实际工作者利用EXCEL 来进行本量利分析，但内容大多局限于单一产品的本量利分析，即使极个别人利用EXCEL 进行了多产品下的本量利分析，但也是把手工计算过程搬到了电子表格里。
本文巧妙地运用EXCEL 的规划求解功能，一次同时求出综合及分产品的保本、保利销售收入及销售量指标，较好地解决了多产品下本量利分析的复杂问题。
一、多产品下传统本量利分析的基本方法在实际经济生活中，大多数企业不止生产销售一种产品。
在企业经营多种产品的情况下，主要用销售收入来表示企业的保本点（盈亏平衡点）或保利点（实现目标利润的销售额），多产品条件下确定保本点、保利点的常用方法有综合边际贡献率法及联合单位法。
综合边际贡献率法是假设产品品种结构保持不变的情况下，通过计算多品种下的加权平均边际贡献率，来确定综合及每种产品的保本、保利销售额。
联合单位法是指企业各种产品之间存在相对稳定的产销量比例关系，这一比例关系的组合可以看做一个联合单位，通过确定每一联合单位的单价及单位变动成本，从而求出联合及每种产品的保本、保利点。
二、多产品下本量利分析的模型设计利用EXCEL 解决多产品下本量利分析决策问题，重点是如何把销售量、单价、单位变动、产品销售条件、有关决策变量的约束等在EXCEL 工作表中细化，下面以“联合单位法”为基础，通过例子给予说明例：翔宇公司计划期销售甲、乙、丙三种产品，计划期固定成本总额为21600元，目标利润为5400元；甲、乙、丙三种产品：预计销售量分别为1000件、2000件、2500件，预计销售单价分别为50元、15元、8元，预计单位变动成本分别为40元、9元、6元。
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应用
培训与发展Training & Development2005年专刊2（总第34期）国家发展和改革委员会培训中心2005年6月30日规划问题求解与EXCEL应用目录第一节EXCEL中的规划求解工具………………………………(2)第二节线性规划求解方法 (7)第三节对偶问题与影子价格 (23)第四节线性规划的敏感度分析 (28)第五节整数规划求解 (32)第六节非线性规划求解 (33)第七节目标规划问题求解 (37)规划问题求解与EXCEL应用国家发展改革委培训中心委机关培训部编写1939年，前苏联科学家康托洛维奇总结了他对生产组织的研究，写出了《生产组织与计划中的数学方法》一书，是线性规划应用于工业生产问题的经典著作。
1947年，丹齐格提出了单纯形方法后，线性规划便迅速形成了一个独立的理论分支。
此后，整数规划、目标规划、非线性规划理论逐渐形成并成熟。
微软在EXCEL中开发的“规划求解”工具是以单纯形方法为基础的，使用起来比较方便。
另外，芝加哥LINDO公司研制的Lindo软件在解决线性规划模型、整数规划模型、二次规划模型等方面功能比较强大。
但目前尚无汉化版，需要学习者，可从LINDO公司的网址免费下载教学演示软件，如果要得到功能全面的软件，必须购买正版软件。
我们组织编写的《经济计量分析与EXCEL应用》一书，对“规划求解”略有介绍。
由于规划理论是经济学中的一种重要方法，规划求解在实际经济管理和管理决策中应用广泛，我们特编一个参阅材料，仅供参考。
第一节EXCEL中的规划求解工具EXCEL中的规划求解工具设置了4个对话框。
有的选项已有默认值，只是需要改变才需要选择。
为便于大家选择，我们特将选项作些说明。
一、关于“规划求解参数”对话框【设置目标单元格】在此指定要设置为特定数值或者最大值或最小值的目标单元格。
该单元格必须包含公式。
【等于】在此指定是否希望目标单元格为最大值、最小值或某一特定数值。
如果需要指定数值，请在右侧编辑框中键入该值。
Excel规划求解的两类应用
元格分别输入 : 初始值 C0 (10123) ,老化速度常数 b 的初始值
012 ,引文数据的平均值 mean of c ,相关系数的平方 R2 。并用
Excel 中的“插入”|“名称”给它们分别命名为 : C0 , b ,mean of
c , RSQ 。
2) 在 B2 : B29 单元格中输入负幂指数公式 (3) ,分别为 : =
这里拟合的负指数函数相对简单 。可以对复杂的函数进行
拟合 ,如[4] :
以及[5 ]
y
=
[1
+
exp
1 ( V - E)
© 1994-2006 China Academic Journal Electronic Publishing House. All rights reserved.
Байду номын сангаас
第 1 期
顾运筠 : Excel 规划求解的两类应用
1 39
图 2 - 2 求非线性回归的规划求解的参数设置 如果对函数取对数 ,进行线性回归所得的结果是 : b = 011233 , R2 = 019968 。这两个结果相当接近 。
C0 3 EXP( - b 3 0) 、= C0 3 EXP ( - b 3 1) 、…、= C0 3 EXP ( - b
3 27) 。其中 : C0 、b 分别为 H1 、H2 单元格中的值 。
3) 在 H3 中输入引文数据的平均值 mean of C : = AV ER2
AGE( A2 : A29) 。
TWO SORTS OF APPL ICATIONS USING EXCEL SOL VER
Gu Yunyun
( Shanghai Sports Technical College , Shanghai 200030)
excel 规划求解
excel 规划求解Excel是一款功能强大的电子表格软件，可以用于数据分析、数据处理、数据可视化以及进行规划求解等多种任务。
在Excel中进行规划求解可以帮助我们优化问题的解决方案，提高效率和准确性。
下面我将介绍一下在Excel中进行规划求解的基本步骤和方法。
首先，我们需要确定需求或者问题，明确目标。
比如我们要求解一个优化问题，如如何在预算有限的情况下，获得最大的利润。
接下来，我们需要在Excel中建立一个模型，将问题转化为一个数学模型。
对于优化问题，我们需要确定决策变量、约束条件和目标函数。
决策变量是我们要优化的变量，是我们需要调整的参数。
在上述问题中，我们可以考虑不同的投资方案和预算分配方式作为决策变量。
约束条件是我们需要满足的条件，它们限制了决策变量的取值范围。
在上述问题中，预算是一个约束条件，我们不能超出预算。
目标函数是我们要最大化或最小化的函数。
在上述问题中，我们的目标是最大化利润，所以利润就是我们的目标函数。
然后，我们可以使用Excel中的规划求解工具来求解问题。
在Excel中，我们可以使用“规划求解”工具来实现。
首先，我们需要将问题转化为Excel能够理解的形式，比如将决策变量和目标函数写入Excel表格的某一列或一行，将约束条件写入Excel表格的某一区域。
然后，我们可以打开Excel中的“数据”选项卡，点击“规划求解”按钮，选择求解目标和约束条件，然后点击“求解”按钮。
Excel会自动寻找最优解，并将结果显示在对应的单元格中。
最后，我们需要分析求解结果，并根据需要进行调整和优化。
如果求解结果不符合需求，我们可以根据结果进行适当的调整，重新运行规划求解工具，直到达到满意的结果为止。
总而言之，Excel提供了方便实用的规划求解工具，可以帮助我们解决各种优化问题。
通过正确使用Excel中的规划求解功能，我们可以提高问题求解的效率和准确性，实现更好的决策和结果。
希望本文能对大家在Excel中进行规划求解提供一些帮助。
利用Excel中的加载宏新加入的规划求解功能解决线性规划问题
利用Excel中的加载宏新加入的规划求解功能解决线性规划问题（郑来运PPT例1）
具体步骤如下：
1.打开Excel，单击“工具”弹出菜单，然后单击“加载宏”会出现如下画面：
选择“规划求解”点击确定，这样你的Excel就有了能解决线性规划问题的功能。
2.依次输入以下数据作为准备工作，如下图：图中用不同的色块表示约束条件和可变部分
3.在表中选中D2的位置然后点击函数，出现“插入函数”的弹出框后，选择”常用函数”中的”SUMPRODUCT”,
如下图所示。
点击确定后在弹出的对话框中array1选择B2：C2，在Array2中选择B6：C6，同时可以看到公式的生成。
用相同的方法让D3,D4,都相应填上公式
选中E6输入公式SUMPRODUCT(B5:C5，B6:C6)
4.单击“工具”选择“规划求解”设置目标单元格为E6，可变单元格为B6，C6，并添加约束条件，如下图
单击“求解”
选择保存规划求解结果，点击“确定”得到求解结果。
第3章Excel在数学规划和统计中的应用
一、线性规划模型
其中：
S为目标函数； Xj为决策变量； aij为技术系数； bi为约束值； Cj为费用系数； m为约束条件的个数; n为变量个数。
一、线性规划模型
单纯型法原理：在找出一个基可行解后， 判断它是否为最优解，如果不是，则另外 换一个基可行解，直到得到问题的解答。 整个计算过程实际上一个在基可行解上的 迭代过程。 由于基可行解是有限的，如果有最优解， 则经过有限步迭代可以达到。
^ 1 2
0
^
y
^
1
x
例题1数据分析中的回归分析
年份 粮食产量(万吨) 化肥(万吨) 播种面积(公顷)
1988
1989 1990 1991 1992 1993 1994 1995 1996 1997 1998
2097.5
2134.5 2148.7 2314.5 2217.1 2904.0 2893.5 2710.5 2545.7 2948.4 2663.0
在Excel中输入数据
其中：
B3:G6为原始数据区； B12:F12为方案1～5的决策变量 （x1,x2,x3,x4,x5），即可变单元格； C8为目标单元格，输入目标函数； C14、C15、C16输入约束条件，等于约 束条件的左式减去右式。
合理利用线材问题的计算公式
单 元
C8
公式
下料 2.9m
根数 2.1m 1.5m 合 料 计 头(m)
1
0 3 7.4 0
2
0 1 7.3 0.1
0
2 2 7.2 0.3
1
2 0 7.1 0.3
0
1 3 6.6 0.8
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel规划求解工具在多目标规划中的应用
摘要：多目标决策方法是从20世纪70年代中期发展起来的一种决策分析方法。
该方法已广泛应用于人口、环境、教育、能源、交通、经济管理等多个领域。
文章采用多目标决策方法中分层序列法的思想，应用excel的规划求解工具，对多目标规划问题进行应用研究，并以实例加以说明。
abstract： multi-objective decision method is a kind of decision analysis method from the mid 1970s. the method has been widely used in population， environment， education，energy， traffic， economic management， and other fields. this paper uses the lexicographic method of multi-objective decision method and makes some researches on the
multi-objective problem using the excel solver tool and an example to illustrate.
关键词： excel规划求解；多目标规划；分层序列法
key words： excel solver；multi-objective programming；the lexicographic method
中图分类号：tp31 文献标识码：a 文章编号：1006-4311（2013）21-0204-02
0 引言
excel中的规划求解工具只能对单目标的问题进行求解。
当遇到多目标问题时，可以把多目标问题先转化为单目标问题，然后求解。
常用的方法是线性加权和分层序列法。
文章主要以分层序列法为例。
1 多目标决策的分层序列法
分层序列法就是将所有目标按其重要性程度依次排序，先求出第一个重要目标的最优解，然后在保证前一个目标最优解的前提下依次求下一个目标的最优解，一直求到最后一个为止。
设有m个目标，其重要性序列为f1（x），f2（x），f1（x）…，fm（x）。
首先对第一个目标求最优，并找出所有最优解的集合记为r0，然后在r0内求第二个目标最优解，记这时的最优解集合为r1，如此等等一直到求出第m个目标的最优解x0，其模型如下：
f1（x）0=■f1（x） f2（x）0=■f2（x）
fm（x）0=■fm（x）
该解法的前提是r0，r1，r2，…，rm-1非空，同时r0，r1，r2，…，rm-2都不能只有一个元素，否则很难进行下去。
当r为紧致集，函数f1（x），f2（x），f1（x）…，fm（x）都是上半连续，则按下式定义的集求解。
r■■={x|fk（x）=■fk（u）；x∈r■■}
k=1，2，3，…，m，其中r■■=r都非空，r■■是非空。
故有最优解，而且是共同的最优解。
2 应用excel规划求解工作进行多目标规划问题求解
例题1：某生产制造企业生产a、b两种产品，两种产品各生产一个单位需要3个工时和7个工时，用电量为4千瓦和5千瓦，原材
料9吨和4吨。
公司可供应的工时为300个，可供的用电量分别为250千瓦，可提供的原材料也为420吨。
两种产品的单位利润分别为20元和25元。
试求在优先考虑总利润最大，其次考虑总工时最小的情况下，最优的生产量。
解：该问题的求解目标有两个：总利润最大，总工时最小。
第一步：根据题意建立数学模型。
设a、b产品的生产量分别为x1、x2，其数学模型如下：
max z1=20x1+25x2 min z2=3x1+7x2
约束条件3x1+7x2？燮3004x1+5x2？燮2509x1+4x2？燮420x1，x2？叟0
第二步：建立excel计算模型。
假设a、b两种产品的初始产量为1，单元格数据计算结果都保留整数。
在运用sum函数的数组运算公式时，公式输完后不能直接按enter键，否则出现“#value”，需要同时按ctrl+shift+enter，才能显示出计算结果。
多目标规划单元格公式和多目标规划模型分别如图1和图2。
第三步：启动规划求解工具求解利润最大。
首先点“工具”——“规划求解”，弹出“规划求解参数”窗口，按图3进行设置。
然后点“求解”按钮，在弹出窗口中选择“保存规划求解结果”，可得总利润最大时的结果如图4。
第四步：在保持利润最大的条件下，求解总工时最小。
此时利润最大值等于1250元，可以作为求解总工时最小的约束条件。
求解总工时最小的“规划求解参数“设置如图5。
同理可得最终结果如图6。
通过以上计算可以看出，在满足约束条件下，最大利润为1250元，最小工时为251个，此时a产品产量为38，b产品产量为20。
3 结论
excel规划求解工具不仅可以处理线性规划问题，而且也可以处理非线性规划问题。
其作为常用的数据处理软件，应用于手工计算比较复杂的多目标规划问题中具有简单、方便、实用的特点。
参考文献：
[1]胡运权主编，郭耀煌副主编.运筹学教程（第3版）[m].清华大学出版社，2008.10.
[2]刘兰娟等编著.经济管理中的计算机应用-excel数据分析、统计预测和决策模拟[m].北京：清华大学出版社，2009.3.
[3]运筹学教材编写组编.运筹学（第三版）[m].北京：清华大学出版社，2007.11.。