VBA统计用WorksheetFunction.SumIfs()还是字典哪个更快？
本帖最后由 O2H2O 于 2023-4-10 11:38 编辑接触VBA不久，试着跟着案例练习，发现分类汇总的时候，引用工作表公式虽然直观，但会比字典要慢一些，而且随着数据量的增大，二者用时差距会变得越来越大。以下是测试过程：首先虚拟一个原始数据表，以学生成绩为例吧，每个学生有语文、数学、英语3科成绩，加和得到总成绩，一年中有2个学期各有一次期中和期末考试，因此每个学生有4行成绩记录。先设置每个班有50名学生，共有50个班级，1万行数据。原始表截图如下：
https://attach.52pojie.cn//forum/202304/10/112845hltsrs8l1q8tz8q8.png?l
目标统计表，要求根据班级分类计数 每次考试的平均分。
https://attach.52pojie.cn//forum/202304/10/111449ng7zqlqg4q42cq72.png?l
测试方法1：使用WorksheetFunction.SumIfs()
代码如下：
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个平均分。
代码如下：
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万。
测试结果如下：
https://attach.52pojie.cn//forum/202304/10/112918ox6w4dldw9wgwwx4.png?l
可以看出，数据量不大时，两种方法用时差不多，随着数据量的增大，花费时间的差距也越来越大了。这个案例涉及的知识很少，不知道还有没有更快速的方法？请大佬们指点指点哈！
第一次发帖，大佬们轻喷啊~~:lol:lol vba里字典+数组应该无敌手了吧 SQL应该更快 rebotdxy 发表于 2023-4-10 12:06
SQL应该更快
等再多学点，下一个了解一下SQL哈 感觉你这个速度慢的症结是在，直接写表了。
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)
就是这里的地方，
你可以试试，定义一个数组，把结果写在数组里面，
最后在把数组写进表里面。
你会发现快很多。 我觉得问题出在给Value赋值上，缓存数据到数组，range().value一次赋值。 写个DLL，使用C++ STL的MAP/unordered_map更快 高手总是这么多 慢在直接写表格了，建议用数组收纳数据，再用worksheetfunction.transpose()一次写入表格 你们vba都在哪学的？怎么都这么厉害，我是用录制宏然后再修改一下代码，有没有大神提供一下教程{:1_923:}
页:
[1]
2