《WPS Office办公应用案例教程》（高乃尧）006-0 教案 第19课 使用函数计算数据

PAGE4PAGE4PAGE3PAGE3

课题使用函数计算表格数据课时2课时（90min）教学目标知识目标：了解函数的概念、类型及使用方法技能目标：能够根据需要利用函数计算工作表数据素质目标：增强自身的实践能力、思维能力和创新能力，做到举一反三、触类旁通教学重难点教学重点：函数的概念、类型及使用方法教学难点：根据需要利用函数计算工作表数据教学方法案例分析法、问答法、讨论法、讲授法教学用具电脑、投影仪、多媒体课件、教材教学过程主要教学内容及步骤课前任务【教师】布置课前任务，和学生负责人取得联系，让其提醒同学通过文旌课堂APP或其他学习软件，完成课前任务请大家了解WPS表格中使用函数的方法。【学生】完成课前任务考勤【教师】使用文旌课堂APP进行签到【学生】按照老师要求签到问题导入【教师】提出以下问题：你知道WPS表格中有哪些函数吗？【学生】思考、举手回答【教师】通过学生的回答引入要讲的知识传授新知【教师】讲解使用函数计算表格数据5.4使用函数计算表格数据5.4.1认识函数✈【教师】多媒体展示函数的构成，介绍函数的概念在WPS表格中，函数是一类特殊的、预定义的公式，主要用于复杂计算，又被称为“特殊公式”。函数通常表示为：函数名(参数1,[参数2],……)，括号中的参数可以有多个，中间用逗号分隔。在函数的表现形式中，带方括号的参数是可选参数，也有的函数没有参数，如图5-5所示。函数的参数可以是数字、文本、逻辑值（如TRUE或FALSE）、数组、已定义的名称、错误值（如“#N/A”）或单元格引用，也可以是常量、公式或函数。图5-5函数的构成（示例）在公式中合理地使用函数，可以实现诸如求和、逻辑判断和财务分析等众多数据处理功能。5.4.2输入与编辑函数使用函数时，首先要在单元格中输入“=”，即确保已进入公式编辑状态。接下来可输入函数名称，再紧跟着一对括号，括号内为一个或多个参数，参数之间用逗号分隔。1.手动输入函数手动输入方法一般用于参数比较单一，且比较常用的函数，即用户能记住函数的名称、参数等。✈【教师】讲解手动输入函数的步骤，并进行演示步骤1继续在打开的工作表中操作，计算优惠。根据A55单元格中的注释（1），在K3单元格中输入“=”，然后输入函数名“IF”和左括号“(”，紧跟着输入函数参数“I3>200,(J3*0.1),0”，最后输入右括号“)”。步骤2按“Enter”键确认，计算出第1条销售记录的优惠。因为该商品的单价不足200元，所以优惠为0。【小提示】IF函数的作用是根据指定条件执行真假值判断，结果为真则返回某个值，结果为假则返回另一个值。其语法格式为IF(测试条件,真值,[假值])，其中测试条件是计算结果为真值或假值的表达式，表达式为真时返回结果真值；表达式为假时返回结果假值。步骤3向下拖动K3单元格的填充柄到K53单元格后释放鼠标，计算出所有销售记录的优惠。2．通过“插入函数”按钮输入函数✈【教师】讲解通过“插入函数”按钮输入函数的步骤，并进行演示步骤1继续在打开的工作表中操作，计算成本单价。在M3单元格中单击，然后单击编辑栏左侧的“插入函数”按钮，打开“插入函数”对话框。步骤2在“或选择类别”下拉列表中选择函数所在类别“查找与引用”，然后在“选择函数”列表中选择所需函数“VLOOKUP”，单击“确定”按钮。【小提示】VLOOKUP函数的作用是在表格或数值数组的首列查找指定数值，并由此返回表格或数值数组当前行中指定列处的数值。……（详见教材）步骤3打开“函数参数”对话框，在“查找值”编辑框中单击，然后在工作表中选择查找值所在的单元格，这里在工作表中选择C3单元格，可看到选择的单元格地址显示在该参数编辑框中。步骤4在“数据表”编辑框中单击，然后切换到“商品列表”工作表，再在该工作表中拖动鼠标选择要查找的单元格区域，这里选择A2:F32单元格区域，再按“F4”键，将所选的单元格区域转换为绝对引用。……步骤5至步骤9详见教材【知识链接】当明确知道所需函数属于哪一类时，可以直接利用“公式”选项卡输入函数。例如，用于计算日期和时间的函数DAY、EOMONTH、TODAY等，属于日期和时间函数，此时可以通过“公式”选项卡中的“日期和时间”分类输入这些函数。3．函数的嵌套✈【教师】多媒体展示函数的嵌套，介绍函数嵌套的使用方法在有些情况下，可以将某个函数作为另一个函数的参数使用，形成函数的嵌套。=IF(AVERAGE(A2:A9)>=75,SUM(B2:B9),0)在图5-16所示的公式中，IF函数中嵌套使用了AVERAGE和SUM函数。该公式的含义是先将AVERAGE函数的结果与75进行比较，当单元格区域A2:A9的平均值大于或等于75时，该公式才会对单元格区域B2:B9求和，否则返回0。=IF(AVERAGE(A2:A9)>=75,SUM(B2:B9),0)嵌套函数嵌套函数嵌套函数嵌套函数图5-16函数的嵌套嵌套函数中的第一层函数通常可以使用前面介绍的方法输入，而作为参数使用的函数，可以使用如下两种方法输入。（1）在公式中直接输入嵌套的函数及其参数。（2）在“函数参数”对话框中单击参数编辑框，然后单击名称框右侧的下拉按钮，在展开的下拉列表中选择所需嵌套的函数，再在打开的对话框中设置其参数。5.4.3常用的函数类型✈【教师】多媒体展示常用的函数类型，介绍函数的使用方法WPS表格提供了大量的内置函数以供选用，如求和函数SUM、求平均值函数AVERAGE、逻辑判断函数IF等。表5-4列出了常用的函数类型和使用范例。表5-4常用的函数类型和使用范例函数类型函数使用范例常用函数SUM（求和）、AVERAGE（求平均值）、MAX（求最大值）、MIN（求最小值）等=AVERAGE(F2:F7)

表示求F2:F7单元格区域中数字的平均值统计函数COUNT（求含有数字的单元格的个数）、COUNTA（求非空单元格的个数）、COUNTIF（求满足给定条件的单元格的个数）、SUMIF（对符合指定条件的单元格求和）、RANK.EQ（求排名）等=COUNT(A1:A5)

表示求A1:A5单元格区域中含有数字的单元格的个数逻辑函数AND（与）、OR（或）、FALSE（假）、TRUE（真）、IF（判断）、NOT（非）等=IF(A3>=B5,A3*2,A3/B5)

表示使用条件测试A3是否大于或等于B5，为真返回A3*2，为假则返回A3/B5日期与

时间函数DATE（日期）、HOUR（小时数）、SECOND（秒数）、TIME（时间）等=DATE(2020,9,17)

表示返回2020年9月17日的序列值数学与

三角函数ABS（求绝对值）、EXP（求指数）、SIN（求正弦值）、COS（求余弦值）、MOD（求余数）、ROUND（四舍五入）、RAND（产生随机数）、LOG（求对数）等=ABS(E4)

表示求E4单元格中数值的绝对值查找与引用函数ADDRESS（返回单元格地址）、AREAS（返回区域个数）、COLUMN（返回列标）、LOOKUP（从向量或数组中查找值）、ROW（返回行号）等=ROW(C10)

表示返回引用单元格所在行的行号财务函数DB（求资产的折旧值）、IRR（求现金流的内部收益率）、PMT（求分期偿还额）等=PMT(B4,B5,B6)

表示在输入利率、总期数和本金作为变量时，计算每期付款额【教师】提出任务打开“学生成绩表”，计算学生总分、平均分，并对学生成绩进行排名。【学生】聆听要求、进行操作【学生】聆听、理解、记录效果展示【教师】通过PPT展示员工培训成绩统计表效果，并对其进行简单介绍【学生】观看案例效果、聆听课堂实训【教师】介绍使用公式和函数计算员工培训成绩统计数据的步骤，安排学生扫描微课二维码观看视频“使用公式和函数计算员工培训成绩统计数据”(详见教材），要求学生按照步骤进行操作下面以计算员工培训成绩统计表中的总分、成绩是否达标及排名为例，练习使用公式和函数计算表格数据的操作。步骤1打开本书配套素材“第5章”/“员工培训成绩统计表.xlsx”工作簿。步骤

2计算总分。根据A39单元格中的注释，在H3单元格中输入公式“=F3*0.4+G3*0.6”，按“Enter”键确认后双击H3单元格的填充柄，即可计算出所有员工的培训成绩总分。步骤3判断成绩是否达标。根据A40单元格中的注释，在I3单元格中输入公式“=IF(H3>=75,"达标","未达标")”，按“Enter”键确认后双击I3单元格的填充柄，即可根据总分判断出所有员工的培训成绩是否达标。步骤4根据总分计算排名。在J3单元格中单击，然后在“公式”选项卡的“其他函数”下拉列表中选择“统计”/“RANK.EQ”函数，打开“函数参数”对话框。步骤5在“数值”编辑框中单击，然后单击工作表中的H3单元格；在“引用”编辑框中单击，然后在工作表中选择H3:H36单元格区域，并按“F4”键将其转换为绝对引用；在“排位方式”编辑框中单击，输入“0”。【小提示】RANK.EQ函数的作用是返回一个数字在数字列表中的排位。其语法格式为RANK.EQ(数值,引用,排位方式)。……(详见教材)步骤6单击“确定”按钮，然后双击J3单元格的填充柄，计算出所有员工培训成绩的排名。步骤7突出显示未达标的单元格。选中I3:I36单元格区域，在“开始”选项卡的“条件格式”下拉列表中选择“突出显示单元格规则”/“等于”选项，打开“等于”对话框，在左侧的编辑框中输入“未达标”，在“设置为”下拉列表中选择“浅红色填充”选项，单击“确定”按钮，即可将“未达标”文本所在的单元格突出显示。【学生】自行扫码观看配套微课，按照要求进行操作，如遇问题可询问老师【教师】巡堂辅导，及时解决学生遇到的问题效果展示【教师】通过PPT展示国内手机市场出货量及5G手机占比表效果、表格分析后效果与进销存报表数据计算效果，并对其进行简单介绍【学生】观看案例效果、聆听综合实训【教师】介绍计算国内手机市场出货量及5G手机占比表数据的步骤，要求学生按照步骤进行操作5.5.1计算国内手机市场出货量及5G手机占比表数据打开本书配套素材“第5章”/“综合实训”/“国内手机市场出货量及5G手机占比表.xlsx”工作簿，然后计算月合计及5G手机月占比。（1）计算月合计。可利用“求和”按钮和拖动填充柄复制公式的方法计算月合计。需要注意的是，单击“求和”按钮后需根据实际情况重新选择要求和的单元格区域。（2）计算5G手机月占比。在F3单元格中输入公式“=D3/E3”，确定后双击F3单元格的填充柄即可。（3）为美观起见，请忽略E3:E26单元格区域中的错误提示，然后将相应单元格的下边框设置为粗底框线（选中相应单元格区域，然后在“开始”选项卡的“边框”下拉列表中选择“粗底框线”选项即可）。【学生】按照要求进行操作，如遇问题可询问老师【教师】巡堂辅导，及时解决学生遇到的问题【学生】对比自己的制作效果，对比操作方法，提升操作技巧【教师】介绍分析员工培训成绩统计数据的步骤，要求学生按照步骤进行操作5.5.2分析员工培训成绩统计数据打开本书配套素材“第5章”/“综合实训”/“员工培训成绩统计表.xlsx”工作簿，然后统计参加培训的员工人数，并分析各分数段人数及其占比。（1）利用COUNT函数统计培训人数。在A43单元格中单击，然后在“公式”选项卡的“其他函数”下拉列表中选择“统计”/“COUNT”选项，或在该选项卡的“常用函数”下拉列表中选择“COUNT”选项，打开“函数参数”对话框，在“值1”编辑框中单击，然后在工作表中选择A3:A36单元格区域，单击“确定”按钮，即可统计出参加培训的员工人数。【小提示】COUNT函数的作用是求含有数字的单元格的个数。其语法格式为COUNT(值1,值2,……)。值1,值2,……为包含或引用各种类型数据的参数（1～30个），但只有数字类型的数据才被计数。（2）利用COUNTIF函数计算各分数段人数。单击C43单元格后使用上述方法选择函数并打开“函数参数”对话框。在“区域”编辑框中单击，然后在工作表中选择H3:H36单元格区域，并按“F4”键将其转换为绝对引用；在“条件”编辑框中根据B43单元格中的说明输入条件“>=50”（总分都在50分以上），单击“确定”按钮。然后在编辑栏中复制该单元格中等号右侧的公式内容，在公式的右侧输入“-”号，再将复制的公式内容粘贴到“-”号右侧，之后修改复制的公式内容中的条件为“>=60”，按“Enter”键，得到“60分以下”的人数。【小提示】COUNTIF函数的作用是统计单元格区域中满足指定条件的单元格的个数。其语法格式为COUNTIF(区域,条件)，其中，区域为用于条件判断的单元格区域；条件的形式可以为数字、表达式、单元格引用或文本。（3）向下拖动C43单元格的填充柄到C47单元格后释放鼠标，然后根据相应单元格左侧的条件，依次修改各公式中的条件参数以得到计算结果。（4）在D43单元格中输入含有绝对引用的公式“=C43/$A$43”，确认后向下拖动D43单元格的填充柄到D47单元格后释放鼠标，即可计算出各分数段人数所占比例。【学生】按照要求进行操作，如遇问题可询问老师【教师】巡堂辅导，及时解决学生遇到的问题【学生】对比自己的制作效果，对比操作方法，提升操作技巧【教师】介绍计算进销存报表数据的步骤，要求学生按照步骤进行操作5.5.3计算进销存报表数据（1）根据“进销存表”工作表B14单元格中的注释①，在D4单元格中输入公式“=SUMIFS(入库表!D:D,入库表!A:A,"<"&$C$2,入库表!B:B,B4,入库表!C:C,C4)-SUMIFS(出库表!D:D,出库表!A:A,"<"&$C$2,出库表!B:B,B4,出库表!C:C,C4)”，然后向下拖动D4单元格的填充柄到D12单元格后释放鼠标。该公式的含义为将“入库表”中2022年3月1日前OPPOReno6Pro手机的入库量减去“出库表”中该日期前该款手机的出库量。本例公式条件中的比较运算符连接单元格地址引用时，一定要用&连接，并且"<"&$C$不要写成"<$C$"。【小提示】SUMIFS函数的作用是对指定单