信息技术基础 教案-教学设计-2.2员工信息统计表制作

《信息技术基础》教学设计案例名称员工信息统计表制作学时4案例简介为建设一支素质优良的人才队伍，江南锡商科技有限公司将对企业员工信息建立员工信息表，包括员工基本信息如：员工号、姓名、性别、学历、职称、年龄、工龄、联系电话等信息。并对员工学历、职称等信息进行相关统计，以便于合理安排工作，保证企业的可持续发展。在Excel中要完成该案例要学会应用Excel中的文本函数、日期函数、查找函数、统计函数等多种函数在实际生活中的灵活应用。知识目标掌握公式的组成及使用。掌握函数的组成及使用。能力目标能应用函数从已有信息提取有用数据。能熟练使用Excel的中文本函数。能熟练使用Excel中的日期函数。能使用相关函数统计数据。素养目标(1)具有数据意识和数据中挖掘信息的能力。(2)养成不断精进技能的习惯，提高数据处理能力。教学内容教学过程【问题导入】员工信息是怎样计算出来的呢？（目的：引导学生进入课程，从整体上把握本单元将要讲述的核心问题；教学手段：提问与讲解相结合）【案例分析】在Excel中要完成该案例要学会应用Excel中的文本函数、日期函数、查找函数、统计函数等多种函数在实际生活中的灵活应用。任务一编辑员工信息【任务分析】打开“员工信息统计表.xlsx”工作簿，利用已有的信息，完善员工信息统计表数据。【任务讲演】1）使用MID函数输入员工的出生年，员工的身份证号包含了员工的很多信息，第7位到第11位是员工的出生年。选中D3单元格，选择“公式”→“函数库”→“插入函数”命令，弹出“插入函数”对话框，在“搜索函数”下输入“MID”，单击“转到(G)”按钮，然后在“选择函数”列表框中选择“MID”函数，单击“确定”按钮。在弹出的“函数参数”对话框中设置函数参数。单击“确定”按钮，利用填充柄向下填充其他数据。2）用TEXT函数和MID函数提取身份证号码的出生日期或者用DATE函数和MID函数，输入员工的出生日期。选中E3单元格，输入公式“=--TEXT(MID(C3,7,8),"0000-00-00")”或者输入公式“=DATE(MID(C3,7,4),MID(C3,11,2),MID(C3,13,2))”,按回车键即可，双击填充柄向下填充其他数据。3）使用MID函数取身份证第17位。选中F3单元格，输入公式“=MID(C3,17,1)”,按回车键即可，双击填充柄向下填充其他数据。4）使用ISODD函数判断第17位是否奇数。选中G3单元格，输入公式“=ISODD(F3)”或“=ISODD(MID(C3,17,1))”,按回车键即可，双击填充柄向下填充其他数据。5）使用IF函数输入性别男，女。选中H3单元格，输入公式“=IF(G3,”男”,”女”)”或者输入公式“=IF(ISODD(MID(C3,17,1),”男”,”女”)”按回车键即可，双击填充柄向下填充其他数据。6）使用LEFT函数取身份证的前两位。选中I3单元格，输入公式“=LEFT(C3,2)”,按回车键即可，双击填充柄向下填充其他数据。7）使用VLOOKUP函数从“地区代码与名称”工作表中根据“地区的代码”查找到相应的籍贯输入。选中J3单元格，选择“公式”→“函数库”→“插入函数”命令，弹出“插入函数”对话框，在“搜索函数”下输入“VLOOKUP”，单击“转到(G)”按钮，然后在“选择函数”列表框中选择“VLOOKUP”函数，单击“确定”按钮。在弹出的“函数参数”对话框中设置函数参数。单击“确定”按钮，利用填充柄向下填充其他数据。8）计算工龄。工龄可以根据入职时间来计算，利用today()函数获取当前日期，减去入职时间得到日期相差的天数，除以365得到相差的年数，利用INT()函数取得年数的整数。在P3单元格输入公式“=INT((today()-O3)/365)”，按【Enter】键，并利用填充柄填充其他员工工龄。9）计算年龄。年龄说法比较多，这里采用的是基本计算年龄的方法，就是今年的年数减去出生年数，在Q3单元格输入公式“=YEAR(TODAY())-D3”，按【Enter】键，设置Q3为数值，小数位数为0。并利用填充柄填充其他员工年龄。10）为保护隐私，使用REPLACE函数为联系电话打码，从第四位开始，中间的4位打*。选中S3单元格，选择“公式”→“函数库”→“插入函数”命令，弹出“插入函数”对话框，在“搜索函数”下输入“REPLACE”，单击“转到(G)”按钮，然后在“选择函数”列表框中选择“REPLACE”函数，单击“确定”按钮。在弹出的“函数参数”对话框中设置函数参数。单击“确定”按钮，利用填充柄向下填充其他数据。【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。【课堂提问】Excel公式中有哪些运算符，优先级是怎样的？【教师提示】运算符的优先级运算符含义优先级:（冒号）引用运算符1（空格）,（逗号）-（负号）负数2%（百分号）百分比3（脱字号）乘方4*和/乘和除5+和-加和减6&字符连接符7=比较运算符8<和><=>=<>【课后思考】如何正确引用单元格？任务二格式化工作表【任务分析】打开“员工信息统计表.xlsx”工作簿,对表格中的文本和数据进行字体格式的设置，行高、列宽的调整，以及对单元格的边框和底纹的添加，使表格显得更加美观。【任务讲演】1）设置标题行的行高为30，第二行的行高为45，其余行的行高为15。选中行1，选择“开始”→“单元格”→“格式”→“行高”命令（或者右击行号1，在弹出的快捷菜单中选择“行高”命令），在弹出的“行高”对话框中输入行高值“30”，单击“确定”按钮；选中行2，用同样方法设置行2行高为45；选中A3:A42单元格区域，选择“开始”→“单元格”→“格式”→“行高”命令，在弹出的“行高”对话框中输入行高值“15”，单击“确定”按钮。2）设置标题行文字跨列居中，设置字体黑体、字号16号，其他行文字水平居中、垂直居中，宋体、11号。选中A1:T1单元格区域，单击“开始”→“对齐方式”右侧的“对齐方式”启动器，在弹出的“设置单元格格式”对话框（如图2-34）的“对齐”选项卡中，设置水平对齐方式为“跨列居中”，垂直对齐方式为“居中”；再选择“字体”选项卡，设置字体为黑体、16号，单击“确定”按钮。选中A2:T42单元格区域，用同样方法设置格式，水平对齐方式为“居中”，垂直对齐方式为“居中”，设置字体为宋体、11号。3）选中所有列自动调整列宽。选中A2:T42单元格区域，选择“开始”→“单元格”→“格式”→“自动调整列宽”命令。4）选中A2:T42单元格区域，选择“开始”→“字体”→“边框”→“其他边框”命令，在弹出的“设置单元格格式”对话框的“边框”选项卡中，设置“线条样式”为双实线，颜色深蓝色，单击“外边框”按钮，再选择细实线，颜色深蓝色，单击“内部”按钮，单击“确定”按钮。5）选中A2:T2单元格区域，选择“开始”→“字体”→“填充颜色”→“白色，背景1，深色25%”命令。完成效果如图所示【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。【课堂提问】合并并居中与跨列居中有什么区别？【教师提示】合并并居中是将选定的单元格全部合并起来，成为一个单元格，再将其中的内容居中，而跨列居中仅将每个单元格的内容居中，如果第一个单元格的内容比单元格宽度长，跨列居中可以将该内容居中在你选定的几个单元格的总宽度之间，但是单元格并没有合并。【课后思考】怎样在工作表中输入正确的身份证号码？任务三统计员工信息【任务分析】打开“员工信息统计表.xlsx”工作簿,单击“员工信息统计”工作表标签，完成统计表1、统计表2、统计表3的数据统计，插入图表并美化。【任务讲演】1）使用COUNTIF函数统计学历是专科、本科、硕士人数。选中B3单元格，选择“公式”→“插入函数”命令，弹出“插入函数”对话框，在“选择函数”列表框中选择“COUNTIF”，单击“确定”按钮，打开COUNTIF函数参数对话框，第一个参数Range，选择员工信息表中的K3:K42，按【F4】变成绝对地址，第二个参数单击A3单元格，单击“确定”按钮，将鼠标指针移B3单元格右下角的填充柄上→鼠标指针变为黑色“+”时双击鼠标左键→完成填充。2）使用公式计算不同学历人数所占百分比。选中C3单元格，输入公式“=B3/SUM($B$3:$B$5)”,按回车键即可，将鼠标指针移C3单元格右下角的填充柄上→鼠标指针变为黑色“+”时双击鼠标左键→完成填充。3）设置C3:C5单元格区域数值为百分比格式并保留两位小数。选中C3:C5单元格区域选择“开始”→“数字”启动器→打开“设置单元格格式”对话框→“数字”选项卡→“百分比”→小数位数2位，单击“确定”按钮。4）使用COUNTIFS函数计算统计表2的数据。单击选中G3单元格，选择“公式”→“函数库”→“插入函数”命令，弹出“插入函数”对话框，在“搜索函数”下输入“COUNTIFS”，单击“转到(G)”按钮，然后在“选择函数”列表框中选择“COUNTIFS”函数，单击“确定”按钮。在弹出的“函数参数”对话框中设置函数参数。单击“确定”按钮，利用填充柄向下填充到G5单元格。5）用4）同样的方法，使用COUNTIFS函数计算G6:G8、G9:G11、G12:G14单元格区域的值。6）使用SUMIF函数计算统计表3的数据。单击选中G3单元格，选择“公式”→“函数库”→“插入函数”命令，弹出“插入函数”对话框，在“搜索函数”下输入“SUMIF”，单击“转到(G)”按钮，然后在“选择函数”列表框中选择“SUMIF”函数，单击“确定”按钮。在弹出的“函数参数”对话框中设置函数参数。单击“确定”按钮，利用填充柄向下填充到K5单元格。7）使用饼图直观显示不同学历占比。先选中A2:A5单元格区域，按住Ctrl键，再选中C2:C5，选择“插入”→“图表”→“饼图”→“二维饼图”下的“饼图”，就会插入一个饼图。8）将图表插入到当前工作表的“A8:C20"单元格区域内。选中图表→光标呈现四向箭头时按住鼠标左键不放将其拖动到“A8:C20”单元格区域内。9）选中图表→图表工具→设计→“图表布局”组中→单击添加“图表元素”下拉按钮→在弹出的下拉列表中选择→数据标签→“数据标签外(O)”命令。10）选中图表→图表工具→设计→“图表布局”组中→单击添加“图表元素”下拉按钮→在弹出的下拉列表中选择【图表标题】下的(图表上方】→将其标题修改为“学历占比图”。11）使用簇状柱形直观显示不同职称人数对比。先选中J2:K5单元格区域，选择"插入"→"图表"→"插入柱形图或条形图"→"二维柱形图"下的"簇状柱形图"，插入一个簇状柱形图。12）参照9）10）在数据标签内显示人数，修改图表标题为“职称人数对比图”。13）不显示图例。图表工具→设计→“图表布局”组→单击添加“图表元素”下拉按钮→在弹出的下拉列表中选择【图例】→“无”。或选中图例，按【Delete】键删除。14）选中图表→光标呈现四向箭头时按住鼠标左键不放将其拖动到“J8:N20”单元格区域内。15以“布局2”和“样式4”修饰图表。图表工具→设计→“图表布局”组-快速布局→选择“布局2”，图表样式→选择“样式4”修饰图表。最后完成效果如图所示。【任务操作】根据教师的操作演示，让学生按照任务要求进行相应操作，教师根据学生操作实际进行巡回指导。【课堂提问】图表的编辑包括哪些方面?【教师提示】更改图表类型：在图表空白处右击，在弹出的快捷菜单中选择“图表类型”命令。更改数据源：在图表的数据系列上右击，在弹出的快捷菜单中选择“选择数据”命令，在弹出的“选择数据源”对话框中设置数据区域。行列数据对换：选中图表后，选择“图表工具-设计”→“数据”→“切换行