Chapter 11. 函式 (六)：陣列與陣列函式 › 11.3 陣列欄列轉換
11.3 陣列欄列轉換
本節將介紹與轉換陣列欄列相關的函式，包括轉置、轉換成特定欄 / 列數與合併等：
- TRANSPOSE：將資料轉置
語法 | TRANSPOSE (陣列或範圍) |
輸入 | 陣列或範圍：要轉置的資料。 |
輸出 | 將 陣列或範圍 轉置，例如 m 欄 n 列會變成 n 欄 m 列。 |
- FLATTEN：將二維資料轉為一維資料
語法 | FLATTEN (範圍_1, [範圍_2,...]) |
輸入 | 範圍_n：要轉為一維的資料。 |
輸出 | 將每個 範圍_n 按列轉為單欄的資料，轉置順序為先左到右、再上到下。 |
- TOCOL / TOROW：將資料轉為單欄 / 列
語法 | |
輸入 |
|
輸出 | 將 陣列或範圍 轉為單欄 / 列。 |
- WRAPCOLS / WRAPROWS：將資料轉置為特定數量的列 / 欄
語法 | |
輸入 |
|
輸出 | 將資料轉置為每 截斷數目 筆資料一欄 / 列，兩者轉置的方向不一樣。 |
- HSTACK / VSTACK：將多個範圍水平 / 垂直合併
語法 | |
輸入 | 範圍_n：要水平 / 垂直合併的範圍。 |
輸出 | 將所有 範圍_n 水平 / 垂直合併的結果。若水平合併但每個 範圍_n 列數不同，或是垂直合併但每個 範圍_n 欄數不同時，缺少的部分會出現 #N/A!。相當於支援欄、列數不同的陣列合併 (使用 { } 直接合併會出現 #REF!)。 |
接下來使用四個基本範例說明以上函式的用法，使用的資料如下，共有三個已命名範圍 — 性別、年齡區間、性別與年齡區間，三個範圍都不包含表頭。
- 將 性別與年齡區間 轉置
公式
=TRANSPOSE(性別與年齡區間)
解說
直接使用 TRANSPOSE 即可，如果在此使用整欄的話，轉置時會自動將 (有內容的) 最後一列之後的空白列都刪除。
- 將 性別與年齡區間 轉成一列
公式
方法一：分開使用 性別 / 年齡區間 (僅適用於欄位數較少時)
- =TRANSPOSE({性別;年齡區間})
- =TRANSPOSE(FLATTEN(性別,年齡區間))
- =TRANSPOSE(VSTACK(性別,年齡區間))
方法二：使用 性別與年齡區間
- =TRANSPOSE(FLATTEN(性別與年齡區間))
- =TRANSPOSE(TOCOL(性別與年齡區間,TRUE,TRUE))
- =TOROW(性別與年齡區間,TRUE,TRUE)
解說
方法一：分開使用 性別 / 年齡區間 (僅適用於欄位數較少時)
可先將資料合併成一欄，再使用 TRANSPOSE 轉置成一列，合併成一欄的方式很多，在此分別使用陣列、FLATTEN、VSTACK 完成。
方法二：使用 性別與年齡區間
- 使用 FLATTEN(性別與年齡區間) 轉為一欄時空白的資料也轉置，因此可改為使用 TOCOL(性別與年齡區間,TRUE)，此外可將 依欄掃描 調整成 TRUE，就會先顯示所有性別再顯示所有年齡區間。
- TOROW 等同於使用 TRANSPOSE + TOCOL。
- 將年齡區間轉換為 3 欄 2 列的資料
公式
- =WRAPCOLS(年齡區間,2,"")
- =WRAPROWS(年齡區間,3,"")
解說
兩者轉置的方向不同，輸出如下圖，可以想成 WRAPCOLS 是轉成幾欄，所以會先填滿一欄再換下一欄，WRAPROWS 則相反。
- 列出所有性別 × 年齡區間的組合，共 2 × 5 種
公式
=ARRAYFORMULA(SPLIT(TOCOL(性別&"|"&TRANSPOSE(年齡區間)),"|"))
解說
本範例較複雜，以下分成多個步驟說明：
- 在上一節有提到，若陣列分別為「單欄多列」和「單列多欄」運算時將不受限制，會將其自動轉為多欄、多列重複的值，而在此 性別 和 TRANSPOSE(年齡區間) 分別為「單欄多列」和「單列多欄」的陣列。
- 使用 ARRAYFORMULA 及 & 將兩個陣列合併之後，會成為多欄、多列的陣列，為了讓組合可以進一步分割成兩欄，可以在兩個陣列合併時插入一個無關的字串，作為之後的分割符號，在此為 "|"。
- 使用 ARRAYFORMULA + SPLIT 將所有合併完成的陣列分割。& 和 SPLIT 都需要使用 ARRAYFORMULA，可以統一包在最外層即可。