excel - 将数据从行转置到单列

Question

我正在尝试这个公式来转置给定的数据，但它也采用空白单元格，我如何在转置时忽略它们

result wanted worksheet ss
这是工作表 ss 我想在 A 列中转置和粘贴数据

B01MU6O7H7                              
B07XB9NN9B  B07261QWHY  B071W4GMN3  B07X8BPD82  B07X8BNJZQ  B07X8BNBJH          
B071JLW811  B071WK2YKV  B071WK2QHN  B072JTCJF8  B071G11SR7  B072QCCV2Q  B0743JHJBH      
B078GVQFB5  B078GQ9V6W  B078GTFHMY  B078GR4H15  B079KFH765  B078GTXD9N  B078GPVH73      
B078G6515S                              
B07T891H6J  B07T9DFRSM  B07T893RJM  B07TFHJ1XR  B07T9DGB2V  B07TFHJ6ZX  B07TBFC852      
B01N2WJ0OR  B01MQYNB3M  B06Y3Z65C5  B01MQZU45F                  
B076YFYD19  B076YF2ZNY  B074Z9ZY1S  B076XZ9WZV  B079KSDHSQ  B079KQJHZD  B074ZK64V3      
B07XJYL5Y2  B07XL3Y773                          
B07FCQTZ5X  B06XZ7Z93Z                          
B07MN7YHLM  B07M9HGJWP  B07MK98FJ5  B07M9HGN5D  B01NCVGDIC  B01N4NBSV9  B07MN8YKFQ  B074MZ93JP  B01N7RH9ZB
B07TKXWLFZ  B071CMQ6N2  B07VG1L2M5                      
B01B0SR1IY                              
B07GZFZQ6H  B07GZHSBRT  B07GZHG64J  B07GZDQ7QW                  
B07WLX685Q  B07WF3MQPB  B07WD3CHDW  B07W9KXP9Q  B07WG787XB  B07WD3BCDR          
B07J2K4WCV  B07J2MGH5W  B07J2L9MZS  B07J2LF71R                  
B07F9VP9QM  B07F9ZLCZW  B07FB1XZGL                      

Sub ConvertRangeToColumn()
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

Answer 1

如果你有 Excel 2010+，你可以使用 Power Query (又名 Get & Transform 在 2016 年以上)。

选择表格中的一个单元格

在 2016 年，您将导航到 Data标签;然后选择Get & Transfrom来自 Table/Range

在打开的 PQ 编辑器中:

选择所有列

来自 Transform选项卡，选择 Unpivot Columns

删除Attribute专栏

关闭并加载

M码: *由PQ生成**

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

结果