信息技术基础 课件 单元2 电子表格处理

电子表格处理

2.1员工培训成绩表制作信息技术基础—课程资源学习目标1.知识目标了解工作簿和工作表的概念。理解单元格名称和活动单元格。了解填充柄的作用。2.技能目标能编辑Excel电子表格数据。能设置Excel

电子表格的格式。能应用Excel电子表格的函数。能使用Excel

电子表格的条件格式。3.素质目标养成团队协作意识和较强的沟通能力。具有较高的职业素养和创新能力。案例分析在Excel中要完成该案例需要使用数据验证、填充柄、函数计算、条件格式等功能，具体操作可分为５个任务:任务1：新建工作簿任务2：编辑数据任务3：计算数据任务4：跨工作表引用数据任务5：设置条件格式任务1新建工作簿任务说明

新建工作簿，并在输入内容后以“员工培训奖励.xlsx”保存工作簿。任务1新建工作簿任务步骤1）启动Excel20162）认识Excel2016窗口图1Excel2016窗口任务1新建工作簿任务步骤3）新建Excel工作簿4）如图输入数据5）在A2到G2单元格依次输入“员工号”，“企业文化（是否90分及以上）”，“职业素养（是否90分及以上）”，“信息安全（是否90分及以上）”，“创新思维（是否90分及以上）”，“有一科目是否90分及以上”，“所有科目是否90分及以上”，“奖励总额”。图2输入数据任务1新建工作簿任务步骤6）录入员工号（A3-A42）7）设置格式8）保存“员工培训奖励.xlsx”工作簿图3输入员工号任务2编辑数据任务说明在“员工培训成绩表.xlsx”工作簿中的“员工培训成绩表”工作表中按照要求输入内容，并在输入内容后保存。任务2编辑数据任务步骤1）打开“员工培训成绩表.xlsx”工作簿，在第一行前插入一空行。在A1输入“江南锡商科技有限公司员工培训成绩表”。2）“员工姓名”前插入一空列,

A2输入“员工号”：001，并填充。图1Excel2016窗口任务2编辑数据任务步骤3）“员工姓名”后插入两列，在C2,D2单元格依次输入“性别”，“所在部门”，并用数据验证输入内容。4）在A43，A44，A45依次输入“各科最高成绩”，“各科最低成绩”，“各科平均成绩”，按照样张输入设置格式。图1输入数据样张任务2编辑数据任务步骤5）在I43，I44，I45依次输入“员工总人数”，“女员工总人数”，“各科总分低于300分人数”，设置格式。7）设置A3:H45单元格区域外边框为粗边框，内边框为细边框。图1输入数据样张任务2编辑数据任务步骤8）保存“员工培训成绩表.xlsx”工作簿任务3计算数据任务说明在“员工培训成绩表.xlsx”工作簿中，按照以下要求计算：各科总分、各科平均分；各科最高成绩、各科最低成绩、各科平均成绩；员工人数、女员工人数、各科总分低于300分人数、并使用Rank函数按照总分降序排名。任务3计算数据任务步骤1）打开“员工培训成绩表.xlsx”。2）用SUM函数计算各科总分。3）用AVERAGE函数计算各科平均分。4）用RANK函数按照总分降序排名次。图1计算完成效果任务3计算数据任务步骤5）使用MAX函数各科计算最高成绩。6）使用MIN函数计算各科最低成绩。7）使用AVERAGE函数计算各科平均成绩。8）使用COUNT函数统计员工人数。图1计算完成效果任务3计算数据任务步骤9）使用COUNTIF函数统计女员工人数。10）使用COUNTIF函数统计各科总分低于300分人数。11）保存“员工培训成绩表.xlsx”。图1计算完成效果任务4跨工作表引用数据任务说明把“员工培训奖励.xlsx”工作簿中的“员工培训奖励”工作表复制到“员工培训成绩表”工作簿中的“员工培训成绩表”工作表之后。

在“员工培训奖励”工作表中，按照以下要求计算：使用if函数单科成绩90分及以上包含90分设置为1，否则为0；使用if函数计算所有科目90分及以上包含90分设置为1，否则为0；计算奖励总额:单科成绩90分及以上奖金每科100元，所有科目90分及以上奖金500元，最后保护的“员工培训奖励”工作表。任务4跨工作表引用数据任务步骤1）打开“员工培训成绩表.xlsx”，重命名sheet1工作表名为：“员工培训奖励”。2）把“员工培训奖励.xlsx”工作簿中的“员工培训奖励”工作表复制到“员工培训成绩表”工作簿中的“员工培训成绩表”工作表之后。3）退出“员工培训奖励.xlsx”工作簿，保存“员工培训成绩表.xlsx”工作簿。图1计算完成效果任务4跨工作表引用数据任务步骤4）使用IF函数计算B列单科成绩90分及以上设置为1，否则为0。5）同样方法计算C、D、E列数据或拖动填充C、D、E列数据。6）使用IF函数计算F列所有科目90分及以上设置为1，否则为0。图1计算完成效果任务4跨工作表引用数据任务步骤8）利用公式计算奖励总额。9）保护“员工培训奖励”工作表。图1计算完成效果任务5设置条件格式任务说明利用条件格式蓝色数据条渐变填充修饰“员工培训成绩表”工作表的“各科总分”列(I3:I42单元格区域）；将排名前三位的相应单元格背景颜色设置成红色。任务5设置条件格式任务步骤1）利用条件格式蓝色数据条渐变填充修饰各科总分列。任务5设置条件格式任务步骤2）将排名前三位的相应单元格背景颜色设置成红色。图1条件设置完成效果谢谢！信息技术基础—课程资源电子表格处理

2.2员工信息统计表制作信息技术基础—课程资源学习目标1.知识目标掌握公式的组成及使用方法。掌握函数的组成及使用方法。2.技能目标能应用函数从已有信息中提取有用数据。能熟练使用Excel的文本函数。能熟练使用Excel的日期函数。能使用相关函数统计数据。3.素质目标具有数据意识和从数据中挖掘信息的能力。养成不断精进技能的习惯，提高数据处理能力。案例分析完成本案例，需要学会应用Ｅｘｃｅｌ中的文本函数、日期函数、查找函数、统计函数等多种函数，具体操作分为３个任务：

具体操作可分为５个任务:任务1：编辑员工信息任务2：格式化工作表任务3：统计员工信息任务1

编辑员工信息任务说明

打开“员工信息统计表.xlsx”工作簿，利用已有的信息，完善员工信息统计表数据。任务1

编辑员工信息任务步骤1）打开“员工信息统计表.xlsx”工作簿。2）使用MID函数输入员工的出生年。3）用TEXT函数和MID函数提取身份证号码的出生日期：选中E3单元格，输入公式“=--TEXT(MID(C3,7,8),"0000-00-00")”或者输入公式“=DATE(MID(C3,7,4),MID(C3,11,2),MID(C3,13,2))”任务1

编辑员工信息任务步骤4）使用MID函数取身份证第17位。5）使用ISODD函数判断第17位是否奇数。6）使用IF函数输入性别男，女。7）使用LEFT函数取身份证的前两位图1编辑数据任务1

编辑员工信息任务步骤8）使用VLOOKUP函数从“地区代码与名称”工作表中根据“地区的代码”查找到相应的籍贯输入。

9）计算工龄。10）计算年龄。11）为保护隐私，使用REPLACE函数为联系电话打码，从第四位开始，中间的4位打*。图2数据完善后结果任务2格式化工作表任务说明打开“员工信息统计表.xlsx”工作簿,对表格中的文本和数据进行字体格式的设置，行高、列宽的调整，以及对单元格的边框和底纹的添加，使表格显得更加美观。图1完成效果图任务2格式化工作表任务步骤1）设置标题行的行高为30，第二行的行高为45，其余行的行高为15。2）设置标题行文字跨列居中，设置字体黑体、字号16号，其他行文字水平居中、垂直居中，宋体、11号。3）选中所有列自动调整列宽任务2格式化工作表任务步骤4）选中A2:T42单元格区域，设置外边框是深蓝色、双实线框线，内边框深蓝色细实线。5）选中A2:T2单元格区域，填充颜色：“白色，背景1，深色25%”。任务3统计员工信息任务说明打开“员工信息统计表.xlsx”工作簿,单击“员工信息统计”工作表标签，完成统计表1、统计表2、统计表3的数据统计，插入图表并美化。任务3统计员工信息任务步骤1）使用COUNTIF函数统计学历是专科、本科、硕士人数。2）使用公式计算不同学历人数所占百分比。选中C3单元格，输入公式“=B3/SUM($B$3:$B$5)”,按回车键，拖动填充。3）设置C3:C5单元格区域数值为百分比格式并保留两位小数。任务3统计员工信息任务步骤4）使用COUNTIFS函数计算统计表2的数据。5）使用COUNTIFS函数计算G6:G8、G9:G11、G12:G14单元格区域的值。6）使用SUMIF函数计算统计表3的数据。任务3统计员工信息任务步骤7）使用饼图直观显示不同学历占比。8）美化饼图：显示数据标签；图表标题修改为“学历占比图”。9）图表位置：图表插入到当前工作表的“A8:C20"单元格区域内。图1插入饼图任务3统计员工信息任务步骤10）使用簇状柱形直观显示不同职称人数对比。11）美化图表：图表标题修改为“职称人数对比图”，无图例。以“布局2”和“样式4”修饰图表。12）图表位置：图表插入到当前工作表的“J8:N20"单元格区域内。图2完成效果图谢谢！信息技术基础—课程资源电子表格处理

2.3员工工资查询表制作信息技术基础—课程资源学习目标1.知识目标了解函数嵌套的含义和使用方法。了解各种函数的使用方法。2.技能目标能熟练掌握IF函数的嵌套应用。能掌握绝对地址的使用方法。能掌握Excel公式的使用方法。能查询工资数据。。3.素质目标提升职业规划能力，具有终身学习意识。培养数据保护习惯和大局观意识。案例分析要完成该案例，需要学会应用Excel中的IF函数、公式、查找函数等多种数据计算功能，具体操作可分为４个任务：任务1：录入数据任务2：计算数据任务3：统计工资查询任务4：筛选数据任务1录入数据任务说明在打开“员工工资查询表.xlsx”工作簿中,录入基本工资，助理工程师的基本工资是3000，工程师的基本工资是3600，高级工程师的基本工资是4000；录入岗位工资，职员的岗位工资是3000，部门经理的岗位工资是4800；录入学历工资（使用IF()函数），硕士的学历工资是800，本科的学历工资是500，专科的学历工资是300，完善员工工资表数据。任务1录入数据任务步骤1）打开“员工工资查询表.xlsx”工作簿，使用IF函数录入基本工资。助理工程师的基本工资是3000，工程师的基本工资是3600，高级工程师的基本工资是4000。开始助理工程师TRUEFALSE工程师高级工程师3000元3600元4000元TRUETRUEFALSE任务1录入数据任务步骤2）使用IF函数录入岗位工资。职员的岗位工资是3000，部门经理的岗位工资是4800；3）使用IF函数录入学历工资。硕士的学历工资是800，本科的学历工资是500，专科的学历工资是300任务2计算数据任务说明计算奖金：利用if函数，根据绩效评分计算奖金，请根据“绩效评分奖金计算规则”计算表中的数据计算奖金列的内容。计算应发工资：应发工资=基本工资+岗位工资+学历工资+奖金+出差补贴，计算“扣款项目”中的“扣款合计”，计算“实发工资”，实发工资=应发工资-扣款合计。任务2计算数据任务步骤1）利用if函数，根据绩效评分计算奖金。在J4中输入公式:“=IF(I4>=90,5000,IF(I4>=80,3000,IF(I4>=70,2000,IF(I4>=60,1000,600))))”，TRUEFALSE开始绩效评分>=90TRUEFALSE绩效评分>=80绩效评分>=70绩效评分>=605000元3000元2000元1000元600元TRUETRUEFALSEFALSE任务2计算数据任务步骤7）计算扣税。选中Q4，在Q4中输入公式:“=IF(L4<=5000,0,IF(L4<=8000,(L4-5000-SUM(M4:P4))*3%,(L4-5000-SUM(M4:P4))*10%))”，按回车键，并利用填充柄填充其他员工扣税。8）计算养扣款合计。选中R4，在R4中输入公式:“=SUM(M4:Q4)”，按回车键，并利用填充柄填充其他员工扣款合计。9）计算实发工资。选中S4，在S4中输入公式:“=L4-R4”，按回车键，并利用填充柄填充其他员工实发工资。任务2计算数据任务步骤10）选中S4:S43单元格区域，选择“开始”→“数字”→“货币”命令。以上所有计算结果如图1所示。图1工资表计算结果图任务3统计工资查询任务说明使用VLOOKUP函数实现员工工资数据的查询，利用员工工资表统计员工工资数据。任务3统计工资查询任务步骤1）在打开的“员工工资查询表.xlsx”中，单击“员工工资查询”工作表标签，打开“员工工资查询”工作表。任务3统计工资查询任务步骤2）使用VLOOKUP函数查询员工姓名。单击选中E4单元格，选择“公式”→“函数库”→“插入函数”命令，弹出“插入函数”对话框，在“搜索函数”下输入“VLOOKUP”，单击“转到(G)”按钮，然后在“选择函数”列表框中选择“VLOOKUP”函数，单击“确定”按钮。图1VLOOKUP函数参数对话框任务3统计工资查询VLOOKUP函数：条件查找函数格式：LOOKUP(lookup_value,table_array,col_index_num,range_lookup)，即：VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)例如：=VLOOKUP($F$5,$B$1:$D$10,2,0)使用该函数时，需要注意以下几点：第4参数一般用0（或FASLE）以精确匹配方式进行查找。第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列。如果查找值与数据区域关键字的数据类型不一致，会返回错误值#N/A。查找值必须位于查询区域中的第一列。任务3统计工资查询任务步骤3）使用VLOOKUP函数查询员工实发工资。4）使用VLOOKUP函数查询员工工资明细的相关数据。参照2）和3）步骤，查询完成工资明细中所有栏目的工资数据查询,然后在E3单元格选择001员工号，员工工资查询结果（如图2所示）并保存工作簿。图2员工工资查结果任务3统计工资查询任务步骤5）在打开的“员工工资查询表.xlsx”中，单击“员工工资统计”工作表标签，打开“员工工资统计”工作表。6）使用AVERAGEIF函数计算统计表1中不同职称的职工平均应发工资。7）使用COUNTIF函数计算统计表1中不同职称的职工人数。8）使用公式计算相应职称人数占三种职称人数的百分比。任务3统计工资查询任务步骤9）使用COUNTIFS函数计算统计表2中应发工资小于等于9000,并且职称是助理工程师的人数。10）使用COUNTIFS函数计算统计表2中应发工资大于9000小于等于10000,并且职称是工程师的人数。11）使用COUNTIFS函数计算统计表2中应发工资大于10000,并且职称是高级工程师的人数。12）使用AVERAGEIFS函数计算符合统计表2中应发工资小于等于9000,并且职称是助理工程师的职工平均实发工资。任务3统计工资查询任务步骤13）使用AVERAGEIFS函数计算统计表2中应发工资大于9000小于等于10000,并且职称是工程师的职工平均实发工资。图2设置AVERAGEIFS函数参数对话框2图1设置AVERAGEIFS函数参数对话框1任务4筛选数据任务说明利用自动筛选功能，筛选出职称是“高级工程师”或“工程师”，并且“应发工资”在值范围为8000～11000元的记录；用高级筛选功能，筛选出基本工资大于等于4000元或应发工资大于11000元的员工信息。任务4筛选数据任务步骤1）单击行号“3”，即选中第3行所有数据。选择“数据”→“排序和筛选”→“筛选”命令，此时会发现标题行上的每一个列字段单元格的右侧都出现了一个“自动筛选”按钮。图1自动筛选任务4筛选数据任务步骤2）用自动筛选出职称是“高级工程师”或“工程师”，并且“应发工资”在值范围为8000～11000元的记录。图2自定义筛选结果任务4筛选数据任务步骤3）使用高级筛选功能，筛选出基本工资大于等于4000元或应发工资大于11000元的员工信息。图3高级筛选条件区域图4高级筛选结果任务4筛选数据高级筛选高级筛选可进行各种复杂的筛选，且可以将筛选出来的数据复制到当前表的其他位置，原数据清单保持不变。使用高级筛选需指定一个条件区域，设置条件区域：条件区域和数据区域中间必须要有一行以上的空行隔开。条件区域中条件的逻辑关系是:写在同一行的条件互为“与”关系;写在不同行的条件互为“或”关系;条件理解顺序:逐行解释，行内条件用“并且”，一行结束后用或者连接下一行的条件。任务4筛选数据高级筛选高级筛选注意事项：1）条件区域和数据区域中间必须要有一行以上的空行隔开。2）恢复源数据，当“在原有区域显示筛选结果”时，需要查看其它数据，单击“数据”→“排序和筛选”→“清除”命令即可。谢谢！信息技术基础—课程资源电子表格处理

2.4销售统计表制作信息技术基础—课程资源学习目标1.知识目标了解数据分析的工具。。理解图表和数据透视表的应用。2.技能目标能选择合适的公式或函数进行计算。能排序Excel电子表格的数据。能分类汇总Excel电子表格的数据。能创建与编辑Excel电子表格的图表。能制作Excel电子表格的数据透视表。3.素质目标具备数据分析能力，具有信息思维和创新精神。增强数据可视化表达能力。案例分析在Excel中要完成该案例需要使用数据验证、填充柄、函数计算、条件格式等功能，具体操作可分为５个任务:任务1：新建工作簿任务2：编辑数据任务3：计算数据任务4：跨工作表引用数据任务5：设置条件格式任务1完善数据任务说明根据“商品销售情况表”中的数据。计算各商品的降价幅度，降价幅度=（市场定价-成交单价）/市场定价（数值百分比格式，保留两位小数）；计算各商品的销售额置于“销售额(万元)”列，销售额(万元)=数量*成交单价/10000。利用RANK.EQ函数销售额排名由高到低的排名。利用IF函数给出“业绩表现”列的内容:如果销售额大于50万，在相应单元格内填入“业绩优秀”，如果销售额大于30万，在相应单元格内填入“业绩良好”，如果销售额大于20万，在相应单元格内填入"业绩合格"否则在相应单元格内填入"业绩差"。任务1完善数据任务步骤1）计算各商品的降价幅度。单击选定H3单元格→输入公式“=(E3-G3)/E3”，设置为百分比，保留两位小数。2）计算各商品销售额（万元）。单击选定J3单元格→在输入公式“=G3*I3/10000”，保留两位小数3）计算销售额排名。将光标定位到K3单元格→在编辑栏输入“=RANK.EQ(J3,$J$3:$J$34,0)”。任务1完善数据任务步骤4）利用if函数，根据销售额计算业绩表现。将光标定位到L3单元格→在编辑栏输入“=IF(J3>50,"业绩优秀",IF(J3>30,"业绩优良",IF(J3>20,"业绩合格","业绩差")))”图1

If函数流程图TRUEFALSE开始销售额>=50TRUEFALSE销售额>=30销售额>=20业绩优秀业绩优良业绩合格业绩差TRUETRUEFALSE任务1完善数据任务步骤5）复制“商品销售情况表”（移至最后）4次，依次命名为“销售数据排序”、“分类汇总数据”、“高级筛选”、“数据透视表”。任务2排序数据任务说明

在“销售数据排序”工作表中，按主要关键字“销售部门”自定义序列（销售一部、销售二部、销售三部）排序和次要关键字“类别”的降序进行排序。本任务是对商品销售数据进行排序。任务2排序数据任务步骤1）在“销售数据排序”工作表中，选中A2:L34或单击选中其中任意一个单元格。2）对所选数据按照主要关键字“销售部门”自定义序列（销售一部、销售二部、销售三部）排序图1“排序”对话框任务2排序数据任务步骤3）添加“次要关键字”：类别，并且按照类别降序排序。图2设置次要关键字的“排序”对话框任务2排序数据任务步骤排序后结果如图3所示。图3排序后结果任务3分类汇总任务说明

在对数据进行分析时，可以用Excel提供的“分类汇总”功能，按要求进行汇总。在“分类汇总数据”工作表中，按主要关键字“销售部门”自定义序列（销售一部、销售二部、销售三部）排序和次要关键字“类别”的降序进行排序，目的是完成数据分类，然后按照要求进行数据分类汇总。本任务是对“分类汇总数据”工作表中的数据按“销售部门”对“销售额（万元）”进行“求和”汇总，并对汇总数据进行数据图表分析。任务3分类汇总任务步骤1）在“分类汇总数据”工作表中，按主要关键字“销售部门”自定义序列（销售一部、销售二部、销售三部）排序和次要关键字“类别”的降序进行排序，完成数据分类。图1“排序”完成分类任务3分类汇总任务步骤2）将光标置于数据区域内，选择“数据”→“分级显示”→“分类汇总”命令，弹出“分类汇总”对话框。图2设置“分类汇总”图3“分类汇总”后结果任务3分类汇总任务步骤3）查看汇总结果4）去掉分类汇总的显示信息，可再次选择“数据”→“分级显示”→“分类汇总”命令，在弹出的“分类汇总”对话框中单击“全部删除”按钮。5）插入图表分析数据。单击工作表的左上方符号“2”，按住【Ctrl】键的同时选中“销售部门”数据和“销售额（万元）”的汇总结果，按【Alt】+【；】组合键。任务3分类汇总任务步骤6）选择“插入”→“图表”→“柱形图”→“簇状柱形图”命令，即可生成图表。7）美化图表。修改图表标题为“各销售部门销售额对比”，添加“数据标签”。添加主要纵坐标标题“单位（万元）”。8）将水平（分类）轴标签修改为“销售一部”“销售二部”“销售三部”任务3分类汇总任务步骤9）设置绘图区填充效果为“花束”的纹理填充。10）将图表插入到“分类汇总数据”工作表的“B40:F55”单元格区域内。调整图表大小并移动到B40:F55单元格区域内。11）结果如图1所示图1“各销售部门销售额对比”图表任务4设置高级筛选任务说明高级筛选无疑是针对自动筛选而言的，高级筛选具有效率高，方便，易操作等特点。本任务是在“高级筛选"工作表中，对工作表内数据清单的内容进行高级筛选(在数据清单前插入四行，条件区域设在A1∶L3单元格区域，请在对应字段列内输入条件)，条件是:销售部门为“销售一部”或“销售三部”且销售额排名在10（包含10）到20（包含20）之间，筛选结果复制到A42开始的单元格区域，工作表名不变，保存“销售数据表.xlsx工作薄。任务4设置高级筛选任务步骤1）切换到“高级筛选'工作表→选中A1:A4单元格区域，并单击鼠标右键→选择“插入…”→在插入对话框中→选择“整行”→单击确定按钮，在数据清单前插入四行。2）设置“高级筛选”的条件区域，如图1所示。图1“高级筛选”的条件区域任务4设置高级筛选任务步骤3）在“数据”功能区→“排序和筛选”组中→单击“高级”按钮→弹出“高级筛选”对话框→设置“列表区域”→$A$6:$L$38→设置“条件区域”