如何实现单元格中数字求和，计数

tjx2016 · 发表于 2024-10-12 17:09

本帖最后由 tjx2016 于 2024-10-13 06:39 编辑

如何实现数字1相加，数字2、3去重计数？弄了一天也没弄出来。

对数字1，进行计数；数字2和数字3进行去重后计数，然后对计数求和，
济源1 博爱1 计数2，
焦作2武陟2计数1
温县3泌阳3计数1,
2+1+1=4 给看看怎么实现

autohotey · 发表于 2024-10-12 17:56

=SUM(UNIQUE(IFERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))

祝洪忠- · 发表于 2024-10-12 19:26

=COUNTA(TEXTSPLIT(A2,ROW(1:10)-1,,1))

槐序三冬 · 发表于 2024-10-12 22:45

数字1相加指的是让——>济源&博爱是吗？数字2、3去重是指让武涉、温县2留1还是其他（同理3）.我没看懂你的提问。

tjx2016 · 发表于 2024-10-13 06:38

槐序三冬发表于 2024-10-12 22:45
数字1相加指的是让——>济源&博爱是吗？数字2、3去重是指让武涉、温县2留1还是其他（同理3）.我没看懂你的 ...

对数字1，进行计数；数字2和数字3进行去重后计数，然后对计数求和，济源1 博爱1 计数2，焦作2武陟2计数1
温县3泌阳3计数1, 2+1+1=4 给看看怎么实现

wbjjzhu · 发表于 2024-10-13 08:40

最新版WPS公式

10贪生pass · 发表于 2024-10-13 10:28

Sub nihao()
Set a = ActiveSheet
Set rng_1 = Selection
Set rng_2 = Application.InputBox("你打算放哪", Type:=8)
col_1 = rng_2.Column
If InStr(rng_1, "1") > 0 Then
l = Len(rng_1.Value)
k = Len(Replace(rng_1, "1", ""))
a.Cells(1, col_1) = l - k
End If
If InStr(rng_1, "2") > 0 Then
a.Cells(1, col_1 + 1) = 1
End If
If InStr(rng_1, "3") > 0 Then
a.Cells(1, col_1 + 2) = 1
End If
End Sub

复制代码

槐序三冬 · 发表于 2024-10-13 10:49

本帖最后由槐序三冬于 2024-10-13 13:52 编辑

Sub sum()
Dim MyArray() As Variant
Set a = ActiveSheet
Set regx = CreateObject("vbscript.regexp")
Set rng_0 = Application.InputBox("您想要从哪里开始计数呢", Type:=8)
x = a.UsedRange.Rows.Count
col_0 = rng_0.Column
index_0 = rng_0.Row
regx.Pattern = "\d+"
regx.Global = True
For i = index_0 To x
Set m = regx.Execute(a.Cells(i, col_0))
For Each m_value In m
ReDim Preserve MyArray(j)
MyArray(j) = m_value
j = j + 1
Next
For j = 0 To UBound(MyArray)
If MyArray(j) = 1 Then
s = s + 1
ElseIf MyArray(j) = 2 Then
s2 = 1
ElseIf MyArray(j) = 3 Then
s3 = 1
'有4、5上面的就往这里加
ElseIf MyArray(j) = "" Then
Exit For
End If
Next
Next
Set rng_1 = Application.InputBox("您想要将求和结果放在哪里呢", Type:=8)
col_1 = rng_1.Column
index_1 = rng_1.Row
a.Cells(index_1, col_1) = s + s1 + s2
End Sub

复制代码

槐序三冬 · 发表于 2024-10-13 10:54

Sub sum()
Dim MyArray() As Variant
Set a = ActiveSheet
Set regx = CreateObject("vbscript.regexp")
Set rng_0 = Application.InputBox("您想要从哪里开始计数呢", Type:=8)
x = a.UsedRange.Rows.Count
col_0 = rng_0.Column
index_0 = rng_0.Row
regx.Pattern = "\d+"
regx.Global = True
For i = index_0 To x
Set m = regx.Execute(a.Cells(i, col_0))
For Each m_value In m
ReDim Preserve MyArray(j)
MyArray(j) = m_value
j = j + 1
Next
For j = 0 To UBound(MyArray)
If MyArray(j) = 1 Then
s = s + 1
ElseIf MyArray(j) = 2 Then
s2 = 1
ElseIf MyArray(j) = 3 Then
s3 = 1
'有4、5上面的就复制上面的 'ElseIf MyArray(j) = 3 Thens3 = 1往这里加
ElseIf MyArray(j) = "" Then
Exit For
End If
Next
Next
Set rng_1 = Application.InputBox("您想要将求和结果放在哪里呢", Type:=8)
col_1 = rng_1.Column
index_1 = rng_1.Row
a.Cells(index_1, col_1) = s + s1 + s2
End Sub

复制代码

槐序三冬 · 发表于 2024-10-13 11:51

Sub sum() Dim MyArray() As Variant Set a = ActiveSheet Set regx = CreateObject("vbscript.regexp") Set rng_0 = Application.InputBox("您想要从哪里开始计数呢", Type:=8) x = a.UsedRange.Rows.Count col_0 = rng_0.Column index_0 = rng_0.Row regx.Pattern = "\d+" regx.Global = True For i = index_0 To x Set m = regx.Execute(a.Cells(i, col_0)) For Each m_value In m ReDim Preserve MyArray(j) MyArray(j) = m_value j = j + 1 Next For j = 0 To UBound(MyArray) If MyArray(j) = 1 Then s = s + 1 ElseIf MyArray(j) = 2 Then s2 = 1 ElseIf MyArray(j) = 3 Then s3 = 1 '有4、5上面的就往这里加 ElseIf MyArray(j) = "" Then Exit For End If Next Next Set rng_1 = Application.InputBox("您想要将求和结果放在哪里呢", Type:=8) col_1 = rng_1.Column index_1 = rng_1.Row a.Cells(index_1, col_1) = s + s1 + s2 End Sub

		自动登录	找回密码
密码			免费注册

[求助] 如何实现单元格中数字求和，计数

评分