Excel - 包含文字的单元格计算

Excel 的单元格中如果包含中文字符，则计算的时候会出错，接下来我们将解决此问题……

解决方法

在录入 Excel 数据的时候，我们有时候为了便于查阅，会在单元格中为公式记录备注，如下：

序号	名称	计算式
1	xxx	1+2+3
2	xxx	1米+2米+3米
3	xxx	。。。

这种公式含有中文字符，在 Excel 中是不能直接计算得出结果的；

初级版

通过公式中的「名称管理器」实现：

第一步 点击工具栏「公式」 –> 「名称管理器」 –> 「新建」；

第二步 在名称定义方便记忆且易于输入的名字，在引用位置粘贴以下公式；

1	=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A$1,"【","*code(","】","^0"))

第四步 在单元格输入=aaa（aaa为刚刚定义的名称），回车即可看到结果；

若使用WPS软件可以将上述公式直接粘贴到单元格进行计算。

高阶版

接下来我们将通用 VBA 函数来实现计算；

解题思路

去除掉公式中非数字、加减乘除运算符号；
通过 VBA 中的 Evaluate 对最终的公式进行计算；

操作方法

将以下代码粘贴到代码编辑窗口即可；

点击跳转到VBA编辑器的使用

'Excel中对带有文本的单元格进行计算
Function CalculateWithText(ParamArray args() As Variant)
    Dim objFormula As String
    
    Dim result As String
    Dim current As String
    Dim eachRange  As Range
    For Each Rng In args
        For Each eachRange In Rng
            If result <> "" Then
                result = result + "+"
            End If
            result = result + CStr(CalculateText(eachRange.Value))
        Next
        result = CStr(Evaluate(result))
    Next
    Debug.Print result
    CalculateWithText = Evaluate(result)
End Function

Function CalculateText(objFormula As String)
    Dim current As String
    Dim validSymbol As String
    validSymbol = "+,-,*,/,.,(,)"
    '替换换行符、空格等
    objFormula = Replace(objFormula, vbNewLine, "+")
    objFormula = Replace(objFormula, vbCr, "+")
    objFormula = Replace(objFormula, vbLf, "+")
    objFormula = Replace(objFormula, vbCrLf, "+")
    objFormula = Replace(objFormula, " ", "+")
    '替换中文（、）
    objFormula = Replace(objFormula, "（", "(")
    objFormula = Replace(objFormula, "）", ")")
    For n = 1 To Len(objFormula)
        current = Mid(objFormula, n, 1)
        If IsNumeric(current) Or IsInArray(current, Split(validSymbol, ",")) Then
            result = result + current
        End If
   Next n
   'Fix当单元格为空时Error 2015，为空设置结果为0
   If result <> "" Then
        CalculateText = Evaluate(result)
   Else
        CalculateText = 0
   End If
End Function

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function