|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub a()
Dim sh As Worksheet, sqt1$, sqt2$, bt, i%, T$, sql$
bt = [{"品名","数量1","数量2","项目1","项目2","项目3","项目4","项目5","项目6","项目7","项目8","项目9","项目10","项目11","项目12","项目13","项目14","项目15","项目16"}]
For Each sh In Sheets
If sh.Name <> "汇总" Then
For i = 4 To UBound(bt)
sqt1 = sqt1 & "数量1*" & bt(i) & "+"
Next
sqt1 = "(" & Left(sqt1, Len(sqt1) - 1) & ") as 数量"
For i = 1 To UBound(bt)
sqt2 = sqt2 & bt(i) & ","
Next
sqt2 = Left(sqt2, Len(sqt2) - 1)
sql = sqt1 & "," & sqt2
sql = "SELECT " & sql & " FROM [" & sh.Name & "$A2:T] WHERE 品名 is not null UNION ALL "
End If
T = T & sql
sql = "": sqt1 = "": sqt2 = ""
Next
[a3] = T
T = Left(T, Len(T) - 11)
For i = 4 To UBound(bt)
sql = sql & "SUM(" & bt(i) & ")/SUM(数量),"
Next
sql = Left(sql, Len(sql) - 1)
sql = "SELECT 品名,null,SUM(数量1),SUM(数量2)," & sql & "FROM (" & T & ") " & "GROUP BY 品名"
Debug.Print sql
Dim cnn As Object
Set cnn = CreateObject("ADODB.CONNECTION")
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & ThisWorkbook.FullName
Range("a3:z9999").ClearContents
[a3] = sql
[a3].CopyFromRecordset cnn.Execute(sql)
cnn.Close: Set cnn = Nothing
End Sub
|
评分
-
1
查看全部评分
-
|