Item: 心心甜心屋 Candy House
Rating: 4
Author: 錦子老師

2854

Excel表格轉置

WILL：「錦子老師，請問一下，如果要把左邊的表格轉成右邊的形式，能用函數解決嗎?謝謝！」

錦子老師：「這是可以的只是有些複雜，具體作法如下：

S2儲存格公式：

公式1：($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4)

傳回C2:L4儲存格中內容為V的儲存格，其欄編號+100乘上列編號。

公式2：COUNTIF($C$2:$L$4,"<>V")

統計C2到L4儲存格中不是V的儲存格筆數。

公式3：SMALL(公式1,公式2) +ROW()-1)

傳回公式1中第N(公式2的值加上列編號減1)小的數值。

公式4：SUMPRODUCT(公式3)

傳回公式3傳回的數值總和。

公式5：LEFT(公式4,1)

傳回公式4所傳回的數值左邊第1位數，表示該數值位於那一列。

公式6：=INDEX($A$1:$A$4,公式5,0)

傳回A1:A4儲存格中第N列(公式5結果)儲存格內容。

公式7：=IFERROR(公式7,"")

如果公式6傳回結果為錯誤訊息，則不填入資料。

完整公式：

=IFERROR(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),"")

再將公式複製到S3:S15儲存格。

T2儲存格公式：

與S2儲存格公式不同點是將「公式6：=INDEX($A$1:$A$4,公式5,0)」變為「公式6：=INDEX($B$1:$B$4,公式5,0)」而己。

完整公式：

=IFERROR(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),"")

再將公式複製到T3:T15儲存格。

U2儲存格公式：

公式1：($C$2:$L$4="V")*(COLUMN($C$2:$L$4)+100)*ROW($C$2:$L$4)

傳回C2:L4儲存格中內容為V的儲存格，其欄編號+100乘上列編號。

公式2：COUNTIF($C$2:$L$4,"<>V")

統計C2到L4儲存格中不是V的儲存格筆數。

公式3：SMALL(公式1,公式2) +ROW()-1)

傳回公式1中第N(公式2的值加上列編號減1)小的數值。

公式4：SUMPRODUCT(公式3)

傳回公式3傳回的數值總和。

公式5：IF(公式4>1000,LEFT(公式4,2),LEFT(公式4,1))

如果公式4傳回的數值大於1000，則擷取公式4結果左邊2位數，否則擷取公式4結果左邊1位數。

公式6：RIGHT(公式4/公式5,2)

傳回公式4除以公式5的結果右邊2位數，表示該數值位於那一欄。

公式7：INDEX($A$1:$L$1,0,公式6)

傳回A1:L1儲存格中第N(公式6的值)欄儲存格內容。

公式8：=IFERROR(公式7,"")

如果公式7傳回結果為錯誤訊息，則不填入資料。

完整公式：

=IFERROR(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)),"")