如何在 Excel 中將列清單轉換為逗號分隔的清單?
在處理 Excel 數據時,您可能經常需要將垂直排列的項目列表轉換為單一的、以逗號分隔的字串。無論是準備資料庫的輸入、製作報表,還是僅僅整理工作表,有許多方法可以實現這一點。下面,我們將探討四種優化的方法:
使用 Kutools for Excel 將列清單轉換為逗號分隔的清單
使用公式將列清單轉換為逗號分隔的清單
Excel 不包含從列到逗號分隔列表的功能,但您可以輕鬆實現:在 Office 365/2019+ 中使用 TEXTJOIN,或在任何版本中使用 CONCAT + IF 來簡化您的數據。
公式 1:TEXTJOIN 函數(Excel 2019 及更高版本,Office 365)
作為 Excel 最先進的連接函數,TEXTJOIN 通過其雙重功能——靈活的分隔符選擇和自動空單元格管理——為現代 Excel 使用者提供了最簡單的解決方案。
1. 選擇您希望逗號分隔清單出現的單元格,並輸入以下公式:
=TEXTJOIN(", ", TRUE, A2:A10)
2. 按下 Enter 鍵以獲取逗號分隔的值。
- ", " 是分隔符(逗號加空格)。
- TRUE 告訴 Excel 跳過空白單元格。
- A2:A10 是您的原始範圍。
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A10))
- 🔹簡單實現:只需一個函數即可完成。
- 🔹靈活的分隔符:可按需使用 ", "(逗號加空格)或僅使用","。
- 🔹自動處理:當設置 ignore_empty 為 TRUE 時跳過空白單元格。
- 🔸需要 Excel 2019 及更高版本 / Office 365。
公式 2:CONCAT 和 IF 函數(所有 Excel 版本)
在 TEXTJOIN 出現之前,您可以結合 CONCAT 和 IF 函數來組裝列表,然後去除前面的分隔符。
1. 選擇您希望逗號分隔清單出現的單元格,並輸入以下公式:
=CONCAT(IF(A2:A10<>"", A2:A10 & ", ", ""))
2. 同時按下 Ctrl + Shift + Enter 鍵以獲得結果。
- IF(A2:A100<>"", A2:A100 & ",", ""):檢查 A2:A100 中的每個單元格。如果單元格不為空,則返回其值加上逗號;否則返回空字符串。
- CONCAT(...): 將該陣列中的所有元素合併成一個連續的字串。
- 🔹廣泛兼容性:適用於支持陣列公式的任何 Excel 版本,無需依賴較新的函數。
- 🔹忽略空白單元格:IF 檢查會自動跳過空白單元格。
- 🔸操作繁瑣:必須作為陣列公式輸入,要求用戶按下 Ctrl + Shift + Enter——這是許多初學者容易忘記的操作。
- 🔸性能差:處理大數據集(數萬行)時計算速度可能會顯著減慢甚至卡住。
- 🔸固定結果:若要刪除最後的分隔符,您需要額外步驟。
使用 Kutools for Excel 將列清單轉換為逗號分隔的清單
Kutools for Excel 包含了一個強大的「合併行/列/儲存格並保留值」工具,讓您只需幾次點擊即可將整個列的內容合併到單一儲存格中。通過選擇源範圍並指定分隔符——無論是逗號、分號、空格還是任何自定義符號,您可以立即生成一個整合後的、由分隔符分開的列表,而無需手動複製粘貼每個項目。該工具還提供忽略空白單元格、保留原始格式等選項,使其成為快速將垂直數據轉換為整潔的逗號分隔列表的理想解決方案。
1. 選擇您要轉換為逗號分隔清單的列,然後點擊 Kutools > 合併與拆分 > 合併行/列/儲存格並保留值。
2. 在彈出的「合併列或行」對話框中,您需要進行以下操作:
- (1) 在「根據以下選項合併所選單元格」部分,勾選「合併到單一儲存格」選項。
- (2) 在「指定分隔符」部分,勾選「其他分隔符」選項,並輸入逗號 ", "。
- (3.) 選擇如何處理原始單元格數據。(您可以保留原始單元格的內容,或者刪除原始單元格中的內容。)
- (4.) 在「放置結果到」部分,選擇「其他單元格」選項,然後點擊選擇一個單元格來輸出結果。
- (5.) 最後,點擊「確定」按鈕。
結果將插入到您指定的目標單元格中。
- 🔹極其易於使用。
- 🔹無需公式或代碼。
- 🔹處理大範圍數據流暢。
- 🔹合併行、列或範圍,使用任何分隔符、保留格式,並選擇輸出位置。
- 🔸需要安裝。
使用 VBA 程式碼將列清單轉換為逗號分隔的清單
另外,如果您想自動化此任務或需要處理更大、動態變化的範圍,您可以編寫一個 VBA 宏,該宏遍歷指定列中的每個單元格,附加其非空值後跟逗號(或您選擇的任何分隔符),在末尾修剪掉多餘的分隔符,然後將完全合併後的、以逗號分隔的字串輸出到目標單元格中。
1. 按住 Alt 和 F11 鍵打開 Microsoft Visual Basic for Applications 窗口。
2. 點擊 Insert > Module,並將 VBA 程式碼複製到模組中。
VBA:將列清單轉換為逗號分隔的清單
Sub ChangeRange()
' Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Dim outStr As String
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Select source range:", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If Len(Trim(rng.Value)) > 0 Then
If outStr = "" Then
outStr = rng.Value
Else
outStr = outStr & ", " & rng.Value
End If
End If
Next rng
OutRng.Value = outStr
End Sub
3. 點擊 Run 按鈕或按 F5 鍵運行 VBA。屏幕上會顯示一個對話框,您可以選擇要轉換的列清單。詳見截圖:
4. 點擊 OK,然後另一個對話框會彈出讓您選擇一個單元格。詳見截圖:
5. 點擊 OK,並且列清單中的所有值都已轉換為單元格內以逗號分隔的列表。
結論:
- 為了簡便和性能,請在 Excel 2019/365 中使用 TEXTJOIN。
- 為了兼容性,CONCAT + IF 陣列方法適用於所有版本。
- 對於一次性任務且不使用公式,Kutools 提供了即點即用的解決方案。
- 對於全面自動化或複雜規則,VBA 宏是最靈活的選擇。
選擇符合您 Excel 版本、任務頻率以及對插件或 VBA 的熟悉程度的方法。有了這些優化技術,將任何列轉換為整潔的、以逗號分隔的列表變得輕而易舉。如果您對探索更多 Excel 技巧感興趣,我們的網站提供了數千篇教程,幫助您精通 Excel。
相關文章:
最佳 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% 的工作效率,每天為你大量減少滑鼠點擊次數!