如何使用Excel2010规划求解功能进行数据分析

Excel技巧 2021-06-06 17:33:54

在使用Excel2010规划求解功能进行数据分析前，需要创建规划求解模型，从数学角度分析规划问题，这就涉及决策变量、约束条件和目标函数。决策变量是指一组需要求解的未知数;约束条件是指对规划问题的决策变量有一定限制的条件，通常由与决策变量相关的不等式或等式表示;目标函数由与决策变量相关的函数表示。

　　1、创建规划求解基本模型

　　新建一个工作表，创建月销售成本最小化规划分析的基本模型，选择E4单元格，输入公式“=D4*B4”，按【Enter】键后向下复制公式至E6，如下图所示。

使用Excel2010规划求解功能进行数据分析

图1

　　2、计算实际月销售利润

　　在B13单元格中输入公式“=SUMPRODUCT(C4:C6，D4:D6)”，按【Enter】键计算出实际月销售利润。

excel计算实际月销售利润

图2

　　3、计算每月最低生产成本

　　选择B14单元格，输入公式“=SUM(E4:E6)”，按【Enter】键计算出每月最低生产成本，如下图所示。

excel计算每月最低生产成本

图3

　　4、单击“规划求解”按钮

　　在“数据”选项卡下单击“规划求解”按钮，如下图所示。

选择规划求解功能

图4

　　5、单击“添加”按钮

　　弹出“规划求解参数”对话框，设置目标单元格为B14，选中“最小值”单选按钮，设置“通过更改可变单元格”区域为D4:D6，最后单击“添加”按钮，添加约束条件，如下图所示。

excel2010规划求解参数对话框

图5

　　6、设置约束条件1

　　弹出“添加约束”对话框，设置第一个约束条件为A商品销量为整数，故设置单元格引用为D4单元格，然后从中间的下拉列表框中选择int选项，设置约束条件为“整数”，单击“添加”按钮继续添加约束条件，如下图所示。

规划求解约束条件1

图6

　　7、设置约束条件2

　　第二个约束条件是A商品销量要大于等于其最低月销量，故在弹出的“添加约束”对话框中设置D4单元格大于等于B9单元格，单击“添加”按钮继续添加约束条件，如下图所示。

规划求解约束条件2

图7

　　8、设置约束条件3

　　第三个约束条件为要求B商品产量为整数，故在弹出的“添加约束”对话框中，设置D5单元格为int整数，单击“添加”按钮，如下图所示。

规划求解约束条件3

图8

　　9、设置约束条件4

　　第四个约束条件为要求B商品数量要大于等于其最低销量，故在弹出的“添加约束”对话框中设置D5单元格大于等于B10单元格，单击“添加”按钮，如下图所示。

规划求解约束条件4

图9

　　10、设置约束条件5

　　第五个约束条件为要求C商品产量为整数，故在弹出的“添加约束”对话框中设置D6单元格为int整数，单击“添加”按钮，如下图所示。

规划求解约束条件5

图10

　　11、设置约束条件6

　　第六个约束条件为要求C商品数量要大于等于其最低销量，故在弹出的“添加约束”对话框中设置D6单元格大于等于B11单元格，单击“添加”按钮，如下图所示。

规划求解约束条件6

图11

　　12、设置约束条件7

　　第七个约束条件为要求实际销售利润要大于等于目标销售利润6000元，故在弹出的“添加约束”对话框中设置B13单元格大于等于B8单元格，单击“确定”按钮完成约束条件的设置，如下图所示。

规划求解约束条件7

图12

　　13、求解规划结果

　　返回“规划求解参数”对话框，在“遵守约束”列表框中可看到所有的约束条件，确定约束都设置完毕后，单击“求解”按钮，如下图所示。

excel求解规划结果

图13

　　14、保留规划求解的解

　　弹出“规划求解结果”对话框，提示用户找到一解，能满足所有的约束及最优状况，单击“确定”按钮即可，如下图所示。

保留规划求解的解

图14

　　15、查看月销售成本最小化的结果

　　返回工作表，此时可以看到当A商品、B商品、C商品的销量分别是71、50、40时，每月销售成本最低，且最低成本额为20650元，如下图所示。

excel规划求解数据分析结果

图15

　　我们通过一个实例为大家讲述了excel2010规划求解的整个部分，现在想想前面用excel制作表格是不是都是非常基础的教程啊，excel的强大之处就是数据分析了，利用数据分析可以做预算，统计分析等等，所以学习excel是非常重要的。

标签： excel excel2010

上一篇：Excel中身份证提取生日的方法图解教程下一篇：如何在excel2010中创建数据透视表

如何使用Excel2010规划求解功能进行数据分析

热门文章

最新文章

标签列表