手邊有一張多行多列的資料表(如 6 行 × 8 欄),需要將所有資料「壓扁」成單一欄位(48 列 × 1 欄)以便後續分析、匯入資料庫或製作樞紐表?不用 Power Query,只需一個簡單公式配合填充控點,即可快速完成多欄轉單欄。本文詳細說明公式邏輯、操作步驟、動態陣列新寫法(Excel 365/2021+),並附常見錯誤排除。

=INDEX($A$1:$H$6, INT((ROW(A1)-1)/COLUMNS($A$1:$H$6))+1, MOD(ROW(A1)-1, COLUMNS($A$1:$H$6))+1)
邏輯解析:
• COLUMNS($A$1:$H$6) = 8 (來源欄數)
• ROW(A1)-1 = 0,1,2... (目標列索引 0-based)
• INT(.../8)+1 → 來源列號 (1→6)
• MOD(... ,8)+1 → 來源欄號 (1→8)
雙擊 J1 右下角填充控點,或拖曳至 J48(筆數 = 來源列數 × 來源欄數)。



全選 J1:J48 → Ctrl+C → 右鍵 →「貼上選項」→「值」→ 完成單欄資料。




在 J1 單一儲存格輸入:
=TOCOL(A1:H6) 或 =TOROW(A1:H6)(依需求直/橫向溢出)。
按 Enter 即自動溢出填滿所需範圍,無需填充控點。
| 錯誤現象 | 原因 | 修正 |
|---|---|---|
| 公式結果為 0 或 #REF! | 來源範圍參考錯誤 (如鎖定錯誤、包含標題列) | 確認 $A$1:$H$6 為純資料範圍,標題列另處理 |
| 填充後資料重複/遺漏 | 填充列數不等於 來源列數×欄數 | 精確計算筆數,填充至對應列 |
| 動態陣列公式出現 #SPILL! | 目標區域有資料阻擋溢出 | 清空目標區域或移至空白欄 |
若需產生「屬性-值」雙欄(如:欄位名、資料值),公式修改為雙欄輸出或改用 Power Query「取消樞紐分欄」更直觀。
多欄轉單欄,舊版 Excel 用 INDEX+INT+MOD 經典公式,新版 Excel 365/2021 直接用 TOCOL 一行搞定。若資料來源頻繁更新,Power Query 「取消樞紐分欄」提供動態連結方案。依版本與自動化需求選擇,讓資料整理零負擔。