|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
对sql计算累计数来说,你可以在论坛找下关于域聚合函数的帖子看下
也就是dsum函数,还有用嵌套子查询的方法。
学习后你会发现,你要求的累计是按照行次来进行累计的,然而你数据表中没有行次,我本想用送货日期来,发现你结果表中送货日期并不是唯一值。
所以我不折腾了,个人认为简单的公式更效率
ps 顺便优化了下你ADO的条件的判断和ado程序以及你第6行的合计代码如下:
- Sub test()
- Dim CNN As Object
- Set CNN = CreateObject("adodb.connection")
- Set d = CreateObject("Scripting.Dictionary")
- CNN.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='excel 12.0;hdr=yes;imex=1';Data Source=" & ThisWorkbook.FullName
- With Sheets("sheet2")
- If .[p2] <> "" Then d(厂家) = "供货厂家='" & .[p2] & "'"
- If .[r2] <> "" And .[t2] <> "" Then d(期间) = "实付厂家货款时间*1 between " & .[r2] * 1 & " and " & .[t2] * 1
- arr = d.items
- If d.Count = 0 Then
- wst = ""
- Else
- wst = "where " & Join(arr, " and ")
- End If
- .[b7:N65536].Clear
- Sql = "select 送货日期,品名,规格,数量,进价,应付金额*1 as 应付金额,工程项目,付款渠道,实付厂家货款时间,实付厂家货款金额,'' as 结余,是否开票,进项票额 from [Sheet1$b7:bb] " & wst & " "
- .[b7].CopyFromRecordset CNN.Execute(Sql)
-
- rw = .[c65536].End(3).Row
- Union(.[e6], .[g6], .[k6]) = "=sum(e7:e" & rw & ")"
- .[l6:L7] = "=K6-G6"
- .[l8].Resize(rw - 7, 1) = "=L7+K8-G8"
-
- End With
- CNN.Close: Set CNN = Nothing
- End Sub
复制代码 结果:
|
评分
-
1
查看全部评分
-
|