在Excel中數(shù)據(jù)從橫向排列到縱向排列的復(fù)雜轉(zhuǎn)換的方法

方法一：用數(shù)據(jù)透視表

1、利用原始數(shù)據(jù)建立數(shù)據(jù)透視表。

按組合鍵“Alt+D”，再按“P”鍵，打開“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А睂?duì)話框，選擇“多重合并計(jì)算數(shù)據(jù)區(qū)域”，單擊“下一步”。

在彈出的對(duì)話框中再次單擊“下一步”，彈出““數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?-步驟2b”，選擇工作表中的A1:G17區(qū)域，單擊“添加”按鈕。

單擊“完成”按鈕，Excel會(huì)在新工作表中建立數(shù)據(jù)透視表。

2、通過數(shù)據(jù)透視表獲取明細(xì)數(shù)據(jù)。

右擊數(shù)據(jù)透視表行總計(jì)和列總計(jì)交叉的單元格，本例為H21，在彈出的快捷菜單中選擇“顯示詳細(xì)信息”(也可雙擊該單元格右下角的填充柄)。

Excel會(huì)自動(dòng)在新工作表中顯示該數(shù)據(jù)透視表數(shù)據(jù)源的明細(xì)數(shù)據(jù)，如圖所示。

3、篩選C列中的非空數(shù)據(jù)，將A至C列數(shù)據(jù)復(fù)制到所需位置即可。

方法二：用數(shù)組公式

假如將轉(zhuǎn)換后的數(shù)據(jù)放在J至L列，在J2單元格輸入數(shù)組公式：

=OFFSET(A$1,SMALL(IF(B$2:G$17="",4^7,ROW(B$1:G$16)),ROW(A1)),)&""

公式輸入完畢按Ctrl+Shift+Enter結(jié)束，下同。然后拖動(dòng)填充柄向下填充公式，直到公式返回空為止。

在K2輸入數(shù)組公式：

=OFFSET(A$1,,SMALL(IF(OFFSET(B$1,MATCH(J2,A$2:A$17,),,,6)<>"",COLUMN($A:$F)),COUNTIF(J$2:J2,J2)))

在L2輸入數(shù)組公式：

=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代碼也可實(shí)現(xiàn)上述轉(zhuǎn)換，方法是按Alt+F11，打開VBA編輯器，在代碼窗口中粘貼下列代碼并運(yùn)行。

Sub 轉(zhuǎn)換()

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