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)}，并向下复制。