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

excel橫向變縱向 Excel中數(shù)據(jù)從橫向排列到縱向排列的復雜轉(zhuǎn)換的幾種方法，我們在工作中會遇到很多文章，像excel橫向變縱向這類問題應該大家也都遇到過吧，今天這篇excel橫向變縱向 Excel中數(shù)據(jù)從橫向排列到縱向排列的復雜轉(zhuǎn)換的幾種方法的文章，專門為你解答了這個問題，相信你看完后一定會收獲很多！

在Excel中將橫向排列的數(shù)據(jù)轉(zhuǎn)換為縱向排列，在上文中，我們講解了通常用的方法：Excel中數(shù)據(jù)從橫向排列到縱向排列的轉(zhuǎn)換的方法，實際上那是通過選擇性粘貼的轉(zhuǎn)置功能實現(xiàn)的簡單將橫向排列的數(shù)據(jù)轉(zhuǎn)換為縱向排列，但很多情況下無法使用“轉(zhuǎn)置”的功能，例如下圖A1:G17區(qū)域為某網(wǎng)店部分商品的庫存數(shù)量，“商品尺寸”是橫向排列的，現(xiàn)在需要將這些數(shù)據(jù)按縱向排列，即將“商品尺寸”及其對應的“貨號”、“數(shù)量”排列到三列中，如圖J至L列所示。

本文介紹用數(shù)據(jù)透視、數(shù)組公式和VBA等三種方法來實現(xiàn)這種橫向排列的數(shù)據(jù)轉(zhuǎn)換為縱向排列，以在Excel 2010操作為例，范例文件下載：百度網(wǎng)盤

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

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

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

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

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

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

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

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

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

方法二：用數(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結束，下同。然后拖動填充柄向下填充公式，直到公式返回空為止。

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

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