excel - 包含一些文本字段的列的总和

Question

看看我是否可以获得输入列的总和，包括具有符号和数字的列。但是，带有符号的实际上是文本字段。

2
1
- → 2
3

有任何想法吗？我尝试了一些不同的方法，包括使用 RIGHT，但仍然将其视为文本并且没有添加它。

标签： excelexcel-formula

Answer 1

尝试以下用户定义函数：

Public Function Zum(rng As Range) As Variant
    Dim r As Range, s As String, L As Long, i As Long
    Dim numbr As String, CH As String

    Zum = 0
    For Each r In rng
        s = r.Text
        If s <> "" Then
            L = Len(s)
            numbr = ""
            For i = 1 To L
                CH = Mid(s, i, 1)
                If CH Like "[0-9]" Or CH = "." Then
                    numbr = numbr & CH
                End If
            Next i
            If numbr <> "" Then
                Zum = Zum + CDbl(numbr)
            End If
        End If
    Next r
End Function

例如：

它将添加单元格，但在添加之前从单元格中删除除数字和小数点之外的任何内容。

编辑＃1：

这个版本的代码只处理每个单元格的最右边的数字，

忽略字母和小数点：

Public Function Zum(rng As Range) As Variant

'   version #2  only sum the right-most digits and ignore the decimal point


    Dim r As Range, s As String, L As Long, i As Long
    Dim numbr As String, CH As String

    Zum = 0
    For Each r In rng
        s = r.Text
        If s <> "" Then
            L = Len(s)
            numbr = ""
            For i = L To 1 Step -1
                CH = Mid(s, i, 1)
                If CH Like "[0-9]" Then
                    numbr = CH & numbr
                Else
                    Exit For
                End If
            Next i
            If numbr <> "" Then
                Zum = Zum + CDbl(numbr)
            End If
        End If
    Next r
End Function

如果你只想要一个最右边的数字：

Public Function Zum(rng As Range) As Variant

'   version #3  only sum the right-most digit and ignore the decimal point


    Dim r As Range, s As String, L As Long, i As Long
    Dim numbr As String, CH As String

    Zum = 0
    For Each r In rng
        s = r.Text
        If s <> "" Then
            CH = Right(s, 1)
            If CH Like "[0-9]" Then
                Zum = Zum + CLng(CH)
            End If
        End If
    Next r
End Function