excel表格内容自动拆解数字能实现么

墨墨20240831

表格1

表格2

请问，表格1中的内容自动拆解成表格2个格式怎么实现啊

usedrange

没有附件？

墨墨20240831

usedrange 发表于 2024-8-31 15:56
没有附件？

加不上附件了，就是两个统计格式的表格，想让表格1的内容自动对应到表格2

limonet

usedrange 发表于 2024-8-31 15:56
没有附件？

你帮他OCR上传一下附件。

沈默00

提问要先上附件

沈默00

=IFNA(REGEXP(REGEXP($A2,"\d+\个"&B$1),"\d+"),"")

lilyhcn1

通义千问可以

良良

B2=IFERROR(SUBSTITUTE(SUBSTITUTE(LOOKUP(,0/FIND(B$1,TEXTSPLIT(出库总表!$B2,"+")),TEXTSPLIT(出库总表!$B2,"+")),B$1,),"个",),"")

醉眼看尘世

1. Sub test()
   
2. Dim reGxp As Object, Arr, i&, j&, dic(1 To 2) As Object, tmPobj As Object
   
3. Set dic(1) = CreateObject("scripting.dictionary")
   
4. Set dic(2) = CreateObject("scripting.dictionary")
   
5. Set reGxp = CreateObject("vbScript.regExp")
   
6. reGxp.Global = True
   
7. reGxp.Pattern = "(\d+)个?([^\+]+)"
   
8. 
   
9. Arr = [a1].CurrentRegion
   
10. With reGxp
    
11.     For i = 2 To UBound(Arr, 1)
    
12.         Set tmPobj = .Execute(Arr(i, 2))
    
13.         For Each m In tmPobj
    
14.             dic(1)(m.submatches(1) & "") = ""
    
15.             dic(2)(Arr(i, 1) & Chr(10) & m.submatches(1)) = m.submatches(0)
    
16.         Next m
    
17.     Next i
    
18. End With
    
19. ReDim Brr(1 To UBound(Arr, 1), 1 To dic(1).Count + 1)
    
20. For i = 1 To UBound(Arr, 1)
    
21.     Brr(i, 1) = Arr(i, 1)
    
22. Next i
    
23. j = 1
    
24. For Each d In dic(1).keys
    
25.     j = j + 1
    
26.     Brr(1, j) = d
    
27. Next d
    
28. For i = 2 To UBound(Brr, 1)
    
29.     For j = 2 To UBound(Brr, 2)
    
30.         Brr(i, j) = dic(2)(Brr(i, 1) & Chr(10) & Brr(1, j))
    
31.     Next j
    
32. Next i
    
33. [e1].Resize(UBound(Brr, 1), UBound(Brr, 2)) = Brr
    
34. End Sub
    

复制代码

minchar

先做个表头和第一列
E2=SUBSTITUTE(IFNA(TEXTAFTER(TEXTBEFORE(VLOOKUP($D2,$A$1:$B$8,2,),E$1),"+",-1,1,1,""),""),"个","")
右拉下拉