资源简介

(共33张PPT)
项目十一
数据的管理与分析
活动一 数据的排序、筛选和汇总
了解Excel 2016的数据管理功能。
掌握Excel 2016对数据进行排序、筛选和汇总的方法。
学习目标
活动背景
毛小红是天津一家企业的销售部经理，按照单位的制度要求，年底要按业绩给予个人和部门奖励，所以，要统计单位各部门下半年的销售情况。
活动一 数据的排序、筛选和汇总
如果按业绩给予奖励，就要对单位各部门的销售情况进行统计，按部门或个人的销售数据排序，筛选满足条件的个人或部门，这就需要使用Excel 2016的数据管理功能。
活动分析
活动一 数据的排序、筛选和汇总
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（一）创建工作簿
启动Excel 2016，选择“空白工作簿”，创建一个新的工作簿文件，按照图11-1制作统计表。
（1）
图11-1 销售人员业绩统计表
计算半年销售总额。单击J4单元格，单击“开始”选项卡下的“编辑”命令组中的“自动求和”按钮，屏幕显示如图11-2所示。即在J4单元格中统计销售人员李红下半年的销售总额，观察公式的计算没有错误后，按Enter键，即可完成李红的销售总额统计。
（2）
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（一）创建工作簿
图11-2 单击“自动求和”按钮的屏幕显示
单击J4单元格，将光标移动到填充柄变为“+”形状时，按住鼠标左键向下拖动到J11单元格，释放鼠标，就可以完成其他人员的半年销售总额计算，如图11-3所示。
图11-3 全体销售人员的业绩统计
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（一）创建工作簿
置表格线。利用鼠标拖动的方法选中单元格区域A3: J11，单击“开始”选项卡下的“单元格”命令组中的“格式”按钮，在弹出的下拉菜单中选择“设置单元格格式”命令，弹出“设置单元格格式”对话框，在“样式”列表中选择单实线，单击“外边框”图标和“内部”图标，单击“确定”按钮，就可设置表格线，如图11-4所示。
（3）
图11-4 设置了表格线的销售人员业绩统计表
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（二）数据的排序
01
按“部门”排序
按“部门”排序的操作步骤如下：
（1）利用鼠标拖动选中单元格区域A3: J11，单击“开始”选项卡下的“编辑”命令组中的“排序和筛选”按钮 ，弹出下拉菜单，如图11-5所示。
图11-5“排序和筛选”下拉菜单
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（二）数据的排序
01
按“部门”排序
（2）选择“自定义排序”命令，弹出“排序”对话框，如图11-6所示。
图11-6“排序”对话框
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（二）数据的排序
01
按“部门”排序
（3）单击“主要关键字”右侧的小三角按钮，在弹出的下拉列表中选择“部门”，在“排序依据”下拉列表中选择“数值”，在“次序”下拉列表中选择“升序”，单击“确定”按钮，表格中的数据按“部门”降序排列，如图11-7所示。
图11-7 按“部门”降序排列的结果
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（二）数据的排序
知识链接：
文本型数据的排序依据是将文本转换为拼音英文字母，再按英文字母的ASCII码值大小进行排序。
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（二）数据的排序
02
按“半年销售总额”排序
利用鼠标拖动的方法选中单元格区域A3:J11，单击“开始”选项卡下的“编辑”命令组中的“排序和筛选”按钮，在弹出的下拉菜单中选择“自定义排序”命令，弹出“排序”对话框。在“主要关键字”下拉列表中选择“半年销售总额”，在“次序”下拉列表中选择“降序”，如图11-8所示。如果仅按“半年销售总额”排序，此时单击“确定”按钮即可。
图11-8 “半年销售总额”降序排列的设置
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（二）数据的排序
02
按“半年销售总额”排序
如果考虑到“半年销售总额“数据相同的情况，还可再按“部门”升序排列。在“排序”对话框中单击”添加条件“按钮，在“次要关键字”列表中选择“部门”，在“次序”列表中选择“升序”，如图11-9所示。
图11-9 “次要关键字”设置
活动一 数据的排序、筛选和汇总
一、创建工作簿及数据排序
（二）数据的排序
02
按“半年销售总额”排序
单击“确定”按钮，即可完成按“半年销售总额”降序、按“部门”升序的排列，如图11-10所示。
图11-10 按“半年销售总额”降序和“部门”升序排列的结果
活动一 数据的排序、筛选和汇总
二、数据的自动筛选
图11-11 筛选后的表格
（一）查看“绘图部”的销售情况
操 作 步 骤
（1）以图11-1中的数据为原始数据，利用鼠标拖动的方法选中单元格区域A3:J11，单击“开始”选项卡下的“编辑”命令组中的“排序和筛选”按钮，从弹出的下拉菜单中选择“筛选”命令，此时，看到的表格如图11-11所示。筛选后，系统会在选中表格的第一行每一个单元格的右侧添加一个按钮，该按钮就是做“筛选”用的按钮。
活动一 数据的排序、筛选和汇总
二、数据的自动筛选
图11-12“筛选”菜单
（一）查看“绘图部”的销售情况
操 作 步 骤
（2）单击“部门”的筛选按钮，弹出“筛选”菜单，如图11-12所示。
活动一 数据的排序、筛选和汇总
二、数据的自动筛选
图11-13 筛选“绘图部”的销售情况
（一）查看“绘图部”的销售情况
操 作 步 骤
（3）取消选中“全选”复选框，再选中“绘图部”复选框，单击“确定”按钮，则屏幕上显示的内容就是所需的“绘图部”销售人员的销售情况，如图11-13所示。
活动一 数据的排序、筛选和汇总
二、数据的自动筛选
（一）查看“绘图部”的销售情况
知识链接：
单击“数据”选项卡下的“排序和筛选”命令组中的“筛选”按钮，也能完成数据的筛选操作。完成筛选操作后，会发现做过筛选的记录的行号变为蓝色，且“部门”右侧按钮变为 。
活动一 数据的排序、筛选和汇总
二、数据的自动筛选
（二）取消“筛选“
单击“部门”单元格的筛选按钮，弹出“筛选”菜单，如图11-14所示。其中“从‘部门’中清除筛选”命令是可选项，选择该命令，表格显示如图11-11所示，再单击“开始”选项卡下的“编辑”命令组中的“排序和筛选”按钮，在弹出的下拉菜单中选择“筛选”命令，即取消所有筛选。
图11-14 做过筛选后的“筛选”菜单
活动一 数据的排序、筛选和汇总
三、数据的高级筛选
（一）查看“文具部”且“半年销售总额”高于13 200元的记录
（1）在L12单元格中输入“部门”，在L13单元格中输入“=‘文具部’”，在M12单元格中输入“半年销售总额”在M13单元格中输入“>13200”。
（2）将光标定位在有数据的单元格区域的任意位置，单击“数据”选项卡下的“排序和筛选”命令组中的“高级”按钮 ，弹出“高级筛选”对话框，如图11-15所示。
（3）“列表区域”中的地址为默认的数据区域地址，单击“条件区域”右侧的按钮 ，出现“高级筛选-条件区域：”对话框，如图11-16所示。
活动一 数据的排序、筛选和汇总
三、数据的高级筛选
（一）查看“文具部”且“半年销售总额”高于13 200元的记录
（4）利用鼠标拖动的方式选中单元格区域L12:M13，则对话框如图11-17所示。
图11-15 “高级筛选”对话框
图11-16“高级筛选-条件区域：”对话框
图11-17 选好条件后的对话框
图11-18 设置好“条件区域“的对话框
（5）单击右侧按钮 ，将“高级筛选”对话框展开，如图11-18所示。
活动一 数据的排序、筛选和汇总
三、数据的高级筛选
（一）查看“文具部”且“半年销售总额”高于13 200元的记录
（6）单击“确定”按钮，完成高级筛选任务，按指定条件查找到的记录即为部门是“文具部”且“半年销售总额”高于13 200元的记录，如图11-19所示。
图11-19 “文具部”且“半年销售总额”高于13 200元的记录
活动一 数据的排序、筛选和汇总
三、数据的高级筛选
（一）查看“文具部”且“半年销售总额”高于13 200元的记录
知识链接：
做过“高级筛选”的记录的行号呈蓝色。
活动一 数据的排序、筛选和汇总
三、数据的高级筛选
（二）查看“文具部”或“半年销售总额”高于13 200元的记录
（1）在L12单元格中输入“部门”在L13单元格中输入“=‘文具部’”，在M12单元格中输入“半年销售总额”，在M14单元格中输入“>13200”。
（2）将光标定位在有数据的单元格区域的任意位置，单击“数据”选项卡下的“排序和筛选”命令组中的“高级”按钮，在弹出的“高级筛选”对话框中单击“条件区域”右侧的按钮，选择单元格区域L12：M14，再单击右侧按钮，则设置筛选条件后的“高级筛选”对话框如图11-20所示。
图11-20 设置筛选条件后的“高级筛选”对话框
活动一 数据的排序、筛选和汇总
三、数据的高级筛选
（二）查看“文具部”或“半年销售总额”高于13 200元的记录
图11-21“文具部”或“半年销售总额”高于13 200元的记录
（3）单击“确定”按钮，完成指定条件的记录筛选，结果如图11-21所示。
活动一 数据的排序、筛选和汇总
四、数据的汇总
（一）
统计所有部门每个月的销售总和
统计所有部门每个月销售总和的步骤如下：
（1）选中单元格区域A3:J11，单击“开始”选项卡下的“编辑”命令组中的“排序和筛选”按钮，在弹出的下拉菜单中选择“自定义排序”命令，弹出“排序”对话框。
（2）在“主关键字”下拉列表中选择“部门”，在“次序”下拉列表中选择“升序”，单击“确定”按钮，则按“部门”升序排列的结果如图11-22所示。此时，部门相同的记录排在一起。
图11-22 按“部门”升序排列的结果
活动一 数据的排序、筛选和汇总
四、数据的汇总
（一）
统计所有部门每个月的销售总和
统计所有部门每个月销售总和的步骤如下：
（3）选中单元格区域A3:J11，单击“数据”选项卡下的“分级显示”命令组中的“分类汇总”按钮，弹出“分类汇总”对话框 ，如图11-23所示。
图11-23“分类汇总”对话框
活动一 数据的排序、筛选和汇总
四、数据的汇总
（一）
统计所有部门每个月的销售总和
统计所有部门每个月销售总和的步骤如下：
（4）在“分类字段”下拉列表中选择“部门”，在“选定汇总项”下拉列表中选中“7月份”“8月份”“9月份”“10月份”“11月份”“12月份”复选框，单击“确定”按钮，则汇总后的结果如图11-24所示。
图11-24 “分类汇总”后的结果
活动一 数据的排序、筛选和汇总
四、数据的汇总
（一）
统计所有部门每个月的销售总和
知识链接：
做过“分类汇总”后，系统会在表格的左侧增加分类汇总层的选择区域，单击“1”“2”“3”按钮，依次按单位、部门、全体人员分类显示汇总情况，如图11-25、图11-26和图11-24所示。
图 11-25 单击“1”按钮的显示结果
图11-26 单击“2”按钮的显示结果
活动一 数据的排序、筛选和汇总
四、数据的汇总
（二）
取消全部汇总
用鼠标选中单元格区域A3:J13，单击“数据”选项卡下的“分级显示”命令组中的“分类汇总”按钮，在弹出的“分类汇总”对话框中单击“全部删除”按钮，就可以取消全部汇总。
活动一 数据的排序、筛选和汇总
四、数据的汇总
（二）
取消全部汇总
知识链接：
在做分类汇总时，系统是对“分类字段”相同的所有记录的指定字段进行汇总计算。系统提供“求和”“计数”“平均值”“最大值”“最小值”等多种汇总方式，可以从“汇总方式”下拉列表中选择。
活动一 数据的排序、筛选和汇总
通过本此活动学习了在Excel 2016中对数据进行排序、筛选和汇总的方法，可以帮助用户快速对数据进行处理，对数据分析有很大的帮助。
活动小结
活动一 数据的排序、筛选和汇总
创建如图11-28所示的表格。要求如下：
（1）计算每号楼全年参与活动的总人次。
（2）复制Sheet1三次，生成Sheet2、Sheet3和Sheet4,并将4个工作表名依次更名为“原始工作表”“排序表”“筛选表”和“分类汇总表”。
（3）在“排序表”中，按“全年参与人次”的多少由高到低排序。
（4）在“筛选表”中，查看“物业单位”是“富华”的全部记录。
（5）在“分类汇总表”中，统计各物业单位所有季度和全年参与人次的平均值。
活动实践
图11-28 志愿者参与活动统计表

展开更多......

收起↑