Excel数据分析与处理(下).ppt

《Excel数据分析与处理(下).ppt》由会员分享，可在线阅读，更多相关《Excel数据分析与处理(下).ppt（40页珍藏版）》请在三一办公上搜索。

1、EXCEL数据处理与分析,讲师：梁国清,提 纲,最佳EXCEL学习方法工作环境基本操作数据输入与导入：数据有效性数据整理与编辑：设置单元格、排序数据查询：筛选、条件格式常规计算分析：分类汇总、合并计算、数据透视表高级分析：模拟运算、单变量求解、规划求解、分析工具,第四章 数据查询,您知道如何找到你要的数据吗？主要介绍借助Excel中的自动筛选、高级筛选及条件格式等功能，方便地实现数据的自动查询，从而极大的提高工作效率。,第四章 数据查询,自动筛选注意事项：如果在选定区域启用自动筛选，筛选标志设在首行；如果选定某个单元格启用筛选，则标记设置在整个数据列表首行。同一个工作表中，只能对一个数据区域启

2、用自动筛选功能。包括空行或空列时，要选取整个数据列表，然后启用自动筛选功能；否则会遗漏数据。示例：对数据列表的局部启用自动筛选包含多重标题行的自动筛选,第四章 数据查询,自动筛选充分利用Excel自适应提供筛选条件自定义筛选可以进行更个性化的筛选用?代表单个字符用*代表多个字符筛选求和要用SUBTOTAL使用辅助列技巧示例：根据筛选结果动态汇总计算,第四章 数据查询,高级筛选需要在数据列表之外单独建立筛选条件参数：列表区域条件区域复制区域示例：了解高级筛选将筛选结果复制到其他工作表,第四章 数据查询,高级筛选1、条件关系设置2、使用公式自定义筛选条件示例：“关系与”条件的设置方法“关系或”条件

3、的设置方法 使用公式自定义筛选条件,与,或,或,或,第四章 数据查询,条件格式可以使用条件格式直观地注释数据以供分析和演示使用。若要在数据中轻松地查找例外和发现重要趋势，可以实施和管理多个条件格式规则，这些规则以渐变色、数据柱线和图标集的形式将可视性极强的格式应用到符合这些规则的数据。条件格式也很容易应用：只需单击几下鼠标，即可看到可用于分析的数据中的关系。示例：标识同一户口本人员记录 标识中标公司和中标金额,第五章 常规计算分析,您知道这些数值的含义吗？主要介绍有关使用分类汇总、合并计算、数据透视表等功能对数据进行计算分析的相关技巧,第五章 常规计算分析,分类汇总 对同类数据进行汇总计算。注

4、意：使用之前先对数据列表按类别排序。只能对连续区域使用”组合”命令，不能对多重选定区域使用组合命令。注意汇总数据显示位置的设置。,第五章 常规计算分析,分类汇总复制分类汇总结果：按要求分类汇总；利用定位条件选项，复制。再粘贴到目标区示例：自动生成分级显示,第五章 常规计算分析,分类汇总创造分类条件进行汇总：增加辅助列创造分类条件；再进行分类汇总。删除分类汇总：重进行分类汇总选择选项。示例：对系列分类汇总,第五章 常规计算分析,合并计算认识合并计算：注意：使用按类别合并的功能时，数据源列表必须包含行或列标题。处理方式不仅仅是SUM,还有其他选择(计数、平均、乘积),第五章 常规计算分析,合并计算

5、示例：认识合并计算分类项不相同的数据表合并计算利用合并计算进行数值型数据核对,第五章 常规计算分析,数据透视表概述快捷、强大的数据分析方法，可以解答您关于数据的种种问题。,第五章 常规计算分析,数据透视表检查源数据开始使用数据透视表之前，请查看一下您的 Excel 工作表，确保它已经准备妥当，可以用来创建报表。都应有列标题，创建数据透视表时，源数据中的每一列标题都会成为可在报表中使用的字段。,第五章 常规计算分析,数据透视表创建数据透视表,第五章 常规计算分析,数据透视表数据透视表的布局区域。数据透视表字段列表。,第五章 常规计算分析,数据透视表创建数据透视表,第五章 常规计算分析,数据透视表

6、创建数据透视表,第五章 常规计算分析,数据透视表示例：从数据透视表中查询数据获取数据透视表的数据源,第五章 常规计算分析,关于数据透视表 源数据第三季度高尔夫汇总的源值数据透视表C2 和 C8 中源值的汇总,第五章 常规计算分析,数据透视表 页字段数据字段行字段列字段,第六章 高级分析,高级分析Excel还拥有强大的数据运算分析工具，包括：模拟运算表、单变量求解工具、规划求解工具和数据分析工具等。轻松解决更多的数据处理分析方面的复杂问题。,第六章 高级分析,加载项默认情况下安装的Excel2007，不能使用规划求解工具、分析工具。需要在选项设置/加载项中进行选择。在数据选项卡中出现分析命令组后

7、即可使用。,第六章 高级分析,分析工具假设分析是在单元格中更改值以查看这些更改将如何影响工作表中公式结果的过程。可以在一个或多个公式中使用不同的几组值来分析所有不同的结果。例如，您可以执行假设分析以生成两个预算，其中每个预算都假定一个特定水平的收入。您还可以指定一个希望公式生成的结果，然后确定哪组值将生成该结果。Excel 附带了三种假设分析工具：方案、数据表和单变量求解。异同：方案和数据表可获取一组输入值并确定可能的结果。数据表仅可以处理一个或两个变量，但可以接受这些变量的众多不同的值。一个方案可具有多个变量，但它最多只能容纳 32 个值。单变量求解与方案和数据表的工作方式不同，它获取结果并

8、确定生成该结果的可能的输入值。,第六章 高级分析,分析工具假设分析该过程通过更改单元格中的值来查看这些更改对工作表中公式结果的影响。例如，更改分期支付表中的利率可以调整支付金额。模拟运算表(数据表)单变量求解工具示例：使用模拟运算表进行单变量预测分析解一元方程式使用模拟运算表同时测算多个项目,第六章 高级分析,分析工具（单变量求解）求解一元方程式：确定目标单元格确定可变单元格指定目标值得到结果：X=？示例：解一元方程式,第六章 高级分析,分析工具规划求解“规划求解”是一组命令的组成部分（有时也称作假设分析工具）。借助“规划求解”，可求得工作表上某个单元格的最优值。“规划求解”将对直接或间接与目

9、标单元格中的公式相关的一组单元格进行处理。“规划求解”将调整所指定的变动单元格来限制“规划求解”可在模型中使用的值，而且约束条件可以引用影响目标单元格公式的其他单元格。示例：计算住房贷款,第六章 高级分析,分析工具（规划求解）鸡兔同笼问题：今有雏兔同笼，上有三十五头，下有九十四足，问雏兔各几何？约束条件1：B3:B4=C3:C4约束条件2：B1:B2=整数使用规划求解解决鸡兔同笼问题,第六章 高级分析,分析工具描述统计：描述统计的任务是描述随机变量的统计规律性。示例：使用描述统计工具分析学生成绩使用描述统计工具优选小麦品种,第六章 高级分析,分析工具移动平均：移动平均就是对一系列变化的数据按照

10、指定的数据数量依次求取平均，并以此作为数据变化的趋势供分析人员参考。移动平均在生活中也不乏见，气象意义上的四季界定就是移动平均最好的应用。示例：移动平均预测,第六章 高级分析,分析工具回归分析：许多现象之间客观地存在各种各样有机的联系，这种联系经常表现为数量上的相互依存关系。例如：粮食产量要受施肥量、降雨量、气温等因素的影响。回归分析预测就是从各种因素的因果关系出发，通过分析与预测对象相关联因素的变动趋势，推算预测对象未来的数量状态。示例：回归分析预测,第六章 高级分析,与货币时间价值有关的函数货币时间价值是现代理财的基本观念之一，也可以说是理财活动的“第一原则”。根据货币时间价值原理，在利率

11、水平一定的条件下，同等数额的资金在不同时点上的经济价值是不等的；而数额不等的资金在不同时点上的经济价值又有可能是相等的。因此我们在会计核算尤其是在进行理财分析与财务决策时，对于跨期较大（如跨年）的收入或支出，需要先把它们放到相同的时间基础上，然后才能进行加减或比较。现值计算：PV(rate,nper,pmt,fv,type)净现值计算:终值计算贴现率计算期数计算等额收（付）款计算,第六章 高级分析,与货币时间价值有关的函数现值计算：返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。PV(rate,nper,pmt,fv,type)参数rate为各期

12、利率，参数nper为投资期（或付款期）数，参数pmt为各期支付的金额。省略pmt参数就不能省略fv参数；fv参数为未来值，省略fv参数即假设其值为0，也就是一笔贷款的未来值为零，此时不能省略pmt参数。type参数值为1或0，用以指定付款时间是在期初还是在期末，如果省略type则假设值为0，即默认付款时间在期末例如：某企业计划在5年后获得一笔资金1000000元，假设年投资报酬率为10%，问现在应该一次性地投入多少资金？PV（10%，5，0，-1000000）=？,第六章 高级分析,与货币时间价值有关的函数终值计算:基于固定利率及等额分期付款方式，返回某项投资的未来值。FV(rate,nper

13、,pmt,pv,type)参数rate为各期利率，参数nper为投资期（或付款期）数，参数pmt为各期支付的金额。省略pmt参数就不能省略pv参数；fv参数为未来值，省略pv参数即假设其值为0，也就是一笔贷款的未来值为零，此时不能省略pmt参数。type参数值为1或0，用以指定付款时间是在期初还是在期末，如果省略type则假设值为0，即默认付款时间在期末例如：向银行借款1000万元，年利率8%，期限5年，到期一次还本付息。问5年后应偿还多少万元？FV（8%，5，-1000）=,第六章 高级分析,与货币时间价值有关的函数3、贴现率计算:RATE(nper,pmt,pv,fv,type,guess

14、)Nper为总投资期，即该项投资的付款期总数。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt 包括本金和利息，但不包括其他费用或税款。如果忽略 pmt，则必须包含 fv 参数。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。其中guess为预期（猜测）利率，如果省略预期利率则假设该值为10%.,第六章 高级分析,与货币时间价值有关的函数4、期数计

15、算:NPER(nper,pmt,pv,fv,type)Nper为总投资期，即该项投资的付款期总数。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt 包括本金和利息，但不包括其他费用或税款。如果忽略 pmt，则必须包含 fv 参数。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。,第六章 高级分析,与货币时间价值有关的函数4、等额收（付）款计算:PMT(rate,nper,pv,fv,type)rate为各期利率Nper为总投资期，即该项投资的付款期总数。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。Type数字 0 或 1，用以指定各期的付款时间是在期初还是期末。,