excel 如何把多行合併成一行：多種方法徹底解析與實用技巧

Byadmin

在 Excel 的日常使用中，我們經常會遇到需要將分散在不同儲存格、甚至不同行中的資料，匯集、合併成一個單一的儲存格或一行。無論是為了資料分析、報表製作，還是為了美觀呈現，這項技能都極為實用。本文將深入探討 Excel 中將多行資料合併成一行的多種方法，從基本函數到進階工具，提供詳細步驟與實用範例，協助您輕鬆解決這個常見的資料整理挑戰。

為何需要將多行合併成一行？

在許多情境下，將多行資料合併成一行是必要的。例如：

  • 資料清洗與整理： 將分散的地址資訊、產品描述或客戶備註合併為一個完整的欄位。
  • 報告生成： 將原本以清單形式呈現的數據，轉換為更簡潔的摘要或單行報告。
  • 特定格式要求： 某些系統或應用程式要求資料以單一行或單一儲存格的形式導入。
  • 美觀與可讀性： 避免過於冗長的表格，提升資料呈現的清晰度。

將多行合併成一行的多種方法

1. 使用 TEXTJOIN 函數：現代且高效的選擇

在 Excel 2016 及更高版本中，TEXTJOIN 函數 是將多個文字字串與自訂分隔符號合併的最佳選擇。它解決了傳統 CONCATENATE 函數無法指定分隔符號的痛點，並能自動忽略空白儲存格。

語法：

TEXTJOIN(分隔符號, 忽略空白, 範圍1, [範圍2], ...)

  • 分隔符號： 用引號括起來的文字字串，用於分隔合併後的每個項目。例如：”、”、”, “、” – “。
  • 忽略空白： TRUE 表示忽略空白儲存格；FALSE 表示包含空白儲存格。通常設定為 TRUE。
  • 範圍1, [範圍2]…： 您想合併的儲存格範圍或個別儲存格。

實作步驟：

  1. 假設您的資料位於 A1 到 A5 儲存格（例如：A1=蘋果, A2=香蕉, A3=梨子, A4=葡萄, A5=橘子）。
  2. 選取您希望顯示合併結果的目標儲存格，例如 B1。
  3. 輸入以下公式：
    =TEXTJOIN("、", TRUE, A1:A5)
  4. 按下 Enter 鍵。

結果： B1 儲存格將顯示為 “蘋果、香蕉、梨子、葡萄、橘子”。

優點：

  • 可指定任意分隔符號。
  • 可選擇性忽略空白儲存格，避免多餘的分隔符號。
  • 可以合併多個不連續的儲存格範圍。
  • 公式簡潔，易於理解和使用。

缺點：

  • 僅適用於 Excel 2016 及更高版本。

2. 使用 CONCATENATE 函數或 “&” 運算子：傳統且直接

對於舊版 Excel 或不需要複雜分隔符號的情況，CONCATENATE 函數 和 “&” 運算子是常見的選擇。它們本質上都是將多個文字或數字值連接起來。

語法：

CONCATENATE(文字1, [文字2], ...)
或
儲存格1 & "分隔符號" & 儲存格2 & "分隔符號" & 儲存格3...

實作步驟（使用 “&” 運算子）：

  1. 假設您的資料與上述範例相同，位於 A1 到 A5。
  2. 在目標儲存格中輸入：
    =A1&"、"&A2&"、"&A3&"、"&A4&"、"&A5
  3. 按下 Enter 鍵。

結果： B1 儲存格將顯示 “蘋果、香蕉、梨子、葡萄、橘子”。

實作步驟（使用 CONCATENATE 函數）：

  1. 在目標儲存格中輸入：
    =CONCATENATE(A1,"、",A2,"、",A3,"、",A4,"、",A5)
  2. 按下 Enter 鍵。

結果： 與使用 “&” 運算子相同。

優點：

  • 適用於所有 Excel 版本。
  • 對於少量的儲存格合併非常直觀快速。

缺點：

  • 需要手動添加每個儲存格和分隔符號，對於大量儲存格效率低。
  • 無法自動忽略空白儲存格，會導致多餘的分隔符號（例如 “蘋果、香蕉、、葡萄”）。
  • 如果來源資料範圍不連續，操作會更複雜。

3. 透過 Power Query 進行資料合併：進階且彈性

Power Query (在 Excel 2016 及更高版本中內建，舊版需安裝外掛) 是一個強大的資料整理工具，特別適用於重複性的資料合併任務、處理大量資料或需要複雜邏輯的合併。

實作步驟：

  1. 將資料轉換為表格： 選取您要合併的資料範圍（例如 A1:A5），然後到「常用」或「插入」索引標籤，點擊「格式化為表格」（或按 Ctrl + T）。
  2. 載入資料至 Power Query：
    到「資料」索引標籤，在「取得與轉換資料」群組中，點擊「從表格/範圍」。Excel 會打開 Power Query 編輯器。
  3. 群組資料 (若有分類需求)：
    如果您有多個分類需要分別合併（例如按部門合併員工姓名），可以選擇「轉換」索引標籤下的「群組依據」。選擇您要群組的欄位（例如「部門」），並新增一個操作，選擇「所有資料列」作為操作。
  4. 新增自訂欄位進行文字合併：
    在 Power Query 編輯器中，選擇「新增欄」索引標籤，點擊「自訂欄」。
    在新視窗中：

    • 新欄名： 輸入例如 “合併結果”。
    • 自訂欄公式： 輸入公式 Text.Combine([原始欄位名稱], "、")。
      例如，如果您的原始資料欄位名稱是 “產品名稱”，公式就是 Text.Combine([產品名稱], "、")。
      (如果第三步有做群組，這裡的原始欄位名稱會是您選擇的群組欄位，然後展開後再用Text.Combine)。
  5. 載入結果到 Excel：
    完成後，點擊「常用」索引標籤下的「關閉並載入」-> 「關閉並載入至…」，選擇您想放置結果的位置（例如新工作表或現有工作表的某個儲存格）。

優點：

  • 非常適合處理大量資料或需要重複執行的合併任務。
  • 可結合其他 Power Query 轉換步驟（例如篩選、清洗）。
  • 可自動更新，當原始資料變動時，只需重新整理查詢即可。
  • 可以處理更複雜的合併邏輯，例如按類別合併。

缺點：

  • 學習曲線較陡峭，對於初學者可能較為複雜。
  • 對於僅需一次性簡單合併的任務，可能過於繁瑣。

4. 選擇性貼上「轉置」(Transpose)：快速轉換資料方向

這種方法並非將多個儲存格的內容合併到一個儲存格，而是將多行的資料轉換成多列的資料，從而實現「多行變成一行」的視覺效果（每個原始儲存格的內容仍獨立存在於新的行中）。

實作步驟：

  1. 選取您要從多行合併成一行的資料範圍（例如 A1:A5）。
  2. 複製選取的範圍 (Ctrl + C)。
  3. 選取您希望結果開始的目標儲存格（例如 B1）。
  4. 在選定的目標儲存格上按滑鼠右鍵，選擇「選擇性貼上」，然後勾選「轉置」選項，再點擊「確定」。

結果： 原始 A1:A5 的資料將分別顯示在 B1, C1, D1, E1, F1 儲存格中。

優點：

  • 操作簡單，速度快。
  • 保持原始資料的格式（如果選擇性貼上時選擇「保留來源格式設定」）。

缺點：

  • 無法將多個儲存格的內容合併到一個單一的儲存格中。
  • 結果是靜態的，原始資料變動不會自動更新。

5. 運用 VBA 巨集：客製化與自動化

對於需要高度客製化、重複執行或處理大量複雜邏輯的合併任務，編寫 VBA (Visual Basic for Applications) 巨集是最終極的解決方案。

實作步驟：

  1. 開啟 VBA 編輯器： 按下 Alt + F11 鍵。
  2. 插入模組： 在 VBA 編輯器中，點擊「插入」->「模組」。
  3. 輸入 VBA 程式碼： 將以下程式碼貼入新模組中。
    這個範例將 A1 到 A5 範圍的內容，以逗號和空格分隔，合併到 B1 儲存格。

    Sub 合併多行成一行()
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Sheets("工作表1") ' 替換成您實際的工作表名稱
        Dim lastRow As Long
        Dim combinedText As String
        Dim i As Long

        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' 取得A欄最後一列有資料的行號
        combinedText = ""

        For i = 1 To lastRow ' 從第1行遍歷到最後一行
            If ws.Cells(i, "A").Value <> "" Then ' 檢查儲存格是否為空
                combinedText = combinedText & ws.Cells(i, "A").Value & "、"
            End If
        Next i

        ' 移除最後一個分隔符號（如果存在）
        If Len(combinedText) > 0 Then
            combinedText = Left(combinedText, Len(combinedText) - 1)
        End If

        ws.Range("B1").Value = combinedText ' 將結果輸出到B1儲存格
        MsgBox "資料已成功合併！", vbInformation
    End Sub

  4. 執行巨集： 關閉 VBA 編輯器。回到 Excel 工作表，到「開發人員」索引標籤（如果沒有，請到「檔案」->「選項」->「自訂功能區」中勾選「開發人員」），點擊「巨集」，選擇剛才建立的「合併多行成一行」，然後點擊「執行」。

優點：

  • 極高的彈性，可實現任何複雜的合併邏輯。
  • 可自動化重複性任務，提升效率。
  • 適用於處理大量資料和特定條件下的合併。

缺點：

  • 需要具備 VBA 程式設計知識。
  • 對於簡單任務來說過於複雜。
  • 含有巨集的工作簿在儲存時需為 .xlsm 格式。

如何選擇最適合您的方法？

選擇哪種方法取決於您的具體需求、Excel 版本以及資料的複雜程度：

  • 簡單、一次性、少量文字合併 (Excel 2016+): TEXTJOIN 函數 是首選，最為推薦。
  • 簡單、一次性、少量文字合併 (舊版 Excel): 使用 & 運算子 或 CONCATENATE 函數。
  • 僅需將多行資料在視覺上轉為多列： 使用 選擇性貼上「轉置」。
  • 大量資料、重複性任務、需資料清洗或複雜合併邏輯： 學習和使用 Power Query 會帶來巨大效益。
  • 高度客製化、需程式碼控制、與其他 Excel 功能整合： 運用 VBA 巨集。

實用技巧與注意事項

  • 1. 清理資料：

    在合併文字之前，建議先清理原始資料。例如，使用 TRIM 函數 移除多餘的空格，或使用 CLEAN 函數 移除非列印字元，以確保合併結果的整潔。

    範例：=TEXTJOIN("、",TRUE,TRIM(A1),TRIM(A2),TRIM(A3))

  • 2. 分隔符號的選擇：

    根據您的用途選擇合適的分隔符號。常見的包括逗號 (,), 分號 (;), 破折號 (-), 或換行符號 (CHAR(10) – 在文字換行功能開啟下實現)。

  • 3. 錯誤處理：

    如果原始儲存格可能包含錯誤值（例如 #N/A），它們也會被合併進結果。您可以使用 IFERROR 函數 進行處理，例如：=TEXTJOIN("、",TRUE,IFERROR(A1,""),IFERROR(A2,""))。

  • 4. 效能考量：

    對於非常大量的資料（數萬行或更多），大量的 TEXTJOIN 或 CONCATENATE 公式可能會影響 Excel 的效能。此時，Power Query 或 VBA 會是更高效的選擇。

結論

掌握將 Excel 中多行資料合併成一行的技巧，是提升資料處理效率的關鍵一環。從簡單的 TEXTJOIN 函數，到強大的 Power Query 和 VBA，Excel 提供了多種工具來應對不同的需求。建議您根據資料量、操作頻率以及對自動化的要求，選擇最適合自己的方法進行實踐。透過不斷的練習，您將能更靈活地操控 Excel，讓資料為您所用。

常見問題 (FAQ)

  • Q: 如何在 Excel 中合併多行文字並自動添加逗號？
    A: 透過 TEXTJOIN 函數最為簡便。例如，若要合併 A1 到 A5 的內容並以逗號分隔，公式為 =TEXTJOIN(",", TRUE, A1:A5)。TEXTJOIN 的第一個參數就是用來設定您需要的分隔符號。
  • Q: 為何我的 TEXTJOIN 函數在 Excel 中無法使用？
    A: TEXTJOIN 函數是在 Excel 2016 版本中引入的新函數。如果您的 Excel 版本是 2013 或更早，將無法直接使用此函數。此時您可以改用 CONCATENATE 函數或 “&” 運算子，或考慮升級您的 Excel 版本。
  • Q: 合併多行資料時，能否同時保留原始儲存格的文字格式（例如顏色、粗體）？
    A: 大多數情況下，當您使用函數（如 TEXTJOIN, CONCATENATE）或 Power Query 進行文字合併時，只會合併文字內容本身，不會保留原始儲存格的格式。合併後的結果會套用目標儲存格的預設格式。若需保留視覺格式，通常需要手動調整或使用更複雜的 VBA 程式碼。
  • Q: 我想把多行資料變成一行，但不是合併到一個儲存格，而是變成多個儲存格的一行，該怎麼做？
    A: 這種情況下，最簡單快速的方法是使用「選擇性貼上」功能中的「轉置」(Transpose) 選項。先複製您的多行資料，然後在目標位置點擊右鍵，選擇「選擇性貼上」，勾選「轉置」即可。這會將原始的行變成列，列變成行。
  • Q: 我有很多組數據需要合併多行成一行，例如每 5 行合併一次，有辦法自動化嗎？
    A: 對於這種帶有規律的批量合併需求，Power Query 是非常強大的工具。您可以將資料載入 Power Query 編輯器，透過「群組依據」功能並結合自訂欄的 Text.Combine() 函數來實現。如果需求更複雜且重複性高，VBA 巨集將提供最高的靈活性和自動化能力。

excel 如何把多行合併成一行

Similar Posts