《EXCEL函数常用技巧浅析(五)IF函数浅析.xls》由会员分享，可在线阅读，更多相关《EXCEL函数常用技巧浅析(五)IF函数浅析.xls（14页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、EXCEL函函数数常常用用技技巧巧浅浅析析(五五)技技巧巧五五:IF函函数数浅浅析析IF函数应用范围很广,在写这个函数之前,我犹豫了很久,这个函数如果想要把他完全写清楚是一件比较困难的事情,因为我们单独用IF的时候比交少,一般配合其它函数使用较多,这里我先把我所想到的先写下来,欢迎各位前辈高人指点.一一:IF函函数数的的基基本本功功能能IF函数最基本的功能就是对条件表达式进行判断,如果条件表达式为TRUE或不等于0则返回第二参数,否则返回第三参数;如果第二三参数只写逗号,则默认返回0;如果忽略第三参数,则返回FALSE,注意:第二参数是忽略不了的.完整参数第二参数只写逗号第三参数只写逗号TRU
2、E1=IF(B10,1,2)TRUE0=IF(G11,2)1FALSE2=IF(B11,1,2)FALSE2=IF(G12,2)011=IF(B12,1,2)10=IF(G13,2)1-11=IF(B13,1,2)-10=IF(G14,2)1-0.11=IF(B14,1,2)-0.10=IF(G15,2)10.11=IF(B15,1,2)0.10=IF(G16,2)102=IF(B16,1,2)02=IF(G17,2)0二二:IF函函数数的的数数组组运运用用IF函数的数组运用与技巧四的数组的基本原理是一样的,只是IF函数多了一个表达式来控制返回的结果,IF函数的三个参数都支持数组,所以在日常数
3、组公式中运用非常之广.1.重构数组实现反向查找,IF(1,0,数组,数组),此种方法在VLOOKUP函数里应用最多,虽然我们可以应用其它办法来解决,但这种思路还是需要学习一下的;如下表:我们要依据姓名来查找他所属部门,部门姓名查找姓名:销售部张三张三销销售售部部人事部李四=VLOOKUP(E26,IF(1,0,C26:C30,B26:B30),2,0)资材部王五生产部陈六业务部刘七第一步:此种列表如果要用VLOOKUP函数来解决的话,首先我们会发现,VLOOKUP 函数搜索某个单元格区域（区域：工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）的第一列，然后返回该区域相同行上任何单
4、元格中的值,显然我们利用第二列来查找第一列根据VLOOKUP函数的特性直接查找是的行不通,这样就需要重新构造VLOOKUP函数的第二参数使姓名在第一列,部门在后面列来查找,这里我们就可以通过IF函数的数组特性来构造了;张三销售部李四人事部王五资材部陈六生产部刘七业务部=IF(1,0,C26:C30,B26:B30)这里我们利用IF函数的第一参数不等于0返回第二参数,第一参数为0返回第三参数,根椐数组的扩展性,构造成一个姓名在前,部门在后的五行两列的二维数组根据数组的基本原理,这里设置的第一参数会自动扩展成与第二参数,第三参数相同行数的二维数组,IF函数的第一参数可以驱动结果的方向,1,0扩展成
5、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函数的第一参数为
6、一个二行二列的数组,第二三参数分别为一列两行的数组,则第二参数自运扩展为一个二行二列的数组.第二参数扩展为第三参数扩展为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)1
7、41414=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)此例相对于来说也比较容易理解,第二参数与第一参对称,第二参数扩展为二列三行的数组.第二
8、参数本身对称第一参数第三参数扩展为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
9、;3,4;5,6;7,8,0,9,10)的的区区别别1212120000095656#N/A#N/A00#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函数的结果:纵纵向向大大小小是是由由任任意意参参数数的的最最大大纵纵向向数数组组决决定定,横横向向大大小小是是任任意意参参数数的的最最大大横横向向数数组组决决定定,当当参参数数不不是是单单个个元元素素时时而而且且数数组组行行或或列列不不对对称称时时是是不不能能自自动动扩扩
10、展展,当当无无对对应应值值时时会会产产生生错错误误。三三：N(IF(1,)或或T(IF(1,)结结构构N(IF(1,)或T(IF(1,)结构结构可以对某些不支持数组参数的参数数组化,返回内存数组;此种方法在技巧四中介绍过了,就不再多讲了,这里我们只能是理解这种方法能够实现这种功能,因为函数以我的水平是无法从理论上来解释某种现象,只只有有通通过过不不断断的的测测试试才才能能发发现现新新的的方方法法与与技技巧巧。此此贴贴由由chenhh803原原创创,转转贴贴请请先先征征求求本本人人同同意意忽略第三参数这种表达方式只是第二参数写逗号,第三参数忽略=IF(G11,1,)1=IF(F11,1)0=IF
11、(F12,)=IF(G12,1,)FALSE=IF(F12,1)FALSE=IF(F13,)=IF(G13,1,)1=IF(F13,1)0=IF(F14,)=IF(G14,1,)1=IF(F14,1)0=IF(F15,)=IF(G15,1,)1=IF(F15,1)0=IF(F16,)=IF(G16,1,)1=IF(F16,1)0=IF(F17,)=IF(G17,1,)FALSE=IF(F17,1)FALSE=IF(F18,)IF函数应用范围很广,在写这个函数之前,我犹豫了很久,这个函数如果想要把他完全写清楚是一件比较困难的事情,因为我们单独用IF的时候比交少,一般IF函数最基本的功能就是对条件
12、表达式进行判断,如果条件表达式为TRUE或不等于0则返回第二参数,否则返回第三参数;如果第二三参数只写逗号,则默认返回0;第三参数只写逗号IF函数的数组运用与技巧四的数组的基本原理是一样的,只是IF函数多了一个表达式来控制返回的结果,IF函数的三个参数都支持数组,所以在日常数组公式中运用非常之广.1.重构数组实现反向查找,IF(1,0,数组,数组),此种方法在VLOOKUP函数里应用最多,虽然我们可以应用其它办法来解决,但这种思路还是需要学习一下的;=VLOOKUP(E26,IF(1,0,C26:C30,B26:B30),2,0)第一步:此种列表如果要用VLOOKUP函数来解决的话,首先我们会
13、发现,VLOOKUP 函数搜索某个单元格区域（区域：工作表上的两个或多个单元格。区域中的单元格可以相邻或不相邻。）的第一列，然后返回该区域相同行上任何单元格中的值,显然我们利用第二列来查找第一列根据VLOOKUP函数的特性直接查找是的行不通,这样就需要重新构造VLOOKUP函数的第二参数使姓名在第一列,部门在后面列来查找,这里我们就可以通过IF函数的数组特性来构造了;这里我们利用IF函数的第一参数不等于0返回第二参数,第一参数为0返回第三参数,根椐数组的扩展性,构造成一个姓名在前,部门在后的五行两列的二维数组根据数组的基本原理,这里设置的第一参数会自动扩展成与第二参数,第三参数相同行数的二维数
14、组,IF函数的第一参数可以驱动结果的方向,第二步:取得上面的数组后我们就可以根据VLOOKUP函数的特性来取得结果了,我们重构数组后,第一列是姓名,第二列是部门,在第一列查找,返回第二列的值,IF函数的第一参数可以决定结果的方向,在此例中,IF函数的第一参数为一个二行二列的数组,第二三参数分别为一列两行的数组,则第二参数自运扩展为一个二行二列的数组.然后由IF函数的第一参数在两个数组中取数,如果为1则取第二参数扩展数组相对应的数值,如果为0则取第三参数扩展数组中相对应的值;此例与上例类似,第二三参数分别扩展为与第一参数一样的二行二列数组,然后再由第一参数在三个扩展数组中取值;此例第一参数为二列
15、三行的数组,第二参数与第三参数为二列一行的数组,这里第二参数与第三参数也会自动扩展,取数时,因为1在第一列,所以取第二参数扩展数组第一列的值1,0在第二列,取第三参数扩展数组第二列的值4;#N/A10#N/A#N/A此例第一参数为一列三行的数组,第二参数为二列三行的数组,第三参数为单个元素,第一参数的行数与第二参数对称,第三参数为单个元素,三个参数运算时分别为以下数组;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)的的区区别别=IF(1;0,1,2;3,4;5,6
16、;7,8,0,9,10)通过比较我们可以看到,IF函数的结果:纵纵向向大大小小是是由由任任意意参参数数的的最最大大纵纵向向数数组组决决定定,横横向向大大小小是是任任意意参参数数的的最最大大横横向向数数组组决决定定,当当参参数数不不是是单单个个元元素素时时而而且且N(IF(1,)或T(IF(1,)结构结构可以对某些不支持数组参数的参数数组化,返回内存数组;此种方法在技巧四中介绍过了,就不再多讲了,这里我们只能是理解这种方法能够实现这种功能,因为函数以我的水平是无法从理论上来解释某种现象,只只有有通通过过不不断断的的测测试试才才能能发发现现新新的的方方法法与与技技巧巧。技技巧巧解解析析一一：下表为
17、某公司客户订购清单,现需把他转为一列,客户名如果相同,只取一个,所订购的产品在客户名的下面.客户购买产品结果客户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,48,ROW($4:$10)*10+1,ROW($4:$10)*10+2),ROW(A1),r0c0),)&公公式式解解析析:第一步:去重取唯一客户名最小的的行列号,用IF函数判断客户名称是否相同,如果不相同则取其行号乘以10再加上其列号,否则取2003版中
18、的最大行号65536乘以10加上其列号,这里把行列号放在一起是为了最后的INDORECT函数取值时所用;=IF($A$4:$A$10=$A$3:$A$9,48,ROW($4:$10)*10+1IF函数的第一参数41客户1客户FALSE655361客户1客户1TRUE655361客户1客户1TRUE71客户2=客户1FALSE81客户3客户2FALSE655361客户3客户3TRUE101客户4客户3FALSE第二步:因为产品名称是要全部取出的,所以利用IF(1,0,)结构重新构造一个7行2列的数组,第一列为IF函数去重后唯一值的行列号,第二列为全部产品名称的行列号=IF(1,0,IF($A$4
19、:$A$10=$A$3:$A$9,48,ROW($4:$10)*10+1,ROW($4:$10)*10+2)414241655361526553616553616265536171727181828165536192655361101102101IF函数的结果第三步:利用SMALL函数依次取出构造好的列号,因为第二列的列号大于第一列的列号,所以每次取数时先取出客户名,再依次取出产品名称,414252627172818292101102655361655361=SMALL(IF(1,0,IF($A$4:$A$10=$A$3:$A$9,48,ROW($4:$10)*10+1,ROW($4:$10)
20、*10+2),ROW(1:13)第四步:用TEXT函数把取得的行列号转化成为R1C1样式,最后用INDIRECT函数取出结果R4C1R4C2R5C2R6C2R7C1R7C2R8C1R8C2R9C2R10C1R10C2R65536C1R65536C1=TEXT(SMALL(IF(1,0,IF($A$4:$A$10=$A$3:$A$9,48,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$
21、9,48,ROW($4:$10)*10+1,ROW($4:$10)*10+2),ROW(1:13),R0C0),)原公式采用下拉方式,这里为了方便查看,使用了T做为内存数组方式表示,技技巧巧解解析析二二：下表为某学校学生考试情况,为方便打印,为节约纸张、绿色环保,需按人次打印再分发给每位同学,现要重新排版，格式如结果。（表格数据只是一个模拟，如有错误，敬请见谅）姓名学号年级专业课程名称考试日期考试时间米米菜5036500842011 计算机多媒体技术基础2011/7/8 14:00-16:00米米菜5036500832011 计算机信息管理系统2011/7/12 14:00-16:00路上菜5
22、036500832011 计算机信息管理系统2011/7/12 14:00-16:00路上菜5036500822011 计算机多媒体技术基础2011/7/8 14:00-16:00路下菜5036500822011 计算机信息管理系统2011/7/9 14:00-16:00路下菜5036500822011 计算机网络数据库SQL2011/7/10 14:00-16:00路小菜5036500812011 计算机多媒体技术基础2011/7/8 14:00-16:00牛皮菜5036500812011 计算机信息管理系统2011/7/12 14:00-16:00牛皮菜5036500812011 计算机网
23、络数据库SQL2011/7/13 14:00-16:00牛皮菜5036500812011 计算机市场营销学2011/7/14 14:00-16:00 姓名学号年级专业课程名称考试日期考试时间米米菜5036500842011计算机多媒体技术基础2011/7/814:00-16:00米米菜5036500832011计算机信息管理系统2011/7/1214:00-16:00 姓名学号年级专业课程名称考试日期考试时间路上菜5036500832011计算机信息管理系统2011/7/1214:00-16:00路上菜5036500822011计算机多媒体技术基础2011/7/814:00-16:00 姓名学
24、号年级专业课程名称考试日期考试时间路下菜5036500822011计算机信息管理系统2011/7/914:00-16:00路下菜5036500822011计算机网络数据库SQL2011/7/1014:00-16:00 姓名学号年级专业课程名称考试日期考试时间路小菜5036500812011计算机多媒体技术基础2011/7/814:00-16:00 姓名学号年级专业课程名称考试日期考试时间牛皮菜5036500812011计算机信息管理系统2011/7/1214:00-16:00牛皮菜5036500812011计算机网络数据库SQL2011/7/1314:00-16:00牛皮菜5036500812
25、011计算机市场营销学2011/7/1414:00-16:00 =INDEX(A1:H103,RIGHT(SMALL(IF(1,1,0,IF(A93:A104A92:A103,IF(1,0,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行的数组,第一列数组为行号
26、加表头和行号,第二列数组为行号加空格行的行号92.12092FALSE92.1209293.1209293.10393.1209294.1209294.10394.1209295.1209295.10395.1209296.1209296.10396.1209297.1209297.10397.1209298.1209298.10398.1209299.1209299.10399.12092100.12092100.103100.12092101.12092101.103101.12092FALSE102.103FALSEFALSE103.103FALSE=IF(ROW(1:12)1,ROW(
27、92:103)+0.103)=IF(1,0,IF(ROW(1:12)1,ROW(92:103)+0.103)第二步:用数据源名字上下交错,如果考生姓名不同,则取一个表头行行号一一个空格行行号;92.12092FALSEFALSEFALSE94.1209294.103FALSEFALSE96.1209296.103FALSEFALSE98.1209298.10399.1209299.103FALSEFALSEFALSEFALSEFALSE102.103FALSE103.103=IF(A93:A104A92:A103,IF(1,0,IF(ROW(1:12)1,ROW(92:103)+0.103)第
28、三步:利用IF(1,1,0,)结构重构一个三列十二行的数组,把所有学生考试的行号也列进数组的第三列92.12092FALSE93FALSEFALSE9494.1209294.10395FALSEFALSE9696.1209296.10397FALSEFALSE9898.1209298.1039999.1209299.103100FALSEFALSE101FALSEFALSE102FALSE102.103103FALSE103.103104第四步:从上面数组可以看出,只要利用SMALL函数就可以为每个学生加上一个表头与空格了,再用RIGHT函数取得插入的表头与空格行的行号;92.12092092
29、9393949494.10310394.120920929595969696.10310396.120920929797989898.10310398.12092092999999.10310399.12092092100100101101102102102.103103SMALL=RIGHT(SMALL(IF(1,1,0,IF(A93:A104A92:A103,IF(1,0,IF(ROW(1:12)1,ROW(92:103)+0.103),ROW(93:104),ROW(1:20),3)第五步:用INDEX函数的数组功能,取得想要的数据姓名学号年级专业课程名称考试日期考试时间米米菜50365
30、00842011计算机多媒体技术基础4073214:00-16:00米米菜5036500832011计算机信息管理系统4073614:00-16:00 姓名学号年级专业课程名称考试日期考试时间路上菜5036500832011计算机信息管理系统4073614:00-16:00路上菜5036500822011计算机多媒体技术基础4073214:00-16:00 姓名学号年级专业课程名称考试日期考试时间路下菜5036500822011计算机信息管理系统4073314:00-16:00路下菜5036500822011计算机网络数据库SQL4073414:00-16:00 姓名学号年级专业课程名称考试日
31、期考试时间路小菜5036500812011计算机多媒体技术基础4073214:00-16:00 姓名学号年级专业课程名称考试日期考试时间牛皮菜5036500812011计算机信息管理系统4073614:00-16:00牛皮菜5036500812011计算机网络数据库SQL4073714:00-16:00牛皮菜5036500812011计算机市场营销学4073814:00-16:00 第二参数第三参数IF函数结果乘10加列号165536444165536565536#65536665536*10+1#65536777165536888165536965536#655361010101单个元素扩展
32、与第一参数对称第一参数为TRUE取第二参数,否则取第三参数416553616553617181655361101全部购买产品的行列号=INDIRECT(TEXT(SMALL(IF(1,0,IF($A$4:$A$10=$A$3:$A$9,48,ROW($4:$10)*10+1,ROW($4:$10)*10+2),ROW(A1),r0c0),)&第一步:去重取唯一客户名最小的的行列号,用IF函数判断客户名称是否相同,如果不相同则取其行号乘以10再加上其列号,否则取2003版中的最大行号65536乘以10加上其列号,这里把行列号放在一起是第二步:因为产品名称是要全部取出的,所以利用IF(1,0,)结
33、构重新构造一个7行2列的数组,第一列为IF函数去重后唯一值的行列号,第二列为全部产品名称的行列号第三步:利用SMALL函数依次取出构造好的列号,因为第二列的列号大于第一列的列号,所以每次取数时先取出客户名,再依次取出产品名称,=SMALL(IF(1,0,IF($A$4:$A$10=$A$3:$A$9,48,ROW($4:$10)*10+1,ROW($4:$10)*10+2),ROW(1:13)考场1205120912091205120912101205120912101211 考场12051209 考场12091205 考场1209=TEXT(SMALL(IF(1,0,IF($A$4:$A$1
34、0=$A$3:$A$9,48,ROW($4:$10)*10+1,ROW($4:$10)*10+2),ROW(1:13),R0C0)=T(INDIRECT(TEXT(SMALL(IF(1,0,IF($A$4:$A$10=$A$3:$A$9,48,ROW($4:$10)*10+1,ROW($4:$10)*10+2),ROW(1:13),R0C0),)下表为某学校学生考试情况,为方便打印,为节约纸张、绿色环保,需按人次打印再分发给每位同学,现要重新排版，格式如结果。（表格数据只是一个模拟，如有错误，敬请见谅）1210 考场1205 考场120912101211 FALSE93.10394.10395
35、.10396.10397.10398.10399.103100.103101.103102.103103.103=INDEX(A1:H103,RIGHT(SMALL(IF(1,1,0,IF(A93:A104A92:A103,IF(1,0,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行的数组,第一列
36、数组为行号加表头和行号,第二列数组为行号加空格行的行号=IF(1,0,IF(ROW(1:12)1,ROW(92:103)+0.103)=IF(A93:A104A92:A103,IF(1,0,IF(ROW(1:12)1,ROW(92:103)+0.103)考场12051209 考场12091205 考场12091210 考场1205 考场120912101211 第四步:从上面数组可以看出,只要利用SMALL函数就可以为每个学生加上一个表头与空格了,再用RIGHT函数取得插入的表头与空格行的行号;=RIGHT(SMALL(IF(1,1,0,IF(A93:A104A92:A103,IF(1,0,IF(ROW(1:12)1,ROW(92:103)+0.103),ROW(93:104),ROW(1:20),3)