Excel数据分析与处理(下)

1、1. 最佳EXCEL学习方法2. 工作环境基本操作3. 数据输入与导入：数据有效性4. 数据整理与编辑：设置单元格、排序5. 数据查询：筛选、条件格式6. 常规计算分析：分类汇总、合并计算、数据透视表7. 高级分析：模拟运算、单变量求解、规划求解、分析工具主要介绍借助Excel中的自动筛选、高级筛选及条件格式等功能，方便地实现数据的自动查询，从而极大的提高工作效率。注意事项：1. 如果在选定区域启用自动筛选，筛选标志设在首行；如果选定某个单元格启用筛选，则标记设置在整个数据列表首行。2. 同一个工作表中，只能对一个数据区域启用自动筛选功能。3. 包括空行或空列时，要选取整个数据列表，然后启用自

2、动筛选功能；否则会遗漏数据。示例：对数据列表的局部启用自动筛选包含多重标题行的自动筛选充分利用Excel自适应提供筛选条件自定义筛选可以进行更个性化的筛选用?代表单个字符用*代表多个字符筛选求和要用SUBTOTAL使用辅助列技巧示例：根据筛选结果动态汇总计算需要在数据列表之外单独建立筛选条件参数： 列表区域 条件区域 复制区域示例：了解高级筛选将筛选结果复制到其他工作表1、条件关系设置2、使用公式自定义筛选条件示例：“关系与”条件的设置方法“关系或”条件的设置方法 使用公式自定义筛选条件可以使用条件格式直观地注释数据以供分析和演示使用。若要在数据中轻松地查找例外和发现重要趋势，可以实施和管理多

3、个条件格式规则，这些规则以渐变色、数据柱线和图标集的形式将可视性极强的格式应用到符合这些规则的数据。条件格式也很容易应用：只需单击几下鼠标，即可看到可用于分析的数据中的关系。 示例： 标识同一户口本人员记录 标识中标公司和中标金额主要介绍有关使用分类汇总、合并计算、数据透视表等功能对数据进行计算分析的相关技巧 对同类数据进行汇总计算。注意： 使用之前先对数据列表按类别排序。 只能对连续区域使用”组合”命令，不能对多重选定区域使用组合命令。 注意汇总数据显示位置的设置。复制分类汇总结果：按要求分类汇总；利用定位条件选项，复制。再粘贴到目标区示例： 自动生成分级显示创造分类条件进行汇总：增加辅助列

4、创造分类条件；再进行分类汇总。删除分类汇总：重进行分类汇总选择选项。示例：对系列分类汇总认识合并计算：注意：使用按类别合并的功能时，数据源列表必须包含行或列标题。处理方式不仅仅是SUM,还有其他选择(计数、平均、乘积)示例：认识合并计算分类项不相同的数据表合并计算利用合并计算进行数值型数据核对数据透视表概述快捷、强大的数据分析方法，可以解答您关于数据的种种问题。数据透视表检查源数据开始使用数据透视表之前，请查看一下您的 Excel 工作表，确保它已经准备妥当，可以用来创建报表。都应有列标题，创建数据透视表时，源数据中的每一列标题都会成为可在报表中使用的字段。数据透视表创建数据透视表数据透视表1

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

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

7、公式结果的影响。例如，更改分期支付表中的利率可以调整支付金额。模拟运算表(数据表)单变量求解工具示例： 使用模拟运算表进行单变量预测分析 解一元方程式 使用模拟运算表同时测算多个项目求解一元方程式： 确定目标单元格 确定可变单元格 指定目标值 得到结果：示例： 解一元方程式规划求解“规划求解”是一组命令的组成部分（有时也称作假设分析工具）。借助“规划求解”，可求得工作表上某个单元格的最优值。“规划求解”将对直接或间接与目标单元格中的公式相关的一组单元格进行处理。“规划求解”将调整所指定的变动单元格来限制“规划求解”可在模型中使用的值，而且约束条件可以引用影响目标单元格公式的其他单元格。 示例：

8、 计算住房贷款鸡兔同笼问题：今有雏兔同笼，上有三十五头，下有九十四足，问雏兔各几何？约束条件1：B3:B4=C3:C4约束条件2：B1:B2=整数使用规划求解解决鸡兔同笼问题描述统计：描述统计的任务是描述随机变量的统计规律性。示例： 使用描述统计工具分析学生成绩 使用描述统计工具优选小麦品种移动平均 ：移动平均就是对一系列变化的数据按照指定的数据数量依次求取平均，并以此作为数据变化的趋势供分析人员参考。移动平均在生活中也不乏见，气象意义上的四季界定就是移动平均最好的应用。示例： 移动平均预测回归分析 ：许多现象之间客观地存在各种各样有机的联系，这种联系经常表现为数量上的相互依存关系。例如：粮食

9、产量要受施肥量、降雨量、气温等因素的影响。回归分析预测就是从各种因素的因果关系出发，通过分析与预测对象相关联因素的变动趋势，推算预测对象未来的数量状态。示例： 回归分析预测 货币时间价值是现代理财的基本观念之一，也可以说是理财活动的“第一原则”。根据货币时间价值原理，在利率水平一定的条件下，同等数额的资金在不同时点上的经济价值是不等的；而数额不等的资金在不同时点上的经济价值又有可能是相等的。因此我们在会计核算尤其是在进行理财分析与财务决策时，对于跨期较大（如跨年）的收入或支出，需要先把它们放到相同的时间基础上，然后才能进行加减或比较。1. 现值计算：PV(rate,nper,pmt,fv,ty

10、pe)2. 净现值计算:3. 终值计算4. 贴现率计算5. 期数计算6. 等额收（付）款计算 1. 现值计算：返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。 参数rate为各期利率，参数nper为投资期（或付款期）数，参数pmt为各期支付的金额。省略pmt参数就不能省略fv参数；fv参数为未来值，省略fv参数即假设其值为0，也就是一笔贷款的未来值为零，此时不能省略pmt参数。type参数值为1或0，用以指定付款时间是在期初还是在期末，如果省略type则假设值为0，即默认付款时间在期末例如：某企业计划在5年后获得一笔资金1000000元，假设年投

11、资报酬率为10%，问现在应该一次性地投入多少资金？ PV（10%，5，0，-1000000）=？ 2. 终值计算:基于固定利率及等额分期付款方式，返回某项投资的未来值。参数rate为各期利率，参数nper为投资期（或付款期）数，参数pmt为各期支付的金额。省略pmt参数就不能省略pv参数；fv参数为未来值，省略pv参数即假设其值为0，也就是一笔贷款的未来值为零，此时不能省略pmt参数。type参数值为1或0，用以指定付款时间是在期初还是在期末，如果省略type则假设值为0，即默认付款时间在期末例如：向银行借款1000万元，年利率8%，期限5年，到期一次还本付息。问5年后应偿还多少万元？FV（8

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