=TOROW(C2:E2&EXPAND("",4,1,""))
=TOROW(UNIQUE(A3:A14)&EXPAND("",1,3,""))
=INDEX(C3:E14,XMATCH(H4#&G5#,A3:A14&B3:B14),XMATCH(H3#,C2:E2))
These formulas are only available in some versions of excel: [ Ссылка ]
As a LAMBDA formula:
=LAMBDA(array,LET(heading,DROP(TAKE(array,1),,2),
nohead,DROP(array,1),
one,TAKE(nohead,,1),
two,CHOOSECOLS(nohead,2),
data,DROP(nohead,,2),
unione,UNIQUE(one),
first,TOROW(heading&EXPAND("",COUNTA(unione),1,"")),
second,TOROW(unione&EXPAND("",1,COUNTA(heading),"")),
side,UNIQUE(two),
HSTACK(VSTACK("","",side),VSTACK(first,second,INDEX(data,XMATCH(second&side,one&two),XMATCH(first,heading))))))
How to rearrange a table in excel | EXPAND & TOROW formula
Теги
microsoft excelexcel new formulasdynamic arraysexcel dynamic arraysdynamic array formulaspivot formulaexcel pivot formulapivot formula in excelnew dynamic array formulastorow formula excelexpand formula excelrearrange table in excelrearrange data in excelindex match formulaunique formula excelrearrange tables in excelrearrange a table in excelrearrange numbers excelreposition table in excelmove around table in excelpivot table excel