会计信息化基础-Excel高级应用 第2章 Excel数据分析.ppt

《会计信息化基础-Excel高级应用 第2章 Excel数据分析.ppt》由会员分享，可在线阅读，更多相关《会计信息化基础-Excel高级应用 第2章 Excel数据分析.ppt（72页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第二章 Excel数据分析 2.1 对数据进行排序 2.2 在数据表中进行数据查询 2.3 对数据清单进行分类汇总 2.4 使用模拟运算表进行数据分析 2.5 方案管理 2.6 数据透视表 2.1 对数据进行排序Excel最主要的功能就是对数据清单进行整理与分析。在讲解排序等功能之前，首先要了解数据清单的基本概念。数据清单其实就是一个规则的数据表格，主要由两部分组成，第一部分是表头各字段，表示数据清单中各列数据所表示的对象；第二部分是数据记录区，数据记录以“条”计算，每条记录都是表头数据的具体反映。数据清单示例如图2.1所示。图2.1 数据清单工作表中的数据要成为一个数据清单，应遵循以下规则：

2、（1）避免在一个工作表中创建多个数据清单，因为数据清单的某些处理功能，一次只能在一个数据清单中应用；（2）工作表中的数据清单与其他数据之间必须至少留出一列或一行的空白单元格，以便在排序、筛选或自动汇总时检测和选定要操作的数据清单；（3）避免在数据清单中放置空白行或空白列，这将有利于检测和选定数据清单；（4）避免将关键的数据放在数据清单的左右两侧，以免这些数据在筛选数据时被隐藏；（5）在数据清单的第一行应有列标题作为字段名；（6）在设计数据清单时，应使同一列有相似类型的数据项；（7）不要使用空白行将列标题和第一行数据分开；（8）在单元格的开始处不要插入多余的空格，因为多余的空格将影响排序与查找。

3、2.1.1单列数据排序单列数据排序如果要快速对数据清单中的某一列数据进行排序，可利用“常用”工具栏上的“升序”按钮与“降序”按钮。具体操作步骤如下。（1）在数据清单中单击要排序的一列数据中的任意一个单元格；（2）单击“常用”工具栏上的“升序”按钮，可实现本列数据按升序排序；单击“常用”工具栏上的“降序”按钮，可实现本列数据按降序排序。2.1.2 按多个关键字对数据清单进行按多个关键字对数据清单进行排序排序（1）在要排序的数据清单上单击任意一个单元格；（2）选择“数据/排序”菜单命令，弹出“排序”对话框，同时系统将自动选中整个数据清单，如图2.2所示；（3）单击对话框中“主要关键字”列表框右侧的

4、向下箭头，在下拉列表中选定排序依据的主要字段名，并选定其“升序”或“降序”的排序方式；（4）若想在主要关键字值相同的情况下，再按第二个字段的值排序，可单击对话框中“次要关键字”列表框右侧的向下箭头，在下拉列表中选定排序依据的次要字段名，并选定其“升序”或“降序”的排序方式；（5）对于较为复杂的数据清单，还可以设定“第三关键字”，并指定其“升序”或“降序”排序方式；（6）为避免数据清单标题参加排序，可选定对话框底部的“有标题行”单选按钮；（7）单击“确定”按钮，完成数据清单的排序。图2.2 按多关键字排序2.2 在数据表中进行数据查询2.2.1 使用记录单来进行数据查询2.2.2 使用自动筛选器

5、来查询数据2.2.3 使用高级筛选来进行数据查询2.2.1 使用记录单来进行数据查询（1）将光标放置在数据清单中，Excel会自动识别数据清单。选择“数据”菜单中的“记录单”命令；（2）在弹出的对话框中单击“条件”按钮设定查询条件；（3）在弹出的对话框中输入查询的条件，如图2.5所示。条件输入完成后，单击“表单”按钮进入查询状态；（4）在查询状态下可单击“上一条”和“下一条”按钮进行查询。在此状态下可对查询到的数据进行编辑，还可以单击“删除”按钮对查找到的记录进行删除操作，也可以单击“新建”按钮在表的末尾新建记录。如果想对条件进行修改还可以单击“条件”按钮返回条件输入对话框。图2.5 输入条件

6、2.2.2 使用自动筛选器来查询数据利用数据记录单一条一条地查询复合条件的记录，但不能将符合条件的记录全部罗列出来。“筛选”可以解决这样的问题的。筛选功能分为“自动筛选”和“高级筛选”，首先介绍“自动筛选”。1.设定筛选设定筛选（1）将光标放置在如图2.4所示的数据清单中，Excel会自动识别数据清单。选择“数据/筛选/自动筛选”命令；（2）设定完自动筛选后发现在表头的每一个字段旁边都会出现一个筛选标识，可以使用此筛选标识选择筛选的条件。单击“地区”字段旁边的筛选标识，会发现所有的地区都已经被列出来了，如图2.6所示；（3）假设选择“东北部”作为筛选的条件，选择好条件后，除符合条件的“东北部”

7、以外，其他的记录都消失了。但通过工作表左边的行号发现，那些记录并没有被删除，而是因自动筛选隐藏起来了，如图2.7所示。图2.6 自动筛选标识图2.7 自动筛选结果2.排序排序启用自动筛选功能后，不仅仅可以对数据进行筛选操作，还可以对筛选后数据进行简单的排序，比如首先对“地区”字段进行筛选后，然后再单击“销售量”字段旁边的筛选标识，选择“降序排列”，这样对筛选后的数据就完成排序。3.多字段筛选多字段筛选筛选的条件可以分布在不同的字段上，在已经使用“地区”作为筛选条件的显示结果上，还可继续单击“产品”旁边的筛选标识，再选择一种产品作为另一个筛选条件。“地区”和“产品”的筛选按钮变成了蓝色，表示使用

8、了这两个字段作为筛选条件。4.显示未经筛选的数据显示未经筛选的数据如果要想取消筛选，可以单击筛选标识，然后选择“（全部）”来显示所有的记录。在多字段筛选的数据表中，需要在所有的筛选字段（即蓝色筛选按钮）中选择“（全部）”项。如果觉得使用该方法显示多字段筛选的数据表麻烦，还有一种简便的方法来显示多字段筛选数据表中的全部记录。那就是选择“数据/筛选/全部显示”命令，这样经过筛选的数据表中的所有记录便会立即显示出来。5.筛选后的复制与计算筛选后的复制与计算筛选的另外一个优点就是，经过筛选后的数据可以轻易地复制到其他地方，而不必担心不符合条件的记录也会被复制。如果想要复制经过筛选的记录，只需要选中这些

9、记录，再进行复制，然后粘贴到目标单元格即可。选择筛选后数据，然后再单击工具栏上的“自动求和”按钮，就可以对筛选后数据进行求和计算了，而不必担心其他数据也会被计算在内。6.自动筛选前自动筛选前10个个如果需要提取出销售量最高的任意条记录，可以使用自动筛选后的“前10个”功能。不仅是数据最高的可以被提取，最低的也可以被提取，只需要按照提示进行设置即可。本例中提取“销售量”最高的5条记录，操作步骤如下：（1）单击条件字段旁边的筛选标识（本例中“销售量”）为筛选条件，在弹出的下拉列表中选择“前10个”命令；（2）在弹出的“自动筛选前10个”对话框中选择显示“最大”，以及显示的数据个数5，设置好后单击“

10、确定”按钮，就会出现“销售量”排名前5位的数据。7.双条件筛选双条件筛选利用自动筛选工具可以在字段中选择筛选条件，但如果一个筛选条件下不能满足需求时，可以利用自动筛选中的“自定义”功能进行双条件筛选。（1）单击“地区”旁的筛选标识，在弹出的下拉列表中选择“自定义”项；（2）在如图2.9所示的“自定义自动筛选方式”对话框中，可以设定两个条件，其中还可以选择大于、等于、小于筛选条件和筛选数值。还可以设置两个条件的关系：是同时满足全部条件的“与”还是满足其中一个就可以的“或”。在本例中选择两个条件均为“等于”，然后选择数值为“东北部”和“西部”，条件关系为“或”；（3）单击“确定”按钮，启用自定义筛

11、选，所有的数据的“地区”字段不是“西部”就是“东北部”。如图2.10所示。图2.9 自定义自动筛选方式对话框图2.10 自定义自动筛选结果8.取消筛选取消筛选如果想要在数据清单上取消自动筛选功能，只需要选择“数据/筛选/自动筛选”命令，将前面的“勾选”符号去掉即可。2.2.3 使用高级筛选来进行数据查询在自动筛选中可以进行数据条件的设定，但如果条件很多，又比较复杂，则需要利用高级筛选功能来完成了。1.设置条件区域设置条件区域为了设定条件，高级筛选引入了“条件区域”这样一个概念，也就是说条件不用像自动筛选那样在列表中选择了，而是专门在Excel中设置一个区域来表示条件。如图2.11所示。在数据清

12、单的下方设置了一个条件区域。条件区域包含两部分内容，一部分是表头，另外一部分是表头下对应的条件，每一行记录都表示一个条件，一行之内的多个条件是“与”的关系，表示必须同时满足；而行与行之间是“或”关系，表示或者满足上一行的全部条件，或者满足下一行的全部条件。例如，图2.11中所示的条件区域表示筛选时依照如下条件：数据或者符合“东北部地区销售量大于500”，或者符合“西部地区销售量大于400”。图2.11 条件区域2.应用高级筛选应用高级筛选（1）将光标放置在数据清单中，Excel会自动识别数据清单，选择“数据/筛选/高级筛选”命令；（2）在“高级筛选”对话框中发现“列表区域”已经被自动选择，只需

13、要单击“条件区域”右边的“选择区域”按钮，进行“条件区域”的选择即可。如图2.12所示；（3）在选择区域的模式下选择刚才设定的条件区域，然后单击条件区域选择窗口旁边的“返回”按钮，返回上一级对话框；（4）选择完条件区域后单击“确定”按钮；（5）筛选后发现满足条件的记录被保留了下来，而其他记录已经被隐藏起来了。这一点通过工作表左边的行号就可以看出。如图2.13所示。图2.12 设置条件区域图2.13 高级筛选结果3.取消高级筛选取消高级筛选如果不再需要对数据进行筛选了，想要取消高级筛选功能，只需要选择“数据/筛选/全部显示”命令，高级筛选功能即被取消。2.3 对数据清单进行分类汇总通过前面的讲解

14、知道了如果使用SUM函数对数据进行汇总，但是如需要将数据分门别类地汇总，SUM就显得力不从心了。此时可以使用Excel中的另外一个功能“分类汇总”来进行不同类别的汇总。2.3.1 快速创建数据清单的分类汇总2.3.2 创建嵌套分类汇总2.3.3 删除分类汇总2.3.1 快速创建数据清单的分类汇总分类汇总的设置非常简单，可以通过如下步骤快速创建分类汇总。操作步骤如下：（1）将数据清单根据需要进行排序，然后将光标放置在数据清单中，Excel会自动识别数据清单，再选择“数据/分类汇总”命令；（2）这时数据列表已经被自动选择，然后在弹出的“分类汇总”对话框中选择分类的字段、汇总的方式和想要汇总的项，然

15、后单击“确定”按钮。2.3.2 创建嵌套分类汇总（1）在现有的分类汇总数据清单的基础上再次调用分类汇总功能。然后选择合适的分类字段，汇总方式选择为“平均值”；（2）清除“替换当前分类汇总”选项，再单击“确定”按钮；（3）嵌套的分类汇总就创建完毕了，在数据清单中不仅仅有汇总值，而且还有平均值。效果如图2.15所示。图2.15 查看嵌套分类汇总结果2.3.3 删除分类汇总不需要显示分类汇总时，可以再次调出“分类汇总”对话框，然后单击“全部删除”按钮，所有的分类汇总项就被删除了。2.4 使用模拟运算表进行数据分析当通过公式得到计算结果后，往往想知道，如果公式中的一个或两个数值发生变化，最终会怎样影响

16、结果。模拟运算表会根据假设条件和公式计算出结果。模拟运算表可以分为单变量模拟运算表和双变量模拟运算表。使用单变量模拟运算表可模拟出公式中一个数值发生变化对结果产生的影响；使用双变量模拟运算表可模拟出公式中两个数值发生变化对结果产生的影响。2.4.1 使用单变量模拟运算表求解2.4.2 使用双变量模拟运算表求解2.4.3 删除模拟运算表2.4.4 将模拟运算表的计算结果换为常量2.4.1 使用单变量模拟运算表求解如果只想知道公式中的某一个数值变化时，会怎样影响结果，就可使用单变量模拟运算表。在使用单变量模拟运算表的时候，根据变量分布在行或列中，可将模拟运算表分为行引用模拟运算表和列引用模拟运算表

17、。不论是行引用模拟运算表还是列模拟运算表，公式、假设值与结果值的位置都非常重要，如果位置设置不对，计算结果也不会正确。1.列引用模拟运算表列引用模拟运算表首先利用列引用模拟运算表来为大家讲解模拟运算表的使用。如图2.16所示，在单元格B6中写有计算利率的公式，公式中引用了B1、B2和B3中的数值，如果想查看“年限（B2）”变化后还款金额的变化，可依照如下步骤。（1）在（A7:A14）区域中输入替换“年限”的假设值。确保公式与假设值的位置关系为对角线关系，且公式必须在假设值的右上方。只有这样设置，最后结果才会正确出现在结果区域中；（2）选中区域，确保区域中包含公式、假设值和结果区域。再单击“数据

18、”菜单，选择“模拟运算表”命令；（3）因为假设值分布在列中，所以此模拟运算表为列引用模拟运算表。在弹出的“模拟运算表”对话框中，将“输入引用列的单元格”设置为假设值要替换的原公式中的引用“年限（B2）”，然后单击“确定”按钮；（4）在结果区域中已经出现了根据假设值和公式计算出的各种结果。如图2.17所示。2.行引用模拟运算表行引用模拟运算表在了解了单变量列引用模拟运算表后，再来了解一个单变量行引用模拟运算表。如果假设值分布在某行中，如图2.18所示，就应当使用单变量行引用模拟运算表来进行假设值的计算，操作步骤如下：（1）在（C6:G6）区域中输入替换“利率”的假设值。确保公式与假设值的位置关系

19、为对角线关系，且公式必须在假设值的左下方。只有这样设置，最后结果才会正确出现在结果区域中；（2）选中区域，确保区域中包含公式、假设值和结构区域，再选择“数据/模拟运算表”命令；（3）因为假设值分布在行中，所以此模拟运算表为单变量行引用模拟运算表。在弹出的“模拟运算表”对话框中，将“输入引用行的单元格”设置为假设值要替换的原公式中的引用“利率（B1）”，然后单击“确定”按钮；（4）如图2.19所示，在结果区域中已经出现了根据假设值和公式计算出的各种结果。2.4.2 使用双变量模拟运算表求解如果需要将公式中的两个数值分别替换为假设值，查看计算结果，此时就需要双变量模拟运算表来帮忙了。在使用双变量模

20、拟运算表时，必须确保两个假设值分别分布于行和列中，且公式位于它们的交汇处，如图2.20所示。具体设置步骤如下。（1）在（C6:F6）区域中输入替换“利率”的假设值，在（B7:B14）区域中输入替换“年限（B2）”的假设值。必须确保公式位于两个假设值区域的交汇处。只有这样设置，最后结果才会正确出现在结构区域中；（2）选中区域，确保区域中包含公式、假设值和结果区域。再选择“数据/模拟运算表”命令；（3）在弹出的“模拟运算表”对话框中，将“输入引用行的单元格”设置为假设值要替换的原公式中的引用“利率（B1）”，再将“输入引用列的单元格”设置为假设值要替换的原公式中的引用“年限（B2）”，然后单击“确

21、定”按钮；（4）如图2.21所示。在结果区域中已经出现了根据假设值和公式计算出的各种结果。2.4.3 删除模拟运算表当建立模拟运算表后，当假设值发生变化时，会自动重新计算结果，这就是模拟运算表最大的优点。不能删除模拟运算表中的某一个单元格的数据。如果想删除模拟运算表中某一个单元格的数据，则必须选中模拟运算表中的整个模拟运算区域，然后再进行删除操作。2.4.4 将模拟运算表的计算结果换为常量当选中模拟运算表的结果区域中的某个单元格时，在编辑栏中可以看见模拟运算表中的数据并不是常量，如果需要将模拟运算表中的数据转换为常量，可依照如下操作步骤。（1）选中模拟运算表的结果区域，单击工具栏上的“复制”按

22、钮；（2）单击工具栏上“粘贴”按钮旁的小三角号，打开下拉菜单，选择“值”命令；（3）单击结果区域中的单元格，从编辑栏中就会发现，表中的计算结果已经被转换为常量了。2.5 方案管理方案是除模拟运算表以外的另外一种假设分析工具。可以利用方案来预测当公式的引用数据发生变化时的相应输出结果。方案是Excel保存在工作表中并可进行自动替换的一组值。与模拟运算表不同的是，方案不需要额外的单元格来设置假设值，而这些假设值保存在单独的“方案容器”中，可以随时使用方案中的数值来替换当前数值，还可以随时切换到任意方案来查看不同的结果。2.5.1 定义和编辑方案2.5.2 显示方案查看结果2.5.3 创建方案摘要2

23、.5.1 定义和编辑方案本例中将如图2.22所示的“总收入”和“总成本”作为方案的假设值。利润是根据“总收入”减“总成本”得出的。如果想设置三种方案，一种为当前的“正常”值，一种为“最佳”值，另外一种为“最差”值。操作步骤如下：（1）选中方案的可变单元格，即“总收入”和“总成本”所在的单元格（B2:B3）。然后，选择“工具/方案”命令；（2）在“方案管理器”对话框中单击“添加”按钮来添加方案；（3）在“添加方案”对话框中，输入第一个方案名“正常”和可变单元格的位置为（B2:B3），然后单击“确定”按钮，如图2.23所示；（4）在“方案变量值”对话框中确认此方案的数值，然后单击“添加”按钮来添加

24、下一个方案；（5）在“添加方案”对话框中输入第二个方案名“最佳”，确认可变单元格为（B2:B3），再单击“确定”按钮；（6）在“方案变量值”对话框中输入最佳方案的相应数值，然后再单击“添加”按钮继续添加新的方案，如图2.24所示；（7）在“添加方案”对话框中输入方案名“最差”，确认可变单元格为（B2:B3），然后单击“确定”按钮；（8）在“方案变量值”对话框中输入“最差”方案的相应数值，然后单击“确定”按钮结束方案的添加，如图2.25所示。2.5.2 显示方案查看结果此时，如图2.26所示，在方案管理器中就可以看见刚才添加的三个方案的名称了。如果想显示任意的方案数值，只需要选中某个方案，然后单

25、击“显示”按钮即可，方案数值即可显示在屏幕上了，而引用引单元格的公式也自动根据新的数值来进行计算了。2.5.3 创建方案摘要如果想对比各种方案，考察各种方案对计算“利润”的影响，可以执行以下的操作步骤来创建方案的摘要。（1）打开“方案管理器”，单击“摘要”按钮；（2）在“方案摘要”对话框中输入方案所影响的结果单元格，然后单击“确定”按钮；（3）Excel创建出一张工作表，名称为“方案摘要”。在方案摘要工作表中，可以查看不同方案的可变单元格数值，以及不同方案中结果单元格的数值变化。2.6 数据透视表如果需要汇总较大的列表并对每种数据进行多种比较时，可以使用数据透视表。数据透视表是一种交互式报表，

26、可快速合并和比较大量数据。可旋转其行和列来查看源数据的不同汇总，而且可查看特定区域的明细数据。由于数据透视表是交互式的，因此，还可以更改数据的视图以查看更多明细数据或计算不同的汇总额。2.6.1 创建数据透视表2.6.2 设置数据透视表2.6.3 建立数据透视图如图2.27所示。如果想在这样一张销售表中分别查看小张、小王、小李在各个月份、各种产品的销售总额和销售总量是非常困难的一件事情，而有了数据透视表，就会变得非常简单。2.6.1 创建数据透视表创建数据透视表可依照如下步骤。（1）将光标放置在数据透视表的区域内，Excel会自动识别数据。选择“数据/数据透视表和数据透视图”命令；（2）在数据

27、透视表和数据透视图向导的第一步中，首先选择数据区域所在的位置，然后选择所创建的报表类型为“数据透视表”。设置好后，再单击“下一步”按钮；（3）在向导的第二步中，应该选择数据透视表所在的区域，一般情况下，不必作过多的设置，因为Excel会自动识别区域，然后再单击“下一步”按钮；（4）在向导的第三步，也是最重要的一步，需要设置数据透视表的布局。然后单击“布局”按钮；（5）在打开的“布局”对话框中，右边显示当前可以使用的字段，左边显示数据透视表的布局，此时可以使用鼠标将右边的字段拖曳至左边的布局中。如图2.28所示；（6）“页”字段为数据透视表中最大的字段。将月份拖曳至“页字段”，只显示某一月份的销

28、售人员与产品的汇总。需要交叉汇总的项目是“销售员”和“销售产品”，所以将这两个字段分别拖曳至“行”和“列”，然后将需要汇总的数据“销售额”拖曳至“数据”区域，完成后单击“确定”按钮；（7）返回到向导第一步后，单击“确定”按钮来完成透视表的设置；（8）现在就可以查看各个销售人员的各项销售产品的汇总数据了。如图2.29所示。2.6.2 设置数据透视表数据透视表建立好后，不仅可以查看所有项目的汇总情况，还可以通过简单的设置，进一步查看各个单项或杂项的汇总数据。1.修改修改“页页”字段字段如果只想查看某个月份中各个销售人员的产品销售情况，可以单击“页”字段旁的下拉列表，然后选择合适的月份，再单击“确定

29、”按钮。数据透视表中就只显示各个销售人员在选择的月份中的销售汇总了。2.修改修改“行行”字段字段如果只想查看各个销售人员销售“彩电”和“冰箱”的汇总数字。可以单击“行”字段，也就是“销售产品”字段旁边的下拉列表，然后选择需要查看的产品，选择好后，再单击“确定”按钮。就可以查看各个销售人员关于这两种产品的销售汇总情况了。3.修改修改“列列”字段字段如果想显示小李和小张的销售汇总情况，可以单击“列”字段，即“销售员”字段旁边的下拉列表，选择待查询的人员后，再单击“确定”按钮。此时，就可以查看这两个销售员的销售汇总情况了。2.6.3 建立数据透视图如果还是觉得数据透视表显示的数据不够直观，那么可以用数据透视表中的数据创建数据透视图。只要将光标放置在数据透视表内，然后单击“数据透视表”工具栏中的“图表向导”按钮即可创建数据透视图。Excel会自动创建一张图表工作表，如果对图表类型或其他设置不满意，可以在图表上单击鼠标右键，在弹出的快捷菜单中选择“图表类型”命令，根据以前学习过的方法设置图表的各种属性。