当前课程知识点:大学计算机基础(艺术类) > 第三章 办公自动化应用 > 3.3 Excel表格编辑基础 > 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
即可去掉小数
填充即可
最后再加框线
加表头“整数成绩”即可
其它两项面试成绩算法跟这个类似
我就不再演示了
最后记得保存
通过以上的操作演示
我们掌握了公式和函数的运用
在运用的过程中
最关键是要熟练记住这些函数的用法
仔细分析数据的结构
才能够举一反三
解决我们学习和工作中碰到的问题
好的
这节课到这里就结束了
再见
-1.1 计算思维概述
--1.1 计算思维
-1.2 了解计算机
-1.3 计算机中信息的表示及存储
-1.4 计算机中信息的表示及存储(2)
-1.5 了解计算机系统
-第一章测验题
-2.1 操作系统概述
-2.2 Windows 基本操作
-2.3 Windows 文件管理
-2.4 Windows 程序管理
-2.5 Windows 设备管理
-第二章测验题
-3.1 Word文本编辑基础
-3.2 Word高级应用——毕业论文排版
-3.3 Excel表格编辑基础
-3.4 Excel高级应用——数据分析与处理
-3.5 PowerPoint幻灯片设计基础
-3.6 毕业论文答辩演示文稿制作
-第三章测验题
-4.1 计算机网络概述
-4.2 认识Internet及应用
-4.3 防治计算机病毒
-第四章测验题
-5.1 云计算
--5.1 云计算
-5.2 人工智能
--5.2 人工智能
-5.3 大数据
-5.4 VR、AR、MR、CR
-5.5 3D打印
--5.5 3D打印
-5.6 “互联网+”
-5.7 区块链
--5.7 区块链
-5.8 物联网
--5.8 物联网
-第五章测验题
-6.1 信息检索概述
-6.2 信息检索系统
-6.3 艺术类信息检索
-第六章测验题
-7.1 多媒体技术概论
--7.1.1 多媒体技术1-习题
--7.1.2 多媒体技术2-习题
-7.2 多媒体计算机系统
--7.2 多媒体计算机系统-习题
-7.3 多媒体技术的发展与艺术类应用
--7.3 多媒体技术的发展与艺术类应用-习题
-8.1 计算机图形学概论
--8.1.1 计算机图形学1-习题
--8.1.2 计算机图形学2-习题
-8.2 计算机视觉
--8.2 计算机视觉-习题
-8.3 数字图像处理技术
--8.3 数字图像处理技术-习题
-9.1 计算机动画基础
--9.1 计算机动画基础-习题
-9.2 计算机视频基础
--9.2 计算机视频基础-习题
-10.1 计算机听觉
--10.1 计算机听觉-习题
-10.2 数字音频技术
--10.2 数字音频技术-习题