第7课 排序“员工考评表”

排序“员工考评表”

——排序、筛选和汇总办公软件实训教程Excel2016制作：孔红霞于靖悦学习目标能够对数据进行单一条件排序、多条件排序、自定义排序，并通过“自动筛选”和“高级筛选”命令来筛选数据，能对数据进行分类汇总显示。办公软件实训教程Excel2016办公软件实训教程Excel2016又到了对员工考评的时候了，查看和分析员工成绩时，需要对表格中的数据按成绩高低排序、按部门分类汇总，或列举出符合条件的数据。卓卓能否利用Excel完成这些工作呢？今天的任务是按总分排序，按部门汇总，通过自动筛选和高级筛选命令筛选出符合条件的员工记录。今天的任务有些挑战性哟，卓卓要加油啦！图2-7-1样表办公软件实训教程Excel2016按某一关键字、多个关键字、自定义关键字进行升序或降序排序，对数据分类汇总，使用自动筛选和高级筛选命令筛选出符合条件的员工记录。办公软件实训教程Excel2016办公软件实训教程Excel2016图2-7-2排序按钮及排序对话框用户在使用Excel处理数据的过程中，常常会希望数据能够以一定顺序显示，从而方便查阅及筛选。Excel排序功能是指按照某种规则对数据进行排序。按照排序条件可分为单一条件排序、多条件排序、自定义排序。数据最基本的排序规则是单一条件排序中的升序排序和降序排序。一.数据的排序1.单一条件排序选中任意排序数据，单击【数据】选项卡【排序和筛选】组中的升序或降序。2.多条件排序首先按主要关键字排序，若主要关键字相同，再依次按次要关键字排序。如，先按部门排序，部门相同的按总分排序。操作如下：①选择任意数据单元格。②单击【数据】选项卡【排序和筛选】组的“排序”按钮。弹出“排序”对话框，在主要关键字中选择“部门”，如图2-7-2所示。③单击“排序”对话框中的“添加条件”按钮，对话框中添加了“次要关键字”。设置“次要关键字”为“总分”，设置为“升序”，如图2-7-3所示。④最后单击“确定”，返回工作表，查看数据。升序、降序不只实用于数字，对其他数据如字母、汉字、时间等均适用。字母排序：按照字母表ABC的顺序，默认字母升序且不区分大小写。汉字排序：分为拼音排序、笔划排序两种，默认为拼音排序。拼音排序是指按照汉语拼音的顺序排序，例如按照拼音升序排序“金”在“王”前面。时间排序：按照时间的先后顺序。混合排序：如果有多种数据类型，排序的规则是数字、字母、逻辑值（先FALSE后TRUE）、错误值、空格。小贴士：图2-7-3次要关键字按笔划排序的方法是打开“排序”对话框中的“选项”按钮，可设置区分大小写、排序方向、按笔划排序。如图2-7-4所示。小贴士：图2-7-4排序选项对话框办公软件实训教程Excel2016图2-7-5自定义排序用户在使用Excel处理数据的过程中，常常会希望数据能够以一定顺序显示，从而方便查阅及筛选。Excel排序功能是指按照某种规则对数据进行排序。按照排序条件可分为单一条件排序、多条件排序、自定义排序。数据最基本的排序规则是单一条件排序中的升序排序和降序排序。一.数据的排序3.自定义排序自定义排序是按自行设定的顺序对工作表中的数据进行排序的一种方式。如按部门中自定义的顺序，销售部、研发部、文秘部排序，操作如下。①选择要排序数据任一单元格。②单击【数据】选项卡【排序和筛选】组的“排序”按钮，弹出“排序”对话框。在主要关键字中选择“部门”，在“次序”中选择“自定义序列”，如图2-7-5所示。③在“自定义序列”对话框的“输入序列”中输入“销售部、研发部、文秘部”，如图2-7-6所示，单击“添加”按钮，单击“确定”。回到“排序”对话框，单击“确定”。图2-7-6自定义序列对话框二.数据筛选数据筛选功能可以快速将所需数据从工作表或者工作簿中挑选出来，使Excel只显示出符合设定筛选条件的行数据，而隐藏其他行。在Excel中提供了“自动筛选”和“高级筛选”命令来筛选数据。一般情况下，自动筛选就能够满足大部分需要，当需要使用复杂的条件来筛选数据清单时，就必须使用高级筛选。1.自动筛选①选择要筛选的任一数据单元格。②单击【数据】选项卡【排序和筛选】组的“筛选”按钮。③在数据清单中每一列旁边插入下拉箭头（筛选按钮），如图2-7-7所示。④单击筛选按钮可看到下拉列表，选择要显示的项目，工作表中就会显示筛选后的数据。如在“性别”的筛选按钮下选择“男”，则显示所有男职工记录。另外，在筛选按钮的下拉列表中可设置“按颜色排序”、“数字筛选”。“数字筛选”可设置“大于”、“大于等于”、“小于”等。办公软件实训教程Excel2016图2-7-7插入筛选按钮后的效果执行筛选操作时，数据清单中必须有列标记。小贴士：办公软件实训教程Excel2016图2-7-8“与”的条件设置图2-7-9“与条件高级筛选”对话框2.高级筛选在一般的筛选条件满足不了用户的需求时，可选用较为复杂的高级筛选。①“与”条件筛选“与”条件筛选是指筛选条件位于同行或同列时，筛选条件之间为“与”。“与”条件筛选需要同时满足几个条件，操作如下：在工作表下方复制列标记行，也可只复制要筛选的列标记。以员工考评表为例，设置条件如：“销售业绩>85”、“表达能力>83”，两个条件在同一行，即为“与”。如图2-7-8所示。单击【数据】选项卡【排序和筛选】组的“高级”按钮。弹出“高级筛选”对话框如图2-7-9所示。在“列表区域”文本框中自动填充了数据区域。单击“条件区域”右侧的折叠按钮，在工作表中选择满足筛选条件的单元格区域，即E19:F20。单击“确定”，筛选出符合条件的一条记录，如图2-7-10所示。图2-7-10“与”筛选的数据办公软件实训教程Excel2016图2-7-15按部门分类汇总平均分图2-7-14分类汇总对话框图2-7-16“1”按钮下总计项三.数据的分类汇总分类汇总就是将工作表中的某些数据进行分类并且汇总后显示出来。汇总方式包括求和、计数、求平均值等。1.对要进行分类汇总的列数据排序，例如部门。2.再选择数据清单里的一个单元格。3.单击【数据】选项卡【分级显示】组的“分类汇总”按钮。弹出“分类汇总”对话框，设置“分类字段”为部门，“汇总方式”为平均值，“汇总项”为平均分，如图2-7-14所示。最后单击“确定”按钮，效果如图2-7-15所示。4.查看“总计”项和“各部门分类汇总”项按部门汇总后，在图2-7-15的左上角有字样，单击“1”按钮，显示总计项，如图2-7-16所示。单击“2”按钮，显示二级汇总项数据，如图2-7-17所示。图2-7-17“2”按钮下各部门分类汇总”分类汇总前对进行分类汇总的列数据要先进行排序。“分类汇总”对话框的“选定汇总项”是复选框，根据需要可以选择多项进行汇总。例如，按部门汇总专业知识、应变能力、销售业绩，就是对多项汇总。删除分类汇总的方法是单击“分类汇总”对话框的“全部删除”命令。小贴士：办公软件实训教程Excel2016图2-7-18第3题效果图1.打开“员工考评表.xlsx”，复制工作表中的内容至Sheet2中，在Sheet2中进行如下操作。2.按“平均分”降序排列。选择表中“平均分”列任一数据单元格，单击【数据】选项卡【排序和筛选】组中的降序。3.按第一关键字为“部门”、第二关键字为“总分”进行升序排序。①选择表中任意数据单元格。②单击【数据】选项卡【排序和筛选】组的“排序”按钮。弹出“排序”对话框，在主要关键字中选择“部门”，如图2-7-2所示。③单击对话框中的“添加条件”按钮。在对话框中添加了“次要关键字”。设置“次要关键字”为“总分”，“次序”设置为“升序”，如图2-7-3所示。④最后单击“确定”，返回工作表，查看数据。效果如图2-7-18所示。4.将姓名列数据按字母降序排序。①选择表中任意数据单元格。②单击【数据】选项卡【排序和筛选】组的“排序”按钮。弹出“排序”对话框，在主要关键字中选择“姓名”。③单击“选项”按钮，在弹出的对话框中设置“方向”为“按列排序”、“方法”选择“字母排序”，如图2-7-19所示。④回到“排序”对话框窗口，在“次序”中选择“降序”。最后单击“确定”，返回工作表，查看数据。效果如图2-7-20所示。图2-7-19排序选项设置图2-7-20第4题效果图办公软件实训教程Excel2016图2-7-21第5题效果图5.按照自定义序列“销售部、研发部、文秘部”进行排序。①选择数据任一单元格。②单击【数据】选项卡【排序和筛选】组的“排序”按钮，弹出“排序”对话框。在主要关键字中选择“部门”，在“次序”中选择“自定义序列”，如图2-7-5所示。③在“自定义序列”对话框的“输入序列”中输入“销售部、研发部、文秘部”，如图2-7-6所示，单击“添加”按钮，单击“确定”。回到“排序”对话框，单击“确定”。效果如图2-7-21所示。6.复制Sheet1工作表中的内容至Sheet3，在Sheet3中筛选“男”职工记录。①选择任一数据单元格，单击【数据】选项卡【排序和筛选】组的“筛选”按钮。②在“性别”筛选按钮下选择“男”。筛选出男员工数据记录。效果如图2-7-22所示。7.在Sheet3中，筛选“销售业绩>=85”记录。①单击【数据】选项卡【排序和筛选】组的“清除”按钮。清除当前的筛选条件，让数据全部显示。②在工作表中单击“销售业绩”的筛选按钮，选择“数字筛选”下的“大于或等于”选项，如图2-7-23所示。③在弹出的对话框中输入85，单击“确定”，最终效果如图2-7-24所示。图2-7-22第6题效果图图2-7-23数字筛选条件图2-6-13“COUNTIF”参数设置8.在Sheet3中，利用高级筛选，筛选“销售业绩>85”且“表达能力>83”的人员。①在工作表下方第19行，复制列标题行第1行。设置条件：“销售业绩>85”、“表达能力>83”。如图2-7-8所示。②单击【数据】选项卡【排序和筛选】组的“高级”按钮。③弹出“高级筛选”对话框如图2-7-9所示。在“列表区域”文本框中自动选择了数据区域。单击“条件区域”右侧的折叠按钮，在工作表中选择满足筛选条件的单元格区域，即E19:F20。④单击“确定”，筛选出符合条件的一条记录，效果如图2-7-10所示。9.在Sheet3中，利用高级筛选，筛选“销售业绩>85”或“写作能力>85”的人员，将Sheet3重命名为“筛选”。①单击【数据】选项卡【排序和筛选】组的“清除”按钮。先清除当前的筛选条件，让数据全部显示。②更改筛选的条件，在单元格区域E20和G21中输入条件，如图2-7-11所示。即“销售业绩>85”和“写作能力>85”。注意：两个条件不在同一行。③单击【数据】选项卡【排序和筛选】组的“高级”按钮。弹出“高级筛选”对话框。设置“方式”为“将筛选结果复制到其他位置”，“列表区域”文本框中默认填充所有数据区域，“条件区域”则选择单元格区域E19:G21，“复制到”选择单元格A24。如图2-7-12所示。④单击“确定”，筛选出符合条件的记录，从A24开始放置。效果如图2-7-13所示。将工作表重命名为“筛选”。办公软件实训教程Excel2016图2-7-9“与条件高级筛选”对话框图2-7-10“与”筛选的数据图2-7-11“或”条件设置图2-7-12“或条件筛选”对话框图2-7-13“或”筛选出的数据办公软件实训教程Excel201610.复制Sheet1工作表至Sheet4，在Sheet4中按部门汇总平均分。查看“总计”项和“各部门分类汇总”项。将Sheet4重命名为“汇总”。①首先对“部门”列数据进行排序。②选择数据清单里的任一数据单元格。③单击【数据】选项卡【分级显示】组的“分类汇总”按钮。弹出“分类汇总”对话框，设置“分类字段”为部门，“汇总方式”为平均值，“汇总项”为平均分，如图2-7-14所示。最后单击“确定”按钮，效果如图2-7-15所示。④查看“总计”项和“各部门分类汇总”项按部门汇总后，在图的左上角有字样，单击“1”按钮，显示总计项，如图2-7-16所示。单击“2”按钮，显示二级汇总项数据，如图2-7-17所示。⑤将工作表更名为“汇总”。11.将文件另存为“员工考评表效果.xlsx”到自己姓名文件夹下。图2-7-14分类汇总对话框图2-7-15按部门分类汇总平均分图2-7-16“1”按钮下总计项图2-7-17“2”按钮下各部门分类汇总”办公软件实训教程Excel2016图2-7-26练技能第4题效果1.新建工作簿，将“员工考评表.xlsx”内容复制到工作表中，保存文件名为“练一练效果.xlsx”。将“练一练效果.xlsx”中Sheet1的内容复制到Sheet2、Sheet3、Sheet4中。在Sheet2进行如下操作。2.对数据按“总分”降序排列。3.先按“性别”，再按平均分降序排列。效果如图2-7-25所示。4.将姓名按笔划升序排序