跳到主要內容

如何在Excel中快速將多列堆疊為一列?

在Excel中,“文本到列”功能可以基於定界符將單元格拆分為多列,但是是否有任何方法可以將多列堆疊為一列,如下面的屏幕截圖所示? 在本文中,我將介紹一些快速解決此任務的技巧。
文檔堆棧列為1

用公式將多列堆疊為一

使用VBA將多列堆棧為一

使用Transform Range將多列堆疊為一列 好主意3


用公式將多列堆疊為一

1.選擇要堆疊的範圍,然後轉到 名稱框 為該範圍命名,然後按 Enter 鍵。 看截圖:
文檔堆棧列為2

2.然後選擇要放置結果的空白單元格,輸入此公式 =INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1), 按 恩特r鍵並向下拖動填充手柄,直到顯示錯誤。 看截圖:
文檔堆棧列為3

在公式中,MyData是您在步驟1中指定的範圍名稱。

使用VBA將多列堆棧為一

這是可以幫助您的VBA代碼。

1。 按 Alt + F11鍵 顯示鍵 Microsoft Visual Basic for Applications 窗口。

2。 點擊 插入 > 模塊,將以下代碼粘貼到 模塊.

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

文檔堆棧列為4

3。 按 F5 鍵運行代碼,彈出對話框選擇源數據,然後單擊 OK,以選擇要放置結果的單元格。 看截圖:
文檔堆棧列為5

4。 點擊 OK。 現在,這些列已被堆疊為一列。
文檔堆棧列為6


使用Transform Range將多列堆疊為一列

申請 Excel的Kutools變換範圍 實用程序也可以幫助您快速解決此問題。

Excel的Kutools, 與以上 300 方便的功能,使您的工作更加輕鬆。 

免費安裝 Kutools for Excel,請執行以下操作:

1.選擇您使用的列數據,然後單擊 庫工具 > 範圍 > 變換範圍。 看截圖:
文檔堆棧列為8

2。 在裡面 變換範圍 對話框,檢查 範圍到單列 選項,然後單擊 Ok,然後選擇一個單元格以放置結果。 看截圖:
文檔堆棧列為9

3。 點擊 OK。 現在,這些列已被堆疊為一個列。
文檔堆棧列為1

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (25)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
For those looking to stack columns into one but not rows into one, you need to have a clear picture of what you really want to do.Suppose you have some 3x3 array, you want your index() function to get entries in the form of (row,column) for (1,1), (2,1), (3,1), then (1,2), (2,2), (3,2) then so on.So what you want to do is to have index() refer to a single column containing all the row numbers (1;2;3;1;2;3;1;2;3) and a single column containing all the column numbers (1;1;1;2;2;2;3;3;3).To get (1;2;3;1;2;3;1;2;3), a common programming approach is to use 1+mod(some_counter-1,3) where the mod() function gives the residuals 0,1,2,0,1,2,...
To get (1;1;1;2;2;2;3;3;3), one uses 1+int((some_counter-1)/3) where it gives 1+0, 1+0, 1+0, 1+1, 1+1, 1+1, 1+2, 1+2, 1+2
Therefore, to get a 3x3 array stacked into one, you use the formula (press Ctrl +Shft + Enter):=index( $A$1:$C$3, 1+mod(row(A1)-1,3), 1+int((row(A1)-1)/3)))where row(A1) serves as the counter as you fill your formula downwards
If you put it the other way round (row to columns, columns to row), you will get rows stacked into one column=INDEX( $A$1:$C$3, 1+INT((ROW(A1)-1)/3),1+MOD(ROW(A1)-1,3))which is essentially what the formula in this article does

In a nutshell, the formula used to stack columns in an array into a single column would be:=index( your_array_cells , 1+mod(row(A1)-1, number_of_rows_of_your_array ), 1+int((row(A1)-1)/ number_of_columns_of_your_array)))
This comment was minimized by the moderator on the site
Thank you for the formula and VBA, but neither one does what the title of the post says. I was expecting them to stack the columns, but instead they transpose each row into a column and stacks those... so it's stacking transposed rows, not columns. Still very helpful for some cases, but does anyone know how to modify the formula and/or VBA to actually stack the columns? I suppose I could transpose the entire input range then use these...
This comment was minimized by the moderator on the site
Thank you, a lot, the formula option worked very well.. your saved my lot of time.
This comment was minimized by the moderator on the site
I am using the Stack Multiple Columns Into One With Formula method and it worked perfectly, but I want it to maintain the formatting, i.e. some columns were bold & 1 was hyperlinks. Is there something that can be added to the formula to make this happen?
This comment was minimized by the moderator on the site
Hi, Susan Milard, their formula can meet your need, you can use the VBA or the Transpose Range tool,both of them can satisfy you.
This comment was minimized by the moderator on the site
I am attempting to do this over a very large table (100s of columns and rows that are all linked to formulas. My excel will keep loading and then ultimately go to not responding. Is there a way to get past this? Thanks
This comment was minimized by the moderator on the site
Hi, Tom, which method you apply does not work? The method three, transform range tool must can work.
This comment was minimized by the moderator on the site
Hi, I'm looking to go a step further with this. I've used Stack Multiple Columns Into One With VBA, code and this works. I've used Tables as the source with this code and I get the list. Is there a way to automate running the Macro? So that when I add a new entry into the table the Macro automatically runs and keeps the resulting list up to date?
This comment was minimized by the moderator on the site
Hi, Chris Blackburn, sorry I am affraid I cannot find a code can automatically run to update the result, maybe someone else can.
This comment was minimized by the moderator on the site
Hi, is there a way to make the formula ignore any blank cells your data may contain?


=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1),


I am using a helper sheet to create a contiguous list for a data validation drop down list. Thanks in advance.
This comment was minimized by the moderator on the site
Sorry, I am not good at formula, maybe you can try to use Kutools for Excel's Select Nonblank cells utility to select the unempty cells firstly, and copy and paste them to another location, then use above methods.
This comment was minimized by the moderator on the site
I have data which ends at the same row but multiple columns in which I want them to be stacked in a sequence where B column data will go under A column data where the data ends for A column and C column data to go under A column data where the data from B column ends and so on. This way I will have data from multiple columns to fit into one column. How can I do that?
This comment was minimized by the moderator on the site
Hello , is there any to convert above data to following form ??
1
A
Lilly
2
B
Judy
...
This comment was minimized by the moderator on the site
Thanks for your leaving message, this tutorial https://www.extendoffice.com/documents/excel/5401-excel-stack-columns-into-one-column.html
is talking about how to solve this problem, you can go to view.
This comment was minimized by the moderator on the site
this is just a clickbait to have you download a paying software
This comment was minimized by the moderator on the site
The first download will be free to use in 60 days, after that, you can decide to pay for use or not.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations