多维变一微数据

hcm19522

N2:N15{=INDEX(B:B,SMALL(IF(MMULT(N(C$2:K$6<>""),ROW(1:9)^0)/3>={1,2,3},ROW($2:$6),99),ROW(A1)))&""

O2:Q15=IF($N2="","",VLOOKUP($N2,$B:$L,COUNTIF($N$2:$N2,$N2)*3-2+COLUMN(A1),))

R2:R15=IF($N2="","",VLOOKUP(N2,B:L,11,))

hugeinfo

用pq 清洗一下

alan57

1. =REDUCE(HSTACK(A1:E1,L1),A2:A601,LAMBDA(x,y,VSTACK(x,IFNA(HSTACK(OFFSET(y,,,,2),IFNA(HSTACK(WRAPROWS(TOROW(OFFSET(y,,2,,9),1),3),OFFSET(y,,11)),OFFSET(y,,11))),OFFSET(y,,,,2)))))

复制代码

高个子

1. =iferror(index(FILTER(CELL("contents",OFFSET(A1,ROW($3:$3333)/3,(MOD(ROW($3:$3333),3)*3+COLUMN($A:$F)-1)*{0,0,1,1,1,0}+{0,1,0,0,0,11})),MMULT(N(CELL("contents",OFFSET(A1,ROW($3:$3333)/3,(MOD(ROW($3:$3333),3)*3+COLUMN($A:$F)-1)*{0,0,1,1,1,0}+{0,1,0,0,0,11}))>0),ROW($1:$6)^0)=6),row(a1),column(a1)),"")

复制代码