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

1、第二章是Excel数据分析，2.1数据排序2.2数据表查询2.3数据分类汇总2.4模拟操作表分析数据2.5方案管理2.6透视表，2.1数据排序，Excel的主要功能是对数据表进行排序分析。在解释排序和其他功能之前，我们应该先了解数据表的基本概念。事实上，数据列表是一个常规的数据表，它主要由两部分组成。第一部分是标题字段，它表示数据列表中每一列数据所代表的对象；第二部分是数据记录区，在这里数据记录被计算为“条”，每个记录都是头数据的具体反映。数据列表的一个例子如图2.1所示。图2.1数据列表，工作表中的数据应该是一个数据列表，应该遵循以下规则：(1)避免在一个工作表中创建多个数据列表，因为数据列

2、表的一些处理功能一次只能应用在一个数据列表中；(2)数据表和工作表中的其他数据之间必须至少有一列或一行空白单元格，以便在排序、过滤或自动汇总时可以检测和选择要操作的数据表；(3)避免在数据列表中放置空白行或空白列，这将有助于检测和选择数据列表；(4)避免将关键数据放在数据列表的左侧和右侧，以免在筛选数据时隐藏这些数据；(5)在数据列表的第一行，应该有列标题作为字段名称；(6)设计数据表时，同一列应该有相似的数据项；(7)不要使用空行将列标题与第一行数据分开；(8)不要在单元格的开头插入多余的空间，因为多余的空间会影响排序和搜索。2.1.1对单列数据进行排序。如果要对数据列表中的一列数据进行快速

3、排序，可以使用“常用”工具栏上的“升序”和“降序”按钮。具体操作步骤如下。(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按自动过滤器查询数据，2.2.3按高级过滤器查询数据，2.2.1按记录表查询数据，(1)将光标放在数据列表中，Excel将自动识别数据列表。在“数据”菜单中选择“记录表”命令；(2)点击弹

5、出对话框中的“条件”按钮，设置查询条件；(3)在弹出的对话框中输入查询条件，如图2.5所示。条件输入完成后，点击“表单”按钮进入查询状态；(4)点击“上一步”和“下一步”按钮进行查询。在这种状态下，您可以编辑查询的数据，单击“删除”删除找到的记录，或者单击“新建”在表的末尾创建新记录。如果要修改条件，也可以点击“条件”按钮返回条件输入对话框。图2.5输入条件，2.2.2使用自动过滤器查询数据，并使用数据记录逐个查询复合条件记录，但所有满足条件的记录不能列出。“筛选”可以解决这样的问题。过滤功能分为“自动过滤”和“高级过滤”。首先介绍了“自动过滤”。1。设置过滤器，(1)将光标放在数据列表中，如

6、图2.4所示，Excel将自动识别数据列表。选择数据/筛选/自动筛选命令；(2)设置自动过滤后，在表头的每个字段旁边都会出现一个过滤标识，可以用来选择过滤条件。单击“区域”字段旁边的过滤标记，您会发现所有区域都已列出，如图2.6所示；(3)假设选择“东北”作为筛选条件，选择好条件后，除合格的“东北”外，所有记录都消失。但是，通过工作表左侧的行号，可以发现这些记录并没有被删除，而是由于自动过滤而被隐藏了，如图2.7所示。2.分类。启用自动过滤功能后，不仅可以过滤数据，还可以简单地对过滤后的数据进行排序。例如，首先过滤“地区”字段后，单击“销售量”字段旁边的过滤标志，然后选择“降序排序”。3。多字

7、段过滤，过滤条件可以分布在不同的字段。在使用“区域”作为过滤条件的显示结果上，您可以继续单击“产品”旁边的过滤标志，然后选择一个产品作为另一个过滤条件。“区域”和“产品”的过滤按钮变为蓝色，表示这两个字段用作过滤标准。4。显示未过滤的数据。如果要取消过滤，可以单击过滤器标识并选择(全部)以显示所有记录。在具有多字段过滤的数据表中，您需要从所有过滤字段中选择“(全部)”(即蓝色过滤按钮)。如果使用这种方法显示多字段筛选数据表很麻烦，那么还有一种简单的方法显示多字段筛选数据表中的所有记录。也就是说，选择“数据/筛选/全部显示”命令，以便立即显示筛选数据表中的所有记录。筛选后的复制和计算筛选的另一个

8、优点是，筛选后的数据可以很容易地复制到其他地方，而不用担心不合格的记录会被复制。如果您想复制过滤后的记录，只需选择它们，复制它们，然后将它们粘贴到目标单元格中。选择过滤后的数据，然后单击工具栏上的“自动求和”按钮，对过滤后的数据求和，而无需担心其他数据会被计算在内。6.自动过滤前10条记录。如果需要提取销量最高的记录，可以使用自动过滤后的“前10名”功能。不仅可以提取最高的数据，还可以提取最低的数据，只需根据提示进行设置。在本例中，提取了五条“销售量”最高的记录。操作步骤如下：(1)点击条件字段旁边的过滤标识(本例中为“销售额”)，作为过滤条件，从弹出的下拉列表中选择“前10名”命令；(2)在

9、弹出的“前10名自动过滤”对话框中，选择显示“最大”，显示的数据数为5。设置完成后，点击“确定”按钮，出现“销售量”的前5位数据。7。双条件过滤，可以使用自动过滤工具选择字段中的过滤条件，但是如果一个过滤条件不能满足要求，可以使用自动过滤中的“自定义”功能进行双条件过滤。、(1)点击“区域”旁边的过滤标志，在弹出的下拉列表中选择“自定义”项；(2)在如图2.9所示的“自定义自动过滤方式”对话框中，可以设置两个条件，其中还可以选择过滤条件大于、等于、小于和过滤值。您还可以设置两个条件之间的关系：“与”同时满足所有条件，或者“或”只满足其中一个条件。在这个例子中，两个条件被选择为“相等”，然后数值

10、被选择为“东北”和“西”，并且条件关系是“或”；(3)单击确定启用用户定义的过滤。所有数据的“区域”字段要么是“西部”，要么是“东北部”。如图2.10所示。图2.9自定义自动过滤模式对话框，图2.10自定义自动过滤结果，8。取消过滤。如果您想取消数据列表上的自动过滤功能，您只需选择“数据/过滤器/自动过滤”命令并删除先前的“检查”符号。2.2.3使用高级过滤功能查询数据，可以在自动过滤中设置数据条件，但是如果有很多复杂的条件，需要使用高级过滤功能来完成。1.设置条件区域。为了设置条件，高级过滤引入了“条件区域”的概念，这意味着不需要像自动过滤一样在列表中选择条件，而是在Excel中设置一个特殊

11、区域来表示条件。如图2.11所示。条件区域设置在数据列表下方。条件区域包含两部分，一部分是表头，另一部分是表头下的对应条件。每一行记录代表一个条件，一行中的多个条件处于“与”的关系中，这意味着它们必须同时满足；行之间的关系是“或”，这意味着前一行或下一行的所有条件都得到满足。例如，图2.11所示的条件区域表示过滤是基于以下条件：数据满足“东北销售量大于500”或“西部销售量大于400”。图2.11条件区域，2。应用高级过滤，(1)将光标放在数据列表中，Excel将自动识别数据列表，并选择“数据/过滤器/高级过滤器”命令；(2)在高级过滤对话框中，发现列表区域已经被自动选中，所以您只需点击条件区

12、域右侧的选择区域按钮，即可选择条件区域。如图2.12所示；(3)在选择区域模式中选择刚刚设置的条件区域，然后点击条件区域选择窗口旁边的“返回”按钮，返回到上一个对话框；(4)选择条件区域后，点击“确定”按钮；(5)筛选后，发现符合要求的记录已被保存，其他记录被隐藏。这可以从工作表左侧的行号看出。如图2.13所示。图2.12设置条件区，图2.13高级过滤结果，3。取消高级过滤。如果您不再需要过滤数据，如果您想取消高级过滤功能，您只需要选择命令数据/过滤/显示全部，高级过滤功能将被取消。2.3对数据列表进行分类和汇总。通过前面的解释，我们知道如果使用SUM函数来汇总数据，但是如果数据需要在不同的类

13、别中进行汇总，SUM将无法这样做。此时，您可以使用Excel中的另一个函数“分类和汇总”，来汇总不同的类别。2.3.1快速创建数据列表分类汇总2.3.2创建嵌套分类汇总2.3.3删除分类汇总2.3.1快速创建数据列表分类汇总。分类汇总的设置非常简单，因此您可以通过以下步骤快速创建分类汇总。操作步骤如下：(1)根据需要对数据列表进行排序，然后将光标放在数据列表上，Excel会自动识别数据列表，然后选择“数据/分类汇总”命令；(2)此时，数据列表已经被自动选中，在弹出的“分类汇总”对话框中，选择要汇总的分类字段、汇总方式和项目，然后点击“确定”。，2.3.2创建嵌套分类汇总，(1)基于现有分类汇总

14、数据列表再次调用分类汇总函数。然后选择合适的分类字段，汇总方式为“平均”；(2)清除“替换当前小计”选项，点击“确定”按钮；(3)创建嵌套分类汇总，数据列表中不仅有汇总值，还有平均值。效果如图2.15所示。图2.15查看嵌套小计结果，2.3.3删除小计。如果不需要显示小计，可以再次调用“小计”对话框，然后点击“全部删除”按钮删除所有小计项目。2.4使用模拟表进行数据分析，当通过公式获得计算结果时，经常会想，如果公式中的一个或两个值发生变化，结果会受到怎样的影响。模拟表将根据假设和公式计算结果。模拟表可分为单变量模拟表和双变量模拟表。单变量模拟表可以用来模拟公式中数值的变化对结果的影响；利用二元

15、模拟运算表，可以模拟公式中两个值的变化对结果的影响。2.4.1使用单变量模拟运算表求解2.4.2使用双变量模拟运算表求解2.4.3删除模拟运算表2.4.4将模拟运算表的计算结果改为常数，2.4.1使用单变量模拟运算表求解，如果只想知道当公式中某个值发生变化时结果会受到怎样的影响，可以使用单变量模拟运算表。当使用单变量模拟表时，根据变量在行或列中的分布，模拟表可以分为行参考模拟表和列参考模拟表。无论是行引用模拟运算表还是列模拟运算表，公式、假设值和结果值的位置都非常重要。如果位置设置不正确，计算结果将不正确。1.列引用模拟操作表。首先，使用列引用模拟操作表来解释模拟操作表的使用。如图2.16所示

16、，计算利率的公式写在单元格B6中，公式中引用了B1、B2和B3的值。如果您想查看“生活(B2)”变更后还款金额的变化，可以按照以下步骤进行。(1)在(A7:A14)区域输入替换“年”的假设值。确保公式和假设值之间的位置关系是对角线的，并且公式必须位于假设值的右上角。只有使用此设置，最终结果才能正确出现在结果区域。(2)选择区域以确保它包含公式、假设值和结果区域。然后点击“数据”菜单，选择“模拟操作表”命令；(3)由于假设值分布在列中，所以该模拟运算表是列参考模拟运算表。在弹出的“模拟运算表”对话框中，将原公式中的“输入引用列的单元格”设置为引用“年龄(B2)”替换为假设值，然后点击“确定”；(

17、4)在结果区域，出现了根据假设值和公式计算的各种结果。如图2.17所示。2。该行指的是模拟操作表。理解了单变量列指的是模拟运算表后，我们就会知道单变量行指的是模拟运算表。如果假设值分布在一行中，如图2.18所示，应使用单变量行参考模拟运算表来计算假设值。操作步骤如下：(1)在(C6:G6)区域输入假设值而不是“利率”。确保公式和假设值之间的位置关系是对角线的，并且公式必须位于假设值的左下方。只有使用此设置，最终结果才能正确出现在结果区域。(2)选择一个区域，确保它包含公式、假设值和结构区域，然后选择“数据/模拟运算表”命令；(3)因为假设值是按行分布的，所以该模拟运算表是单变量行参考模拟运算表。在弹出的“模拟运算表”对话框中，将原公式中的“输入参考行单元格”设置为参考“利率(B1)”并替换为假设值，然后点击“确定”；(4)如图2.19所示，根据假设值和公式计算的各种结果出现在结果区域。2.4.2使用二元模拟运算表解决问题。如果您需要用假设值替换公式中的两个数值并检查计算结果，此时您需要二元模拟运算表的帮助。当使用