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

EXCEL函函数数常常用用技技巧巧浅浅析析 五五 技技巧巧五五 IF函函数数浅浅析析 IF函数应用范围很广 在写这个函数之前 我犹豫了很久 这个函数如果想要把他完全写清楚是一件比较困难的事情 因为我们单独用IF的时候比交少 一般 配合其它函数使用较多 这里我先把我所想到的先写下来 欢迎各位前辈高人指点 一一 IF函函数数的的基基本本功功能能 IF函数最基本的功能就是对条件表达式进行判断 如果条件表达式为TRUE或不等于0则返回第二参数 否则返回第三参数 如果第二三参数只写逗号 则默认返回0 如果忽略第三参数 则返回FALSE 注意 第二参数是忽略不了的 完整参数第二参数只写逗号第三参数只写逗号 TRUE1 IF B10 1 2 TRUE0 IF G11 2 1 FALSE2 IF B11 1 2 FALSE2 IF G12 2 0 11 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 1 0 11 IF B15 1 2 0 10 IF G16 2 1 02 IF B16 1 2 02 IF G17 2 0 二二 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 扩展成 10 10 10 10 10 然后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 131 242 IF 1 0 1 0 1 2 3 4 IF函数的第一参数可以决定结果的方向 在此例中 IF函数的第一参数为一个二行二列的数组 第二三参数分别为一列两行的数组 则第二参数自运扩展为一个二行二列的数组 第二参数扩展为第三参数扩展为 1133 2244 然后由IF函数的第一参数在两个数组中取数 如果为1则取第二参数扩展数组相对应的数值 如果为0则取第三参数扩展数组中相对应的值 13 24 IF 1 0 1 0 1 2 3 4 2 2 IF 1 0 1 0 1 2 12 12 IF 1 0 1 0 1 2 此例与上例类似 第二三参数分别扩展为与第一参数一样的二行二列数组 然后再由第一参数在三个扩展数组中取值 第二参数扩展为第三参数扩展为 1122 1122 取值同上 2 3 IF 1 0 1 0 1 0 1 2 3 4 14 14 14 IF 1 0 1 0 1 0 1 2 3 4 此例第一参数为二列三行的数组 第二参数与第三参数为二列一行的数组 这里第二参数与第三参数也会自动扩展 第二参数扩展为第三参数扩展为 1234 1234 1234 取数时 因为1在第一列 所以取第二参数扩展数组第一列的值1 0在第二列 取第三参数扩展数组第二列的值4 14 14 14 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 10 30 50 IF 1 0 1 0 1 0 1 2 3 4 5 6 0 此例相对于来说也比较容易理解 第二参数与第一参对称 第二参数扩展为二列三行的数组 第二参数本身对称第一参数第三参数扩展为 1200 3400 5600 取值方法同上例 2 5 IF 1 0 0 1 2 3 4 5 6 0 12 00 00 IF 1 0 0 1 2 3 4 5 6 0 此例第一参数为一列三行的数组 第二参数为二列三行的数组 第三参数为单个元素 第一参数的行数与第二参数对称 第三参数为单个元素 三个参数运算时分别为以下数组 第一参数行数与第二参数对称第二参数不变第三参数扩展为 111200 003400 005600 再根据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 000009 5656 N A N A 00 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原原创创 转转贴贴请请先先征征求求本本人人同同意意 忽略第三参数 这种表达方式只是第二参 数写逗号 第三参数忽略 IF G11 1 1 IF F11 1 0 IF 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 EXCEL函函数数常常用用技技巧巧浅浅析析 五五 IF函数应用范围很广 在写这个函数之前 我犹豫了很久 这个函数如果想要把他完全写清楚是一件比较困难的事情 因为我们单独用IF的时候比交少 一般 IF函数最基本的功能就是对条件表达式进行判断 如果条件表达式为TRUE或不等于0则返回第二参数 否则返回第三参数 如果第二三参数只写逗号 则默认返回0 第三参数只写逗号 IF函数的数组运用与技巧四的数组的基本原理是一样的 只是IF函数多了一个表达式来控制返回的结果 IF函数的三个参数都支持数组 所以在日常数组公式中运用非常之广 1 重构数组实现反向查找 IF 1 0 数组 数组 此种方法在VLOOKUP函数里应用最多 虽然我们可以应用其它办法来解决 但这种思路还是需要学习一下的 第一步 此种列表如果要用VLOOKUP函数来解决的话 首先我们会发现 VLOOKUP 函数搜索某个单元格区域 区域 工作表上的两个或多个单元格 区域中的 单元格可以相邻或不相邻 的第一列 然后返回该区域相同行上任何单元格中的值 显然我们利用第二列来查找第一列根据VLOOKUP函数的特性直接查找是的 行不通 这样就需要重新构造VLOOKUP函数的第二参数使姓名在第一列 部门在后面列来查找 这里我们就可以通过IF函数的数组特性来构造了 这里我们利用IF函数的第一参数不等于0返回第二参数 第一参数为0返回第三参数 根椐数组的扩展性 构造成一个姓名在前 部门在后的五行两列的二维数组 根据数组的基本原理 这里设置的第一参数会自动扩展成与第二参数 第三参数相同行数的二维数组 IF函数的第一参数可以驱动结果的方向 第二步 取得上面的数组后我们就可以根据VLOOKUP函数的特性来取得结果了 我们重构数组后 第一列是姓名 第二列是部门 在第一列查找 返回第二列的值 IF函数的第一参数可以决定结果的方向 在此例中 IF函数的第一参数为一个二行二列的数组 第二三参数分别为一列两行的数组 则第二参数自运扩展为一个二行二列的数组 然后由IF函数的第一参数在两个数组中取数 如果为1则取第二参数扩展数组相对应的数值 如果为0则取第三参数扩展数组中相对应的值 此例与上例类似 第二三参数分别扩展为与第一参数一样的二行二列数组 然后再由第一参数在三个扩展数组中取值 此例第一参数为二列三行的数组 第二参数与第三参数为二列一行的数组 这里第二参数与第三参数也会自动扩展 取数时 因为1在第一列 所以取第二参数扩展数组第一列的值1 0在第二列 取第三参数扩展数组第二列的值4 N A 10 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 7 8 0 9 10 通过比较我们可以看到 IF函数的结果 纵纵向向大大小小是是由由任任意意参参数数的的最最大大纵纵向向数数组组决决定定 横横向向大大小小是是任任意意参参数数的的最最大大横横向向数数组组决决定定 当当参参数数不不是是单单个个元元素素时时而而且且 N IF 1 或T IF 1 结构结构可以对某些不支持数组参数的参数数组化 返回内存数组 此种方法在技巧四中介绍过了 就不再多讲了 这里我们只能是理解这种方法能够实现这种功能 技技巧巧解解析析一一 下表为某公司客户订购清单 现需把他转为一列 客户名如果相同 只取一个 所订购的产品在客户名的下面 客户购买产品结果 客户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 A 4 A 10 A 3 A 9 4 8 ROW 4 10 10 1IF函数的第一参数 41客户1客户FALSE 655361客户1客户1TRUE 655361客户1客户1TRUE 71客户2 客户1FALSE 81客户3客户2FALSE 655361客户3客户3TRUE 101客户4客户3FALSE 第二步 因为产品名称是要全部取出的 所以利用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 414241 65536152655361 65536162655361 717271 818281 65536192655361 101102101 IF函数的结果 第三步 利用SMALL函数依次取出构造好的列号 因为第二列的列号大于第一列的列号 所以每次取数时先取出客户名 再依次取出产品名称 41 42 52 62 71 72 81 82 92 101 102 655361 655361 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函数取出结果 R4C1 R4C2 R5C2 R6C2 R7C1 R7C2 R8C1 R8C2 R9C2 R10C1 R10C2 R65536C1 R65536C1 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做为内存数组方式表示 技技巧巧解解析析二二 下表为某学校学生考试情况 为方便打印 为节约纸张 绿色环保 需按人次打印再分发给每位同学 现要重新排版 格式如结果 表格数据只是一个模拟 如有错误 敬请见谅 姓名学号年级专业课程名称考试日期考试时间 米米菜5036500842011 计算机多媒体技术基础2011 7 8 14 00 16 00 米米菜5036500832011 计算机信息管理系统2011 7 12 14 00 16 00 路上菜5036500832011 计算机信息管理系统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 计算机网络数据库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 姓名学号年级专业课程名称考试日期考试时间 路下菜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 牛皮菜5036500812011计算机市场营销学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行的数组 第一列数组为行号加表头和行号 第二列数组为行号加空格行的行号 92 12092FALSE92 12092 93 1209293 10393 12092 94 1209294 10394 12092 95 1209295 10395 12092 96 1209296 10396 12092 97 1209297 10397 12092 98 1209298 10398 12092 99 1209299 10399 12092 100 12092100 103100 12092 101 12092101 103101 12092 FALSE102 103FALSE FALSE103 103FALSE IF ROW 1 12 1 ROW 92 103 0 103 IF 1 0 IF ROW 1 12 1 ROW 92 103 0 103 第二步 用数据源名字上下交错 如果考生姓名不同 则取一个表头行行号一一个空格行行号 92 12092FALSE FALSEFALSE 94 1209294 103 FALSEFALSE 96 1209296 103 FALSEFALSE 98 1209298 103 99 1209299 103 FALSEFALSE FALSEFALSE FALSE102 103 FALSE103 103 IF A93 A104A92 A103 IF 1 0 IF ROW 1 12 1 ROW 92 103 0 103 第三步 利用IF 1 1 0 结构重构一个三列十二行的数组 把所有学生考试的行号也列进数组的第三列 92 12092FALSE93 FALSEFALSE94 94 1209294 10395 FALSEFALSE96 96 1209296 10397 FALSEFALSE98 98 1209298 10399 99 1209299 103100 FALSEFALSE101 FALSEFALSE102 FALSE102 103103 FALSE103 103104 第四步 从上面数组可以看出 只要利用SMALL函数就可以为每个学生加上一个表头与空格了 再用RIGHT函数取得插入的表头与空格行的行号 92 12092092 9393 9494 94 103103 94 12092092 9595 9696 96 103103 96 12092092 9797 9898 98 103103 98 12092092 9999 99 103103 99 12092092 100100 101101 102102 102 103103 SMALL 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