|
|
在用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函数组合来实现相同的效果），其作用是当计算式的结果是错误值时，返回引号中的内容；当结算结果不是错误值时，返回计算结果。
五、效果演示
|
|
