求助：用vba两组数据汇总

colin083

chxw68 发表于 2023-3-6 09:53
详见附件。

谢谢老师！

colin083

sxbjzjf110 发表于 2023-3-6 11:52

谢谢老师！

colin083

liulang0808 发表于 2023-3-6 09:58
模拟供参考。。。。。。。。。。。

谢谢版主！

colin083

cjc209 发表于 2023-3-6 10:27
答案

谢谢老师！

colin083

ykcbf1100 发表于 2023-3-6 10:13
参与一下。

谢谢老师！

demonimissyou

Sub XX()
Dim I, Conne, rst, pathstr, strConne, strSQL
Set Conne = CreateObject("adodb.connection")
Set rst = CreateObject("adodb.recordset")
pathstr = ThisWorkbook.FullName
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathstr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
Conne.Open strConn
strSQL = "SELECT 姓名,sum(工资) as 工资,sum(奖金) as 奖金 from (select 姓名,工资,null as 奖金 from [sheet1$A1:B5] union all select 姓名,null as 工资,奖金 from [sheet1$E1:F4]) group by 姓名"
    Set rst = Conne.Execute(strSQL)
     Sheet1.Range("i:k").Clear
    For I = 0 To rst.fields.Count - 1
        Sheet1.Cells(1, I + 9) = rst.fields(I).Name
    Next
    Sheet1.[i2].CopyFromRecordset rst
rst.Close
Conne.Close
Set rst = Nothing
Set Conne = Nothing
End Sub