3.3.2 公式与函数在线视频

各位同学大家好

上一节课

我们录入了云南艺术学院艺术管理专业

招生考试的成绩数据

并且对数据样式进行了调整及美化

今天我们继续来完善这个成绩数据表

这里会涉及到公式和函数的运用

其中包括以下几个函数

在这些函数中

前8个是有参函数

函数名后面的括号里通常要包括一个或多个参数

而后两个是无参函数

就是括号里面没有参数

下面请看操作演示

这是上次编好的原始成绩

我们现在要在此基础上产生另外一些新的数据

这原始数据我们最好不要动它

在另一张工作表上来操作

所以我在同一个工作簿中复制此工作表

在工作表标签上右键

移动或复制

将选定工作表移至工作簿1

在sheet 2之前

建立副本勾选上

确定

将其重命名为公式与函数

当然

我们也可以在不同工作簿中复制工作表

比如这里有一个招生考试成绩统计表

我打开

选“面试成绩”工作表

右键

移动或复制

将选定工作表移至工作簿1

在sheet 2之前

建立副本

确定

工作簿1上就多了一张面试成绩的工作表

现在关闭“招生考试成绩统计表”

好的

现在我们在“公式与函数”工作表上来操作

在目前的原始数据上

我们要增加这样几列数据

年龄、综合成绩、排名、等级

我们逐一来增加

首先我们在表格的最后增加综合成绩列

我们可以复制前面的某一列

粘贴

再删除里面的数据

输入新的数据

这样格式可以保持一致

比较方便

或者

我撤销

我直接在这个空白列上输入数据

输入之前

选中某一列

格式刷

刷空白列

也可以让格式保持一致

选择标题

合并居中

在这里输入数据“综合成绩”

下面的单元格存放数值

综合成绩就是前面4个数值相加

我们可以自己编写公式

以=开头

引用单元格地址

E4+F4+G4+H4

确定输入

或敲回车

即可得到第一个学生的综合成

往下填充即可算出其它的成绩

我们看

每往下填充一个数据

所引用单元格地址都在发生变化

这样的地址叫做“相对地址”

或者我们直接使用sum求和函数

我删除刚才的数据

选中I4单元格

fx

sum函数

这是函数参数框

我们直接拖选E4：H4

这里冒号是引用符号

表示连续

如果是逗号

则表示不连续

其它单元格自动填充

这样我们的综合成绩就算出来了

接下来我们增加年龄列

我们把它增加到民族的前面

选中D列列标

右键

插入列即可

选中表头

合并居中

输入“年龄”

下面是数值区域

那年龄要如何计算呢

我们只需要知道出生的年份

就可以用函数计算出年龄来

这里我们要用到求年份的函数year（）

参数里添加日期即可

比如

我要得到第一个人出生的年份

就在这里输入=year（c4）

敲回车即可得到出生年份为2002

那么今年是哪一年呢

我们可以用now函数和today函数

这是两个无参函数

比如

我输入= now( )即可得到现在的日期和时间

我输入= today( )即可得到现在的日期

所以我就可以在第一个单元格内输入

=year（now( )）-year（C4）

即可求出现在的年龄

这里显示的不对是因为数值类型不对

右键

设置单元格格式

数值

0位小数

我们再看一下

这是函数的嵌套使用

这里now函数也可以替换为today函数

其它数值同样自动填充即可

接着我们来计算排名

同样我们先在最后面添加排名一列数据

我输入排名

合并居中

用格式刷刷一下格式

两种格式

分别刷两次

计算每个同学的综合成绩排名

我们用排名函数rank

选中k4单元格

fx

找到全部里面rank函数

下面有对函数功能的解释

返回某数字

在一列数字中相对于其它数值的大小排名

确定

这里它有三个参数

第一个参数number指的是我要对哪个数据进行排名

我对第一个同学的综合成绩进行排名

所以我选择j4单元格

第二个参数ref

是指我要排名的范围

我选择j4：j32

就是在所有学生综合成绩里进行排名

第三个参数

order

表示排序

我们可以空着

好确定

此时我们看到第一位同学的综合成绩排名为8

其它同学的排名我们用填充柄往下填充

填充完以后

我们观察一下排名对不对

我们看一共有29个学生数据

但是这里却出现了1234

4个第一名

而且这四个第一名的综合成绩也是不一样的

那显然这个排名是不对的

出错了

我们要找出出错的原因是什么

我们点开第二个数据来观察

我们就发现第一个参数发生了变化

从j4变为j5这是对的

但是第二个参数

它也在变

排名范围从j4：j32变成了j5：j33

而j33单元格是没有数据的

下面第三个数同样

排名范围一直在变化

而所有学生成绩的排名对比范围应该是一个固定不变的范围

这种会变化的单元格地址叫做相对地址

我们需要用到绝对地址

绝对地址就是指在填充或复制粘贴时固定不变的地址

我们在行号和列标前面加上一个美元符号来表示

此时我的第一个参数是相对地址

第二个参数是绝对地址

确认输入

我再重新填充一下

这样无论我填充到哪个单元格

它的第二个参数永远是j4：j32

这样的排名就对了

总成绩最高的272的为第一名

总成绩260的第二名等等

接下来我们来判定等级

同样我们先在最后面添加“等级”一列数据

输入等级

我们用if函数把成绩在270分及以上的判定为“优秀”

成绩在180分以下的判定为“不合格”

介于其中的就是“合格”

If函数有三个参数

第一个参数为条件判断J4是否大于等于270

如果满足条件则第二个参数"优秀"

否则第三个参数不满足条件

那就还需要再判断一次是否大于等于180

如果是，"合格"

否则，"不合格"

这里嵌套了两个if函数

当然如果等级里需要有优良中差多个等级

那就需要更多个if函数嵌套了

最后我们来看表格里的面试成绩

面试成绩由三项构成

自我介绍、命题口述和特长展示

而这三项考试时考官分别以100分为满分来打分

最后再折成20分、90分、40分

这里显示的成绩是已经折算之后的数据了

那么这个打分和折算的过程到底是怎么样的呢

我们经过艺考的同学都知道

面试时不止是一位考官

一般是5-10个

每个考官给你打个成绩

最后要么是直接取平均成绩

要么是去掉一个最高分

去掉一个最低分

然后再取平均成绩

现在我们来看这个面试成绩的表格

这是自我介绍的成绩

按100分的满分来打

最后再折成20分

这里有7个评委打的成绩

我们来求最终成绩

假如我们直接取平均值

那就简单了

直接fx

常用里面average函数

选取参数E3：K3

确定

成绩就出来了

其它的往下填充即可

而如果我们要去掉一个最高分

去掉一个最低分后再取平均成绩

那就复杂一些

我们动手编辑函数

以等号开头

我们要先求七个评委的总分

再减去最高分和最低分

最后再求平均分

求总分

我用sum函数

输入函数

前括号

参数E3：k3

后括号

减去最高分

我们用最大值函数Max

E3：k3

后括号

减去最低分

我们用最小值函数

Min

E3：k3

后括号

注意我们一个函数匹配一对括号

一定不能少

所有数据加个括号

斜杠表示除号

最后这样的数据再除以5个评委除以5

回车确认输入

即可得到最终的成绩

其它的数据往下填充

最后折算成满分为20分

等于L3×20

再除以100

回车即可得到最终的成绩

同样其它的往下填充即可

这里有小数点

如果我们要去掉

可以用round函数

fx

round

两个参数

第一个参数是要进行四舍五入的数

第二个参数是要保留几位小数

我输入0

即可去掉小数

填充即可

最后再加框线

加表头“整数成绩”即可

其它两项面试成绩算法跟这个类似

我就不再演示了

最后记得保存

通过以上的操作演示

我们掌握了公式和函数的运用

在运用的过程中

最关键是要熟练记住这些函数的用法

仔细分析数据的结构

才能够举一反三

解决我们学习和工作中碰到的问题

好的

这节课到这里就结束了

再见