信息技术基础 课件 模块3 Excel电子表格处理-3

模块3Excel电子表格处理任务清单任务3.1创建电子表格-《国家中药保护品种表》任务3.2数据处理-《中国茶叶产业发展》任务3.3数据管理-《中国非物质文化遗产数据》任务3.3数据管理-《中国非物质文化遗产数据》学习目标知识目标：掌握排序的使用方法掌握筛选的使用方法掌握分类汇总的使用方法掌握数据透视表的使用方法掌握合并计算的使用方法能力目标：能对《中国非物质文化遗产数据》统计与管理能统计分析公司员工信息数据能统计分析唐代诗歌素养目标：培养大学生的数据素养和严谨细致的工匠精神增强大学生的民族认同感、自豪感和责任感情境导入从舌尖上的味道，到指尖上的技艺，从民间文学到名族歌舞，从中华武术到传统中药，每一项传承的背后都饱含着先人的智慧与情感，蕴藏着我们的文化基因与精神信仰，非物质文化遗产不仅代表过去，更属于现在与未来，它来源于生活，流淌在我们的血脉里，潜移默化，影响着我们的思维和习惯，是我们创造美好生活的力量和源泉。任务描述小菲是某茶叶有限公司的销售部职员，销售部经理要求小菲提交一份中国茶叶发展调研报告，为更准确、直观的呈现中国茶叶发展的小菲参加了“非遗进社区”的志愿者服务活动，为了顺利圆满的开展活动，需要对中国非物质文化遗产数据进行如下处理。具体要求如下：1.对中国非物质文化遗产数据先按“第一批、第二批、第三批、第四批、第五批”的公布批次排序，后按类别升序进行排序，效果；2.筛选出2010至2020年公布的传统医药的中国非物质文化遗产；3.筛选出第一批公布的传统戏剧或第二批公布的传统技艺的中国非物质文化遗产；4.统计各公布批次中国非物质文化遗产的个数及其中各类别的非遗个数；5.生成各公布时间中各类别非遗个数的数据透视表任务实现1.排序自定义排序① 选中数据清单区域，切换到“开始”选项卡，单击“编辑”选项组中的“排序和筛选”按钮，从弹出的下拉菜单中选择“自定义排序”命令，打开“排序”对话框，或是切换到“数据”选项卡，单击“排序和筛选”选项组中的“排序”按钮，打开“排序”对话框。任务实现②在“主要关键字”下拉列表框中选择“公布批次”，“排序依据”选择默认值“单元格值”，在“次序”下拉列表框中，应选择“自定义序列”，打开“自定义序列”对话框。在“输入序列”文本中输入“第一批”，按“Enter”键，输入“第二批”，按“Enter”键，同理输入后续批次后，单击“添加”按钮，则添加到了自定义序列中，选中该自定义序列，单击“确定”按钮返回“排序”对话框。任务实现③单击“添加条件”按钮，在“次要关键字”下拉列表框中选择“类别”，“排序依据”选择默认值“单元格值”，在“次序”下拉列表框中应选择“升序”，单击“确定”按钮。任务实现（1）自动筛选①首先使用自定义筛选出2010至2020年公布的中国非物质文化遗产。选中数据清单区域任一单元格，切换到“开始”选项卡，单击“编辑”选项组中的“排序和筛选”按钮，从弹出的下拉菜单中选择“筛选”命令，或是切换到“数据”选项卡，单击“排序和筛选”选项组中的“筛选”按钮，此时工作表中数据清单的列标题全部变成下拉按钮。任务实现②单击“公布时间”按钮，从弹出的下拉菜单中选择“数字筛选”命令，从子菜单中“自定义筛选”命令，弹出“自定义自动筛选方式”对话框。任务实现在“公布时间”的第一个下拉列表框中选择“大于”，在右侧的组合框中输入“2010”，在“公布时间”的第二个下拉列表框中选择“小于”，在右侧的组合框中输入“2020”，单击“确定”按钮即可选出2010至2020年公布的中国非物质文化遗产。任务实现③在2010至2020年公布的非遗中使用文本筛选出传统医药的非遗。选中自定义筛选的数据清单任一单元格，单击“类别”按钮，从弹出的下拉菜单中选中“传统医药”复选框，单击“确定”按钮即可。（2）高级筛选①首先设置条件区域。选中工作表第一行任一单元格，切换到“开始”选项卡，单击“单元格”选项组中的“插入”按钮，从弹出的的下拉菜单中选择“插入工作表行”命令，重复此操作，插入四行作为高级筛选的条件区域。②在D1和E1单元格中输入筛选条件字段，分别是公布批次和类别，D2和E2单元格应分别输入“第一批”、“传统戏剧”；D3和E3单元格应分别输入“第二批”、“传统技艺”。任务实现③然后对数据进行高级筛选。选中数据区域任一单元格，切换到“数据”选项卡，单击“排序与筛选”选项组中的“高级”按钮，打开“高级筛选”对话框，选择“方式”栏中的“在原有区域显示筛选结果”单选按钮，通过“切换”按钮选中或输入列表区域和条件区域，单击“确定”按钮。小组讨论：

高级筛选条件区域设置要求有哪些？任务实现3.分类汇总各公布批次的中国非物质文化遗产个数及其中各类别的非遗个数可以使用分类汇总来统计。分类汇总顾名思义就是要先分类后汇总，分类可以使用排序来完成。（1）对数据进行排序①选中数据清单区域任一单元格，切换到“数据”选项卡，单击“排序和筛选”选项组中的“排序”按钮，打开“排序”对话框，在“主要关键字”下拉列表框中选择“公布批次”，“排序依据”选择默认值“单元格值”，在“次序”下拉列表框中选择“升序”。②单击“添加条件”按钮，在“次要关键字”下拉列表框中选择“类别”，“排序依据”选择默认值“单元格值”，在“次序”下拉列表框中选择“升序”，单击“确定”按钮。任务实现（2）简单分类汇总选中数据清单区域任一单元格，单击“数据”选项卡中“分级显示”选项组中的“分类汇总”按钮，打开“分类汇总”对话框，在“分类字段”下拉列表框中选择“公布批次”，“汇总方式”下拉列表框中选择“计数”，“选定汇总项”列表框中选择“序号”复选框，选中“汇总结果显示在数据下方”复选框，取消选中“每组数据分页”复选框，单击“确定”按钮即可统计出不同公布时间中国非物质文化遗产的个数。任务实现（3）多级分类汇总①各公布批次中各类别的非遗个数可以使用多级分类汇总统计，即使用2次分类汇总。②选中分类汇总数据区域任一单元格，切换到“数据”选项卡，单击“分级显示”选项组中的“分类汇总”按钮，打开“分类汇总”对话框，在“分类字段”下拉列表框选择“类别”，“汇总方式”下拉列表框中选择“计数”，“选定汇总项”列表框选择“序号”复选框，选中“汇总结果显示在数据下方”复选框，取消选中“替换当前分类汇总”和“每组数据分页”复选框，单击“确定”按钮即可。（4）删除分类汇总选中分类汇总数据区域任一单元格，切换到“数据”选项卡，单击“分级显示”选项组中的“分类汇总”按钮，打开“分类汇总”对话框，单击“全部删除”按钮即可。任务实现4.数据透视表统计各公布批次的中国非物质文化遗产个数及其中各类别的非遗个数也可以使用数据透视表来完成。数据透视表可以从数据清单中提取数据并重新布局和分类汇总。任务实现（1）创建数据透视表。选中数据清单区域任一单元格，切换到“插入”选项卡，单击“表格”选项组中的“数据透视表”按钮，打开“来自表格或区域的数据透视表”对话框，“表/区域”文本框中已默认了选中的数据清单区域。“选择放置数据透视表的位置”栏选中“新工作表”单选按钮，单击“确定”按钮。任务实现（2）设置数据透视表。①打开“数据透视表字段”任务窗格，从“选择要添加到报表的字段”列表框中将“公布批次”字段拖动到“行”列表框中，将“类别”字段拖动到“列”列表框中，将“序号”字段拖动到“值”列表框中。任务实现②切换到“数据透视表工具-数据透视表分析”选项卡，单击“活动字段”选项组中的“字段设置”按钮，打开“值字段设置”对话框，在“计算类型”列表框中选择“计数”，单击“确定”按钮即可。相关知识1、分列在Excel2016前期数据处理时常用到分列的功能。它可以根据一定的规则快速的分隔一些文本，保存在多列中。而且还可以转换一些数据的格式。（1）按分隔符号分列如果分列的数据中有特殊的符号，比如逗号、冒号等。一般选择使用分隔符号。具体操作步骤如下：①选中要分列的数据区域，切换到“数据”选项卡，单击“数据工具”选项组中的“分列”按钮，打开“文本分列向导”对话框。任务实现②选中“分隔符号”单选按钮，单击“下一步”按钮，在“分隔符号”栏中选中“Tab键”、“分号”、“逗号”、“空格”单选按钮，如果是冒号等，则可以选中“其他”单选按钮，并在文本框中输入相应分隔符号，单击“下一步”按钮，单击“完成”按钮。相关知识（2）按固定列宽分列如果想要分列的数据有固定宽度（比如从身份证提取出生年月），或者想要的数据部分是固定宽度，选择使用固定宽度。具体操作步骤如下：①选中要分列的数据区域，切换到“数据”选项卡，单击“数据工具”选项组中的“分列”按钮，打开“文本分列向导”对话框。任务实现②选中“固定宽度”单选按钮，单击“下一步”按钮，“数据预览”栏中有标尺，在需要分列的标尺位置单击鼠标左键，生成分割线，单击“下一步”按钮，单击“完成”按钮即可。相关知识（3）统一日期格式在某些上报的表格中，出生日期格式五花八门，有斜杠的、有点的、有短横线的，这时可以通过“分列”按钮统一日期格式。具体操作步骤如下：①选中数据区域，切换到“数据”选项卡，单击“数据工具”选项组中的“分列”按钮，打开“文本分列向导”对话框。②单击“下一步”按钮，再单击“下一步”按钮，在“列数据格式”栏中选中“日期”单选按钮，在其下拉列表框中选择“YMD”，单击“完成”按钮，日期格式就统一了。相关知识（4）文本转换为数值类型选中数据区域，切换到“数据”选项卡，单击“数据工具”选项组中的“分列”按钮，打开“文本分列向导”对话框，直接单击“完成”按钮即可将文本类型转换成数值类型。相关知识2.排序（1）数值排序规则按默认排序依据“数值”进行升序排序时，排序规则如下：①数字：由小到大②文本：拼音字母顺序③日期：最早到最晚④空白单元格总是排最后⑤多种数据类型排序时：按“数字、特殊字符、a-z、拼音”排列。降序反之。相关知识（2）简单排序简单排序又称按列简单排序，选中进行待排序字段列中的任意非空单元格，切换到“数据”选项卡，单击“排序和筛选”选项组中的“升序”或“降序”按钮。（3）按行排序常用排序方式是按列排序，在某些二维表中按行排序是非常实用的。按行排序是指对选中的数据区域按其中一行作为排序关键字进行排序的方法。具体操作步骤如下：任务实现①选中数据区域，切换到“数据”选项卡，单击“排序和筛选”选项组中的“排序”按钮，打开“排序”对话框。②单击“选项”按钮，打开“排序选项”对话框，“方向”栏中选中“按行排序”单选按钮，单击“确定”按钮，返回到“排序”对话框，在“主要关键字”下拉列表框中选择相应行，单击“确定”按钮。相关知识（4）多关键字排序多关键字排序是指对数据区域按两个及以上的关键字检修排序的方法。具体操作步骤如下：①选中数据区域，切换到“数据”选项卡，单击“排序和筛选”选项组中的“排序”按钮，打开“排序”对话框。②在“主要关键字”中选择第一排序的字段，依次选择“排序依据”和“次序”。③单击“添加条件”按钮，在新增的“次要关键字”中选择第二排序的字段，依次选择“排序依据”和“次序”，单击“确定”按钮。相关知识（5）自定义排序自定义排序是指对数据区域按用户自定义的序列进行排序。首先要添加自定义序列，有两种方法可以添加自定义系列。第一种方法：切换到“文件”选项卡，选择“选项”命令，打开“Excel选项”对话框，切换到“高级”选项卡，在“常规”栏中，单击“编辑自定义列表”按钮，打开“自定义序列”对话框，在“输入序列”文本框中依次输入自定义的序列项，每个序列项输入后用回车符分隔，单击“添加”按钮。相关知识第二种方法：选中数据区域，切换到“数据”选项卡，单击“排序和筛选”选项组中的“排序”按钮，打开“排序”对话框，依次选择“主要关键字”“排序依据”后，“次序”选择“自定义序列”，打开自定义序列对话框，在“输入序列”文本框中依次输入自定义的序列项，每个序列项输入后用回车符分隔，单击“添加”按钮，则添加到自定义序列中了，选中该自定义序列，单击“确定”按钮，返回到“排序”对话框，单击“确定”按钮，则按自定义的序列进行排序。相关知识3.筛选（1）自动筛选自动筛选可以分为按值列表（文本筛选、数字筛选）、按颜色筛选或自定义筛选。可以隐藏不符合条件的记录，将筛选结果显示在原始数据区域中。选中数据区域任意单元格，切换到“数据”选项卡，单击“排序和筛选”选项组中的“筛选”按钮，则列标题后出现筛选按钮，单击筛选按钮从弹出下拉菜单中选择筛选条件。如果筛选数据是文本类型，则显示“文本筛选”，是数字类型，则显示“数字筛选”，如果待筛选数据中包含颜色则显示“按颜色筛选”。相关知识（2）高级筛选相比自动筛选，高级筛选可以将筛选结果显示在其他位置，并可以实现多条件复杂的筛选操作。在高级筛选中最重要的是设置条件区域。高级筛选设置条件区域的要求如下：①条件区域第一行是所有作为筛选条件的字段名，必须与数据区域中的字段名一致。②其他行输入筛选条件，与关系的条件在同行，或关系条件不同行。③条件区域和数据区域不能连接，需用空行或空列隔开。相关知识4.合并计算合并计算可以对同一工作簿或者不同工作簿之间的多个工作表中的数据进行合并，并可以实现求和、计数、求平均值、最大值、最小值等多种方式的计算。合并表可以建在某原始表，也可以建在新工作表中。具体操作步骤如下：①选中合并表的单元格，切换到“数据”选项卡，单击“数据工具”选项组中的“合并计算”按钮，打开“合并计算”对话框。②“函数”下拉列表框中选择计算方式，“引用位置”选择需要合并的数据区域，单击“添加”按钮，可以看到选择的数据区域已经添加到“所有引用位置”列表中。相关知识依次将所有数据区域添加到“所有引用位置”中，“标签位置”栏选中“首行”和“最左列”复选框，单击“确定”按钮即可。课堂实训：统计分析公司员工信息数据实训要求小李是某公司人力资源部的员工，现在要进行招聘，人力资源部经理需要根据公司已有员工的情况进行综合考虑设置招聘岗位的要求。具体要求如下。①对公司员工学历按“博士、硕士、本科、大专”的顺序，年龄降序进行排序；②筛选出研发部年龄在25-38之间的高级工程师或研发部的主任工程师”的员工信息。③分类汇总公司员工各职称的平均工资，及不同职称男女员工的人数。常用技巧1.如何快速核对两个表格数据？在工作中经常需要核对两个表格的数据，比如月末了要对库存进行盘点，需要快速比对两个表格数据，找出库存数量不一致的商品。可以按如下步骤操作：①选中第