VBA中用sumifs运算太慢怎么优化？

tigertc

VBA中用sumifs运算太慢怎么优化？数据将进8万条，速度太慢了。见附件。

把公式改了字典，但好像还是有问题，我有一栏是要用sumifs的条件是空值“”，但不知道怎么写，只能借用了columns("N:N")，运算结果不对，编码也乱码了不知道该 怎么转换成文本格式？

lsc900707

参考用字典数组代替sumifs

tigertc

    arr1 = Sheets("汇总A").UsedRange
    Set d1 = CreateObject("scripting.dictionary")
    Set d2 = CreateObject("scripting.dictionary")
    Set d3 = CreateObject("scripting.dictionary")
    Set d4 = CreateObject("scripting.dictionary")

    For j = 2 To UBound(arr1)
        d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) + arr1(j, 24)
        d2(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) + arr1(j, 37)
        d3(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 2)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 2)) + arr1(j, 37)
        d4(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 1)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 1)) + arr1(j, 37)
    Next j

    arr2 = Sheets("汇总B").UsedRange
    For i = 2 To Sheets("汇总B").Range("a1048576").End(3).Row
        arr2(i, 10) = d1(arr2(i, 1) & arr2(i, 7) & arr2(i, 8))
        arr2(i, 11) = d2(arr2(i, 1) & arr2(i, 7) & arr2(i, 8))
        arr2(i, 12) = d3(arr2(i, 1) & arr2(i, 7) & arr2(i, 8) & arr2(i, 14))
        arr2(i, 13) = d4(arr2(i, 1) & arr2(i, 7) & arr2(i, 8) & arr2(i, 14))

    Next i
    Sheets("汇总B").UsedRange = arr2

这样会出错，并且编号数值过长会乱码，不知道怎么转换

tigertc

lsc900707 发表于 2018-9-2 08:16
参考用字典数组代替sumifs

    arr1 = Sheets("汇总A").UsedRange
    Set d1 = CreateObject("scripting.dictionary")
    Set d2 = CreateObject("scripting.dictionary")
    Set d3 = CreateObject("scripting.dictionary")
    Set d4 = CreateObject("scripting.dictionary")

    For j = 2 To UBound(arr1)
        d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) + arr1(j, 24)
        d2(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77)) + arr1(j, 37)
        d3(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 2)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 2)) + arr1(j, 37)
        d4(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 1)) = d1(arr1(j, 7) & arr1(j, 4) & arr1(j, 77) & arr1(j, 1)) + arr1(j, 37)
    Next j

    arr2 = Sheets("汇总B").UsedRange
    For i = 2 To Sheets("汇总B").Range("a1048576").End(3).Row
        arr2(i, 10) = d1(arr2(i, 1) & arr2(i, 7) & arr2(i, 8))
        arr2(i, 11) = d2(arr2(i, 1) & arr2(i, 7) & arr2(i, 8))
        arr2(i, 12) = d3(arr2(i, 1) & arr2(i, 7) & arr2(i, 8) & arr2(i, 14))
        arr2(i, 13) = d4(arr2(i, 1) & arr2(i, 7) & arr2(i, 8) & arr2(i, 14))

    Next i
    Sheets("汇总B").UsedRange = arr2

这样吗，会报错呢

tigertc

求指教。。。。