[已解决]数据汇总的问题，请大师帮帮我。

weixin321

hz.rar (14.38 KB, 下载次数: 16)

2012-10-27 16:42 上传

点击文件名下载附件
——————————————————————————————
老师如果我想只汇总（采购进货入库）（采购退货出库）这两项怎么弄？？？

而（采购进货赠送）（采购赠送退货）这两项则不汇总。
———————————————————————————————————
Private Sub CommandButton1_Click()
Range("M5:U65535").ClearContents
Dim intRow As Integer, t As Single
    Dim ARow As Integer
    t = Timer
    Dim cn As New ADODB.Connection, sql As String
    intRow = Sheet1.Range("B65536").End(xlUp).Row
    cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "select 货品编号,供应商,产地,品名,规格,sum(进货数量),单位,sum(进货总金额),abs(sum(进货总金额)/sum(进货数量)) from [sheet1$B4:K" & intRow & "]" & _
     "GROUP BY 货品编号,供应商,产地,品名,规格,单位"
    Sheet1.Range("M5").CopyFromRecordset cn.Execute(sql)
    cn.Close
    Set cn = Nothing
    ActiveWindow.SmallScroll Down:=-65536
End Sub

最佳答案

月排行榜 / 总排行榜

mxg825

2012-10-29 13:32

Private Sub CommandButton1_Click()
Range("M5:U65535").ClearContents
Dim intRow As Integer, t As Single
    Dim ARow As Integer
    t = Timer
    Dim cn As New ADODB.Connection, sql As String
    intRow = Sheet1.Range("B65536").End(xlUp).Row
    cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "select 货品编号,供应商,产地,品名,规格,sum(进货数量),单位,sum(进货总金额),abs(sum(进货总金额)/sum(进货数量)) from [sheet1$B4:K" & intRow & "]" & _
     "WHERE 方式 IN('采购进货入库','采购退货出库') GROUP BY 货品编号,供应商,产地,品名,规格,单位"
    Sheet1.Range("M5").CopyFromRecordset cn.Execute(sql)
    cn.Close
    Set cn = Nothing
    ActiveWindow.SmallScroll Down:=-65536
End Sub

跳转到最佳答案楼层

cliyu

mxg825

Private Sub CommandButton1_Click()
Range("M5:U65535").ClearContents
Dim intRow As Integer, t As Single
    Dim ARow As Integer
    t = Timer
    Dim cn As New ADODB.Connection, sql As String
    intRow = Sheet1.Range("B65536").End(xlUp).Row
    cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
sql = "select 货品编号,供应商,产地,品名,规格,sum(进货数量),单位,sum(进货总金额),abs(sum(进货总金额)/sum(进货数量)) from [sheet1$B4:K" & intRow & "]" & _
     "WHERE 方式 IN('采购进货入库','采购退货出库') GROUP BY 货品编号,供应商,产地,品名,规格,单位"
    Sheet1.Range("M5").CopyFromRecordset cn.Execute(sql)
    cn.Close
    Set cn = Nothing
    ActiveWindow.SmallScroll Down:=-65536
End Sub

weixin321

mxg825 发表于 2012-10-29 13:32
Private Sub CommandButton1_Click()
Range("M5:U65535").ClearContents
Dim intRow As Integer, t As  ...

老师如何加http://www.excelpx.com/thread-296042-1-1.html