Excel函数（进阶版）

Excel函数

1.求加权平均分

如果你的平均分公式是这个样的：
平均分=（课程分数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的同学。

现在黄色的这些人就是有资格参评的啦
（2）有两个解决方案：

• 调出“替换”，将优替换为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），选中加权平均分这列，排序-降序-扩展选定区域

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

2.提取家庭详细住址中的省份/身份证号中的出生日期/学号中的入学年份

已知：详细地址的前3位为省份（包括“省”字，但黑龙江和内蒙古提取后不包括“省”字），其中身份证号的第7位开始连续8位为出生年月日，假设学号的前4位为入学年份。
使用left()函数，提取三者的公式分别为：

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

3.VLOOKUP多表关联查询函数

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

注意：

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

为满足注意事项中第二条，调整原表格顺序为下图所示。

例如，查找学号为2018111111的人的家庭住址。
则查询条件X和原表中的待查值X’均为学号2018111111，家庭住址为Y，设学号为2018111111存储在A2单元格，写出的查询函数如下：

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

整表的查询结果如下：