gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-04 20:29:21 26 4
gpt4 key购买 nike

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

2
1
- → 2
3

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

最佳答案

尝试以下用户定义函数:

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

例如:

enter image description here

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

编辑#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

关于excel - 包含一些文本字段的列的总和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/52994458/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com