实验5EXCEL函数

实验5 EXCEL函数

一、实验目的与要求

1.掌握统计函数与数学函数及其应用

2.掌握逻辑函数及其应用

3.掌握数据库函数及其应用

4.日期函数及其应用

5.掌握查找和引用函数及其应用

二、实验内容与操作步骤

本实验共有6题，每题的数据已经输入在工作簿文件中。

[第1题]统计函数与数学函数应用（一）

XX年研究生入学考试成绩如图1所示（该表数据共有5974行）。

图 1

要求：

（1）统计参加考试的考生人数（总分不为0的记录个数）；缺考人数；总分在350分以上的人数；

操作步骤：

参加考试的考生人数：在单元格O3中输入公式“=COUNTIF(J2:J5974,"<>0")”

缺考人数：在单元格Q3中输入公式“=COUNTIF(J2:J5974,"=0")”

总分在350分以上的人数；在单元格O4中输入公式=COUNTIF(J2:J5974,">350")

结果如图2所示。

图

图 2

（2）将参加考试的考生记录复制到表“第2题”中，为[第2题]的练习做准备。

操作步骤：

利用EXCEL对数据的自动筛选功能，将符合条件的记录筛选出来，再进行复制、粘贴操作。

●选定字段列标题行A1:J1；

●单击数据→筛选→自动筛选，则在每个字段列标题行旁出现下拉箭头；

●单击“总分”字段的下拉箭头，选择“自定义”命令，则出现“自定义自动筛选方式”对话框（参见图10。26），在对话框左侧“总分”框下选择“不等于”，右侧数值框中输入“0”，确定。

●选定单元格引用A列至K列（在列标题上拖动），执行“复制”命令；

●选定工作表标签“第2题”的A1单元格，执行“粘贴命令”。

[第2题] 统计函数与数学函数应用（二）

1

在工作表“第2题”中，按如下要求统计：

（1）统计表中各项成绩及总分的最高分、最低分、平均分、标准差、众度、中位数；

操作步骤：

外语分最高成绩：在单元格M4中输入公式“=MAX(D2:D5242)”；

外语分最低成绩：在单元格M5中输入公式“=MIN(D2:D5242)”；

外语分平均成绩：在单元格M5中输入公式“=AVERAGE(D2:D5242)”；

外语分标准差：在单元格M5中输入公式“=STDEV(D2:D5242)”；

外语分众度：在单元格M5中输入公式“=MODE(D2:D5242)”；

外语分中位数：在单元格M5中输入公式“=MEDIAN(D2:D5242)”。

其它成绩对应项目的统计方法类似，不再重复。统计结果如图3所示

图 3

（2）频度统计：分区间统计总分出现的频率及各区间人数的百分比。区间为：400分以上；350~400；300~350；260~300；260以下

操作步骤：

●输入频率计算分段点：在M16:M20单元格中输入259,299,349,399

●选定N16:N20，输入公式：“=FREQUENCY(J2:J5241,M16:M19)”

●按CTRL+SHIFT+ENTER键即得出运算结果。

将结果填入工作表的M12:Q12区域。

各区间人数的百分比：

选定M14，输入公式：“=M13/SUM($M$13:$Q$13)”，将公式复制到N14:Q14即可。运算结果如图4所示。

图 4

（3）将（2）统计结果复制到其他位置，分别使用取整函数“INT()”，将结果保留整数；使用四舍五入函数“ROUND()”将结果保留二位小数（自己练习）。

[第3题] 逻辑函数

[练习准备] 复制工作表“第2题”中总分排在前50名的“ksbh”、“外语分”、“政治分”、“业务1分”、“业务2分”及“总分”字段（见图5）的内容到表“第3题”。

操作步骤：

2

3

利用EXCEL 对数据的自动筛选功能，将符合条件的记录筛选出来，再进行复制、粘贴操作。 ● 选定字段列标题行A1:J1；

●

● 单击“总分”字段的下拉箭头，选择“前10个”命令，则出现“自动筛选前10个”对话框，将对话框中参数设置为显示“最大”、“50”、“项”

，确定。 ●

单击“A ”列标题，按住“CTRL ”键同时单击列标题“D ”、“E ”、“G ”、“I ”、“J ”，执行“复

制”命令；

选定工作表标签“第3题”的A1单元格，执行“粘贴命令”。

图 5

（1）按“总分”自动评出等级

等级标准为：“优秀”：总分400分以上；“良好”：总分350~400；“中等”:总分300~350；

“及格”：总分260~300；“不及格”：总分在260以下

操作步骤：

在单元格G2中输入公式“=IF(F2>=400,"优秀",IF(F2>=350,"良好",IF(F2>=300,"中等",IF(F2>=260,"及格","不及格"))))”，并将公式复制到区域从G3至本列记录结尾处。

（2）输入公式，以便判断该记录“基础业务均优”、“业务优”、“基础优或业务优并且总分高于400”字段的值为“TRUE ”或者为“FALSE ”。

判断标准：

基础优：政治+外语在150分以上者 业务优：业务1+业务2在250分以上

操作步骤：

业务优：在单元格I2中输入公式“=D2+E2>=250”，将公式复制到从I3至本列记录结尾处。 基础业务均优：在单元格H2中输入公式“=AND(B2+C2>=150,I2)”，将公式复制到从H3至本列

记录结尾处。

基础优或业务优并且总分高于400：在单元格J2中输入公式

“=AND(OR(B2+C2>=150,I2),F2>=400)”，将公式复制到从J3至本列记录结尾处。

计算结果如图6所示。

图 6

注意：在操作完毕，要将工作表“第1题”和“第2题”的数据筛选去掉，以使计算结果正常显示出来，同时保证“第4题”能够正常进行。方法是：执行数据→筛选，将“自动筛选”命令左边的勾去掉。

[第4题] 数据库函数

使用数据库函数在工作表“第2题”中按下列要求统计：