龙源期刊网
整数规划模型Excel求解的简化方法
作者：陈候炎 徐玉娥 陈其嶙
来源：《科学与财富》2011年第12期
[摘 要] 整数规划是一类典型的线性规划问题。对于这类问题，运筹学中已有解决的方法，但比较繁琐。本文利用Excel软件的“规划求解”工具，对整数规划问题求解的模型建立和求解作了较详尽的论述。
[关键词] 整数规划问题 Excel 规划求解
整数规划是线性规划中的一类典型问题，应用于解决生产实际的许多问题，有着广泛的应用前景。对于这类问题，运筹学中已有解决方法，如分枝定界法、穷举法等，但很繁琐。也有借助于Matlab、Mathematics和 Lingo等软件求解,但专业性太强。相比之下,Excel功能强大，汉化水平高，菜单操作方便，拥有大量的函数、公式等，不需专门购买和安装。为解决整数规划问题提供了一种很好的工具。本文结合实例说明利用在Excel软件中“规划求解”工具，建立数学模型并求解整数规划问题。
1 “规划求解”工具
Microsoft Excel的“规划求解”工具取自于Leon Lasdon和Allan Waren共同开发的非线性最优化代码。“规划求解”是Execl中的一个加载宏。
1.1 安装 “规划求解”
加载宏是Excel的一个可选安装模块，在安装Microsoft Excel时，系统默认的安装方式不会安装宏程序，只有在选择“完全／定制安装”时才可选择安装这个模块。如果采用“典型安装”，则“规划求解”工具没有安装 ，就必须重新启动Office安装程序并且选择Excel选项，在加载宏区段中选择 “规划求解”，然后进行安装。
1.2 加载“规划求解”
安装了“规划求解”之后，在“工具”菜单下可能仍然找不到“规划求解”，此时您可以选择“工具/加载宏”，在打开的“加载宏”对话框中选中 “规划求解”复选框，确定后，就可以将“规划求解”命令添加到“工具”菜单栏中了。
EXCEL规划求解功能操作说明
Excel规划求解功能是Excel内置的解决最优化问题的工具，可用于线性规划、整数规划、非线性规划等诸多领域。该功能十分便捷灵活，可以帮助用户快速找到问题的最优解。
一、添加求解功能
1.打开Excel表格，点击“文件”>“选项”>“加载项”。
2.在弹出的窗口中选择“Excel加载项”>“转到”>“excel加载项”>“管理”。在“可用的加载项”中勾选“求解器”并关闭窗口。
3.返回Excel表格，在数据选项卡中选择“分析”>“求解”，弹出求解对话框。
二、建立规划模型
1.确定目标：需要确定最终要达到的目标或绩效指标，例如最大化利润、最小化成本等。
2.确定决策变量：需要确定影响目标的变量，例如销售量、成本等。
3.建立约束：需要确定影响决策变量的条件，例如材料成本、生产时间等。注意约束需要用等式、不等式等数学形式表示。
例如，在一个玩具生产厂家的例子中，有以下规划问题：在有限的资源下，最大化玩具的利润。
目标：最大化利润。
决策变量：生产每种玩具的数量。
三、设置求解参数
1.目标单元格：选择Excel表格中目标单元格，该单元格包含要优化的方程式。
4.变量单元格必须满足约束：勾选此项，保证变量单元格满足约束条件。
5.求解方法：选择要使用的求解算法，包括线性规划、非线性规划和整数规划等。
1.点击“求解”按钮，系统会自动寻找目标单元格、变量单元格和约束单元格区域。
2.系统执行计算，找到最优解并将其展示在新的单元格区域中。
3.若求解成功，单击“继续”将结果保存在Excel表中。 总之，利用Excel规划求解功能，用户可以通过建立规划模型，设置求解参数和运行求解功能轻轻松松地优化各种最优化问题。
规范型式线性规划Excel通用模型
关文忠 （重庆三峡学院 退休）
Excel内置了“规划求解”，对于线性规划、整数规划、0-1规划提供了快捷便利的工具，但Excel建模需要花费大量工夫。为此，制作了线性规划Excel模型（如图1）。
图 1 线性规划Excel模型
1.该模型的特点
（1）变量数和约束数通过改变C3和C4单元格可自动扩展，为此将模型的约束左端项和右端项置于左侧；
（2）模型可以英文显示也可以中文显示，只要在C5单格的下拉列表中选择“中文”，即可得到中文显示（如图2）。
图 2 线性规划Excel模型（中文显示）
（3）左端项和目标值的公式是动态连接的，数据区域的选择通过offset函数连接C2、C3单元格自动扩展。
2.模型的应用
该模型适用范围：对于如下形式
的连续型、整型、二进制，多变量、多约束均可。模型中的约束符及优化目标只是在设置“规划求解参数”时起到提示作用。
【例】求解线性规划：
操作如下：
（1）将C3，C4单元格数字均改为3，优化目标选择“最小化”，第1个约束符改为“>=”。输入目标系数、约束系数和右端项（如图3）
图 3 线性规划Excel模型
（2）设置“规划求解参数”对话框。从“数据”选择“分析”选项卡，选择“规划求解”，设置规划求解参数如图4.
图 4 规划求解参数设置
（若整数规划，再添加整型约束，即约束左端选择决策变量所在单元格区域，约束符选择“int”；若0-1规划，则约束符选择“bin”）设置完成单击“求解”得“规划求解结果”对话框（如图5）
图 5 规划求解结果
选择3个报告“运算结果报告”、“敏感性分析报告”和“极限值报告”（若是整数规划和0-1规划只有“运算结果报告”）。有关报告的解释本文从略。运行完成后，可另存为其他名字的文档，以保持模型的原始状态。
图 6 规划求解结果
图 7 规划求解结果
图 8 规划求解结果
图 9 规划求解结果
excel建模的知识,方法和一般步骤
Excel建模是一种应用广泛的数据分析方法，通过Excel软件的强大功能，将现实世界的问题抽象化，建立起数学模型，从而实现对问题的定量分析和预测。下面将介绍Excel建模的知识、方法和一般步骤，希望能对初学者提供一定的指导意义。
一、知识
1. Excel基础知识：熟悉Excel软件的基本操作，如数据输入、单元格格式化、公式计算、图表制作等。
2. 数据分析知识：了解常见的数据分析方法，如统计学、回归分析、决策树等，了解各种指标的含义和计算方法。
3. 建模方法知识：了解常见的建模方法，如线性规划、整数规划、动态规划等，了解建模思路和解决问题的步骤。
二、方法
1. 确定建模目标：明确问题的核心，确定需要解决的关键问题或预测的指标。
2. 收集数据：根据建模目标，收集相关的数据，可以是现有的数据，也可以通过实地调查或模拟等方式获取。
3. 数据处理：将收集到的数据整理成Excel表格的形式，进行适当的数据清洗、筛选和格式化。 4. 建立数学模型：根据问题的特点和目标，选择适当的建模方法，将问题抽象成数学公式，并在Excel中实现。
5. 模型求解：通过合适的求解方法，使用Excel提供的求解器或自定义的宏等工具，对建立的模型进行求解，得出结果。
6. 结果分析：对求解结果进行分析和解释，根据需求和问题的性质，制作相应的图表和报表，以便更好地理解和应用结果。
7. 模型验证：通过与实际情况的比较，验证建立的模型的准确性和可靠性，不断改进和修正模型，提高建模的效果。
三、一般步骤
在进行Excel建模时，一般可以按照以下步骤进行：
1. 确定建模问题和目标，明确需要解决的核心问题。
2. 收集与问题相关的数据，进行数据处理和整理。
3. 选择合适的建模方法，将问题抽象成数学公式。
4. 在Excel中建立模型，使用公式和函数进行计算和预测。
5. 制作图表和报表，对结果进行可视化展示和分析。
