时间戳

首页 > 解决方案 > VBA：在另一个工作表中对多个工作表中的相同列求和（概述）

excel - VBA：在另一个工作表中对多个工作表中的相同列求和（概述）

问题描述

我是 VBA 新手，想快速学习 :)

我有 6 张 Excel 表格（第 1-6 个月），数据相同（如表格所示）但行数不同。

在此处输入图像描述

  1. 我想创建一个表，其中月份（1-6）作为行和卷以及作为列的储备

在此处输入图像描述

  1. 另外，有没有办法我可以编写一个代码，让我在储备方面（F 列）给出前 3 个客户组，同时也给出相应的数量（E 列）——比如 sumif() 但在 vba 中？

在此处输入图像描述

我已经研究了好几天，但没有发现太多……我很高兴能得到任何帮助！太感谢了：）

标签： excelvbasumsumifs

解决方案

这就是您使用 adodb 对象的方式。只需添加 3 张纸并运行它。

数据表用作获取 top3 的临时表。

在此处输入图像描述

Sub exeSQL(Ws As Worksheet, strSQL As String)

    Dim Rs As Object  'ADODB.Recordset
    Dim strConn As String
    Dim i As Integer

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
            "Extended Properties=Excel 12.0;"
    
    Set Rs = CreateObject("ADODB.Recordset") 'New ADODB.Recordset
    
    Rs.Open strSQL, strConn
    
    If Not Rs.EOF Then
         With Ws
            .Range("a2").CurrentRegion.ClearContents
            For i = 0 To Rs.Fields.Count - 1
               .Cells(1, i + 1).Value = Rs.Fields(i).Name
            Next
            .Range("a2").CopyFromRecordset Rs
            .Columns.AutoFit
        End With
    End If
    Rs.Close
    Set Rs = Nothing
End Sub
Sub Main()
    Call sumVolume
    Call sumTop3
End Sub
Sub sumVolume()
    Dim Ws As Worksheet
    Dim strSQL As String, strU As String
    Dim sName(1 To 6) As Variant
    Dim i As Integer
    
    Set Ws = Sheets("Resultsum")

    For i = 1 To 6
        sName(i) = Sheets(i).Name
    Next i
    For i = 1 To 5
        strU = strU & "select '" & sName(i) & "' as sday, [Volume], [Reserves] from [" & sName(i) & "$] union all "
    Next i
        strU = strU & "select '" & sName(6) & "' as sday, [Volume], [Reserves] from [" & sName(6) & "$] "
        
    strSQL = "SELECT sday, sum([Volume]) as [sum of volume], sum([Reserves]) as [sum of Reserves ] "
    strSQL = strSQL & " FROM (" & strU & " )"
    strSQL = strSQL & "WHERE not isnull([Volume]) "
    strSQL = strSQL & "GROUP BY sday "
        
    exeSQL Ws, strSQL
    
    
End Sub
Sub sumTop3()
    Dim Ws As Worksheet
    Dim strSQL As String, strU As String
    Dim i As Integer
    
    Call sumData
    
    Set Ws = Sheets("ResultTop3")


    strSQL = "SELECT *   "
    strSQL = strSQL & " FROM [Data$] as a "
    strSQL = strSQL & "WHERE [Reserves] in ( "
    strSQL = strSQL & "SELECT TOP 3 [Reserves] FROM [Data$] as b "
    strSQL = strSQL & "ORDER BY b.[Reserves] DESC ) "
    strSQL = strSQL & "ORDER BY a.[Reserves] DESC  "
    
        
    exeSQL Ws, strSQL
    
    
End Sub

Sub sumData()
    Dim Ws As Worksheet
    Dim strSQL As String, strU As String
    Dim sName(1 To 6) As Variant
    Dim i As Integer
    
    Set Ws = Sheets("Data")

    For i = 1 To 6
        sName(i) = Sheets(i).Name
    Next i
    For i = 1 To 5
        strU = strU & "select [Customer Group Nr] as [Customer Group], [Volume], [Reserves] from [" & sName(i) & "$] union all "
    Next i
        strU = strU & "select [Customer Group Nr] as [Customer Group], [Volume], [Reserves] from [" & sName(6) & "$] "
        

    strSQL = "SELECT [Customer Group], sum([Volume]) as Volume , sum([Reserves]) as Reserves  "
    strSQL = strSQL & " FROM (" & strU & " )"
    strSQL = strSQL & "WHERE not isnull([Volume]) "
    strSQL = strSQL & "GROUP BY [Customer Group]  "
        
    exeSQL Ws, strSQL
    
    
End Sub

推荐阅读