表格格式转换

deadzlq

PQ参考解法

飞天篮球猪

wdx223 发表于 2024-10-24 14:25
球哥的spl已入化境

随便玩玩

hbzql

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    分组 = Table.Group(源,"省",{"分组",each {_[省]{0}}&_[授权医院]}),
    生成表格 = Table.PromoteHeaders(Table.FromColumns(分组[分组]))
in
    生成表格

无关岁月

zhangcheng6688 发表于 2024-10-24 15:07
帅，空得时候能否用vba字典写一个，我写得思路有点凌乱
Sub test()
arr = [a1].CurrentRegion

如果没有重复的话，基本套&路是这样

1. Sub 按钮1_Click()
   
2. Dim d, arr, s, i, t, m
   
3. Set d = CreateObject("scripting.Dictionary")
   
4. arr = Range("a1").CurrentRegion
   
5. ReDim brr(1 To UBound(arr), 1 To 100)
   
6. For i = 2 To UBound(arr)
   
7.     s = arr(i, 2)
   
8.     d(s) = d(s) & "," & arr(i, 1)
   
9. Next
   
10. For Each key In d.keys
    
11.     t = Split(d(key), ",")
    
12.     m = m + 1
    
13.     brr(1, m) = key
    
14.     For i = 1 To UBound(t)
    
15.       brr(i + 1, m) = t(i)
    
16.     Next
    
17. Next
    
18. [j1].Resize(UBound(brr), m) = brr
    
19. End Sub

复制代码

mjm04

纯函数的参与下呢，一步到位的

省份列去重后FILERT提取：

=LET(_a,TOROW(UNIQUE(B2:B272)),_a1,CHOOSECOLS(_a,1),_a2,CHOOSECOLS(_a,2),_a3,CHOOSECOLS(_a,3),_a4,CHOOSECOLS(_a,4),VSTACK(_a,IFNA(HSTACK(FILTER(A2:A272,_a1=B2:B272),FILTER(A2:A272,_a2=B2:B272),FILTER(A2:A272,_a3=B2:B272),FILTER(A2:A272,_a4=B2:B272)),"")))

===========================================================

PIVOTBY 列-聚合后拆分：

=LET(a,PIVOTBY(,B2:B272,A2:A272,ARRAYTOTEXT,,,,0,1),_a1,INDEX(a,2,1),_a2,INDEX(a,2,2),_a3,INDEX(a,2,3),_a4,INDEX(a,2,4),IFNA(VSTACK(CHOOSEROWS(a,1),HSTACK(TEXTSPLIT(_a1,,","),TEXTSPLIT(_a2,,","),TEXTSPLIT(_a3,,","),TEXTSPLIT(_a4,,","))),""))

mjm04

mjm04 发表于 2024-10-25 11:42
纯函数的参与下呢，一步到位的

省份列去重后FILERT提取：

还有一种写法，
偱环

=LET(_a,DROP(IFNA(REDUCE("",UNIQUE(B2:B272),LAMBDA(a,b,HSTACK(FILTER(A2:A272,B2:B272=b),a))),""),,-1),_a1,CHOOSEROWS(_a,1),_b,XLOOKUP(_a1,A2:A272,B2:B272),VSTACK(_b,_a))