计算机基础上机实验精华版九

实验九 Excel公式和函数的使用
一、实验目的：
1．掌握Excel公式的应用。

2．掌握Exc1e常用函数的应用。

3．掌握三大地址的区别与应用。

二、实验设备和软件：
1、硬件设备：Pentium Ⅳ以上微型计算机。

2、软件设备：Windows XP、Excel 2002或以上版本。

三、实验原理和预备知识：
公式是利用单元格的引用地址对存放在其中的数据进行计算的等式。

一个公式由三部分组成：等号、运算数据和运算符。

运算数据可以是常量数据（包括数值常量和字符常量）、单元格地址引用、区域引用和Excel提供的函数。

运算符可以是算术运算符（+、-、*、/、^、%）、比较运算符（>、>=、<、<=、=、<>）、文本运算符（&）和引用运算符（:）。

创建公式的步骤如下：
1、选定输入公式的单元格；
2、输入等号“=”；
3、输入公式具体内容（可以直接在单元格中输入，也可以在编辑栏中输入）；
4、按下Enter键，完成公式创建。

函数是Excel自带的一些已经定义好的公式。

函数处理数据的方式与直接创建的公式处理数据的方式是相同的。

使用函数既可以减少输入的工作量，也可以减小输入时出错的概率。

所有函数都是由函数名和位于其后的一系列用括号括起来的参数组成的，即：函数名(参数1，参数2，……)。

函数名代表了该函数具有的功能。

不同类型的函数要求的参数类型不尽相同，可以是数字、文本、逻辑值、单元格地址、区域地址和另一个函数(函数嵌套)，给定的参数必须能产生有效数值。

函数可以有两种方法输入，一种是直接输入法，如果能记住函数名的拼写以及正确的参数类型，可以直接通过键盘在编辑栏或单元格中输入函数，这是一种效率较高的输入方式；另一种是粘贴函数法，通过Excel提供的函数输入指南，逐步完成函数的输入。

在Excel公式中，单元格是作为变量参与运算的，运算中指定的是存放数据的单元格地址，而不是数据本身，公式运算的结果总是采用单元格中的当前数据。

如果改变单元格中的数据内容，则计算结果也会发生变化。

引用单元格的公式的复制操作，可节省大量重复公式的输入。

在这类公式复制过程中，可根据不同的位置或情况变换不同的单元格地址引用方式。

单元格地址引用分为相对引用、绝对引用和混合引用。

1、相对引用相对引用是指单元格引用会随公式所在单元格的位置变更（公式复制时）而改变。

相对引用地址由列号行号表示。

2、绝对引用绝对引用单元格地址将不随公式位置的变化而改变。

绝对引用地址的表示方法是在相对地址的列号和行号前分别加上一个美元符号“$”。

3、混合引用混合引用是指只在行号或列号前加“$”符号。

公式复制后，只有其中的相对地址部分发生改变而绝对地址部分不变。

四、实验内容和步骤：
1、模仿性实验
实验任务1 算术公式和逻辑函数的使用
打开“职工工资表.XLS”，在“工资表”工作表中，进行以下操作运算：
⑴、在“补贴”所在列用公式计算各职工的补贴额。

补贴的计算方法如下：若基本工资大于2000元，则
补贴基本工资的12%；若基本工资大于1500元、小于等于2000元，则补贴基本工资的8%；若基本工资不多于1500元，则一律补贴95元。

⑵、根据职工的出生日期，在“年龄”列用公式计算出各职工的年龄(不考虑周岁)。

⑶、用公式计算各职工的应发工资：应发工资=基本工资+补贴。

⑷、按以下要求用公式计算各职工应纳所得税额：若应发工资大于2000元，则应发工资减去1500元后
的余额纳10%的税；若应发工资大于1500元但小于等于2000元，则应发工资减去1500元后的余额纳8%的税；其它情况不纳税。

⑸、计算各职工的实发工资额：实发工资=应发工资-所得税。

实验步骤
（1）、计算补贴。

在H3单元格中输入公式：=IF(G3>2000,G3*12%,IF(G3>1500,G3*8%,95))。

拖动H3单元格句柄到H22单元格。

结果如图9-1所示。

图9-1 计算补贴
（2）、计算年龄。

在E3单元格中输入公式：=YEAR(TODAY())-YEAR(F3)，拖动E3单元格句柄到E22单元
格，选择E3：E22区域，单击“格式”→“单元格”→“数字”，在“分类”中选择“常规”。

结果如图9-2所示。

（3）、计算应发工资。

在I3单元格中输入公式：=G3+H3，拖动I3单元格句柄到I22单元格。

结果如图
9-3所示。

（4）、计算所得税。

在J3单元格中输入公式：=IF(I3>2000,(I3-1500)*10%,IF(I3>1500,(I3-1500)*8%,0))。

拖动J3单元格句柄到J22单元格。

结果如图9-4所示。

图9-2 计算年龄
图9-3 计算应发工资
（5）、计算实发工资。

在K3单元格中输入公式：=I3-J3，拖动K3单元格句柄到K22单元格。

结果如图
9-5所示。

图9-4 计算所得税
图9-5 计算实发工资
实验结果
结果如图9-1、图9-2、图9-3、图9-4、图9-5所示。

实验任务2 常用函数的使用
将“Sheet4”工作表改名为“公式与函数”，然后完成以下操作：
（1）、用函数统计出“工资表”工作表中职工的平均年龄、所得税总额和最高实发工资。

（2）、在“公式与函数”工作表的A1单元格中输入“46.057”作为变量x 的值，然后在A2:G2区域实现
以下函数运算：
x 、将x 值舍人到个位、求x 的小数部分、产生一个100至300间的随机整数、求1222211除以111的余数、1
)12/cos()sin(+ππx （3）、在“公式与函数”工作表的A4:J4区域中用日期公式填入以下规律变化的日期数据：“Jan 01,1999、
Jan 02,1999、Jan 03,1999、……”。

（4）、在“公式与函数”工作表的A6:C13区域中输入以下电话号码升位表，如图9-6所示。

然后按以下
规律用公式完成电话号码由7位到8位的升位计算：原电话号码第一位为8时则在8之后插入1，否则在原号码首位直接加8。

实验步骤
（1）、将鼠标指针指向“sheet4”工作表，单击鼠标右键，在快捷菜单中选择“重命名”，输入工作表名
称“公式与函数”。

（2）、统计函数的使用。

在“工资表”工作表中，分别在B24、D24、G24单元格中输入下列公式：
=AVERAGE(E3:E22)、=SUM(J3:J22)、=MAX(K3:K22)。

（3）、数学函数的使用。

在“公式与函数”工作表中的A1单元格中输入“46.057”，然后分别在A2、B2、
C2、D2、E2、F2、G2单元格中依次输入下列公式：=SQRT(A1)、=ROUND(A1,0)、=A1-INT(A1)、=INT(RAND()*(300-100)+1)+100、=MOD(1222211,111)、=SQRT(SIN(A1*PI()))/(COS(PI()/12)+1)。

（4）、用函数计算日期序列。

先用日期函数公式按要求产生规律变化的序列：在“公式与函数”工作表
中的A4单元格中输入初始日期数据“1999/1/1”，在B4单元格中输入公式：=DATE(YEAR(A4),MONTH(A4),DAY(A4)+1)，拖动B4单元格的句柄到J4单元格。

然后通过自定义格式显示输出：选择区域A4：J4，单击“格式”→“单元格”→“数字”，在“分类”中选择“自定义”，在“类型”框中输入“mmm dd,yyyy ”，单击【确定】按钮。

（5）、电话号码升位。

在“公式与函数”工作表A6:C13区域中输入图9-6
所示的电话号码升位表，在图9-6 升位前电话号码
C7单元格中输入公式：=IF(LEFT(A7,1)="8","81"&RIGHT(A7,6),"8"&A7)，拖动C7单元格的句柄到C13单元格。

实验结果
结果如图9-7所示。

实验任务3 三大地址的使用
将“Sheet3”工作表改名为“三大地址”，在区域A7：G9生成如图9-8所示的数据表格，在B9单元格输入一个公式，并将其沿行方向复制，算出当月止之累计营业额，即B9的值为一月份营业额；C9的值为一月、二月营业额之和；依此类推。

实验步骤 （1）、工作表重命名。

将鼠标指针指向“sheet3”工作表，单击鼠标右键，在快捷菜单中选择“重命名”，
输入工作表名称“三大地址”。

（2）、混合地址计算。

在区域A7：G9生成如图所示的数据表格，在单元格B9 中输入公式：=SUM($B8:B8)，
拖动B9单元格句柄到G9单元格。

实验结果
结果如图9-9所示。

图9-7 常用函数
图9-8 累计前营业额
图9-9 累计后营业额
2、设计性实验
实验任务1 计算周岁
在“工资表(2)”工作表的区域E3：E22中，按周岁计算各职工的年龄。

实验结果
结果如图9-10所示。

实验任务2 混合地址计算
在“三大地址”工作表中，在区域A12：E19生成如图
9-11所示的数据表格，该表给出了存入银行的不同本金和不同存款年限的年利率，要求在C14单元格输入计算税后本息合计的公式，再通过复制公式，完成全部税后本息合计的计算，要求结果以两位小数显示，注意利息要扣20%的税。

实验结果
结果如图9-12所示。

实验任务3 计算九九乘法表
在“三大地址”工作表中的区域A21：J30中生成如图9-13所示的“九九乘法表”，要求先在B21：J21区域中输入一行数据1~9，在A22：A30区域中输入一列数据1~9，然后在B22单元格中输入一个公式，再将该公式沿行/列两个方向复制，从而得出九九乘法表。

图9-10 计算周岁
图9-11 本息计算前数据
图9-12 本息计算后结果
实验结果
结果如图9-13所示。

实验任务4 学生成绩表绩点的计算和奖学金等级的判定
(1)、打开“学生成绩表.XLS ”文件，在“课程1”和“课程2”工作表中分别计算“总评成绩”。

计算方
法：总评成绩=平时成绩*30%+期末成绩*70%，要求结果四舍五入到整数位，如总评成绩为76.5，则舍入后的实际总评成绩应为77。

(2)、在“课程1”和“课程2”工作表中分别计算各学生的课程“绩点”。

计算方法：成绩60分时绩点
为1，成绩每增加1分绩点增加0.1；成绩小于60分时绩点为0。

(3)、在“奖学金”工作表中计算各学生的“平均绩点”，并判断其应获得的“奖学金等级”。

奖学金等级
判断方法：“平均绩点”在4.0或以上者能获得一等奖学金，3.5或以上者能获得二等奖学金，3.0或以上者能获得三等奖学金。

要求“平均绩点” 四舍五入到小数点后1位。

提示：此工作表中“绩点1”和“绩点2”的数据可通过“粘贴值”或“粘贴链接”从“课程1”和“课程2”中获得。

实验结果
结果如图9-14、图9-15、图9-16所示
图9-13 九九乘法表
图9-14 计算机应用基础成绩表
图9-15 大学英语成绩表
图9-16 奖学金等级评定表
五、思考题：
1、函数运算用到的参数必需放在一对紧接函数名的括号中，但对于无参函数而言，则括号可以省略。

这句话对吗？
2、在Excel中，所有函数实现的功能都可以用非函数的方法来完成。

这句话对吗？
3、相对地址、绝对地址和混合地址的共性在哪里？主要区别又在哪里？。