高级筛选
- 应用场景:今天导员发了一个excel表格,内容是整个学院的学生名单,而且是乱序的,没有专业班级的信息,现在老师要求我们去完善表格中的邮箱这一项。然而,在那么多的数据中去找到自己的名字还是比较费时费眼睛的,所以我想要从中筛选出我们班级的同学信息,以便班内同学找到自己的名字。
- 方法:首先我去找了一份我们班级的学生名单,然后选中整个学院的学生名单,点击工具栏中的筛选下的高级筛选,就会出现以下的界面,根据图片所示进行操作,再点击确定后就能筛选出来了。
VLOOKUP(查找值,数据表,列序数,[匹配条件])
-
应用场景:今天导员发了一个excel表格,内容是整个学院的学生名单,而且是乱序的,没有专业班级的信息,现在老师要求我们去完善表格中的邮箱这一项。正好,我们班之前就已经搜集了学生的邮箱信息,我现在要把我们班的邮箱号码按照姓名匹配填到老师发的数据表中。
-
方法:
- 先使用VLOOKUP(查找值,数据表,列序数,[匹配条件])函数:4个参数的意义就是---》去指定的“数据表”中找与“查找值”匹配的这一行数据,然后把数据表中“列序数”对应列的这项数据填写到空格中,”匹配条件“有FALSE精确匹配和TRUE近似匹配。通过上述操作,我们已经填好了一个空格,现在只需要鼠标选中这一填好的空格,把鼠标移到这一空格的右下角,待鼠标变成十字架时往下拉动就能把所有的都填好了。
- 为什么要加
$
符号?从下图中可以看到,“小万”对应的邮箱格子中的查找值是B11,相较于上图的“小中”的查找值B4是发生了变化的。所以其实我们可以想到,“鼠标十字架下拉”的操作实际上就是把函数中的参数也自动“下拉”。但是,事实上我们这里的数据表是不需要再自动“下拉”的,否则就出错了(如下面的第二张图,图中的数据表也由G3:H9变成了G10:H16,而我们的数据表始终是在G3:H9的,所以这样不行)。$
符号就是用来固定的,如第一张图中在“3”和“9”前加了$
,那么效果就是使这个“3”和“9”不会随着“下拉”自动改变,此处是为了保证数据表的正确。
IFNA(正常要填的值,若正常值为空要填的值)
- 应用场景:在上面这个使用VLOOKUP()函数填充邮箱的使用场景中,我们最
后虽然正确地填好了我们班级学生的邮箱,但却有一个问题---》就是在查找匹配过程中,那些别的班的同学由于匹配不到,最后在这些同学的邮箱空格处填上了#N/A(表示空值null)。这样由什么不好呢?那就是别的班如果在我填充晚的基础上也这样去填充他们班的邮箱,那岂不是我们班原本填了的都会变成#N/A,因为他们班的名单里面肯定没有我们班的人啊。所以,我们想要进一步优化:让能匹配的匹配,不能匹配的保留原值。
2. 方法:使用IFNA(正常要填的值,若正常值为空要填的值)函数,该函数含义就是---》正常的话都是填前面正常要填的值,但如果这个值是空,那就填后面的值。结合VLOOKUP()函数使用如下图所示,下图中,我把原来数据中Emile的信息复制粘贴了一份在旁边,用作IFNA()函数的第二个参数值。这样,会正常先去计算VLOOKUP(B4,G$3:H$9,2,FALSE)
,如果计算出的不是空,就填计算得到的结果,如果因为匹配不到而计算结果为空,那么就填复制粘贴在旁边的这一列值(E4随着“下拉”也会变成E5、E6,即对应的原值)。
注意,可能有人要问了,为什么IFNA()函数的第二个参数不直接填C4、C5....这一列,而还要那么麻烦地去复制一列呢?问就是我试过了,直接弄不行,excel报错。