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
Excel规划求解工具在商场经营面布局规划中的应用
2012.No19在竞争日益剧烈的今天，如何能科学地对商场经营布局作出有效地规划是现代商场经营者面临的重要研究课题？笔者通过多年的工作经验和现代技术较好地解决了这一靠手工或经验判断无法解决难题。
我的解决办法就是利用EXCEL2003办公软件的中的规划解决工具科学计算出商场受销售额和毛利额双指标约束条件下的最佳商场经营布局规划方案的相关数据，以此数据实现最佳最科学布局方案的制定。
通过该方案的制定实施提升企业竞争力和管理决策的能力。
商场经营布局规划主要是解决商场在一定的经营面积内，受各种不同品类商品的单位面积产生的销售额和毛利额的贡献值不同的影响，如何科学规划各种不同经营品类的商品的经营面积以实现商场经营的销售额和毛利额的综合业绩效最大化的决策过程。
在理论上讲，一个商场为保证销售额和毛利额最大化，那就经营单位经营面积产生的销售额和单位经营面积产生的毛利额高的商品最好。
但在实现经营过程中，一个商场为保证消费者不同消费需求和经营商品的品类商品的丰富度，必须和必要的经营品类是必须要求经营，因此就存在商场经营品类的丰富主要求和商品综合绩效要求的矛盾。
这解决这一矛盾，经营者就必须考虑如何在这两者要求之间找到平衡点，这一平衡点如果通过手工计算或经验判断基本上是无法解决的，只能通过科学的方法和现代计算技术加以解决，即本文提出的“应用EXCEL规划求解工具解决商场经营布局规划”。
以下以某商场为例，谈如何实现经营布局规划的解决过程。
第一、由于商场经营调整规划可能有很多种方案，可能存在经营者受竞争对手的压力和商场本身经营绩效的压力的双重作用下，商场经营布局规划方案可能就有很多种，因此解决多方案规划决策问题的步骤是首先建立多方案规划布局决策问题的数字模型，即以多个方案构成的商场综合绩效值最大为目标函数，以一定经营面积为限额等限制条件为约束条件，建立线形规划模型，然后再利用Excel规划求解工具求出最优解。
第二、基本数学模型构建。
Excel规划求解在多方案组合排队投资决策中的应用
万方数据
Ｅ郁ｘ玉ｃ环ｅｌ“规划求解”在多方案组合排队投资决策中的应用 ２第中ＣＭＩＦＶ（［过【１多在可方合利量（授Ｅ的ｌＡ资求方按序３以完８巨逐内素任上的披人（目责认单露一露ｆｃ不比任制大始企会熟负独信主究ｅ０２Ｈ０ｈａｎ收基作８ＡＮ１２３摘文案０３４５关中、０１２湖ｘｂ１０Ｉ０１ｉｎｆ。ＮＦ要］，程文行以案法指照下善０大步进质学也露）。任为，内侧同较报责的债．０Ｎ７国方企和应额作案，０）信员独的多业后立息９２ｎａｏ稿金者ｃＡＯ山刘季７Ａ（要章例０在统虽年卷ａｇｒ键图硕Ｂ万当对ＧＲ参２，１Ｙ７北ｅ较献的实“”数最自结建行历仅工信。如部也重告逐任披表ｅｍ２日项０简ＥＭ哑）长０晓管案业比用、出组计息实列信立企社，的。２。】编８【原一然．２第ｍａ士ｌＭＡ考００词分９元限会月ｅｔ为标，现］的期目介项优制产影合立逐会占作据息可环有不ＥＴ侧是步披从露、男翠６东０Ｎ汽）“４理ｎｉ组投较。Ｃ净合算。施示息ＮＩ的业会可社文（多号１有行随９ｏ资期ｔｏ］】ＴＯ．１复】类识它任即大’、目投定度生响我社步计计仅１的披过以保准利同重最推露应ｎ２湖助Ｎ献０车方】对规５会业着信合资。现决累经考来社责要会４ｉ０郁Ｉ略孔）企杂码们意在小。组资对来，国会推人占％要露于投十用终行的由ｚＥ号０北Ｄ４投人Ｚ．案１这计内社ａ８玉Ａ论晓业息ｘ排决从、值总策计５调核披可会说任求责工划ｔ。】６既组投．－省合其我实责广员被求和分现资限进Ｔ一趋编内企简ｃ］０资员ｉ０环Ｉ企婷社组７种报部会化ｏｅＦ队策中７教Ｅ、额。投万查露以Ｏ责改信企任本Ａ３不合资结法进对国际任。能涮这文散有费制行正个势容业单业求ｎ—（Ｎ业．会ｌ２五总进合—投表标发育１文；７投中属或限合选净分”资元：行企可国信够发查种需以字、报有在常统。任革独息和社模业利报３９社责学解排０资的准展５１厅６个额行利１于排额资３首规业以情息用总素要表不污并一但立披会式润会任规；资，出现别额、提关会报的披在告社一队９方基。，院”投为社责信用４相队允本先范产参，披更人质定述易基染有内很的就社露责逐表划Ｆ决如能会）值为与６，高的计告成露现形投（案础对我，任息２资０３会管在Ｅ２互，许限按。生考露恰数结期相于础治选部有口目会模任渐及科求３策果使率累０我会社报。本在有式资ｘ进上国女会披ｃ２０独则的额学项各万Ｓ制责当构进结查上理择必径前责式披向社理多，计露决ｅ９行Ａ增会企解程一净和０计万国会表因太我财全研ｌ）立这范进方度的１，行合找．费性信要来任等露高会目万元任湖信初工策“０决方６加责业；序组现究内净元企人责附为高国务面４，些围行案方％还专。及在地息．看报的级责北息探为元进会在规－策社任编“程案投方值部现时业员任注此。还报系－又方内各汽净由式，难业为缺某售进对设衡。告有实模任披【十非、行计实划０所会项制车露Ｊ资０案不案则种之收现值。点披目而以培的解少些后信中行比置量只方应。处待际式告统系组一］务求２采责目独互４投专Ｔ［的．８属被需组值向露具满训决数科服社。权有于来推金决中和五业益５，最前素息进法先于基地Ｊ，合中解－用任设立］实江斥０资业０于称按合”学率面企有足，据目务会不重极进看流．策各最率万然优会质。行不从探础披证苏采”３的信置湖排规院方组知黑相作净排的，业中社并等下费责同可助少一。．量研商；投大数元后计。对文是一索上露用工方息权社划管龙北队互组现队案大合在识的级会逐披问添等任行以于数步企待表究论的具法重会组资的项理据、分人于字很部阶编企江排合值，小全社以责年露题加信业将行的探条。【，．。培十投目工对方，是科。责合项最如６别组员不说切分段报业Ｊ２斥或率反排国会上任对的，明在息信各业企索社件最程有０反训外］０堰法方“资统．０或目优表０按合具便明大．社，排或复范责职信会社笔细报披息行问业与成终系经５关万复。是便项会（４决排都而队获衡组教１投为围有称息计会者科表用。披业的编实企社责会以贸１４计）“快目原所元比高。．是中２策队是合资大数际业责研项捷组０始示时较方。限专字开产任０中目准投．多得２案额以披对组确）要到合地法得”到，不需同列投出资各限种额组的合最，优反投复资衡组量合和结比果较。。决策
Excel高级技巧使用目标求解和数据表实现业务优化与决策支持的精确性与可靠性
Excel高级技巧使用目标求解和数据表实现业务优化与决策支持的精确性与可靠性Excel是一款功能强大的电子表格软件，为用户提供了丰富的数据处理和分析工具。
在日常办公和商业决策中，Excel的高级技巧可以帮助我们实现业务的优化和决策的支持。
本文将介绍如何使用Excel的目标求解和数据表功能，来提高业务的精确性和可靠性。
一、目标求解目标求解是一种通过调整输入值，使达到指定的输出值或达到最优解的方法。
在Excel中，我们可以使用“数据”选项卡中的“什么如果分析”工具来进行目标求解。
1. 准备数据在使用目标求解前，首先需要准备好相应的数据。
以一个销售业绩目标设定为例，我们可以在Excel中创建一个表格，包括销售额、成本、利润等相关数据。
2. 设定目标接下来，我们需要设定目标，即希望达到的销售额或利润。
在Excel中选择“数据”选项卡中的“什么如果分析”，选择“目标值”，然后输入目标值。
3. 设置约束条件除了目标值，有时候我们还需要设置一些约束条件，以符合实际情况。
例如，销售额不能小于某个数值，或者成本不能超过某个百分比。
在“什么如果分析”中选择“约束条件”，然后设置相应的条件。
4. 进行目标求解设置好目标和约束条件后，点击“确定”开始进行目标求解。
Excel会自动通过调整输入值，计算出满足目标和约束条件的最优解。
通过目标求解，我们可以快速找到最优的业务方案。
无论是优化销售业绩，还是最大化利润，都可以借助Excel的目标求解功能，提高业务的精确性和可靠性。
二、数据表数据表是Excel中用于管理和分析大量数据的强大工具。
通过使用数据表，我们可以轻松地对数据进行排序、筛选、汇总和分析。
1. 创建数据表在Excel中，选中需要创建数据表的范围，然后在“插入”选项卡中选择“表格”。
Excel会自动将选中的范围转换为一个数据表，同时添加了自动筛选和排序的功能。
2. 筛选数据数据表中最常用的功能之一就是筛选数据。
我们可以根据某一列或多列的条件，筛选出符合要求的数据。
应用excel规划求解实例
应用EXCEL规划求解工具进行优化1.线性规划—生产规划：步骤一：建立模型：每天生产甲乙两种产品分别为X1和X2，数学模型为：目标函数：minf（X1，X2）=60*X1+120*X2约束条件：9*X1+4*X2<=3603*X1+4*X2<=3004*X1+5*X2<=200-X1<=0-X2<=0用EXCEL建立模型如下：步骤二：规划求解参数确定：步骤三：选项参数确定：步骤四：求解：由上面求解过程可知：X1=20，X2=24时，可使目标函数值最小，即f（X1，X2）=4080. 2.工程下料问题规划求解：由题意可列出下列方案：步骤一：设使用8种方案的次数分别为X1，X2，X3，X4，X5，X6，X7和X8，且均为正整数，建立数学模型如下：目标函数：f（X）=（5*X1+10*X2+25*X3+5*X4+30*X5+10*X6+25*X7+5*X8）/（（X1+X2+X3+X4+X5+X6+X7+X8）*180）约束条件：gX1=2*X1+X2+X3+X4=100gX1=2*X2+X3 +3*X5+2*X6+X7gX1=X1+X3+33*X4 +2*X6+3*X7+5*X8用EXCEL建立模型如下：步骤二：规划求解参数确定：步骤三：选项参数确定：步骤四：求解：由上面求解过程可知：X1=23，X2=50，X3=0，X4=4，X5=0，X6=0，X7=0和X8=3时，可使目标函数值最小，即f（X）=0.045139.3．规划求解—工时安排：某厂生产A B C三种产品，净利润分别为90元，75元，50元；使用的机时数分别为3h，手工时数分别为4h，3h，2h，由于数量和品种受到制约，机工最多为400h，手工为280h，数量最多不能超过50件，C至少要生产32件。
求：如何安排A B C的数量以获得最大利润？解：建立数学模型：A、B、C三种产品的数量分别为X1，X2和X3，其利润为f（X）：目标函数：maxf（X）=90*X1+75*X2+50*X3约束条件：3*X1+4*X2+5*X3<=4004*X1+3*X2+2*X3<=280X1<=50X2>=32用EXCEL建立模型如下：步骤一：建立模型：步骤二：规划求解参数确定：步骤三：选项参数确定：步骤四：求解：由上面求解过程可知：X1=0，X2=93，X3=0时，可使目标函数值最大，即f（X）=11160.4.FORTRAN语言解读：C ======================SUBROUTINE FFX(N,X,FX) ；（目标函数定义）C ======================DIMENSION X(N)COMMON /ONE/ I1,I2,I3,I4,NFX,I6NFX=NFX+1P0=ACOS(((1.0+X(1))**2-X(2)**2+25.0)/(10.0*(1.0+X(1))))；（输入角初始值）Q0=ACOS(((1.0+X(1))**2-X(2)**2-25.0)/(10.0*X(2)))；（输出角初始值）T=90.0*3.1415926/(180.0*30.0) ；（将输入角30等分后每一份值）FX=0.0 ；（目标函数初始值）DO 10 K=0,30 ；（循环程序入口，循环次数30次）PI=P0+K*T ；（计算每一次循环后的输入角）QE=Q0+2.0*(PI-P0)**2/(3.0*3.1415926)；（计算每一次循环后的理想输出角）D=SQRT(26.0-10.0*COS(PI)) ；（与L1和L4相邻的连杆四边形对角线长度r）AL=ACOS((D*D+X(2)*X(2)-X(1)*X(1))/(2.0*D*X(2)))；（L3和r的夹角）BT=ACOS((D*D+24.0)/(10.0*D)) ；（L4和r的夹角）IF (PI.GE.0.0 .AND. PI.LT.3.1415926) THEN；（判断输入角是否在0到pi之间，计算实际输出角）QI=3.1415926-AL-BTELSEQI=3.1415926-AL+BTENDIFIF(K.NE.0 .OR. k.NE.30) THEN ；（判断循环次数是否在30次内,计算目标函数）FX=FX+(QI-QE)**2*T；ELSEFX=FX+(QI-QE)**2*T/2.0ENDIF10 CONTINUE ；（继续循环）END ；（程序段结束）C =========================SUBROUTINE GGX(N,KG,X,GX) ；（约束条件函数子程序）C =========================DIMENSION X(N),GX(KG) ；（定义GX<=0的约束条件函数）GX(1)=-X(1) ；（杆长L2>=0）GX(2)=-X(2) ；（杆长L1>=0）GX(3)=-(X(1)+X(2))+6.0 ；（最短杆L1和杆L4之和小于另两杆之和）GX(4)=-(X(2)+4.0)+X(1) ；（最短L1和杆L2之和小于另两杆之和条件）GX(5)=-(4.0+X(1))+X(2) ；（最短L1和杆L3之和小于另两杆之和条件）GX(6)=-(1.4142*X(1)*X(2)-X(1)**2-X(2)**2)-16.0 ；（传动角大于45度）GX(7)=-(X(1)**2+X(2)**2+1.4142*X(1)*X(2))+36.0；（传动角小于135度）ENDC =========================SUBROUTINE HHX(N,KH,X,HX) ；（约束条件函数子程序）C =========================DIMENSION X(N),HX(KH) ；（定义HX=0的约束条件函数）X(1)=X(1)END5.学习心得：这次作业让我收获了很多，通过课堂上的学习，让我对优化设计有了一个充分的认识，老师的讲解细致入微，也让我对这门课充满了兴趣。
EXCEL规划求解工具在OM中的应用
EXCEL规划求解工具在OM中的应用EXCEL是一种广泛使用的电子表格软件，它可以帮助用户进行数据分析、计算和规划。
在运营管理中，EXCEL的规划求解工具可以被应用于各种决策问题的求解和优化。
首先，EXCEL的规划求解工具可以用于生产计划的制定。
通过输入相关的生产数据和约束条件，利用规划求解工具，可以快速求解出最优的生产计划，使得生产效率最大化，同时满足生产资源的约束条件。
其次，EXCEL的规划求解工具还可以应用于物流配送的优化。
用户可以通过输入各个仓库的存货量、客户的需求量以及运输成本等信息，利用规划求解工具来寻找最佳的配送方案，从而减少运输成本，提高物流效率。
另外，EXCEL的规划求解工具还可以用于人力资源的调度和排班。
用户可以通过输入员工的工作时间、班次需求以及员工的技能等信息，利用规划求解工具来生成最佳的排班方案，从而达到最佳的人力资源利用效率。
除此之外，EXCEL的规划求解工具还可以应用于库存管理、项目管理、投资决策等各个方面。
通过利用规划求解工具，可以帮助管理者迅速找到最优解决方案，从而加快决策的过程，提高决策的准确性和效率。
总之，EXCEL的规划求解工具在运营管理中有着广泛的应用前景，它可以帮助管理者快速解决各种决策问题，提高运营效率和管理水平。
因此，掌握和应用这一工具对于提升运营管理的水平和竞争力具有非常重要的意义。
很难想象现代的运营管理工作没有EXCEL的存在。
EXCEL作为一种功能强大、易于使用的电子表格软件，为运营管理者提供了重要的工具，帮助他们在复杂的决策过程中做出明智的选择。
作为EXCEL的一个重要功能，规划求解工具更是为运营管理者提供了一种简单而有效的方法来解决各种优化问题。
在工业制造领域中，生产计划的制定对于企业的运营效率至关重要。
利用EXCEL的规划求解工具，生产管理者可以输入生产线的产能、原材料需求、订单量等信息，然后简单地执行规划求解功能，系统将自动寻找最佳的生产计划，以最大程度地提高生产效率并减少浪费。
Excel规划求解使用说明
规划模型的线性规划标准型式为：
2－21
规划求解使用说明
5.规划求解小结..............................................................................................................................19 6.Excel补注 ....................................................................................................................................19
Max z = C T X
⎧ AX = bΒιβλιοθήκη s .t .⎨ ⎩X
≥0
X 为决策变量向量 其中： C 为价值向量
b 为资源向量 A 为技术矩阵
这些都是经济生产中所具有的现实意义。
1.3.利用规划求解解决现实问题的步骤
z 建立对应的规划模型 z 在 Excel 表中输入已知数据（如线性规划模型的 A、b、C），以及指定规划模型三
4.人员安排模型..............................................................................................................................16 4.1.Excel关于规划求解自带的系统示例文件......................................................................16 4.2.问题描述...........................................................................................................................17 4.3.模型分析、建立...............................................................................................................17 4.4.规划求解...........................................................................................................................18
EXCEL规划求解案例分析
下面介绍用Excel中的“规划求解”功能求此题。 第一步 在Excel中描述问题、建立模型，如下图所示。
=SUMPRODUCT(B6:E6,$B$15:$E$15)
第二步 在“工具”菜单中选择“规划求解”。
第三步 在“规划求解参数”对话框进行选择如下图。
第四步 点击“选项”按钮，弹出“规划求解选项”对话框
筹办航空事宜
处
三、从驿传到邮政 1．邮政 (1)初办邮政： 1896年成立“大清邮政局”，此后又设 ， 邮传邮正传式部脱离海关。 (2)进一步发展：1913年，北洋政府宣布裁撤全部驿站； 1920年，中国首次参加 万国。邮联大会
2．电讯 (1)开端：1877年，福建巡抚在 架台设湾第一条电报线，成为中国自 办电报的开端。
[合作探究·提认知] 电视剧《闯关东》讲述了济南章丘朱家峪人朱开山一家， 从清末到九一八事变爆发闯关东的前尘往事。下图是朱开山 一家从山东辗转逃亡到东北途中可能用到的四种交通工具。
依据材料概括晚清中国交通方式的特点，并分析其成因。 提示：特点：新旧交通工具并存(或：传统的帆船、独轮车， 近代的小火轮、火车同时使用)。 原因：近代西方列强的侵略加剧了中国的贫困，阻碍社会发 展；西方工业文明的冲击与示范；中国民族工业的兴起与发展； 政府及各阶层人士的提倡与推动。
规划问题的特点（共性）
一般来讲，规划问题都具有如下特点：
1. 所求问题都有单一的目标（如求生产的最低 成本，求运输的最佳路线，求产品的最大盈 利，求产品周期的最短时间），要求求目标 函数的最优解。
2. 对于问题涉及的对象（如路程、原材料等） 存在有明确的可以用不等式表达约束条件。
3. 问题的表达可以描述为：一组约束条件（不 等式），和一个目标方程。
Excel规划求解工具进行经济规划决策_电子表格应用系列_七_
信用标准定得过高，将使客户因信用达不到信用标准而被拒之门 外， 其结果虽然减少了坏账损失和应收账款的机会成本， 但不利于 企业扩大销售， 甚至会减少销售。 反之， 尽管会增加销售量， 但也会 增加坏账损失和应收账款的机会成本。 （ ３ ）现金折扣政策。 现金 折扣是客户在折扣期限内付款时企业提供的优惠。其主要目的在 于吸引顾客为享受优惠而提前付款，缩短企业的平均收款期。另 外， 现金折扣也能招揽一些视折扣为减价出售的顾客购货， 借以扩 大销售量。 （ ４ ）收账政策。收账政策是指信用条件被违反时， 企 业采取的收账策略。 企业如果采取积极的收账政策， 虽然会减少应 收账款投资的收益和坏账损失， 但要增加收账成本。 如果采用消极 的收账政策， 虽可减少收账费用， 则会增加应收账款投资的机会成 本和坏账损失 二、 影响应收账款信用政策制定的主要因素 应收账款信用政策的制定具体可从以下几个因素考虑：
１ ２ ３ ４ ５ 项 销售单价 （ 元） 单位变动成本 （ 元） 单位边际贡献 （ 元） 分摊固定成本总额 （ 元） 每件产品的Ａ 机器工时定额 （ 小时） 每件产品的Ｂ 机器工时定额 （ 小时） 每件产品的Ｃ 机器工时定额 （ 小时） Ａ 机器生产能力 （ 小时） Ｂ 机器生产能力 （ 小时） Ｃ 机器生产能力 （ 小时） 最大销售量 （ 件） 可实现的最大经济效益 （ 元） 产品决策结果 （ 产品数量） 产品组合决策约束条件 Ａ 机器生产能力约束 Ｂ 机器生产能力约束 Ｃ 机器生产能力约束 甲产品数量约束条件 乙产品数量约束条件 丙产品数量约束条件 ＝Ｂ７"Ｂ１５＋Ｃ ７"Ｃ １５＋Ｄ ７"Ｄ １５ ＝Ｂ８"Ｂ１５＋Ｃ ８"Ｃ １５＋Ｄ ８"Ｄ １５ ＝Ｂ９"Ｂ１５＋Ｃ ９"Ｃ １５＋Ｄ ９"Ｄ １５ ＝Ｂ１５ ＝Ｃ１５ ＝Ｄ１５ １０００ ２ ３ ２
EXCEL中的规划求解
目录1.关于“规划求解”2.如何加载“规划求解”3.“规划求解”各参数解释和设置4.“规划求解”的步骤5.“规划求解”疑难解答6.利用“规划求解”解线性规划问题7.利用“规划求解”解整数规划问题8.利用“规划求解”解目标规划问题9.利用“规划求解”解运输问题10.利用“规划求解”解最短路径问题11.利用“规划求解”解最大流问题12.利用“规划求解”解数据包络分析（DEA）问题13.利用“规划求解”解其他运筹学问题1、关于“规划求解”“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。
公式总是以等号 (=) 开始。
）的最优值。
“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。
“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。
在创建模型过程中，可以对“规划求解”模型中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。
可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。
而且约束条件可以引用其他影响目标单元格公式的单元格。
使用“规划求解”可通过更改其他单元格来确定某个单元格的最大值或最小值。
Microsoft Excel 的“规划求解”工具取自德克萨斯大学奥斯汀分校的 Leon Lasdon 和克里夫兰州立大学的 Allan Waren 共同开发的Generalized Reduced Gradient (GRG2) 非线性最优化代码。
线性和整数规划问题取自 Frontline Systems 公司的 John Watson 和 Dan Fylstra 提供的有界变量单纯形法和分支边界法。
2、如何加载“规划求解”安装office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。
Excel规划求解的使用
§9.6 Excel软件“规划求解”的使用用Excel软件的“规划求解”功能可以方便地求解线性规划、整数规划和非线性规划问题。
但如果安装Office 97时采用的是典型安装方法，则【工具】菜单中是无“规划求解”功能项的。
可参照§2.8中介绍的方法将未安装的组件安装完整。
下面以第八章例8.1为例介绍用Excel求解线性规划的操作步骤和运行输出结果的分析。
一．求解线性规划的操作过程1．输入数据、公式和说明文字（1）在工作表中按图9.7所示格式输入必要的说明文字（图中粗体字部分）和LP模型的原始数据（图中虚线框所示单元格内，注意并不需要化为标准型）；图中F4是放置目标函数的单元格，B5:D5是放置决策变量X1、X2、X3（既“可变单元格”）的区域。
图9.7（2）在F4单元格内输入目标函数X0的计算公式：=B4*B5+C4*C5+D4*D5或=SUMPRODUCT（B4:D4,B5:D5）其中SUMPRODUCT（）函数返回两个或多个区域（即数组）中对应单元格乘积之和的值。
该函数可在Excel的“数学和三角函数”中找到。
（1）在E8单元格中输入第一个约束条件左端的计算公式：=B8*$B$5+ C8*$C$5+D8*$D$5或= SUMPRODUCT（B8:D8,$B$5:$D$5）然后拖曳E8的填充柄将公式复制到E9、E10单元格（注意公式中的B5、C5、D5或B5:D5要使用绝对引用）。
当模型中的变量数较多时，使用SUMPRODUCT（）函数可大大加快以上两个公式的输入速度。
说明：图中粗线框是表示要输入公式的单元格。
用Excel求解线性规划的数据输入格式可由用户自行设计，但以上介绍的格式不仅与我们所熟悉的LP模型相似，便于理解和使用；而且便于在对话框中输入约束条件。
按以上格式输入说明文字后，还可以使系统所输出的三个运行结果报告更具可读性。
2．选【工具】→“规划求解”，“打开规划求解参数”对话框，见图9.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.。