스크립팅 딕셔너리

결론부터 해보자면, 이렇게 된다!

딕셔너리의 사전적 의미는 사전이다 (훗)

사전에는 여러 "고유의" <단어>가 있고, 각 단어의 의미나 <뜻>이 "여러가지로" 설명되어있다.

우리는 종종 하나의 값에 매칭되는 여러개의 데이터를 정리해야할 필요가 있다.

아주 다양한 방법이 있는데, 생각나는대로 나열해보면...

 

1. LOOKUP 류 엑셀 내장함수 이용

 - LOOKUP

 - VLOOKUP

 - HLOOKUP

2. INDEX & MATCH 중첩 함수 활용

3. (엑셀 버전에 따라) XLOOKUP, UNIQUE & FILTER 함수 활용

4. 배열(CSE)함수 활용

5. VBA에서의 배열 처리

6. 스크립팅 딕셔너리(Scripting Dictionary) 활용

등 이 있다.

 

저마다 상황에 따라 쓰기 용이한 방식이 있겠지만, 그 중 스크립팅 딕셔너리를 활용해서 아래와 같은 데이터를 처리해보자.

각 분류에 여러개의 항목을 정리해보자

우선 코드는 이렇다.

Sub 딕셔너리연습()
    '변수 선언
    Dim 동적범위 As Range, 활성범위 As Range
    Dim i As Long
    Dim 키값 As String, 밸류값 As String
    '딕셔너리 후기바인딩
    Set 딕셔너리 = CreateObject("Scripting.Dictionary")

    '화면갱신/경고알림창 off
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    '해당 동적범위를 동적범위 변수로 설정
    Set 동적범위 = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
    
    '해당 동적범위를 순환하는 순환문 작성
    For Each 활성범위 In 동적범위
        
        '각 키값과 밸류값의 변수 설정
        키값 = 활성범위
        밸류값 = 활성범위.Offset(, 1)
        
        '값이 이미 있을때와 없을때의 데이터 누적방식 지정
        If 딕셔너리.Exists(키값) Then
            딕셔너리(키값) = 딕셔너리(키값) & "," & 밸류값
        Else
            딕셔너리.Add 키값, 밸류값
        End If
    Next
    
    '곧 데이터 입력될 섹션 초기화
    Range("e1").CurrentRegion.Offset(1, 0).Clear
    
    '딕셔너리값을 행열전환하여 삽입
    Range("e2").Resize(딕셔너리.Count, 1) = Application.Transpose(딕셔너리.Keys)
    Range("f2").Resize(딕셔너리.Count, 1) = Application.Transpose(딕셔너리.Items)
    
    '밸류값의 동적범위 설정
    Set 동적범위 = Range("f2:f" & Cells(Rows.Count, "f").End(xlUp).Row)
    
    '텍스트나누기, 해당범위부터, 구분자는 콤마
    동적범위.TextToColumns Destination:=Range("f2"), comma:=True
    
    '서식설정
    With Range("e1").CurrentRegion
        .Borders.LineStyle = 1
        .HorizontalAlignment = xlCenter
    End With
    
    '화면갱신/경고알림창 on
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
        
End Sub

 

뭐 이미 주석 처리를 여럿 해두었지만,

주요 코드를 나름의 방식대로 해석해봤다.

정석적인 해석은 아니고, ... 이해가 되는 방식으로 말이다.

 

1. 후기 바인딩

Set 딕셔너리 = CreateObject("Scripting.Dictionary")

나 니네가 만든 딕셔너리라는것 좀 쓸게요~

그리고 그걸 나는 "딕셔너리" 라고 부를게.

 

2. 동적 범위 설정

Set 동적범위 = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)

예제에서는 분류하고자하는 데이터의 시작셀이 A2셀이고, 갯수는 가변적으로 변한다.

이 구문을 이용하면 A2부터 시작해서, 상황에 따라 데이터의 갯수가 달라지는때에도 그 전체를 범위로 설정할 수 있게 된다.

 

3. For Each 구문을 통한 반복문

For Each 활성범위 In 동적범위
	...
Next

내 범위를 하나씩 순환하면서, 코드를 적용시킨다.

쉽게 말해, A2 → A3 → A4 , ... → A마지막셀 이런순서로.

 

4. 변수 할당

키값 = 활성범위
밸류값 = 활성범위.Offset(, 1)

반복문에서 활성된 범위의 값을 "키값" 과 "밸류값" 이라는 변수에 할당한다.

키값은, 위에서 설정한 A열의 값일테고, ..

밸류값은 그 우측에 있는 값이어야하기 때문에, offset 을 통해 바로 옆 데이터를 지칭해준다.

Offset(행, 열)이며 쉼표가 먼저들어간 것은 0이 생략 가능하기 때문이다.

따라서, Offset(, 1)은 "1개열 옆 데이터"를 의미한다. 즉, 예제에서 B열.

 

5. 딕셔너리 구문

If 딕셔너리.Exists(키값) Then
	딕셔너리(키값) = 딕셔너리(키값) & "," & 밸류값
Else
	딕셔너리.Add 키값, 밸류값
End If

키값은, 중복되어 들어가면 안되고 (사전에 단어가 중복되어 들어가지 않듯,)

따라서 exists 매서드를 이용해 이미 그 값이 있는지 없는지 여부를 if 조건절로 판단하는 구문이다.

 

"만약 키값이 이미 존재한다면, 원래 있던 기존의 데이터를 ","(구분자가 콤마)로 연결시켜서 값을 재지정하고

만약 키값이 존재하지 않는다면, 키값과 밸류값을 추가해!"

라는 뜻이다.

/// 여기까지가 반복문, 아직은 가상의 공간에 값을 할당했을 뿐, 값을 뿌리기 직전 상태이다!

 

6. 값 뿌리기

Range("e2").Resize(딕셔너리.Count, 1) = Application.Transpose(딕셔너리.Keys)
Range("f2").Resize(딕셔너리.Count, 1) = Application.Transpose(딕셔너리.Items)

예제에서의 e2셀과 f2셀이 각각 데이터가 입력되어야 할 첫 셀이다.

사전으로 비유하면, e2셀에는 키값 즉, 단어에 해당되는 값이 들어가야하고

f2셀에는 밸류값(아이템값) 즉, 뜻에 해당되는 값이 들어가야한다.

resize 매서드를 통해 딕셔너리에 담긴 데이터의 갯수만큼 범위를 확장시키고,

transpose를 이용해서 행/열이 전환된 키값과 아이템 값을 넣어준다.

여기까지 실행시키면, 이런 형태로 실행된다!

7. 콤마로 연결된 데이터를 각 열의 셀에 텍스트나누기

Set 동적범위 = Range("f2:f" & Cells(Rows.Count, "f").End(xlUp).Row)
동적범위.TextToColumns Destination:=Range("f2"), comma:=True

다시 밸류값의 범위를 동적으로 설정해주고,

TextToColumns 매서드를 통해, 첫 범위위치와 구분자를 명시해주면.

엑셀에서의 텍스트나누기 효과가 구현된다.

 

8. 표서식 입히기

With Range("e1").CurrentRegion
	.Borders.LineStyle = 1
	.HorizontalAlignment = xlCenter
End With

e1셀과 연관된 모든 범위의

외곽선과 정렬 방식을 설정해준다.

 

With 구문을 이용하면, 반복해서 입력해야하는 코드를 생략할 수 있게 해준다.

위 구문을,

Range("e1").CurrentRegion.Borders.LineStyle = 1

Range("e1").CurrentRegion.HorizontalAlignment = xlCenter

라고 길게 반복하는 것은 비경제적이므로, 그럴때 With 구문을 활용한다고 생각하면 된다.

 

9. 결과

짜잔~

(이 모든 것은, 엑사남님 유튜브 채널에서의 강좌를 통해 습득했습니다.)