Excel数据列表的基本处理方法

数据列表的基本处理方法第一节基本概念数据列表是一个矩形表格，表中单元格没有进行过合并。数据列表的一行数据叫做一条记录数据列表的一列数据叫做一个字段，数据列表的每一列可以有一个名字——字段名，如果一个数据列表有字段名，则一定是在数据表列的第一行。图：数据列表的基本概念如果表格中有单元格由2个以上的单元格合并而成，那么这个这个表格就不是数据列表。图：非数据列表的例题对于数据列表，excel可以进行数据表的排序、筛选、分类汇总、做数据透视表、多表的合并计算等操作，也可以使用数据库函数对其进行处理等。而非数据列表不能做上述操作，也字段名1行叫做1条记录1列叫做1个字段这个单元格由两个单元格合并而成不能运用数据库函数等。第二节数据列表的数据筛选数据筛选是对一个数据列表，筛选出满足给定条件的数据行（记录）。对不满足条件的数据行（记录）进行隐藏，使其不显示出来。Excel的数据筛选分为自动筛选和高级筛选2种。一、自动筛选自动筛选是excel对使用“自动筛选”命令的数据列表的每一个字段的字段名（没有字段名的数据列表则是每一个字段的第1行）设置一个筛选条件设置下拉列表框，使用者可根据需要设置筛选条件。数据列表自动筛选的操作要点：1、选定需要进行数据筛选的数据列表；2、使用excel的数据列表“自动筛选”命令（使用该命令后，数据列表第1行各个字段傍出现设置筛选条件的下拉列表的按钮）；3、设置鼠标点击设置筛选条件的下拉列表的按钮，然后设置筛选条件，得到数据列表中满足筛选条件的记录。例：对如下的工资表进行自动筛选1、对工资表使用自动筛选命令2、筛选出财政局的所有职工筛选结果：1、选定工资表（不要包括表名）2、鼠标点击“筛选”命令按钮3、出现“自动筛选”设置条件按钮1、点击设置筛选条件按钮2、财政局复选框加钩，其余不加钩3、点击“确定”按钮注：虽然仅显示了4行数据，但其余行的数据仅被隐藏了，而没有被删除。3、筛选出基本工资在3000元以上的所有职工该符号说明该列设置了筛选条件1、点击设置筛选条件按钮2、点击“数字筛选”3、点击“大于(G)…”输入具体条件值可以设置第2个筛选条件筛选结果：4、筛选出财政局工资在3000元以上的所有职工该筛选要求需要对2个字段设置筛选条件，“单位”字段设置筛选条件选出所有财政局的职工，“基本工资”设置筛选条件选出工资在3000元以上的职工，这样最后的筛选结果就同时满足2个条件。筛选条件设置方法见前面的例题操作，筛选结果如下：注：excel自动筛选中，如果两个以上的字段设置了筛选条件，则这些筛选条件是“并且”的关系，即选出的记录只能是同时满足这些条件的记录，而不能选出只满足其中一个或一部分条件的记录。如果给出几个筛选条件，而要选出满足其中一个或一部分条件的所有记录，则需要使用高级筛选命令。二、高级筛选1、高级筛选的基本概念Excel的高级筛选是在数据列表的区域外，另外在一个矩形区域中根据筛选要求设置筛选条件，高级筛选命令则根据给定的数据列表的区域引用和筛选条件的区域引用，得到筛选条件，并对的数据列表进行筛选。高级筛选可灵活设置各种筛选条件，并对多个筛选条件进行组合，这种筛选条件的组合可以是要求几个条件同时成立，也可以是只要求一部分条件成立。要使用好excel的高级筛选，则必须掌握筛选条件的设置方法和组合方法。2、高级筛选条件的设置方法高级筛选的筛选条件是设置在一个矩形区域中，这个区域叫做条件区域，条件区域可以在工作表的任意区域，但不能与要进行筛选的数据列表重迭。条件区域的基本格式如下：字段名字段名……字段名条件11条件12条件1m条件21条件22条件2m……该符号说明该列设置了筛选条件2个字段同时设置了筛条件条件n1条件n2条件nm条件区域最少由2行1列构成，条件区域的第1行必须是数据列表中的字段名。条件ij的书写有一个的格式要求，格式为：关系运算符条件值由每一列的字段名与这一列的一个“条件ij”构成一个完成的筛选条件。如果一列中有多个条件，则构成多个筛选条件，同一列的多个条件为“或者”（OR）的关系。一个条件区域可以由多列构成，同一行同一列的条件为“并且”（AND）的关系。不同行的条件为“或者”（OR）的关系。例1：基本工资3000该条件区域构成的完成筛选条件为：基本工资3000即，基本工资大于3000元例2：基本工资30001000该条件区域构成2个条件，且这2个条件为“或者”（OR）关系：基本工资3000OR基本工资1000即基本工资大于3000元或基本工资小于1000元。例3：基本工资基本工资10003000这个条件区域构成2个条件，且这2个条件为“并且”（AND）关系：基本工资1000AND基本工资3000元即基本工资在1000元至3000元之间例4：基本工资单位3000财政局这个条件区域构成2个条件，且这2个条件为“并且”（AND）关系：基本工资3000AND单位=”财政局”即“财政局工资在3000元以上的”注：如果条件为等值，则不写等号（=），如果是文本值，在条件区域中不输入双引号。例5：基本工资单位3000财政局这个条件区域构成2个条件，且这2个条件为“或者”（OR）关系：基本工资3000OR单位=”财政局”即“基本工资在3000元以上的（不管是哪个单位的）或者单位是财政局的（不管工资高低）”。3、高级筛选的基本操作要点（1）在需要进行筛选操作的数据列表的同一工作表上选一矩形区域作为条件区域，并根据筛选要求设置筛选条件；（2）使用excel的“高级筛选”命令；（3）在“高级筛选”命令对话框中给定需要进行筛选的数据列表的单元格区域引用和条件区域的单元格区域引用；（4）如果筛选出来的数据需要另行存放，则给定存放筛选结果区域左上角单元格的引用（只需要一个单元格的引用即可）。如果筛选结果还需另行存放，则不需要做第（4）步。例：根据“自动筛选”例题中的工资表，使用“高级筛选”命令做如下筛选：（1）筛选出基本工资在3000元以上的所有职工。1、设置筛选条件2、点击“高级”命令按钮3、数据列表区域4、筛选条件区域筛选结果：筛选结果另行存放：筛选结果1、点击该选项2、说明筛选结果存放在A20开始的区域（2）筛选出财政局基本工资在2000元至3500元的所有职工筛选结果：第三节数据列表的排序数据列表的排序是指将数据列表中的每一条记录根据其在指定字段中的值大小重新确定该条记录中的位置。例如在工资表中，可以根据根据单位名称进行排序，即同一个单位的职工排在一起，如下所示。图：工资表原表图：按“单位”排序后的工资表图：以“单位”为主关键字“基本工资”为次要关键字排序后的工资表以某一字段为依据进行排序，则这一字段就叫做关键字，例如，将同一单位的职工排在一起，则“单位”字段就是关键字段。在排序时还可以根据2个或2个以上的关键字对数据列表进行排序，例如先将工资表中职工按单位排在一起，然后同一单位的职工则按基本工资从高到低的顺序进行排列。在这种情况下，按排序依据（关键字）的先后顺序依次叫做“主关键字”、“次要关键字”。在上述例子中，“单位”是主关键字，“基本工资”为次要关键字。在排序时，可根据关键字值从小到大排序——升序，也可根据关键字值从大到小排序——降序。例如在学生成绩表中可以按平均成绩从低到高进行排序，也可按从高到低的顺序进行按序。排序操作的基本要点：1、选定要进行排序的数据列表；2、使用excel的“排序”命令；3、设置排序参数——排序的关键字及升序或降序。例：对学生成绩表按平均成绩进行排序。图：排序命令的使用排序结果：1、选择要排序的数据列表2、点击“排序”3、选择排序关键字4、选择“升序”或“降序”图：学生成绩表按“平均成绩”从低到高（升序）排序后的结果图：学生成绩表按“平均成绩”从高到低（降序）排序后的结果第四节数据列表的分类汇总在排序时，有时数据列表中的关键字仅有若干个取值，那么排后数据列表的记录就按关键字的值被分为这若干个类。分类汇总就是对数据列表排序后形成的若干类的每一类按一定的汇总方式进行汇总。例如求出每一类中的最大值、最小值、平均值、总和或每一类中的记录数等。分类汇总的操作要点：1、选择要进行分类汇总的数据列表；2、按关键字对数据列表进行排序（如果已经排序，则这一步可省略）；3、使用“分类汇总”命令；4、设置分类汇总参数：分类字段——排序的关键字汇总方式——对数据列表的每一类记录以哪一种方式进行汇总，可以是：求每类中数值数据是总和、平均值、最大值、最小值、方差、标准差、总体方差等，也可求每一类的记录数汇总项——分类汇总可以对数据列表中每个字段分别求总和、平均值等，这部分参数设置说明要对数据列表中的哪些字段的值进行汇总，需要计算汇总值的字段就就加钩。替换当前分类汇总——如果在此之前使用过分类汇总命令，选中该选项则替换掉以前的汇总数据，如果该选未选中，则保留以前的汇总数据。汇总结果显示在数据下方——该选项选中，则汇总后的数据在每一类记录的下方加一行，汇总值放在相应的字段。未选中该选项，则汇总后的数据在每一类记录的上方加一行，汇总值放在相应的字段。全部删除——删除该数据列表中已有的所有汇总数据，仅保留数据列表的原始数据。例：在工资表中汇总得到每一个单位的“基本工资”和“应发工资”总和图：排序后原始工资表图：分类汇总命令图：分类汇总参数设置图：按单位对“基本工资”和“应发工资”求和汇总后的结果如果数据列表未按分类字段排序则不能得到正确的汇总数据，下面是未按“单位”排序的工资表，求各个单位的基本工资和应发工资总和的例子。数据列表必须按分类字段排序此处说明只对“基本工资”和“应发工资”求和汇总总计为一级数据每一个单位的合计为二级数据一个职工的数据为三级数据说明显示几级数据图：未按“单位”排序的工资表图：未按“单位”排序的工资表分类汇总的结果第五节数据透视表一、数据透视表的基本概念数据透视表是根据数据列表中的2个分类字段形成的一个二维汇总表。如下所示：1、原始数据列表图：商品销售表（数据有隐藏）2、商品销售金额汇总表（数据透视表）图：根据商品销售流水账表得到的全体售货员的销售金额统计表图：根据商品销售流水账表得到的售货员陈实的销售金额统计表图：根据商品销售流水账表得到的售货员陈实的销售金额统计表3、行字段、列字段、页字段、汇总字段的概念商品销售流水账表中可以看出该表记录了每一天每个售货员销售的每一笔商品的名称、单价、数量及金额。在第1张透视表中，统计的是全体售货员在8月1日至8月7日这段时期，每一种商品的销售金额。在第2张透视表中，统计的仅仅是售货员陈实在8月1日至8月7日这段时期，每一种商品的销售金额。从数据透视表中可以看出，商品销售流水账表中的商品名称构成了数据透视表的行，所以这个字段就叫做行字段，同理构成数据透视表列的字段叫做列字段。还可以以一个字段的某一个值做一张数据透视表，如第1张数据透视表是根据全体售货员做的数据透视表，而第2张数据透视表和第3张数据透视表则只是售货员陈实在8月1日至8月7日的销售数据汇总表。如果根据原始数据列表中的某个字段的某一个值可以做一张数据透视表，则这个字段叫做而字段。原始数据列表中的各个字段均可做为数据透视表的中的行字段、列字段及页字段，且没有限制，如原始数据列表中的日期字段既可做为行字段也可做为列字段，见上面的图。同样，日期字段也可做为页字段，如下图所示：图：以日期作页字段、商品名称作行字段、售货员为列字段的数据透视表汇总字段——数据透视表中汇总数据所依据的字段。在上述例子中，汇总字段是销售金额，也可以是其他字段，例如也可把销售数量作为汇总字段。与分类汇总一样，数据透视表也可有多种汇总方式，即求和、求平均值、最大值，……，等。二、数据透视表的操作要点1、选定要做数据透视表的原始数据列表（不需要排序）；2、运用excel的数据透视表命令，并根据数据透视表向导进行操作（可在向导的第一步就结束）；本步骤结束后得到数据透视表的框架；3、设置数据透视表的页字段（可以省略）、行字段、列字段、汇总字段及汇总方式。4、对数据透视表进行详细调整（如几列合并成一列，几