如何利用Excel2016 软件的规划求解功能求解线性方程组
excel线性规划求解
excel线性规划求解Excel是一种功能强大的电子表格软件，除了可以进行基本的计算和数据分析外，还可以用于求解线性规划问题。
线性规划是一种数学优化方法，通过最大化或最小化线性目标函数，同时满足一系列线性约束条件，从而寻找最优解。
在Excel中，我们可以使用内置的线性规划求解器来解决这类问题。
下面将介绍如何使用Excel进行线性规划求解。
首先，我们需要将线性规划问题转化为Excel的表格形式。
假设我们有一个线性目标函数和一系列线性约束条件，我们可以将变量和常数分别放置在表格的不同单元格中。
然后，我们可以在Excel中的“数据”选项卡中找到“线性规划求解器”。
单击“线性规划求解器”并选择“确定”后，我们将进入求解器对话框。
在求解器对话框中，我们需要设置求解的目标、变量和约束条件。
首先，我们需要选择是求取最大值还是最小值。
然后，我们需要指定目标函数和约束条件中的变量单元格范围。
接下来，我们可以指定变量的约束条件。
例如，我们可以将某个变量约束为非负数，或者指定它的取值范围。
最后，我们可以选择求解方法和优化选项。
一般来说，我们可以选择线性规划求解器自动选择最佳求解方法。
如果需要更精确的结果，我们可以选择增加迭代次数和精度。
完成设置后，单击“确定”按钮，Excel将自动计算并求解线性规划问题。
求解结果将显示在工作表中，并且还可以显示最优解的目标函数值和各个变量的取值。
使用Excel进行线性规划求解的优点是，它提供了一个直观和易于使用的界面，并且能够快速计算出结果。
然而，它也有一些局限性，例如只能处理线性约束条件和目标函数、求解的精度有限等。
总的来说，使用Excel进行线性规划求解是一种简单而方便的方法。
通过将问题转化为Excel的表格形式，并使用内置的线性规划求解器，我们可以快速求解线性规划问题，并获得最优解。
以上为关于Excel线性规划求解的简要介绍，希望能帮助你了解如何在Excel中进行线性规划求解。
Excel求解线性规划问题
（8）假定非负。对于在“添加约束”对话框中 “约束值编辑框中没有设置下限的可变单元格， 假定其下限为零。 （9）装入模型。输入对所要调入模型的引用。 （10）保存模型。指定输入模型的存在位置。 只有当需要在工作表上保存多个模型时才单击 此命令。
8）关闭。关闭对话框不进行规划求解，但保留 通过“选项”、“添加”、“更改”或“删除” 按钮所做的修改。 9）全部重置。清除规划求解中的当前设置， 将所有的设置恢复为初始值。 10）求解。输入对所要调入模型的引用。
规划求解结果的提示： 1）“规划求解”找到一个解。即找到一个满 足所有约束条件及设置精度要求的解。 2） “规划求解”收敛于当前结果，并满足全 部约束条件。表明最近5 次求解过程中的变化 量小于“规划求解选项”对话框中“收敛度” 设置的值。
当规划求解不能得到最佳结果时的提示信息 1）满足所有约束条件。“规划求解”不能进 一步优化结果。这表明仅得到近似值，不能得 到比显示结果更精确的数值。修改精度值。 2）求解达到最长运行时间后停止。表明达到 最长运行时间限制时，没有满意的结果。
2’ Excel求解线性规划
3 1 3 2
线性规划模型在Excel中的表示 Excel解线性规划问题
1、线性规划模型在Excel中的表示
1) 启动Excel 2)将决策变量、目标函数、约束函数三种信息在 3)Excel单元格中表示出来。如Excel表。 （１）约束条件（合计）列用到：sumproduct 函数． （２）目标函数表达式的输入．
7）无法满足设定的“采用线性模型”条件。 8）“规划求解”在目标或约束条件单元格中 发现错误值。找到有错误的目标函数或约束条 件单元格，修改其中的公式或内容。 9）内存不够。无法满足规划求解的内存要求。 方法是关闭一些文件或应用程序。 10）其它的Excel程序正在使用SOLVER.DLL。 关闭其中一个正在使用SOLVER.DLL的Excel文件。
用EXCEL解线性规划的步骤
1. 将目标函数系数放入一行
2. 将每个约束条件系数及常数项放入一行，所有约束系数及常数项成一矩阵
3. 将决策变量的初始值（全0或1）放入一列
4. 用函数SUMPRODUCT或MMULT（A1，A2）将目标 函数值放入一格：目标函数系数行与决策变量列的乘积
5. 用MMULT将每个约束条件系数行与决策变量列乘积放 入对应的常数项旁边格
6. 在工具栏选规划求解
7. 填好目标值所在格、决策变量（可变单元格）、约束条件
选项底下勾选采用线性模型
保存规划求解结果，包括运算结果、敏感性报告、极限值报告
最后结果
灵敏度分析
• 目标函数的系数
– 允许增加或减少的量：此范围内最优解不变 – 递减成本：最优解中等于0的变量，对应的 目标函数中的系数增加或减少多少，最优解 不再为0
• 约束条件右端常数项
– 阴影价格：约束右端常数项增加一个单位， 使得目标函数最优值增加的量 – 允许增加或减少的量：此范围内对应的阴影 价格不变
用EXCEL解线性规划的步骤
1. 2. 3.Biblioteka 4. 5. 6.– – – – – –
将目标函数系数放入一行 将每个约束条件系数及常数项放入一行，所有约束系数及常数 项成一矩阵 将决策变量的初始值（全0或1）放入一列 用函数MMULT（A1，A2）将目标函数值放入一格：目标函数 系数行与决策变量列的乘积（或者用SUMPRODUCT(A1,A2,…) 用MMULT将每个约束条件系数行与决策变量列乘积放入对应 的常数项旁边格 在工具栏选规划求解
巧用Excel解线性方程组(没学matlab先用这个,呵呵)
线性方程组在工程技术、经济等领域有着广泛的应用。
许多实际问题可以归结为一个线性方程组的解，因此线性方程组的解法已成为广大工程技术人员、经济工作者必须掌握的知识。
在线性代数领域，Excel可以通过插入函数来直接求行列式的值，逆矩阵和矩阵的乘积。
但Excel并没有直接求解线性方程组的功能，本人发现Excel的规划求解可用来解线性方程组，现把这种方法介绍给大家。
例如要解线性方程组x1+x2+2x3+3x4=13x1-x2-x3-2x4=-42x1+3x2-x3-x4=-6x1+2x2+3x3-x4=-4可按如下的步骤来解这个方程组：1.打开Excel。
2.由于在本方程组中未知数有4个，所以预留4个可变单元格的位置A1-A4。
3.将活动单元格移至B1处，从键盘键入：=A1＋A2＋2＊A3＋3＊A4：然后回车(此时B1显示0)。
即在B1处输入方程组中第一个方程等号左边的表达式。
4.在B2处从键盘键入：=3＊A1－A2－A3－2＊A4；然后回车(此时B2显示0)。
即在B2处输入方程组中第二个方程等号左边的表达式。
5.在B3处从键盘键入：=2＊A1＋3＊A2－A3－A4；然后回车(此时B3显示0)。
即在B3处输入方程组中第三个方程等号左边的表达式。
6.在B4处从键盘键入：=A1＋2＊A2＋3＊A3－A4；然后回车(此时B4显示0)。
即在B4处输入方程组中第四个方程等号左边的表达式。
7.点击工具规划求解，出现规划求解参数对话框。
9.对话框中第三栏为：可变单元格；我们预留的可变单元格为A1-A4，所以在可变单元格框内键入 A 1： A 4。
10.对话框中最后一栏为：约束；首先点击添加按钮，屏幕出现添加约束对话框。
11.在添加约束对话框的单元格引用位置键入：B1；在中间的下拉式菜单中选取=；在约束值处键入：1；然后按添加按钮，屏幕出现空白的添加约束对话框。
12.在添加约束对话框的单元格引用位置键入：B2；在中间的下拉式菜单中选取=；在约束值处键入：－4；然后按添加按钮，屏幕出现空白的添加约束对话框。
用Excel解方程(组)
用Excel解方程(组)准备工作本方法要用到"规划求解"这一功能，在"工具"菜单中若没有"规划求解…"命令，则单击菜单栏"工具"→"加载宏…"，在"加载宏"对话框的"当前加载宏"的列表框中选中"规划求解加载宏"，然后按"确定"钮，即可在"工具"菜单下出现"规划求解…"。
若"当前加载宏"的列表框中没有"规划求解加载宏"一行，则需重新安装Excel（自定义安装），具体过程本文从略。
求解步骤例如解方程组：1.运行Excel。
2.在A1～A3三个单元格中(也可在其他单元格中，最好是连续的，单元格个数由方程的个数决定。
我们暂称这个区为"方程区")依次输入"=2＊b1-b2＋2＊b3"、"=-b1＋3＊b2-5＊b3"、"=4＊b1＋2＊b2＋3＊b 3"(不分大小写，引号不输入，下同。
b1、b2、b3分别表示三个未知数x、y、z的值，即B1～B3单元格将显示三个未知数的值，它决定第4（3）步中要输入的内容)，分别回车后，三个单元格均显示"0"(因此时B 1～B3单元格均为空,当"0"处理,通过计算,刚输入的三个表达式的值也为0,故都显示0)。
3.单击菜单栏"工具"→"规划求解…"。
4.在"规划求解参数"对话框中进行下列设置：(1)在第一行"设置目标单元格"中输入"＄A＄1"(也可只输入"A1"，还可用鼠标选取对应的单元格，下同)；(2)在第二行"等于"中选取"值为"一项，并在其后的框中输入"－1"(即第一个方程右边的值)；(3)在第三行"可变单元格"框中输入"＄B＄1:＄B＄3"(即B1～B3单元格，由第2步决定，这三个单元格用于显示三个未知数的值，暂称之为"解区")；(4)在"约束"框中单击"添加"钮，出现"添加约束"对话框，在"单元格引用位置"中输入"＄A＄2"，在中间的下拉列表框中选取"="，在"约束值"中输入"12"(即第二个方程右边的值)。
EXCEL求解线性规划问题
约束右端值降低15时，目旳函数值旳变化量。
解：（1）最优解为x1=0, x2=12.4, x3=9.5
（2） x1旳目旳系数降低5，占允许降低旳百分比=5/∞=0%，x2 旳目旳系数增长4,占允许增长旳百分比=4/7.8=51.2%。
变化旳百分比和为51.2%，没有超出100%，所以最优解不变。
（3）第一资源约束右端值增长30，占允许增长旳30 /∞=0%， 第二资源约束右端值增长4 ，占允许增长旳4/15=26.7%，
•初值和终值分别指 单元格在此次求解 前旳数值和求解后 旳数值。
敏感性分析报告（1）
可变单元格中 • “单元格”指决策变量所在单元格旳地址 • “名字”是决策变量旳名称 • “终值”是决策变量旳终值,即最优值 • “递减成本”指最优解中档于0旳变量，相应旳目旳函数中旳系数
增长或降低多少，最优解不再为0 • “目旳式系数”目旳函数中旳系数，为已知条件 • “允许旳增量”与“允许旳减量”表达目旳函数中旳系数在增量
（1）引用旳类型
三种类型 ：
相对引用、 绝对引用、混合引用
（2） 相对引用
格式： A3 、B6
使用相对引用后，系统将会记住建立公式旳单元格和被 引用旳单元格旳相对位置，在复制这个公式时，新旳公式单 元和被引用旳单元依然保持这种相对位置。
（3）绝对引用 格式：$a$3 $d$5
绝对引用是指被引用旳单元与引用旳公式单元旳位置 关系是绝正确，不论将这个公式复制到任何单元，公式所 引用旳还是原来单元格旳数据。
2) 在弹出旳对话框中旳“可用加载宏”列表框 中,选定待添加旳加载宏“规划求解”选项旁 旳复选框,然后单击“拟定”.单击“拟定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
应用Excel软件求解线性规划问题
使用变量xij代表第i种原料用于生产第j种产品的数量（桶）
1
i=1, 2, 3分别代表催化裂化汽油、异戊烷和直馏汽油
2
j=1, 2, 3分别代表80#、100#汽油和燃料油
3
物料平衡约束
4
物料平衡约束
蒸汽压限制
01
辛烷值限制
02
变量非负约束
03
各类约束
Excel求解步骤
打开Excel，建立新工作表，输入公式
应用软件求解线性规划问题
添加副标题
202X
1.1 Excel的规划求解工具
Excel软件提供了求解一般规模数学规划问题的“规划求解”工具 该工具具有界面友好、操作简单、与Excel无缝集成等优点 可用于化学化工常见中、小规模线性规划、非线性规划、整数规划问题的求解
Excel提供的规划求解工具对模型规模有一定限制：求解模型的决策变量数不超过200个。当“规划求解选项”对话框中的“采用线性模型”复选框处于选中状态时，对约束条件的数量没有限制；而对于非线性问题，每个可变单元格除了变量的范围和整数限制外，还可以有最多达100个约束条件
Excel结果分析-3
整数规划的运行结果
由于工程实际问题中存在各种不确定性，因此线性规划模型中的系数C、aji、bj等均可能偏离原来的计算值，因此决策者必须掌握这些系数改变时对原最优解的影响，也就是必须进行线性规划的灵敏度分析。
可以应用Excel方便地实现
线性规划的灵敏度分析
The End
Excel求解步骤-1
打开规划求解窗口
Excel求解步骤-2
设置目标单元格 设置可变单元格 约束的输入
Excel求解步骤-3
设置规划求解选项
利用EXCEL进行线性规划
解：依题意，设置四种家具的日产量分别为决策变量
x1, x2 , x3, x4 ，目标要求是日利润最大化，
约束条件为三种资源的供应量限制和产品销售量限制。 据此，列出下面的线性规划模型：
MaxZ 60x1 20x2 40x3 30x4
4x1 2x2 x3 2x4 600
x1, x2 , x3 , x4 0 （非负约束）
其中 x1, x2 , x3, x4 分别为四种家具的日产量。
下面介绍用Excel中的“规划求解”功能求此题。 第一步 在Excel中描述问题、建立模型，如下图所示。
=SUMPRODUCT(B6:E6,$B$15:$E$15)
第二步 在“工具”菜单中选择“规划求解”。
第三步 在“规划求解参数”对话框进行选择如下图。
第四步 点击“选项”按钮，弹出“规划求解选项”对话框
第五步 选择“采用线性模型”和“假定非负”， 单击“确定”，返回下图。单击“求解”，即可解 决此题。
最后结果如下页图所示。
应如何安排这四种家具的日产量，使得该厂的日利 润最大？
表1 雅致家具厂基本数据
12
3
4
家具类型
劳动时间
21 3
2
（小时/件）
木材
42 1
2
（单位/件）
玻璃（单位/ 6 2
1
2
件）
单位利润
60 20 40
30
（元/件）
最大销量 100 200 50
100
（件）
可提供两 400小时 600单位 1000单位
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
线性规划的EXCEL求解
关于“规划求解选项”各可选项的说明 (３)
• 装入模型：输入对所要调入模型的引用 • 保存模型：将打开“保存模型”对话框， 输入模型的保存位置，只有当需要在工作 表上保存多个模型时，单击此命令，第一 个模型会自动保存。
一类特殊的线性规划问题：运输问 题
例1 某公司经销甲产品。它下设三个加工厂。 每日的产量分别是：A1为7吨，A2为4吨，A3为9吨。 该公司把这些产品分别运往四个销售点。各销售点 每日销量为：B1为3吨，B2为6吨，B3为5吨，B4为 6吨。已知从各工厂到各销售点的单位产品的运价如 下表所示。问该公司应如何调运产品，在满足各销 点的需要量的前提下，使总运费为最少。
( j 1, 2,3, 4) (i 1, 2,3)
这类问题，我们称之为运输问题。产量正好和销 量相等的运输问题称为产销平衡问题，产销平衡问题 有以下特征：
1. 平衡运输问题必有可行解，也必有最优解. 2. 平衡运输问题的约束方程系数矩阵 A 的所有各阶子 式只取 0，1 或 -1 三个值. 3. 如果平衡运输问题中的所有产量 ai 和销量 bj 4. 都是整数，那么，它的任一基可行解都是整数解.
线性规划问题的EXCEL求解
• 用EXCEL求解线性规 划问题前，需要在工 具菜单上选择加载宏： 弹出对话框
勾选规划工具，点击“确定”即可
• 若已加载过则无需再次加载。若安装不完全，也是无法加 载的，需要重新安装。 • 加载宏之后，工具菜单上即出现“规划求解”按钮，可以 用来求解许多规划问题，当然包含线性规划问题
例：某工厂生产三种产品，各种产品所需的原材料和设备 台时及能供给数量如下表所示，问如何安排生产利润最大？
甲
原材料 工时 单位利润 ３ ２ 4
乙
利用Excel求解线性方程组
利用Excel求解线性方程组{x1+x2=7…① x1-x2=-5…②}实例
一、打开excel,点击菜单栏中的数据选项，如图1：
二、在单元格a1中输入x1,在单元格a2中输入x2,在单元格a3中输入x1+x2,在a4单元格中输入x1-x2,如下图：
三、在单元格b3输入公式”=b1+b2”,回车；在单元格b4输入公式”=b1-b2”,回车，如图：
四、将b3单元格设为活动单元格，再点击命令按钮“规划求解”，如图：
五、在规划求解参数对话框中，设置目标为b3单元格绝对地址，到目标值（选中）为7，通过更改可变单元格，用鼠标选中b1:b2单元格，遵守约束里点击添加按钮，在添加约束对话框中，单元格引用中选择b4单元格，点击下拉按钮选择”=”,在约束栏填-5，再点击确定，如图：
六、再点击求解，即可在b1和b2单元格中求出x1和x2的值，如图：
Δ若点击数据菜单项没有规划求解，怎么办呢？可在文件菜单项中的选项子菜单中点击加载项，选择规划求解加载项，然后点击确定即可，如图：。
Excel求解线性规划问题实验教程要点
数学与信息科学学院Excel求解线性规划问题实验教程二零一三零八月目录1.关于“规划求解” (1)2.如何加载“规划求解” (2)3.“规划求解”各参数解释和设置 (3)4.“规划求解”的步骤 (6)5.Excel求解线性规划问题 (8)6.Excel求解运输问题 (14)7.Excel求解目标规划问题 (18)8.Excel求解整数规划问题 (22)1.关于“规划求解”“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式:单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。
公式总是以等号（=）开始）的最优值。
“规划求解”将对直接或间接目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。
“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。
在创建模型过程中，可以对“规划求解”中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。
可以将约束条件应用于可变单元格、目标单元格或其它与目标单元格直接或间接相关的单元格。
而且约束条件可以引用其它影响目标单元格公式的单元格。
使用“规划求解”可通过更改其它单元格来确定某个单元格的最大值或最小值。
）Microsoft Excel的“规划求解”工具取自德克萨斯大学奥斯汀分校的Leon Lasdon 和克里夫兰州立大学的Allan Waren共同开发的Generalized Reduced Gradient(GRG2)非线性最优化代码。
线性和整数规划问题取自Frontline Systems公司的John Watson 和Dan F ylstra提供的有界变量单纯形法和分支边界法。
2.如何加载“规划求解”安装office的时候，系统默认的安装方式不会安装宏程序，需要用户根据自己的需求选择安装。
利用excel软件求解线性规划问题
数学规划模型实验指导手册Excel的规划求解加载宏求解数学规划问题一、什么是规划求解加载宏？规划求解加载宏（简称规划求解）是Excel的一个加载项1，可以用来解决线性规划与非线性规划优化问题。
规划求解可以用来解决最多有200个变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的问题。
可以设置决策变量为整型变量。
规划求解加载宏的开发商是Fronline System公司。
用户通过自定义安装MS-Office所使用的是标准版本规划求解加载宏，Fronline System公司同时提供增强的Premium Solver工具。
规划求解工具在Office典型安装状态下不会安装，可以通过自定义安装选择该项或通过添加/删除程序增加规划求解加载宏。
二、怎样加载规划求解加载宏？加载规划求解加载宏的方法如下：（1）打开“工具”下拉列菜单，然后单击“加载宏”，打开“加载宏”对话框。
（2）在“可用加载宏”框中，选中“规划求解”旁边的复选框2，然后单击“确定”按钮。
1加载项的功能是为Microsoft Office 提供自定义命令或自定义功能的补充程序2如果“规划求解”未列出，请单击“浏览”进行查找。
（3）如果出现一条消息，指出您的计算机上当前没有安装规划求解，请单击“是”用原Office安装盘进行安装。
（4）单击菜单栏上的“工具”。
加载规划求解后，“规划求解”命令会添加到“工具”菜单中。
三、怎样使用规划求解加载宏求解数学规划？规划求解加载宏是一组命令构成的一个子程序，这些命令有时也称作假设分析3工具，其功能是可以求出线性和非线性数学规划问题的最优解和最优值。
使用规划求解加载宏求解数学规划的步骤首先，在Excel工作表中输入目标函数的系数向量、约束条件的系数矩阵和右端常数项（每一个单元格输入一个数据）；其次，选定一个单元格存储目标函数（称为目标单元格），用定义公式的方式在这个目标单元格内定义目标函数；再次，选定与决策变量个数相同的单元格（称为可变单元格），用以存储决策变量；再选择与约束条件个数相同的单元格，用定义公式的方式在每一个单元格内定义一个约束函数（称为约束函数单元格）；最后，点击下拉列菜单中的规划求解按钮，打开规划求解参数设定对话框（如图4所示），完成规划模型的设定模型设定方法如下：（1）设定目标函数和优化方向：光标指向规划求解参数设定对话框中的“设置目标单元格”提示后的域，点击鼠标左键，然后选中Excel工作表中的目标单元格。
Excel规划求解使用教程(图)
附录4 Excel“规划求解”1. 在系统中安装“规划求解”1、启动EXCEL。
打开“工具”菜单。
如果没有“规划求解”，单击“加载宏”。
弹出以下窗口：2、在复选框中选中“规划求解”，单击“确定”后返回Excel。
这时在“工具”菜单中出现“规划求解”。
关闭“工具”菜单2. 在Excel中创建线性规划模型1、输入线性规划模型的约束条件系数、右边常数和目标函数系数。
定义线性规划的变量单元格、约束条件左边单元格和目标函数单元格。
2、定义“设备能力占用”（即约束条件左边）以及“总利润”的计算公式。
首先定义设备A的“能力占用”单元格（G3）的计算公式，界面如下：其次定义设备B的“能力占用”单元格（G4）的计算公式，界面如下：再次定义设备C的“能力占用”单元格（G5）的计算公式，界面如下：最后定义“总利润”单元格（C8）的计算公式，界面如下：3、将光标停留在“总利润”值的单元格（C8）中，打开“工具/规划求解”，弹出以下窗口：4、设置目标函数单元格：检查“设置目标函数单元格”是否在“$C$8”，如不是，单击文本框右侧的图标，重新选定目标函数单元格，也可以直接单击Excel表中的“C8”。
5、设置变量：单击“规划求解窗口”中“可变单元格”文本框，然后在Excel工作表中选定变量单元格（C7、D7、E7和F7），在文本框中出现“$C$7:$F$7”，如下图所示。
6、设置约束单击“添加”，弹出以下窗口：单击“单元格引用位置”文本框空白处，然后单击工作表G3单元格，“单元格引用位置”文本框中出现“$G$3”；打开“单元格引用位置”和“约束值”之间的下拉文本框，选定“<=”；单击“约束值”文本框空白处，然后单击工作表H3单元格。
结果如下图所示。
单击“添加”，完成第一个约束设置。
继续设置第二、第三个约束，最后设置所有变量非负。
约束设置完成以后，单击“确定”，返回“规划求解参数”窗口，如下图所示。
7、设置叠代参数。
单击“选项”，弹出以下窗口：输入“最长运行时间”、“叠代次数”、“精度”、“允许误差”、“收敛度”等叠代参数。
巧用Excel解线性方程组
收稿日期:2005-05-11作者简介:刘铸飘(1977～),男,助理工程师,学士,主要从事天气预报、计算机管理等工作。
文章编号:1007-6190(2005)03-0042-02巧用Excel 解线性方程组刘铸飘(始兴县气象局,广东始兴 512500) 摘 要:介绍一种较为简单明了的用Excel 规划求解功能来解线性方程组的方法。
关键词:规划求解;线性方程组;计算精度中图分类号:TP391.13 文献标识码:B 线性方程组在工程技术、经济等领域有着广泛的应用。
许多实际问题可以归结为一个线性方程组的解,因此线性方程组的解法已成为广大工程技术人员、经济工作者等必须掌握的知识。
在线性代数领域,Excel 可以通过插入函数来直接求行列式的值、逆矩阵和矩阵的乘积。
但Ex 2cel 并没有直接求解线性方程组的功能,本文试用Excel 的“规划求解”功能来解线性方程组,现把这种方法介绍如下。
方法介绍首先,必须先安装Excel 的“规划求解”加载宏,因为在安装Micros oft O ffice 时,一般选择“典型”安装,此时,Excel 工具下拉菜单中不带有“规划求解”选项,利用O ffice 安装盘中的“添加/删除”功能安装即可。
气象部门在做中、长期等天气趋势预报时,经常用到概率统计方法,当应用这些方法时,往往会碰到要求解计算较为繁琐的线性方程组。
例如,以下的线性方程组为始兴县气象局2004年前汛期降雨量作平稳时间序列分析时“五点”预报公式的系数求解方程组: 31081.14x 1+(-3656.751)x 2+(-538.293)x 3+8042.577x 4+(-6695.649)x 5=-2515.871(-3656.751)x 1+31081.14x 2+(-3656.751)x 3+(-538.293)x 4+8042.557x 5=-6695.649(-538.293)x 1+(-3656.751)x 2+31081.14x 3+(-3656.751)x 4+(-538.293)x 5=8042.5578042.557x 1+(-538.293)x 2+(-3656.751)x 3+31081.14x 4+(-3656.751)x 5=-538.293(-6695.649)x 1+8042.557x 2+(-538.293)x 3+(-3656.751)x 4+31081.14x 5=-3656.751 可按如下的步骤来解这个方程组。
用Excel电子表格解方程组的方法
用Excel电子表格解方程组的方法下面给你介绍用Excel电子表格解方程组的方法。
现有以下方程组：如何用Excel 电子表格解这个方程组呢？在Excel 电子表格计算中，解决复杂的方程求值和各类线性和非线性有约束优化问题时，都要用到“规划求解”这个加载工具。
解以上方程组也不例外。
“规划求解”为解决这些问题建立了数学模型，Excel 还为各类问题建立了数学模型。
计算机计算速度非常快，达到每秒钟数百万次以上，只要我们在数学模型上填上有关数据，填完数据，计算就完毕，就能得出答案。
Excel电子表格功能区通常没有”规划求解”选项卡，所以需要我们先人工加载，并且要在加载“宏”的情况下才能成功加载“规划求解”。
一．加载“规划求解”的步骤是：在Excel 电子表格单击左上角圆形状office按钮，在打开的对话框中单击右下方的“Excel选项”按钮，在弹出的对话框单击左侧任务窗格的“加载项”按钮，在弹出的对话框左下方单击“转到”，在弹出的对话框单击“规划求解加载项”，单击“确定”。
如果弹出“此项还不可用。
要加载宏才可用，是否现在就加载宏?"的提问框，单击”是”，开始加载宏，加载宏完成后，再次重复以上步骤，在弹出的“加载宏”对话框中单击“规划求解加载项”，单击“确定”。
见图-1：图-1加载完成后关机重启，使生效。
开机后单击Excel电子表格功能区的“数据”选项卡，在功能区就能看到增加了“分析”组，里面有“规划求解”按钮。
(已用红笔圈出)。
见图-2：图-2二．解以上方程组的步骤:（注意操作出现差错时，可按屏幕左上角的“撤消”按钮撤消错误，或按右侧的Delete键删除错误。
）1.打开一个空白Excel 电子表格。
2.由于本方程组有3个未知数，即x, y, z ,所以要预留A1,A2,A3这3 个可变单元格，分别代表x, y, z .这时表格中的A1,A2,A3单元格为空。
3. B1,B2,B3 为活动单元格，分别代表以上方程组各方程等号左边，操作方法是：（1）在B1单元格输入=A1+2*A2-4*A3 (注，*为乘号，注意不要漏掉等号= 和乘号*，否则出错。
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。
用EXCLE求解线性规划问题
4.在约束条件左端项系数存放单元格右边的单元格中输入约束 条件左端项的计算公式，计算出约束条件左端项对应于目前决 策变量的函数值。 5.在步骤4的的数据右边输入约束条件中右端项（即常数项）
6.确定目标函数值存放单元格，并在该单元格中输入目标函数 值电容计算公式。
例.求下列线性规划问题
SUMPRODUCT(B3:C3,B10:C10)
例.求解如下的线性规划问题 某企业的产品生产数据如下
分共厂 门 生产时间 窗 0 2小时 2小时 500 4小时 12小时 18小时 每周可利用时间
s.t
第一步：选择决策变量单元格 决策变量的一般初始值赋0。
第二步：目标单元格，用函数公式表示。
用EXCLE求解线性规划问题
1.“线性规划求解”的安装(文件
选项)
加载项 规划求解加载项 选择在数据加载项
转到
加载数据规划求解选项以后,在“数据”菜单中就 会出现“规划求解”
线性规划求解的步骤：
1.确定目标函数系数存放单元格，并在这些单元格中输入目标 函数系数。 2.确定决策变量存放单元格，并任意输入一组数据； 3.确定约束条件中左端项系数存放单元格，并输入约束条件左 端项系数；
G 11 Total Proft 12 =sumproduct（C4:D4，C12:D12）
第三步：约束条件左边项用函数表示
5 6 7 8 9
E Hours Used SUMPRODUCT(C7:D7,$C$12:$D$12) SUMPRODUCT(C8:D8,$C$12:$D$12) SUMPRODUCT(C9:D9,$C$12:$D$12)
第四步：激活规划求解，确定可变单元格和目标 单元格
第五步：增加约束 条件
利用excel软件求解线性规划问题讲解
下面我们通过一个例子来解释怎样用“规划求解”来求解数学规划问题。
例1 公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。
具体来说就是，产品组合问题就是要确定公司每月应该生产的每种产品的数量以使利润最大化。
产品组合通常必须满足以下约束：● 产品组合使用的资源不能超标。
● 对每种产品的需求都是有限的。
我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。
下面，我们来考虑让某医药公司的最优产品组合问题。
该公司有六种可以生产的药品，相关数据如下表所示。
设该公司生产药品1～6的产量分别为126,,,x x x （磅），则最优产品组合的线性规划模型为123456123456123456123456max 6 5.3 5.4 4.2 3.8 1.86543 2.5 1.545003.2 2.6 1.50.80.70.316009609281041..977108410550,16j z x x x x x x x x x x x x x x x x x x x x x s t x x x x j =++++++++++≤⎧⎪+++++≤⎪⎪≤⎪≤⎪⎪≤⎨⎪≤⎪≤⎪⎪≤⎪⎪≥≤≤⎩下面用规划求解加载宏来求解这个问题： 首先，如下如所示，在Excel 工作表内输入目标函数的系数、约束方程的系数、右端常数项；其次，选定目标函数单元、可变单元、约束函数单元，定义目标函数、约束函数其中，劳动力约束函数的定义公式是“=MMULT(B3:G3, J5:J10)”，原料约束函数的定义公式是“＝MMULT(B4:G4,J5:J10)”，目标函数的定义公式是“MMULT(B5:G5, J5:J10)”。
注：函数MMULT(B3:G3, J5:J10)的意义是：单元区B3:G3表示的行向量与单元区J5:J10表示的列向量的内积。
这一要特别注意的是，第一格单元区必须是行，第二格单元区必须是列，并且两个单元区所含的单元格个数必须相等。
Excel 2016数据处理与分析 5 规划求解
5.3 求解任务分配
1. 问题描述 某医院新建一个病房，需要配备护士，周一到周日分别最少需
要16、12、12、15、14、16、14人，按规定一个护士一周要连续 上班5天，医院病房给配备了20名护士，是否够用？
单击“文件”选项卡下的“选项”，在弹出的 “Excel选项”对话框中单击左侧列表中的“加载项” 选项卡，然后在右下方“管理”组合框中选择 “Excel加载项”，并单击“转到”按钮，如图所示。
5.1 规划求解基础
在弹出的“加载宏”的对话框中勾选“规划求解加载项”复选 框，并单击“确定”按钮完成操作，如图所示。在Excel主界面点击 “数据”选项卡，在“分析”选项组中，显示“规划求解”，如图 所示。
5.1 规划求解基础
规划求解的特点： 1. 有多个可以调整的单元格。 2. 可以通过更改其他单元格来确定某个单元格的最大值或最小值。 3. 可以指定可调整单元格可能的数值约束。 4. 一个问题可以有多个解。
5.1 规划求解基础
1. 规划求解问题的特点 规划求解是一组命令的组成部分，这些命令有时也称作假设分析
ቤተ መጻሕፍቲ ባይዱ
5.3 求解任务分配
6. 显示如图所示，单击“确定”按钮。显示如图所示，给出求解结 果。
再见！
5.2 求解取料问题
4. B12单元格实际丁原料的使用量，输入公式： “B12=SUM(D5:E5)”。
5. B13单元格为总成本，设定为目标单元格输入公式： “B13= SUMPRODUCT(C2:C5,D2:D5+E2:E5)”
5.2 求解取料问题
6.选中B13单元格，打开“规划求解参数”对 话框，在“设置目标单元格”文本框中选择 B13单元格，选中“最小值”单选按钮，在可 变单元格文本框中选择D2:E5单元格区域。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
如何利用Excel2016 软件的规划求解功能求解线性方程组
将线性方程组视为线性规划的特殊情形。
以方程组中一个方程作为目标函数；将方程组中各个方程作为约束条件；方程组中各个变量作为决策变量。
按Excel 软件的规划求解方法求解。
以4 元线性方程组为例：
操作步骤如下:
1．在A2：A7 单元格分别输入“方程1”、“方程2”、“方程3”、“方程4”、“可变单元”、
“目标函数”；在B1：E1、G1、I1 单元格分别输入“系数1”、“系数2”、“系数3”、“系数4”、“约束表达式”、“常数”。
2．在B2：E5 区域中输入方程组各方程的系数，在I2：I5 单元格输入方程组各方程的常数。
3．将B6：E6 的4 个单元格设为决策变量单元。
4．在G2：G5 单元格设置约束表达式，在G2 中输入“=B2*B$6+C2*C$6+D2*D$6+E2*E$6”，选定G2 单元格，利用填充句柄向下填充至G5 单元格。
5．在B7：E7 单元格依次输入方程1 的各个系数。
6．在G7 单元格设置目标函数，输入“=B7*B$6+C7*C$6+D7*D$6+E7*E$6”,如下图所示:
求解实施步骤如下。
1.对于Excel 2016,点击“开发工具”功能,点击其中的“Excel加载项”功能,找到“规划求解加载
项”,在前边打对勾,点击“确定”。
点击后，找到“数据”功能,可以看到新增了“规划求解”功能。
在“设置目标单元格”栏中输入表示目标函数值的单元格地址$G$7（也可直接单击G7
单元格），并在“等于”一栏中选择“值为”单选项，并在其右的文本栏中输入8。
在“可变单元格”一栏中输入决策变量的单元格地址“$B$6:$E$6”。
在“约束”中，通过“添加”按钮，在弹出的“添加约束”对话框中添加约束条件：在
“单元格引用位置”输入表示“约束表达式”的单元格地址“$G$2:$G$5”，将其右的关系运
算符选为“=”，在“约束值”栏中输入表示“常数”单元格地址“$I$2:$I$5”，单击“确定”。
不选中”使无约束变量为非负数”选项。
在求解方法中,选择单纯线性规划点击”求解”选项。
2对于在Excel 2010以下版,在“数据”选项卡菜单中，选择“规划求解”选项，打开“规划求解参数”对话框，设置目标单元格、可变单元格和约束条件,设置过程与Excel 2016基本一致。
3．在弹出的“规划求解结果”对话框中，选中“保存规划求解结果”单选项，单击“确
定”按钮，即可求得该线性方程组的解,如下图所示为Excel 2016结果:
如下图所示为Excel 2007操作过程及结果:
如果是Excel2010以下版本:在“数据”选项卡菜单中，选择“规划求解”选项，打开“规划求解参数”对话框，设置目标单元格、可变单元格和约束条件：
如果是Excel 2010以下版本:则需要单击“选项”按钮，在弹出的“规划求解选项”对话框中，选定“采用线性模型”复选项，其他参数不变，单击“确定”按钮返回“规划求解参数”对话框。