位置:首頁(yè) > 軟件操作教程 > 辦公軟件 > Excel > 問題詳情

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

提問人:周麗平發(fā)布時(shí)間:2021-08-07

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

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

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

image.png

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

image.png

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

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

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

image.png

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

image.pngExcel中數(shù)據(jù)從橫向排列到縱向排列的復(fù)雜轉(zhuǎn)換

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,雙擊填充柄將公式填充到這兩列的其余單元格。

image.pngExcel中數(shù)據(jù)從橫向排列到縱向排列的復(fù)雜轉(zhuǎn)換

方法三、用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

繼續(xù)查找其他問題的答案?

回復(fù)(0)
返回頂部