Question

excel多行多列批量转置 把一家人从一列转置到户主行，及从图1变为图2





Answer 1

使用vba转置吧

在vbe里输入代码如下：

Sub 按钮3_Click()

Application.ScreenUpdating = False

arr = [a1].CurrentRegion

For j = 2 To UBound(arr)

If arr(j, 1) <> "户主" Then

Cells(a, b) = arr(j, 2)

b = b + 1

Else

a = j

Cells(a, 6) = arr(j, 2)

b = 7

End If

Next j

Application.ScreenUpdating = True

End Sub

Answer 2

大神给你写个宏学习一下噻，效果截图

Sub GetFamilyInfoToLine()

Dim i As Long

Dim j As Long

Dim nLast As Long

Dim arr()

Dim wsh As Worksheet

Set wsh = Workbooks("家庭信息列转行.xls").Worksheets("Sheet1")

nLast = wsh.Range("A:A").Find("*", , , , xlByRows, xlPrevious).Row

i = 2

While i <= nLast

If wsh.Cells(i, 1) = "户主" Then

j = i + 1

While wsh.Cells(j, 1) <> "户主" And j <= nLast

j = j + 1

Wend

If j - i > 1 Then

arr = wsh.Range("B" & i & ":B" & CLng(j - 1))

wsh.Range("F" & i & ":" & Chr(Asc("F") + j - i - 1) & i) = Application.WorksheetFunction.Transpose(arr)

Else

wsh.Cells(i, Asc("F") - Asc("A") + 1) = wsh.Cells(i, Asc("B") - Asc("A") + 1)

End If

i = j

End If

Wend

Set wsh = Nothing

End Sub

Answer 3

F2=IF(AND(COLUMN(A1)<=IF(ISERROR(MATCH("户主",$A3:$A$35,0)),1,MATCH("户主",$A3:$A$35,0)),$A2="户主"),INDEX($B2:$B$34,COLUMN(A1)),"")向下向右复制

Answer 4

E2公式下拉：=INDEX(A:A,SMALL(IF(MATCH(A$1:A$999&"",A$1:A$999&"",)=ROW($1:$999),ROW($1:$999),4^8),ROW(A1)))&""

F1公式右拉：=INDEX($B:$B,SMALL(IF(MATCH($B1:$B999&"",$B1:$B999&"",)=ROW(1:999),ROW(1:999),4^8),COLUMN(A1)))&""

上面两条公式是数组公式，输入完成后不要直接回车，要按三键 CTRL+SHIFT+回车 结束。

F2公式右拉下拉：=IF(($E2="")+(F$1=""),"",SUMPRODUCT(($A$1:$A$999=--$E2)*($B$1:$B$999=F$1)*($C$1:$C$999)))