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

发布网友 发布时间：2023-09-15 13:16

我来回答

共4个回答

热心网友 时间：2024-12-05 09:43

使用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

热心网友 时间：2024-12-05 09:44

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

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

热心网友 时间：2024-12-05 09:44

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)),"")向下向右复制

热心网友 时间：2024-12-05 09:45