取特定文本

Excel地板砖

D列：=INDEX($A$2:$A$171,SMALL(IF($A$2:$A$171<>"",ROW($1:$170),1000),ROW(A1)))
E列：=TEXTJOIN(" ",TRUE,OFFSET($B$2,MATCH(D5,$A$2:$A$172,0)-1,0,MATCH(D6,$A$2:$A$172,0)-MATCH(D5,$A$2:$A$172,0),1))
F列：=SUM(OFFSET($C$2,MATCH(D5,$A$2:$A$172,0)-1,0,MATCH(D6,$A$2:$A$172,0)-MATCH(D5,$A$2:$A$172,0),1))/2

mykitami

D2
=UNIQUE(A2:A171,,1)
E2
=TEXTJOIN(" ",,FILTER($B$2:$B$171,(LOOKUP(ROW($A$2:$A$171),ROW($A$2:$A$171)/($A$2:$A$171<>""),$A$2:$A$171)=D2)*($B$2:$B$171<>"")))
下拉
F2
=FILTER($C$2:$C$171,(LOOKUP(ROW($A$2:$A$171),ROW($A$2:$A$171)/($A$2:$A$171<>""),$A$2:$A$171)=D2)*($B$2:$B$171=""))
下拉

世界客都

如附件所示，请下载查看

88.zip (13.37 KB, 下载次数: 9)

2024-3-9 18:18 上传

点击文件名下载附件

用三个公式：
=UNIQUE(A2:A171,,1)
=TEXTJOIN(" ",,OFFSET(B$1,MATCH(D2,A:A,),,ABS(NPV(-2,IFNA(MATCH(D2:D3,A:A,),)))-1))
=VLOOKUP(D2,A:C,3,)
即可解决

世界客都

第2个公式这样也可以，稍微短点：
=TEXTJOIN(" ",,OFFSET(B$1,MATCH(D2,A:A,),,NPV(-2,IFNA(MATCH(D2:D3,A:A,),4^8))-1))

静听溪流

atop885 发表于 2024-3-9 15:26
使用了 不行 为空是为什么

      数组三键

咔咔乱坠

给个高版本的解法吧
最新版wps

1. =LET(a,DROP(FILTER(B$2:C171,LOOKUP(ROW($2:171),ROW($2:171)/(A$2:A171>0),A$2:A171)=D2),1),IF({1,0},TEXTJOIN(" ",,TAKE(a,,1)),SUM(DROP(a,,1))))

复制代码

excel365

1. =LET(a,DROP(FILTER(B$2:C171,SCAN(,A$2:A171,LAMBDA(x,y,IF(y>0,y,x)))=D2),1),IF({1,0},TEXTJOIN(" ",,TAKE(a,,1)),SUM(DROP(a,,1))))

复制代码

excel365

1. =LET(a,DROP(FILTER(B$2:C171,SCAN(,A$2:A171,LAMBDA(x,y,IF(y>0,y,x)))=D2),1),IF({1,0},CONCAT(TAKE(a,,1)&" "),SUM(DROP(a,,1))))

复制代码

swl4561255

1. Sub 合并数据()
   
2. ar = Range("A1").CurrentRegion.Rows.Count
   
3. arr = Range("A2:C" & ar)
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. Set d2 = CreateObject("Scripting.Dictionary")
   
6. For i = 1 To ar - 1
   
7.   If arr(i, 1) <> "" Then
   
8.     r = i
   
9.   End If
   
10.     d1(arr(r, 1)) = d1(arr(r, 1)) & " " & arr(i, 2)
    
11.     d2(arr(r, 1)) = d2(arr(r, 1)) + arr(i, 3)
    
12. Next
    
13. k1 = d1.keys
    
14. t1 = d1.items
    
15. t2 = d2.items
    
16. [H1:J1] = Array("格式", "颜色汇总", "数量汇总")
    
17. [H2].Resize(d1.Count, 1) = Application.Transpose(k1)
    
18. [I2].Resize(d1.Count, 1) = Application.Transpose(t1)
    
19. [J2].Resize(d1.Count, 1) = Application.Transpose(t2)
    
20. For j = 2 To d1.Count + 1
    
21.     Range("I" & j) = Trim(Range("I" & j))
    
22.     Range("J" & j) = Range("J" & j) / 2
    
23. Next
    
24. Set d1 = Nothing
    
25. Set d2 = Nothing
    
26. End Sub
    

复制代码