[퀴즈_127] 표 변환하기

=MAKEARRAY(ROWS(J2:J7),COLUMNS(K1:M1),LAMBDA(iR,iC,
LET(CnIdx,MATCH(INDEX(K1:M1,1,iC),B1:G1,0), ReVe,XLOOKUP(INDEX(J2:J7,iR,1),CHOOSECOLS(B2:G4,CnIdx),CHOOSECOLS(B2:G4,CnIdx+1),0),
IF(ReVe=0,"-",ReVe))))

K2=IFERROR(1/(1/SUMPRODUCT(($B$1:$G$1=K$1)*($B$2:$G$4=$J2),$C$2:$H$4)),"-")

복붙이요.

K2 =SUMPRODUCT(IFERROR(($B$1:$G$1=K$1)* 
         ($B$2:$G$4=$J2)*$C$2:$H$4,0))  옆아래복사

=IFERROR(
--VLOOKUP( $K$1:$M$1& $J$2:$J$7, 
WRAPROWS(TOCOL($B$1:$G$1&$B$2:$G$4),2),
2,0),"")

​

=LET( fx, LAMBDA(n, IFERROR(VLOOKUP($J$2:$J$7, DROP($B$2:$G$4,, 2*(n-1)),2,0),"")),
HSTACK(fx(1),fx(2),fx(3)))

​

=LET(Area,B2:G9,HD,B1:G1,
Data,WRAPROWS(TOCOL(Area,3),2),cha,TOCOL(IF(ISNUMBER( OFFSET(Area,,1) ),HD,NA()),3),
PIVOTBY(TAKE(Data,,1),cha, INDEX(Data,,2),SUM,0,0,,0))

=SUMPRODUCT(($B$1:$G$1=K$1)*($B$2:$G$4=$J2), $C$2:$H$4)

​

파워쿼리로 만들어 봅니다.

반대로 우측표를 좌측표으로 다시 환원하는 방법도 구현 해보세요...

행/열 제목까지 

=LET(HD, B1:G1, DV, B2:G4,
DD, WRAPROWS(TOCOL(DV,1),2),
DT, HSTACK(DD,BYROW(SEQUENCE(COUNTA(DV)/2),LAMBDA(r,INDEX(FILTER(HD,HD<>""),MOD(r-1,3)+1)))),
PIVOTBY(TAKE(DT,,1),TAKE(DT,,-1),INDEX(DT,,2),SUM,0,0,,0))