搜索
第四章电子表格软件在会计中的应用第四节数据清单及其管理分析一、数据清单的构建（一）数据清单的概念Excel中，数据库是通过数据清单或列表来实现的。数据清单是一种包含一行列标题和多行数据且每行同列数据的类型和格式完全相同的Excel工作表。数据清单中的列对应数据库中的字段，列标志对应数据库中的字段名称，每一行对应数据库中的一条记录。（二）构建数据清单的要求为了使Excel自动将数据清单当作数据库，构建数据清单的要求主要有：1.列标志应位于数据清单的第一行，用以查找和组织数据、创建报告。2.同一列中各行数据项的类型和格式应当完全相同。3.避免在数据清单中间放置空白的行或列，但需将数据清单和其他数据隔开时，应在它们之间留出至少一个空白的行或列。4.尽量在一张工作表上建立一个数据清单。二、记录单的使用（一）记录单的概念记录单又称数据记录单，是快速添加、查找、修改或删除数据清单中相关记录的对话框。“记录单”对话框左半部从上到下依次列示数据清单第一行从左到右依次排列的列标志，以及待输入数据的空白框；右半部从上到下依次是“记录状态”显示区和“新建”、“删除”、“还原”、“上一条”、“下一条”、“条件”、“关闭”等按钮。（二）通过记录单处理数据清单的记录1.通过记录单处理记录的优点通过记录单处理记录的优点主要有：界面直观，操作简单，减少数据处理时行列位置的来回切换，避免输入错误，特别适用于大型数据清单中记录的核对、添加、查找、修改或删除。2.“记录单”对话框的打开打开“记录单”对话框（如图4-79所示）的方法是：输入数据清单的列标志后，选中数据清单的任意一个单元格，点击“数据”菜单中的“记录单”命令。Excel2013的数据功能区中尽管没有“记录单”命令，但可通过依次按击快捷键“Alt+D”、“Alt+O”打开，或者通过单击以定义方式添入“快速访问工具栏”中的“记录单”按钮来打开。将“记录单”按钮添入“快速访问工具栏”的方法是：单击“文件”选项卡标签后，单击“选项”按钮（或单击“快速访问工具栏”右下角“自定义快速访问工具栏”按钮，单击“其他命令”菜单），打开“Excel选项”窗口，选定左侧菜单中的“快速访问工具栏”选项，进入“自定义快速访问工具栏”对话框，在左侧上部的“从下列位置选择命令”对话框下拉列表中选定“不在功能区中的命令”选项，从其下面的列表框中移动右边的向下滚动块，选定“记录单”选项，单击“添加”按钮，“记录单”选项被添入右侧的“自定义快速访问工具栏”列表框（如图4-80所示），单击“确定”按钮。“记录单”对话框打开后，只能通过“记录单”对话框来输入、查询、核对、修改或者删除数据清单中的相关数据，但无法直接在工作表的数据清单中进行相应的操作。3.在“记录单”对话框中输入新记录在“记录单”对话框中输入一条新记录的方法是：单击“新建”按钮，光标被自动移入第一个空白文本框，等待数据录入。在第一个空白文本框内输入相关数据后，按“Tab”键（不能按“Enter”键）或鼠标点击第二个空白文本框，将光标移入第二个空白文本框（按“Shift+Tab”快捷键则移入上一个文本框），等待数据录入，以下类推。输完一条记录的所有空白文本框后，按下“Enter”键或上下光标键确认。该条记录将被加入数据清单的最下面，光标被自动移入下一条记录的第一个空白文本框，等待新数据的录入。在数据录入过程中，如果发现某个文本框中的数据录入有误，可将光标移入该文本框，直接进行修改；如果发现多个文本框中的数据录入有误，不便逐一修改，可通过单击“还原”按钮放弃本次确认前的所有输入，光标将自动移入第一个空白文本框，等待数据录入。所有记录输入完毕，单击“关闭”按钮，退出“记录单”对话框并保存退出前所输入的数据。4.利用“记录单”对话框查找特定单元格利用“记录单”对话框查找特定单元格的方法是：单击“条件”按钮，该按钮变为“表单”，对话框中所有列后文本框中的数据都被清空，光标自动移入第一个空白文本框，等待键入查询条件（如图4-81所示）。键入查询条件后，单击“下一条”按钮或“上一条”按钮（或上下光标键）进行查询，符合条件的记录将分别出现在该对话框相应列后的文本框中，“记录状态”显示区相应显示该条记录的次序数以及数据清单中记录的总条数。种方法尤其适合于具有多个查询条件的查询中，只要在对话框多个列名后的文本框内同时输入相应的查询条件即可。5.利用“记录单”对话框核对或修改特定记录利用“记录单”对话框核对或修改特定记录的方法是：查找到待核对或修改的记录后，在对话框相应列后文本框中逐一核对或修改，修改完毕后按“Enter”键或单击“新建”、“上一条”、“下一条”、“条件”、“关闭”等按钮或上下光标键确认。在确认修改前，“还原”按钮处于激活状态，可通过单击“还原”按钮放弃本次确认前的所有修改。6.利用“记录单”对话框删除特定记录利用“记录单”对话框删除特定记录的方法是：查找到待修改的记录，单击“删除”按钮，弹出“显示的记录将被删除”的提示框，单击“确定”按钮，即可删除找到的纪录。记录删除后无法通过单击“还原”按钮来撤销。三、数据的管理与分析在数据清单下，可以执行排序、筛选、分类汇总、插入图表和数据透视表等数据管理和分析功能。（一）数据的排序数据的排序是指在数据清单中，针对某些列的数据，通过“数据”菜单或功能区中的排序命令来重新组织行的顺序。1.快速排序使用快速排序的操作步骤为：（1）在数据清单中选定需要排序的各行记录；（2）执行工具栏或功能区中的排序命令。Excel2003中，单击工具栏中的“升序”或“降序”按钮（如图4-82所示）；Excel2013中，单击“数据”功能区选项卡，单击“排序和筛选”功能组中的“升序”或“降序”命令按钮，如图4-83所示。需要注意的是，如果数据清单由单列组成，即使不执行第一步，只要选定该数据清单的任意单元格，直接执行第二步，系统都会自动排序；如果数据清单由多列组成，应避免不执行第一步而直接执行第二步的操作，否则数据清单中光标所在列的各行数据被自动排序，但每一记录在其他各列的数据并未随之相应调整，记录将会出现错行的错误。2.自定义排序使用自定义排序的操作步骤为：（1）在“数据”菜单或功能区中打开“排序”对话框。Excel2003中，单击“数据”菜单，选定“排序”命令（如图4-84所示），打开“排序”对话框（如图4-85所示）；Excel2013中依次单击“数据”功能选项卡和“排序和筛选”功能组中的“排序”命令按钮，打开“排序”对话框，如图4-86所示。（2）在“排序”对话框中选定排序的条件、依据和次序。在Excel2003中的“排序”对话框中，可分别从“主要关键字”、“次要关键字”、“第三关键字”下拉对话框列出的“关键字”中选定排序的条件；从“升序”或“降序”选项按钮中选定排序的次序（如图4-85所示）。Excel2013“排序”对话框中，不仅可以通过点击“添加条件”按钮来添加多个“次要关键字”作为排序的条件，而且可以在“排序依据”下拉对话框中选择“数值”、“单元格颜色”、“字体颜色”或“单元格图标”作为排序的依据，如图4-87所示。（二）数据的筛选数据的筛选是指利用“数据”菜单中的“筛选”命令对数据清单中的指定数据进行查找和其他工作。筛选后的数据清单仅显示那些包含了某一特定值或符合一组条件的行，暂时隐藏其他行。通过筛选工作表中的信息，用户可以快速查找数值。用户不但可以利用筛选功能控制需要显示的内容，而且还能够控制需要排除的内容。1.快速筛选使用快速筛选的操作步骤为：（1）在数据清单中选定任意单元格或需要筛选的列；（2）执行“数据”菜单或功能区中的“筛选”命令，第一行的列标识单元格右下角出现向下的三角图标。Excel2003中，单击“数据”菜单后，进入“筛选”子菜单，选定“自动筛选”菜单命令（如图4-88所示）；Excel2013中，依次单击“数据”功能区选项卡、“排序和筛选”组中的“筛选”命令按钮，如图4-89所示。（3）单击适当列的第一行，在弹出的下拉列表中取消勾选“全选”，勾选筛选条件，单击“确定”按钮可筛选出满足条件的记录。2.高级筛选使用高级筛选的操作步骤为：（1）编辑条件区域；（2）打开“高级筛选”对话框。Excel2003中，单击“数据”菜单后，进入“筛选”子菜单，选定“高级筛选”菜单命令（如图4-88所示）；Excel2013中，依次单击“数据”功能选项卡、“排序和筛选”功能组中“高级”命令按钮（如图4-89所示）。（3）选定或输入“列表区域”和“条件区域”，单击“确定”按钮（如图4-90所示）。3.清除筛选对经过筛选后的数据清单进行第二次筛选时，之前的筛选将被清除。（三）数据的分类汇总数据的分类汇总是指在数据清单中按照不同类别对数据进行汇总统计。分类汇总采用分级显示的方式显示数据，可以收缩或展开工作表的行数据或列数据，实现各种汇总统计。1.创建分类汇总创建分类汇总的操作步骤为：（1）确定数据分类依据的字段，将数据清单按照该字段排序。（2）排序完成后，在数据菜单或功能区中打开“分类汇总”对话框。Excel2003中，单击“数据”按键，选定“分类汇总”菜单命令；Excel2013中，依次单击“数据”、“分级显示”功能组中的“分类汇总”命令按钮，如图4-91所示。（3）在“分类字段”下拉列表中选择分类依据的字段名，设置采用的“汇总方式”和“选定汇总项”的内容，单击“确认”按钮后完成设置。数据清单将以选定的“汇总方式”按照“分类字段”分类统计，将统计结果记录到选定的“选定汇总项”列下，同时可以通过单击级别序号实现分级查看汇总结果，如图4-92所示。2.清除分类汇总打开“分类汇总”对话框后，单击“全部删除”按钮即可取消分类汇总。（四）数据透视表的插入数据透视表是根据特定数据源生成的，可以动态改变其版面布局的交互式汇总表格。数据透视表不仅能够按照改变后的版面布局自动重新计算数据，而且能够根据更改后的原始数据或数据源来刷新计算结果。1.数据透视表的创建（1）打开需要创建数据透视表的工作簿。如果需要通过Excel数据清单或数据库建立报表，可以选中数据清单或数据库中的任意单元格。（2）单击“数据”菜单中的“数据透视表和数据透视图…”命令项，接着按“数据透视表和数据透视图向导”提示进行相关操作，如图4-93所示。具体步骤如下：在弹出的“步骤之1”对话框中的“请指定待分析数据的数据源类型”中选择“MicrosoftExcel数据列表或数据库”项；在“所需创建的报表类型”中选择“数据透视表”项，然后单击“下一步”按钮。在弹出的“步骤之2”对话框中核对系统自动定位到选中的区域是否正确（如图4-94所示），如果不正确或未选中，在对话框中输入数据源地址或者用鼠标点选数据源区域，单击“下一步”按钮。在弹出的“步骤之3”对话框中的“数据透视表显示位置”中选择“新建工作表”，单击“完成”按钮，如图4-95所示。（3）将自动生成的“数据透视表字段列表”中的各字段和数据项拖至数据透视表布局框架中的适当位置，自动生成相应版面布局的报表。数据透视表的布局框架由页字段、行字段、列字段和数据项等要素构成，可以通过需要选择不同的页字段、行字段、列字段，设计出不同结构的数据透视表，如图4-96所示。2.数据透视表的设置（1）重新设计版面布局。在数据透视表布局框架中选定已拖入的字段、数据项，将其拖出，将“数据透视表字段列表”中的字段和数据项重新拖至数据透视表框架中的适当位置，报表的版面布局立即自动更新。（2）设置值的汇总依据。值的汇总依据有求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体偏差、方差和总体方差。Excel2003中，可通过右键单击数据透视表的“计数项”单元格，选择“字段设置”（如图4-97所示），打开“数据透视表字段”对话框，选择“汇总依据”中的一种（如图4-98所示）；Excel2013中，可以通过右键单击数据透视表的“计数项”单元格，在“值汇总依据”菜单中选定其中一种，如图4-99所示。（3）设置值的显示方式。值的显示方式有无计算、百分比、升序排列、降