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

模块3Excel电子表格处理任务清单任务3.1创建电子表格-《国家中药保护品种表》任务3.2数据处理-《中国茶叶产业发展》任务3.3数据管理-《中国非物质文化遗产数据》任务2.1创建电子表格-《国家中药保护品种表》学习目标知识目标：掌握工作表的基本操作掌握单元格格式、列宽和行高的设置方法掌握自动套用格式的设置方法掌握数据条、图标集、色阶的添加方法掌握数据验证的设置方法能力目标：能完成《国家中药保护品种表格》制作能完成中药材价格表格制作能制作学习周计划表素养目标：提升大学生的健康素养和保健意识增强大学生的传统文化自信和民族自信情境导入一株小草带来健康，一缕药香飘溢古今，中医药学是中国古代科学的瑰宝，也是打开中华文明宝库的钥匙。屠呦呦领导的研究小组从系统收集历代医籍、本草、地方药志和老中医经验入手，汇集了2000多种方药，从中筛选出200多种供研究，最后发现了青蒿素，这一抗疟药物拯救了全球数百万人的性命。任务描述小菲是某县城市场监督管理局药品股的职员，股长给小菲安排了一个任务，在周三前制作《国家中药保护品种表格》。具体要求如下：复制工作表“Sheet1”，重命名为“国家重要保护品种表”；在第一行前插入一行，合并A1:I1单元格，内容水平居中，标题为“国家重要保护品种表”，为标题单元格设置单元格样式为“标题1”，行高设为“30”，并设置单元格背景色为灰色，图案颜色“绿色”，图案样式“6.25%，灰色”；设置A2:I2单元格区域绿色底纹，字体加粗，字号为13，字体颜色为“白色，背景1”；设置表格数据中部居中，外边框的样式为双实线，颜色为“蓝色”，内边框为虚线，颜色为“浅灰色，背景2，深色25%”，第2行和第3行之间框线为单实线，颜色为“蓝色”。套用表格样式“白色，表样式浅色18”；为“价格”列添加绿色数据条。为“保护级别”设置星标“图标集”。为“保护期限”列设置“绿-黄-红色阶”；为“保护终止日”列添加“数据验证”，“验证条件”设为日期大于“2022/1/1”，“输入信息”标题输入“日期格式”，输入信息为“202*/*/*或者202*-*-*”，“出错警告”错误信息输入框输入“此值与此单元格定义的数据验证限制不匹配”,并“圈释无效数据”。任务实现1.

工作表的基本操作（1）工作表的移动和复制①右击工作表Sheet1标签，从弹出的快捷菜单中选择“移动或复制”命令，打开“移动或复制工作表”对话框，在“工作簿”下拉列表框中选择目标工作薄，目标工作薄必须是打开状态。在“下列选中工作表之前”列表框中选择工作表移动或复制后的位置。如果进行复制操作，则需选中“建立副本”复选框，单击“确定”按钮。任务实现②还可以使用第二种方法复制工作表，选中Sheet1标签，按住Ctrl键，拖动工作表Sheet1到目标位置，先松开鼠标左键，再松开Ctrl键完成复制工作表。如果只是移动工作表则不需要使用Ctrl键，选中工作表标签，拖动到目标位置即可。（2）工作表重命名双击复制的工作表标签，此时工作表名称将突出显示，直接输入工作表名“国家重要保护品种表”，或者右击复制的工作表标签，从弹出的快捷菜单中选择“重命名”命令，输入工作表名。任务实现2. 设置单元格格式（1）设置表标题单元格①插入一个工作表行。选中第一行，切换到“开始”选项卡，单击“单元格”选项组中的“插入”按钮，从弹出的下拉菜单中选择“插入工作表行”命令。选中单元格区域A1:I1，单击“对齐方式”选项组中的“合并后居中”按钮，在合并单元格中输入“国家重要保护品种表”。任务实现②为表标题套用单元格样式。单击“样式”选项组中的“单元格样式”按钮，从弹出的下拉菜单中选择“标题”栏中的“标题1”选项。任务实现③单击“单元格”选项组中的“格式”按钮，从弹出的下拉菜单中选择“行高”命令，打开“行高”对话框，在文本框中输入30，单击“确定”按钮。任务实现④右击表标题单元格，从弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，切换到“填充”选项卡，“背景色”栏中选择“灰色”，“图案颜色”下拉列表框中选择标准色“绿色”，“图案样式”下拉列表框中选择“6.25%，灰色”，单击“确定”按钮。任务实现（2）设置标题行字体和颜色选中单元格区域A2:I2，切换到“开始”选项卡，单击“字体”选项组中的“加粗”按钮，“字号”文本框输入13，单击“填充颜色”右侧的箭头按钮，从弹出的下拉菜单中选择“标准色绿色”，单击“字体颜色”右侧的箭头按钮，从弹出的下拉菜单中选择“白色，背景1”。任务实现（3）设置表格边框①选中单元格区域A2:I57，切换到“开始”选项卡，单击“对齐方式”选项组中的“居中”和“垂直居中”按钮。②单击“字体”选项组中的“边框”按钮，从弹出的下拉菜单中选择“其他边框”命令，打开“设置单元格格式”对话框，切换到“边框”选项卡，“样式”列表框中选择“双实线”，“颜色”下拉列表框中选择“蓝色”，“预置”栏选择“外边框”选项，完成了外边框的设置。任务实现③设置内边框。“样式”列表框中选择虚线，“颜色”下拉列表框中选择“浅灰色，背景2，深色25%”，“预置”栏选择“内部”选项，单击“确定”按钮。④设置第一行和第二行之间的边框线。选中单元格区域A2:I3，右击弹出快捷菜单，选择“设置单元格格式”命令，切换到“边框”选项卡，“样式”列表框中选择“单实线”，“颜色”下拉列表框选择“蓝色”，“边框”栏选择“中线”，单击“确定”按钮。任务实现（4）套用表格格式选中单元格区域A2:I57，切换到“开始”选项卡，单击“样式”选项组中的“套用表格格式”按钮，从弹出的下拉菜单中选择“浅色”栏中的“白色，表样式浅色18”选项，在弹出的“创建表”对话框中，选中“表包含标题”复选框，单击“确定”按钮。任务实现3. 设置条件格式（1）添加数据条选中“价格”列，切换到“开始”选项卡，单击“样式”选项组中的“条件格式”按钮，从弹出的下拉菜单中选择“数据条”命令，从子菜单“渐变填充”栏中选择“绿色数据条”选项。任务实现（2）添加图标集①选中“保护级别”列，切换到“开始”选项卡，单击“样式”选项组中的“条件格式”按钮，从弹出的下拉菜单中选择“新建规则”命令，打开“新建格式规则”对话框。②在“编辑规则说明”栏，“格式样式”下拉列表框中选择“图标集”，“图表样式”下拉列表框选择“五角星”。③第一行“图标”下拉列表框中选择“白色五角星”，“类型”栏下拉列表框选择“数字”，“值”文本框中输入“3”。任务实现④第二行“图标”下拉列表框中选择“半金色五角星”，“类型”栏下拉列表框选中“数字”，数据框中输入“2”；。⑤第三行“图标”下拉列表框中选择“金色五角星”，单击“确定”按钮。任务实现（3）添加色阶选中“保护年限”列，单击“样式”选项组中的“条件格式”按钮，从弹出的下拉菜单中选择“色阶”命令，从子菜单中选择“绿-黄-红色阶”选项。任务实现4.设置数据验证（1）设置数据验证①选中单元格区域G3:G57，切换到“数据”选项卡，单击“数据工具”选项组中的“数据验证”按钮，打开“数据验证”对话框，切换到“设置”选项卡，“验证条件”栏中“允许”下拉列表框选择“日期”，“数据”下拉列表框选择“大于”，“开始日期”文本框中输入“2022/1/1”任务实现②切换到“输入信息”选项卡，“标题”栏文本框中输入“日期格式”，“输入信息”文本框输入“202*/*/*”。③切换到“出错警告”选项卡，“错误信息”文本框输入“此值与此单元格定义的数据验证限制不匹配”，单击“确定”按钮，如图3-15所示。如果数据不规范则无法输入，并会给出错警告。任务实现（2）圈释无效数据。可对已录入无效数据进行圈释。选中单元格区域G3:G57，单击“数据工具”选项组中的“数据验证”按钮，从弹出的下拉菜单中选择“圈释无效数据”命令即可。小组讨论：

数据验证的作用有哪些？相关知识1、工作簿、工作表和单元格（1）工作簿在Excel2016中，用来储存并处理工作数据的文件叫做工作簿。工作簿是一个Excel文件,扩展名为“.xlsx”。一个工作簿中最多可建立255个工作表。启动Excel2016后会自动新建一个工作簿。默认有一个工作表Sheet1。（2）工作表工作表由行号、列标、单元格、滚动条、工作表标签等组成。行号用数字表示，列标用英文字母表示。一个工作表有1048576行，16384列。相关知识① 新建工作表可以通过工作表标签右侧的“插入工作表”按钮来新增工作表。或者按“Shift+F11”组合键。也可以右击已有工作表标签，在弹出的菜单中选择“插入”命令新建工作表。② 设置工作表标签颜色右击工作表标签，从弹出的快捷菜单中选择“工作表标签颜色”命令，从子菜单中选择相应颜色，或者单击“其他颜色”打开“颜色”对话框，选择“自定义”选项卡，自定义工作表标签颜色。③ 隐藏或显示工作表右击工作表标签，在弹出的菜单中选择“隐藏”或“取消隐藏”命令即可。相关知识（3）单元格工作表中行列交汇处的区域成为单元格，是工作表的最小单位。单元格名称（或地址）由行号和列标组成，列标在前，行号在后。如第5行的第三列的单元格名称为C5。当选中某一单元格是，成为活动（当前）单元格，此时活动单元格的框线为粗框线。单元格内容会显示在数据编辑区内，可以对其修改。单元格右下角的黑色小方块称为控制句柄。相关知识（4）保护工作表和工作簿① 保护工作表切换到“审阅”选项卡，单击“更改”选项组中的“保护工作表”按钮，打开“保护工作表”对话框，如图3-17所示，默认选中“保护工作表及锁定的单元格内容”，在“取消工作表保护时使用的密码”文本框中设置密码“123”。根据实际需要，在“允许此工作表的所有用户进行”列表框中选择允许所有用户进行的操作。比如不能删除列，但需要备注说明的时候可以插入行或列。或者可以进行排序、筛选和使用数据透视表等。单击“确定”按钮，在弹出的“确认密码”对话框中再次输入密码“123”，单击“确定”按钮相关知识此时双击锁定的单元格，则会提示“您试图更改的单元格或图表位于受保护的工作表中。若要进行更改，请取消工作表保护。您可能需要输入密码。”。相关知识② 撤销工作表保护切换到“审阅”选项卡，单击“更改”选项组中的“撤销工作表保护”按钮，打开“撤销工作表保护”对话框，输入密码“123”，单击“确定”按钮即可。③ 保护工作簿保护工作簿结构可限制增加、移动、删除或隐藏工作表等。切换到“审阅”选项卡，单击“更改”选项组中的“保护工作薄”按钮，打开“保护结构和窗口”对话框相关知识默认选中“结构”，在“密码”文本框中设置密码“321”，单击“确定”按钮，在“确认密码”对话框中再次输入“321”，单击“确定”按钮。此时“保护工作簿”按钮的颜色变深，工作表标签的新增工作表按钮“+”号也变成灰色，无法操作了。同时工作表无法进行移动、隐藏、修改工作表名等操作。相关知识④ 撤销工作簿保护切换到“审阅”选项卡，单击“更改”选项组中的“保护工作簿”按钮，打开“撤销工作簿保护”对话框，输入密码“321”，单击“确定”按钮即可。相关知识（5）Excel2016文件加密为Excel2016文件加密可以限制访问Excel2016文件本身。具体操作步骤如下：①切换到“文件”选项卡，选择“另存为”命令，单击右侧视图中的“浏览”按钮，打开“另存为”对话框。②选择右下角的“工具”下拉列表中的“常规选项”命令，打开“常规选项”对话框相关知识③在“打开权限密码”文本框中输入密码“456”，则工作薄是“只读”状态，在“修改权限密码”文本框中输入密码“789”，则可以进行编辑，单击“确定”按钮，在“确认密码”文本框中再次输入密码“456”，单击“确定”按钮。又弹出“确认密码”对话框，再次输入密码“789”，单击“确定”按钮，返回到“另存为”对话框，④选择保存路径或更改Excel2016文件名后，单击“保存”按钮。⑤如果再次打开该文件，会弹出“密码”对话框，输入“456”，单击“确定”按钮，又弹出“密码”对话框，输入密码“789”获取写权限，或是单击“只读”按钮进行阅读，单击“确定”按钮。相关知识⑥如果要撤销Excel2016文件加密，可以切换到“文件”选项卡，选择“另存为”命令，单击右侧视图的“浏览”按钮，打开“另存为”对话框，选择右下角的“工具”命令下拉列表中的“常规选项”命令，打开“常规选项”对话框，将“打开权限密码”和“修改权限密码”文本框中的密码删除，单击“确定”按钮，返回到“另存为”对话框，单击“保存”按钮即可。相关知识2. 输入数据（1）输入数据① 输入文本文本就是字符串，由字母、数字、汉字等组合而成。可以进行字符串运算。默认左对齐。文本长度超过单元格宽度，如果右侧单元格中有内容，则超出部分隐藏，如果右侧单元格为空时，则超出部分在右侧单元格接着显示。② 输入数值输入数值默认右对齐，类型默认为“常规”，可以进行算术运算，如果数值长度超过单元格宽度，则自动转换成科学表示法。相关知识③ 输入日期和时间单元格中输入带有“/”或“-”字符的日期，Excel2016会自动识别为日期格式，并转换为默认的“2022/1/1”或“2月14日”。该单元格已经被格式化为日期类型，再输入数值也会被转换为日期。日期格式默认右对齐。④ 嵌入图片切换到“插入”选项卡，单击“插图”选项组中的“图片”按钮，打开“插入图片”对话框，找到存放图片的文件夹，选择图片，单击“插入”按钮，将图片插入到表格中。相关知识选中图片，调整图片大小，拖动到到单元格内，右击图片，在弹出的快捷菜单中选择“设置图片格式”，在属性里选中“随单元格改变位置和大小”，则将图片嵌入到单元格中。相关知识（2）快速填充① 自动填充选中一个单元格或数据区域时，右下角会出现控制柄，光标移动到控制柄时会出现“+”型，拖动鼠标，会根据所选内容实现自动填充。“自动填充选项”右侧三角箭头，从弹出的下拉菜单中可以选择“复制单元格”、“填充序列”、“仅填充序列”、“不带格式填充”等。也可切换到“开始”选项卡，单击“编辑”选项组中的“填充”按钮，从弹出的下级菜单中选择“序列”命令，打开“序列”对话框填充数据序列。相关知识② 填充多个合并单元格多个单元格合并居中了，需对多个合并单元格取消合并，并填充数据。首先取消合并后居中，再按“Ctrl+G”组合键，打开“定位”对话框，单击“定位条件”按钮，打开“定位条件”对话框，选中“空值”，单击“确定”按钮。选中单元格，在编辑栏中输入“=”，选择上一个单元格，按Ctrl+回车即可自动填充。③ 自定义序列在工作中经常需要填充固定的序列，则可以自定义该序列，通过控制柄自动快速填充。自定义序列的具体操作步骤如下：相关知识切换到“文件”选项卡，选择“选项”命令，打开“Excel选项”对话框，切换到“高级”选项卡，在“常规”栏中，单击“编辑自定义列表”按钮，打开“自定义序列”对话框。在“输入序列”文本框中依次输入自定义的序列项，每个序列项输入后用回车键分隔，单击“添加”按钮，再单击“确定”按钮则可以自定义序列。相关知识3. 设置数据验证数据验证就是对单元格设置规则，用户输入数据时验证该数据是否有效，通过限制输入数据的类型或范围，防止用户输入无效数据。可以设置整数、小数、序列、日期、时间、文本长度、自定义等数据类型的有效性。同时可以设置提示输入信息和出错警告。（1）限制数字范围员工年龄介于18-60之间，可以为“年龄”字段设置数据验证来避免手误录入错误。切换到“数据”选项卡，单击“数据工具”选项组中的“数据验证”按钮，打开“数据验证”对话框。相关知识切换到“设置”选项卡，“验证条件”栏“允许”下拉列表框中选择“整数”，“数据”下拉列表框中选择“介于”，“最小值”文本框中输入“18”，“最大值”文本框中输入“60”，单击“确定”按钮即可。设置小数可以用类似方法。相关知识（2）设置文本的长度在输入身份证号、手机号等固定长度时，为避免输入错误可以预先对单元格进行“文本长度”设置。例如“身份证号”字段设置文本长度。相关知识具体操作步骤如下：切换到“数据”选项卡，单击“数据工具”选项组中的“数据验证”按钮，打开“数据验证”对话框，切换到“设置”选项卡，“验证条件”栏中“允许”下拉列表框中选择“文本长度”，“数据”下拉列表框中选择“等于”，“长度”文本框中输入“18”，单击“确定”按钮即可。相关知识（3）设置序列序列设置可以提供下拉列表，不仅提高输入速度，而且避免输入错误。例如“性别”字段设置序列相关知识具体操作步骤如下：切换到“数据”选项卡，单击“数据工具”选项组中的“数据验证”按钮，打开“数据验证”对话框，切换到“设置”选项卡，“验证条件”栏中“允许”下拉列表框中选择“序列”，“来源”文本框输入“男,女”，或者单击右侧折叠按钮选择引用工作表内数据内容作为“序列”的来源。相关知识（4）自定义数据验证可以通过输入公式来自定义规范数据的输入，但公式结果必须是逻辑值，即必须输入返回值为TRUE或FALSE的公式，否则会出错。如果返回的值是TRUE则输入有效，返回的值是FALSE则报错无法输入。比如A1单元格只能输入文本，在公式文本框输入“=istext(A2)”相关知识（5）数据验证设置的清除①连续区域的数据验证的清除，首先选中需要清除数据验证的数据区域，切换到“数据”选项卡，选择“数据工具”选项组中的“数据验证”命令，会弹出“是否清除数据验证”对话框，单击“确定”按钮即可。②单元格数据验证的清除，首先选中需要清除数据验证的数据区域，切换到“数据”选项卡，单击“数据工具”选项组中的“数据验证”按钮，打开“数据验证”对话框，单击左下角的“全部清除”，再单击“确定”按钮即可。课堂实训：制作中药材价格表实训要求小华是某药材公司采购部职员，领导要求他对比药材价格，制作中药价格表，格式要求如下：（1）工作表“Sheet1”，重命名为“中药材价格表”；（2）在表格第一行添加标题“中药材价格表”，内容水平居中，为标题单元格新建单元格样式，名称为“标题样式”，字体设为“黑体”，字形“加粗”，字号为“28”，颜色设为绿色。并设置单元格背景色为灰色；（3）套用表格样式“表样式浅色9”；（4）“价格”列超过