Excel 的数据分析

1、1.1.模拟运算表（数据表）模拟运算表（数据表）2.2.单变量求解单变量求解3.3.规划求解与分析工具库规划求解与分析工具库4.4.方案方案分析分析模拟运算模拟运算表表是一个单元格区域，是一个单元格区域，显示公式显示公式中的某些参数值的变中的某些参数值的变化将如何影响公式化将如何影响公式结果。结果。模拟运算表有两种模拟运算表有两种，单变量模拟单变量模拟运算表运算表和和双双变量模拟运算表变量模拟运算表。单变量模拟运算表查看一个变量的不同值单变量模拟运算表查看一个变量的不同值对一个或多个对一个或多个公式公式结果的影响。结果的影响。输入值需排在一行或一列。输入值需排在一行或一列。可变单元格：公式中输

2、入值要替换的单元格。可变单元格：公式中输入值要替换的单元格。模拟运算模拟运算表中，输入表中，输入值要排在一行或一值要排在一行或一列。如果输入值排在同列。如果输入值排在同一列，则在一列，则在“输入引用列的单元格输入引用列的单元格”框中，键入可变单元格。框中，键入可变单元格。 如果输入值排在同一行上，则在如果输入值排在同一行上，则在“输入引用行的单元格输入引用行的单元格”框中，框中，键入可变单元格。键入可变单元格。当输入值排成一列时，在第一个数值的上一行且处于数值列右当输入值排成一列时，在第一个数值的上一行且处于数值列右侧的单元格中键入所需公式。（可以在公式右边的单元格中键侧的单元格中键入所需公式

3、。（可以在公式右边的单元格中键入其它公式）入其它公式）当输入值排成一行时，在第一个数值左边一列且数值行下方的当输入值排成一行时，在第一个数值左边一列且数值行下方的单元格内键入所需公式。（可以为一个以上的公式）单元格内键入所需公式。（可以为一个以上的公式）模拟运算模拟运算表的计算结果存放在数组中，不能单独修改其表的计算结果存放在数组中，不能单独修改其中的某一个值。要清除计算结果，选中中的某一个值。要清除计算结果，选中模拟运算模拟运算表中所表中所有结果，按有结果，按Delete键即可。键即可。例：计算商场出售商品的获利情况。公式为毛利例：计算商场出售商品的获利情况。公式为毛利=进货进货成本成本*加

4、价百分比加价百分比*销售数量销售数量-销售费用销售费用参数写在同一列参数写在同一列A10:A14A10:A14上，因此选择上，因此选择B9B9单元格输入单元格输入公式：公式：=B1=B1* *B2B2* *B3-B4B3-B4；选择选择A9:B14A9:B14区域区域，在，在“模拟运算表模拟运算表”对话框中对话框中，选择选择 “输入引用列的单元格输入引用列的单元格”，单击，单击B2B2单元格，则产生结单元格，则产生结果，此结果区域为果，此结果区域为数组；数组；“考勤应扣款计算表考勤应扣款计算表”中用模拟运算表计算每个部门的基中用模拟运算表计算每个部门的基本工资、扣款合计的总额。本工资、扣款合计

5、的总额。设设A19单元格为可变单元格，输入任何一个部门的值；单元格为可变单元格，输入任何一个部门的值；所有参数值写在所有参数值写在C20:C23区域，在区域，在D19、E19单元格输单元格输入公式：入公式：SUMIF(C3:C16,A19,E3:E16)和和SUMIF(C3:C16,A19,L3:L16)选择选择C19:E23区域，在区域，在“模拟运算表模拟运算表”对话框的对话框的“输入输入引用列的单元格引用列的单元格”中输入中输入“A19”，单击，单击“确定确定”按钮；按钮；创建双变量模拟运算表的创建双变量模拟运算表的过程与过程与创建单变量模拟运算表创建单变量模拟运算表类似类似。双变量中的两

6、组输入值使用双变量中的两组输入值使用同一个同一个公式公式，一组输入值在，一组输入值在公式下方的同一列上，另外一组输入值在公式右侧的同公式下方的同一列上，另外一组输入值在公式右侧的同一行上。一行上。 在某个单元格内输入所需的引用两个输入单元格的公式。在在某个单元格内输入所需的引用两个输入单元格的公式。在公公式下面同一列式下面同一列中键入一组输入值，在中键入一组输入值，在公式右边公式右边同一行中键入第同一行中键入第二组输入数值二组输入数值； 选定公式及输入行和列的单元格区域；选定公式及输入行和列的单元格区域；有人要申请等额本息贷款购买房子，其中利率固定，考虑几种有人要申请等额本息贷款购买房子，其中

7、利率固定，考虑几种可能的贷款年限和贷款可能的贷款年限和贷款金额下求每月金额下求每月还贷额，选择最佳方案。还贷额，选择最佳方案。B5B5单元格先输入公式，然后在单元格先输入公式，然后在B6B6：B10B10区域和区域和C5:G5C5:G5区域输入输入值；区域输入输入值；选择选择B5:G10B5:G10区域区域，在，在“模拟运算表模拟运算表”对话框中对话框中“引用引用行的单元格行的单元格”里选择里选择F2F2，“引用列的单元格引用列的单元格”里选择里选择C3C3；在在“考勤应扣款计算表考勤应扣款计算表”中用模拟运算表计算不同部门、中用模拟运算表计算不同部门、不同职位的人数。不同职位的人数。在在A1

8、9:B20区域，创建条件区域；区域，创建条件区域；在在D20:D23区域输入部门的所有参数值，在区域输入部门的所有参数值，在E19:H19区域输入职位的所有参数值；区域输入职位的所有参数值；在在D19单元格输入公式：单元格输入公式：=DCOUNT(A2:L16, “基本工资基本工资,A19:B20);选择选择D19:H23区域，在区域，在“模拟运算表模拟运算表”对话框的对话框的“输入输入引用列的单元格引用列的单元格”中输入中输入 “B20”，“输入引用行的单输入引用行的单元格元格”中输入中输入“A20”，单击，单击“确定确定”按钮；按钮；单变量单变量求解是在已知求解是在已知结果结果的情况下推测

9、出形成这个结果的情况下推测出形成这个结果的参的参数。在数。在Excel中根据所提供的目标值，不断改变公式中涉及中根据所提供的目标值，不断改变公式中涉及的引用单元格的输入值，直到达到所需要求的公式的目标值。的引用单元格的输入值，直到达到所需要求的公式的目标值。公式所在的单元格为目标单元格公式所在的单元格为目标单元格，在求解过程中可修改其值，在求解过程中可修改其值的单元格为的单元格为可变单元格可变单元格，“单变量求解单变量求解”中中可变单元格为一可变单元格为一个个。 选择目标单元格，选择目标单元格，“数据数据”|“数据工具数据工具”|“假设分假设分析析”|“单变量求解单变量求解”，在，在“单变量求

10、解单变量求解”对话框中输入目对话框中输入目标单元格、目标值以及可变单元格；标单元格、目标值以及可变单元格；某人准备向银行贷款，贷款期限为某人准备向银行贷款，贷款期限为30年，贷款利率为年，贷款利率为6%，每月，每月能承受的还款额为能承受的还款额为5000元，那么能贷多少。元，那么能贷多少。选择选择B5B5单元格，输入公式：单元格，输入公式：=PMT(B4/12,B3,B2)=PMT(B4/12,B3,B2)；“数据数据”| | “数据工具数据工具”|“|“假设分析假设分析”|“|“单变量求解单变量求解”，目标单元格中选择目标单元格中选择B5B5，目标值目标值-5000-5000，可变单元格选择

11、，可变单元格选择B2B2；例：例：猴子摘了很多猴子摘了很多桃桃，第一天吃了一半再多吃一个，第二天又，第一天吃了一半再多吃一个，第二天又吃了一半再多吃了一个，如此下去，到第十天恰好剩吃了一半再多吃了一个，如此下去，到第十天恰好剩了一个桃了一个桃，问猴子一共摘了多少桃子？问猴子一共摘了多少桃子？设原来的桃个数放在设原来的桃个数放在B15B15单元格里，在单元格里，在B16B16单元格里输入单元格里输入公式：公式：=B15-B15/2-1,=B15-B15/2-1,拖动填充柄到拖动填充柄到B25B25；“单变量求解单变量求解”对话框中，对话框中，选择目标单元格为选择目标单元格为B25B25，目标，目

12、标值为值为1 1，可变单元格为，可变单元格为B15B15； “规划求解规划求解”与与“分析工具库分析工具库”是是Excel提供的两组数据分析加提供的两组数据分析加载宏。载宏。加载宏的安装：加载宏的安装：单击单击“文件文件”按钮，选择按钮，选择“选项选项”；选择选择“加载宏加载宏”类别，选择类别，选择“Excel加载宏加载宏”，单击，单击“转到转到”；在在“加载宏加载宏”对话框中选中对话框中选中“分析工具库分析工具库”和和“规划求解加载规划求解加载项项”；规划求解可求规划求解可求多个变量多个变量在一定在一定约束条件约束条件下的下的最佳解最佳解。“数据数据”|“分析分析”|“规划求解规划求解”规划

13、求解的规划求解的参数：参数：目标单元格，可选目标单元格，可选最大值最大值、最小值最小值或或某一数值某一数值；可变单元格，用逗号分隔不相邻单元格的引用；可变单元格，用逗号分隔不相邻单元格的引用；约束条件；约束条件；采用线性模型：如果求解模型是线性模型，需选采用线性模型：如果求解模型是线性模型，需选“单纯线性规单纯线性规划划”复选框，规划求解能找到求解模型的最优解。如果一个求复选框，规划求解能找到求解模型的最优解。如果一个求解模型是非线性的，并不解模型是非线性的，并不一定都能得到满意的结果，它受初值一定都能得到满意的结果，它受初值以及规划求解选项（如迭代次数、迭代时间等）的影响，在求以及规划求解选

14、项（如迭代次数、迭代时间等）的影响，在求解过程中按解过程中按EscEsc键可中止求解过程键可中止求解过程。若有如下约束条件，若有如下约束条件，y-x=1y-x=1; ; 3x+y3x+y=0 0；y y=0 0，求，求满足满足上述四个条件的上述四个条件的x x、y y使得使得x+yx+y取最大值。取最大值。在在A4A4单元格里输入公式：单元格里输入公式：=A3-A2=A3-A2，在在A5A5单元格里输单元格里输入公式：入公式：=3=3* *A2+A3A2+A3；在在B7B7单元格里输入公式：单元格里输入公式：=A2+A3=A2+A3；在在 “规划求解参数规划求解参数”对话框中如对话框中如图图设

15、置设置。设某食品公司生产设某食品公司生产A、B两种产品，需要甲乙丙丁两种产品，需要甲乙丙丁4种原种原料。每种产品所需的原料和产品的利润值以及每种原料料。每种产品所需的原料和产品的利润值以及每种原料的储备量具体数据如图所示：的储备量具体数据如图所示：格式：格式：SUMPRODUCT(array1, array2, array3, .)功能：在给定的几组数组中，将数组间对应的元素相乘，功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。并返回乘积之和。要求：数组参数必须具有相同的维数。要求：数组参数必须具有相同的维数。设设A A、B B两种产品分别生产两种产品分别生产x1x1、x2x

16、2吨，则按要求建立条件：吨，则按要求建立条件： 2x1+2x2=12 2x1+2x2=12 x1+2x2=8 x1+2x2=8 4x1+0 x2=16 4x1+0 x2=16 0 x1+4x2=12 0 x1+4x2=0,x2=0 x1=0,x2=0 求求2x1+3x22x1+3x2的最大值的最大值在在B5B5单元格里输入公式：单元格里输入公式：=SUMPRODUCT(B2:B3,$G$2:$G$3)=SUMPRODUCT(B2:B3,$G$2:$G$3)，并把公式填充，并把公式填充到到C5:E5C5:E5单元格；单元格；在在B6B6单元格里输入公式：单元格里输入公式：=SUMPRODUCT(

17、F2:F3,G2:G3)=SUMPRODUCT(F2:F3,G2:G3)在在“规划求解参数规划求解参数”对话框设置参数，参考图片对话框设置参数，参考图片，“选项选项”中选择中选择“采用线性模型采用线性模型”和和“假定非负假定非负”；在在“Excel选项选项”中，选中，选“加载项加载项”，单击，单击“转到转到”，在打开的在打开的“加载宏加载宏”对话框中选择对话框中选择“分析工具库分析工具库”。方案方案就是保存在工作表中并可进行自动替换的一组值，可以比就是保存在工作表中并可进行自动替换的一组值，可以比较一组变量的不同的取值对结果的影响，也可以建立报表对这较一组变量的不同的取值对结果的影响，也可以建

18、立报表对这些方案进行对比或汇总。些方案进行对比或汇总。“数据数据”|“数据工具数据工具”|“假设分析假设分析”|“方案管理器方案管理器”创建、显示、编辑、删除方案创建、显示、编辑、删除方案合并方案：可以把其它工作表的所有方案都复制到当前工作表合并方案：可以把其它工作表的所有方案都复制到当前工作表中。中。生成方案总结生成方案总结生成方案摘要生成方案摘要方案数据透视表方案数据透视表保护方案保护方案在方案管理器中，在方案管理器中，选定方案名称，选定方案名称，“编辑编辑”里设为里设为“防止更防止更改改”和和“隐藏隐藏”。当。当工作表受保护时能添加方案，但不能编工作表受保护时能添加方案，但不能编辑或删除