《从 N 个选择中选一个的 Excel 条件函数.doc》由会员分享,可在线阅读,更多相关《从 N 个选择中选一个的 Excel 条件函数.doc(5页珍藏版)》请在三一文库上搜索。
1、精品论文大集合从 N 个选择中选一个的 Excel 条件函数骆瑞昌,李小英 广西南宁高级技校,南宁(530001) E-mail:摘要:本文以一个英语等级填充实例,介绍了关于 Excel 从 N 个选择中选一个的条件函 数,并从那些 N 维的复杂程度来讨论了 IF 函数、LOOKUP 函数、HLOOKUP 函数、VLOOKUP函数等同类方法的使用,解决了 IF 函数嵌套多层使多数人难以理解接受的问题,对初学者快速掌握这些函数所需要的技巧有一定的帮助。关键词:Excel 等级评定;Excel 条件函数;IF 嵌套函数;LOOKUP 函数 中图分类号:G4341. 问题的提出如表 1,设学生1英语
2、分数为 x,按照表 2 的评定划分标准,表 1 英语的等级2列区域分数区间标准 1标准 2标准 3x30不及格不及格无级30x60一级60x65及格及格二级65x70三级70x75四级75x80良好五级80x85六级85x90七级90x95优秀八级95x九级C4:C11 应该用 Excel 的什么公式填充呢?- 5 -表 1 学生英语成绩等级评定表表 2 学生英语等级评定划分标准ABCD12学生英语成绩等级评定表3姓名英语等级4李广林96.35马丽萍74.06高青云48.37王卓然91.38张成斌68.79郑俊霞88.310张雷芳28.311韩云会58.0这是属于从 N 个选择中选一个的问题。
3、对于准标 1,单元 C4 用 IF(B460, 不及格, 及格)公式,多数人都能很容易地理解;对于标准 2,单元 C4 用 IF(B460, 不及格, IF(B475,及格, IF(B490, 良好, 优秀)比较复杂,多数人比较难理解;对于准标 3, 单元 C4 用 IF(B430, 无级 ,IF(B460, 一级 ,IF(B465, 二级 ,IF(B470, 三级 ,IF(B475, 四级 ,IF(B480, 五级 ,IF(B485, 六级 ,IF(B490, 七级 ,IF(B495, 八级 ,九级)就更复杂了,多数人很难理解接受,而且 IF 函数如此嵌套入 8 层已超过 Excel2003
4、 版本 IF 函数嵌套最多能嵌入 7 层的限制3。为此,我们需要寻找另一种使多数 人易于理解接受的好方法来解决准标 3 的等级评定问题。2. 解决问题的方法对于准标 3 问题的填充,可在等级列的 C4 插入下列之一的公式(公式 2.12.4 或公式3.13.4 其中之一),然后用填充柄把 C4 公式下拉直到最后一个人的 C11 即可。公式 2.1:“=LOOKUP(B4,0,30,60,65,70,75,80,85,90,95;无级,一级,二级,三级,四 级,五级,六级,七级,八级, 九级)”表 3 公式 2.1 直观解释表0,30,60,65,70,75,80,85,90,95;无级,一级,
5、二级,三级,四级,五级,六级,七级,八级, 九级该公式含义解释为4:如表 3,在数组首行查找小于等于 B4 的最大值(95),并返回末行与此最大值同列交叉处的值(九级)。公式中数组各行写在大括号内,各行用分号隔 开。查找规则是:若数组列数行数,则在首列查找,否则在首行查找。而本公式数组是 2 行 10 列,是属于行数列数,故是在数组首行查找。公式 2.2:“=LOOKUP(B4,0,无级;30,一级;60,二级;65,三级;70,四级;75,五级;80,六级;85,七级;90,八级;95,九级)”该公式含义解释为4:如表 4,在数组首列查找 小于等于 B4 的最大值(95),并返回末列与此最大
6、 值同行交叉处的值(九级)。公式中数组各行写在大 括号内,各行用分号隔开。查找规则是:若数组列 数行数,则在首列查找,否则在首行查找。而本公 式数组是 2 列 10 行,是属于列数行数,故是在数表 4 公式 2.2 直观解释表0, 无级;30, 一级;60, 二级;65, 三级;70, 四级;75, 五级;80, 六级;85, 七级;90, 八级;95, 九级公式 2.3:“=LOOKUP(B4,$D$2:$M$2,$D$3:$M$3)”,其中$D$2:$M$2, $D$3:$M$3 是英语分数区间的左端数升序单行连续区域和对应标准 3 的单行连续区域(对照表 5 和表 2)。公 式中这两区域
7、为什么要加$的绝对地址呢?这是因为用填充柄把 C4 这公式下拉复制时,公 式中的分数 B4 对各人应该变,而这两区域是等级评定划分标准对每个人是不该变的,加$ 的绝对地址就可使 C4 这公式下拉复制时各人用统一标准 3 的绝对地址不会改变。单元格下 拉复制会使公式中相对行地址递增。表 5 公式 2.3 直观解释表ABCDEFGHIJKLM12学生英语成绩等级表03060657075808590953姓名英语等级无级一级二级三级四级五级六级七级八级九级4李广林96.3该公式含义解释为4:如表 5,在单行区域$D$2:$M$2 中查找小于等于 B4 的最大值(95), 并返回单行区域$D$3:$M
8、$3 与此最大值同列交叉处的值(九级)。ABCDEF12学生英语成绩等级表0无级3姓名英语等级30一级4李广林96.360二级5马丽萍74.065三级6高青云48.370四级7王卓然91.375五级8张成斌68.780六级9郑俊霞88.385七级10张雷芳28.390八级公式 2.4:“=LOOKUP(B4,$E$2:$E$11,$F$2:$F$11) 3 ” , 其 中 $E$2:$E$11 和$F$2:$F$11 是英语分数区间的左端数升序 单列连续区域和对应标准 3 的单列连续区域 (对照表 6 和表 2)。公式中这两区域为什么 要加$的绝对地址呢?解释与上述公式 2.3 的相同。该公式
9、含义解释为4:如表 6,在单列 区域$E$2:$E$11 中查找小于等于 B4 的最大 值(95),并返回单列区域$F$2:$F$11 与此最大值同行交叉处的值(九级)。表 6 公式 2.4 直观解释表上述公式 2.12.4 的任一个公式,都可以作为 C4 的公式,填了 C4 公式后再用填充柄把 C4 公式下拉复制直到最后一个人的 C11 即可解决准标 3 的填充问题。通过上述公式 2.12.4, 可以概括 LOOKUP 函数的普通格式4如下表 7 几种形式:表 7 LOOKUP 函数的普通格式表数组形式 1:LOOKUP( 某值 , 行数 列 数的数 组 )例见上述公式 2.1,注意数组的写
10、法。功能:在数组首行查找小于等于某值的最大值,并返回末行与此最大值同列交叉处的值。数组形式 2:LOOKUP( 某值 , 列数 行 数的数组 )例见上述公式 2.2,注意数组的写法。功能:在数组首列查找小于等于某值的最大值,并返回末列与此最大值同行交叉处的值。向量形式 1:LOOKUP(某值,单行升区域 1, 单行区域 2)例见上述公式 2.3功能:在单行升区域 1 查找小于等于某值的最大值,并返回单行区域 2 与此最大值同列交叉处的值向量形式 2:LOOKUP(某值,单列升区域 1, 单列区域 2)例见上述公式 2.4功能:在单列升区域 1 查找小于等于某值的最大值,并返回单列区域 2 与此
11、最大值同行交叉处的值。3. 讨论关于 Excel 从 N 个选择中选一个的问题,一般是从中查找符合标准条件的那一个,应用 什么公式较好呢?当 N=2 时,是最简单的问题,此时用 IF 函数较通俗易懂。如上述问题标准 1 的 C4 单 元公式,用“=IF(B460, 不及格, 及格)”较简便。当然,用“=LOOKUP (B4,0,60;不及格 , 及格)”或“=LOOKUP (B4,0,不及格;60, 及格)”也是可以的。当 N2 但 N 不是太大如 10 左右时,是较复杂的问题,此时用 LOOKUP 函数的数组形 式较通俗易懂。如上述问题标准 3 的 C4 单元公式,用上述的公式 2.1 或公
12、式 2.2 较简便。 当 N2 且 N 较大如 10 以上时,是更复杂的问题,此时用 LOOKUP 函数的向量形式较简便。如上述问题标准 3 的 C4 单元公式,用上述的公式 2.3 或公式 2.4 较简便。 此外,HLOOKUP 函数4与 V LOOKUP 函数4,跟上述 LOOKUP 函数在直观性与功能效果方面是差不多的,下面给出 HLOOKUP 与 V LOOKUP 的普通格式解析供读者参考选用。表 7 HLOOKUP 与 V LOOKUP 函数的普通格式表数组形式 1:HLOOKUP( 某值 , 数组 , 该组 第 k 行 )例见下面公式 3.1功能:在数组首行查找小于等于某值的最大值
13、,并返回第 k 行与此最大值同列交叉处的值。数组形式 2:VLOOKUP( 某值 , 数组 , 该组 第 k 列 )例见下面公式 3.2功能:在数组首列查找小于等于某值的最大值,并返回第 k 列与此最大值同行交叉处的值。向量形式 1:HLOOKUP( 某值 , 区域, 该区域 第 k 行 )例见下面公式 3.3功能:在区域查找小于等于某值的最大值,并返回该区域第 k 行与此最大值同列交叉处的值向量形式 2:VLOOKUP( 某值 , 区域, 该区域 第 k 列 )例见下面公式 3.4功能:在区域查找小于等于某值的最大值,并返回该区域第 k 列与此最大值同行交叉处的值。注意:表中的数组各行写在大
14、括号内,各行用分号隔开,表中区域的首行或首列是升序的。可替代公式 2.1 的公式 3.1:“=HLOOKUP(B4,0,30,60,65,70,75,80,85,90,95;无级,一级,二级,三级,四级,五级,六级,七级,八级, 九级,2)”该公式含义解释为:参见表 3,在数组首行查找小于等于 B4 的最大值(95),并返回 第 2 行与此最大值同列交叉处的值(九级)。可替代公式 2.2 的公式 3.2:“=VLOOKUP(B4, 0,无级;30,一级;60,二级;65,三级;70,四级;75,五级;80,六级;85,七级;90,八级;95,九级,2)”该公式含义解释为:参见表 4,在数组首列
15、查找小于等于 B4 的最大值(95),并返回 第 2 列与此最大值同行交叉处的值(九级)。可替代公式 2.3 的公式 3.3:“=HLOOKUP(B4,$D$2:$M$3,2)”该公式含义解释为:参见表 5,在区域$D$2:$M$3 首行查找小于等于 B4 的最大值(95), 并返回该区域第 2 行与此最大值同列交叉处的值(九级)。可替代公式 2.4 的公式 3.4: “=VLOOKUP(B4,$E$2:$F$11,2)”该公式含义解释为:参见表 6,在区域$E$2:$F$11 首列查找小于等于 B4 的最大值(95), 并返回该区域第 2 列与此最大值同行交叉处的值(九级)。LOOKUP 与
16、 HLOOKUP/ VLOOKUP 的默认区别是前者在第末行或列返回值,而后者在 第 k 行或列返回值。两者默认共同点都是在某范围的首行或列查找小于等于某值的最大值。 其中这“某范围”通常是一个数组或绝对地址的区域,这被查找的“首行或列”必须是升排序的(否则找到的那个最大值就不准确),这升排序中的最小值必须小于等于这“某值”(否则就 找不到那个最大值),理解这些意思需要结合上例公式及其直观解释表方知其含义。4. 结语要从 N 个选择中选一个,可以用 IF、LOOKUP、HLOOKUP、VLOOKUP 等条件函数, 其中用 LOOKUP 函数是比较通用而通俗易懂的方法。 初学者可先掌握 LOOK
17、UP 函数普 通格式的意义,再进一步研究 HLOOKUP 与 VLOOKUP 的普通格式,这样就可以用多种方 法解决 IF 函数嵌套多层令人难以理解接受的问题,就可以快速掌握 Excel 条件函数的应 用技巧。参考文献1 黄培周,陈捷主编.计算机实用技术操作实习.(第二版),北京:中国劳动社会保障出版社,2002.6:144-153 2 黄培周,陈捷主编.计算机实用技术(第二版),北京:中国劳动社会保障出版社,2002.6:169-2293 宋志明.LOOKUP 函数解决 IF 函数嵌套层数的问题.http:/ 微软中国. Microsoft Office Excel2003 帮助:IF、LO
18、OKUP、HLOOKUP、VLOOKUP 函数的语法说明.About “Excel” choose one from multiple(N) choices to conditional functionLuo Ruichang, Li XiaoyingGuangxi Nanning High-class Polytechnic School, Nanning (530001)AbstractThis article uses Fill in Grade in English examples to introducesomesolution about “Excel”chooses one
19、from multiple(N) choices to conditional function,and from those complexity multiple(N)choices to discuss IF function, Lookup function, H lookup function, and Vlookup function using the same principle, this method is to solve “IF nested function” from many people who have the same difficulty to understand the function problems. For those beginners at Excel, it can quickly help them to grasp the skills necessary to these functionKey words:Excel grade; Excel conditional function; IF nested function; Lookup function作者简介: 骆瑞昌,男,数学系本科毕业,广西南宁高级技校高级讲师。 李小英,女,数学与计算机科学系本科毕业,广西南宁高级技校讲师。
链接地址:https://www.31doc.com/p-3624009.html