关于EXCEL中IF 的多重嵌套问题

Question

如题所述

Answer 1

1. 将七层之外的IF语句，放在另外的单元格内来处理例：C5=if(if,...,(if...),B5))),B5单元格就是存放七层之外的IF语句。依此类推，可以实现在数据库语言中CASE语句的功能。2. IF 函数的确有七层嵌套的限制。遇到七层嵌套还解决不了的问题，可以尝试用其它的函数组合和数组公式来解决；有时用 VBA 方案可以有很好的效果。这里给出一个解决IF函数嵌套超出范围的方法，可能比较容易使初学者看懂。其思路是：一个单元格做不了的事，分给两个或更多的单元格来做，文字内容是这样，函数内容也是这样。例子：假如 A1＝1，则 B1＝A；A1＝2，则 B1＝B …… A1＝26，则 B1＝Z解决方法如下：B1 = 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",C1))))))))C1 = 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",D1))))))))D1 = 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",E1))))))))E1 = IF(A1=25,"Y",IF(A1=26,"Z","超出范围"))根据情况，可以将 C、D、E 这些从事辅助运算的单元格放在其它任何地方3. 一个单元格也可以实现=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",""))(数组形式输入)。4. 以一例：a1=1,2,3,4,5,6,7,8,9,10B、可以用自定义数字格式。也可以用=CHOOSE(A1+1,"一二三四五六七八九十")答：新建一表，取名Data，找一区域设置名称为SubjectTable：语文Chinese=vlookup(SubjectTable,a1,2,false)可以有65536个，够了吧。其实，稍加改进，理论上，可以有达到你硬盘空间的个数。或用if和or的组合可以解决15个。再举个例子：

Answer 2

你的问题可以做个二维表，第一行表头是K5的范围，第一列的是K6的范围，用index函数较简单了本回答被网友采纳