对1-12个月工资表明细汇总到一张表上

加菲海带 · 发表于 2024-12-4 19:22

老师们：

请问怎么按照员工把工资的各个明细表汇总到一张表上。谢谢！

加菲海带 · 发表于 2024-12-4 20:52

请大神们帮忙啊

yfan17 · 发表于 2024-12-4 20:56

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW($1:$12)&"月'!B3:B29"),$B3,INDIRECT("'"&ROW($1:$12)&"月'!G3:G29")))

复制代码

dambcer · 发表于 2024-12-4 20:56

如何就是，
1、手动复制后透视
2、VBA
3、PQ
4、公式
5、其它

3190496160 · 发表于 2024-12-5 09:02

Sub 汇总()
Dim ar As Variant
Dim br()
Dim i As Long, r As Long
Set d = CreateObject("Scripting.Dictionary")
ReDim br(1 To 50000, 1 To 21)
For Each sh In Sheets
If InStr(sh.Name, "月") > 0 Then
      r = sh.Cells(Rows.Count, 3).End(xlUp).Row
      If r > 2 Then
         ar = sh.Range("a1:r" & r)
         For i = 3 To UBound(ar)
            If ar(i, 3) <> "" Then
                  zd = ar(i, 3)
                  t = d(zd)
                  If t = "" Then
                     k = k + 1
                     d(zd) = k
                     t = k
                     For j = 1 To 5
                        br(k, j) = ar(i, j)
                     Next j
                  End If
                  For j = 6 To UBound(ar, 2)
                     br(t, j + 3) = br(t, j + 3) + ar(i, j)
                  Next j
            End If
         Next i
      End If
End If
Next sh
With Sheets("汇总")
.UsedRange.Offset(2).Borders.LineStyle = 0
.UsedRange.Offset(2) = Empty
.[a3].Resize(k, UBound(br, 2)) = br
.[a3].Resize(k, UBound(br, 2)).Borders.LineStyle = 1
End With
MsgBox "ok!"
End Sub

3190496160 · 发表于 2024-12-5 09:04

2024年1-12月汇总表.rar (33.21 KB, 下载次数: 16)

		自动登录	找回密码
密码			免费注册