Office教程学习网

假如将转换后的数据放在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