单元格里面统计指定项（多项）总数

cnmlgb9998

=SUM(--TEXTSPLIT(IFNA(REGEXEXTRACT([@列1],"(?<=槟榔)[\d ]+"),0)," ",,TRUE))

我会修电视

1. =IFERROR(EVALUATE(SUBSTITUTE(TRIM(REGEXP(D4,"槟榔\K[0-9 ]+"))," ","+")),"")

复制代码

沈默00

=IFNA(SUM(--REGEXP(D3,"[槟榔\s]\K[0-9]+")),"")

mykitami

=LET(a,TEXTAFTER(D2,"槟榔",,,,""),IFERROR(SUM(--TEXTSPLIT(LEFT(a,IFNA(MATCH(2,LENB(MID(a,SEQUENCE(LEN(a)),1)),),999)-1)," ",,1)),""))

ba3259878266

mykitami 发表于 2024-9-25 11:51
=LET(a,TEXTAFTER(D2,"槟榔",,,,""),IFERROR(SUM(--TEXTSPLIT(LEFT(a,IFNA(MATCH(2,LENB(MID(a,SEQUENCE(LE ...

谢谢老师  很好用  感谢

ba3259878266

mykitami 发表于 2024-9-25 11:51
=LET(a,TEXTAFTER(D2,"槟榔",,,,""),IFERROR(SUM(--TEXTSPLIT(LEFT(a,IFNA(MATCH(2,LENB(MID(a,SEQUENCE(LE ...

老师 有超层 怎样解决

高冷cool

VBA代码：
Sub day9252()
    Set a = ActiveSheet
    x = a.Range("D" & a.Cells.Rows.Count).End(xlUp).Row
    For i = 2 To x
        sum_s = 0
        s = a.Cells(i, 4)
        If InStr(s, "槟榔") > 0 Then
            s = Mid(s, InStr(s, "槟榔") + 2, Len(s))
            n = Len(s)
            For j = 1 To n
                If IsChineseChar(Mid(s, j, 1)) Then
                    s = Mid(s, 1, j - 1)
                    Exit For
                End If
            Next
            arr = Split(s, " ")
            For Each va In arr
                If va <> "" Then sum_s = sum_s + Val(va)
            Next
        End If
        If sum_s > 0 Then a.Cells(i, 5) = sum_s
    Next
End Sub
Function IsChineseChar(ch As String) As Boolean
    Dim codePoint As Long
    codePoint = AscW(ch) ' 获取字符的Unicode值
    IsChineseChar = (codePoint >= 19968 And codePoint <= 40869)
End Function

ba3259878266

hugeinfo 发表于 2024-9-25 10:53
=SUM(IFERROR(--TEXTSPLIT(TAKE(TEXTSPLIT(TEXTAFTER(D3,"槟榔"),CHAR(ROW($35000:$60000)),),,1)," ",),0) ...

谢谢老师  槟榔数目超过一万后无效  怎样修改

槐序三冬

1. Sub 正则()
   
2.     Set a = ActiveSheet
   
3.     x = a.UsedRange.Rows.Count
   
4.     Set regx = CreateObject("vbscript.regexp")
   
5.     regx.Pattern = "槟榔( *\d+)+"
   
6.     regx.Global = True
   
7.     For i = 2 To x
   
8.         s = 0
   
9.         content_value = ""
   
10.         Set m = regx.Execute(a.Cells(i, 1))
    
11.         For Each k In m
    
12.             
    
13.             content_value = Trim(Replace(k, "槟榔", ""))
    
14.             arr = Split(content_value, " ")
    
15.             For Each kk In arr
    
16.                 If kk <> "" Then s = s + Val(kk)
    
17.             Next
    
18.         Next
    
19.         
    
20.         If s > 0 Then a.Cells(i, 4) = s
    
21.     Next
    
22. End Sub
    

复制代码

高冷cool

高冷cool 发表于 2024-9-25 15:05
VBA代码：
Sub day9252()
    Set a = ActiveSheet

第二种方法：
Sub day9253() '正则
    Set a = ActiveSheet
    x = a.Range("D" & a.Cells.Rows.Count).End(xlUp).Row
    Set regx = CreateObject("vbscript.regexp")
    regx.Pattern = "槟榔( *\d+)+"
    regx.Global = True
    For i = 2 To x
        s = 0
        content_value = ""
        Set m = regx.Execute(a.Cells(i, 4))
        For Each k In m
            content_value = Trim(Replace(k, "槟榔", ""))
            arr = Split(content_value, " ")
            For Each kk In arr
                If kk <> "" Then s = s + Val(kk)
            Next
        Next
        If s > 0 Then a.Cells(i, 5) = s
    Next
End Sub