VLOOKUP函数与IF函数的配合实现在教务管理中的多条件查找

VLOOKUP函数与IF函数的配合
实现在教务管理中的多条件查找
关键词：VLOOKUP函数与IF函数教务管理多条件查找
一、问题的提出
一个年级，经过二年的学习后，学生成绩因为多方面的原因会有很大的变化，班级与班级之间成绩会出现不平衡。

为对师生公平，便于管理，学校对九年级按学生在校期间的5次成绩（图一）按比例综合（图二）后平行分班。

但是，这5次成绩排序不一致，学生人数也有变化。

怎样把5次成绩综合在一起，VLOOKUP函数可以解决。

图一
图二
后面的举例只把一次成绩“转移”到总成绩工作表中。

图一是成绩工作簿七下期末成绩工作表，图二是成绩工作簿总成绩工作表，如何把七下期末成绩的信息“转移”到总成绩中来（把图二的空白处填满），就是我们要解决的问题。

二、VLOOKUP函数语法介绍
VLOOKUP函数是一个查找函数，语法规则如下：
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
1、Lookup_value为查找的目标，如图二总成绩工作表中的姓名。

①查找的目标必须在查找区域的第一列。

②查找的目标可以是数值、引用或文本。

2、Table_array是查找区域。

如果图二总成绩中的姓名为查找的目标，那么图一七下期末成绩中的查找区域是B2:F14，不能是A2:F14。

3、col_index_num是返回值在查找区域中的列序号。

如果查找区域是图二总成绩B2:F14，col_index_num分别为1、2、3、4、5时，则分别返回与姓名、班级、语文、数学、英语相对应的值。

如果col_index_num分别为0（小于1）或7（大于查找区域的列数），则分别返回错误值 #VALUE!、#REF!。

4、Range_lookup是精确匹配与近似匹配
Range_lookup是一个逻辑值。

Range_lookup为false，则返回精确匹配（与查找目标精确匹配），如果找不到，则返回错误值 #N/A。

Range_lookup为TRUE，则返回近似匹配（与查找目标近似匹配），即如果找不到，则返回小于查找目标的最大值。

近似匹配时，第四个参数TRUE可以省略。

近似匹配时，查找区域的首列应升序排列。

FALSE和TRUE分别以0和1参与运算，在函数中可以用0和1替代。

综上所述，VLOOKUP的语法为：VLOOKUP（查找目标，查找范围，返回值的列数，0或1)。

三、VLOOKUP函数举例分析
在图二总成绩工作表的D2单元格输入=VLOOKUP(B2,七下期末成绩!B2:F15,3,0),公式的含义是:在七下期末成绩工作表查找段志全(查找目标B2)，返回七下期末成绩!B2:F15与段志全相对应的语文(第3列)成绩，结果为106。

为使D2单元格的公式向右向下复制而不产生错误，对D2单元格的公式可修改为=VLOOKUP($B2,七下期末成绩!$B$2:$F$15,COLUMN(C$1),0)，说明如下:
1、改查找目标单元格相对引用为混合引用，即改B2为$B2。

当公式向下复制时，查找目标依次为$B3、$B4、$B5……。

当公式向右复制时，查找目标$B2不发生改变。

$B2也叫列绝对引用。

2、改查找区域相对引用为绝对引用，即改七下期末成绩!B2:F15为七下期末成绩!$B$2:$F$15，当公式向下向右复制时，查找区域不发生改变。

3、改返回值在查找区域中的列序号3为COLUMN(C$1)。

COLUMN函数返回的是引用的列号，COLUMN(A1)、COLUMN(A2)、 COLUMN(A3)返回1，因为A1、A2、A3都
在第1列。

COLUMN(C1) 、COLUMN(C2)、 COLUMN(C3)返回3，因为C1、C2、C3都在第3列。

COLUMN(C$1)中，C$1是混合引用(行绝对引用)。

当公式向下复制时，COLUMN(C$1)不发生改变，始终为3；当公式向右复制时，COLUMN(C$1)依次变为COLUMN(D$1)3 COLUMN(E$1) COLUMN(F$1)，即列序号为4、5、6……。

D2单元格的公式向右向下复制，结果如下:
图三
四、新问题的提出与IF函数返回数组
现在要把七年级成绩工作表中的学籍号“转移”到总成绩工作表中，VLOOKUP 函数的查找的目标必须在查找区域的第一列，那么，学籍号所在的列就在查找区域之外，一般的办法是添加辅助列，复制姓名列，插入到学籍号列的前面。

不添加辅助列行不行呢？回答是肯定的，这就要用到IF函数。

IF函数是EXCEL中常用的一个函数，表面上看起来比较简单，但深层次的运用却有点复杂，对于EXCEL初识者来说难于理解。

1、IF函数语法
IF函数语法：IF(logical_test，value_if_true，value_if_false）。

第一个参数logical_test指条件值，第二、三个参数分别是返回值1、返回值2。

如果条件值为真(TRNE)，则公式的结果是返回值1；如果条件值为假(FALSE)，则公式的结果是返回值2。

举例如下：
图四
图四中，C2、C3、C4、C5单元格的公式解释如下：
=IF(B2<80,"一般","优秀")，B2<80为真，返回值一般；
=IF(B3<80,"一般","优秀")，B3<80为假，返回值优秀；
=IF(B4<80,"一般","优秀")，B4<80为真，返回值一般；
=IF(B5<80,"一般","优秀").，B2<80为假，返回值优秀。

2、IF函数返回数组
当IF函数的第一个参数是一个数组，其返回值也是一个数组。

①什么是数组？
数组，就是相同类型的数据的集合。

{1,2}是数组，{1，2，5}是数组， C2:C10是数组，C2:E10是数组，B2:B7>=35是数组。

{1，0}和{0，1}也是数组，它们在公式中使用时是表示真假的数组，即{ TRUE，FALSE }和{ FALSE ，TRUE}。

单元格区域是数组。

图四中，A2:A5表示的是{张三，李四，王五，刘六}数组。

图四中, A2:B5区域表示的数组是{张三,60;李四,80;王五,75;刘六,84}
②IF函数返回数组
以图三为例，在A2单元格输入=IF({0,1},$B$2:$B$15,C$2:C$15)，然后在编辑栏内选中公式按F9（F9键有一个作用，就是检验公式的结果，嵌套的公式也可以检验，选中嵌套的公式按F9），结果是一个数组：{6,"王博阳"；3,"胡桐"；7,"黄帅"；1,"简菲"；4,"刘诗"；10,"喻琪"；13,"贾茹"；14,"王珂"；2,"曾杰"；5,"肖涵"；8,"段志全"；10,"罗若冠"；11,"周子"；0,0}。

这个数组是由二列数据组成的数组，如图五：
图五
比较图五与图三，姓名列与班级列调换了。

=IF({0,1},$B$2:$B$15,C$2:C$15)与=IF({1,0},C$2:C$15, $B$2:$B$15)的结果是一样的，
四、VLOOKUP函数与IF函数配合使用
我们再回到图三总成绩工作表和图一七下七下期末成绩工作表，在总成绩工作表的A2单元格输入
{=VLOOKUP($B2,IF({1,0},七下期末成绩!$B$2:$B$14,七下期末成绩!$A$2:$A$14),2,0)}
（注：数组公式的外部必须加上{}；同时按下CTRL+SHIFT+ENTER，公式的外面会自动加上大括号{}）。

结果如图六
图六
上面的公式中，VLOOKUP函数的四个参数如下：
查找目标：$B2
查找范围：IF({1,0},七下期末成绩!$B$2:$B$14,七下期末成绩!$A$2:$A$14)。

使用IF函数（返回数组）后，B列为第一列，A列为第一列，B列与A列组成查找区域。

返回值的列数：2。

精确匹配与近似匹配：0。

五、新问题的提出与解决
假如在成绩中增加一个同名学生，如图七七下期末成绩
图七
现在要把图七七下期末成绩转移到图八总成绩中，
图八
在A2单元格输入
{=VLOOKUP($B2,IF({1,0},七下期末成绩!$B$2:$B$14,七下期末成绩!$A$2:$A$14),2,0)}，并向下复制。

在D2单元格输入
=VLOOKUP($B2,七下期末成绩!$B$2:$F$15,COLUMN(C$1),0)，并向下向右复制，结果如图九。

图九
比较图九总成绩工作表与图七七下期末成绩工作表，发现有明显错误存在:二
个工作表中均有二个黄帅，总成绩工作表中，七(7)班黄帅语文、数学、英语成绩分别为106、120、112，七(11)班黄帅语文、数学、英语成绩分别为100、90、104；图九总成绩工作表中二个黄帅成绩一样，语文、数学、英语成绩分别为106、120、112。

以此可以看出，在查找目标有重复数据的情况下，VLOOKUP函数不会报错，只会直接返回第一个找到的对应值。

因此，在查找目标重复的情况下，必须对查找目标进行多条件限制。

解决上面出现的问题，运用VLOOKUP函数与IF函数可以解决。

1、对查找目标进行多条件限制，即把$B2改为$B2&$C2。

2、用IF函数建立一个$B2&$C2处于首列的查找区域。

①七下期末成绩!$B$2:$B$15是一个数组，七下期末成绩!$C$2:$C$15是一个数组，七下期末成绩!$B$2:$B$15&七下期末成绩!$C$2:$C$15是一个如图十的数组。

图十图十一图十二图十三
②根据IF函数返回数组建立查找区域。

IF({1,0},七下期末成绩!$B$2:$B$15&七下期末成绩!$C$2:$C$15,七下期末成绩!D$2:D$15)返回的是图十一的数组
IF({1,0},七下期末成绩!$B$2:$B$15&七下期末成绩!$C$2:$C$15,七下期末成绩!E$2:E$15)返回的是图十二的数组
IF({1,0},七下期末成绩!$B$2:$B$15&七下期末成绩!$C$2:$C$15,七下期末成绩!F$2:F$15)返回的是图十三的数组
③用VLOOKUP函数把七下期末成绩转移到总成绩工作表。

在总成绩工作表A2单元格输入
{=VLOOKUP($B2,IF({1,0},七下期末成绩!$B$2:$B$14,七下期末成绩!$A$2:$A$14),2,0)}，并向下复制。

在总成绩工作表D2单元格输入
{=VLOOKUP($B2&$C2,IF({1,0},七下期末成绩!$B$2:$B$15&七下期末成绩!$C$2:$C$15,七下期末成绩!D$2:D$15),2,0)}，并向下向右复制。

结果如图十四。

图十四
最后需要说明的是，运用VLOOKUP函数进行查找，当查找值在查找区域不存在时，结果会返回#N/A（错误值），再运用ISNA函数可以将#N/A变为空值。

至此，VLOOKUP函数与IF函数的配合实现了在教务管理中的多条件查找，其中IF函数
返回数组是解决问题的关键。