excel橫向變縱向 Excel中數據從橫向排列到縱向排列的復雜轉換的幾種方法

excel橫向變縱向 Excel中數據從橫向排列到縱向排列的復雜轉換的幾種方法，我們在工作中會遇到很多文章，像excel橫向變縱向這類問題應該大家也都遇到過吧，今天這篇excel橫向變縱向 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