Sumifs ()를 사용하는 더 빠른 방법

Nov 21 2020

매주 약 500 행씩 증가하는 보고서 (현재 50K 행이 약간 넘음)를 업데이트해야하는 주간 작업이 있습니다. 새 데이터를 수동으로 추가 한 후 아래 코드를 실행 Sumifs()하여 데이터를 요약합니다.

데이터 구조는 다음과 같습니다. 열 A에서 C는 기준 열 (숫자-영숫자)이고 열 D에는 합계 할 수량 (정수)이 있습니다. 데이터는 연속적입니다. 내 매크로는 Sumifs()수식을 E 열에 넣 습니다. 거기에있는 것을 덮어 씁니다.

내 질문은 :이 작업을 더 빨리 수행 할 수 있습니까? 현재 매크로를 실행하는 데는 1 분 정도 걸리지 만 데이터가 증가함에 따라 더 오래 걸립니다.

이 사이트에는 배열을 사용하여 작업을 더 빨리 수행하는 방법에 대한 많은 내용이 있지만 예제 중 어느 것도 나에게 의미가 없으며 가능한 경우 사용하지 않는 것이 좋습니다.

Sub MySumIfs()
Dim LastRow As Long

LastRow = Sheet1.Range("A1").End(xlDown).Row

With Sheet1.Range("E2:E" & LastRow)
    .FormulaR1C1 = "=sumifs(R2C4:R" & LastRow & "C4, R2C1:R" & LastRow & "C1, RC1, R2C2:R" & LastRow & "C2, RC2, R2C3:R" & LastRow & "C3, RC3)"
    .Value = .Value
End With

End Sub

답변

2 TimWilliams Nov 21 2020 at 13:33

다른 방법이 있습니다.

편집-내 초기 (잘못된) "countifs"버전에 "averageifs"및 "sumifs"를 추가하도록 업데이트되었습니다.

Sub SetupDummyData()
    Const NUM As Long = 100001
    Range("A1:E1").Value = Array("A_Header", "B_Header", "C_Header", "Value", "ResultHere")
    Range("A2:A" & NUM).Formula = "=""A#"" & round(RAND()*10,0)"
    Range("B2:B" & NUM).Formula = "=""B#"" & round(RAND()*10,0)"
    Range("C2:C" & NUM).Formula = "=""C#"" & round(RAND()*10,0)"
    Range("D2:D" & NUM).Formula = "=round(RAND()*100,1)"
    
    Range("A2:D" & NUM).Value = Range("A2:D" & NUM).Value
End Sub


Sub Tester()
    
    Dim arr, ws, rng As Range, keyCols, valueCol As Long, destCol As Long, i As Long, frm As String, sep As String
    Dim t, dict, arrOut(), arrValues(), v, tmp, n As Long
    
    keyCols = Array(1, 2, 3)  'these columns form the composite key
    valueCol = 4              'column with values (for sum)
    destCol = 5               'destination for calculated values
    
    t = Timer
    
    Set ws = ActiveSheet
    Set rng = ws.Range("A1").CurrentRegion
    n = rng.Rows.Count - 1
    Set rng = rng.Offset(1, 0).Resize(n) 'exclude headers
    
    'build the formula to create the row "key"
    For i = 0 To UBound(keyCols)
        frm = frm & sep & rng.Columns(keyCols(i)).Address
        sep = "&""|""&"
    Next i
    arr = ws.Evaluate(frm)  'get an array of composite keys by evaluating the formula
    arrValues = rng.Columns(valueCol).Value  'values to be summed
    ReDim arrOut(1 To n, 1 To 1)             'this is for the results
    
    Set dict = CreateObject("scripting.dictionary")
    'first loop over the array counts the keys
    For i = 1 To n
        v = arr(i, 1)
        If Not dict.exists(v) Then dict(v) = Array(0, 0) 'count, sum
        tmp = dict(v) 'can't modify an array stored in a dictionary - pull it out first
        tmp(0) = tmp(0) + 1                 'increment count
        tmp(1) = tmp(1) + arrValues(i, 1)   'increment sum
        dict(v) = tmp                       'return the modified array
    Next i
    
    'second loop populates the output array from the dictionary
    For i = 1 To n
        arrOut(i, 1) = dict(arr(i, 1))(1)                       'sumifs
        'arrOut(i, 1) = dict(arr(i, 1))(0)                      'countifs
        'arrOut(i, 1) = dict(arr(i, 1))(1) / dict(arr(i, 1))(0) 'averageifs
    Next i
    'populate the results
    rng.Columns(destCol).Value = arrOut
    
    Debug.Print "Checked " & n & " rows in " & Timer - t & " secs"

End Sub
1 kevin9999 Nov 21 2020 at 12:00

@ RuthMac77 당신은 chris neilsen의 조언을 듣고 가능한 배열 솔루션을 찾기 위해 SO를 검색해야합니다. 아니면 Google에서 배열 자습서를 검색해야합니다. 거기에는 많은 것이 있습니다.

그렇게 말하면서 몇 년 전 여기에서 이와 매우 유사한 질문에 답했습니다 . 귀하의 설명을 사용하여 50, 000 행의 데이터로 설명 한 데이터 구조를 복제했습니다. 기존 코드를 사용하여 테스트하는 데 약 55 초가 걸렸습니다.

아래에 설명 된 연결 / 정렬 / IF 방법을 사용하여 동일한 데이터를 계산하는 데 1.5 초 밖에 걸리지 않았습니다. 코드를 모듈에 복사하고 어떻게 사용하는지 알려주세요.

Option Explicit
Sub FasterThanSumIfs()
Application.ScreenUpdating = False

Dim LastRow As Long
LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

'Step 1: Concatenate the 3 values to a single string then sort by that string
With Sheet1.Range("E2:E" & LastRow)
    .FormulaR1C1 = "=(RC1 & CHAR(32) & RC2 & CHAR(32) & RC3)"
    .Value = .Value
End With
Sheet1.Columns("A:E").Sort Key1:=Sheet1.Range("E2"), Order1:=xlAscending, Header:=xlYes
Sheet1.Sort.SortFields.Clear

'Step 2: calculate the sum range column where the concatenated values are the same
With Sheet1.Range("F2:F" & LastRow)
    .FormulaR1C1 = "=IF(RC5=R[-1]C5,RC4+R[-1]C6,RC4)"
    .Value = .Value
End With

'Step 3: sort by string then by summed values largest to smallest to
'place the largest values at the top of each concatenated values' 'list'
Sheet1.Columns("A:F").Sort Key1:=Range("E2"), Order1:=xlAscending, _
Key2:=Range("F2"), Order2:=xlDescending, Header:=xlYes
Sheet1.Sort.SortFields.Clear

'Step 4: Return the highest value for each concatenated string
With Sheet1.Range("G2:G" & LastRow)
    .FormulaR1C1 = "=IF(RC5=R[-1]C5,R[-1]C7,RC6)"
    .Value = .Value
End With

'Step 5: replace the concatenated string values in column E with
'the Sumifs() values from column G.  Column E now contains the correct Sumifs()
'values as if a Sumifs() formula had been used - only much quicker!

Sheet1.Range("G2:G" & LastRow).Copy Sheet1.Range("E2")
Sheet1.Range("F:G").Clear

Application.ScreenUpdating = True
End Sub