Excel怎样快速把数据表生成工资条格式新表

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 Lo荏鱿胫协ng, 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 iEnd Sub

4、以上操作动态过程如下：

5、回到工作表窗口，运行【生成工资刳噪受刃条样式表】宏（菜单栏中点【视图】中下列表中【宏】列表【查看宏(V)】打开宏对方框，选该宏名，执行），秒徽革蕖颖速生成一个新表（9-13表中箱号顺序变时生成相应表），运行过程如下图。

6、如果觉得这篇经验帮到了您，请点击下方的 “投票点赞" 或者“收藏”支持我！还有疑问的话可以点击下方的 “我有疑问”，谢谢啦！