excel吧 关注：280,879贴子：1,543,776
- 29回复贴，共1页
菠萝蜜
'
Option Explicit
'
Sub abc()
Dim a, i, j, p, n, s, f, d, t(2)
a = [a1].CurrentRegion.Offset(1).Resize(, 1).Value
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(a) - 1
p = InStr(a(i, 1), ")")
If p Then n = Val(Mid(a(i, 1), p + 1))
ReDim b(1 To 2, 1 To Len(a(i, 1)))
s = a(i, 1) & "?": f = 0
For j = 1 To Len(s) - 1
t(0) = Mid(s, j, 1)
If t(0) = "(" Then
f = 1
ElseIf t(0) = ")" Then
f = 0
Else
If Not IsNumeric(t(0)) Then
If Asc(t(0)) > 64 Then
t(2) = Mid(s, j + 1, 1)
If t(2) >= "a" And t(2) <= "z" Then
t(0) = t(0) & t(2)
j = j + 1
End If
b(1, j) = t(0)
t(1) = Val(Mid(s, j + 1))
If t(1) = 0 Then t(1) = 1
b(2, j) = t(1)
If f Then b(2, j) = b(2, j) * n
End If
End If
End If
Next
For j = 1 To UBound(b, 2)
If Len(b(1, j)) Then d(b(1, j)) = d(b(1, j)) + b(2, j)
Next
t(1) = vbNullString
For Each j In d.keys
If d(j) = 1 Then t(0) = vbNullString Else t(0) = d(j)
t(1) = t(1) & j & t(0)
Next
a(i, 1) = t(1): d.RemoveAll
Next
[b2].Resize(UBound(a) - 1) = a
End Sub
好长~多用正则应该能精简，但是这个正则返回的数组在某些情况下调试不好，类似多维展开错误
=LET(x,TEXTBEFORE(TEXTAFTER(A1,"("),")"),y,REGEXEXTRACT(IFNA(SUBSTITUTE(A1,x,REPT(x,REGEXEXTRACT(A1,"(?<=\))\d+"))),A1),"[A-Z][a-z0-9]*",1),z,CONCAT(IF(ISNUMBER(--RIGHT(y)),y,y&1)),a,TEXTSPLIT(z,SEQUENCE(9),,1),b,TEXTSPLIT(z,a,,1),CONCAT(UNIQUE(MAP(a,LAMBDA(m,LET(s,SUM((a=m)*b),m&IF(s=1,"",s)))),1)))
=LET(x,TEXTBEFORE(TEXTAFTER(A1,"("),")"),y,REGEXEXTRACT(IFNA(SUBSTITUTE(A1,x,REPT(x,REGEXEXTRACT(A1,"(?<=\))\d+"))),A1),"[A-Z][a-z0-9]*",1),z,CONCAT(IF(ISNUMBER(--RIGHT(y)),y,y&1)),a,TEXTSPLIT(z,SEQUENCE(9),,1),b,TEXTSPLIT(z,a,,1),CONCAT(UNIQUE(MAP(a,LAMBDA(m,LET(s,SUM((a=m)*b),m&IF(s=1,"",s)))),1)))
能写，但思路不太好，二次处理了一下，公式就开起了火车
=LET(fn,LAMBDA(data,DROP(REDUCE(0,data,LAMBDA(x,y,VSTACK(x,LET(a,SUM(IFNA(--REGEXP(y,"\d+(?=$)"),1)),b,REGEXP(y,"\d+(?=$)",2,""),EXPAND(b,a,,b))))),1)),c,fn(REGEXP(CONCAT(fn(REGEXP(A2,"[A-z](\d+)?|\(\w+\)\d+"))),"[A-z](\d+)?")),d,UNIQUE(c),CONCAT(BYROW(DROP(REDUCE(0,d,LAMBDA(m,n,VSTACK(m,HSTACK(n,TEXT(SUM(--(c=n)),"[>1]0;;;"))))),1),CONCAT)))
=LET(fn,LAMBDA(data,DROP(REDUCE(0,data,LAMBDA(x,y,VSTACK(x,LET(a,SUM(IFNA(--REGEXP(y,"\d+(?=$)"),1)),b,REGEXP(y,"\d+(?=$)",2,""),EXPAND(b,a,,b))))),1)),c,fn(REGEXP(CONCAT(fn(REGEXP(A2,"[A-z](\d+)?|\(\w+\)\d+"))),"[A-z](\d+)?")),d,UNIQUE(c),CONCAT(BYROW(DROP(REDUCE(0,d,LAMBDA(m,n,VSTACK(m,HSTACK(n,TEXT(SUM(--(c=n)),"[>1]0;;;"))))),1),CONCAT)))
我化学也不好。
=LET(x,WRAPROWS(MID(REGEXREPLACE(IF(REGEXTEST(A1,"\("),REGEXREPLACE(A1,"\(.+\).",REPT(REGEXEXTRACT(A1,"\((.+)\)",2),REGEXEXTRACT(A1,"(?<=\))."))),A1),"([A-Za-z])(?=[a-zA-z])","${1}1"),ROW($1:$20),1),2),y,TAKE(x,,1),CONCAT(TEXT(DROP(GROUPBY(HSTACK(MATCH(y,y,),y),IFERROR(--DROP(x,,1),0),SUM,,0,,y<>""),,1),"[>1]0;"))) =LET(k,@REGEXP(A2,"\(.*\)\d+"),m,MID(k,2,FIND(")",k)-2),x,SUBSTITUTE(A2,k,REPT(m,--TEXTAFTER(k,")"))),p,REGEXP(x,"[A-Z][a-z]?"),q,IFERROR(--DROP(TEXTSPLIT(L2,,p),1),1),r,UNIQUE(TRANSPOSE(p)),t,MMULT(N(r=p),q),CONCAT(r&IF(t=1,"",t)))
不太清楚wps的正则具体的规则，这个是我自己写的regexp：
Function REGEXP(text, Expression)
Dim Reg As Object, i%, temp, p As Match, matches As MatchCollection
Set Reg = CreateObject("vbscript.regexp")
With Reg
.Pattern = Expression
.Global = True
Set matches = .Execute(text)
End With
ReDim temp(0 To matches.Count - 1)
For Each p In matches
temp(i) = p.Value
i = i + 1
Next p
REGEXP = temp
End Function
不太清楚wps的正则具体的规则，这个是我自己写的regexp：
Function REGEXP(text, Expression)
Dim Reg As Object, i%, temp, p As Match, matches As MatchCollection
Set Reg = CreateObject("vbscript.regexp")
With Reg
.Pattern = Expression
.Global = True
Set matches = .Execute(text)
End With
ReDim temp(0 To matches.Count - 1)
For Each p In matches
temp(i) = p.Value
i = i + 1
Next p
REGEXP = temp
End Function
