[分享]：解除函数超过7层限制的方法

h_shj2006

解除函数超过7层限制的方法
EXCEL中一个众所周知的限制是你不能嵌套超过7层函数.例如下面的公式是错误的,因为限制被超过.
=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,
IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,
IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,IF($A$4=7,77,FALSE))))))))
通常的方法,如果你有一个公式超过这个限制,你会考虑用VBA代替.然而,如果你不想使用VBA,你可以通过对公式的一部分”定义名称”来解决这种限制.
我们利用一个IF嵌套公式来测试
IF A4 = 1 Then 11
Else If  A4 = 2 Then 22
Else If  A4 = 3 Then 33
Else If  A4 = 4 Then 44
...
Else If  A4 = 13 Then 130  Else "Not Found"

当然在实践应用中,我们最好利用VLOOKUP这个函数去实现结果,但这里我们的目的是做个演示.首先,我们定义一个名叫”ONE TO SIX”的名称, 里面包括公式:
=IF(Sheet1!$A$4=1,11,IF(Sheet1!$A$4=2,22,IF(Sheet1!$A$4=3,33,
IF(Sheet1!$A$4=4,44,IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=4,44,
IF(Sheet1!$A$4=5,55,IF(Sheet1!$A$4=6,66,FALSE))))))))

接着,再定义另一个名叫”SEVERTOTHIRTEEN”的名称,里面包括公式:
=IF(Sheet1!$A$4=7,77,IF(Sheet1!$A$4=8,88,IF(Sheet1!$A$4=9,99,
IF(Sheet1!$A$4=10,100,IF(Sheet1!$A$4=11,110,IF(Sheet1!$A$4=12,120,
IF(Sheet1!$A$4=13,130,"NotFound")))))))

最后,在B4单元格中输入下面的公式:
=IF(OneToSix,OneToSix,SevenToThirteen)

这样就解决了被嵌套函数的限制.因为公式中没有一个单独的部分超过限制,即使是”各个组成部分的集合”也没有超过限制.

当你编制一个超过限制的嵌套函数时可以使用这种方法.

xuehuchen

可以看看我得例子，=IF(G10=10,9.5,IF(G10=15,15.75,IF(G10=20,21.25,IF(G10=25,27,IF(G10=32,35.75,IF(G10=40,41,IF(G10=50,53,IF(G10=65,66,""))))))))&IF(G10=80,80.5,IF(G10=100,106,IF(G10=125,131,IF(G10=150,156,""))))

gouweicao78

IF的7层嵌套突破只是个例子——关键并不在于突破IF，而在于——遇到问题知道是怎么回事：
比如你一个公式里即有IF又有Index还有small……
=if(row(1:1)>……,"",index(A:A,small(if(……等等公式，如果嵌套了很多函数，当某个参数右边或左边的括号达到9个时，意味着超过7层嵌套的限制，必须要定义名称。

whd1688

没看明白！

zhongsu916

没看明白啊

buckaroo

我不会用VBA我以前都是 用几个单元格来分公式嵌套,最后归总,看到 2位的方法,真的是茅塞顿开,谢谢了..学习了2种方法.

cablecable

看不明白，不懂呀