第四节 数据清单及其管理分析

1、第四节 数据清单及其管理分析一、数据清单的构建（）（一） 数据清单的概念Excel中,数据库是通过数据清单或列表来实现的。数据清单是一种包含一行列标题和多行数据且每行同列数据的类型和格式完全相同的Excel工作表。数据清单中的列是数据库中的字段,数据清单中的列标志是数据库中的字段名称,数据清单中的每一行对应数据库中的一条记录。（二）构建数据清单的要求1. 列标志应位于数据清单的第一行,作为字段名,用以查找和组织数据、创建报告。2. 同一列中各行数据项的类型和格式应当完全相同。3. 避免在数据清单中间放置空白的行或列，但需将数据清单和其他数据隔开时，应在他们之间留出至少一个空白的行或列。4.尽量

2、在一张工作表上建立一个数据清单。【例题单选题】下列关于Excel数据清单的说法中，不正确的是（）。A.列标志应位于数据清单的第一行B.数据清单中的列是数据库中的字段C.每一行对应数据库中的一条记录D.同一行中各列数据项的类型和格式应当完全相同正确答案D答案解析同一列中各行数据项的类型和格式应当完全相同。二、记录单的使用（）（一） 记录单的概念记录单又称数据记录单,是快速添加、查找、修改或删除数据清单中相关记录的对话框。（二） 通过记录单处理数据清单的记录1.通过记录单处理记录的优点界面直观,操作简单,减少数据处理时行列位置的来回切换,避免输入错误,特别适用于大型数据清单中记录的核对、添加、查找

3、、修改或删除。2.记录单对话框的打开Excel2003, “数据”菜单“记录单”Excel2013,“快速访问工具栏”“记录单”或ALT+D,然后按O(字母键)在快速访问工具栏中加入记录单的方法：文件-选项-快速访问工具栏-不在功能区中的命令-记录单-添加【提示】打开记录单对话框以后，只能通过“记录单”对话框来输入、查询、核对、修改或者删除数据清单中的相关数据,无法直接在工作表的数据清单中进行相应的操作。3.在“记录单”对话框中输入新记录4.利用“记录单”对话框查找特定单元格5.利用“记录单”对话框核对或修改特定记录6.利用“记录单”对话框删除特定记录【例题判断题】打开记录单对话框以后，仍可以

4、直接在工作表的数据清单中进行相应的操作。（）正确答案×答案解析打开记录单对话框以后，只能通过“记录单”对话框来输入、查询、核对、修改或者删除数据清单中的相关数据,无法直接在工作表的数据清单中进行相应的操作。操作实例：打开“数据清单构建.xls”工作簿，完成如操作：（1）打开“操作实例”工作表，通过“记录单”，将如下两条记录分别添加现有记录单中；编号科室姓名基本工资补助工资应发工资养老金公积金扣款合计实发工资11人事科王振宇1250.00300.001550.00124.0077.50201.501348.5012人事科苏小燕1470.00360.001830.00146.4091.5

5、0237.901592.10（2）在上述记录单中，查找姓名为“石卫国”的记录单，并将其中的“基本工资”修改为4200元；（3）删除姓名为“杨圣洪”的记录。三、数据的管理与分析Excel可以方便地对数据清单中的数据进行管理和分析，包括排序、筛选、分类汇总、插入图表和数据透视表等数据管理和分析功能。（一）数据的排序数据的排序是指在数据清单中，针对某一列或某几列的数据（有时又称关键字），通过“数据”菜单或功能区中的排序命令来重新组织行的顺序，通常有升序和降序排列两种方式。1、快速排序使用快速排序的操作步骤为：（1）在数据清单中选定需要排序的各行记录，如果列标题只有一行的话，也可以只选定其中的某一个单

6、元格；（2）执行工具栏或功能区中的排序命令（这时要选定其中的一个单元格，且只能对选定的单列进行排序），也可以通过“数据排序”菜单执行。教材p195中的注意事项不很准确，需注意。需要注意的是，如果数据清单由单列组成，即使不执行第一步，只要选定该数据清单的任意单元格，直接执行第二步，系统都会自动排序；如果数据清单由多列组成，应避免不执行第一步而直接执行第二步的操作，否则数据清单中光标所在列的各行数据被自动排序，但每一记录在其他各列的数据并未随之相应调整，记录将会出现错行的错误。以面说的只有一种情况，就是对含有多列的数据清单，只选中其中的一列，对其进行排序时，在“给出排序依据”中选择“以当前选定区域

7、排序”，这时才只对本列排序，其他列不作相应调整。2、自定义排序快速排序时，排序的方向、方法默认的，且字母不区分大小写，即排序的方向按列方向，字符按字母顺序，英文不分大小写，如果要对其进行调整，则要用到自定义排序功能。使用自定义排序的操作步骤为：（1）选择数据清单中的所有记录或记录中的某一单元格；（2）在“数据”菜单或功能区中打开“排序”对话框；（2）在“排序”对话框中，点击“选项”按钮，选定排序的条件、依据和次序。默认情况下，对文字的排序是按其拼音第一个字母排序，如果采用自定义排序，则可按“姓氏笔划”排序，这更符合中国人的习惯。操作实例打开“排序.xls”工作簿：（1）在sheet40工作表中

8、，以“部门”为主要关键字递减，以“姓名”为次要关键字递增排序，并设置A1：H2区域的底纹颜色为黄色；（2）在Sheet12工作表中按“科室”递减、同科室人员按“生日”递增、同生日人员按其“姓名”的笔划顺序递增进行排序。（二）数据的筛选数据的筛选是指利用“数据”菜单中的“筛选”命令对数据清单中的指定数据进行查找和其他工作。筛选后的数据清单仅显示那些包含了某一特定值或符合一组条件的行，暂时隐藏其他行。通过筛选工作表中的信息，用户可以快速查找数值。用户不但可以利用筛选功能控制需要显示的内容，而且还能够控制需要排除的内容。Excel中提供了两种数据的筛选操作，即“自动筛选”和“高级筛选”。自动筛选是一

9、种快速的筛选方法，它可以方便地将那些满足条件的记录显示在工作表上，高级筛选可进行复杂的筛选，挑选出满足多重条件的记录。1、快速筛选“快速筛选”也叫“自动筛选”，一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据，使用快速筛选的操作步骤为：（1）在数据清单中选定任意单元格或需要筛选的列；（2）执行“数据”菜单或功能区中的“筛选”命令，第一行的列标识单元格右下角出现向下的三角图标；（3）单击适当列的第一行，在弹出的下拉列表中取消勾选“全选”，勾选筛选条件，单击“确定”按钮可筛选出满足条件的记录；（4）自动筛选可以同时对多个列进行限定。操作实例打开“数据筛选.xls”

10、工作簿：在“自动筛选1”工作表中完成以下操作： 第一步：用填充柄自动填充“序号”列，从“19990301”开始，按步长为1的等差序列填充，完成后A12单元格的内容应为“19990310”； 第二步：建立一个“自动筛选”的查询器，筛选出“跳远”成绩等于30的记录。在“自动筛选2”工作表中完成以下操作：建立一个“自动筛选”的查询器，筛选出“基本工资”大于2000（不含2000）且小于5000（不含5000）的全体人员。这里要用到“自定义项”，其中的“与”是表示同时满足多个条件的意思，“或”即表示在多个条件中满足其中之一的意思。在“自动筛选3”工作表中完成以下操作：建立一个“自动筛选”的查询器，筛选

11、出“人事科”中“基本工资”大于100的人员。这道题要求对两个以上的列进行同时限定条件筛选，做题时可按顺序分别对各列进行设置，全部条件设置完成后再保即可。在“自动筛选4”工作表中完成以下操作：第一步：筛选“人事科”中基本工资大于或等于1000并且补助工资小于或等于200的人员；第二步：将筛选出的符合条件的最后一行字体设置为黑体、粗体。2、高级筛选“高级筛选”一般用于条件较复杂的筛选操作，其筛选的结果可显示在原数据表格中，不符合条件的记录被隐藏起来；也可以在新的位置显示筛选结果，不符合条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的比对了。使用高级筛选的操作步骤为：（1）编辑

12、条件区域；（2）打开“高级筛选”对话框；（3）选定或输入“列表区域”、“条件区域”和“结果区域”（如果要保留原有数据不变的话），单击“确定”按钮。注意：结果区域只需指定起始单元格即可自动延伸。操作实例：分别打开“高级筛选1”、“高级筛选1” 、“高级筛选3”、“高级筛选4”工作表，应用“高级筛选”方法按上述要求进行重新筛选，并保留原有数据不变，复制地址自行选定。3、清除筛选对经过筛选后的数据清单进行第二次筛选时，之前的筛选将被清除，也可以通过“数据筛选”，去掉“自动筛选”或“高级筛选”子菜单前面的“勾”清除所有筛选设置。（三）数据的分类汇总数据的分类汇总是指在数据清单中按照不同类别对数据进行汇

13、总统计。分类汇总采用分级显示的方式显示数据，可以收缩或展开工作表的行数据或列数据，实现各种汇总统计。在Excel中，分类汇总的方式有求和、平均值、最大值、最小值、偏差、方差等十多种，常用的是对分类数据求和或平均值。通过分类汇总，可以得到需要的统计信息。在进行分类汇总之前，必须按“分类字段”对数据清单进行排序，使同一类的记录集中在一起。1、创建分类汇总（1）确定数据分类依据的字段，并将数据清单按该字段进行排序（升序降序都可）；（2）选择数据清单中的任一单元格，通过“数据分类汇总”菜单打开分类汇总对话框；（3）设定分类汇总选项（包括分类字段、汇总方式、汇总项目），单击“确定”，完成分类汇总操作。2

14、、清除分类汇总打开“分类汇总”对话框后，单击“全部删除”按钮即可取消分类汇总。操作实例：打开“分类汇总练习题.xls”工作簿(1) 打开sheet1工作表进行操作：按“科室”递增排序；按“科室”为分类字段进行分类汇总，分别将每个科室“应发工资”的汇总求和，汇总结果显示在数据下方。(2) 打开sheet2工作表进行操作：按“车间”递减排序；设置A1：H1单元格区域的底纹颜色为红色；按“车间”为分类字段进行分类汇总，分别汇总出每个车间的“补助工资”的最小值，汇总结果显示在数据下方。(3) 打开sheet3工作表进行操作：以“部门”为主要关键字递减，以“姓名”为次要关键字递增排序，并设置A1：H2区

15、域的底纹颜色为黄色；按“部门”为分类字段进行分类汇总，分别汇总出每个部门的“补助工资”的最大值，汇总结果显示在数据下方。(4) 打开sheet4工作表进行操作：按“科室”递减排序；按“科室”为分类字段进行分类汇总，分别算出每个科室的“基本工资”和“实发工资”的总和，汇总结果显示在数据下方。（四）数据透视表数据透视表是根据特定数据源生成的，可以动态改变其版面布局的交互式汇总表格。数据透视表不仅能够按照改变后的版面布局自动重新计算数据，而且能够根据更改后的原始数据或数据源来刷新计算结果。1、数据透视表的创建（1）打开需要创建数据透视表的工作簿；（2）单击“数据”菜单中的“数据透视表和数据透视图”命

16、令项，接着按“数据透视表和数据透视图向导”提示进行相关操作可创建数据透视表。数据源类型选择excel数据，报表类型选择数据透视表；选择数据源区域，系统默认选择整个数据清单，用户可根据需要进行调整；确定数据透视表显示位置，可以在当前工作表中选定一个起始单元格，也可以在新建的工作表中显示；分别点击“布局”和“选项”进行相应设置后，点击“完成”按扭完成数据透视表创建。2、数据透视表的设置数据透视表的布局框架由页字段、行字段、列字段和数据项等要素构成，可以根据需要选择不同的页字段、行字段、列字段，设计出不同结构的数据透视表。（1）重新设计版面布局。在数据透视表布局框架中选定已拖入的字段、数据项，将其拖

17、出，将“数据透视表字段列表”中的字段和数据项重新拖至数据透视表框架中的适当位置，报表的版面布局立即自动更新。（2）设置值的汇总依据。值的汇总依据有求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体偏差、方差和总体方差，可以选择其中之一，默认是求和。（3）设置值的显示方式。值的显示方式有无计算、百分比、升序排列、降序排列等，EXCEL2003在字段设置的选项中，EXCEL2013中，可通过右键单击数据透视表的“计数项”单元格，在“值的汇总方式” 中选定其中一种。（4）进行数据的筛选。分别对报表的行和列进行数据的筛选，系统会根据条件自行筛选出符合条件的数据列表。（5）设定报表样式。

18、数据透视表中，既可通过单击“自动套用格式”（适用于Exce12003，单击“格式”菜单后进入）或“套用报表格式”（适用于Exce12013）按钮选用系统自带的各种报表样式，也可通过设置单元格格式的方法自定义报表样式。操作实例打开“数据透视操作.xls”工作簿，完成如下操作：（1）选择sheet1410工作表，为数据区域B2:C8在当前工作表D2:E8上创建数据透视表操作提示：点击数据菜单下的“数据透视表与数据透视图”；分析数据源指定为excel，报表类型选择“数据透视表”；选定分析区域为B2:C8，显示区为现有工作表的D2:E8区域；直接点击完成按扭完成本题操作。（2）选择sheet1414工

19、作表，为数据区干域A1:D5在当前工作表A11:D15上创建数据透视表，将化学成绩拖入到数据项中，并汇总出它的最大值。操作提示：点击数据菜单下的“数据透视表与数据透视图”；分析数据源指定为excel，报表类型选择“数据透视表”；选定分析区域为A1:D5，显示区为现有工作表的A11:D15区域；先点击完成后，再将化学成绩拖到数据项；双击A11单元格，即求和项所在的单元格，在弹出的“数据透视表字典”中，将汇总方式改为最大值后确定。（3）选择sheet1415工作表，为数据区域A1:D6在新工作表中创建数据透视表，并将姓名拖入到页字段显示区，并显示出“李明”。操作提示：点击数据菜单下的“数据透视表与

20、数据透视图”；分析数据源指定为excel，报表类型选择“数据透视表”；选定分析区域为A1:D6，显示区为新工作表；先点击完成后，再将姓名拖到页字段显示区；页字段下拉菜单中选择李明并确定。（4）选择sheet5工作表，为当前数据透视表设置格式为“报表2”操作提示：点击格式菜单下的“自动套用格式”；选择“报表2”后确定；（5）选择sheet1417工作表，将第9行删除后，刷新sheet9工作表中对应的数据透视表。操作提示：删阶sheet1417工作表第9行选择工作表sheet9，在数据透视表中右击鼠标，在弹出的快捷菜单中，选择“选定整张表格”；再次右击数据透视表，在弹出的快捷菜单中，选择“刷新数据

21、”。（6）将sheet10中的数据透视表删除。操作提示：选择工作表sheet10，在数据透视表中右击鼠标，在弹出的快捷菜单中，选择“选定整张表格”；在编辑菜单的下级菜单中，点击“删除”子菜单。（五）图表的插入框选需要生成图表的数据清单、列表或者数据透视表，选择“插入”菜单中的“图表”菜单，按照相关步骤操作可完成图表的插入。图表不仅可以根据需要分别输入标题和各轴所代表的数据含义，而且可以适当调整大小及其位置。具体操作如下：1、编辑数据表格，并选中其中的一个单元格；2、通过主菜单上的“插入图表”或工具栏上的“插入图表”按扭插入图表；3、选择图表类型，如折线状、柱状、饼状等；4、选择数据区域（可以是

22、整个表格，也可以是表格中的某几行或某几列，不连续区域之间用半角的逗号分开）；5、选择系列产生方式（行或列）,即水平方向上的项目名称；6、设置图表标题（包括图表标题、轴名称、轴名称）；7、设置坐标轴分类、网络线、图例显示位置、数据标称志；8、设置图表的位置（作为新工作表插入还是作为其中的对象插入，注意：图表与数据只能放在同一工作簿的同一工作表或不同工作表中），完成图表的插入；9、设置图表区格式与绘图区格式（通过在图表区与绘图区单击鼠标右键弹出属性菜单操作）；10、修改图表元素(1)图表元素通常包括坐标轴（分类轴和数值轴）、绘图区、数值轴主要网格线、图表标题、图表区、系列等;(2)修改图表选项的方式有：A.选择某一图表元素，在格式菜单中选择相应的图表元素命令，在弹出的格式对话框中进行格式设置B.双击图表中的某图表元素，在显示出来的格式对话框中进行格式设置C.在打开的图表工具栏中，选取相应的图表元素，单击图表工具栏中的格式化工具按钮，进行格式设置D.将鼠标指针指向要格式化的图表元素，单击鼠标右键，在弹出的快捷菜单选取格式化命令，在弹出的格式对话框中进行格式设置操作实例打开“图表练习.xls”工作簿，完成如下操作：（1）在Sheet33工作表中完成以下操作：分别计算每月的合计，将