EXCEL函数常用技巧浅析(五)IF函数浅析

EXCEL函数常用技巧浅析(五)技巧五:IF函数浅析IF函数应用范围很广,在写这个函数之前,我犹豫了很久,这个函数如果想要把他完全写清楚是一件比较困难的事情,因为我们单独用IF的时候比交少,一般配合其它函数使用较多,这里我先把我所想到的先写下来,欢迎各位前辈高人指点.一:IF函数的基本功能IF函数最基本的功能就是对条件表达式进行判断,如果条件表达式为TRUE或不等于0则返回第二参数,否则返回第三参数;如果第二三参数只写逗号,则默认返回0;如果忽略第三参数,则返回FALSE,注意:第二参数是忽略不了的.完整参数第二参数只写逗号第三参数只写逗号忽略第三参数这种表达方式只是第二参数写逗号,第三参数忽略true1=IF(B10,1,2)true0=IF(G11,,2)1=IF(G11,1,)1=IF(F11,1)0=IF(F12,)false2=IF(B11,1,2)false2=IF(G12,,2)0=IF(G12,1,)false=IF(F12,1)false=IF(F13,)11=IF(B12,1,2)10=IF(G13,,2)1=IF(G13,1,)1=IF(F13,1)0=IF(F14,)-11=IF(B13,1,2)-10=IF(G14,,2)1=IF(G14,1,)1=IF(F14,1)0=IF(F15,)-0.11=IF(B14,1,2)-0.10=IF(G15,,2)1=IF(G15,1,)1=IF(F15,1)0=IF(F16,)0.11=IF(B15,1,2)0.10=IF(G16,,2)1=IF(G16,1,)1=IF(F16,1)0=IF(F17,)02=IF(B16,1,2)02=IF(G17,,2)0=IF(G17,1,)false=IF(F17,1)false=IF(F18,)二:IF函数的数组运用IF函数的数组运用与技巧四的数组的基本原理是一样的,只是IF函数多了一个表达式来控制返回的结果,IF函数的三个参数都支持数组,所以在日常数组公式中运用非常之广.1.重构数组实现反向查找,IF({1,0},数组,数组),此种方法在VLOOKUP函数里应用最多,虽然我们可以应用其它办法来解决,但这种思路还是需要学习一下的;如下表:我们要依据姓名来查找他所属部门,部门姓名查找姓名:销售部张三张三销售部人事部李四=VLOOKUP(E26,IF({1,0},C26:C30,B26:B30),2,0)资材部王五生产部陈六业务部刘七第一步:此种列表如果要用VLOOKUP函数来解决的话,首先我们会发现,VLOOKUP函数搜索某个单元格区域（区域：工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）的第一列，然后返回该区域相同行上任何单元格中的值,显然我们利用第二列来查找第一列根据VLOOKUP函数的特性直接查找是的行不通,这样就需要重新构造VLOOKUP函数的第二参数使姓名在第一列,部门在后面列来查找,这里我们就可以通过IF函数的数组特性来构造了;张三销售部李四人事部王五资材部陈六生产部刘七业务部=IF({1,0},C26:C30,B26:B30)这里我们利用IF函数的第一参数不等于0返回第二参数,第一参数为0返回第三参数,根椐数组的扩展性,构造成一个姓名在前,部门在后的五行两列的二维数组根据数组的基本原理,这里设置的第一参数会自动扩展成与第二参数,第三参数相同行数的二维数组,IF函数的第一参数可以驱动结果的方向,{1,0}扩展成1010101010然后IF函数再根据上面的二维数组分别返回结果,形成我们上面所得到的二维数组;第二步:取得上面的数组后我们就可以根据VLOOKUP函数的特性来取得结果了,我们重构数组后,第一列是姓名,第二列是部门,在第一列查找,返回第二列的值,第三参数所以为2,再把第四参数设为精确查找.李四人事部=VLOOKUP(B53,IF({1,0},C26:C30,B26:B30),2,0)2.数组加强,理解IF函数的各个参数的对应设置2.1=IF({1,0;1,0},{1;2},{3;4})131242=IF({1,0;1,0},{1;2},{3;4})IF函数的第一参数可以决定结果的方向,在此例中,IF函数的第一参数为一个二行二列的数组,第二三参数分别为一列两行的数组,则第二参数自运扩展为一个二行二列的数组.第二参数扩展为第三参数扩展为11332244然后由IF函数的第一参数在两个数组中取数,如果为1则取第二参数扩展数组相对应的数值,如果为0则取第三参数扩展数组中相对应的值;1324=IF({1,0;1,0},{1;2},{3;4})2.2=IF({1,0;1,0},1,2)1212=IF({1,0;1,0},1,2)此例与上例类似,第二三参数分别扩展为与第一参数一样的二行二列数组,然后再由第一参数在三个扩展数组中取值;第二参数扩展为第三参数扩展为11221122取值同上2.3=IF({1,0;1,0;1,0},{1,2},{3,4})141414=IF({1,0;1,0;1,0},{1,2},{3,4})此例第一参数为二列三行的数组,第二参数与第三参数为二列一行的数组,这里第二参数与第三参数也会自动扩展,第二参数扩展为第三参数扩展为123412341234取数时,因为1在第一列,所以取第二参数扩展数组第一列的值1,0在第二列,取第三参数扩展数组第二列的值4;141414=IF({1,0;1,0;1,0},{1,2},{3,4})2.4=IF({1,0;1,0;1,0},{1,2;3,4;5,6},0)103050=IF({1,0;1,0;1,0},{1,2;3,4;5,6},0)此例相对于来说也比较容易理解,第二参数与第一参对称,第二参数扩展为二列三行的数组.第二参数本身对称第一参数第三参数扩展为120034005600取值方法同上例2.5=IF({1;0;0},{1,2;3,4;5,6},0)120000=IF({1;0;0},{1,2;3,4;5,6},0)此例第一参数为一列三行的数组,第二参数为二列三行的数组,第三参数为单个元素,第一参数的行数与第二参数对称,第三参数为单个元素,三个参数运算时分别为以下数组;第一参数行数与第二参数对称第二参数不变第三参数扩展为111200003400005600再根据IF函数的特性取相对应的值2.6=IF({1;0;1;0},{1,2;3,4;5,6},0)、=IF({1;0;1;1},{1,2;3,4;5,6},0)与=IF({1;0},{1,2;3,4;5,6;7,8},{0,9,10})的区别121212#N/A000009105656#N/A#N/A#N/A00#N/A#N/A#N/A#N/A#N/A=IF({1;0;1;0},{1,2;3,4;5,6},0)=IF({1;0;1;1},{1,2;3,4;5,6},0)=IF({1;0},{1,2;3,4;5,6;7,8},{0,9,10})通过比较我们可以看到,IF函数的结果:纵向大小是由任意参数的最大纵向数组决定,横向大小是任意参数的最大横向数组决定,当参数不是单个元素时而且数组行或列不对称时是不能自动扩展,当无对应值时会产生错误。三：N(IF({1},))或T(IF({1},))结构N(IF({1},))或T(IF({1},))结构结构可以对某些不支持数组参数的参数数组化,返回内存数组;此种方法在技巧四中介绍过了,就不再多讲了,这里我们只能是理解这种方法能够实现这种功能,因为函数以我的水平是无法从理论上来解释某种现象,只有通过不断的测试才能发现新的方法与技巧。此贴由chenhh803原创,转贴请先征求本人同意技巧解析一：下表为某公司客户订购清单,现需把他转为一列,客户名如果相同,只取一个,所订购的产品在客户名的下面.客户购买产品结果客户1毛巾客户1客户1洗涤精毛巾客户1肥皂洗涤精客户2肥皂肥皂客户3毛巾客户2客户3洗涤精肥皂客户4毛巾客户3毛巾洗涤精客户4毛巾=INDIRECT(TEXT(SMALL(IF({1,0},IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1,ROW($4:$10)*10+2),ROW(A1)),"r0c0"),)&""公式解析:第一步:去重取唯一客户名最小的的行列号,用IF函数判断客户名称是否相同,,如果不相同则取其行号乘以10再加上其列号,否则取2003版中的最大行号65536乘以10加上其列号,这里把行列号放在一起是为了最后的INDORECT函数取值时所用;=IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1IF函数的第一参数第二参数第三参数IF函数结果乘10加列号141客户1客户false655364441655361客户1客户1true65536565536655361655361客户1客户1true65536665536*10+165536171客户2=客户1false65536777181客户3客户2false655368881655361客户3客户3true65536965536655361101客户4客户3false655361010101单个元素扩展与第一参数对称第一参数为TRUE取第二参数,否则取第三参数第二步:因为产品名称是要全部取出的,所以利用IF({1,0},)结构重新构造一个7行2列的数组,第一列为IF函数去重后唯一值的行列号,第二列为全部产品名称的行列号=IF({1,0},IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1,ROW($4:$10)*10+2)414241416553615265536165536165536162655361655361717271718182818165536192655361655361101102101101IF函数的结果全部购买产品的行列号第三步:利用SMALL函数依次取出构造好的列号,因为第二列的列号大于第一列的列号,所以每次取数时先取出客户名,再依次取出产品名称,414252627172818292101102655361655361=SMALL(IF({1,0},IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1,ROW($4:$10)*10+2),ROW(1:13))第四步:用TEXT函数把取得的行列号转化成为R1C1样式,最后用INDIRECT函数取出结果R4C1R4C2R5C2R6C2R7C1R7C2R8C1R8C2R9C2R10C1R10C2R65536C1R65536C1=TEXT(SMALL(IF({1,0},IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1,ROW($4:$10)*10+2),ROW(1:13)),"R0C0")客户1毛巾洗涤精肥皂客户2肥皂客户3毛巾洗涤精客户4毛巾=T(INDIRECT(TEXT(SMALL(IF({1,0},IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1,ROW($4:$10)*10+2),ROW(1:13)),"R0C0"),))原公式采用下拉方式,这里为了方便查看,使用了T做为内存数组方式表示,技巧解析二：下表为某学校学生考试情况,为方便打印,为节约纸张、绿色环保,需按人次打印再分发给每位同学,现要重新排版，格式如结果。（表格数据只是一个模拟，如有错误，敬请见谅）姓名学号 年级 六年级体育公开课教案九年级家长会课件PPT下载六年级家长会PPT课件一年级上册汉语拼音练习题六年级上册道德与法治课件 专业课程名称考试日期考试时间考场米米菜5036500842011计算机多媒体技术基础7/8/1114:00-16:001205米米菜5036500832011计算机信息管理系统7/12/1114:00-16:001209路上菜5036500832011计算机信息管理系统7/12/1114:00-16:001209路上菜5036500822011计算机多媒体技术基础7/8/1114:00-16:001205路下菜5036500822011计算机信息管理系统7/9/1114:00-16:001209路下菜5036500822011计算机网络数据库SQL7/10/1114:00-16:001210路小菜5036500812011计算机多媒体技术基础7/8/1114:00-16:001205牛皮菜5036500812011计算机信息管理系统7/12/1114:00-16:001209牛皮菜5036500812011计算机网络数据库SQL7/13/1114:00-16:001210牛皮菜5036500812011计算机市场营销学7/14/1114:00-16:001211姓名学号年级专业课程名称考试日期考试时间考场米米菜5036500842011计算机多媒体技术基础7/8/1114:00-16:001205米米菜5036500832011计算机信息管理系统7/12/1114:00-16:001209姓名学号年级专业课程名称考试日期考试时间考场路上菜5036500832011计算机信息管理系统7/12/1114:00-16:001209路上菜5036500822011计算机多媒体技术基础7/8/1114:00-16:001205姓名学号年级专业课程名称考试日期考试时间考场路下菜5036500822011计算机信息管理系统7/9/1114:00-16:001209路下菜5036500822011计算机网络数据库SQL7/10/1114:00-16:001210姓名学号年级专业课程名称考试日期考试时间考场路小菜5036500812011计算机多媒体技术基础7/8/1114:00-16:001205姓名学号年级专业课程名称考试日期考试时间考场牛皮菜5036500812011计算机信息管理系统7/12/1114:00-16:001209牛皮菜5036500812011计算机网络数据库SQL7/13/1114:00-16:001210牛皮菜5036500812011计算机市场营销学7/14/1114:00-16:001211=INDEX(A1:H103,RIGHT(SMALL(IF({1,1,0},IF(A93:A104<>A92:A103,IF({1,0},IF(ROW(1:12)<11,ROW(92:103)+0.12092),IF(ROW(1:12)>1,ROW(92:103)+0.103))),ROW(93:104)),ROW(1:20)),3),COLUMN(A:H))多单元格区域数组公式公式解析:此例要解决的问题就在于，相同学生要排在一起，然后每个学生中间要加多一个表头与一行空格，以方便打印。为完美体现数据，先在A103：H103中每个单元格输入一个空格第一步:先用IF({1,0},,)结构构造一个二列12行的数组,第一列数组为行号加表头和行号,第二列数组为行号加空格行的行号92.12092false92.12092false93.1209293.10393.1209293.10394.1209294.10394.1209294.10395.1209295.10395.1209295.10396.1209296.10396.1209296.10397.1209297.10397.1209297.10398.1209298.10398.1209298.10399.1209299.10399.1209299.103100.12092100.103100.12092100.103101.12092101.103101.12092101.103false102.103false102.103false103.103false103.103=IF(ROW(1:12)<11,ROW(92:103)+0.12092)=IF(ROW(1:12)>1,ROW(92:103)+0.103)=IF({1,0},IF(ROW(1:12)<11,ROW(92:103)+0.12092),IF(ROW(1:12)>1,ROW(92:103)+0.103))第二步:用数据源名字上下交错,如果考生姓名不同,则取一个表头行行号一一个空格行行号;92.12092falsefalsefalse94.1209294.103falsefalse96.1209296.103falsefalse98.1209298.10399.1209299.103falsefalsefalsefalsefalse102.103false103.103=IF(A93:A104<>A92:A103,IF({1,0},IF(ROW(1:12)<11,ROW(92:103)+0.12092),IF(ROW(1:12)>1,ROW(92:103)+0.103)))第三步:利用IF({1,1,0},,)结构重构一个三列十二行的数组,把所有学生考试的行号也列进数组的第三列92.12092false93falsefalse9494.1209294.10395falsefalse9696.1209296.10397falsefalse9898.1209298.1039999.1209299.103100falsefalse101falsefalse102false102.103103false103.103104第四步:从上面数组可以看出,只要利用SMALL函数就可以为每个学生加上一个表头与空格了,再用RIGHT函数取得插入的表头与空格行的行号;92.120920929393949494.10310394.120920929595969696.10310396.120920929797989898.10310398.12092092999999.10310399.12092092100100101101102102102.103103SMALL=RIGHT(SMALL(IF({1,1,0},IF(A93:A104<>A92:A103,IF({1,0},IF(ROW(1:12)<11,ROW(92:103)+0.12092),IF(ROW(1:12)>1,ROW(92:103)+0.103))),ROW(93:104)),ROW(1:20)),3)第五步:用INDEX函数的数组功能,取得想要的数据姓名学号年级专业课程名称考试日期考试时间考场米米菜5036500842011计算机多媒体技术基础4073214:00-16:001205米米菜5036500832011计算机信息管理系统4073614:00-16:001209姓名学号年级专业课程名称考试日期考试时间考场路上菜5036500832011计算机信息管理系统4073614:00-16:001209路上菜5036500822011计算机多媒体技术基础4073214:00-16:001205姓名学号年级专业课程名称考试日期考试时间考场路下菜5036500822011计算机信息管理系统4073314:00-16:001209路下菜5036500822011计算机网络数据库SQL4073414:00-16:001210姓名学号年级专业课程名称考试日期考试时间考场路小菜5036500812011计算机多媒体技术基础4073214:00-16:001205姓名学号年级专业课程名称考试日期考试时间考场牛皮菜5036500812011计算机信息管理系统4073614:00-16:001209牛皮菜5036500812011计算机网络数据库SQL4073714:00-16:001210牛皮菜5036500812011计算机市场营销学4073814:00-16:001211