如何在 Excel 中快速將多列堆疊成一列?
在 Excel 中, Text to Columns(文字轉欄) 功能旨在使用指定的分隔符將單列中的數據拆分為多列。然而,如果你需要執行相反的操作——將多列的值合併或堆疊到一個單列中(如下例所示),該怎麼辦呢?這種情況經常出現在整合數據集、準備分析信息或格式化報告以進行進一步處理時。遺憾的是,Excel 中並沒有內建直接垂直堆疊列的功能,但有幾種實用的解決方案可以幫助你高效完成此任務。
使用公式將多列堆疊成一列
如果你不想使用宏或外掛,可以使用包含 INDEX 函數的陣列公式將多列堆疊成單列。此方法適用於動態數據集,或希望避免手動操作的情況。其優勢在於,如果源數據發生變化,公式會自動更新結果,但你需要精確命名範圍,並避免在引用範圍內插入或刪除單元格,否則可能會影響結果。
1. 選擇要堆疊的數據範圍(例如 A1:C4),然後點擊 名稱框 (位於公式欄左側),輸入一個有意義的名稱,如 MyData,然後按下 Enter鍵。這會為你的範圍命名,方便後續引用。
2. 點擊一個空白單元格作為單列堆疊的起始位置——通常是在數據下方或另一個工作表中。將以下公式粘貼到選定的單元格中:
=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)
3. 按下 Enter 確認,然後向下拖動填充柄,直到看到 #REF! 或類似的錯誤提示,表示所有數據都已列出。如有必要,刪除錯誤的單元格即可。
在此公式中,MyData 指代你在步驟 1 中定義的範圍。COLUMNS(MyData) 會根據你的數據列數自動調整。請確保在輸入公式後不要在 MyData 範圍內插入或刪除列,因為這可能會影響結果。如果數據中包含空白單元格,這些空白也會被堆疊——你可以稍後過濾掉它們。對於大範圍數據,拖動填充柄可能耗時;如果左側列有完整數據,可以考慮雙擊填充柄來加快操作。
如果你的 Excel 版本支持動態陣列(Excel 365 或 Excel 2021 及更高版本),可以嘗試以下公式:
=TOCOL(MyData,1)
此公式僅適用於較新版本的 Excel,並且能夠立即將多列堆疊到單列中。
使用 VBA 將多列堆疊成一列
如果你經常需要堆疊列或多一些控制,VBA 宏可以在幾次點擊內自動完成此過程。VBA 非常適合處理大型數據集或重複性任務的進階用戶。但請注意,啟用宏可能存在安全風險,尤其是當你無法確定宏的來源時。運行不熟悉的代碼前,務必先保存文件並保持警惕。
1. 按 Alt + F11 打開 Microsoft Visual Basic for Applications 窗口。
2. 在 VBA 窗口中,點擊 插入 > 模組。將以下代碼複製並粘貼到新模組中:
VBA: 將多列堆疊成一列
Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
Rng.Copy
Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
3. 要運行宏,按 F5 鍵,或點擊 運行 按鈕。運行後,會出現提示——選擇要堆疊的數據範圍,然後點擊 確定。
4. 接著,系統會提示你選擇結果的目標單元格。點擊 OK 再次。列將從目標單元格開始堆疊到單列中。
如果遇到錯誤,請仔細檢查所選範圍,並確保沒有受保護的工作表或合併單元格。若需要更進階的自定義,例如跳過空白或僅堆疊某些列,可以進一步修改代碼。記得在運行 VBA 代碼之前保存工作,以防止意外數據丟失。
使用「轉換區域」將多列堆疊成一列
如果你偏好快速且用戶友好的解決方案,無需編寫公式或代碼,可以使用 Kutools for Excel 的「轉換區域」功能。這個工具特別適合希望通過直觀界面快速處理數據的用戶,例如辦公室管理員或經常處理多維度數據的人員。使用 Kutools 能減少公式錯誤的風險,節省大量時間,但需要安裝外掛。
免費安裝 Kutools for Excel 後,按照以下步驟操作:
1. 選擇要堆疊為單列的列或數據範圍。然後點擊 Kutools > 區域 > 轉換區域 以打開對話框。
2. 在 轉換區域 對話框中,選擇 區域轉單列 選項,然後點擊 OK。接著,選擇一個單元格作為結果顯示的位置。
3. 最後,點擊 OK。選定的列將立即堆疊到單列中,並顯示在你選擇的位置。
此方法簡單直觀且不易出錯。如果結果不正確,可以輕鬆撤銷更改(Ctrl + Z),或根據不同數據集重新運行「轉換區域」。如果數據包含合併單元格,最好在使用此功能前取消合併,以避免意外結果。
使用 Power Query 將多列堆疊成一列
Power Query(在某些 Excel 版本中也稱為 Get & Transform)是另一種將多列堆疊成單列的實用方式,特別適合處理大批量數據或需要自動化重複數據轉換任務的用戶。Power Query 內建於 Excel 2016 及更高版本中,而在 Excel 2010 和 2013 中則可作為免費外掛使用。它最適合用於高級數據準備,並能通過簡潔的用戶界面輕鬆處理複雜的重塑操作。不過,初次使用者需要一定的學習曲線。
將源表格加載到 Power Query 中。在 Excel 中選擇你的數據,然後前往 數據 > 從表格/範圍 打開 Power Query 編輯器。
2. 在查詢預覽中選擇所有列。前往 轉換 頁籤,在 表格 組中點擊 轉置。這會翻轉行和列。
3. 再次選擇所有轉置後的列。在 轉換 頁籤下,於 任何列 組中點擊 展開列。
4. 如果不需要「屬性」列,可以右鍵點擊「屬性」列標題 > 選擇 移除。
5. 點擊 關閉並載入 將結果傳回 Excel,生成新的工作表或表格。隨時可以刷新或重新應用轉換,如果你的源數據發生變化。
Power Query 允許你將此堆疊過程保存為可重用的查詢,並在未來數據變化時快速更新結果。
注意:此方法可能不會保留源數據中的原始列標題信息。
總結來說,這些方法各有優勢:公式適合動態更新數據,VBA 優秀於自動化重複性任務,Kutools for Excel 提供了點擊操作的便捷性,技術門檻低,而 Power Query 適合進階或重複性數據轉換。處理後務必檢查堆疊數據中的空白、合併單元格或格式錯誤。如果出現錯誤或意外結果,請仔細檢查定義的範圍或步驟選擇。結合這些方法,將有助於你高效管理和轉換 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% 的工作效率,每天為你大量減少滑鼠點擊次數!