Excel在公式计算方面有其自身的标准与规范,这些规范对公式的编写有一定的限制,主要包括以下几个方面。

  1. 公式内容的长度不能超过1024个字符。
  2. 公式中函数的嵌套不能超过7层。
  3. 公式中函数的参数不能超过30个。

正是因为上面的这些限制条件,使得用户在操作Excel的过程中存在诸多问题。下面以具体实例来详细讲解如何突破函数的7层嵌套以及30个函数参数的限制。

当函数B在函数A中用作参数的时候,函数B为第2级函数。如公式“=IF(A1>0,SUM(B1,G1),"")”,其中SUM函数是第2级函数,因为它是IF函数的参数。如果在SUM函数中继续嵌套函数则为第3级函数,以此类推,Excel函数公式可以包含多达7级的嵌套函数。函数的7层嵌套限制了使用多个函数,要解决这个问题,可以通过定义名称的方法来实现。

例如,要将A1中字符串“我113爱322学43习75E56x5353c256382e85626l54”的数字去掉,利用SUBSTITUTE函数来解决,在B1中输入公式“=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,),1,),2,),3,),4,),5,),6,),7,)”,之后就不能直接再套用函数了,因为从第2个SUBSTITUTE开始,每一个都是前一个的参数,已经达到了7级嵌套。此时,切换到“公式”选项卡,然后单击“定义名称”按钮,打开“新建名称”对话框,将上面的名称定义为X,然后在B1中输入“=SUBSTITUTE(SUBSTITUTE(X,8,),9,)”,这样就可以得到去掉数字的字符串“我爱学习Excel了!”。同样,利用名称定义的方法可解决用IF判断,以及其他函数因为嵌套层数超过7层而导致公式无法输入的问题。

上面介绍了如何突破函数的7层嵌套限制,在实际应用过程中,用户还会受到Excel中30个函数参数的限制,下面简单介绍如何突破30个函数参数的限制。

Excel规定的函数参数最多为30个,如SUM函数、COUNT函数、COUNTA函数、AVERAGE函数、CHOOSE函数等。例如需要计算某些特定单元格中数值的平均值,有以下公式“=AVERAGE(A1,A2,B1,B3,B6,C6,D8,...)”,其中,括号里面的参数多于30个。这个时候,可以在函数参数的两边加上一对括号,形成联合区域来作为参数,相当于只有1个参数。这样,公式就只受到字符个数的限制了。