[已解决]请大师大侠帮我在这汇总出结果末位置生成合计

excel010

请大师大侠帮我在这汇总出结果末位置生成合计
Book2.rar (18.55 KB, 下载次数: 16)

2012-1-8 15:13 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

sunjing-zxl

2012-1-8 19:51

1. 
   
2. Sub 汇总2_Click()
   
3.     Range("W10:AC65535").ClearContents
   
4.     Dim intRow As Long, t As Single
   
5.     Dim ARow As Integer, SQdate As String
   
6.     Dim arr1, arr2, i As Long
   
7.     t = Timer
   
8.     Dim cn As New ADODB.Connection, sql As String
   
9. 
   
10.     SQdate = "WHERE "
    
11.     If Len([X3]) > 0 And Len([Z3]) > 0 Then SQdate = SQdate & "日期 between # " & [X3] & " # AND #" & [Z3] & "# AND "
    
12.     For Each Ran In Range("W6:Y6")
    
13.         If Len(Ran) > 0 Then SQdate = SQdate & Ran.Offset(-1) & "='" & Ran & " ' AND "
    
14.     Next
    
15.     If Len(SQdate) = 6 Then
    
16.         SQdate = ""
    
17.     Else
    
18.         SQdate = Left(SQdate, Len(SQdate) - 5)
    
19.     End If
    
20.     cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
    
21.     sql = "select 客户,总帐科目,sum(借方),sum(贷方),sum(借方-贷方),分类 from [sheet1$C9:V" & intRow & "]" & _
    
22.           SQdate & "GROUP BY 客户,总帐科目,分类"
    
23.     Sheet1.Range("W10").CopyFromRecordset cn.Execute(sql)    '导出数据
    
24.     cn.Close
    
25.     Set cn = Nothing
    
26.     arr1 = Range("Y10:AA" & [Y65536].End(xlUp).Row)
    
27.     ReDim arr2(1 To 1, 1 To 3)
    
28.     For i = 1 To 3
    
29.         arr2(1, i) = Application.Sum(Application.Index(arr1, , i))
    
30.     Next i
    
31.     Range("Y" & [Y65536].End(xlUp).Row + 1).Resize(1, 3) = arr2
    
32.     MsgBox "汇总完成-共用：" & (Timer - t) * 1000 & "毫秒"
    
33. End Sub

复制代码

跳转到最佳答案楼层

sunjing-zxl

1. 
   
2. Sub 汇总2_Click()
   
3.     Range("W10:AC65535").ClearContents
   
4.     Dim intRow As Long, t As Single
   
5.     Dim ARow As Integer, SQdate As String
   
6.     Dim arr1, arr2, i As Long
   
7.     t = Timer
   
8.     Dim cn As New ADODB.Connection, sql As String
   
9. 
   
10.     SQdate = "WHERE "
    
11.     If Len([X3]) > 0 And Len([Z3]) > 0 Then SQdate = SQdate & "日期 between # " & [X3] & " # AND #" & [Z3] & "# AND "
    
12.     For Each Ran In Range("W6:Y6")
    
13.         If Len(Ran) > 0 Then SQdate = SQdate & Ran.Offset(-1) & "='" & Ran & " ' AND "
    
14.     Next
    
15.     If Len(SQdate) = 6 Then
    
16.         SQdate = ""
    
17.     Else
    
18.         SQdate = Left(SQdate, Len(SQdate) - 5)
    
19.     End If
    
20.     cn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
    
21.     sql = "select 客户,总帐科目,sum(借方),sum(贷方),sum(借方-贷方),分类 from [sheet1$C9:V" & intRow & "]" & _
    
22.           SQdate & "GROUP BY 客户,总帐科目,分类"
    
23.     Sheet1.Range("W10").CopyFromRecordset cn.Execute(sql)    '导出数据
    
24.     cn.Close
    
25.     Set cn = Nothing
    
26.     arr1 = Range("Y10:AA" & [Y65536].End(xlUp).Row)
    
27.     ReDim arr2(1 To 1, 1 To 3)
    
28.     For i = 1 To 3
    
29.         arr2(1, i) = Application.Sum(Application.Index(arr1, , i))
    
30.     Next i
    
31.     Range("Y" & [Y65536].End(xlUp).Row + 1).Resize(1, 3) = arr2
    
32.     MsgBox "汇总完成-共用：" & (Timer - t) * 1000 & "毫秒"
    
33. End Sub

复制代码

excel010

sunjing-zxl 发表于 2012-1-8 19:51

老师，请问在合计金额的前面X列，添加（合计）两个字怎么加？

sunjing-zxl

加在msgbox 前面一行
Range("X" & [Y65536].End(xlUp).Row) = "合计"
MsgBox "汇总完成-共用：" & (Timer - t) * 1000 & "毫秒"
End Sub

excel010

sunjing-zxl 发表于 2012-1-9 12:40
加在msgbox 前面一行
Range("X" & [Y65536].End(xlUp).Row) = "合计"
MsgBox "汇总完成-共用：" & (Timer ...

老师又遇到1个问题还请帮忙看看

刚刚那张表格中计算余额的问题

http://www.excelpx.com/thread-218917-1-1.html