在用Excel表格计算工程量时,经常需要一个能将计算式自动出结果的公式,今天就详细说说这个公式。(此文约2700字,还附不少图片,请耐心阅读,必定会有所收获

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

计算表达式直接计算出结果

上图中可以把计算式转换为计算结果的公式,如下:

=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()),其原理和详细说明,见下图。

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

 

②计算结果在计算式的右边:

此时,获取当前行计算公式所在单元格的公式为:OFFSET(INDIRECT(“A2”),ROW()-2,COLUMN()-2),其原理和详细说明,见下图。

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

 


二、处理计算式:SUBSTITUTE、ISTEXT

由于在编制计算式时,会对数据做注释,以便今后理解计算式中的数据。此部分内容,主要讲解如何处理计算式中的文本注释和其他符号,以实现让计算式能计算出正确的结果。

1、ISTEXT函数:检查其参数是否为文本,是文本得到TRUE,不是文本得到FALSE。见下图

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

计算式中用ISTEXT函数对文本进行处理后,表达式可以计算

如上图所示,计算式中的〖”注释内容”〗是文本,所以ISTEXT(“注释内容”)=1,公式得出了正确的计算结果。所以要让带注释的计算式计算出结果,就需要将注释内容放到ISTEXT函数的参数中,即把:1*2+3【注释内容】 →转换成→ 1*2+3*ISTEXT(“注释内容”) → 得到结果 → 1*2+3*1 → 5

这就要用到下面介绍的这个SUBSTITUTE函数了。

2、SUBSTITUTE函数:字符串处理函数,可以将一个指定的字符串替换为另一个指定的字符串。

处理后的计算式=SUBSTITUTE(计算式,被替换的内容,替换成的内容)

①处理注释内容

因为注释的括号是一对,所以这里要进行两次替换,依次将左括号和右括号替换成以下内容:①替换左括号〖【 〗→〖 ISTEXT(” 〗;②替换右括号 〖 】〗 → 〖 “) 〗,这个就是注释内容的处理思路。

有了思路后,我们就需要按照Excel的规则写公式了,在Excel中,字符串需要用〖“”〗引起来,而SUBSTITUTE函数的前三个参数都是字符串,所以还需要对字符串进行几次加工处理:

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

 

另外,还需要在数据和注释内容之间添加一个〖乘号〗,添加〖乘号〗的原则是:

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

 

注意:一旦根据注释内容和数字的关系设置好公式后,就只能按照此位置进行注释,否则公式的计算会出现错误。

经过以上处理,就可以把计算式中用【】注释的内容替换成可以参与计算的ISTEXT函数了。

②处理其他符号

Excel只能计算带小括号的计算式,但计算式比较复杂时,如果全部用小括号,计算式的逻辑就不那么清晰,给后期理解造成一定的影响。所以,在编辑计算公式时,如果能在计算式中使用中括号“[]”,就能在一定程度上解决这个问题。

计算式中使用“[]”是可行的,还是需要用SUBSTITUTE函数对其进行处理,将其替换为可以直接计算的小括号。即将① 〖 [ 〗 → 〖 ( 〗;② 〖 ] 〗 → 〖 ) 〗。这里对括号替换,所以就比上面更容易理解。

③总结

由于SUBSTITUTE函数一次只能对一个字符串进行替换处理,而这里需要对计算式进行四次替换处理,所以需要连续使嵌套4个SUBSTITUTE函数。这四次替换的内容如下:

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

 


三、计算结果:EVALUATE

这里用到的函数是EVALUATE函数,这个函数的作用就是对表达式进行计算,得到结果。MS-Excel和WPS-Excel中都有这个函数,但是,在这两个软件中,这个函数有一定的差异。

计算结果=EVALUATE(处理后的计算式)

①在MS-Excel中(微软)

在微软的Excel中,EVALUATE函数是宏表函数,就是不能在工作表中直接使用,需要将函数放到宏表中使用,或者在名称管理器中,通过定义名称的方式使用,见下图。

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网

 

②在WPS-Excel中(金山)

在金山的Excel中,可以直接在工作表中使用EVALUATE函数(见最开始的截图),如果使用的是金山的Excel,相对就会方便很多。但是需要注意一个问题:如果在工作表中使用了这个函数,当用微软的MS-Excel打开文件时,就会全部显示错误值,而不能正常的计算出结果。所以从兼容性上考虑,建议都采用定义名称的方式,整个函数公式定义成一个名称(见上图),然后在工作表中直接使用,这样,无论MS-Excel或者WPS-Excel中,都可以正常使用了。


四、处理结果:IFERROR

处理后结果=IFERROR(计算结果, “当计算结果为错时,要显示的内容”)

这个函数在MS-EXCEL的2007及以上版本中存在(2003版本可以使用IF+ISERROR函数组合来实现相同的效果),其作用是当计算式的结果是错误值时,返回引号中的内容;当结算结果不是错误值时,返回计算结果。


五、效果演示

excel计算式自动转结果,还支持备注文字,用函数公式就可以实现-天天办公网