计算机基础实验7参考资料之公式函数.docx

计算机基础实验7参考资料之公式函数.docx

《计算机基础实验7参考资料之公式函数.docx》由会员分享，可在线阅读，更多相关《计算机基础实验7参考资料之公式函数.docx（14页珍藏版）》请在冰豆网上搜索。

计算机基础实验7参考资料之公式函数

实验七Excel2003公式和函数

一、实验目的

熟练掌握Excel2003的公式与函数的使用。

二、实验内容与预备知识

1．公式

（1）公式作用：

公式以“=”开头，由运算数和运算符组成，作用是对数据进行运算。

（2）运算符：

包括数学运算符、文本运算符、比较运算符三类，应掌握各种运算符的含义。

（3）运算数：

包括常量、单元格引用和函数。

（4）单元格引用：

包括相对引用、绝对引用和混合引用三种，掌握三种之间的区别。

（5）公式输入方法：

在单元格中直接输入，如“=A3+B3”。

2．函数

（1）函数基本形式：

函数名（参数1、参数2，……）

◆函数名表示函数的功能。

◆参数是函数运算的对象，可以是否常量、单元格、数据区域、公式或函数，参数的形式和多少因不同函数而异。

◆如函数“SUM（B2:

E2）”是求B2：

E2区域内数据之和，与公式“B2+C2+D2+E2”作用相同。

（2）函数输入方法

方法一：

直接输入法。

如在F2单元格中输入“=SUM（B2：

E2）”，功能是B2：

E2区域内数据之和，结果放在F2单元格中。

方法二：

粘贴函数法。

操作步骤如下：

步骤1：

单击要输入计算结果的单元格。

步骤2：

单击常用工具栏的“自动求和”按钮右侧的下三角按钮，弹出“常用函数”菜单，如果选择“其它函数”（或选择菜单中“插入|函数”命令，或编辑栏中“插入函数”按钮），将显示“插入函数”对话框，如图9-1：

图9-1插入函数对话框

步骤3：

从函数分类列表中选择要插入函数的类型；从函数名列表中选择要插入的函数名。

进入该函数的对话框，如插入countif函数数的对话框，如图9-2所示，它显示出了所选函数的名称、函数的功能说明、参数和参数的描述。

图9-2函数设置对话框

步骤4：

根据参数说明输入参数，若参数为单元格引用，可直接输入，也可单击参数框右侧的暂时隐藏按钮，再选择所需的单元格，最后再次单击该按钮，恢复函数对话框。

步骤5：

单击“完成”按钮。

3．常用函数介绍

（1）求和函数sum

使用格式：

SUM（number1,number2,...）

功能：

计算所有参数的数值之和。

参数说明：

number1，number2，……表示需要求和的数值或引用单元格（区域），参数不超过30个。

（2）条件求和函数sumif

使用格式：

SUMIF（Range，Criteria，Sum_range）

功能：

求出区域内满足某一特定条件的所有数字之和。

参数说明：

Range表示用于条件判断的单元格区域；Criteria表示单元格将被相加求和的条件，形式可以为数字、表达式或文本；Sum_range为需要求和的实际单元格

（3）求平均值函数average

功能：

求出所有参数的算术平均值。

使用格式：

AVERAGE（number1，number2，……）

参数说明：

number1，number2，……表示需要求平均值的数值或引用单元格（区域），参数不超过30个。

（4）计数函数count

使用格式：

COUNT（value1,value2,...））

主要功能：

统计所有参数中数字参数的个数。

参数说明：

Value1,value2,...   为包含或引用各种类型数据的参数，但只有数字类型的数据才被计算，参数不超过30个。

（5）条件计数函数countif

使用格式：

COUNTIF（Range，Criteria）

主要功能：

统计某个单元格区域中符合指定条件的单元格数目。

参数说明：

Range代表要统计的单元格区域；Criteria表示指定的条件表达式。

（6）条件函数if

使用格式：

IF（Logical_test，Value_if_true，Value_if_false）

功能：

判断一个条件是否满足，如果满足返回一个值，如果不满足则返回另一个值。

参数说明：

Logical_test为任何一个可判断为TRUE或FALSE的数值或表达式。

当其为TRUE时，执行Value_if_true，否则执行Value_if_false。

（7）最大值函数max

使用格式：

MAX（number1,number2,...）

功能：

返回一组值中的最大值

参数说明：

Number1,number2,...   是待找出最大值的一组参数，参数不超过30个。

（8）最小值函数min

使用格式：

MIN（number1,number2,...）

功能：

返回一组值中的最小值

参数说明：

Number1,number2,...   是待找出最小值的一组参数，参数不超过30个。

3．多工作表数据的引用

引用其他工作表的单元格方法：

工作表名称+“!

”+单元格引用，如要在Sheet1工作表中引用Sheet2工作表中的B1单元格的数据，可以表示为：

“Sheet2!

B1”。

三、实验过程

给定的的“成绩表”工作簿中有“成绩统计”工作表（如图9-3）和“计算机基础”工作表（如图9-4），请完成以下实验内容。

（成绩表.xls文件可从作业系统下载）

图9-3成绩统计工作表

图9-4计算机基础成绩工作表

打开“成绩表”工作簿，进行如下操作：

1．根据“计算机基础成绩”工作表中的数据，计算“成绩统计”工作表的“计算机基础”成绩（计算公式：

计算机基础成绩=平时成绩×比例+实验成绩×比例+考试成绩×比例）。

1在“成绩统计”工作表的E2单元格中输入公式“=计算机基础成绩!

C3*计算机基础成绩!

$C$1+计算机基础成绩!

D3*计算机基础成绩!

$D$1+计算机基础成绩!

E3*计算机基础成绩!

$E$1”，单击按钮“√”或按回车键。

2

选中E2单元格，鼠标指向右下角的填充柄，按下左键拖动至E12单元格，计算出每个人的计算机基础成绩。

操作结果如图9-5:

图9-5计算机基础成绩计算结果

2．利用求和函数sum求每位同学的总分。

在“成绩统计”工作表的F2单元格中输入公式“=SUM（C2:

E2）”,单击按钮“√”或按回车键。

1选中F2单元格，鼠标指向右下角的填充柄，按下左键拖动至F12单元格，计算出每个人的总分。

操作结果如图9-6。

图9-6总分计算结果

3．利用平均值函数average求出每位同学的平均分

1在“成绩统计”工作表的G2单元格中输入公式“=AVERAGE（C2:

E2）”,单击按钮“√”或按回车键。

2

选中G2单元格，鼠标指向右下角的填充柄，按下左键拖动至G12单元格，计算出每个人的平均分。

操作结果如图9-7：

图9-7平均分计算结果

4．利用最大值函数max求出每门课程的最高分及总分、平均分的最高分。

1在“成绩统计”工作表的C13单元格中输入公式“=MAX（C2:

C12）”,单击按钮“√”或按回车键。

2选中C13单元格，鼠标指向右下角的填充柄，按下左键拖动至G13单元格，求出每门课程的最高分及总分、平均分的最高分。

操作结果如图9-8：

图9-8最高分计算结果

5．利用最小值函数min求出每门课程的最低分及总分、平均分的最低分。

1

在“成绩统计”工作表的C14单元格中输入公式“=MIN（C2:

C12）”,单击按钮“√”或按回车键。

2选中C14单元格，鼠标指向右下角的填充柄，按下左键拖动至G14单元格，求出每门课程的最低分及总分、平均分的最低分。

操作结果如图9-9：

图9-9最低分计算结果

6．利用平均值函数average求出每门课程的平均分。

1在“成绩统计”工作表的C15单元格中输入公式“=AVERAGE（C2:

C12）”,单击按钮“√”或按回车键。

2

选中C15单元格，鼠标指向右下角的填充柄，按下左键拖动至E15单元格，求出每门课程的平均分。

操作结果如图9-10：

图9-10各科平均分计算结果

7．利用条件计数函数countif求出每门课程90以上的人数。

1在“成绩统计”工作表的C16单元格中输入公式“=countif（C2:

C12,">=90"）”,单击按钮“√”或按回车键。

2选中C16单元格，鼠标指向右下角的填充柄，按下左键拖动至E16单元格，求出每门课程90以上的人数。

操作结果如图9-11。

图9-1190分以上的人数统计结果

8．利用count、countif函数计算每门课程的及格率。

1在“成绩统计”工作表的C17单元格中输入公式“=COUNTIF（C2:

C12,">=60"）/COUNT（C2:

C12）”,单击按钮“√”或按回车键。

2选中C17单元格，鼠标指向右下角的填充柄，按下左键拖动至E17单元格，求出每门课程的及格率。

操作结果如图9-12：

图9-12各科及格率统计结果

9.按照每个学生的平均成绩给出”总评”（平均成绩≥85：

优秀；85>平均成绩≥70：

良好；70>平均成绩≥60：

及格；平均成绩<60：

不及格）。

1在“成绩统计”工作表的H2单元格中输入公式“=IF（G2>=85,"优秀",IF（G2>=70,"良好",IF（G2>=60,"及格","不及格"）））”,单击按钮“√”或按回车键。

2

选中H2单元格，鼠标指向右下角的填充柄，按下左键拖动至H12单元格，计算出每个人的总评等级。

操作结果如图9-13：

图9-13总评统计结果

四、巩固练习

给定的的“工资表”工作簿中有“工资统计”工作表（如图9-14）和“奖金统计”工作表（如图9-15）（工资表.xls文件可从作业系统下载），请完成以下操作：

图9-14工资统计工作表

图9-15奖金统计工作表

1．利用if函数计算每个人的职务工资，计算方法：

若职务为“普通员工”，职务工资为500元；若职务为“经理”，职务工资为800元；若职务为“总经理”，职务工资为1200元。

2．根据“奖金统计”工作表中的数据，计算“工资统计”工作表中每个人的奖金（计算公式：

奖金=基本奖金+加班奖金-罚金）。

3．利用求和函数计算应发工资（应发工资=基本工资+职务工资+奖金）

4．利用if函数计算税款，计算方法是应发工资大于或等于免税工资的按给定的税率征收税收，应发工资低于免税工资的不征税（说明：

计算免税工资和税率不能使用具体数值，而要引用相应的单元格）。

5．计算实发工资（实发工资=应发工资-税款）

6．统计基本工资、职务工资、奖金、税款、应发工资、实发工资各项的最高值

7．统计基本工资、职务工资、奖金、税款、应发工资、实发工资各项的最小值

8．统计基本工资、职务工资、奖金、税款、应发工资、实发工资各项的平均值

9．统计所有税款低于150元的人交税额占总交税额的比例。

10．统计实发工资大于3000的人数