请教EXCEL 大神,想做一个智能表格。

发布网友发布时间：2022-04-30 11:19

共1个回答

热心网友时间：2022-06-21 16:12

通过窗体在sheet1添加一个按钮，新建，将以下代码复制粘贴，然后回到sheet1，点击按钮，将会自动在sheet2形成你所要的汇总表格。

Sub 按钮1_单击()

Sheet2.Range("a:e").ClearContents

Sheet1.Range("a1:e1").Copy

Sheet2.Range("a1:e1").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Dim i, j, k, l, rws As Integer

Dim str As String, yn As Boolean

rws = 1

k = 0

j = Sheet1.[a65536].End(xlUp).Row

For i = 2 To j

yn = True

For l = 1 To i - 1

If Sheet1.Cells(l, 1) = Sheet1.Cells(i, 1) Then

yn = False

End If

If yn = True Then

rws = rws + 1

str = ""

For k = i + 1 To j

If Sheet1.Cells(k, 1) = Sheet1.Cells(i, 1) Then

If Sheet1.Cells(k, 5) > 1 Then

str = str & Sheet1.Cells(k, 4) & Sheet1.Cells(k, 5) & "件" & "；"

Else

str = str & Sheet1.Cells(k, 4) & "；"

End If

Sheet2.Cells(rws, 1) = Sheet1.Cells(i, 1)

Sheet2.Cells(rws, 2) = Sheet1.Cells(i, 2)

Sheet2.Cells(rws, 3) = Sheet1.Cells(i, 3)

Sheet2.Cells(rws, 5) = 1

If Sheet1.Cells(i, 5) > 1 Then

str = Sheet1.Cells(i, 4) & Sheet1.Cells(i, 5) & "件" & "；" & str

Else

str = Sheet1.Cells(i, 4) & "；" & str

End If

Sheet2.Cells(rws, 4) = Left(str, Len(str) - 1)

End If

End Sub