Excel2010中规划求解的应用_王侃
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
（1）小 立 只 有 周 五 有 空 ，可 以 去 学 生 会 值 班 。 （2）小 新 只 能 紧 跟 着 小 王 ，也 就 是 比 小 王 晚 一 天 值 班 。 （3）小 倩 要 比 小 新 早 两 天 值 班 。 （4）小 苏 又 要 比 小 倩 早 两 天 值 班 。 （5）小 鹏 只 要 比 小 立 早 值 班 就 行 。 （6）小 巍 只 要 比 小 立 晚 值 班 就 行 。 （7）小 辉 哪 天 值 班 都 可 以 。 步骤一：首先根据上面的情况，在 Excel 表格中输入如下内容：
1 特点
1.1 规划问题的特点 一般来讲，适合使用规划求解的问题具有如下特点： （1）目 标 单 元 格 的 解 都 有 单 一 的 目 标 ， 如 求 运 输 的 最 佳 路 线 ， 求 值
班人员的最佳安排时间表，求产品的最低成本等等。 （2）对 于 目 标 单 元 格 的 解 存 在 有 明 确 的 可 以 用 不 等 式 表 达 的 约 束
湖州 是 长 江 三 角 洲 地 区 “先 行 规 划 、先 行 发 展 ”的 14 个 重 点 城 市 之一，处于上海、杭州、苏南三个大经济圈中心地带，发展物流业有着 天然的区位优势。 可是，湖州市物流业总体相对周边而言处于低端水 平，层次不高，竞争力不强，市场份额逐渐被外地企业渗透。 再加上近 几年生产型企业把风险转移至物流企业，使得物流企业应收账款逐年 递增，“营改增”带来的影响尚未最终显现，油价和用工成本增加，物流 企业获利能力变得十分有限，发展后劲明显不足。 近年来，经济社会发 展取得了一定进步，市民的平均消费水平逐年提高，电子商务交易量 逐渐增大。 作为物流行业管理部门，我们要和物流企业一起分析研究 电商市场给物流发展带来的新转机，降低投资风险，引导企业加快培 育物流新业态，促进物流业转型发展。 Ｓ
［责任编辑：汤静］
Science & Technology Vision 科技视界 267
科技·探索·争鸣
Science & Technology Vision
Βιβλιοθήκη Baidu科技视界
职校科技
Ｅｘｃｅｌ２０１０ 中规划求解的应用
王侃 (常州工程职业技术学院，江苏 常州 213164)
【摘 要】Excel2010 具有强大的电子表格处理功能，使用它可以进行各种数据处理、统计分析和辅助决策等。 规划求解是一组命令中的一 部分，有时称为假设分析工具。 通过使用规划求解，可以根据对工作表中单元格值的约束或限制找出目标单元格中公式的最优（最大或最小） 值。
现有快递企业已经很多，但这些企业大都规模小，没有足够的场 地，也没有足够的资金，想进一步扩大业务很是困难，今后几年，必将 进行一次洗牌。 不过这些企业一部分已经具有比较成型的操作经验， 同时相对集中了熟练工人和技术人员。 如果我们的物流企业和这类进 行兼并、重组，甚至联合股份新组企业，则很快就会发挥各自优势，打 开局面。 另一方面，电商物流和湖州本地以干线运输为主的物流企业 在业务上完全可以兼容，甚至可以视作供应链的延伸，但必须对原有 流程进行改造，把电商物流及时、准确、高效的特色和以“以顾客为中 心”理念很好地融入业务流程每个环节。 5.4 加强信息化建设，确立电子物流发展观
电商发展必须以信息化为基础，电商物流也必须建立在高度信息 化的基础上。 物流是电子商务重要的组成部分，缺少了现代化的物流 技术，电子商务给消费者带来的购物便捷等于零。 没有物流的信息化， 任何先进的技术装备都不可能应用于物流领域，具体表现在物流信息 的商品化、物流信息收集的数据化和代码化、物流信息处理的电子化 和程序化、物流信息传递的标准化和实时化、物流信息存储的数字化 几个方面。 信息技术和计算机技术的应用将改变物流业的现状，我们
【 关 键 词 】Excel2010 ； 规 划 求 解 ； 值 班 表
Microsoft Excel 中的规划求解工具取自德克萨斯大学奥斯汀分校 的 Leon Lasdon 和 克 里 夫 兰 州 立 大 学 的 Allan Waren 共 同 开 发 的 Generalized Reduced Gradient (GRG2) 非线性最优化代码。 规划求解是 一组命令的组成部分，这些命令有时也称作假设分析工具，即该过程 通过更改单元格中的值来查看这些更改对工作表中公式结果的影响， 规划求解主要是为工作表中的目标单元格中的公式找到一个优化值， 在保证工作表中的其他数据保持在设置的范围之内时通过改变输入 值从而求出最优解。
2 安装规划求解插件
由 于 在 Excel2010 中 规 划 求 解 是 一 个 插 件 ， 并 不 显 示 在 选 项 卡 中，因此在使用规划求解之前要先进行安装。 安装规划求解插件的方 法有两种，第一种方法具体步骤如下。
（1）在 Excel2010 窗 口 单 击 【文 件 】选 项 卡 ，在 打 开 的 菜 单 中 点 击 【选项】命令。
3 规划求解的应用
通过应用 Excel 规划求解，可求得工 作 表 上 某 个 目 标 单 元 格 中 公 式的最优值。 规划求解将对直接或间接与目标单元格中公式相关联的 一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的 结果。 在创建模型过程中，可以对规划求解模型中的可变单元格数值 应用约束条件，即规划求解中设置的限制条件，可以将约束条件应用 于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单 元格。 而且约束条件可以引用其他影响目标单元格公式的单元格。 下 面 通 过 一 个 具 体 的 例 子 来 说 明 如 何 使 用 Excel2010 中 的 规 划 求 解 功 能。
【参考文献】 ［1］张 铎 .电 子 商 务 与 物 流 [M]. 清 华 大 学 出 版 社 ，2000. ［2］ 王 公 强 . 电 子 商 务 环 境 下 物 流 发 展 的 探 讨 [J]. 价 值 工 程 ，2010(4)：47-52. ［3］ 杨 乐 如 . 电 子 商 务 与 物 流 [J]. 物 流 技 术 与 应 用 (货 运 车 辆 )，2011(7)：60-64. ［4］王磊.浅析我 国 电 子 商 务 物 流 的 发 展 及 对 策[J].现 代 经 济 信 息 ，2010(21)：1215.
条件和限制。 （3）可 以 把 问 题 的 表 达 描 述 为 ：一 组 约 束 条 件 及 限 制 （ 不 等 式 ）， 一
个目标方程。 （4）输 入 值 直 接 或 间 接 地 影 响 约 束 条 件 和 目 标 单 元 格 的 解 。 （5）利用 Excel 可以简单的求得问 题 满 足 约 束 条 件 和 限 制 求 得 的
（2）在 弹 出 的 【Excel 选 项 】对 话 框 中 ，左 侧 列 表 选 择 【加 载 项 】，然 后 打 开 右 侧 的 【管 理 】下 拉 菜 单 ，选 择 【Excel 加 载 项 】，单 击 【转 到 】按 钮。
（3）在 弹 出 的 【加 载 宏 】对 话 框 中 ，勾 选 【规 划 求 解 加 载 项 】，单 击 【确定】按钮。
［责任编辑：汤静］
（上接第 265 页）市场，前瞻的科学的制定 发 展 规 划 ，对 物 流 业 发 展 做 出准确预判，主动出击，在激烈市场竞争中抢占先机。 5.2 加强基础建设，打造以电商物流为核心业务的功能区
有物流基地建设基本以打造为生产型企业服务为主第三方物流 功能，而电商物流有其自身特点，但两者之间差别不大，只要内部稍加 改建，即可通用。 现在已打算发展电商物流为核心业务的企业在基地 建设时则要通盘考虑，尤其在线路布置、包装业务、分拣流程、条形码 应用以及内部交通组织等各方面，突出电商物流特点，便于快速高效 开展电商第三方物流业务。 湖州已经有两家企业把自身基地建设定位 为电商物流配套服务。 5.3 资源整合，促进传统业务流程再造
目标最优解。 1.2 规划求解的特点
（1）有 多 个 可 以 调 整 的 单 元 格 。 （2）可 以 通 过 更 改 其 他 单 元 格 来 确 定 某 个 单 元 格 的 最 大 值 或 最 小 值。 （3）可 以 指 定 可 调 整 单 元 格 可 能 的 数 值 约 束 。 （4）一 个 问 题 可 以 有 多 个 解 。
（4）在 功 能 区 切 换 到 【数 据 】选 项 卡 ，可 以 看 到 【规 划 求 解 】在 【分 析】组中，鼠标悬停在【规划求解】按钮上，可得到该功能的即时帮助信 息。
第二种方法具体步骤如下。 （1）第 一 步 和 第 一 种 方 法 相 同 ，在 Excel2010 窗 口 单 击 【文 件 】选 项卡，在打开的菜单中点击【选项】命令。 （2）在 弹 出 的 【Excel 选 项 】对 话 框 中 ，左 侧 列 表 选 择 【自 定 义 功 能 区】，然后勾选右侧主选项卡中的【开发工具】，单击【确定】按钮。 （3）回到 Excel2010 主界面，此时 选 项 卡 上 就 多 出 了 一 项 【开 发 工 具】，单击其中的【加载项】按钮，在弹出的【加载宏】对话框中，勾选【规 划求解加载项】，单击【确定】按钮。
4 小结
由于在实际安排过程中，要求各异，会出现与本文所举实例不一 致的情况，这就需要适当对其进行修改，使之能符合实际要求。
【参考文献】 ［1］孙国俊.用 Excel“规划求解”解决应用问题[J].微电脑世界，2001：8. ［2］何锦源.解析基于 Excel2003 进行规划求解[J].电脑编程技巧与维护，2010：14. ［3］孙爱萍，王瑞梅.如何利用 Excel 求解线性规划 问 题 及 其 灵 敏 度 分 析[J].办 公 自 动 化 ，2009：22.
作 者 简 介 ： 王 侃 (1975.06— )， 女 ， 汉 族 ， 讲 师 ， 主 要 从 事 计 算 机 基 础 及 软 件 编 程 的 教 学 。
266 科技视界 Science & Technology Vision
职校科技
Science & Technology Vision
科技视界
在重视信息流、商流和资金流的电子化同时也必须重视物流电子化建 设，发展电子物流。 5.5 重视人才储备，为后续发展提供源动力
电子商务“三高三新”（高人力资本含量、高技术含量、高附加价 值、新技术、新业态、新方式）的特点决定了电商物流也具备同样特点， 但是我们的企业在这一方面是个软肋。 人才缺乏是企业持续发展的瓶 颈，尤其是既懂业务又懂信息技术的人才更是凤毛麟角。 这就要求企 业站在长远发展的角度有意识地培养自己的后备力量，为企业后续发 展做好人才储备。
图1
由于小鹏和小巍的情况无法用具体的数值来描述，所以在输入公 式时引用了单元格 E1 作为变量来代表早或晚几天。
步骤二：设定规划求解参数 由于星期 1 到 7 之间的乘积值是固定 的 ，等 于 5040，所 以 选 定 星 期数的乘积作为目标值，在目标单元格 B12 内计算出当前各干事值班 时间的乘积，等于 6000，可以看出目标单元格内的值与 目 标 值 尚 不 符 合。 然后设置约束条件，设置参数如图 2 所示。
科技·探索·争鸣
图3
总 之 利 用 Excel2010 提 供 的 规 划 求 解 功 能 可 以 各 类 线 性 规 划 问
题、目标问题、运输问题、最短路径问题、最大流问题、数据包分析及其
图2
他运筹学问题，给工作带来了极大的方便，也提高了工作效率。 Ｓ
步骤三：创建规划求解报告 单击【求解】按钮即可弹出【划归求解结果】对话框，在此对话框在 红选中“运算结果报告”，最后单击【确定】按钮即可得到规划求解的结 果，同时生成一份规划求解报告，如图 3 所示。
在大学学生会里需要安排干事值班， 由于干事本身也是一名学 生，在安排值班时必须考虑到该干事何时有课程，何时有其他活动安 排等，因此做一份值班安排表也是学生会工作的重要内容之一。 在安 排值班表的过程中，常常会遇到值班时间与干事上课或活动时间冲突 的情况，因此需要统筹规划根据干事的具体情况来安排。 如我们假设 在一个星期内各个干事的具体情况如下：
1 特点
1.1 规划问题的特点 一般来讲，适合使用规划求解的问题具有如下特点： （1）目 标 单 元 格 的 解 都 有 单 一 的 目 标 ， 如 求 运 输 的 最 佳 路 线 ， 求 值
班人员的最佳安排时间表，求产品的最低成本等等。 （2）对 于 目 标 单 元 格 的 解 存 在 有 明 确 的 可 以 用 不 等 式 表 达 的 约 束
湖州 是 长 江 三 角 洲 地 区 “先 行 规 划 、先 行 发 展 ”的 14 个 重 点 城 市 之一，处于上海、杭州、苏南三个大经济圈中心地带，发展物流业有着 天然的区位优势。 可是，湖州市物流业总体相对周边而言处于低端水 平，层次不高，竞争力不强，市场份额逐渐被外地企业渗透。 再加上近 几年生产型企业把风险转移至物流企业，使得物流企业应收账款逐年 递增，“营改增”带来的影响尚未最终显现，油价和用工成本增加，物流 企业获利能力变得十分有限，发展后劲明显不足。 近年来，经济社会发 展取得了一定进步，市民的平均消费水平逐年提高，电子商务交易量 逐渐增大。 作为物流行业管理部门，我们要和物流企业一起分析研究 电商市场给物流发展带来的新转机，降低投资风险，引导企业加快培 育物流新业态，促进物流业转型发展。 Ｓ
［责任编辑：汤静］
Science & Technology Vision 科技视界 267
科技·探索·争鸣
Science & Technology Vision
Βιβλιοθήκη Baidu科技视界
职校科技
Ｅｘｃｅｌ２０１０ 中规划求解的应用
王侃 (常州工程职业技术学院，江苏 常州 213164)
【摘 要】Excel2010 具有强大的电子表格处理功能，使用它可以进行各种数据处理、统计分析和辅助决策等。 规划求解是一组命令中的一 部分，有时称为假设分析工具。 通过使用规划求解，可以根据对工作表中单元格值的约束或限制找出目标单元格中公式的最优（最大或最小） 值。
现有快递企业已经很多，但这些企业大都规模小，没有足够的场 地，也没有足够的资金，想进一步扩大业务很是困难，今后几年，必将 进行一次洗牌。 不过这些企业一部分已经具有比较成型的操作经验， 同时相对集中了熟练工人和技术人员。 如果我们的物流企业和这类进 行兼并、重组，甚至联合股份新组企业，则很快就会发挥各自优势，打 开局面。 另一方面，电商物流和湖州本地以干线运输为主的物流企业 在业务上完全可以兼容，甚至可以视作供应链的延伸，但必须对原有 流程进行改造，把电商物流及时、准确、高效的特色和以“以顾客为中 心”理念很好地融入业务流程每个环节。 5.4 加强信息化建设，确立电子物流发展观
电商发展必须以信息化为基础，电商物流也必须建立在高度信息 化的基础上。 物流是电子商务重要的组成部分，缺少了现代化的物流 技术，电子商务给消费者带来的购物便捷等于零。 没有物流的信息化， 任何先进的技术装备都不可能应用于物流领域，具体表现在物流信息 的商品化、物流信息收集的数据化和代码化、物流信息处理的电子化 和程序化、物流信息传递的标准化和实时化、物流信息存储的数字化 几个方面。 信息技术和计算机技术的应用将改变物流业的现状，我们
【 关 键 词 】Excel2010 ； 规 划 求 解 ； 值 班 表
Microsoft Excel 中的规划求解工具取自德克萨斯大学奥斯汀分校 的 Leon Lasdon 和 克 里 夫 兰 州 立 大 学 的 Allan Waren 共 同 开 发 的 Generalized Reduced Gradient (GRG2) 非线性最优化代码。 规划求解是 一组命令的组成部分，这些命令有时也称作假设分析工具，即该过程 通过更改单元格中的值来查看这些更改对工作表中公式结果的影响， 规划求解主要是为工作表中的目标单元格中的公式找到一个优化值， 在保证工作表中的其他数据保持在设置的范围之内时通过改变输入 值从而求出最优解。
2 安装规划求解插件
由 于 在 Excel2010 中 规 划 求 解 是 一 个 插 件 ， 并 不 显 示 在 选 项 卡 中，因此在使用规划求解之前要先进行安装。 安装规划求解插件的方 法有两种，第一种方法具体步骤如下。
（1）在 Excel2010 窗 口 单 击 【文 件 】选 项 卡 ，在 打 开 的 菜 单 中 点 击 【选项】命令。
3 规划求解的应用
通过应用 Excel 规划求解，可求得工 作 表 上 某 个 目 标 单 元 格 中 公 式的最优值。 规划求解将对直接或间接与目标单元格中公式相关联的 一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的 结果。 在创建模型过程中，可以对规划求解模型中的可变单元格数值 应用约束条件，即规划求解中设置的限制条件，可以将约束条件应用 于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单 元格。 而且约束条件可以引用其他影响目标单元格公式的单元格。 下 面 通 过 一 个 具 体 的 例 子 来 说 明 如 何 使 用 Excel2010 中 的 规 划 求 解 功 能。
【参考文献】 ［1］张 铎 .电 子 商 务 与 物 流 [M]. 清 华 大 学 出 版 社 ，2000. ［2］ 王 公 强 . 电 子 商 务 环 境 下 物 流 发 展 的 探 讨 [J]. 价 值 工 程 ，2010(4)：47-52. ［3］ 杨 乐 如 . 电 子 商 务 与 物 流 [J]. 物 流 技 术 与 应 用 (货 运 车 辆 )，2011(7)：60-64. ［4］王磊.浅析我 国 电 子 商 务 物 流 的 发 展 及 对 策[J].现 代 经 济 信 息 ，2010(21)：1215.
条件和限制。 （3）可 以 把 问 题 的 表 达 描 述 为 ：一 组 约 束 条 件 及 限 制 （ 不 等 式 ）， 一
个目标方程。 （4）输 入 值 直 接 或 间 接 地 影 响 约 束 条 件 和 目 标 单 元 格 的 解 。 （5）利用 Excel 可以简单的求得问 题 满 足 约 束 条 件 和 限 制 求 得 的
（2）在 弹 出 的 【Excel 选 项 】对 话 框 中 ，左 侧 列 表 选 择 【加 载 项 】，然 后 打 开 右 侧 的 【管 理 】下 拉 菜 单 ，选 择 【Excel 加 载 项 】，单 击 【转 到 】按 钮。
（3）在 弹 出 的 【加 载 宏 】对 话 框 中 ，勾 选 【规 划 求 解 加 载 项 】，单 击 【确定】按钮。
［责任编辑：汤静］
（上接第 265 页）市场，前瞻的科学的制定 发 展 规 划 ，对 物 流 业 发 展 做 出准确预判，主动出击，在激烈市场竞争中抢占先机。 5.2 加强基础建设，打造以电商物流为核心业务的功能区
有物流基地建设基本以打造为生产型企业服务为主第三方物流 功能，而电商物流有其自身特点，但两者之间差别不大，只要内部稍加 改建，即可通用。 现在已打算发展电商物流为核心业务的企业在基地 建设时则要通盘考虑，尤其在线路布置、包装业务、分拣流程、条形码 应用以及内部交通组织等各方面，突出电商物流特点，便于快速高效 开展电商第三方物流业务。 湖州已经有两家企业把自身基地建设定位 为电商物流配套服务。 5.3 资源整合，促进传统业务流程再造
目标最优解。 1.2 规划求解的特点
（1）有 多 个 可 以 调 整 的 单 元 格 。 （2）可 以 通 过 更 改 其 他 单 元 格 来 确 定 某 个 单 元 格 的 最 大 值 或 最 小 值。 （3）可 以 指 定 可 调 整 单 元 格 可 能 的 数 值 约 束 。 （4）一 个 问 题 可 以 有 多 个 解 。
（4）在 功 能 区 切 换 到 【数 据 】选 项 卡 ，可 以 看 到 【规 划 求 解 】在 【分 析】组中，鼠标悬停在【规划求解】按钮上，可得到该功能的即时帮助信 息。
第二种方法具体步骤如下。 （1）第 一 步 和 第 一 种 方 法 相 同 ，在 Excel2010 窗 口 单 击 【文 件 】选 项卡，在打开的菜单中点击【选项】命令。 （2）在 弹 出 的 【Excel 选 项 】对 话 框 中 ，左 侧 列 表 选 择 【自 定 义 功 能 区】，然后勾选右侧主选项卡中的【开发工具】，单击【确定】按钮。 （3）回到 Excel2010 主界面，此时 选 项 卡 上 就 多 出 了 一 项 【开 发 工 具】，单击其中的【加载项】按钮，在弹出的【加载宏】对话框中，勾选【规 划求解加载项】，单击【确定】按钮。
4 小结
由于在实际安排过程中，要求各异，会出现与本文所举实例不一 致的情况，这就需要适当对其进行修改，使之能符合实际要求。
【参考文献】 ［1］孙国俊.用 Excel“规划求解”解决应用问题[J].微电脑世界，2001：8. ［2］何锦源.解析基于 Excel2003 进行规划求解[J].电脑编程技巧与维护，2010：14. ［3］孙爱萍，王瑞梅.如何利用 Excel 求解线性规划 问 题 及 其 灵 敏 度 分 析[J].办 公 自 动 化 ，2009：22.
作 者 简 介 ： 王 侃 (1975.06— )， 女 ， 汉 族 ， 讲 师 ， 主 要 从 事 计 算 机 基 础 及 软 件 编 程 的 教 学 。
266 科技视界 Science & Technology Vision
职校科技
Science & Technology Vision
科技视界
在重视信息流、商流和资金流的电子化同时也必须重视物流电子化建 设，发展电子物流。 5.5 重视人才储备，为后续发展提供源动力
电子商务“三高三新”（高人力资本含量、高技术含量、高附加价 值、新技术、新业态、新方式）的特点决定了电商物流也具备同样特点， 但是我们的企业在这一方面是个软肋。 人才缺乏是企业持续发展的瓶 颈，尤其是既懂业务又懂信息技术的人才更是凤毛麟角。 这就要求企 业站在长远发展的角度有意识地培养自己的后备力量，为企业后续发 展做好人才储备。
图1
由于小鹏和小巍的情况无法用具体的数值来描述，所以在输入公 式时引用了单元格 E1 作为变量来代表早或晚几天。
步骤二：设定规划求解参数 由于星期 1 到 7 之间的乘积值是固定 的 ，等 于 5040，所 以 选 定 星 期数的乘积作为目标值，在目标单元格 B12 内计算出当前各干事值班 时间的乘积，等于 6000，可以看出目标单元格内的值与 目 标 值 尚 不 符 合。 然后设置约束条件，设置参数如图 2 所示。
科技·探索·争鸣
图3
总 之 利 用 Excel2010 提 供 的 规 划 求 解 功 能 可 以 各 类 线 性 规 划 问
题、目标问题、运输问题、最短路径问题、最大流问题、数据包分析及其
图2
他运筹学问题，给工作带来了极大的方便，也提高了工作效率。 Ｓ
步骤三：创建规划求解报告 单击【求解】按钮即可弹出【划归求解结果】对话框，在此对话框在 红选中“运算结果报告”，最后单击【确定】按钮即可得到规划求解的结 果，同时生成一份规划求解报告，如图 3 所示。
在大学学生会里需要安排干事值班， 由于干事本身也是一名学 生，在安排值班时必须考虑到该干事何时有课程，何时有其他活动安 排等，因此做一份值班安排表也是学生会工作的重要内容之一。 在安 排值班表的过程中，常常会遇到值班时间与干事上课或活动时间冲突 的情况，因此需要统筹规划根据干事的具体情况来安排。 如我们假设 在一个星期内各个干事的具体情况如下：