Excel规划求解的两类应用

收稿日期：2003 - 05 - 29。顾运筠，硕士生，主研领域：软件的应用。 Excel规 划 求 解 的 两 类 应 用 顾 运 筠 （上海体育运动技术学院 上海 200030） 摘 要 EXceI规划求解在很多方面都有应用，但它的应用在一般的教材和 EXceI的帮助中都没有详细的介绍。本文介绍用 EXceI 规划求解来解决线性规划和非线性回归这两类问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 。这两类问题分别在经济、交通、通信和生物医学等领域都有广泛的应用。如果 不用 EXceI的规划求解，解决线性规划和非线性回归问题是相当复杂的编程运算。应用了规划求解则可以直观而简捷地求得答案。 本文对两类问题都提供案例，给出解决 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 的步骤。 关键词 EXceI 规划求解 线性规划 非线性回归 曲线拟合 TWO SORTS OF APPLICATIONS USING EXCEL SOLVER Gu Yunyun （Shanghai Sports Technical College，Shanghai 200030） Abstract AIthough EXceI soIver is very usefuI in practice，there is no detaiI information about the appIication of EXceI SoIver in teXt books and EX- ceI heIp. This articIe introduce a simpIe，easy method to soIve Iinear programming probIem and non-Iinear regression probIem. Both probIems have a wide appIication in various practicaI fieIds，such as economics，transportation，communication，biomedicine and etc. Figuring out these two probIems are difficuIt，but the method described here is to use the SoIver function of Microsoft EXceI spreadsheet to both probIems. It is easy to be understood and impIemented step by step. Keywords EXceI SoIver Linear programming Non-Iinear regression Curve-fit 0 引 言 EXceI的规划求解是一个对数据进行因果分析的有效而简 洁的工具。本文要介绍的是它的两类应用。第一：如何用它来 求解线性规划问题。第二：如何用它实现非线性回归，也就是如 何利用规划求解实现非线性曲线拟合。 EXceI的规划求解工具是在“工具”菜单栏的“加载宏”里面。 可以进入“工具”菜单栏选择加载宏，然后在出现的对话框中选 中“规划求解”，按“确定”。如果没有安装，系统会提醒你安装。 下面分别说明如何实现两类应用。 1 线性规划问题 线性规划是在几个线性方程式或不等式的约束条件下，求 得满足目标函数取最大或最小的解。它是现代经营管理数学的 一个分支。它的应用包括：有效的分配资源、安排生产和投资、 切分问题、矩阵游戏、几何连接、网络流量问题等等。 下面是一个利用规划求解解决线性规划的运输问题的应 用。 有一个生产某产品的厂家，有 xl、x2 两个生产工厂，要把生 产的产品运送到 yl、y2、y3 三所消费中心。生产厂的供应量是： xl 厂是 ll0单位，x2 厂是 l30 单位。另一方面，消费中心的需 要量是：yl 是 80单位，y2 是 60单位，y3 是 l00单位。从各生产 厂往消费中心运送 l单位的产品，运输经费如表 l - l所示。这 时，为使总运输经费最少，应该从哪家工厂往哪个消费中心各运 多少单位的产品？ 表 1 - 1 费用矩阵 （ 单元 初级会计实务单元训练题天津单元检测卷六年级下册数学单元教学设计框架单元教学设计的基本步骤主题单元教学设计 ：万元） 消费中心 生产厂 yl y2 y3 供应量 xl 2 3 4 ll0 x2 3 4 6 l30 需要量 80 60 l00 240 在这个问题中，设 xl 的供应量为 al，x2 的供应量为 a2，yl 的需要量为 6l，y2 的需要量为 62，y3 的需要量为 63，xl! yl 的 运输量为 xll，xl! y2 的运输量为 xl2，⋯，x2! y2 的运输量为 x22，⋯，运输经费也按此设为 cll，cl2，⋯。上述运输问题可规格 化如下： 目标函数（总运输经费） C =" 2 i = l " 3 j = l cij xij!MIN （l） 约束条件： 供应量：" 3 j = l xij = ai （ i = l，2） 需要量：" 2 i = l xij = 6j （ j = l，2，3） xij#      0 （2） 第 22卷第 l期 计算机应用与软件 VoI.22，No.l 2005年 l月 Computer AppIications and Software Jan.2005 也就是说，在（2）式的约束条件下，确定满足（1）式的 xij。这个 问题可以通过数学计算求解，但可以用 EXOeI的规划求解迅速而 准确地求得结果。步骤如下： 1）在 EXOeI的 Sheet1工作表的 C3：E4单元格中输入 xij的 可行解：C3：E3中为（ x11 x12 x13）的值（80 30 0）；C4：E4 中为（ x21 x22 x23）的值（0 30 100）。 2）在 Sheet1工作表的 G3单元格输入约束值 a1 = 110，G4 单元格输入约束值 a2 = 130，在 C5单元格输入约束值 61 = 80， 在 D5单元格输入约束值 62 = 60，在 E5单元格输入约束值 63 = 100。 3）在 Sheet1工作表的 A1单元格输入目标函数的公式： = C3!2 + D3!3 + E3!4 + C4!3 + D4!4 + E4!6 其中的系数为表 1 - 1中的对应的运输经费。 4）选中 A1，然后进入菜单栏上的“工具”I“规划求解⋯”， 在对话框中输入如下内容（如图 1 - 1）：“目标单元格”：S"A S"1； “等于”：最小值；“可变单元格”：S"CS"3：S"ES"4。添加约束条件，在 图 1 - 2的对话框中输入：“单元格引用位置”：S"GS"3；选择等号； “约束值输入”：= S"CS"3 + S"DS"3 + S"ES"3。同样再输入约束条件： S"GS"4 = S"CS"4 + S"DS"4 + S"ES"4，S"CS"5 = S"CS"3 + S"CS"4，S"DS"5 = S"DS"3 +S"DS"4，S"ES"5 = S"ES"3 + S"ES"4，S"CS"3 > = 0，S"CS"4 > = 0，S"DS"3 > = 0，S"DS"4 > = 0，S"ES"3 > = 0，S"ES"4 > = 0。最后按“求解”键求出 最优解，如图 1 - 3所示。如果想生成“运算结果报告”、“敏感性 报告”和“极限值报告”，只要在最后的对话框中把这三个报告选 中即可。 图 1 - 1 规划求解参数设置和工作表中输入的数据 图 1 - 2 规划求解的约束条件设置 图 1 - 3 求解以后的工作表中的数据 由图 1 - 3可知，表 1 - 2是该运输问题的最优解。这时的 总运输费用是： c = 10 > 2 + 100 > 4 + 70 > 3 + 60 > 4 = 870（万元） 表 ! " # 运输问题最优解 消费中心 生产厂 y1 y2 y3 x1 10 100 x2 70 60 # 非线性回归 非线性回归就是用连续的曲线来描述实验所得的离散数 据。非线性回归在生物学实验、医学实验、化学实验、计量学等 领域有广泛的应用。 下面是一个如何应用 EXOeI的规划求解获得文献老化规律 的负指数模型的参数。 文献老化规律的负指数模型为： c（ t）= C0 e - 6t （3） 其中：C0 为 t = 0时，文献被引用的初始量；t 为年份，从文献被 引用的峰值那一年开始。我们要根据文献引用的数据求取 6 值。所取的数据是 1989年 SCI 引文分布数据［3］。 求解步骤如下： 1）在 A2：A29单元格中输入引文分布数据。在 H1：H4单 元格分别输入：初始值 C0（10.23），老化速度常数 6 的初始值 0.2，引文数据的平均值 mean Of O，相关系数的平方 R2。并用 EXOeI中的“插入”I“名称”给它们分别命名为：C0，6，mean Of O，RSO。 2）在 B2：B29单元格中输入负幂指数公式（3），分别为：= C0!EXP（ - 6!0）、= C0!EXP（ - 6!1）、⋯、= C0!EXP（ - 6 !27）。其中：C0、6 分别为 H1、H2单元格中的值。 3）在 H3中输入引文数据的平均值 mean Of C：= AVER- AGE（A2：A29）。 4）在 H4中输入相关系数的平方。相关系数的公式为： R = 1 - #（C - Cfit） 2 #（C - Cmean）$ 2 所以 H4中的公式为：= 1 - SUM（（A2：A29 - B2：B29）*2）/ SUM（（A2：A29 - mean Of C）*2），输入完以后按 CtrI + Shift + En- ter，给公式加上｛｝，因为是矩阵运算。整个 1 - 4步输入的数据 如图 2 - 1 所示。（EXOeI 中显示单元格中的公式：“工具”I“选 项”I“视图”I“窗口选项”：“公式”前的勾打上。） 图 2 - 1 EXOeI中输入的求解非线性回归的数据和公式 5）最后进入 EXOeI的规划求解。如图 2 - 2，目标单元格： RSO；等于：最大值；可变单元格：6；约束：6 < = 2、6 > = 0。选项 的设置如图 2 - 3。 运行规划求解后，求得 6 = 0.1251，这时 R2 = 0.9977459101。 138 计算机应用与软件 2005年 图 2 - 2 求非线性回归的规划求解的参数设置 如果对函数取对数，进行线性回归所得的结果是：b = 0.1233，R2 = 0.9968。这两个结果相当接近。 这里拟合的负指数函数相对简单。可以对复杂的函数进行 拟合，如［4］： y = 1 ［1 + exp（ V - Eslope ）］ 以及［5］ f（X）= Hexp -（X - x） 2 ![ ]2 + B 等等。除了指数曲线以外，对于其他曲线也适用。 图 2 - 3 求解非线性回归的有关迭代次数 和精度等的规划求解选项设置 ! 总 结 本文介绍了 Excei的规划求解在线性规划和非线性回归中 的应用。可以看出，Excei中的规划求解可以适用于目标函数是 线性的，也可以适用于目标函数是非线性的。既可以对单变量 求解，也可以对多变量求解。使用者不用了解复杂的求解数学 过程，只要把数据在数据表中设置好，目标函数、变量和约束条 件等给定以后就可以轻松地求得结果。 参 考 文 献 ［1］Va ek Chv tai，《Linear Programming》（1983）NewYork，W. H. Freeman and Company. ［2］木下荣藏，规划数学入门，湖南科学技术出版社，1991. ［3］丁学东，文献计量学基础，（1993）81 ~ 84. ［4］Angus M. Brown，Computer Methods and Programs in Biomedicine，65（2001） 191 ~ 200. ［5］s.Waish and D.Diamond，Taianta，Voi .42，No.4，561 ~ 572（1995） !!!!!!!!!!!!!!!!!!!!!!! . （上接第 ""页） aunitCase = AnaiysisCase（aAnaiysisunit）"提取相应的格 asematicFrame = FiiisemanticFrame（aunitCase）"填充局部语义框架 aLocaireievancy = CaicuiateLocaireievancy（asemanticFrame） "计算局部相关度 END giobaireievancy = Caicuiategiobaireievancy（iocaireievancy［］） "计算全局相关度 return（giobaireievancy > fiiteringThreshoid） "比较全局相关度和过滤阈值 END # 系统实验性能 实验选取了如下 5类文本样本： 类别 内容 数量 A 宣扬邪教 77 B 批判邪教 38 C 批判吴晗 25 D 赞扬吴晗 10 E 散文 34 实验采用的语料库不仅包括了正面信息的语料，还特意增 加了负面信息的语料。目的是考察能否有效拦截 A、C两类的 文本，并且抵御 B、D两类的干扰。我们用基于统计 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 的 kNN 分类方法进行了同样的测试。两种方法的各项指标如下表： 基于语义分析 kNN 宏准确率 99% 85% 召回率 97% 82% 准确率 97.8% 83% 本系统过滤的准确率达到了 99%，召回率达到了 97%。通 过分析，不能完全精确过滤的瓶颈在于语义分析技术，这有待于 自然语言理解技术的全面发展，对 kNN方法的结果进行分析后 发现，kNN方法对于信息的倾向性分析效果不佳，造成过滤的准 确率不高。我们还选取了平均大小不同的文本进行测试，发现 文本大小对效果影响不大。 $ 结束语 本文提出了一种倾向性文档过滤系统，通过考虑特征项之 间的语义关系，从而达到立场判断和内容审查的目的。系统的 特色是进行局部分析的时候采用基于规则的方法，在进行全局 分析时采用简化的统计方法。实验表明，本系统具有很高的查 全率和查准率，对倾向性文本可以高效地进行识别和过滤。通 过建立相应领域的数据库，该系统能应用到各种领域的倾向性 文档过滤。 文本过滤是一项涉及面较广的领域，还有很多问题需要深 化研究。就本文来讲，如何简化建立过滤模板就是一个需要进 一步探索的问题。可以采取潜在语义标注的方法［4］，帮助用户 分析语义关系，采用统计方法对相关度进行训练，减少用户建立 语义框架的负担。 参 考 文 献 ［1］甘勇、陈锬、朱贵良，“基于语义分析的电子邮件过滤系统 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 ”，《微 电子学与计算机》，2002.8. ［2］Dougias W.Oard，et ai .，A Conceptuai Framework for Text Fiitering. http：" www. ciis. umd. edu/dirg/fiiter/papers. ps. ［3］Fiimore C. J.，The case of the case. In：Bach E.，Harms r.（eds）universais in Linguistic Theory，New York，Hoit，rinehart and Winston，1986 . ［4］Laham D.，Latent semantic Anaiysis approaches to categorization. In：Pro- ceedings fo the Cognitive science society，1997. 第 1期 顾运筠：Excei规划求解的两类应用 139 Excel规划求解的两类应用 作者： 顾运筠 作者单位： 上海体育运动技术学院,上海,200030 刊名： 计算机应用与软件 英文刊名： COMPUTER APPLICATIONS AND SOFTWARE 年，卷(期)： 2005,22(1) 被引用次数： 12次 参考文献(5条) 1.S Walsh;D Diamond 查看详情[外文期刊] 1995(04) 2.Angus M.Brown A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet.[外文期刊] 2001(3) 3.丁学东 文献计量学基础 1993 4.木下荣 规划数学入门 1991 5.Linear Programming 1983 引证文献(12条) 1.付木亮.余小飞 基于Excel的网络最短路问题的求解[期刊论文]-技术与市场 2010(6) 2.梁琛平.彭灿 MC模式下多属性产品的改进生产指派模型[期刊论文]-工业工程与管理 2009(3) 3.顾治萍 Excel在混合整数规划中的应用[期刊论文]-兰州工业高等专科学校学报 2008(2) 4.何丽.周从直.谢有奎.高银峰 Excel规划求解水-岩作用模型[期刊论文]-地下空间与工程学报 2008(2) 5.宁振刚.郭子平.李震平 基于Excel规划求解的应用程序复杂数据处理[期刊论文]-科技信息（学术版） 2007(31) 6.李中军 利用EXCEL求解数据包络分析[期刊论文]-科技经济市场 2007(5) 7.尹小英.姚卫峰.胡育筑 中药指纹图谱的线性梯度分离条件的快速优化方法[期刊论文]-分析化学 2007(6) 8.胡斌.黄天强.陈生萍 用Excel求解网络优化问题[期刊论文]-计算机技术与发展 2006(7) 9.陈生萍.田宏秀.黄天强 Excel规划求解在决策分析中的应用[期刊论文]-吉首大学学报（自然科学版） 2006(2) 10.曹玲 基于Excel、Matlab和Chemcad工程软件对蒸馏过程的研究[学位论文]硕士 2006 11.利用Excel求解线性规划问题[期刊论文]-湖南农业大学学报（自然科学版） 2005(5) 12.王伟 高精度超声波原油含水率仪器的研究[学位论文]硕士 2005 本文链接：http://d.g.wanfangdata.com.cn/Periodical_jsjyyyrj200501054.aspx