《Excel操作》-第2章 Excel数据管理

本章主要介绍Excel数据管理的相关知识，包括数据的排序、筛选、分类汇总以及数据透视表等。本章简介本章重点重点、难点数据的排序；数据的筛选；分类汇总；定义名称的作用与方法。本章难点数据透视表的建立。2.1数据清单2.2数据排序2.3数据筛选2.4分类汇总2.5数据的有效性本章目录2.6数据透视表2.1数据清单在Excel中，数据的管理需要在数据清单中进行，数据清单是由工作表的单元格构成的满足特定条件的矩形区域。数据清单具有以下的特点。1数据清单的一列称为一个字段，一行称为一条记录，第一行为表头，由若干字段名组成，其余行是数据清单中的数据。23在一个工作表中，只能建立一个数据清单。如果一个工作表中既有数据清单，又有其他数据，则数据清单和其他数据之间要有一个空白行或空白列。在一个数据清单中，不允许出现空列或空行，避免使用空格。4数据清单中每一列数据必须性质相同、类型相同。2.2.2多条件排序2.2.3自定义条件排序2.2.1简单排序2.2数据排序2.2.1简单排序简单排序是指对数据清单中的某一个列（字段）进行排序，排序时，只需要单击此列中任一单元格，再单击常用工具栏上的升序按钮或降序按钮即可快速排序。2.2.2多条件排序按照一个字段进行排序，有时会遇到列中某些数据相同，这时就需要采用第二个字段进行排序，这就是多条件排序，Excel2003最多支持一次三个字段的复杂排序。2.2.2多条件排序例2-1 打开“2012—2013学年信息系学生成绩.xls”工作簿，对Sheet1工作表中的数据清单按“总分”降序，“总分”相同时再按“程序设计”降序排序。具体的操作步骤如下。单击数据清单中的任意一个单元格。选择“数据”→“排序”菜单命令，打开“排序”对话框。在“排序”对话框中的“主要关键字”下拉列表框中选择“总分”选项，单击右边的“降序”单选框；在“次要关键字”下拉列表框中选择“程序设计”选项，单击右边的“降序”单选框，如图2-1所示123单击“确定”按钮，排序完成，效果如图2-2所示。图

2-1设置多条件排序图

2-2多条件排序2.2.3 自定义条件排序在实际的应用中，有时候汉字和数值的排序可能比较特殊，既不是按数值的大小次序排序，也不是按汉字的字母次序或笔画次序排序，而是按指定的一种特殊的次序排列，如常见的月份、星期、区域等。例2-2打开“2012—2013学年优秀学生干部花名册.xls”工作簿，对Sheet1工作表中的数据清单按“籍贯”的地域进行排序，地域的排序如下：南宁、柳州、桂林、梧州、北海、来宾、玉林、河池、百色、钦州、防城港、贵港。具体的操作步骤如下。2.2.3 自定义条件排序第一步：建立自定义序列。方法如下：选择“工具”→“选项”命令，弹出“选项”对话框，在“选项”对话框中选择“自定义序列”选项卡，在“输入序列”文本框中输入自定义序列。每输入一个地域名称后按Enter键，自定义序列输入完成后，单击“添加”按钮，添加自定义序列，如图2-3所示。132图2-3自定义序列单击“确定”按钮，完成自定义序列的设置。2.2.3 自定义条件排序第二步：按前面设置的自定义序列排序。方法如下：4321单击数据表中的任一非空单元格，选择“数据”→“排序”菜单命令，打开“排序”对话框，在“排序”对话框中的“主要关键字”下拉列表框中选择“籍贯”选项，并选择排序方式为“升序”，如图2-4所示。图2-4设置排序的主要关键字单击“选项”按钮，弹出“排序选项”对话框。在“自定义排序次序”下拉列表中选择前面建立的自定义序列，如图2-5所示。图2-5选择自定义排序次序单击“确定”按钮，最后得到的排序结果如图2-6所示。图2-6自定义条件排序2.3.2高级筛选2.3.1自动筛选2.3数据筛选2.3.1自动筛选自动筛选是按选定内容筛选，它适用于简单条件筛选。自动筛选命令在数据清单的各字段名的右下角显示一个下拉箭头，单击该箭头就出现升序排序、降序排序、全部、前10个、自定义和这一列中所有出现过的值。2.3.1自动筛选例2-3打开“优秀学生干部花名册.xls”工作薄，在Sheet1工作表中，通过自动筛选的方法查看数统学院的学生信息。具体的操作步骤如下：1单击Sheet1工作表中的任一非空单元格。2选择“数据”→“筛选”→“自动筛选”命令。此时，在数据表第一行的每一列标题右边会添加一个黑色下拉列表标志，如图2-7所示。图2-7自动筛选的下拉列表标志2.3.1自动筛选例2-3打开“优秀学生干部花名册.xls”工作薄，在Sheet1工作表中，通过自动筛选的方法查看数统学院的学生信息。具体的操作步骤如下：图2-7自动筛选的下拉列表标志3单击“所属学院”的下拉箭头，选择列表中的“数统学院”，系统就会自动把“所属学院”列中不是“数统学院”的学生数据行隐藏起来，只显示所属学院为“数统学院”的学生信息，如图2-8所示。2.3.1自动筛选例2-4打开“优秀学生干部花名册.xls”工作薄，在Sheet1工作表中，通过自动筛选的方法查看“综合评分”大于或等于90的学生信息。具体的操作步骤如下：1采用与前面例2-3的一样方法，对数据表设置自动筛选，让每个列表头的右边出现黑色的下拉列表标志。2单击“综合评分”的下拉箭头，选择列表中的“自定义”选项，打开“自定义自动筛选方式”对话框。在对话框中，选择筛选条件为“大于或等于”，然后在后面的方框中输入“90”，如图2-9所示。图2-9“自定义自动筛选方式”对话框2.3.1自动筛选例2-4打开“优秀学生干部花名册.xls”工作薄，在Sheet1工作表中，通过自动筛选的方法查看“综合评分”大于或等于90的学生信息。具体的操作步骤如下：图2-10自动筛选“综合评分”大于或等于90的结果3单击“确定”按钮，系统将自动从表格中筛选出“综合评分”大于或等于90的学生数据，如图2-10所示。2.3.2高级筛选自动筛选只能完成简单的数据筛选，如果筛选条件比较复杂，就需要采用高级筛选来完成。高级筛选可以查找较为复杂的“与”、“或”条件数据，突破了自动筛选对一列数据最多只能应用两个条件的限制，可以对一列数据应用三个或更多的条件筛选。高级筛选需要定义3个单元格区域：3

数据区域：数据清单区域。存放筛选出来记录的区域：可以是原来的数据区域，也可以是其他区域。2

条件区域：至少两行，而且首行必须是与数据清单相应字段完全相同的字段。12.3.2高级筛选例2-5打开“2012—2013学年优秀学生干部花名册.xls”工作簿，在Sheet1工作表中，将“所属学院”为“法律学院”，或者“籍贯”为“桂林”的女同学筛选出来，显示在原数据的下方。具体的操作步骤如下。21打开“2012—2013学年优秀学生干部花名册.xls”工作簿，在任意空白的单元格中输入条件，这里在K2单元格中输入文本“性别”，在K3、K4单元格中输入“女”；在L2单元格中输入文本“所属学院”，在L3单元格中输入“法律学院”；在M2单元格中输入文本“籍贯”，在M4单元格中输入“桂林”，如图2-11所示。2选中数据区域中的任意一个单元格，选择“数据”→“筛选”→“高级筛选”命令，打开“高级筛选”对话框。图2-11设置的条件区域2.3.2高级筛选例2-5打开“2012—2013学年优秀学生干部花名册.xls”工作簿，在Sheet1工作表中，将“所属学院”为“法律学院”，或者“籍贯”为“桂林”的女同学筛选出来，显示在原数据的下方。具体的操作步骤如下。3在“高级筛选”对话框中，选中“方式”栏下的“将筛选结果复制到其他位置”单选按钮。4单击“列表区域”文本框后的按钮，打开“高级筛选-列表区域”对话框，在“优秀学生干部花名册.xls”表格中选择A2:H17单元格区域，然后单击“列表区域”文本框后的按钮，返回“高级筛选”对话框。2.3.2高级筛选例2-5打开“2012—2013学年优秀学生干部花名册.xls”工作簿，在Sheet1工作表中，将“所属学院”为“法律学院”，或者“籍贯”为“桂林”的女同学筛选出来，显示在原数据的下方。具体的操作步骤如下。采用与前面步骤一样的方法选择K2:M4单元格区域作为“条件区域”，单元格A21作为“复制到”的位置，如图2-12所示。5图2-12“高级筛选”对话框2.3.2高级筛选例2-5打开“2012—2013学年优秀学生干部花名册.xls”工作簿，在Sheet1工作表中，将“所属学院”为“法律学院”，或者“籍贯”为“桂林”的女同学筛选出来，显示在原数据的下方。具体的操作步骤如下。单击“确定”按钮，关闭“高级筛选”对话框，系统将表格中符合条件的学生记录筛选出来，最终的效果如图2-13所示。6图2-13高级筛选的结果2.4.2分类汇总的删除2.4.1分类汇总的创建2.4分类汇总2.4.1分类汇总的创建分类汇总就是对数据清单按某个字段进行分类，将字段值相同的记录作为一类，再进行求和、求平均、计数等汇总运算。分类汇总采用分级显示的方式显示工作表数据，可以收缩或展开工作表的数据行（或列），可以快速创建各种汇总报告。在做分类汇总前，必须先对要分类的字段进行排序，从而实现记录分类。2.4.1分类汇总的创建1.简单分类汇总对数据清单中的一个字段仅进行一种方式的汇总，称为简单汇总。下面举例讲解创建简单分类汇总的方法。例2-6打开“2013年8月东丽公司工资表.xls”工作簿，在Sheet1工作表中，求每个部门的平均基本工资、平均奖金。具体的操作步骤如下。2.4.1分类汇总的创建选中“部门”字段中的任一单元格，单击工具栏上的升序或降序按钮进行排序。0102例2-6打开“2013年8月东丽公司工资表.xls”工作簿，在Sheet1工作表中，求每个部门的平均基本工资、平均奖金。具体的操作步骤如下。选择菜单栏中的“数据”→“分类汇总”命令，打开“分类汇总”对话框。在对话框的“分类字段”下拉列表框中选择“部门”，在“汇总方式”下拉列表框中选择“平均值”，在“选定汇总项”拉列表框中选择“基本工资”和“奖金”选项，如图2-14所示。。图2-14“分类汇总”对话框2.4.1分类汇总的创建03例2-6打开“2013年8月东丽公司工资表.xls”工作簿，在Sheet1工作表中，求每个部门的平均基本工资、平均奖金。具体的操作步骤如下。单击“确定”按钮，完成求“基本工资”和“奖金”的分类汇总，结果如图2-15所示。图2-15分类汇总的结果2.4.1分类汇总的创建2.多重分类汇总Excel可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据时，只需对数据表进行两次不同的汇总运算，第二次分类汇总在第一次的汇总结果上进行。2.4.1分类汇总的创建2.多重分类汇总例2-7打开“2013年8月东丽公司工资表.xls”工作簿，在Sheet1工作表中，同时查看各部门的平均基本工资、平均奖金和各部门的销售提成总额、失业险总额。具体的操作步骤如下。12按照例2-6的方法先对各部门进行平均基本工资、平均奖金的分类汇总。单击第一次汇总表的任一非空单元格，选择“数据”→“分类汇总”命令，打开“分类汇总”对话框。2.4.1分类汇总的创建3再次设置“分类汇总”对话框，“分类字段”选择“部门”，“汇总方式”选择“求和”，“选定汇总项”选择“销售提成”和“失业险”，取消“替换当前分类汇总”复选框，单击“确定”按钮，完成第二次分类汇总，结果如图2-16所示。图2-16第二次分类汇总的结果2.4.1分类汇总的创建3.嵌套分类汇总在一个已经建立分类汇总的汇总表中进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果上操作，第三次汇总在第二次的结果上操作，其余以此类推。建立嵌套分类汇总的前提也是要对每个分类汇总关键字排序，第一级汇总关键字应该是排序的第一排序关键字，第二级汇总关键字应该是第二排序关键字，其余依此类推。2.4.1分类汇总的创建3.嵌套分类汇总例2-8打开“2013年8月东丽公司工资表.xls”工作簿，在Sheet1工作表中，同时查看各部门的平均基本工资、平均奖金和各部门男、女员工的人数。具体的操作步骤如下。以“部门”为第一关键字，“性别”为第二关键字对数据表进行排序。第一步：第二步：第三步：按照例2-7的方法先对各部门进行平均基本工资、平均奖金的分类汇总。单击第一次汇总表的任一非空单元格，选择“数据”→“分类汇总”命令，打开“分类汇总”对话框。2.4.1分类汇总的创建3.嵌套分类汇总再次设置“分类汇总”对话框，“分类字段”选择“性别”，“汇总方式”选择“计数”，“选定汇总项”选择“性别”，取消“替换当前分类汇总”复选框，完成第二次分类汇总，结果如图2-17所示。第四步：图2-17第二次分类汇总的结果2.4.2 分类汇总的删除对于刚刚建立的分类汇总表，可以选择“编辑”→“撤销”菜单命令取消分类汇总，或者单击工具栏中的撤销按钮进行恢复。但对已经建好的分类汇总表，可按下面的方法删除分类汇总：单击分类汇总表中的任一单元格，选择“数据”→“分类汇总”菜单命令，在弹出的“分类汇总”对话框中单击“全部删除”按钮，数据表中的分类汇总就被删除，汇总表恢复成汇总前的数据表。2.5.2限制输入重复数据2.5.1动态下拉菜单快速输入数据2.5数据的有效性2.5.1动态下拉菜单快速输入数据1.使用一级菜单的方法快速输入数据例2-9打开“鸿雁食品公司员工信息2.xls”工作薄，对照图2-18中的信息，在Sheet1工作表的D列中快速无误地输入员工的所属部门。具体操作方法与步骤如下：图2-18使用函数填充文本序列2.5.1动态下拉菜单快速输入数据1.使用一级菜单的方法快速输入数据例2-9打开“鸿雁食品公司员工信息2.xls”工作薄，对照图2-18中的信息，在Sheet1工作表的D列中快速无误地输入员工的所属部门。具体操作方法与步骤如下：打开“鸿雁食品公司员工信息2.xls”工作薄，选中Sheet1工作表的D列，单击“数据”→“有效性”菜单命令，打开“数据有效性”对话框。在“数据有效性”对话框中，单击“设置”选项卡，在“允许”下拉列表中选择“序列”选项；在“来源”输入框中输入部门名称序列“财务部,客服部,销售部,质检部,技术部”，如图2-19所示，单击“确定”按钮。第一步：

第二步：

图2-19输入指定的序列数据2.5.1动态下拉菜单快速输入数据1.使用一级菜单的方法快速输入数据例2-9打开“鸿雁食品公司员工信息2.xls”工作薄，对照图2-18中的信息，在Sheet1工作表的D列中快速无误地输入员工的所属部门。具体操作方法与步骤如下：设置好后，单击D列数据区域的某个单元格时，在该单元格的右侧出现一个下拉按钮，单击该按钮，就可以在列表框中选择输入某个部门名称，如图2-20所示。第三步：

图2-20从下拉列表中选择输入数据2.5.1动态下拉菜单快速输入数据2.使用两级联动菜单的方法快速输入数据例2-10打开“鸿雁公司各部门员工姓名信息.xls”工作簿，在“各部门员工姓名”工作表中（内容如图2-21所示），为“部门”单元格区域B1:F1定义一个名称“部门名称”，为每个部门所属的员工单元格区域分别定义相应的名称。例如，把B2:B3单元格区域的名称定义为“财务部”，C2:C9单元格区域的名称定义为“技术部”，…，F2:F4单元格区域的名称定义为“质检部”。然后通过设置多种限制的数据有效性，在“员工信息”工作表中，快速输入如图2-22所示的员工的姓名。图2-21“各部门员工姓名”工作表中的内容图2-22要输入的部门及所属员工姓名的信息2.5.1动态下拉菜单快速输入数据选取要定义名称为“部门名称”的单元格区域B1:F1。①单击“插入”→“名称”→“定义”菜单命令（或者按“Ctrl+F3”组合键），打开“定义名称”对话框。②第一步：定义名称。（1）用“定义名称”对话框定义名称2.5.1动态下拉菜单快速输入数据图2-24定义名称“部门名称”在“在当前工作簿中的名称”文本框中输入“部门名称”，如图2-24所示，如果不想再定义其他名称，单击“确定”按钮即可。③第一步：定义名称。（1）用“定义名称”对话框定义名称这里，因为还要定义其他名称，所以单击“添加”按钮，在“在当前工作簿中的名称”文本框中输入“财务部”，在“引用位置”输入框中输入单元格区域“=$B$2:$B$3”，如图2-25所示。图2-25输入引用位置的地址2.5.1动态下拉菜单快速输入数据图2-27继续定义其他名称按同样的方法继续定义其他名称，如图2-27所示。本案例中定义的名称及相应的引用位置如图2-28所示。

④第一步：定义名称。（1）用“定义名称”对话框定义名称名称引用位置部门名称=各部门员工姓名!$B$1:$F$1财务部=各部门员工姓名!$B$2:$B$3技术部=各部门员工姓名!$C$2:$C$9客服部=各部门员工姓名!$D$2:$D$6销售部=各部门员工姓名!$E$2:$E$8质检部=各部门员工姓名!$F$2:$F$4图2-28定义的名称及对应的引用位置2.5.1动态下拉菜单快速输入数据选取要定义名称为“部门名称”的单元格区域B1:F1。①在名称框中输入名称“部门名称”，按Enter键。这样就定义好了“部门名称”，如图2-29所示。②第一步：定义名称。（2）利用名称框定义名称采用同样的方法具体定义其他名称。③图2-29利用名称框定义名称2.5.1动态下拉菜单快速输入数据第二步：设置多种限制的数据有效性。①在“员工信息”工作表中，选择要输入部门名称的单元格区域B2:B26，单击“数据”→“有效性”菜单命令，打开“数据有效性”对话框。②在“数据有效性”对话框中，选择“设置”选项卡，在“允许”下拉列表中选择“序列”选项，在“来源”输入框中输入公式“=部门名称”，如图2-30所示，单击“确定”按钮。图2-30为B列设置部门名称序列2.5.1动态下拉菜单快速输入数据第二步：设置多种限制的数据有效性。③选择要输入员工姓名的单元格区域C2:C26，单击“数据”→“有效性”菜单命令，打开“数据有效性”对话框。④在“数据有效性”对话框中，选择“设置”选项卡，在“允许”下拉列表中选择“序列”选项，然后在“来源”输入框中输入公式“=INDIRECT(B2)”，如图2-31所示，单击“确定”按钮。这样，部门名称输入及其所属员工名称序列输入设置完毕。在B列的某个单元格选择输入部门名称，那么在C列的该行单元格内只能选择输入该部门所属的员工名称，如图2-32和图2-33所示。图2-31为C列设置某部门所属员工名称序列图2-32选择输入“技术部”的员工姓名图2-33选择输入“质检部”的员工姓名2.5.1动态下拉菜单快速输入数据第二步：设置多种限制的数据有效性。公式解析：在本示例中，B列的某个单元格输入的是部门名称字符串，而这个部门名称字符串又恰好是各个部门下属员工姓名列表区域的名称，因此需要使用INDIRECT函数将这个字符串转换为真正的名称。2.5.2限制输入重复数据在Excel工作表中，有些数据是唯一的，但是在输入时，有可能由于人为的因素，出现输入重复数据的情况，从而造成数据错误。为了避免这种错误的发生，在输入唯一的数据时，可以使用数据有效性功能，对输入数据的条件进行约束，并在输入重复值时弹出输入错误的提示对话框。2.5.2限制输入重复数据例2-11打开“鸿雁食品公司员工信息3.xls”工作簿，对Sheet1工作表中的A2:A26单元格区域（员工编号单元格区域），设置数据有效性，避免输入重复的员工编号，如果输入的员工编号重复，则弹出“员工编号重复，请重新输入正确的员工编号”的提示信息。具体操作如下。第一步：打开“鸿雁食品公司员工信息3.xls”工作簿，在Sheet1工作表中，选择A2:A26单元格区域。第二步：单击“数据”→“有效性”菜单命令，打开“数据有效性”对话框。2.5.2限制输入重复数据例2-11打开“鸿雁食品公司员工信息3.xls”工作簿，对Sheet1工作表中的A2:A26单元格区域（员工编号单元格区域），设置数据有效性，避免输入重复的员工编号，如果输入的员工编号重复，则弹出“员工编号重复，请重新输入正确的员工编号”的提示信息。具体操作如下。第三步：在“数据有效性”对话框中，选择“设置”选项卡，在“有效性条件”栏中单击“允许”右侧的下三角按钮，选择“自定义”选项。在“公式”文本框中输入允许条件的公式“=COUNTIF($A$2:$A$26,A2)<=1”。如图2-34所示。图2-34设置数据有效性公式2.5.2限制输入重复数据例2-11打开“鸿雁食品公司员工信息3.xls”工作簿，对Sheet1工作表中的A2:A26单元格区域（员工编号单元格区域），设置数据有效性，避免输入重复的员工编号，如果输入的员工编号重复，则弹出“员工编号重复，请重新输入正确的员工编号”的提示信息。具体操作如下。第四步：切换至“出错警告”选项卡，在“标题”文本框中输入“请输入正确编号！”，在“错误信息”文本框中输入错误时的提示信息，在此输入“员工编号重复，请重新输入正确的员工编号！”，如图2-35所示。图2-35设置出错警告信息2.5.2限制输入重复数据例2-11打开“鸿雁食品公司员工信息3.xls”工作簿，对Sheet1工作表中的A2:A26单元格区域（员工编号单元格区域），设置数据有效性，避免输入重复的员工编号，如果输入的员工编号重复，则弹出“员工编号重复，请重新输入正确的员工编号”的提示信息。具体操作如下。第四步：单击“确定”按钮，返回Sheet1工作表。在单元格区域A3:A26中输入正确的员工编号，如果在A5元格中输入了A3的重复值“HY201306081002”，按Enter键后弹出“请输入正确编号！”提示对话框，并显示设置的错误警告信息，如图2-36所示。图2-36输入重复的员工编号时弹出出错警告信息2.5.2限制输入重复数据公式解析：在本例中，公式“=COUNTIF($A$2:$A$26,A2)<=1”的意思是，在 A2:A26单元格区域中输入数据时，相同数据的个数要小于或等于1，即指定的单元格区域中不能出现相同的数据，如果相同数据的个数大于1，即指定的单元格区域中有相同的数据，那么输入的数据就为非法的数据。2.6.2透视图制作2.6.1数据透视表的创建2.6数据透视表2.6.1数据透视表的创建例2-12打开“2012年邕凯公司电器销售表.xls”工作簿，创建一个数据透视表，在透视表中按季度求每个区不同产品的销售总额。单击数据清单中的任一单元格。选择“数据”→“数据透视表及数据透视图”命令，打开如图2-37所示的“数据透视表和数据透视图向导—3步骤之1”对话框。图2-37“数据透视表和数据透视图向导—3步骤之1”对话框第一步：2.6.1数据透视表的创建例2-12打开“2012年邕凯公司电器销售表.xls”工作簿，创建一个数据透视表，在透视表中按季度求每个区不同产品的销售总额。在“数据透视表和数据透视图向导--3步骤之1”对话框中，需要确定数据源类型和所需创建的报表类型。这里在“请指定待分析数据的