Sumifs ()를 사용하는 더 빠른 방법
매주 약 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
답변
다른 방법이 있습니다.
편집-내 초기 (잘못된) "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
@ 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