在用Excel表格计算工程量时,经常需要一个能将计算式自动出结果的公式,今天就详细说说这个公式。(此文约2700字,还附不少图片,请耐心阅读,必定会有所收获)
计算表达式直接计算出结果
上图中可以把计算式转换为计算结果的公式,如下:
=IFERROR(EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(OFFSET(INDIRECT(“A2”),ROW()-2,COLUMN()),”【”,”ISTEXT(“”“),”】”,”“”)*“),”[“,”(“),”]”,”)”)),””) |
这是一个比较长的函数嵌套组合公式,公式中一共用了8个函数:IFERROR、EVALUATE、SUBSUIUTUE、OFFSET、INDIRECT、ROW、COLUMN、INTEXT,下面依次做解释和说明:
一、提取计算式:OFFSET、INDIRECT、ROW、COLUMN
这几个函数需要放在一起讲解,因为公式中的〖OFFSET(INDIRECT(“A2”),ROW()-2,COLUMN())〗是一个起独立作用的部分,其目的是获取计算式所在单元格的位置,从而得到单元格中的计算公式。
1、OFFSET函数:偏移函数。以某个单元格为起点,向下偏移一定的行数 + 向右偏移一定的列数,然后得到的一个新的单元格位置。
公式所在的单元格=OFFSET(参照单元格,向下偏移的行数,向右偏移的列数)=OFFSET(INDIRECT(“A2”),ROW()-2 ,COLUMN());
2、INDIRECT函数:引用函数。引用当前工作表的某单元格,这样可以实现所有工作表通用一个公式。
参照单元格=INDIRECT(“A2”),即公式所在的工作表中的〖A2〗单元格;
3、ROW函数、COLUMN函数:行号值、列号值函数,用于获取公式所在单元格的行号和列号。
向下偏移的行数=ROW()-2,即公式所在行的行号-2;
向右偏移的列数=COLUMN(),即公式所在列的列号。
4、注意事项:
计算式列和计算结果列有两种相对关系:①计算结果在计算式的左边:②计算结果在计算式的右边:
①计算结果在计算式的左边:
此时,获取当前行计算公式所在单元格的公式为:OFFSET(INDIRECT(“A2”),ROW()-2,COLUMN()),其原理和详细说明,见下图。
②计算结果在计算式的右边:
此时,获取当前行计算公式所在单元格的公式为:OFFSET(INDIRECT(“A2”),ROW()-2,COLUMN()-2),其原理和详细说明,见下图。
二、处理计算式:SUBSTITUTE、ISTEXT
由于在编制计算式时,会对数据做注释,以便今后理解计算式中的数据。此部分内容,主要讲解如何处理计算式中的文本注释和其他符号,以实现让计算式能计算出正确的结果。
1、ISTEXT函数:检查其参数是否为文本,是文本得到TRUE,不是文本得到FALSE。见下图
计算式中用ISTEXT函数对文本进行处理后,表达式可以计算
如上图所示,计算式中的〖”注释内容”〗是文本,所以ISTEXT(“注释内容”)=1,公式得出了正确的计算结果。所以要让带注释的计算式计算出结果,就需要将注释内容放到ISTEXT函数的参数中,即把:1*2+3【注释内容】 →转换成→ 1*2+3*ISTEXT(“注释内容”) → 得到结果 → 1*2+3*1 → 5。
这就要用到下面介绍的这个SUBSTITUTE函数了。
2、SUBSTITUTE函数:字符串处理函数,可以将一个指定的字符串替换为另一个指定的字符串。
处理后的计算式=SUBSTITUTE(计算式,被替换的内容,替换成的内容)
①处理注释内容
因为注释的括号是一对,所以这里要进行两次替换,依次将左括号和右括号替换成以下内容:①替换左括号〖【 〗→〖 ISTEXT(” 〗;②替换右括号 〖 】〗 → 〖 “) 〗,这个就是注释内容的处理思路。
有了思路后,我们就需要按照Excel的规则写公式了,在Excel中,字符串需要用〖“”〗引起来,而SUBSTITUTE函数的前三个参数都是字符串,所以还需要对字符串进行几次加工处理:
另外,还需要在数据和注释内容之间添加一个〖乘号〗,添加〖乘号〗的原则是:
注意:一旦根据注释内容和数字的关系设置好公式后,就只能按照此位置进行注释,否则公式的计算会出现错误。
经过以上处理,就可以把计算式中用【】注释的内容替换成可以参与计算的ISTEXT函数了。
②处理其他符号
Excel只能计算带小括号的计算式,但计算式比较复杂时,如果全部用小括号,计算式的逻辑就不那么清晰,给后期理解造成一定的影响。所以,在编辑计算公式时,如果能在计算式中使用中括号“[]”,就能在一定程度上解决这个问题。
计算式中使用“[]”是可行的,还是需要用SUBSTITUTE函数对其进行处理,将其替换为可以直接计算的小括号。即将① 〖 [ 〗 → 〖 ( 〗;② 〖 ] 〗 → 〖 ) 〗。这里对括号替换,所以就比上面更容易理解。
③总结
由于SUBSTITUTE函数一次只能对一个字符串进行替换处理,而这里需要对计算式进行四次替换处理,所以需要连续使嵌套4个SUBSTITUTE函数。这四次替换的内容如下:
三、计算结果:EVALUATE
这里用到的函数是EVALUATE函数,这个函数的作用就是对表达式进行计算,得到结果。MS-Excel和WPS-Excel中都有这个函数,但是,在这两个软件中,这个函数有一定的差异。
计算结果=EVALUATE(处理后的计算式)
①在MS-Excel中(微软)
在微软的Excel中,EVALUATE函数是宏表函数,就是不能在工作表中直接使用,需要将函数放到宏表中使用,或者在名称管理器中,通过定义名称的方式使用,见下图。
②在WPS-Excel中(金山)
在金山的Excel中,可以直接在工作表中使用EVALUATE函数(见最开始的截图),如果使用的是金山的Excel,相对就会方便很多。但是需要注意一个问题:如果在工作表中使用了这个函数,当用微软的MS-Excel打开文件时,就会全部显示错误值,而不能正常的计算出结果。所以从兼容性上考虑,建议都采用定义名称的方式,整个函数公式定义成一个名称(见上图),然后在工作表中直接使用,这样,无论MS-Excel或者WPS-Excel中,都可以正常使用了。
四、处理结果:IFERROR
处理后结果=IFERROR(计算结果, “当计算结果为错时,要显示的内容”)
这个函数在MS-EXCEL的2007及以上版本中存在(2003版本可以使用IF+ISERROR函数组合来实现相同的效果),其作用是当计算式的结果是错误值时,返回引号中的内容;当结算结果不是错误值时,返回计算结果。