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 函数来实现计算;

解题思路

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

操作方法

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
'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

第二行代码的CalculateWithText可以替换成自己方便记忆的名称,将作为函数名使用;

支持功能:

  • 计算带文本公式
  • 支持多区间和单元格计算
  • 支持单元格中内容包含换行
  • 支持单元格中内容包含空格
  • 解决因单元格过多超过公式的最大长度导致结果出现 #VALUE! 的错误
  • 解决单元格为空时,出现 #VALUE! 的错误
  • 解决 () 未优先计算的问题

测试结果:

计算成功

总结

两种方法各有利弊,各位可以根据实际情况按需使用;

优点: 上手简单,使用简便,复制粘贴即可食用;

缺点: 因为在名称管理器的引用位置设置了具体的工作簿和单元格,所以并不适用任何单元格计算得出结果;

优点: 因采用VBA函数定义,所以可以一次定义,可以在当前工作簿任意单元格输入定义的函数进行计算;

缺点: 涉及了VBA函数,使用门槛较高,有一定的学习成本;

参考链接