办公软件应用项目式教程 课件 项目六 制作新员工考核成绩表

办公软件应用项目式教程Office2021学习目标了解数据处理和数据清理的基本概念掌握数据清理的常用方法掌握公式和函数的组成及使用方法会使用删除重复值、数据验证等方式清理数据会使用公式和函数等进行数据计算和统计分析、查询引导学生确立竞争意识，形成创新素养01制作“入职培训考核成绩表”——数据清理和公式02制作“制作新员工专业知识和技能考核成绩表”——常用函数03制作成绩查询小系统——公式和函数的应用目录CONTENTS6.1创建“入职培训考核成绩表”——数据清理和公式数据清理1公式2单元格引用31．数据处理预备知识数据清理公式单元格引用数据清理是指发现和更正数据的错误，筛选和清除重复数据，查找并补全缺失数据，转换和统一数据格式等，以提高数据使用的质量。数据处理是指对数据的采集、清理、存储、检索、加工、变换和传输。数据处理的目的是从大量的、杂乱无章的、难以理解的数据中抽取并推导出某些有价值、有意义的数据。2．数据清理3．数据清理的基本内容（1）尽可能赋予属性名和属性值明确的含义；（2）统一多个数据源的属性值代码；（3）去除无用的属性或空值属性；（4）去除重复属性；（5）去掉数据中的噪音、填充空值、丢失值和处理不一致数据；（6）合理选择关联字段；4．常用的数据清理预备知识数据清理公式单元格引用（1）删除重复值Excel表格中有时候会出现重复的记录，我们一般情况下不需要这些重复的记录，这就需要删除掉重复的记录。自动删除重复值可以单击“数据”菜单下的“删除重复值”按钮，然后在对话框中选择一个或多个包含重复值的列，点击“确定”就可以删除了。预备知识数据清理公式单元格引用（2）分列分列有两种方式：按照固定的宽度：选中需分列单元格，然后在“数据”菜单下选择“分列”，选择“固定宽度”,单击“下一步”，然后在数据预览窗口中单击，指定列宽，单击“下一步”，就完成了。按分隔符分列：选中需分列单元格，然后在“数据”菜单下选择“分列”，选择“分隔符号”，单击“下一步”，选定分隔符号（可以在括号里输入所列选项中没有的符号），单击“下一步“，单击“完成”。预备知识数据清理公式单元格引用（3）补齐缺失数据缺失数据是指原始数据表中的单元格值为空，或为NULL等。寻找缺失数据的方法有定位空值或筛选等。定位空值是指在“开始”菜单中单击“查找和替换”按钮下的“定位条件”，在“定位条件”对话框里选择“定位空值”，可以筛选出所有空值，然后进行替换或直接插入值。预备知识数据清理公式单元格引用（4）数据验证数据验证是数据处理中常用的方法，可以对数据的录入添加一定的限制条件，全面地控制输入的数据。在“数据”菜单下单击“数据验证”按钮，打开“数据验证“对话框，单击“设置”选项卡，在其中可以进行设置，如图6-5所示。“允许”下拉列表框中可以设置所选单元格的输入类型，如序列、整数、小数、日期等。“数据”下拉列表框中可以设置数据输入的范围条件，如小于、大于、介于、小于或等于等，并可在下方的文本框中设置数据范围，包括最小值、最大值等，这些参数组合起来就确定了数据的输入范围。5．公式预备知识数据清理公式单元格引用Excel中的公式是以“=”开头，通过使用运算符将数据、单元格引用、函数等元素按一定顺序连接在一起，从而实现对工作表中的数据执行计算的等式。如图6-8所示。6．单元格引用预备知识数据清理公式单元格引用单元格的引用分为相对引用、绝对引用和混合引用。公式中的单元格相对引用(例如A1)是基于包含公式和单元格引用的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。单元格中的绝对引用(例如$F$6)总是在指定位置引用单元格F6。如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要时才将它们转换为绝对引用。混合引用具有绝对列和相对行，或是绝对行和相对列两种情况。绝对引用列采用$A1、$B1等形式。绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。任务实施（详情操作步骤请扫描右侧二维码。）1．删除重复值2．查找和替换3．使用公式计算任务一制作入职培训考核成绩表6.2制作“新员工专业知识和技能考核成绩表”——常用函数函数1常用函数21．函数预备知识函数常用函数函数是一种预设好的、特殊的公式。函数可以简化公式的输入过程，提高计算效率。Excel中的函数包括“=、函数名、小括号和函数参数”四部分，如图6-18所示。其中函数名称表示函数的功能，每个函数都具有唯一的函数名称；函数参数指函数运算对象，可以是数字、文本、逻辑值、表达式、引用或其他函数等。2．常用函数预备知识函数常用函数SUM函数：属于数学函数，计算指定单元格区域中所有数值的和。其语法格式为：SUM(number1,number2,…)。如“=SUM(C3:J3)”表示将C3:J3单元格区域中的数值相加求和。和SUM函数相关的函数有SUMIF函数、SUMIFS函数等。AVERAGE函数：属于统计函数，返回其参数的算术平均值。其语法格式为：AVERAGE(number1,number2,…)。如“=AVERAGE(C3:J3)”表示求C3:J3单元格区域中的数值的平均值。相关函数有AVERAGEA函数、AVERAGEIF函数、AVERAGEIFS函数等。RANK.EQ函数：属于统计函数，返回某数字在一列数字中相对于其他数值的大小排名，如果多个数值排名相同，则返回该组数值的最佳排名。其语法格式为：RANK.EQ(number,ref,order)。如“=RANK.EQ(K3,$K$3:$K$8,0)表示求K3单元格中的数据在K3:K8区域之间的排名，排名方式为降序。RANK()函数也能实现排名的功能，主要用在Excel2007及之前的版本。2．常用函数预备知识函数常用函数IF函数：属于逻辑函数，判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。其语法格式为：IF(logical_test,value_if_true,value_if_false)。如”=IF(L3>80,”优秀”,”合格”)表示如果L3单元格中的值大于80，则返回文本“优秀”，否则就返回文本“合格”。3.NULL值NULL表示的是空值，如果单元格为空，数值计算时Excel会把空值作为数值0进行计算。任务实施（详情操作步骤请扫描右侧二维码。）1．SUM函数和AVERAGE函数2．RANK.EQ函数3．IF函数任务二制作新员工知识和技能考核成绩表6.3制作“成绩查询小系统”——公式和函数的应用制作成绩统计表1制作查询小系统21．常用函数预备知识常用函数COUNT函数属于统计函数，计算区域中包含数字的单元格的个数。其语法格式为：COUNT(value1,value2,…)。如“=COUNT(C3:C8)”表示求C3:C8单元格区域中包含数字的单元格个数。此函数只对数据型数据进行计数。COUNTA函数属于统计函数，计算区域中非空单元格的个数。其语法格式为：COUNTA(value1,value2,…)。如“=COUNTA(C3:C8)”表示求C3:C8单元格区域中非空单元格的个数。和COUNT不同的是，COUNTA函数可以对任何类型数据进行计数。COUNTIF函数也属于统计函数，计算某个区域中满足给定条件的单元格数目。其语法格式为：COUNTIF(Range,Criteria)。如“=COUNTIF(C3:C8,">=80")”表示统计C3:C8单元格区域中大于或等于80分的成绩数量。COUNTIFS函数也属于统计函数，统计一组给定条件所指定的单元格数。其语法格式为：COUNTIFS(Criteria_range1,Criteria1,Criteria_range2,Criteria2,…)。MAX函数：属于统计函数，返回一组数值中的最大值，忽略逻辑值及文本。其语法格式为：MAX(number1,number2,…)。如“MAX(C3:C8)”表示统计C3:C8单元格区域数值中的最大值。MIN函数：属于统计函数，返回一组数值中的最小值，忽略逻辑值及文本。其语法格式为：MIN(number1,number2,…)。如“MIN(C3:C8)”表示统计C3:C8单元格区域数值中的最小值。SUMIF函数：属于数学函数，对满足条件的单元格求和。其语法格式为：SUM(Range,Criteria,Sum_range)。如“=SUMIF(D3:D8,">=80",K3:K8)”表示对满足条件“D列大于或等于80分的”，K3:K8单元格区域中的成绩求和，其中第三个参数可以省略。SUMIFS函数：属于数学函数，对一组给定条件指定的单元格求和。其语法格式为：SUMIFS(sum_range,criteria_range,criteria,…)。如“=SUMIFS(K3:K8,I3:I8,">=80",D3:D8,">=80")”表示对满足条件“I列数据大于或等于80分并且D列数据大于或等于80分的”，K3:K8单元格区域中的成绩求和。INT函数：属于数学函数，将数值向下取整为最接近的整数。其语法格式为：INT(number)。如“=INT(L3)”表示对L3单元格中的数据进行取整，不管小数大小，一律舍去，取小于或等于L3单元格中数据的整数。ROUND函数：属于数学函数，返回某个数字按指定位数四舍五入后的数值。其语法格式为：=ROUND(Number,Num_digits)。如“=ROUND(L3,1)”表示对L3单元格中的数据进行四舍五入，保留1位小数。VLOOKUP函数:属于查找与引用函数，查找数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。其语法格式为：Vlookup(lookup_value,table_array,co