【Excel实例】IF函数嵌套、LOOKUP、VLOOKUP函数实现成绩分等级.docx

【excel实例】if函数嵌套、lookup、vlookup函数实现成绩分等级最近，总有人问我一些关于lookup、vlookup函数使用的问题。其实二者有相似之处，但也有不同之处，今天做一个很简单的实例，这个实例分别用if函数嵌套、lookup、vlookup三种函数实现，还不是很了解这三个函数的的朋友可以看一下，会使用的完全没有必要来看了。由于有人问了我学生成绩如何转化为等级的问题，今天就以此为例，演示三个函数的使用，实现的是同一种功能。先准备以下两个表（为了方便观看与截图，我把两个表放在一起了），一个是成绩表，一个是等级表，如下图所示：右边的小表是数据分段，分段的数据供lookup、vlookup函数使用，实现的意思是0-60(不含60)为不及格，60-70(不含70)为及格, 70-85(不含85)为良, 85以上为优。这个小表的单元格区域为g5:h8，这个在后面要用到。一、用if嵌套实现学生成绩转化为等级。这一功能我们在第三列实现，分析：根据上面的等级规则，用if函数进行判断，但一条if语句只能有两种结果，而这里要四种结果（优良中差），因此一条语句是无法实现的。必须进行嵌套使用。如何嵌套的呢？必须按照一定逻辑顺序来，否则实现不了。这里我按从小到大的顺序来对数据进行判断与转换。、首先判断b2单元格的成绩是否小于60，如果小于60为不及格，否则就不确定。公式为：=if(b260,不及格,不确定)、为什么不确定的呢？因为大于60的有三种可能，这个先不管。出现“不确定”是不对的，必须将“不确定”变成公式，这就是嵌套。现在该判断是否小于70了，小于70的为及格，否则为不确定。公式为：=if(b270,及格,不确定)，将这个公式嵌入第一个公式的“不确定”处。嵌套公式为：=if(b260,不及格, if(b270,及格,不确定)、现在实现的两重if的嵌套，但还没有最后完成。还有大于70的情况，只剩两个情况，小于85与大于85了，即良与优，不存在不确定的情况了。这个用一个if语句可以搞定。即如果小于85则等级为良，否则等级为优，公式为：=if(b285,良,优)将此公式嵌入到上一个公式的“不确定处”即可。嵌套后的公式为：=if(b360,不及格,if(b370,及格,if(b385,良,优)在c2单元格中输入此公式，用鼠标向下拖动应用即可。其效果如下图：if嵌套是不是很简单，能理解吗？多做几次就行了。二、用lookup函数来实现。lookup函数是查找函数，可以在单元格区域（本例中即g5:g8）查找一个值(成绩，如b2)，并且返回后一个单元格区域（即h5:h8）中相对应的值。注意：、提供的数据区域必须是升序；，第一个数据为起始，没有达到后一个数据的值，默认属于前一数据，数据查找区域与返回区域必须数据量相同。格式：lookup(要查找的值，查找区域，返回区域)对照格式，本例得到的公式为：=lookup(b2,g5:g8,h5:h8)。由于引用的区域是不变的，为了能拖动应用公式，改为：=lookup(b2,$g$5:$g$8,$h$5:$h$8)在d2单元格输入该公式，并向下拖动应用即可，效果如下图所示：和if函数实现的效果是不是完全一样啊。还要显得简单些。三、用vlookup函数来实现此效果。vlookup函数有些类似于lookup。也能在数据区域（本例为g5:h8）中查找一个值（如b2的值），并返回相对应的指定的列的数值（本例为2）。注意：1、与lookup相比引用区域有变化，可以是多行多列，本例为四行两列（即g5:h8），并且只引用一次；2、与lookup相比不设置返回区域，而是指定返回的列的值，本例为2。（即返回第2列相对应的值，这个列的序号不是相对于整个表格，而只相对于引用区域g5:h8，在这个区域中，等级在第2列，所以为2）3、vlookup函数还有参数，参数为true时，为近似匹配，本例必须用近似匹配（即true）。如果参数为false，则为精确匹配，大家能理解精确匹配吧。vlookup函数语法格式：vlookup（要查找的值，要查找的区域，返回的列号，参数）对照格式得出本例的公式为：=vlookup(b2,g5:h8,2,true)同样，引用的区域g5:h8是不变的，改为静态引用即可。最终公式是：=vlookup(b2,$g$5:$h$8,2,true)在e2单元格输入该公式，并向下拖动鼠标即可，最终效果图如下：三种函数实现了同一种效果，新手跟着做一做，能加深你对这三个函数的理解，理解以后，你再去看看hlookup函数，相信你能自己琢磨出hlo