Excel 实验指导-函数的使用

./实验八学生成绩表数学函数、统计函数实验目的1、掌握数组公式、Sum<>函数2、掌握Averageif<>和Sumif<>3、掌握COUNT<>、COUNTA<>、COUNTIF<>、Countblank<>4、RANK.EQ5、数据库函数的使用实验容1、利用数组公式或Sum<>函数来统计每个同学上学期的总分。2、利用Averageif<>和Sumif<>统计平均分和总分。3、利用统计函数统计班级人数,每门课程不及格人数,缺考科目数。4、对班级同学的考试情况进行排名。5、选择合适的数据库函数统计信息实验任务小王是班级学习委员,现正值新学期评优时期,班主任委托小王统计班级同学上学期的考试成绩情况。小王要应用函数分析学生信息、计算考试成绩,分析每科成绩的最高分、最低分和平均分,统计每个学生的总分排名,并统计不同寝室的学习情况。本例效果图如REF_Ref373353617\h图9-1所示,小王需要完成的工作包括：〔1统计每个同学各门课程的总分并排名。〔2统计每个寝室的平均分。〔3统计每门课程的不及格人数和缺考人数。〔4统计符合特定条件的学生信息。图9-SEQ图9-\*ARABIC1学生成绩表效果图9.3

案例实现9．3．1统计班级每个学生的考试总分1．使用一般公式方法公式是Excel工作表中进行数值计算的等式,公式输入是以"="开始的,简单的公式有加、减、乘、除等计算。我们可以在I3单元格中编辑公式,输入"=D3+E3+F3+G3+H3”2．数组公式计算总分Excel中数组公式非常有用,尤其在不能使用工作表函数直接得到结果时,数组公式显得特别重要,它可建立产生多值或对一组值而不是单个值进行操作的公式。输入数组公式首先必须选择用来存放结果的单元格区域〔可以是一个单元格,在编辑栏输入公式,然后按Ctrl＋Shift＋Enter组合键锁定数组公式,Excel将在公式两边自动加上花括号"{}"。注意：不要自己键入花括号,否则,Excel认为输入的是一个正文标签。利用数组公式计算I3：I32单元格的总分。选中I3：I32单元格,然后按下"="键编辑加法公式计算总分,因为数组公式是对一组值进行操作,所以直接用鼠标选择D3：D32,按下"+"号,再用鼠标选择其余科目成绩依次累加,然后按Ctrl＋Shift＋Enter组合键完成数组公式的编辑,如REF_Ref373353670\h图9-2所示。图9-SEQ图9-\*ARABIC2数组公式在数组公式的编辑过程中,第一步选中I3：I32单元格尤为关键。绝不能开始只选中I3单元格,在最后用填充柄填充其他单元格,那样其他单元格的左上角将会出现绿色小三角,是错误的方法。3．使用Sum<>函数计算总分Sum<>求和函数,可以用来计算总分列。选择I3单元格,使用"公式"→"插入函数"或"自动求和"按钮,可选择Sum<>函数,选中求和区域D3:H3,如REF_Ref373353689\h图9-3所示,按Enter键,求和结果显示在单元格中。通过填充操作完成其余各行总分的计算。图9-SEQ图9-\*ARABIC3Sum<>函数参数对话框9．3．2统计班级相关人数1．使用Count<>、Counta<>函数统计班级人数Count<>函数统计含有数字的单元格个数,统计全班学生人数的时候可以选择统计寝室号或各科成绩列单元格的个数。选中N3单元格,使用"公式"→"插入函数"按钮,在搜索函数中输入"count"后单击"转到"按钮,如REF_Ref373353714\h图9-4所示,打开"函数参数"对话框,在Value1文本框中输入C3:C32,表示统计该区域包含数字的单元格个数,如REF_Ref373353729\h图9-5所示,单击确定按钮,完成输入。图9-SEQ图9-\*ARABIC4插入Count<>函数图9-SEQ图9-\*ARABIC5Count<>函数参数对话框Counta<>函数统计区域中不为空的单元的数目,它不仅对包含数值的单元格进行计数,还对包含非空白值〔包括文本、日期和逻辑值的单元格进行计数。该函数的使用方法与Count<>函数使用方法一致。2．使用Countif<>函数统计总分大于400分的学生人数Countif<>函数是对指定区域中符合某一条件的单元格计数的统计函数。选中N4单元格,使用"公式"→"插入函数"按钮,找到Countif<>函数后打开参数对话框,在Range参数中输入要统计的区域围,总分区域为I3:I32,Criteria参数中输入条件">400”,注意在Excel中输入的标点符号均为英文格式,单击确定按钮完成计算,如REF_Ref373353751\h图9-6所示。图9-SEQ图9-\*ARABIC6Countif<>函数参数对话框3．使用Countblank<>函数统计缺考人次数Countblank<>函数用来计算指定区域中空白单元格的个数。选中N5单元格,使用"公式"→"插入函数"按钮,找到Countblank<>函数后打开参数对话框,在Range参数中输入要统计的区域围,课程成绩区域为D3:H32,单击确定按钮完成计算。9．3．3统计平均分、总分1．使用Average<>函数统计语文平均分在N6单元格中计算语文平均分。选中N6单元格,使用"公式"→"插入函数"按钮,找到Average<>函数后打开参数对话框,在Number1参数中输入要统计的区域围,语文成绩区域为D3:D32,如REF_Ref373353767\h图9-7所示,单击确定求得平均成绩。图9-SEQ图9-\*ARABIC7Average<>函数参数对话框2．使用Averageif<>函数统计男生C语言程序设计平均分在N7单元格中计算班级所有男生C语言程序设计的平均分。选中N7单元格,使用"公式"→"插入函数"按钮,找到Averageif<>函数后打开参数对话框,在Range参数中输入要统计平均值的条件所在的区域,条件区域为性别男即B3:B32,Criteria参数中输入条件,我们可以直接输入"男",也可以从性别列中引用容为男的单元格如B3,Average_range参数中输入要计算平均值的实际单元格区域,输入C程课程考分G3:G32,如REF_Ref373353784\h图9-8所示,单击确定求得男生C程的平均成绩。图9-SEQ图9-\*ARABIC8Averageif<>函数参数对话框3．使用Sumif<>函数统计1401寝室学生总分Sumif<>函数是根据指定条件对若干个单元格、区域或引用进行求和。选中N8单元格,使用"公式"→"插入函数"按钮,找到sumif<>函数后打开参数对话框,在Range参数中输入要统计求和值的条件所在的区域,条件区域为寝室号1401即C3:C32,Criteria参数中输入条件,我们可以直接输入"1401”,也可以从寝室号列中引用容为1401的单元格如C3,Sum_range参数中输入要计算求和值的实际单元格区域,输入总分I3:I32,如REF_Ref373353800\h图9-9所示,单击确定求得1401寝室学生的总分。图9-SEQ图9-\*ARABIC9Sumif<>函数参数对话框9．3．4使用Rank.EQ<>统计班级学生排名在J列中统计每个学生的总分由高分到低分的排名,我们可以使用Rank.EQ<>函数,该函数功能为返回某个数字在数字列表中的排名。选择J3单元格,使用"公式"→"插入函数"按钮,找到Rank.EQ<>函数后打开参数对话框,在Number参数中输入要计算哪个数值的排名,在此我们输入I3；在Ref中输入要进行排名的区域,在此我们输入I3:I32；前两个参数合并起来的意思就是计算I3在I3:I32区域中的大小排名；Order参数为排序的方式,如果忽略不填或填0表示降序排列,否则就是升序排列。如REF_Ref373353827\h图9-10所示,单击确定按钮,这时在J3单元格中显示结果为14,表示I3单元格的数据在I3:I32区域中从高到低降序排列为第14名。图9-SEQ图9-\*ARABIC10Rank.EQ<>函数参数对话框对于J列其余单元格的排名,如果简单的使用填充柄进行填充,我们将会发现是错误的,因为函数参数有所改变。但是Ref参数I3:I32应该是不变的,因此需要对Ref参数I3:I32使用绝对地址,使其在用填充柄填充的时候不发生变化。选中已经编辑好的J3单元格,单击编辑栏上的"插入函数"按钮,直接弹出编辑好的Rank.EQ<>函数对话框,选中Ref参数中的I3:I32,按下功能键F4,直接给行列号添加上$绝对地址引用符号,如REF_Ref373353864\h图9-11所示,单击确定完成,之后我们就可以使用填充柄正确计算其余同学的总分排名。图9-SEQ图9-\*ARABIC11Rank.EQ<>函数参数绝对地址引用9．3．5数据库函数的使用Excel数据库函数共有12个,每个数据库函数的首字母都是D,我们也称为D函数。常用数据库函数有Dmin<>、Dmax<>、Daverage<>、Dsum<>、Dcount<>。这些函数去掉前面的D就和普通的常用函数一样,分别用来计算数据库清单在一定条件下的最小值、最大值、平均值、求和、计数。对于数据库函数,我们可以理解为多条件求值函数,如无条件计数函数为Count<>,单条件计数函数为Countif<>,而多条件计数函数为Dcount<>,因此在使用数据库函数前必须先设计好条件区域以供引用。数据库函数的参数均是一样的,都包含三个参数：Datebase：所求的数据清单或数据库单元格区域。Field：指定函数运算所使用的列,可输入加上双引号的字段名或直接引用该字段名,也可以输入代表列表中列位置的数字。Criteria：给定的条件区域。1．使用Dcount<>函数统计男生语文高于90分的学生人数要统计男生语文高于90分的学生人数,需要两个条件,分别是性别为男和语文成绩高于90,属于多条件计数,因此用数据库函数Dcount<>。先在P2:Q3设置条件区域如REF_Ref373353887\h图9-12所示,注意：数据清单中能复制的容尽量复制。在N14单元格中,使用"公式"→"插入函数"按钮,找到Dcount<>函数后打开参数对话框,在Datebase参数中输入数据清单围,即A2:J32,在此注意第二行必须选,作为数据清单的首行；Field中引用D2,表示要算计大学语文的个数；Criteria中输入P2:Q3,如REF_Ref373353903\h图9-13所示,单击确定后完成计算。图9-SEQ图9-\*ARABIC12条件区域图9-SEQ图9-\*ARABIC13Dcount<>函数参数对话框2．使用Dget<>函数统计语文高于90分的女生Dget<>函数的功能是提取符合条件的唯一记录,如果记录不唯一则返回#NUM!。条件区域设置如REF_Ref373353928\h图9-14所示,函数参数如REF_Ref373353943\h图9-15所示。图9-SEQ图9-\*ARABIC14条件区域图9-SEQ图9-\*ARABIC15Dget<>函数参数对话框3．使用Dmax<>函数统计女生中网络技术的最高分Dmax<>函数的功能是提取满足条件记录指定列的最大值,函数参数如REF_Ref373353966\h图9-16所示。图9-SEQ图9-\*ARABIC16Dmax<>函数参数对话框4．使用Daverage<>函数统计排名前10名中男生的平均总分Daverage<>函数的功能是求满足条件记录指定列的平均值,条件区域如REF_Ref373353980\h图9-17所示,函数参数如REF_Ref373353997\h图9-18所示。图9-SEQ图9-\*ARABIC17条件区域图9-SEQ图9-\*ARABIC18Daverage<>函数参数对话框5．使用Dproduct<>函数统计排名前5名中的1403寝室学生物理成绩的乘积Dproduct<>函数的功能是将数据库中符合条件记录的特定字段中的值相乘。条件区域如REF_Ref373354025\h图9-19所示,函数参数如REF_Ref373354048\h图9-20所示。图9-SEQ图9-\*ARABIC19条件区域图9-SEQ图9-\*ARABIC20Dproduct<>函数参数对话框9.4

案例总结本项目主要学习了Excel中数组公式的使用,基本函数中计数函数Count<>、Counta<>、Countif<>、Countblank<>用以统计个数,求和函数Sum<>、Sumif<>,平均值函数Average<>,排序函数Rank.EQ<>并进一步强化了绝对地址的应用。项目中也介绍了数据库函数,用于对数据清单中满足条件的记录中指定列的数据进行分析,数据库函数具有相同的参数,求解的结果是由函数自身决定的。9.5

课后练习打开采购情况表.xlsx,完成如下设置,效果如REF_Ref373354067\h图9-21所示。1．使用数组公式,计算采购情况表中每种产品的采购总额,将结果填到"采购总额"列中,采购总额的计算方法为：采购