excel怎么做线性分析(实例讲解用Excel求解线性规划)

最近了解到Excel也能求解一些简单的线性规划，于是就去学习了一下，操作比较简单，适合电脑中没有安装MATLAB、Lingo、Python等编程求解规划的软件。所以想以实际的例子讲解Excel求解线性规划。

实例1

Excel求解线性规划的步骤

Step1：新建一个空白的excel。

Step2：点击数据，在Excel右上方看是否有规划求解工具箱。

如果没有该规划求解标识，则可以在搜索栏搜索“规划求解”，在弹出的加载项对话框中勾选规划求解加载项，点击确定即可出现规划求解标识。

Step3：在任意空白区域输入模型的目标函数和约束条件。

Step4：按照文字描述将模型转化为excel规划模型，没有的项，其系数为0。

Step5:将目标函数值处G19单元格的计算公式输入目标函数计算式。

Step6：将约束条件左半部分的计算公式输入对应的单元格。

例如K11单元格对应的是第一个约束条件，其计算公式如下

K12单元格对应的是第二个约束条件，其计算公式如下

以此类推，在相应的单元格输入相应的约束条件的计算公式。

K11单元格输入的计算公式为： =G11*G16+H11*G17+I11*G18 K12单元格输入的计算公式为： =G12*G16+H12*G17+I12*G18 K13单元格输入的计算公式为： =G13*G16+H13*G17+I13*G18 K14单元格输入的计算公式为： =G14*G16+H14*G17+I14*G18 K14单元格输入的计算公式为： =G15*G16+H15*G17+I15*G18

Step7：利用Excel规划求解工具求解，点击数据->右上角的规划求解，弹出以下对话框。

我们首先将目标函数计算值的单元格选入为“设置目标”，同时将x1,x2,x3的变量取值处的单元格选择为“通过更改可变单元格”。

其次，我们要添加我们的约束条件，点击添加，弹出以下对话框。

例如我们将第一个约束条件添加到规划求解工具箱中，操作如下：

选择不等式约束的左边计算公式和右边常数部分，并按照约束条件选择关系符号，点击添加，即可将约束条件添加成功。其余约束条件以此类推。

第一个约束条件

第二个约束条件

第三个约束条件

第四个约束条件

第五个约束条件

最后规划求解工具箱对话窗口如图所示，选择求解最小值：

选择对应的计算方法非线性GRE或者单纯线性规划的方法求解，选择方法之后点击求解按键。

出现以下窗口，点击确定

Step8:获得规划求解结果

Step9:MATLAB编程求解该线性规划，检验Excel求解结果与MATLAB求解结果是否一致。

程序

clc;clearall;closeall;c= [2;3;1];a= [1, 4,2;3,2,0];b= [8;6];[x,y]= linprog(c,-a,-b,[],[],zeros(3,1));fprintf(‘当x1=%.4f x2 = %.4f x3 = %.4f时，目标函数取得最小值z = %.4fn’,x(1),x(2),x(3),y);

运行结果

Optimalsolutionfound.当x1=2.0000×2=0.0000×3=3.0000时，目标函数取得最小值z=7.0000

实例2

Excel求解线性规划

求解结果

MATLAB编程验证

clc;clearall;A=[-4-32-13-8-4-14-110-205-160-160-420-260-2-12-54-285-22-80100000010000001000000100000010000001];b=[-55-2000-800432822];Aeq=[];beq=[];lb=[0;0;0;0;0;0];ub=[];c1=[302401309020060];[x1,goal1]=linprog(c1,A,b,Aeq,beq,lb,ub)

运行结果

x1=4.0000002.08781.68082.0000goal1=764.0695

实例3

MATLAB编程验证

clc;clearall;A=[];b=[];c1=-[91.4141.2895.73149.8935.3891.4];Aeq=[111111];beq=372263;lb=[507921996263288.3752173.8833880.110];ub=[51103.54inf3388.53inf34380.1112730.39];[x1,goal1]=linprog(c1,A,b,Aeq,beq,lb,ub);fprintf(‘福州市经济效益目标函数单目标规划求解：f1(x)=%.2fn此时的变量取值：X1=%.2fX2=%.2fX3=%.2fX4=%.2fX5=%.2fX6=%.2fn’,-goal1,x1(1),x1(2),x1(3),x1(4),x1(5),x1(6));

运行结果

Optimalsolutionfound.福州市经济效益目标函数单目标规划求解：f1(x)=47051187.61此时的变量取值：X1=50792.00X2=199626.00X3=3288.37X4=84676.52X5=33880.11X6=0.00