求助 代号不同 名称相同 自动求总数

cayuer

能否跳过数据透视的一系列繁杂的操作实现 如图的功能，求助大侠，谢谢谢谢！！！！！！

499279666

1. =DROP(GROUPBY(HSTACK(MATCH(A3:A62&B3:B62,A:A&B:B,),A3:B62),C3:C62,SUM),,1)

复制代码

无关岁月

1. =DROP(LET(_a,A2:A62&B2:B62,GROUPBY(HSTACK(MATCH(_a,_a,),A2:B62),C2:C62,VSTACK(SUM,"总数"),3,1)),,1)

复制代码

swl4561255

1. Sub test()
   
2. arr = Range("A1").CurrentRegion
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. For i = 3 To UBound(arr) - 1
   
5.     d(arr(i, 1) & "-" & arr(i, 2)) = d(arr(i, 1) & "-" & arr(i, 2)) + arr(i, 3)
   
6. Next
   
7. k = d.keys
   
8. t = d.items
   
9. [L3].Resize(d.Count, 1) = Application.Transpose(k)
   
10. [L3].Resize(d.Count, 1).TextToColumns other:=True, otherchar:="-"
    
11. [N3].Resize(d.Count, 1) = Application.Transpose(t)
    
12. For j = 3 To d.Count + 2
    
13.     s = s + Cells(j, "N")
    
14. Next
    
15. Cells(3 + d.Count, "N") = s
    
16. Set d = Nothing
    
17. End Sub
    

复制代码

xlllw203

1. =HSTACK(UNIQUE(A3:B62),BYROW(UNIQUE(A3:B62),LAMBDA(y,SUMIFS(C:C,A:A,TAKE(y,,1),B:B,TAKE(y,,-1)))))

复制代码

cyh3230

数据透视表很复杂么????

cayuer

cyh3230 发表于 2024-4-11 09:17
数据透视表很复杂么????

不不复杂 只不过想偷点懒 所以求助大神帮忙

yellowhawk

=CHOOSE(COLUMN(A1),LEFT(INDEX(UNIQUE($A$3:$A$62&$B$3:$B$62),ROW(A1)),11),MID(INDEX(UNIQUE($A$3:$A$62&$B$3:$B$62),ROW(A1)),12,3),SUM((INDEX(UNIQUE($A$3:$A$62&$B$3:$B$62),ROW(A1))=$A$3:$A$62&$B$3:$B$62)*($C$3:$C$62)))
如果有let函数简化为：
=let(a,INDEX(UNIQUE($A$3:$A$62&$B$3:$B$62),ROW(A1)),CHOOSE(COLUMN(A1),LEFT(a,11),MID(a,12,3),SUM((a=$A$3:$A$62&$B$3:$B$62)*($C$3:$C$62))))