4.3.5 函数的应用（课件）2024年《计算机应用基础教程》（航空工业出版社）同步教学

第四章 Excel 2010电子表格的使用 Use of Excel 2010 Spreadsheet 1 Excel 2010是Microsoft Office中的一个电子表格应用程序。它可以方便地对电子表格中的数据进行管理、计算、排序、筛选和分类汇总等，也可以根据表格中的数据快速生成各种图表。 本章主要学习Excel电子表格的制作，单元格格式的设置，公式与函数的应用，图表的创建与修饰，数据清单的排序、筛选、分类汇总等。其中编辑工作表、输入公式和建立图表是考核重点。下面对本章的考核知识点进行全面分析。 掌握Excel的基本功能，理解工作簿、工作表、单元格地址的概念。 熟练掌握工作表数据的录入、编辑方法；会对工作表进行排版等格式化操作。 知识目标 熟练掌握图表的创建方法，会对图表对象进行编辑与修饰。 熟练掌握单元格数据的引用方法；熟练使用公式和函数对表格数据进行计算。 熟练掌握数据清单的排序、筛选、分类汇总等操作。 会使用数据透视表分析数据。 3 会新建、保存和打开工作簿。 会编辑工作表内容，对工作表进行格式化操作。 技能目标 会创建图表，编辑图表对象，美化修饰图表。 会使用公式和函数对表格数据进行计算。 会对数据清单进行排序、筛选、分类汇总等操作。 会利用数据列表来创建数据透视表；会打印工作表。 4 难易程度： 考核概率：10%~100% 技能目标 知识要点与考试概率分析表 60% 使用工作表和单元格 设置单元格格式 设置条件格式 输入公式 创建图表 编辑、修饰图表 1 2 3 4 5 6 35% 80% 10% 100% 90% 50% 5 难易程度： 考核概率：10%~100% 技能目标 知识要点与等级考试考核概率分析表 工作表的数据库操作 数据排序 数据筛选 数据分类汇总 建立数据透视表 打印工作表 1 2 3 4 5 6 10% 60% 75% 40% 10% 2% 6 教学案列 对“学生成绩表”进行统计分析，计算每位学生课程的总分与平均分，排出名次，为下学期的评优工作做好准备； 需要对每门课程的成绩分布情况、优秀率、及格率进行计算分析，对学生各门课程的掌握情况做到心中有数； 及时通知课程不及格的学生认真复习，为下学期开学初的补考做好准备。 班级成绩表的设计与制作 4-1 学期结束前，各班级辅导员首先要收集本班学生的各科成绩。 7 教学案列 班级成绩表的设计与制作 4-1 辅导员可以使用Excel制作“学生成绩表”，借助其强大的数据计算与处理功能，能够大大减少工作量，轻松完成任务。制作好的“学生成绩表”如图4-1所示 （最终效果可参见素材与实例→第4章→班级成绩表） 图4-1 学生成绩表 8 4.3 Excel公式与函数的使用 在单元格中输入公式和函数，可以对表中数据进行求和、求平均，以及其他更为复杂的运算，从而可以避免手工计算的繁杂和易错。此外，当数据修改后公式的计算结果也会自动更新。 9 “=Al*3+SUM(D1:D4)” 4.3.5 函数的应用 函数就是常用公式的简化形式：Excel提供了11类函数，每一类又包含很多个不同的函数。 “常用函数”类中有求和函数SUM()、求平均值函数AVERAGE()、求最大值函数MAX()、计数函数COUNT()等函数。单击“自动求和”按钮 ，实际调用的是SUM()函数。 例如 函数 单独使用 混合使用 如: 如: “=SUM(A1:B6)” 提高计算效率 10 10 4.3.5 函数的应用 函数的形式：函数名（参数1，参数2…）。 1．函数的形式 在Excel中使用函数的要求如下： （1）函数必须有函数名。 （2）函数名后的圆括号必不可少。 （3）参数可以是数值、文字、单元格引用、或者套用的其他函数。 （4）各参数之间用“,”分隔。 （5）参数可以有，也可以没有；可以有一个，也可以有多个。 11 11 SUM(A2:A3,C4:D5)有2个参数，表示计算两个不连续区域A2:A3与C4:D5的和。 AVERAGE(26,C2,A1:C1)有3个参数，表示计算26，C2单元格，A1:C1单元格区域中数据的平均值。 PI()返回圆周率的值（3.14159265），此函数无参数。 NOW()返回计算机系统内部时钟的日期和时间，此函数无参数。 例如 12 12 4.3.5 函数的应用 在公式中使用函数，可以使用编辑栏的“插入函数”按钮 来完成，也可以使用“公式”选项卡“函数库”命令组中的“插入函数”按钮来完成，这两种方法类似。 2．函数的输入 以在F6单元格输入公式“=A6+B6*AVERAGE(C6:E6)”为例，具体操作步骤如下： 步骤1 单击需要输入公式的F6单元格，使之成为活动单元格。 步骤2 在编辑栏输入“=”，然后输入“A6+B6*”。 13 13 4.3.5 函数的应用 步骤3 单击编辑栏左侧的“插入函数”按钮 ，打开“插入函数”对话框，如图4-28所示。 图4-28 “插入函数”对话框 14 14 4.3.5 函数的应用 步骤4 在“选择函数”列表中选择AVERAGE函数，单击“确定”按钮。此时会弹出“函数参数”对话框，并在参数“Number1”文本框中出现系统预测的求平均的区域，如图4-29所示。 图4-29 “函数参数”对话框 单元格拾取按钮 15 15 4.3.5 函数的应用 步骤5 若系统预判的单元格区域不正确，可单击“单元格拾取”按钮 ，此时“函数参数”对话框缩为一个窄条，然后释放鼠标。在工作表中选择要进行操作的C6:E6单元格区域，如图4-30所示，单击“单元格拾取返回”按钮 ，返回“函数参数”对话框，单击“确定”按钮，完成计算。 图4-30 缩为窄条并释放鼠标的函数参数对话框 16 16 4.3.5 函数的应用 Excel中常用的函数见表4-3： 3．常用函数 函数名 功能 类别 应用示例与说明 SUM(Al,A2,…) 求各参数的和。A1，A2等参数可以是数值或含有数值的单元格的引用 数据计算 SUM(2,3)= 5 AVERAGE(Al,A2,…) 求各参数的平均值。A1，A2等参数可以是数值或含有数值的单元格的引用 数据计算 AVERAGE(3,5)= 4 MAX(Al,A2,…) 求各参数中的最大值 数据计算 MAX(1,6)= 6 MIN(Al,A2,…) 求各参数中的最小值 数据计算 MIN(1,6)= 1 COUNT(Al,A2,…) 求各参数中数值型数据的个数。参数的类型不限 条件统计 COUNT(12,6,A)= 2 表4-3 常用函数一览表 17 17 4.3.5 函数的应用 函数名 功能 类别 应用示例与说明 COUNTA(Al,A2,…) 返回参数列表中非的个数 统计计算 COUNTA(12,6,A)= 3 ROUND(Al,A2) 根据A2对数值A1进行四舍五入。A2>0表示舍入到A2位小数，即保留A2位小数；A2=0表示保留整数；A2<0表示从整数的个位开始向左对第K位进行舍入，其中K是A2的绝对值 数据计算 ROUND(136.725,1)=136.7ROUND(136.725,2)=136.73ROUND(136.725,0)=137ROUND(136.725,-1)=140 INT(A1) 取不大于数值A1的最大整数 数据计算 INT(12.23)=12INT(-12.23)=-13 ABS(A1) 取A1的绝对值 数据计算 ABS(-12)=12 IF(P,T,F) 其中P是能产生逻辑值（真或假）的条件表达式，T和F是表达式。若P为真，则取T表达式的值；若P为假，取F表达式的值 条件计算 IF(6>5,10,-10)=10 COUNTIF(R,C) 统计选定单元格区域中符合指定条件的单元格数目，其中R指范围，C指条件 条件统计 假设B3:B6单元格区域中的内容分别为32、54、75、86。COUNTIF(B3:B6,">55")=2 18 18 4.3.5 函数的应用 函数名 功能 类别 应用示例与说明 NOW() 返回系统当前的日期和时间 日期时间 2017-10-28 11:06 LEFT(T,N) 从文本字符串T的第一个字符开始，截取指定数目为N个字符 字符截取 LEFT("Sale Price",4)="Sale" SUMIF(Range,Criteria,Sum_range) 对满足条件的单元格求和 条件计算 Range指条件判断的单元格区域；Criteria指判断条件；Sum_range指用于求和计算的单元格区域 AVERAGEIF(range, criteria, average_range) 返回某个单元格区域内满足给定条件的所有单元格的平均值 条件计算 Range指条件判断的单元格区域；Criteria指判断条件；average_range指用于求平均值的单元格区域 RANK(Number,Ref,Order) 返回某数字在一列数字中相对于其他数值的大小排位 排名计算 Number是要查找排名的数字；Ref是对参与排名的一组数的绝对引用；Order是降序或者升序（非零值） 19 19 “SUM(AVERAGE(A2:C2),16)，其中SUM()是一级函数，AVERAGE()作为二级函数，其计算出的平均值是一级函数SUM()的一个参数。计算顺序是首先执行AVERAGE()函数，再执行SUM()函数。 4.3.5 函数的应用 4．函数嵌套 “函数嵌套”是Excel函数应用中的一个技巧，它是指将一个函数作为另一个函数的参数来使用。 例如 20 20 【提示】 二级函数的返回值必须是一级函数所允许的参数类型，且函数左右括号一定要对称。 4.3.5 函数的应用 如图4-31所示，以IF函数为例，对学生成绩表中“成绩等级”项按照“五级记分制”进行计算，条件是如果“计算机基础”成绩>=90，则为“A”，成绩>=80，则为“B”，成绩>=70，则为“C”，成绩>=60，则为“D”，成绩<60，则为“E”。我们使用“函数嵌套”来计算，公式为：=IF(D3<=60,"E",IF(D3<=70,"D",IF(D3<=80,"C",IF(D3<=90,"B","A"))))。 图4-31 函数嵌套 22 22 4.3.5 函数的应用 如果公式有错误，Excel将显示一个错误值。例如，在需要输入数值型数字的公式中错误地使用了文本，或删除了被公式引用的单元格，都会产生错误值。另外，错误值也可能不是由公式本身引起的，如公式产生错误值“#N/A”或“#VALUE”，则是公式所引用的单元格可能有错误。 5．常见错误信息 23 23 4.3.5 函数的应用 常见错误一览表如表4-4所示。 错误值 错误解释 # # # # # # # （1）单元格公式所产生的结果太长，单元格容纳不下 （2）对日期和时间做减法时产生了负值 #VALUE! 使用了错误的函数参数或运算对象的类型有错误 #DIV/0! 当公式中分母为0（零）时，会产生错误值#DIV/0! #NAME? 在公式中使用Excel不能识别的文本时将产生错误值#NAME? #N/A 当在函数或公式中没有可用数值时，将产生错误值#N/A #REF! 当单元格引用无效时产生错误值#REF! #NUM! 当公式或函数中某个数字有问题时，将产生错误值#NUM! #NULL! 当试图为两个并不相交的单元格区域指定交叉点时，将产生错误值#NULL! 表4-4 常见错误一览表 24 24 【任务4-6】 对完成格式设置的“学生成绩表”进行计算。 利用SUM()函数计算总分。 利用AVERAGE()函数计算平均分。 利用IF()函数判断学生是否需要补考。 利用RANK()函数计算名次。 4.3.5 函数的应用 26 操作提示： （1）IF函数。 函数功能：对满足条件的数据进行处理，条件满足返回前一个值，条件不满足返回后一个值。 参数说明：IF函数中的参数logical-test是一个逻辑判断式，结果为真或假；value-if-true是当判断结果为真时的函数返回值；value-if-false是当判断结果为假时的函数返回值。 函数格式： IF(logical-test,value-if-true,value-if-false)。 任务示例：如判断“柴睿鑫”同学是否补考，可在I3单元格中输入公式=IF(D3<60,"t",IF(E3<60,"t",IF(F3<60,"t","f")))。 26 4.3.5 函数的应用 27 操作提示： （2）RANK函数。 函数功能：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值。 参数说明： number为参与计算的数字或含有数字的单元格； ref是对参与计算的数字单元格区域的绝对引用； order是用来说明排序方式的数字。 如果order为0（零）或省略，为降序排列。如果order不为零，为升序排列。函数RANK对重复数的排位相同，但重复数的存在将影响后续数值的排位。 例如，在一列按升序排列的整数中，如果整数25出现两次，其排位为3，则26的排位为5（没有排位为4的数值）。 任务示例：如计算“柴睿鑫”同学的名次，可在J3单元格中输入公式=RANK(G3,$G$3:$G$14)。 函数格式： RANK(number,ref,order)。 27 【任务4-7】 计算各门课程的平均分、最高分、最低分、优秀人数、不及格人数、课程优秀率和及格率、班级总人数、各分数段的人数、男女生的平均分、参加补考人数。 4.3.5 函数的应用 29 操作提示： （1）MAX最大值和MIN最小值函数。 函数功能：AX()函数返回参数列表中的最大数值；MIN()函数返回参数列表中的最小数值。 函数格式： MAX/MIN(numberl,number2,…)。 任务示例：如计算“各门课程最高分”，可在D17单元格输入公式=MAX(D3:D14)。 任务示例：如计算“各门课程最低分”，可在D18单元格输入公式=MIN(D3:D14)。 29 4.3.5 函数的应用 30 操作提示： COUNTIF函数。 函数功能：统计某一区域中符合条件的单元格数目。 参数说明：range为需要计算其中满足条件的单元格数目的单元格区域；criteria是以数字、表达式或文本形式定义的条件，其中数字可以直接写入，表达式和文本必须加引号。 函数格式： COUNTIF(range,criteria)。 任务示例：如计算“各门课程优秀人数（>=85）”，可在D19单元格输入公式=COUNTIF(D3:D14,">=85")；“各门课程不及格人数（<60）”，可在D20单元格输入公式=COUNTIF(D3:D14,"<60")。 30 4.3.5 函数的应用 31 操作提示： （3）COUNTA函数。 函数功能：COUNTA函数返回参数列表中单元格的个数。 参数说明：value1,value2,…为包含或引用各种类型数据的参数，但所有类型的数据都被计算（区别于COUNT函数，COUNT函数仅计算放置数值型数据单元格的个数）。 函数格式： COUNTA(value1,value2,…)。 任务示例：如计算“班级总人数”，可在I16单元格输入公式=COUNTA(B3:B14)。 31 4.3.5 函数的应用 32 任务示例： 其余单元格计算公式如下： “各门课程及格率”，可在D21单元格输入公式=COUNTIF(D3:D14,">=60")/$I$16 “各门课程优秀率”，可在D22单元格输入公式=COUNTIF(D3:D14,">=85")/$I$16 “平均分（90～100）人数”，可在I17单元格输入公式=COUNTIF(H3:H14,">=90") “平均分（80～89）人数”，可在I18单元格输入公式=COUNTIF(H3:H14,">=80"-I17 “平均分（70～79）人数”，可在I19单元格输入公式=COUNTIF(H3:H14,">=70")-I17-I18 “男生平均分”，可在I20单元格输入公式=SUMIF(C3:C14,"男",H3:H14)/COUNTIF (C3:C14,"男")； “女生平均分”，可在I21单元格输入公式=SUMIF(C3:C14,"女",H3:H14)/COUNTIF (C3:C14,"女") “参加补考总人数”，可在I22单元格输入公式=SUM(D20:F20) 32 【任务4-8】 【任务4-8】 完成“某单位人员学历情况表”的录入 利用常用函数对上表进行编辑计算 4.3.5 函数的应用 “某单位人员学历情况表”计算结果如图4-32所示。具体要求如下： 图4-32 函数的综合使用示例 （1）利用“AVERAGE”函数计算职工平均年龄。 （2）利用“RANK”函数给职工的年龄排名。 （3）利用“IF”函数计算“年龄大于25并小于40”的职工，在备注栏添加“中青年”。 （4）利用“COUNT”函数计算职工总人数。 （5）利用“COUNTIF”函数分别计算“本科”“硕士”和“博士”的职工人数。 （6）利用“SUMIF”函数分别计算“本科”“硕士”和“博士”职工的平均年龄。 （7）利用“MAX”和“MIN”函数计算职工年龄中的最大值和最小值。 35 35 4.3.5 函数的应用 具体操作步骤如下： 步骤1 利用“AVERAGE”函数求平均值。选定D13单元格，在“开始”选项卡“编辑”命令组单击“自动求和”下拉按钮，在展开的列表中选择“平均值”选项，单元格中出现了求平均值函数“=AVERAGE()”，Excel自动选定了范围D3:D12，按“Enter”键或单击按钮 确认。如果Excel自动选取的数据范围不是需要的范围，可以通过鼠标重新选取范围。 步骤2 利用“RANK”函数排名。选定E3单元格，单击“插入函数”按钮 ，在打开的对话框找到“RANK”函数，单击“确定”按钮。在RANK函数的“函数参数”对话框输入如下内容：在“number”参数文本框中输入“D3”，“Ref”中输入“$D$3:$D$12”，“Order”中输入“0”（降序），单击“确定”按钮即可。利用自动填充功能，拖拽到D12完成其他单元格的计算。计算公式：E3=RANK(D3,$D$3:$D$12,0)。 36 36 4.3.5 函数的应用 步骤3 利用“IF”函数填写备注。根据题目要求：“年龄大于25并小于40”的职工，在备注栏里添加“中青年”，需要使用“AND”函数。Excel中AND函数可以用来对多个条件进行判断，当所有的条件都满足时返回“TURE”，只要有条件不满足时就返回“FALSE”。函数形式如下：AND(logical1,logical2, ...)，最多可以有30个条件。 在“常用函数”中找到IF函数，打开IF函数的“函数参数”对话框。在参数“Logical_test”文本框中输入“AND(D3>25,D3<45)”，“Value_if_ture”中输入“中青年”，“Value_if_false”中输入“”，单击“确定”按钮。其他单元格的计算同样使用填充柄拖拽完成。计算公式：F3=IF(AND(D3>25,D3<40),"中青年"," ")。 37 37 【提示】 在函数参数中文本信息的输入需要添加英文双引号“""”完成。在结果输出时，如果希望函数参数什么都不显示，则直接输入英文的双引号即可。 4.3.5 函数的应用 步骤4 利用“COUNT”函数计算职工总人数。选定H4单元格，输入计算公式：H4=COUNT(D3:D12)。 步骤5 利用“COUNTIF”函数计算各学历层次的人数。选定H6单元格，打开COUNTIF的“函数参数”对话框，在参数“RANGE”文本框中输入“$C$3:$C$12”，“Criteria”中输入“本科”或选择“G6”单元格，单击“确定”按钮。“硕士”和“博士”人数的计算方法与“本科”的计算方法相同。注意“绝对引用”的使用方法。计算公式：H6=COUNTIF($C$3:$C$12,G6)。 39 39 4.3.5 函数的应用 步骤6 利用“SUMIF”函数计算各学历层次的总年龄。“SUMIF”函数是对符合条件的数据区域进行求和。在本例中，首先用SUMIF求“本科”组人员的年龄总和，再除以“本科”组人数，得到“本科”组平均年龄。选定H10单元格，打开SUMIF函数的“函数参数”对话框，在参数“RANGE”文本框中输入“$C$3:$C$12”，“Criteria”中输入“G6”，“Sum_range”中输入“$D$3:$D$12”，单击“确定”按钮。计算公式：H10=SUMIF($C$3:$C$12,G6,$D$3:$D$12)/H6。 步骤7 计算年龄“最大值”。选定D14单元格，输入计算公式：D14=MAX(D3:D12)。 同理，年龄“最小值”的计算公式：D15=MIN(D3:D12)。完成效果如图4-32所示。 40 40 教学案列 书店每隔一段时间都要对所销售的教材进行各季度销售量的统计对比，以决定下个季度订购教材的种类与数量。 由于表格形式无法直观地反映数据之间的联系与对比。为此，使用Excel来使得数据更加直观、易懂。 “第二、四季度教材销售统计图”如图4-33所示，从中可看到图表的形式要比二维表格的形式更加形象和直观。 教材销售统计图表的制作 4-2 图4-33 “第二、四季度教材销售统计图”示例 最终效果文档可参见素材与实例→第4章→图表示例 41 汇报人：WPS 谢谢观看 $$