Excel中数据从横向排列到纵向排列的复杂转换的几种方法

                        在Excel中将横向排列的数据转换为纵向排列，在上文中，我们讲解了通常用的方法：Excel中数据从横向排列到纵向排列的转换的方法，实际上那是通过选择性粘贴的转置功能实现的简单将横向排列的数据转换为纵向排列，但很多情况下无法使用“转置”的功能，例如下图A1:G17区域为某网店部分商品的库存数量，“商品尺寸”是横向排列的，现在需要将这些数据按纵向排列，即将“商品尺寸”及其对应的“货号”、“数量”排列到三列中，如图J至L列所示。

       

        本文介绍用数据透视、数组公式和VBA等三种方法来实现这种横向排列的数据转换为纵向排列，以在Excel 2010操作为例，范例文件下载：百度网盘
        方法一：用数据透视表
        1、利用原始数据建立数据透视表。
        按组合键“Alt+D”，再按“P”键，打开“数据透视表和数据透视图向导”对话框，选择“多重合并计算数据区域”，单击“下一步”。

       

        在弹出的对话框中再次单击“下一步”，弹出““数据透视表和数据透视图向导--步骤2b”，选择工作表中的A1:G17区域，单击“添加”按钮。

       

        单击“完成”按钮，Excel会在新工作表中建立数据透视表。
        2、通过数据透视表获取明细数据。
        右击数据透视表行总计和列总计交叉的单元格，本例为H21，在弹出的快捷菜单中选择“显示详细信息”(也可双击该单元格右下角的填充柄)。

       

        Excel会自动在新工作表中显示该数据透视表数据源的明细数据，如图所示。

       

        3、筛选C列中的非空数据，将A至C列数据复制到所需位置即可。
        方法二：用数组公式
        假如将转换后的数据放在J至L列，在J2单元格输入数组公式：
        =OFFSET(A$1,SMALL(IF(B$2:G$17="",4^7,ROW(B$1:G$16)),ROW(A1)),)&""
        公式输入完毕按Ctrl+Shift+Enter结束，下同。然后拖动填充柄向下填充公式，直到公式返回空为止。
        在K2输入数组公式：
        =OFFSET(A$1,,SMALL(IF(OFFSET(B$1,MATCH(J2,A$2:A$17,),,,6)<>"",COLUMN($A:$F)),COUNTIF(J$2:J2,J2)))
        在L2输入数组公式：
        =OFFSET(A$1,MATCH(J2,A$2:A$17,),SMALL(IF(OFFSET(B$1,MATCH(J2,A$2:A$17,),,,6)<>"",COLUMN($A:$F)),COUNTIF(J$2:J2,J2)))
        然后选择K2:L2，双击填充柄将公式填充到这两列的其余单元格。

       

        方法三、用VBA
        用下面的VBA代码也可实现上述转换，方法是按Alt+F11，打开VBA编辑器，在代码窗口中粘贴下列代码并运行。
        Sub 转换()
        Dim Arr1, Arr2()
        Dim Rnum As Integer, Cnum As Integer, Tnum As Integer
        Dim i As Integer, j As Integer, k As Integer
        Application.ScreenUpdating = False
        Rnum = [A65536].End(xlUp).Row
        Cnum = 7
        Tnum = Rnum * Cnum
        Range("J2:L" & Tnum).ClearContents
        Arr1 = Range("A1:G" & Rnum)
        ReDim Arr2(1 To Tnum, 1 To 3)
        For i = 2 To Rnum
        For j = 2 To Cnum
        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