如何实现函数IF的嵌套超过七层?

arrowlking · 发表于 2006-11-3 21:39

有一判断语句

if(search("X",A3)>0,sum(*****),0),如果在A3格里含有X字符，公式没有问题，但如在A3格里不包含X,会显示#VALUE！

怎么不显示0？请帮忙！

liaurnHOME · 发表于 2007-1-15 23:57

用定义名称，结合choose（）函数实现的29层判断:

定义名称：pd1=if(条件1，1，30）、pd2=if(条件2，2，30）...pd28=if(条件28，28，30）最后一个同时不满足以上条件：pd29=if(not(and(条件1，条件2，...条件28)),29,30).

=choose(min(pd1,pd2,...pd29),1对应的目的，2对应的目的...29对应的目的）

适合很复杂的逻辑判断的，只要你的条件不乱，结果一定正确。

这里的数字30可以用大于29的任意数字，只要你不嫌麻烦的话。

头一次发贴，别扔鸡蛋

liaurnHOME · 发表于 2007-1-16 00:12

改进一下：

最后一个同时不满足条件的名称改为：

PD29=IF(AND(PD1=30,PD2=30,...PD28=30),29,100)

CHOOSE函数用法同上，

这个实用于大部分为非数字的逻辑判断，我用过最多的是9层。

远游 · 发表于 2007-2-6 13:42

QUOTE:

以下是引用半榻茶烟在2002-10-21 22:40:00的发言：
=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",""))))))))&
IF(A1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF(A1=21,"U",IF(A1=22,"V",IF(A1=23,"W",IF(A1=24,"X",""))))))))&IF(A1=25,"Y",IF(A1=26,"Z",""))

有了这个计算方法让我完成了一个复杂表格的全部自动生成。非常感谢！

YANJUNCHEN95 · 发表于 2007-2-6 22:14

QUOTE:

以下是引用半榻茶烟在2002-10-21 22:40:00的发言：
=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",""))))))))&
IF(A1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF(A1=21,"U",IF(A1=22,"V",IF(A1=23,"W",IF(A1=24,"X",""))))))))&IF(A1=25,"Y",IF(A1=26,"Z",""))

QUOTE:

这个方法真是不错呢。不经意的又学了一招，看来函数也要灵活应用才行呢

sll19820129 · 发表于 2007-4-25 10:10

QUOTE:

以下是引用半榻茶烟在2002-10-21 22:40:00的发言：
=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",IF(A1=11,"K",IF(A1=12,"L",IF(A1=13,"M",IF(A1=14,"N",IF(A1=15,"O",IF(A1=16,"P",""))))))))&
IF(A1=17,"Q",IF(A1=18,"R",IF(A1=19,"S",IF(A1=20,"T",IF(A1=21,"U",IF(A1=22,"V",IF(A1=23,"W",IF(A1=24,"X",""))))))))&IF(A1=25,"Y",IF(A1=26,"Z",""))

我在实际用这个方法做的时候，发现当输入到一定时候，会提示输入的过多，就无法再输入进去了。那怎么办泥

hnclxkx · 发表于 2007-4-27 12:18

领教了,我也遇到过这个问题,多谢

magicmaggot · 发表于 2007-4-28 15:16

很多年以前就被7层嵌套给难住了，不过使用了其他的数学方法解决了，记得为了解决这个问题费了好长时间，看完各位高手的表述，实在受益匪浅呀！

timesmedia · 发表于 2007-4-28 15:38

用EXCEL2007就可以了

curieju · 发表于 2007-8-23 15:48

如果是数字呢？怎么改呀。我用这个方法只能选择是文字的

		自动登录	找回密码
密码			免费注册