엑셀 VBA #127 / Vlookup시리즈3_배열+Dictionary활용 [VBA]

Sub Vlookup기능실현_New_Scripting_Dictionary_배열231228()
      
      Dim dict As New Scripting.Dictionary
      Dim rngS, rngF
      Dim i As Long
      Dim arr()
      
      rngS = Sheet1.Range("a2", Sheet1.Cells(Rows.Count, "b").End(3)) 'rngS배열방 지정
      rngF = Sheet2.Range("a2", Sheet2.Cells(Rows.Count, "a").End(3)) 'rngT배열방 지정
      
      For i = 1 To UBound(rngS, 1)
            If dict.Exists(rngS(i, 1)) Then
            Else
                  dict.Add rngS(i, 1), rngS(i, 2)
                  'rngS(i,1)은 Key값, rngS(i,2)는 Item값임!!!!!
                  'cells(i,1)..cells(i,2)가 아님에 주의
            End If
      Next

      ReDim arr(1 To UBound(rngF, 1), 1 To 1)
      For i = 1 To UBound(rngF, 1)
            If dict.Exists(rngF(i, 1)) Then
                  arr(i, 1) = dict(rngF(i, 1))
                  ' "rngF(i,1)"은 Key값이고, 
                  "dict(rngF(i,1))"은 그 Key값에 해당하는 Item의 값임!!!!!!!
            Else
            End If
      Next
      
      Sheet2.Range("e2").Resize(UBound(arr, 1), 1) = arr

End Sub

엑셀 VBA #118 / Dictionary 개체_기본 [VBA]
엑셀 VBA #119 / Dictionary 개체_실무 [VBA]



https://www.youtube.com/watch?v=I4iyh9tF5kc&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=129
ㅁㅁ

엑셀 VBA #126 / Vlookup시리즈2_배열 활용 [VBA]
Vlookup 함수같은 기능
   1. 파워쿼리
   2. 배열
   3. 배열 + Dictionary

Sub VlookupVBA_1()   '배열로 Vlookup기능실현

Dim rngS As Variant     '워크시트의 셀범위를 배열로 집어 넣을 땐 항상 Variant임
Dim rngF As Variant      '           "
Dim arr()


Application.ScreenUpdating = False
Sheet2.Range("E2:E" & Sheet2.Range("A2").End(xlDown).Row).Clear

rngS = Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "B").End(3))

셀 범위'Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "B").End(3))'를 rngS방에 넣기
rngF = Sheet2.Range("A2", Sheet2.Cells(Rows.Count, "A").End(3))
셀 범위'Sheet2.Range("A2", Sheet2.Cells(Rows.Count, "A").End(3))'를 rngS방에 넣기

ReDim arr(1 To UBound(rngF, 1), 1 To 1) '?????
For i = 1 To UBound(rngF, 1)        'UBound(rngF,1) : rngF의 1차원영역의 최대값
    For j = 1 To UBound(rngS, 1)   'UBound(rngS,1) : rngS의 1차원영역의 최대값
        'Sheet2.Cells(i, "E") = rngS(j, 2) '본 코드사용시 속도저하(배열코드가 아니라 워크시트 코드?이므로)
                If rngF(i, 1) = rngS(j, 1) Then
                        arr(i, 1) = rngS(j, 2)
                        Exit For '영상에서 이 코드는 빼먹었습니다.    넣어야 합니다.

                                       '1:1로 매칭되므로 값을 찾았으면 안쪽 for문을 빠져나와야지

                                       '그렇지 않으면 끝까지 루프를 돌므로 시간이 더 소요가 됩니다.
                End If
    Next
Next

Sheet2.Range("E2").Resize(UBound(arr, 1), 1) = arr
Application.ScreenUpdating = True

End Sub

https://www.youtube.com/watch?v=QM-o-95daIc&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=128

엑셀 VBA #29 / VBA함수(split) [VBA]

엑셀 VBA #119 / Dictionary 개체_실무 [VBA]

Range("A1").CurrentRegion.Copy Range("D1")
Range("D1").CurrentRegion.RemoveDuplicates Array(1, 2), xlYes
'array(1,2) : 제거할 인수가 CurrnetRegion영역의 1열과, 2열!!!
'xlYest 영역에 머리글이 포함되어 있음

최종코딩

Sub Pdictionary()
    Dim dict As New Scripting.Dictionary
    Dim rngS As Range, c As Range
    Dim i As Long
    Dim s As Variant
    
    Application.ScreenUpdating = False
    
    Columns("D:I").Clear
    Range("A1").CurrentRegion.Copy Range("D1")
    Range("D1").CurrentRegion.RemoveDuplicates Array(1, 2), xlYes
    'array(1,2) : 제거할 인수가 CurrnetRegion영역의 1열과, 2열!!!
    'xlYest 영역에 머리글이 포함되어 있음
    
    Set rngS = Range("D2", Cells(Rows.Count, "D").End(xlUp))
    
    For Each c In rngS
        If dict.Exists(c.Value) Then
            dict(c.Value) = dict(c.Value) & "," & c.Offset(, 1)
        Else
            dict.Add c.Value, c.Offset(, 1)  '딕셔너리명.Add Key값,Item값
            'dict 딕셔너리에 c의 key값과,c에서 1열 떨어진(offset(,1)) item값을 넣어라
        End If
    Next
    
Set rngt = Range("G1")

For i = 0 To UBound(dict.Items)
    s = Split(dict.Items(i), ",")  '딕셔너리 각각의 item의 값이 ","로 분리된 값을 임의의 배열방 s에 저장한다.
    rngt.Offset(0, i) = dict.Keys(i)
    rngt.Offset(1, i).Resize(UBound(s) + 1, 1).Value = Application.Transpose(s)   
Next

Columns("C:E").Delete
 Application.ScreenUpdating = True
  
  Set dict = Nothing
  Set Rng = Nothing
  Set c = Nothing
  Set rngS = Nothing
  Set rngt = Nothing
End Sub

엑셀 VBA #65 / 질문 답변(데이터 취합) [VBA]    RemoveDuplicates (Array(1,2)

엑셀 VBA #47 / 중복데이터 처리_1 [VBA]- Removeduplicates. 고급필터. New Collection

https://www.youtube.com/watch?v=SrOrTwZuxXA&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=121

ㅇ

엑셀 VBA #118 / Dictionary 개체_기본 [VBA]

[New Dictionary]
Dim dict As Oject
Set dict = CreateObject("scripting.dictionary")
위 두 문장으로 된 구문( Late Binding이라 함)을

아래 하나의 문장으로 된 구문( Early Binding이라 함)으로 할 수 있다.

Dim dict As New Scripting.Dictionary



1.구조 
Dictionary이름명. Add  Key  Item


2. 예시
dict.Add "사과",111
dict.Add "바나나",222
dict.Add "딸기",333

3. For ~ Next문에 적용

3.1. 기본
Dim dict As New Scripting.Dictionary
Dim i As Long

dict.Add "사과", 111
dict.Add "바나나", 222
dict.Add "딸기", 333

For i = 0 To 2
    Cells(i + 1, 1) = dict.Items(i)
Next

위 구문의 문제점은 Item 갯수가 아주 많은 경우에는 처리속도가 느려진다는 것이다..

그래서 Key  및 Item값을 워크시트에 한꺼번 뿌려줄 필요가 있다.

이것을 할 수 있는 방법이 '배열'이다.

3.1. 동적배열

Dim dict As New Scripting.Dictionary
Dim i As Long
Dim arr() As Variant    '동적배열 적용. arr()의 값 유형을 모르니 Variant로 한다.

dict.Add "사과", 111
dict.Add "바나나", 222
dict.Add "딸기", 333

For i = 0 To 2
    'Cells(i + 1, 1) = dict.Items(i)
    ReDim Preserve arr(i)   ' 동적배열 적용문으로 동적배열의 사이즈를 i값으로 재선언
    arr(i) = dict.Items(i)        'item(i)값을 arr방에 집어넣기!!!!
Next

이제는 arr방에 들어온 item(i)값을 시트에 뿌려보자

......

   arr(i) = dict.Items(i)
Next

range("A1:A3") =arr

메모리 상에서는 arr은 아래 그림처럼 가로로 담겨져 있다.

이를 A1:A3로 올바르게 뿌려지기 위해서는 Transpose함수를 아래처럼 적용해야 한다.

range("A1:A3") =Application.transpose(arr)

3.1.1. UBound & LBound

For~Next문에서 i의 마지막 값을 프로그램이 자동으로 지정할 수 있도록 해야한다. 하드코딩하는 방법을 바람직하지 않다.프로그램이 알아서 값을 지정할 수 있도록 하는 변수에는 UBound와 LBound가 있다.

For i = 0 To 2

For i = 0 To UBound(dict.Items)   

4. 최종 코드

Sub Pdictionary()

Dim dict As New Scripting.Dictionary
Dim i As Long
Dim arr() As Variant

dict.Add "사과", 111
dict.Add "바나나", 222
dict.Add "딸기", 333

For i = 0 To UBound(dict.Items)
    'Cells(i + 1, 1) = dict.Items(i)
    ReDim Preserve arr(i)
    arr(i) = dict.Items(i)
Next

Range("A1:A3") = Application.Transpose(arr)
 
End Sub

VBA - Dictionary(Late vs Early Binding) by 우노사설

https://www.youtube.com/watch?v=1yBE6MGkIA4&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=120

엑셀 VBA #49 / New Collection_버블정렬의 콜라보 [VBA]

Dim nc As New Collection
Dim rng As Range, c As Range
Dim a() As Variant
Dim i As Long

Set rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))

'----------------------------
'고유목록 추출
'----------------------------
On Error Resume Next
For Each c In rng
    If Len(c) Then
        nc.Add Trim(c), CStr(Trim(c))
    End If
Next
On Error GoTo 0

'---------------------------
'배열에 넣기(dc에서 저장하고 있는 고유목록을 동적배열a에 넣기)
'---------------------------
ReDim a(nc.Count - 1)

For i = 0 To nc.Count - 1
    a(i) = nc(i + 1)
Next

'---------------------------
'동적배열a에 있는 값을 뿌리기
'---------------------------
Range("C1").Resize(1, UBound(a) + 1) = a
Range("B1").Resize(UBound(a) + 1, 1) = Application.Transpose(a)

위 결과값 그림상의 고유항목들이 오름 or 내림차순 정렬이 아니다.
이를 오름차순 정렬한 코드는 다음과 같다.
Dim nc As New Collection
Dim rng As Range, c As Range
Dim a() As Variant
Dim i As Long, j As Long
Dim temp As Variant

.................
'---------------------------
'배열에 넣기(dc에서 저장하고 있는 고유목록을 동적배열a에 넣기)
'---------------------------
ReDim a(nc.Count - 1)
For i = 0 To nc.Count - 1
    a(i) = nc(i + 1)
Next

'---------------------------
'정렬(버블정렬)
'---------------------------
For i = 0 To dc.Count - 2
    For j = i + 1 To dc.Count - 1
        If a(i) > a(j) Then
            temp = a(i)
            a(i) = a(j)
            a(j) = temp
        End If
    Next
Next
'---------------------------
'동적배열a에 있는 값을 뿌리기
'---------------------------
Range("C1").Resize(1, UBound(a) + 1) = a
Range("B1").Resize(UBound(a) + 1, 1) = Application.Transpose(a)

https://www.youtube.com/watch?v=uuyRUNZC2GE&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=49

엑셀 VBA #41 / 행 삭제_개선2 [VBA] - 배열

Sub 행삭제_배열_myexr231224()

Dim r As Variant   '모든 데이타를 넣을 배열방r....variant적용
Dim a()            '배열방r의 데이타 중 'A-1'이외 값을 넣을 배열방a....동적배열적용
Dim i As Long, j As Long, k As Long

'-----------------------------------------------------
'값을 각 배열방에 넣는 과정
r = Range("A1", Cells(Rows.Count, "B").End(xlUp))
ReDim a(1 To UBound(r, 1), 1 To 2)   
'동적배열의 영역 확정, a는 2차원이므로 a(1차원,2차원)으로 표시해야함
'1차원 크기는 1부터 r의 행 갯수, 2차원 크기는 1부터 2열까지

For i = 1 To UBound(r, 1) ' UBound(r,1)=UBound(1): r배열의  1차원 위치의 최대값까지
      If Cells(i, "A") <> "A-1" Then   'cells(i,"A")의 값이 "A-1"이 아니면
            j = j + 1    'j의 초기값이 지정이 없으므로 지정해야하는데, 
            'j값은 1부터 시작해야 하므로 'j=j+1'코드 설정함
            For k = 1 To 2    '열변호를 확장시킬 변수가 필요함
                  a(j, k) = r(i, k) 'i는 r방에 대응하는 변수이므로,
                  'i를 a방에 대응할 수 없으니 별도의 변수j를 대입함                  
            Next
      End If
Next
'------------------------------------------

'-------------------------------------------
'a방 배열 값을 뿌려주기
Range("A1").CurrentRegion.ClearContents
Range("A1").Resize(UBound(a, 1), 2) = a  
'셀A1을 기준으로 "a배열의 1차원 최대값 및 2차원 최대값"만큼 리사이즈한 영역에 a배열값을 넣어준다.
'-------------------------------------------
End Sub

https://www.youtube.com/watch?v=c7rkH3le9cs&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=41

엑셀 VBA #29 / VBA함수(split) [VBA]

1. 목적 : VBA함수 Split 사용법 습득
2. Split함수 구조
VBA.Split(대상,구분자)   또는
Split(대상, 구분자)

3. 사용
  3.1. For~Next문
    Dim s As Variant
    Dim i As Long

    s = Split(Range("A2"), " ")

    For i = 0 To UBound(s)
        Cells(i + 5, "A") = s(i)
    Next

  3.2. For Each~Next문
    Dim s As Variant
    Dim j As Long

    For Each s In Split(Range("A2"), " ")
        Cells(j + 5, "A") = s
        j = j + 1
    Next

@ayje5718   2년 전
좋은강의 잘 보고 있습니다. 감사합니다. 이전 강의에서 동적 배열을 선언하는 영상을 보고 해당 영상을 보니 갑자기 헷갈립니다 ㅠ 변수방에 값을 받아 배열로 출력하는부분에 동적 배열을 선언해 사용한다고 생각했는데 아니네요 ㅠ 그럼 변수에 배열을 선언하는건 어떤 경우인가요
      @user-dq4gc5hl5j   2년 전
      안녕하세요, 반갑습니다 :)
       프로시저의 처리속도를 높이기 위해서는 배열 사용이 필수입니다. 
       그런데 배열을 이해하고, 실무에 적용하는데 시간이 조금 소요됩니다.
       한번에 이해가 안되도 조급해하지 마시고
       여러 코딩 경험을 통해 배열 개념이 정리되는 시간을 주셔요^^
       먼저 VBA #126 영상을 살펴보시길 추천합니다.

https://www.youtube.com/watch?v=OCbhs5a71ZQ&list=PLfxvqpVCYZ8e0qlyc_FU46neoWjO7yTWj&index=29