|
发表于 2011-9-14 13:44
|
显示全部楼层
本楼为最佳答案
本帖最后由 mxg825 于 2011-9-14 14:57 编辑
回复 快樂學習 的帖子
- Sub 汇总()
- Dim cn As New ADODB.Connection, t As Single
- Dim sq1 As String, sq2 As String, sq3 As String
- t = Timer
- Range("A2:I" & Range("A65536").End(xlUp).Row + 1).ClearContents
- cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
- sq1 = "select 编号,名称,入库数量,入库金额,0 as 出库数量,0 as 出库金额 from [表1$]"
- sq2 = "select 编号,名称,0 AS 入库数量,0 AS 入库金额,出库数量,出库金额 from [表2$]"
- sq3 = "select 编号,名称,SUM(入库数量),SUM(入库金额)/SUM(入库数量),SUM(入库金额),SUM(出库数量),SUM(出库金额)/SUM(出库数量),SUM(出库金额),SUM(入库数量)-SUM(出库数量) " & _
- "from ( " & sq1 & " UNION ALL " & sq2 & ") GROUP BY 编号,名称"
- Sheets("汇总").Range("A2").CopyFromRecordset cn.Execute(sq3)
- cn.Close
- Set cn = Nothing
- MsgBox "共用时:" & (Timer - t) * 1000 & "毫秒"
- End Sub
复制代码 进价= 总进货金额/总进货数量
售价= 总出货金额/总出货数量
最后加了一列 结存数量!
使用前,要引用 Microsoft ActiveX Data Objects 2.5 Library (2.5 版本吧 其他也行)
|
|