請問一下,如果要把左邊的表格轉成右邊的形式,能用函數解決嗎?
謝謝
=INDEX($A$1:$A$4,LEFT(SUMPRODUCT(SMALL(($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4),COUNTIF($C$2:$L$4,"<>V")+ROW()-1)),1),0)
再向下複製。
O2公式:
=INDEX($B$1:$B$4,LEFT(SUMPRODUCT(SMALL(($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4),COUNTIF($C$2:$L$4,"<>V")+ROW()-1)),1),0)
再向下複製。
P2公式:
=INDEX($A$1:$L$1,0,RIGHT(SUMPRODUCT(SMALL(($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4),COUNTIF($C$2:$L$4,"<>V")+ROW()-1))/IF(SUMPRODUCT(SMALL(($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4),COUNTIF($C$2:$L$4,"<>V")+ROW()-1))>1000,LEFT(SUMPRODUCT(SMALL(($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4),COUNTIF($C$2:$L$4,"<>V")+ROW()-1)),2),LEFT(SUMPRODUCT(SMALL(($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4),COUNTIF($C$2:$L$4,"<>V")+ROW()-1)),1)),2))
再向下複製。