資料的方向放反了，想把橫的轉成直的；或是一張二維表格，想攤成一欄再拿去 JOIN、UNIQUE；又或是排序完只想留前三名。這三個老牌函式 TRANSPOSE、FLATTEN、ARRAY_CONSTRAIN 各管一件事，我自己用得最多的是 TRANSPOSE，其次是 FLATTEN，ARRAY_CONSTRAIN 算是冷門的。
怎麼選
| 我想要 | 用這個 | 結果的形狀 |
|---|---|---|
| 列變欄、欄變列 | TRANSPOSE | 列數與欄數對調 |
| 把一個或多個範圍排成單欄 | FLATTEN | 一欄 |
| 只留左上角 N 列 M 欄 | ARRAY_CONSTRAIN | N 列 M 欄 |
要攤成單列、要略過空白格、或要指定依欄還是依列掃描，改用 TOROW、TOCOL；要挑特定幾列幾欄，是 CHOOSEROWS、CHOOSECOLS。
下面的例子用這張銷量表，放在 A1:C4：
| A | B | C | |
|---|---|---|---|
| 1 | 品項 | 台北店 | 台中店 |
| 2 | 蘋果紅茶 | 37 | 8 |
| 3 | 烏龍奶茶 | 112 | 64 |
| 4 | 青茶 | 21 | 53 |
公式都放在 E1，結果往右、往下展開。
轉置
TRANSPOSE
把範圍的列與欄互換，原本的第一列變成第一欄。
語法：
- 陣列或範圍：要轉置的資料，可以是儲存格範圍，也可以是別的函式算出來的陣列。
A1:C4 是 4 列 3 欄，轉置後變成 3 列 4 欄
=TRANSPOSE(A1:C4)先想像一下：第一列「品項、台北店、台中店」會直立起來，變成 E 欄。來看看結果，E、F、G、H 是欄名：
| E | F | G | H | |
|---|---|---|---|---|
| 1 | 品項 | 蘋果紅茶 | 烏龍奶茶 | 青茶 |
| 2 | 台北店 | 37 | 112 | 21 |
| 3 | 台中店 | 8 | 64 | 53 |
注意事項：
- 轉出來的是公式結果，不是複製過去的值，原資料改了，這邊跟著變。
- 展開的範圍有東西擋住（例如
G2已經有字），會得到#REF!，提示是「系統未展開陣列結果，以免覆蓋G2中的資料。」，清掉那格就好。 - 最常搭配的是
SPLIT：SPLIT一定是橫著拆，想要直的就在外面包TRANSPOSE，範例在 SPLIT、TEXTJOIN 與 JOIN。
攤平
FLATTEN
把一個或多個範圍的所有值排成單一欄。
語法：
- 範圍1：要攤平的範圍，可以是二維。
- 範圍2：選填，後面可以接更多範圍，會接在前一個後面。
先攤平整張表：
把 A1:C4 的 12 格攤成一欄
=FLATTEN(A1:C4)結果是一列一列由左到右讀，讀完一列再讀下一列：
| E | |
|---|---|
| 1 | 品項 |
| 2 | 台北店 |
| 3 | 台中店 |
| 4 | 蘋果紅茶 |
| 5 | 37 |
| 6 | 8 |
| 7 | 烏龍奶茶 |
| 8 | 112 |
| 9 | 64 |
| 10 | 青茶 |
| 11 | 21 |
| 12 | 53 |
如果要依欄由上到下讀，TOCOL 有參數可以選。Excel 也有對應的函式：Microsoft 365／Excel 2024 的 TOCOL、TOROW 可以攤平，TRANSPOSE 則是 Excel 本來就有的函式。
多個範圍會依序接起來。把兩家店的銷量接成一欄：
先接台北店 B2:B4，再接台中店 C2:C4
=FLATTEN(B2:B4, C2:C4)結果是 37、112、21、8、64、53。
我最常拿它來治 JOIN：JOIN 只吃一維，二維範圍先攤平再接：
把 B2:C4 攤平後用頓號接起來
=JOIN("、", FLATTEN(B2:C4))結果是 37、8、112、64、21、53，同樣是逐列的順序。
空白格不會被丟掉，會留成空白的一格。想去掉空白，外面再包一層 FILTER：。這邊用 TOCOL 的忽略參數比較短，要多寫幾次範圍的時候我會換過去。
截取
ARRAY_CONSTRAIN
只保留陣列左上角的前幾列、前幾欄。
語法：
- 輸入範圍：要截取的範圍或陣列。
- 列數：從上面數起要留幾列。
- 欄數：從左邊數起要留幾欄。
先拿剛剛那張表試試，留前 2 列 2 欄：
A1:C4 只留左上角 2 列 2 欄
=ARRAY_CONSTRAIN(A1:C4, 2, 2)結果：
| E | F | |
|---|---|---|
| 1 | 品項 | 台北店 |
| 2 | 蘋果紅茶 | 37 |
它真正好用的地方，是接在排序後面取前幾名。這是另一張單店的杯數表，放在 A1:B8：
| A | B | |
|---|---|---|
| 1 | 品項 | 杯數 |
| 2 | 蘋果紅茶 | 38 |
| 3 | 烏龍奶茶 | 112 |
| 4 | 蘋果綠茶 | 8 |
| 5 | 青茶 | 64 |
| 6 | 桂花烏龍 | 21 |
| 7 | 鮮奶茶 | 41 |
| 8 | 冬瓜檸檬 | 19 |
我想看杯數前三名，先照杯數由大到小排，再留 3 列 2 欄：
SORT 依第 2 欄遞減排序，ARRAY_CONSTRAIN 留前 3 列 2 欄
=ARRAY_CONSTRAIN(SORT(A2:B8, 2, FALSE), 3, 2)放在 D1，結果：
| D | E | |
|---|---|---|
| 1 | 烏龍奶茶 | 112 |
| 2 | 青茶 | 64 |
| 3 | 鮮奶茶 | 41 |
SORT 的參數看 SORT 與 UNIQUE。
如果列數、欄數比範圍還大，會直接回傳整個範圍，不會補空白也不會報錯。
取前幾名我現在多半直接用 SORTN，一個函式完成排序加截取：，結果同樣是那三列。ARRAY_CONSTRAIN 的好處是它不管前面是什麼函式，任何陣列都可以截，所以還是有機會用到。
試著把這三個函式串在一起：先 SORT、再 ARRAY_CONSTRAIN、最後 TRANSPOSE，看前三名橫過來是什麼樣子吧！
我是喜特先生，Mr. Sheet，我們下個教學見！
接下來可以
相關文章
查看所有文章TOROW、TOCOL、WRAPROWS、WRAPCOLS：攤平與重新排列資料
把二維表格攤平成單列或單欄的 TOROW、TOCOL，以及把一長串名單切成每列或每欄固定個數的 WRAPROWS、WRAPCOLS。附 ignore、scan_by_column、pad_with 的用法，最後用座位表當實例。
VSTACK、HSTACK、CHOOSEROWS、CHOOSECOLS：疊表格與挑欄列
把兩張表上下疊起來的 VSTACK、左右併起來的 HSTACK，以及只留下指定列或欄的 CHOOSEROWS、CHOOSECOLS。附語法、負數索引與欄數不一致時的結果，最後用兩間分店的銷量表串起四個函式。
拆字串與合字串：SPLIT、TEXTJOIN、JOIN、CONCATENATE 與 CONCAT
整理一格拆多格的 SPLIT（split_by_each、remove_empty_text、數字和日期自動轉換）與多格合一格的 TEXTJOIN、JOIN、CONCATENATE、CONCAT，包含 ignore_empty、JOIN 只吃一維、CONCAT 只有兩個參數，以及和 & 運算子的差別。
留言討論
共 0 則留言