Excel怎样快速把数据表生成工资条格式新表
1、有原表格（图一）的数据，根据箱号生成一个带格式的新表格（图二）。图二中“日期”、“站点”分别为图一“送货日期”、“备货仓库”；供商代码为“0888”。
1、首先打开Microsoft Office Excel 2007，上例文件保存文件名《Excel怎样快速把数据表生成工资条格式新表 .xlsm》（演示文件，下面代码复制到能运行宏的工作簿都可以）如图。
2、然后按下快捷键ALT+F11打开VBE（宏）编辑界面，然后点菜单栏【插入】下拉中列表中点【模块（M）】如图。
3、然后插入了一个模块1，在代码框中复制如下代码：
Sub 生成工资条样式表()
'2020-9-15 22:37:38
Dim r As Long, x1 As Long, x2 As Long, mb(), st As String
Dim i As Long, j As Long, k As Long, m, mc(), n As Long
Dim d As Date, md, mn1 As Long, mn2 As Long
r = Range("h" & Rows.Count).End(xlUp).Row
x1 = WorksheetFunction.Min(Range("h6:h" & r))
x2 = WorksheetFunction.Max(Range("h6:h" & r))
mb = Range("b6:h" & r)
d = Range("f4").Value
ReDim mc(x2, 3)
n = 0
For i = x1 To x2
k = 0
For j = 1 To UBound(mb, 1)
If i = mb(j, 7) Then
k = k + 1
If k = 1 Then
m = j
Else
m = m & "," & j
End If
End If
Next j
If k > 0 Then
n = n + 1
mc(n, 1) = i
mc(n, 2) = k
mc(n, 3) = m
End If
Next i
st = ActiveSheet.Name
Sheets.Add After:=Sheets(st)
mn2 = UBound(mb, 1) + n - 1 + n
Range(Cells(1, 1), Cells(mn2, 1)).NumberFormatLocal = "m-d;@"
Range(Cells(1, 2), Cells(mn2, 2)).NumberFormatLocal = "@"
With Range(Cells(1, 1), Cells(mn2, 7))
.Font.Name = "宋体"
.Font.Size = 18
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
Columns(1).ColumnWidth = 12.44
Columns(2).ColumnWidth = 13.22
Columns(3).ColumnWidth = 11.11
Columns(4).ColumnWidth = 30
Columns(5).ColumnWidth = 13.22
Columns(6).ColumnWidth = 7.67
Columns(7).ColumnWidth = 6.78
Rows("1:" & mn2).RowHeight = 22.8
k = 0
For i = 1 To n
k = k + 1
Cells(k, 1).Value = "日期": Cells(k, 2).Value = "供商代码": Cells(k, 3).Value = "站点"
Cells(k, 4).Value = "SKU": Cells(k, 5).Value = "颜色": Cells(k, 6).Value = "数量"
Cells(k, 7).Value = mc(i, 1)
If mc(i, 2) = 1 Then
k = k + 1
Cells(k, 1).Value = d: Cells(k, 2).Value = "0888": Cells(k, 3).Value = mb(mc(i, 3), 1)
Cells(k, 4).Value = mb(mc(i, 3), 3): Cells(k, 5).Value = mb(mc(i, 3), 5): Cells(k, 6).Value = mb(mc(i, 3), 6)
Else
md = Split(mc(i, 3), ",")
For j = 0 To mc(i, 2) - 1
k = k + 1
Cells(k, 1).Value = d: Cells(k, 2).Value = "0888": Cells(k, 3).Value = mb(md(j), 1)
Cells(k, 4).Value = mb(md(j), 3): Cells(k, 5).Value = mb(md(j), 5): Cells(k, 6).Value = mb(md(j), 6)
Next j
End If
k = k + 1
Next i
For i = 1 To n
If i = 1 Then
mn2 = mc(i, 2) + 1
Range(Cells(1, 7), Cells(mn2, 7)).Merge
With Range(Cells(1, 1), Cells(mn2, 7))
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
Else
mn1 = mn2 + 2
mn2 = mn1 + mc(i, 2)
Range(Cells(mn1, 7), Cells(mn2, 7)).Merge
With Range(Cells(mn1, 1), Cells(mn2, 7))
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
End If
Next i
End Sub
4、以上操作动态过程如下：
5、回到工作表窗口，运行【生成工资条样式表】宏（菜单栏中点【视图】中下列表中【宏】列表【查看宏(V)】打开宏对方框，选该宏名，执行），秒速生成一个新表（9-13表中箱号顺序变时生成相应表），运行过程如下图。
6、如果觉得这篇经验帮到了您，请点击下方的 “投票点赞" 或者“收藏”支持我！还有疑问的话可以点击下方的 “我有疑问”，谢谢啦！