VBA统计用WorksheetFunction.SumIfs()还是字典哪个更快？

O2H2O

接触VBA不久，试着跟着案例练习，发现分类汇总的时候，引用工作表公式虽然直观，但会比字典要慢一些，而且随着数据量的增大，二者用时差距会变得越来越大。以下是测试过程：首先虚拟一个原始数据表，以学生成绩为例吧，每个学生有语文、数学、英语3科成绩，加和得到总成绩，一年中有2个学期各有一次期中和期末考试，因此每个学生有4行成绩记录。先设置每个班有50名学生，共有50个班级，1万行数据。原始表截图如下：


目标统计表，要求根据班级分类计数 每次考试的平均分。


测试方法1：使用WorksheetFunction.SumIfs()
代码如下：

[Visual Basic] 纯文本查看 复制代码

Sub test_sumifs()    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim r As Range, i As Long, Renshu As Long, tm
    Set ws1 = ThisWorkbook.Sheets("成绩")
    Set ws2 = ThisWorkbook.Sheets("统计")
    Set r = ws1.Range("A2:F" & ws1.UsedRange.Rows.Count)
    Application.ScreenUpdating = False
    tm = Timer
    Renshu = 50
    For i = 2 To ws2.UsedRange.Rows.Count
        '第1学期期中考
        ws2.Cells(i, 2).Value = WorksheetFunction.SumIfs(r.Columns(6), r.Columns(1), ws2.Cells(i, 1), r.Columns(7), "第1学期期中考")
        ws2.Cells(i, 2).Value = Round(ws2.Cells(i, 2).Value / Renshu, 2)
        '第1学期期末考
        ws2.Cells(i, 3).Value = WorksheetFunction.SumIfs(r.Columns(6), r.Columns(1), ws2.Cells(i, 1), r.Columns(7), "第1学期期末考")
        ws2.Cells(i, 3).Value = Round(ws2.Cells(i, 3).Value / Renshu, 2)
        '第2学期期中考
        ws2.Cells(i, 4).Value = WorksheetFunction.SumIfs(r.Columns(6), r.Columns(1), ws2.Cells(i, 1), r.Columns(7), "第2学期期中考")
        ws2.Cells(i, 4).Value = Round(ws2.Cells(i, 4).Value / Renshu, 2)
        '第2学期期末考
        ws2.Cells(i, 5).Value = WorksheetFunction.SumIfs(r.Columns(6), r.Columns(1), ws2.Cells(i, 1), r.Columns(7), "第2学期期末考")
        ws2.Cells(i, 5).Value = Round(ws2.Cells(i, 5).Value / Renshu, 2)
    Next i

    Debug.Print "sumifs用时：" & Timer - tm
    Application.ScreenUpdating = True
End Sub

共用时：0.41秒
测试方法2：
先按4个考试汇总成4个字典，然后根据班级对应填入每行4个平均分。
代码如下：

[Asm] 纯文本查看 复制代码

Sub test_dic()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim k As Long, Renshu As Long, tm
    Dim D_1 As Object, D_2 As Object, D_3 As Object, D_4 As Object
    Application.ScreenUpdating = False
    tm = Timer
    Renshu = 50
    Set ws1 = ThisWorkbook.Worksheets("成绩")
    Set ws2 = ThisWorkbook.Worksheets("统计")
    
    Set D_1 = CreateObject("Scripting.Dictionary")
    Set D_2 = CreateObject("Scripting.Dictionary")
    Set D_3 = CreateObject("Scripting.Dictionary")
    Set D_4 = CreateObject("Scripting.Dictionary")
    
    '循环“成绩”表，得到不同的字典
    For k = 2 To ws1.UsedRange.Rows.Count
    
        '筛选有“第1学期期中考”项，并按班级合并“总分”
        If ws1.Cells(k, 7).Value = "第1学期期中考" Then
            D_1(ws1.Cells(k, 1).Value) = D_1(ws1.Cells(k, 1).Value) + ws1.Cells(k, 6).Value
        End If
        
        '筛选有“第1学期期末考”项，并按班级合并“总分”
        If ws1.Cells(k, 7).Value = "第1学期期末考" Then
            D_2(ws1.Cells(k, 1).Value) = D_2(ws1.Cells(k, 1).Value) + ws1.Cells(k, 6).Value
        End If
        
        '筛选有“第2学期期中考”项，并按班级合并“总分”
        If ws1.Cells(k, 7).Value = "第2学期期中考" Then
            D_3(ws1.Cells(k, 1).Value) = D_3(ws1.Cells(k, 1).Value) + ws1.Cells(k, 6).Value
        End If
        
        '筛选有“第2学期期末考”项，并按班级合并“总分”
        If ws1.Cells(k, 7).Value = "第2学期期末考" Then
            D_4(ws1.Cells(k, 1).Value) = D_4(ws1.Cells(k, 1).Value) + ws1.Cells(k, 6).Value
        End If
    Next k
    
    '循环“统计”表，从字典取值填入表中
    For k = 2 To ws2.UsedRange.Rows.Count
        ws2.Cells(k, 2).Value = Round(D_1(ws2.Cells(k, 1).Value) / Renshu, 2)
        ws2.Cells(k, 3).Value = Round(D_2(ws2.Cells(k, 1).Value) / Renshu, 2)
        ws2.Cells(k, 4).Value = Round(D_3(ws2.Cells(k, 1).Value) / Renshu, 2)
        ws2.Cells(k, 5).Value = Round(D_4(ws2.Cells(k, 1).Value) / Renshu, 2)
    Next k
    
    Debug.Print "arr用时：" & (Timer - tm)
    
    Application.ScreenUpdating = True
End Sub

共用时：0.48秒
接下来分别把原始数据扩大到100个班级、150个班级、200个班级、250个班级。对应数据行数分别为2万、3万、4万、5万。
测试结果如下：


可以看出，数据量不大时，两种方法用时差不多，随着数据量的增大，花费时间的差距也越来越大了。这个案例涉及的知识很少，不知道还有没有更快速的方法？请大佬们指点指点哈！
第一次发帖，大佬们轻喷啊~~

放羊的狼

vba里字典+数组应该无敌手了吧

rebotdxy

SQL应该更快

O2H2O

rebotdxy 发表于 2023-4-10 12:06
SQL应该更快

等再多学点，下一个了解一下SQL哈  

lmze2000

感觉你这个速度慢的症结是在，直接写表了。

[Asm] 纯文本查看 复制代码

 ws2.Cells(i, 2).Value = WorksheetFunction.SumIfs(r.Columns(6), r.Columns(1), ws2.Cells(i, 1), r.Columns(7), "第1学期期中考")
        ws2.Cells(i, 2).Value = Round(ws2.Cells(i, 2).Value / Renshu, 2)

就是这里的地方，
你可以试试，定义一个数组，把结果写在数组里面，
最后在把数组写进表里面。
你会发现快很多。

wangtk1982

我觉得问题出在给Value赋值上，缓存数据到数组，range().value一次赋值。

JuncoJet

写个DLL，使用C++ STL的MAP/unordered_map更快

hlw2008

高手总是这么多

bjxiaoyao

慢在直接写表格了，建议用数组收纳数据，再用worksheetfunction.transpose()一次写入表格

魔术使nqy

你们vba都在哪学的？怎么都这么厉害，我是用录制宏然后再修改一下代码，有没有大神提供一下教程