鲁达
你理解了吗？
解决 IF() 7 层嵌套终极方案!
思路
按照条件优先顺序返回满足条件时的位置。比如 <100, <1000, <10000 三个条件，如果 <100 满足，则返回 1； 如果 <100 不满足，<1000 满足，则返回 2；如果 <100 和 <1000 均不满足，但满足 <10000，则返回 3。多个条件的情况同理。
这样做的好处是可以利用 CHOOSE 函数返回满足各种条件情况下的值。
解决办法
1) 将各个条件的值转换为整型后连接在一起。比如 500 对于上面的三个条件比较后的结果为 FALSE, TRUE, TRUE，转换为整型后为 0, 1, 1，连接起来后为 011
2) 用 FIND() 函数查找第一个 1 的位置。FIND("1", "011")，结果为 2，即第二个条件满足。
3) 用 CHOOSE 函数返回满足条件的值。CHOOSE(FIND("1", "011"), 11, 22, 33)，结果为 22。
18 个条件的示例
=CHOOSE(FIND("1",(A1<=1)*1 & (A1<=2)*1 & (A1<=3)*1 & (A1<=4)*1 & (A1<=5)*1 & (A1<=6)*1 & (A1<=7)*1 & (A1<=8)*1 & (A1<=9)*1 & (A1<=10)*1 & (A1<=11)*1 & (A1<=12)*1 & (A1<=13)*1 & (A1<=14)*1 & (A1<=15)*1 & (A1<=16)*1 & (A1<=17)*1 & (A1>17)*1),11,22,33,44,55,66,77,88,99,"AA","BB","CC","DD","EE","FF","GG","HH",0)
条件个数的限制
条件个数受以下条件的限制
1) 单元格内容最多 32767 个字符
2) CHOOSE 函数最多可以有 29 的 Value 参数，就是说最多 29 个条件。
但 CHOOSE 函数也是可以嵌套的，所以完全可以突破 29 个条件。
38 个条件的示例
=CHOOSE(FIND("1",(A1<=1)*1 & (A1<=2)*1 & (A1<=3)*1 & (A1<=4)*1 & (A1<=5)*1 & (A1<=6)*1 & (A1<=7)*1 & (A1<=8)*1 & (A1<=9)*1 & (A1<=10)*1 & (A1<=11)*1 & (A1<=12)*1 & (A1<=13)*1 & (A1<=14)*1 & (A1<=15)*1 & (A1<=16)*1 & (A1<=17)*1 & (A1>17)*1),11,22,33,44,55,66,77,88,99,"AA","BB","CC","DD","EE","FF","GG","HH",CHOOSE(FIND("1",(A1<=18)*1 & (A1<=19)*1 & (A1<=20)*1 & (A1<=21)*1 & (A1<=22)*1 & (A1<=23)*1 & (A1<=24)*1 & (A1<=25)*1 & (A1<=26)*1 & (A1<=27)*1 & (A1<=28)*1 & (A1<=29)*1 & (A1<=30)*1 & (A1<=31)*1 & (A1<=32)*1 & (A1<=33)*1 & (A1<=34)*1 & (A1<=35)*1 & (A1<=36)*1 & (A1<=37)*1 & (A1>37)*1),1818,1919,2020,2121,2222,2323,2424,2525,2626,2727,2828,2929,3030,3131,3232, 3333,3434,3535,3636,3737,0))
这个发现很有价值，值得关注与进一步探讨。但结果目前而言，还没多大作用：
=IF({1},ROW(1:6),COLUMN(A1:C1))
该公式结果相当于：
=ROW(1:6)+COLUMN(A:C)*0
另一个公式结果相当于：
=ROW(1:6)*0+COLUMN(A:C)
又试了一下,得出如下结论:
IF({1},ROW(1:6),COLUMN(A1:C1))等价于IF({1,1,1},ROW(1:6),COLUMN(A1:C1))
IF({1,1,1,1},ROW(1:6),COLUMN(A1:C1))等价于IF({1,1,1,1},ROW(1:6),COLUMN(A1:D1))
觉得这一用法也只是数组运算时个数的自动匹配问题。