如何在 Excel 中將儲存格內容拆分到多行(情境 + 工具比較)
本指南將引導您如何在 Excel 中將單一儲存格的內容拆分為多行。內容涵蓋了兩種常見的情境:一種僅分割目標儲存格(忽略其他列),另一種則是在保留整行數據的情況下進行分割。我們提供了多種方法來有效處理這些情況,從內建的 Excel 功能到進階工具如 Power Query、VBA 和第三方插件等。
兩種常見的分割情境
根據是否需要保留數據集中的其他列,分割數據可以有多種形式。這裡我們定義了兩種主要方式,您可以根據需求選擇最適合的方法。
情境 1:僅分割目標儲存格(忽略其他列)
有時候,您只關心單一儲存格中的值。您希望將它們拆分為多行以便於閱讀,而無需複製該行中其他相關數據。
範例
A,B,C
結果:
A
B
C
使用案例:輕量數據清理或顯示任務,其他列無關緊要。
情境 2:分割儲存格並保留其他列數據
在其他情況下,您需要讓每個分割值與原始行的數據配對——實際上是將一行展開為多個條目。
範例
美國 | A,B,C
結果:
美國 | A
美國 | B
美國 | C
使用案例:數據正規化,維持列之間關係至關重要。
- 方法 1:Excel 內建功能(文字轉欄 + 轉置貼上選項)
- 方法 2:TRANSPOSE(TEXTSPLIT) 函數(Microsoft 365)
- 方法 3:Power Query – 分割並展開行(Microsoft 2016 及更高版本)
- 方法 4:Kutools 插件 – 點擊即可分割
- 方法 5:VBA 腳本 – 完全自訂控制
何時需要將儲存格拆分成多行?
在深入了解方法之前,了解為什麼需要分割儲存格是很重要的。這項操作通常是數據清理、重構或可視化工作流程的一部分。
典型的使用案例包括:
- 單一列中的多個值:例如標籤、ID 或地址一起存儲。
- 樞紐分析表或圖表:更適合正規化的數據格式。
- 導入數據清理:特別是來自 CRM 匯出或調查工具的數據。
- 數據標準化:使表格更易於數據庫和分析準備。
拆分儲存格的逐步方法
現在您已經知道自己的情境,讓我們探索不同的方法來執行分割——從內建功能到進階自動化。選擇最適合您技術水平和使用頻率的方法。
方法 1:Excel 內建功能(文字轉欄 + 轉置貼上選項)
這是一種簡單直觀的手動方法,適用於一次性操作,僅使用 Excel 的內建功能。
適用於:情境 1 僅分割目標儲存格(忽略其他列)
步驟:
第一步:選擇儲存格。
第二步:選擇資料 > 文字轉欄。
第三步:在「將文字轉換成欄」向導中,
選擇分隔符號,點擊下一步。
選擇逗號,點擊下一步。
選擇目標儲存格,點擊完成。
現在該儲存格已按逗號拆分成多列。
第四步:選擇分割後的儲存格,按下 Ctrl + C 鍵複製它們。右鍵點擊目標儲存格,在貼上選項中,選擇轉置圖標。
內容被轉置為多行。
優勢 | 劣勢 |
|
|
方法 2:TRANSPOSE(TEXTSPLIT)) 函數(Microsoft 365)
此方法結合了 TEXTSPLIT 函數(根據分隔符號拆分文字)與 TRANSPOSE,即時以垂直方式在多行中顯示拆分的值。
適用於:情境 1 僅分割目標儲存格(忽略其他列)
步驟:
第一步:在您希望垂直列表開始的空白儲存格中輸入公式。
公式範例:A1 是用來拆分到多行的目標儲存格。
在此情況下,公式為:
第二步:按下 Enter 鍵。
優勢 | 劣勢 |
|
|
方法 3:Power Query – 分割並展開行(Microsoft 2016 及更高版本)
Power Query 適合結構化、可重複的操作。它功能強大、可刷新,非常適合批量數據轉換。
適用於:情境 1(僅分割目標儲存格)和情境 2(分割儲存格並保留其他列數據)
步驟:以情境 1(僅分割目標儲存格)為例
第一步:選擇範圍,前往資料 > 取得與轉換 > 從表格/範圍。
一個
第二步:在建立表格中,點擊確定。(如果所選範圍包含標題,勾選我的表格有標題。)
第三步:在 Power Query 編輯器中,使用分隔符號分割列。
第四步:選擇或輸入分隔符號,然後點擊進階以展開選項,選擇分割到多行,然後點擊確定。
第五步:選擇關閉並載入 > 關閉並載入或關閉並載入至。
關閉並載入
功能:直接將查詢結果載入預設位置。
關閉並載入至...
功能:提供可自訂的載入選項,允許用戶選擇數據的存儲位置和方式。最適合:定期報告、大型數據集。
相同步驟適用於情境 2(分割儲存格同時保留其他列數據)- 讓我們查看結果。
目標儲存格範圍:
結果:
注意:在 Power Query 編輯器窗口中,僅選擇要拆分為多行的目標列,然後在分割列下點擊依分隔符號。
優勢 | 劣勢 |
|
|
提示:添加修剪和清潔步驟以修復空格或特殊字符。
方法 4:Kutools 插件 – 點擊即可分割
如果您經常拆分儲存格並且偏好基於 GUI 的解決方案,Kutools 是一款出色的工具。它將複雜操作簡化為幾次點擊。
適用於:情境 1(僅分割目標儲存格)和情境 2(分割儲存格並保留其他列數據)
步驟:情境 1(僅分割目標儲存格)
第一步:選擇目標儲存格。
第二步:前往 Kutools > 合併與分割 > 分割儲存格。
第三步:選擇分割到多行並定義分隔符號。點擊確定。/p>
第四步:選擇目標儲存格。點擊確定。
步驟:情境 2(分割儲存格並保留其他列數據)
第一步:選擇要拆分到多行的列。
第二步:前往 Kutools > 合併與分割 > 拆分數據到多行。
第三步:定義分隔符號。點擊確定。
相同步驟適用於情境 2(分割儲存格同時保留其他列數據)- 讓我們查看結果。
目標儲存格範圍:
結果:
優勢 | 劣勢 |
|
|
注意:30 天試用版提供所有功能供測試。
方法 5:VBA 腳本 – 完全自訂控制
對於有編程知識的用戶,VBA 提供了極致的靈活性。您可以根據確切的數據佈局和業務規則定制腳本。
適用於:情境 1(僅分割目標儲存格)和情境 2(分割儲存格並保留其他列數據)
步驟:
第一步:按下 Alt + F11 打開 VBA 編輯器。
第二步:點擊插入 > 模塊。
第三步:複製並粘貼以下 VBA。
情境 1 僅分割目標儲存格
'Update by Extendoffice
Sub SplitCellToRows_Simple()
Dim InputCell As Range
Dim Values As Variant
Dim i As Long
Set InputCell = Range("A1") ' change A1 to your target cell
Values = Split(InputCell.Value, ",")
For i = 0 To UBound(Values)
InputCell.Offset(i, 1).Value = Trim(Values(i))
Next i
End Sub
情境 2 分割儲存格並保留其他列數據
'Update by Extendoffice
Sub SplitRowsWithOtherColumns()
Dim ws As Worksheet
Dim LastRow As Long, i As Long
Dim arr As Variant, j As Long
Dim SplitVals As Variant
Dim CurrentRow As Long
Set ws = ActiveSheet
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 2 Step -1
If InStr(ws.Cells(i, 2).Value, ",") > 0 Then
SplitVals = Split(ws.Cells(i, 2).Value, ",")
For j = UBound(SplitVals) To 1 Step -1
ws.Rows(i + 1).Insert Shift:=xlDown
ws.Cells(i + 1, 1).Value = ws.Cells(i, 1).Value
ws.Cells(i + 1, 2).Value = Trim(SplitVals(j))
Next j
ws.Cells(i, 2).Value = Trim(SplitVals(0))
End If
Next i
End Sub
注意:此 VBA 腳本專門配置為當您的數據位於 A 列到 B 列,且要拆分的值位於 B 列時使用。
劣勢 |
|
💡提示:
- 運行宏之前務必備份數據。
- 在腳本中替換 "," 為實際的分隔符號(例如 ";" 表示分號或 "|" 表示管道符號),以匹配數據格式。
常見問題
- 空白行或列:通常由尾部分隔符或隱藏空格引起。
- 合併儲存格錯誤:開始前請取消合併儲存格。
- Power Query 未刷新:記住修改源數據後點擊刷新。
- 錯誤的分隔符:分號與逗號的誤差可能會導致整個結果偏差。
相關文章:
最佳 Office 辦公效率工具
🤖 | Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入... | |
高級 LOOKUP:多條件查找|多值查找|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)... | |
前15 大工具集:12 款文本工具(添加文本,刪除特定字符,...)|50+ 種圖表 類型(甘特圖,...)|40+ 實用 公式(基於生日計算年齡,...)|19 款插入工具(插入QR码,按路徑插入圖片,...)|12 款轉換工具(金額轉大寫,匯率轉換,...)|7 款合併和分割工具(高級合併行,分割儲存格,...)| ...以及更多 |
利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...
Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆
- 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
- 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
- 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!