rand(life)
Dictionary에 Item을 배열로 넣기 본문
Scripting.Dictionary를 이용한 방법이다. 지식인에 올라온 질문에 대한 답으로 작성했다.(들어가려면 성인 인증이 필요하다)
특정 시간이 3회 이상 등장한 경우, 해당 시간에 발생한 수량을 합산하고,
같은 수량이 특정 구간에 3회 이상 반복 등장할 경우, 그런 수량들을 모두 더하는 결과를 나타낸다.
Scripting.Dictionary를 이용해서 3회 이상 등장하는 시간을 Key로, 해당 시간에 발생한 수량을 Item으로 더하는 방법은 이전에 빈도 순위 높은 문자, 숫자 찾기에서 사용했던 방법과 동일하다.
If Va(iv, 1) >= ds And Va(iv, 1) <= de Then '시간이 해당 구간에 있으면
d(Vc(iv, 1)) = d(Vc(iv, 1)) + 1 '시간의 '수량'에 있는 숫자를 key로, 그 숫자의 출현횟수를 item으로
이런 식으로 d 에 key와 item을 모든 뒤
ak = d.keys() '수량
ai = d.items() '수량의 횟수
이렇게 배열로 모두 넣은 다음
For i = 0 To d.Count - 1
If ai(i) >= 3 Then iCount = iCount + (ak(i) * ai(i))
Next
순환문을 이용해 item이 2이상이면 item의 출현횟수를 변수에 더해준다.
같은 시간이 3회 이상 등장했을 때, 그 수량의 합계를 내는 것이 약간은 어려웠는데
구간 안에 같은 시간이 등장하는 것이 여러개일 수 있어서
key는 시간으로 등록해야하는데
item에 들어갈 것은 그 시간에 등록된 수량과 해당 시간(key)이 출현한 횟수 두 개를 모두 넣어야하기 때문이다.
그래야 나중에 해당 시간(key)이 출현한 횟수가 3개 이상인지 판단한 다음
그 시간(key)의 item을 더해줄 수 있다.
해결 방법은 역시 Scripting.Dictionary에 있었는데
http://www.snb-vba.eu/VBA_Dictionary_en.html
[번역] Data Dictionary in VBA - 구문(Syntax) 작성의 완성이라는 글에
"Dictionary Object가 Collection보다 유리한 점"이 몇가지 쓰여 있다.
• 여러 Key(예를 들어 대소문자 식별 Key)에 대한 비교 Method를 지정하는 옵션.(??)
• Dictionary에 Object가 존재하는지 판별하는 메소드
• 복수의 Key값을 전부 묶어 하나의 배열로 집어 넣는 메소드
• 복수의 Item값을 전부 묶어 하나의 배열로 집어 넣는 메소드
• Key 값을 변경하는 메소드
• Dictionary로부터 모든 Item 개체를 삭제하는 메소드
• Dictionary Key가 String 데이터형식으로 제한 되지 않는 점
이 중 마지막 항목이 중요한데, Collection과는 달리 Dictionary에서는 item에 텍스트외에 여러가지를 넣을 수 있다. 셀이나 시트 같은 개체도 가능하고, 배열도 가능하다.
이 점에 착안하여, item에 배열을 넣음으로써 해결한다.
If dTime.exists(Va(iv, 1)) Then
dTime(Va(iv, 1)) = Array(dTime(Va(iv, 1))(0) + 1, dTime(Va(iv, 1))(1) + Vc(iv, 1))
'key는 시간, item(0)은 같은시간의 출현횟수, item(1)은 그 시간의 '수량'
Else
dTime(Va(iv, 1)) = Array(1, Vc(iv, 1))
End If
dTime이라는 dictionary에 key는 시간, item에는 배열을 넣고, item(0)에는 출현횟수를, item(1)에는 그 시간의 '수량'을 넣는다.
이렇게 넣고 난 다음
aTimeItem = dTime.items()
item을 배열로 만들면, 만들어진 배열도 마찬가지로 배열이 된다.
그래서 아래와 같이 순환문을 이용해 출현횟수가 3이상인지 확인한 다음, 수량을 더해주면 된다.
For i = 0 To dTime.Count - 1
If aTimeItem(i)(0) >= 3 Then iTc = aTimeItem(i)(1) + iTc
Next
아래는 완성본.
작업완료까지 소요된 시간을 넣는 코드도 추가했다.
Option Explicit
Sub macro()
Dim ds As Date, de As Date
Dim r As Range
Dim i As Long, loc As Long, iCount As Long, iTc As Long
Dim ak, ai, Va, Vc, Vr()
Dim iv As Long, iResult As Long
Dim d As Object, dTime As Object
Set d = CreateObject("Scripting.Dictionary")
Set dTime = CreateObject("Scripting.Dictionary")
Dim starttime As Date, endtime As Date
Dim aTimeItem
starttime = Timer
Va = Range("A2", Cells(Rows.Count, "a").End(xlUp))
Vc = Range("c2", Cells(Rows.Count, "C").End(xlUp))
For Each r In Range("G2", [G2].End(xlDown))
loc = InStr(1, r, "~")
ds = CDate(Left(r, loc - 1)) '시작시간
de = CDate(Right(r, Len(r) - loc)) '끝시간
For iv = LBound(Va) To UBound(Va)
If Va(iv, 1) >= ds And Va(iv, 1) <= de Then '시간이 해당 구간에 있으면
d(Vc(iv, 1)) = d(Vc(iv, 1)) + 1 '시간의 '수량'에 있는 숫자를 key로, 그 숫자의 출현횟수를 item으로
If dTime.exists(Va(iv, 1)) Then
dTime(Va(iv, 1)) = Array(dTime(Va(iv, 1))(0) + 1, dTime(Va(iv, 1))(1) + Vc(iv, 1))
'key는 시간, item(0)은 같은시간의 출현횟수, item(1)은 그 시간의 '수량'
Else
dTime(Va(iv, 1)) = Array(1, Vc(iv, 1))
End If
End If
Next
ak = d.keys() '수량
ai = d.items() '수량의 횟수
aTimeKey = dTime.keys()
aTimeItem = dTime.items()
For i = 0 To d.Count - 1
If ai(i) >= 3 Then iCount = iCount + (ak(i) * ai(i))
Next
For i = 0 To dTime.Count - 1
If aTimeItem(i)(0) >= 3 Then iTc = aTimeItem(i)(1) + iTc
Next
ReDim Preserve Vr(1, iResult)
Vr(0, iResult) = iTc
Vr(1, iResult) = iCount
iResult = iResult + 1
d.RemoveAll
dTime.RemoveAll
Erase ak
Erase ai
iCount = 0
iTc = 0
Next
Range("H2").Resize(iResult, 2) = Application.Transpose(Vr)
endtime = Timer
MsgBox "완료~ " & Round(endtime - starttime, 0) & " 초 걸렸습니다"
End Sub
아울러, 대용량의 데이터에 접근할 때는 셀에 직접 접근하기 보다는 셀의 내용을 변수로 받은 뒤, 변수에서 처리하는 것이 훨씬 빠르다는 것을 실감했다.
위의 경우에서는, 특정 조건에 맞는 셀이 몇개인지를 세는 과정이 들어가야하는데
i = Application.WorksheetFunction.CountIf(rng, r)
이런 식으로 했더니 8분이 걸렸고
배열이라 countif함수를 쓸 수 없었지만
Vc = Range("c2", Cells(Rows.Count, "C").End(xlUp))
이런 식으로 배열로 받은 다음 배열 안에서 계산하게 했더니
2초(!)만에 끝났다
