Excel中如何对带单位前缀的数值进行求和运算？

方法一：用Excel内置公式直接求和 #

不用写代码，直接用嵌套的SUBSTITUTE和SUMPRODUCT组合公式，把带前缀的文本转换成标准数值后求和：

比如要对A2:A4区域求和，输入以下公式：

=SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A4,"m","e-3"),"μ","e-6"),"n","e-9"),"p","e-12"))

• 原理：SUBSTITUTE依次把"m"替换成"e-3"（科学计数法表示10^-3）、"μ"替换成"e-6"，以此类推；--把转换后的文本转成数值；SUMPRODUCT完成求和。
• 注意：如果数据里前缀和数值之间有空格，要先去掉空格，比如在公式最外层加一步SUBSTITUTE(A2:A4," ","")。

方法二：自定义VBA函数（适合频繁使用的场景） #

如果经常需要处理这类数据，写个自定义函数更高效：

1. 按Alt+F11打开VBA编辑器
2. 点击菜单栏「插入」→「模块」，粘贴以下代码：

Function ConvertAndSum(rng As Range) As Double
    Dim cell As Range
    Dim valueStr As String
    Dim multiplier As Double
    Dim num As Double
    
    ConvertAndSum = 0
    
    For Each cell In rng
        valueStr = cell.Value
        multiplier = 1
        
        Select Case Right(valueStr, 1)
            Case "m"
                multiplier = 0.001
                valueStr = Left(valueStr, Len(valueStr) - 1)
            Case "μ"
                multiplier = 0.000001
                valueStr = Left(valueStr, Len(valueStr) - 1)
            Case "n"
                multiplier = 0.000000001
                valueStr = Left(valueStr, Len(valueStr) - 1)
            Case "p"
                multiplier = 0.000000000001
                valueStr = Left(valueStr, Len(valueStr) - 1)
        End Select
        
        If IsNumeric(valueStr) Then
            num = CDbl(valueStr)
            ConvertAndSum = ConvertAndSum + num * multiplier
        End If
    Next cell
End Function

3. 返回Excel，直接在单元格输入=ConvertAndSum(A2:A4)就能得到求和结果。

额外说明 #

• 如果数据里有其他单位前缀（比如千k），可以在VBA代码的Select Case里新增对应的分支；
• 确保单元格里的前缀是单个字符，没有多余符号，否则需要调整代码或公式适配。

内容的提问来源于stack exchange，提问作者tadm123