Excel函数（进阶版）

Excel函数

如果你的平均分公式是这个样的：
平均分=（课程分数1课程学分1+课程分数2课程学分2+…+课程分数n*课程学分n）/ 总学分
在excel里是这样保存的：
成绩表示例

假设三个课程的学分分别为0.3，5，4
那加权平均分公式为：=(C4*0.3+D4*5+E4*4)/G4
那么存在两个问题：
（1）存在不及格课程的同学不能评选奖学金
（2）课程1是考查课，成绩需要由优良中及转化为90，80，70，60

先来解决（1）在不及格门数列加筛选，留下所有不及格门数为0的同学。

调出“替换”，将优替换为90，良替换为80，中替换为70，及格替换为60，不及格不用管啦，反正也没有资格
在该列（这里为C）后插入一列D，使用公式： =IF(C5 = "优",90,IF(C5 = "良",80,IF(C5 = "中",70,IF(C5 = "及格",60))))

都解决完之后，最终的加权平均分在I5输入：=(D5*0.3+E5*5+F5*4)/H5

都解决后可以将同一专业各个班符合条件的这些同学复制到新的表中（见sheet2），选中加权平均分这列，排序-降序-扩展选定区域
新的排好序的表

建议：将成绩第一的人标为红色填充，一等资格的人标为黄色填充，二等资格的人标为蓝色填充，三等资格的人标为绿色填充。这样可以最大限度地防止只有二等资格的同学加完德育分比排在有一等资格的同学前面，看颜色就知道能评几等了，可以根据颜色直接调整，二等资格就是加到综合成绩第一也没用。

 =left(A2,3) #取A2单格内容的前3位 =mid(C2,7,8) #从C2单格内容的第7位开始，取连续的8位 =left(E2,4) #取E2单格内容的前4位

Left、MID函数演示视图

如果你想对比两列数据是否相同，或者查询条件为X的Y信息，在无需改变原表格的情况下，可以使用VLOOKUP函数。
函数格式：
=vlookup(待查值X’,被查询数据范围,被查值Y在被查询数据范围中第几列,是否模糊查询)
其中X’为新表中被查信息，X为原表中待查信息，X=X’

注意：

被查询数据范围前加$表示固定引用整列，引用数据范围不随拖动的单格而该改变。
被查值Y必须在被查询数据范围中的待查值X所在的列后面。
新表中的待查值X’和原表中被查询数据范围中的待查值X的数据类型（单格格式）必须相同。如，学号在原表中存储为数字格式，在新表中存储为文本格式，则二者可能匹配失败！
原表中待查信息X需要唯一，否则查询结果为第一个匹配到的值。

为满足注意事项中第二条，调整原表格顺序为下图所示。
调整后的表格
例如，查找学号为的人的家庭住址。
则查询条件X和原表中的待查值X’均为学号，家庭住址为Y，设学号为存储在A2单格，写出的查询函数如下：

=VLOOKUP(A2,Sheet1!$C:$F,3,0)

整表的查询结果如下：
VLOOKUP函数用法

今天的文章 Excel函数（进阶版）分享到此就结束了，感谢您的阅读。