Excel中将横向数据转换为纵向数据的几种方法（先横后竖）
2016-08-05 12:56阅读：
源数据：
一、数据透视表法
1、在Excel中按下快捷键Alt+D+P出现下图对话框，点击“创建单页字段”，添加A1:G15数据区域，创建数据透视表。
2、创建的数据透视表如下图，双击行总计与列总计的交叉位置（如图45的单元格）或者右击--显示详细信息，筛选C列中的非空数据并复制到其他地方即可。
先竖后横排列数据只需将数据先在Excel里面转置或者在数据透视表里将行列字段对调即可。
------------------------------------------------------------------------------------------------
二、公式法
I1单元格中输入公式：
=OFFSET(A$1,SMALL(IF($B$2:$G$15<>'',ROW($B$2:$G$15)-1,4^8),ROW(A1)),)&''
J1单元格中输入公式：
=OFFSET(A$1,,SMALL(IF(OFFSET(B$1,MATCH($I1,$A$2:$A$15,),,,6)<>'',COLUMN($B$2:$G$15)-1),COUNTIF(I$1:I1,I1)))&''
K1单元格中输入公式：
=OFFSET(A$1,MATCH($I1,$A$2:$A$15,),SMALL(IF(OFFSET(B$1,MATCH($I1,$A$2:$A$15,),,,6)<>'',COLUMN($B:$G)-1),COUNTIF(I$1:I1,I1)))&''
或者使用INDEX(引用区域,MATCH(),MATCH())即可
公式输入完成后按Ctrl+Shift+Enter结束，结果如下图。
先竖后横排列数据只需将行ROW列COLUMN互换
=OFFSET(A$1,,SMALL(IF($B$2:$G$15<>'',COLUMN($B$2:$G$15)-1,2^8),ROW(A1)),)&''
=OFFSET(A$1,SMALL(IF(OFFSET(A$2,,MATCH($I1,$B$1:$G$1,),14,)<>'',ROW($B$2:$G$15)-1),COUNTIF(I$1:I1,I1)),)&''
=OFFSET(A$1,SMALL(IF(OFFSET(A$2,,MATCH($I1,$B$1:$G$1,),14,)<>'',ROW($B$2:$G$15)-1),COUNTIF(I$1:I1,I1)),MATCH($I1,$B$1:$G$1,))&''
------------------------------------------------------------------------------------------------
三、转置法
复制B2:G15粘贴为文本到Word中，然后替换（查找内容：^t，替换为：^p，如下图)，将结果复制到Excel中，定位空值并删除即可得到结果。
先竖后横排列数据只需将数据先在Excel里面转置再在Word里替换即可
------------------------------------------------------------------------------------------------
四、微软插件PowerQuery逆透视法(插件集成到Excel2016版)
点击'PQ-从文件-从Excel--编辑'进入'查询编辑器'视窗。点击'转换-->将第一行作为标题和逆透视咧',整理数据，得到结果并加载至Excel中即可。
先竖后横排列数据只需将数据先在Excel里面转置再使用PQ或者在PQ里转置即可
------------------------------------------------------------------------------------------------
五、VBA法
Sub transfer()
Dim arr1, arr2, Row_ As Integer, Col_ As Integer, Total As
Integer
Dim i As Integer, j As Integer, k As Integer
Application.ScreenUpdating = False
Row_ = Range('A' & Rows.Count).End(xlUp).Row
Col_ = Cells(1, Columns.Count).End(xlToLeft).Column
Total = Row_ * Col_
Range('J2:L' & Total).ClearContents
arr1 = ActiveSheet.UsedRange.Value
ReDim arr2(1 To Total, 1 To 3)
For i = 2 To Row_
For j = 2 To Col_
If arr1(i, j) <> '' Then
k = k + 1
arr2(k, 1) = arr1(i, 1)
arr2(k, 2) = arr1(1, j)
arr2(k, 3) = arr1(i, j)
End If
Next
Next
Range('J2').Resize(k, UBound(arr2, 2)) = arr2
Application.ScreenUpdating = True
End Sub
先竖后横排列数据只需将
For i = 2 To Row_和For j = 2 To
Col_对调，arr1(i, 1)和arr1(1, j)对调即可
