单元格内关键字段提取数据求和，求帮忙。

IPS · 发表于 2023-2-17 23:43

dambcer 发表于 2023-2-17 23:07
打开后另存为加载宏xlam，再调用自定义函数

十分感谢。

quqiyuan · 发表于 2023-2-18 07:46

PowerQuery方法。

249773986 · 发表于 2023-2-18 09:54

本帖最后由 249773986 于 2023-2-18 13:35 编辑

Sub MyTest()
Dim s, ss, ss1, m
Set reg = CreateObject("vbscript.regexp")
With reg
.Global = True
.Pattern = "会员/订票费:-?\d+\.?\d*x?\d*=(-?\d+\.?\d*)"
For Each ss In Range("b2", Cells(Rows.Count, 2).End(xlUp))
Set ss1 = .Execute(ss)
For Each s In ss1
m = m + s.submatches(0) * 1
ss.Offset(0, 1) = m
Next s
m = 0
Next ss
End With
End Sub

我的城市你来过 · 发表于 2023-2-18 12:36

=IF((LENB(B2)-LENB(SUBSTITUTE(B2,"订票费","")))/LENB("订票费")=1,MID(B2,FIND("订票费",B2)+11,4),SUM(MID(B2,FIND("订票费",B2)+11,4),TRIM(MID(SUBSTITUTE(TRIM(MID(SUBSTITUTE(B2,"订票费",REPT(" ",199)),399,199)),"=",REPT(" ",99)),99,99))))

solextrade · 发表于 2023-2-18 13:22

=SUM(IFERROR(--LEFT(SUBSTITUTE(MID(B2,(FREQUENCY((MID(B2,ROW($1:$98),2)="订票")*ROW($1:$98),(MID(B2,ROW($1:$98),1)="=")*ROW($1:$98))*ROW($2:$100)),9)," "," "),9),))

IPS · 发表于 2023-2-18 19:06

dambcer 发表于 2023-2-17 23:07
打开后另存为加载宏xlam，再调用自定义函数

万分感谢，满足各种条件，试过非常棒。

sucx007 · 发表于 2023-2-19 13:22

=SUM(--RIGHT(SUBSTITUTE(FILTERXML("<a>"&SUBSTITUTE(B2,";","")&"</a>","a/b[contains(.,'订票')]"),"="," "),5))

准提部林 · 发表于 2023-2-19 15:41

數組公式//
=SUM(--TEXT(-1%%,SUBSTITUTE(LEFT(SUBSTITUTE(IF(MID(B2,ROW($1:$99),4)="订票费:",MID(B2,ROW($1:$99)+4,19),"0=0"),";",REPT(" ",19)),19),"=",";",1)))

Tony412 · 发表于 2023-2-19 18:57

=SUM(--MID(FILTERXML("<a>"&SUBSTITUTE(B2,";","")&"</a>","a/b[contains(.,'订票')]"),FIND("=",FILTERXML("<a>"&SUBSTITUTE(B2,";","")&"</a>","a/b[contains(.,'订票')]"))+1,100))

我的城市你来过 · 发表于 2024-6-24 09:28

=EVALUATE(TEXTJOIN("",,SUBSTITUTE(REGEXP(B2,"(?<=订票费:)[-0-9.x=]+(?==)"),"x","*")))

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

[求助] 单元格内关键字段提取数据求和，求帮忙。