萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 辦公室軟體 >  >> Excel 教學 >> Excel 綜合

Excel 多欄資料合併為單欄教學:公式法快速將多行多列轉置為單一列,無需 Power Query

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

情境與目標

  • 來源:A1:H6 範圍(6 列 × 8 欄 = 48 筆資料)。
  • 目標:在 J 欄產生單一欄位,依序包含 A1、B1、C1…H1、A2、B2…H6 共 48 筆資料。

方法一:INDEX + 算術公式(通用所有 Excel 版本)

步驟 1:在目標起始儲存格 (J1) 輸入公式

=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)

步驟 2:向下填充至足夠列數

雙擊 J1 右下角填充控點,或拖曳至 J48(筆數 = 來源列數 × 來源欄數)。

步驟 3:複製 → 貼上值(固化結果)

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

方法二:動態陣列公式(Excel 365 / 2021+ 一鍵溢出)

在 J1 單一儲存格輸入:
=TOCOL(A1:H6) 或 =TOROW(A1:H6)(依需求直/橫向溢出)。
按 Enter 即自動溢出填滿所需範圍,無需填充控點。

方法三:Power Query(資料來源頻繁更新、需自動化)

  1. 「資料」→「從表格/範圍」建立查詢。
  2. Power Query 編輯器:全選所有欄 →「轉換」→「取消樞紐分欄」→ 選「屬性」與「值」欄位名稱 →「確定」。
  3. 「關閉並上傳」→ 產生動態連結的單欄表,來源資料更新時右鍵「重新整理」即同步。

常見錯誤與除錯

錯誤現象原因修正
公式結果為 0 或 #REF!來源範圍參考錯誤 (如鎖定錯誤、包含標題列)確認 $A$1:$H$6 為純資料範圍,標題列另處理
填充後資料重複/遺漏填充列數不等於 來源列數×欄數精確計算筆數,填充至對應列
動態陣列公式出現 #SPILL!目標區域有資料阻擋溢出清空目標區域或移至空白欄

進階:保留標題列對應關係

若需產生「屬性-值」雙欄(如:欄位名、資料值),公式修改為雙欄輸出或改用 Power Query「取消樞紐分欄」更直觀。

小結

多欄轉單欄,舊版 Excel 用 INDEX+INT+MOD 經典公式,新版 Excel 365/2021 直接用 TOCOL 一行搞定。若資料來源頻繁更新,Power Query 「取消樞紐分欄」提供動態連結方案。依版本與自動化需求選擇,讓資料整理零負擔。

copyright © 萬盛學電腦網 all rights reserved