根据一列数据求平均

grf1973

1. Sub 分文件汇总()
   
2.     On Error Resume Next
   
3.     Dim wb As Workbook
   
4.     Dim Filename$, fn$, sql$
   
5.     Set cnn = CreateObject("ADODB.Connection")
   
6.     cnn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source=" & ThisWorkbook.FullName
   
7.     Filename = Dir(ThisWorkbook.Path & "\*.xlsx")
   
8.     Application.DisplayAlerts = False
   
9.     Do While Filename <> ""
   
10.         If Filename <> ThisWorkbook.Name Then
    
11.             fn = ThisWorkbook.Path & "" & Filename
    
12.             Set wb = Workbooks.Open(fn)
    
13.             sql = "select TIME,avg(K1),avg(K2),avg(K3),avg(K4),avg(K5),avg(K6),avg(K7) from [" & fn & "].[汇总$] group by TIME"
    
14.             wb.Sheets(2).Delete
    
15.             wb.Sheets.Add after:=wb.Sheets(wb.Sheets.Count)
    
16.             With wb.Sheets(2)
    
17.                 .Name = "结果"
    
18.                 .[a1].Resize(1, 8) = Array("TIME", "K1", "K2", "K3", "K4", "K5", "K6", "K7")
    
19.                 .[a2].CopyFromRecordset cnn.Execute(sql)
    
20.             End With
    
21.         End If
    
22.         wb.Close True
    
23.         Filename = Dir
    
24.     Loop
    
25.     cnn.Close: Set cnn = Nothing
    
26.     Application.DisplayAlerts = True
    
27. End Sub
    

复制代码