|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- Sub 统计1()
- Dim oConn As Object, sSQL As String
-
- Set oConn = CreateObject("ADODB.Connection")
- oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0';Data Source=" & ThisWorkbook.FullName
- sSQL = "Select [推荐人],[新老],[记账日期],Sum([消费金额]) As 总额 From [1-2$A1:F] " & _
- "Where [记账日期] Between #" & [O2] & "# And #" & [P2] & "# Group by [推荐人],[新老],[记账日期]"
- sSQL = "Select [推荐人],Sum(IIF([新老]='新客',1,0)),Sum(IIF([新老]='新客',[总额],0)),Sum(IIF([新老]='老客',1,0)),Sum(IIF([新老]='老客',[总额],0)) " & _
- "From (" & sSQL & ") " & _
- "Group by [推荐人]"
- [H2:L2].Resize(ActiveSheet.UsedRange.Rows.Count).ClearContents
- [H2].CopyFromRecordset oConn.Execute(sSQL)
- oConn.Close
- Set oConn = Nothing
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|