EXCEL 多欄表格橫轉直 cross table to list 交叉表格轉為列表 Unpivot Table 免巨集

雖然EXCEL內建的簡單的資料橫直對調的功能，但是資料的呈現不如人意

另外一種方法是用Unpivot Table功能(翻譯寫逆透視表格，我找不到別的翻譯)，有興趣的話可以google看看，不過這樣只是把資料的值轉過去，連動是做不到的

我爬文發現還有最新的函數tocol可以輕鬆做到想要的樣子，但是限定要OFFICE365，OFFICE2021沒有，所以還是想要研究出用函數直接處理的方式。

最後我終於想出來了，不過僅限於固定種類，筆數可以無限增加連動，但如果增加種類要再改過函數。

 

如圖，我想把原本姓名跟成績交錯的表，轉成列表

我使用的方法是這樣，首先要增加一個欄位【編號】做索引

然後因為有四個科目，所以我在新生成的結果要有的第一個數列是1111222233334444

然後姓名的部分很好處理，用VLOOKUP靠編號去找A:B的第二欄就好

科目的部分，則是要找出編號那一列的第3 4 5 6欄，成績也是

所以下一個要生成的就是無限的3456數列 

所以只要去找出編號1的第3欄，第4欄，第5欄，第6欄，接著去找編號2的，就可以靠函數一直往下找了

我另外做了1234數列來方便理解

----

姓名

=VLOOKUP(H2,A:B,2,FALSE)

-----

11223344數列

=INT(ROW(A2)/2)

111222333444數列

=INT(ROW(A3)/3)

1111222233334444數列

=INT(ROW(A4)/4)

 11111222223333344444數列

=INT(ROW(A5)/5) 

用ROW函數取得當前列號，要重複幾次就除以多少，然後用INT取整數，就會從1開始得到所需的數列，注意第一格要能整除為1，所以要除以5就要用A5這格

-----

1212數列

=MOD(ROW(A2),2)+1

123123數列

=MOD(ROW(A3),3)+1

12341234數列

=MOD(ROW(A4),4)+1

用MOD函數去取 ROW函數的餘數，跟上面的函數一樣，第一格要能整除，就能依據所要的數量取得相對應的數列

-----

34數列 345數列 3456數列

這個直接拿上面的結果再加2就好了 

=MOD(ROW(A2),2)+3

=MOD(ROW(A3),3)+3

=MOD(ROW(A4),4)+3

---

科目

=VLOOKUP("編號",$A$1:$F$1,J2,FALSE) 

=VLOOKUP("編號",$A$1:$F$1,J3,FALSE)

=VLOOKUP("編號",$A$1:$F$1,J4,FALSE)

=VLOOKUP("編號",$A$1:$F$1,J5,FALSE)

簡單來說就是用編號這個值，去找A1:F1的第3~6欄，這邊注意A1:F1要鎖定A$1:F$1

3~6欄我們前面已經產生了3~6樹列了，所以直接去找J2~J5

----

成績 

=VLOOKUP(H2,A:F,J2,FALSE)

=VLOOKUP(H3,A:F,J3,FALSE)

=VLOOKUP(H4,A:F,J4,FALSE)

=VLOOKUP(H5,A:F,J5,FALSE) 

=VLOOKUP(H6,A:F,J6,FALSE)

用H2的編號值1，去找A:F的第3欄，也就是J2

到了H5，一樣用編號值2， 去找A:F的第3欄，也就是J6

----

以上就是我用編號進行索引去將表格轉向的作法

範例檔參考

 

https://docs.google.com/spreadsheets/d/1KC9vDv3-2dm-758venJFc4ylUzAdgPVI/edit?usp=drive_link&ouid=111432457316698416668&rtpof=true&sd=true