ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 22346|回复: 36

用数组公式解决 IF() 函数 7 层嵌套问题

[复制链接]

TA的精华主题

TA的得分主题

发表于 2002-5-21 01:17 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
Excel 中的函数嵌套最多可有 7 层。 对于 IF(),有些时候可以配合AND(),OR()来解决多层次问题。 不过有些时候用它们是解决不了的,这就需要我们考虑其它的方法。 最开始,我还以为只有 IF() 函数有 7 层嵌套问题,后来用想用 CHOOSE() 替换 IF() 时,才发现 CHOOSE() 也不能做大于 7 层的嵌套。 最后得出:Excel 中可能所有函数都不能超过7层嵌套。 ------------------------ 对 IF() 7 层嵌套的解决: ------------------------ 先看看 IF() 函数的语法: IF(logical_test,value_if_true,value_if_false), 这里要研究的是 logical_test ,它只能返回 TRUE 或 FALSE , 而 Excel 中 TRUE 值为1, FALSE 值为0,不过要想体现出 1 和 0 , 必须对 TRUE 和 FALSE 作加 0 或 乘 1 之类的操作(也就是说把类型转换一下)。 再一点,也是非常关键的一点是: 对于IF()结构来说,只要遇到 logical_test 为 TRUE(按照条件的顺序) ,则结束判断。 原理清楚了之后,就开始实际操作了: 首先,把条件按顺序陈列出来。 比如:A1<-50,-50<=A1<0,A1=0,0=-50)*(A1<0),...,IF(A1=0,...,IF((A1>0)*(A1<50),...,...)))) 说明:这里的 * 含义为 AND , 之所以用它,是因为将用到数组公式。 写到这里就差不多了,最后要做的就是:使条件和结果对应起来。 根据刚才说的,只要遇到条件为 TRUE(按照条件的顺序) ,则结束判断。 所以我们要找到第一个满足条件的位置。 先给个例子: {=MIN(IF({TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,TRUE}+0={1,1,1,1,1,1,1,1,1},{1,2,3,4,5,6,7,8,9}))} 这里的{TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,TRUE}即为条件,共有九个; {1,1,1,1,1,1,1,1,1}相当于一个全是 TRUE 的数组(这是为了方便,完全可以用TRUE替换); {1,2,3,4,5,6,7,8,9}是满足条件对应的操作(这里只是简单的数值)。 MIN()目的是为了找到第一个满足条件的位置。 这里有个问题,就是如果条件都为 FALSE 时,则 MIN() 返回0, 这是因为没有与全不满足条件对应的项, 如果再加个判断条件,就显得冗余了, 简单的办法是对单元个设置 自定义数字格式 , 对 0 位置设置格式即可。 比如:...;...;"数据未找到" 上面的例子是一个标准的IF()结构,如果想让条件为 FALSE 时,执行相应的操作,只要把{1,1,1,1,1,1,1,1,1}中的1换成0即可。

TA的精华主题

TA的得分主题

发表于 2002-9-5 21:59 | 显示全部楼层

to:leaf

妙极! 可是我还是不太明白min()为什么会起作用? 还有,你所说的if()进行条件判断的数组是不是只可以对单个单元格进行判断?如果我想把每一个判断条件都设置成对数组判断可以吗? 就像 =if({A1:A100=0,A1:A100=2……

TA的精华主题

TA的得分主题

发表于 2006-6-9 11:54 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
高手強作

TA的精华主题

TA的得分主题

发表于 2006-6-9 16:15 | 显示全部楼层
經 leaf 大師講解,概念更清楚,謝謝 !!!

TA的精华主题

TA的得分主题

发表于 2006-7-7 16:17 | 显示全部楼层

公式可以简化为:

{=MIN(IF({TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,TRUE}=TRUE,{1,2,3,4,5,6,7,8,9}))}

用Min()可以解决7层的限制,但该思路并不能很好地运用于实际,公式中的If()条件是个常量数组,如果把实际中的诸多条件转化为一个由元素TRUE、FALSE组成的数组,其过程远比公式本身复杂,不如用Lookup()、Match()等函数解决。

TA的精华主题

TA的得分主题

发表于 2006-7-7 16:25 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
厉害,学习学习

TA的精华主题

TA的得分主题

发表于 2006-7-7 16:25 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
厉害,学习

TA的精华主题

TA的得分主题

发表于 2006-7-7 17:13 | 显示全部楼层

=MIN(IF({(a1<100),(a1>=100)*(a1<200)}=TRUE,{1,2}))

这样写为什么产生错误呢,请指点。

谢谢

[此贴子已经被作者于2006-7-7 17:31:15编辑过]

TA的精华主题

TA的得分主题

发表于 2006-7-7 18:07 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

从楼主的题意看:{TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,TRUE}内的逻辑值应该来源于表达式,但是手工输入含有表达式的数组公式系统接受吗?

举例:如果A1在1--10之间结果为a;在11--20之间结果为b;在21--30之间结果为c。现在A1中输入13,结果是什么?

解:=IF({(A1<=10)*1,(A1>10)*(A1<=20),(A1>20)*(A1<=30)}={1,1,1},{"a","b","c"})

这个公式系统不承认啊。如果承认结果应该是{FALSE,"b",FALSE}。

再举个简单的例子:={3,4,9}和={1+2,2+2,3+3}都能被系统认可吗?显然后者不行。

TA的精华主题

TA的得分主题

发表于 2006-7-7 20:09 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
在公式中使用数组{},其元素只能是常数,不能是表达式。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 01:06 , Processed in 0.061134 second(s), 9 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表