截图
简介
这是excel数据分析ppt，包括了模拟运算表（数据表），单变量求解，规划求解与分析工具库，方案分析，行引用和列引用，单变量模拟运算表操作步骤等内容，欢迎点击下载。
excel数据分析ppt是由红软PPT免费下载网推荐的一款课件PPT类型的PowerPoint.
Excel 的数据分析 模拟运算表（数据表） 单变量求解 规划求解与分析工具库 方案分析 §1. 模拟运算表 模拟运算表是一个单元格区域，显示公式中的某些参数值的变化将如何影响公式结果。 模拟运算表有两种，单变量模拟运算表和双变量模拟运算表。 (1) 单变量模拟运算表 单变量模拟运算表查看一个变量的不同值对一个或多个公式结果的影响。 输入值需排在一行或一列。 可变单元格：公式中输入值要替换的单元格。 行引用和列引用 模拟运算表中，输入值要排在一行或一列。如果输入值排在同一列，则在“输入引用列的单元格”框中，键入可变单元格。 如果输入值排在同一行上，则在“输入引用行的单元格”框中，键入可变单元格。 “单变量模拟运算表”中公式位置 当输入值排成一列时，在第一个数值的上一行且处于数值列右侧的单元格中键入所需公式。（可以在公式右边的单元格中键入其它公式） 当输入值排成一行时，在第一个数值左边一列且数值行下方的单元格内键入所需公式。（可以为一个以上的公式） 模拟运算表计算结果 模拟运算表的计算结果存放在数组中，不能单独修改其中的某一个值。要清除计算结果，选中模拟运算表中所有结果，按Delete键即可。 单变量模拟运算表操作步骤： 根据输入值的方向，选择公式的所在位置，输入公式； “数据”|“数据工具”|“假设分析”|“模拟运算表”，在“模拟运算表”对话框中键入引用的单元格； 实例1：单变量模拟运算表 例：计算商场出售商品的获利情况。公式为毛利=进货成本*加价百分比*销售数量-销售费用 操作步骤： 参数写在同一列A10:A14上，因此选择B9单元格输入公式：=B1*B2*B3-B4； 选择A9:B14区域，在“模拟运算表”对话框中，选择 “输入引用列的单元格”，单击B2单元格，则产生结果，此结果区域为数组； 实例2：单变量模拟运算表 “考勤应扣款计算表”中用模拟运算表计算每个部门的基本工资、扣款合计的总额。 操作步骤： 设A19单元格为可变单元格，输入任何一个部门的值； 所有参数值写在C20:C23区域，在D19、E19单元格输入公式：SUMIF(C3:C16，A19，E3:E16)和SUMIF(C3:C16，A19，L3:L16) 操作步骤： 选择C19:E23区域，在“模拟运算表”对话框的“输入引用列的单元格”中输入“A19”，单击“确定”按钮； (2) 双变量模拟运算表 创建双变量模拟运算表的过程与创建单变量模拟运算表类似。 双变量中的两组输入值使用同一个公式，一组输入值在公式下方的同一列上，另外一组输入值在公式右侧的同一行上。 操作步骤： 在某个单元格内输入所需的引用两个输入单元格的公式。在公式下面同一列中键入一组输入值，在公式右边同一行中键入第二组输入数值； 选定公式及输入行和列的单元格区域； “数据”|“数据工具”|“假设分析”|“模拟运算表”，输入行引用的单元格以及输入列引用的单元格； 实例3：双变量模拟运算表 有人要申请等额本息贷款购买房子，其中利率固定，考虑几种可能的贷款年限和贷款金额下求每月还贷额，选择最佳方案。 操作步骤： B5单元格先输入公式，然后在B6：B10区域和C5:G5区域输入输入值； 选择B5:G10区域，在“模拟运算表”对话框中“引用行的单元格”里选择F2，“引用列的单元格”里选择C3； 实例4：双变量模拟运算表 在“考勤应扣款计算表”中用模拟运算表计算不同部门、不同职位的人数。 操作步骤： 在A19:B20区域，创建条件区域； 在D20:D23区域输入部门的所有参数值，在E19:H19区域输入职位的所有参数值； 在D19单元格输入公式：=DCOUNT(A2:L16， “基本工资，A19:B20); 操作步骤： 选择D19:H23区域，在“模拟运算表”对话框的“输入引用列的单元格”中输入 “B20”，“输入引用行的单元格”中输入“A20”，单击“确定”按钮； §2. 单变量求解 单变量求解是在已知结果的情况下推测出形成这个结果的参数。在Excel中根据所提供的目标值，不断改变公式中涉及的引用单元格的输入值，直到达到所需要求的公式的目标值。 公式所在的单元格为目标单元格，在求解过程中可修改其值的单元格为可变单元格，“单变量求解”中可变单元格为一个。 操作步骤： 选择目标单元格，“数据”|“数据工具”|“假设分析”|“单变量求解”，在“单变量求解”对话框中输入目标单元格、目标值以及可变单元格； 实例5：单变量求解 某人准备向银行贷款，贷款期限为30年，贷款利率为6%，每月能承受的还款额为5000元，那么能贷多少。 操作步骤 选择B5单元格，输入公式：=PMT(B4/12，B3，B2)； “数据”| “数据工具”|“假设分析”|“单变量求解”，目标单元格中选择B5，目标值-5000，可变单元格选择B2； 实例6：单变量求解 例：猴子摘了很多桃，第一天吃了一半再多吃一个，第二天又吃了一半再多吃了一个，如此下去，到第十天恰好剩了一个桃，问猴子一共摘了多少桃子？ 操作步骤： 设原来的桃个数放在B15单元格里，在B16单元格里输入公式：=B15-B15/2-1，拖动填充柄到B25； “单变量求解”对话框中，选择目标单元格为B25，目标值为1，可变单元格为B15； §3. 规划求解 “规划求解”与“分析工具库”是Excel提供的两组数据分析加载宏。 加载宏的安装： 单击“文件”按钮，选择“选项”； 选择“加载宏”类别，选择“Excel加载宏”，单击“转到”； 在“加载宏”对话框中选中“分析工具库”和“规划求解加载项”； 规划求解 规划求解可求多个变量在一定约束条件下的最佳解。 “数据”|“分析”|“规划求解” 规划求解的参数： 目标单元格，可选最大值、最小值或某一数值； 可变单元格，用逗号分隔不相邻单元格的引用； 约束条件； 规划求解的选项 采用线性模型：如果求解模型是线性模型，需选“单纯线性规划”复选框，规划求解能找到求解模型的最优解。如果一个求解模型是非线性的，并不一定都能得到满意的结果，它受初值以及规划求解选项（如迭代次数、迭代时间等）的影响，在求解过程中按Esc键可中止求解过程。 实例7：规划求解 若有如下约束条件，y-x<=1; 3x+y<=4；x>=0；y>=0，求满足上述四个条件的x、y使得x+y取最大值。 操作步骤： 在A4单元格里输入公式：=A3-A2，在A5单元格里输入公式：=3*A2+A3； 在B7单元格里输入公式：=A2+A3； 在 “规划求解参数”对话框中如图设置。 实例8：规划求解 设某食品公司生产A、B两种产品，需要甲乙丙丁4种原料。每种产品所需的原料和产品的利润值以及每种原料的储备量具体数据如图所示： 补充：SUMPRODUCT函数 格式： SUMPRODUCT(array1， [array2]， [array3]， ...) 功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 要求：数组参数必须具有相同的维数。 步骤1：建立数学模型 设A、B两种产品分别生产x1、x2吨，则按要求建立条件： 2x1+2x2<=12 x1+2x2<=8 4x1+0x2<=16 0x1+4x2<=12 x1>=0，x2>=0 求2x1+3x2的最大值 步骤2：规划求解设置 在B5单元格里输入公式：=SUMPRODUCT(B2:B3，$G$2:$G$3)，并把公式填充到C5:E5单元格； 在B6单元格里输入公式：=SUMPRODUCT(F2:F3，G2:G3) 在“规划求解参数”对话框设置参数，参考图片，“选项”中选择“采用线性模型”和“假定非负”； 分析工具库 在“Excel选项”中，选“加载项”，单击“转到”，在打开的“加载宏”对话框中选择“分析工具库”。 §4. 方案分析 方案就是保存在工作表中并可进行自动替换的一组值，可以比较一组变量的不同的取值对结果的影响，也可以建立报表对这些方案进行对比或汇总。 “数据”|“数据工具”|“假设分析”|“方案管理器” 创建、显示、编辑、删除方案 合并方案：可以把其它工作表的所有方案都复制到当前工作表中。 方案分析 生成方案总结 生成方案摘要 方案数据透视表 保护方案 在方案管理器中，选定方案名称，“编辑”里设为“防止更改”和“隐藏”。当工作表受保护时能添加方案，但不能编辑或删除已有方案。 方案分析和模拟运算表相比具有的优点 模拟运算表最多可以考察一个或两个参数值的改变对公式结果的影响，而方案分析可以考察一组变量（可以是两个以上）对公式结果的影响，并可产生报表进行不同方案的比较。 思考题: 方案管理器中显示的是当前工作表的方案还是当前工作簿的方案？ 若对某一个方案设置保护，不允许别人修改，如何操作？
展开
