• 최초 작성일: 2023-08-26
  • 최종 수정일: 2023-08-26
  • 조회수: 1,933 회
  • 작성자: 권현욱 (엑셀러)
  • 강의 제목: VB0263 ㅡ 중복된 항목 제거 VBA 코드 활용 ㅡ 품목별 요약표 만들기

엑셀러 권현욱

들어가기 전에

최근 강의(Y206, VB0262)에서 중복 값을 제거하는 방법들에 대해 살펴보았습니다.

"엑셀 함수로도 할 수 있고, '중복된 항목 제거' 기능으로도 해결할 수 있다는데 VBA를 꼭 알아야 하나요?"

이런 의문을 갖는 분들이 있을 듯 합니다. 중복을 제거하고 고유한 값만 추출하는 것이 목적이라면 그렇게 생각할 수도 있습니다.

하지만 거기서 한 걸음 더 나아가 자료를 가공하거나 분석하는 등 추가 작업을 해야 한다면 함수나 기본 기능만으로는 해결할 수 없습니다.



  • 이 콘텐츠는 '엑셀러TV' 멤버십 회원 '우선 공개' 콘텐츠입니다. 전체 대상으로는 9월 2일 공개 예정입니다.

  • 멤버십 회원에게는 예제 파일을 보내드립니다 ( 멤버십 보러 가기).

  • VBA에 익숙하지 않은 분들은 전반적인 흐름을 이해하는 데 주안점을 두고 보시기 바랍니다.

멤버십 회원님들은 다음 동영상 강의와 함께 보시면 학습효과가 높아집니다(멤버십 전용 영상).

로딩 중...

멤버십 전용

완성 예

[품목 업데이트] 버튼을 클릭하면 G4:H4 영역에 콤보 박스가 자동으로 만들어집니다. 콤보 박스에서 '품목'을 선택하면 그에 해당하는 데이터가 추출됩니다.

콤보 박스Combo Box는 드롭다운 목록DropDown List이라고도 부릅니다.

B열의 품목이 변경되었다면 [품목 업데이트] 버튼을 누르면 반영되며, 오름차순으로 표시됩니다. 콤보 박스에서 품목을 선택하면 그에 해당하는 자료와 요약표가 만들어집니다.

로딩 중...

완성 예

상황을 단순화시켜기 위해서 '합계'만 표시했지만 필요에 따라 얼마든지 지표를 추가할 수 있습니다. 필요한 것은 VBA 역량과 문제 의식입니다.

Step 1: 콤보 박스에 품목 채우기

콤보 박스를 채우는 코드를 작성합니다. 이 때 B열에 있는 품목 중에서 중복되지 않게 고유 값만 추출하고 오름차순으로 채웁니다.

  1. 지금까지와는 달리, 프로시저를 작성하기 전에 변수를 선언합니다. 여러 프로시저에서 공통적으로 사용되는 변수는 모듈 시작 부분(Option Explicit 문 아래)에 선언합니다. 이런 변수를 '전역 public 변수'라고 합니다. 반면에 특정 프로시저 내부에서만 영향력을 가진 변수는 '로컬 local 변수'라고 부릅니다.

  2. Dim shtData As Worksheet
    Dim rTbl As Range
    Dim oDropDown As DropDown

    CODE 1

  3. 드롭다운 버튼에 '품목'을 오름차순으로 추가하기 위한 코드이므로 이름을 'fillDropDownByAscendingOrder'라고 지정했습니다. 조금 길지만 이름만 보고도 무슨 일을 하는 프로시저인지 알 수 있도록 하는 것이 좋습니다. 등장 인물과 배역을 지정하는(변수 선언, 값 할당) 것으로 코딩을 시작합니다.

  4. Sub fillDropDownByAscendingOrder()
    Dim oItems As New Collection    ''' 품목을 담을 컬렉션 개체
    Dim rX As Range        ''' 각 품목 셀에 접근하기 위한 순환 변수
    Dim sItems() As String    ''' 품목을 담을 동적 배열
    Dim sX As String    ''' 품목을 정렬할 때 사용
    Dim i As Integer, j As Integer    ''' 품목을 정렬할 때 사용
    
    On Error Resume Next
    Set shtData = Worksheets("연습")
    Set rTbl = shtData.Range("A1").CurrentRegion
    Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)

    CODE 2

    VBA 강의를 하다 보면 다음 두 줄에서 헷갈려 하는 분들이 많습니다.

    Set rTbl = shtData.Range("A1").CurrentRegion
    Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)

    원하는 범위에 접근하기 위해 개체 변수를 재정의하는 과정입니다. 이유는? A1 셀 인접 영역에서 제목(1행)을 제외한 영역을 rTbl 변수에 담기 위해서입니다. 빈번하게 사용되는 패턴이며, 거의 공식처럼 사용됩니다.

  5. 시트에 이미 만들어진 콤보 박스가 있다면 지우고 새로 만듭니다.

  6. shtData.DropDowns("myDropDown").Delete
    With shtData.Range("G4")    ''' G4셀을 기준으로 드롭다운 박스 삽입
        Set oDropDown = shtData.DropDowns.Add(.Left, .Top, .Width * 2, .Height)
        With oDropDown
            .Name = "myDropDown"
            .OnAction = "filterDropDownItem"
        End With
    End With

    CODE 3

    콤보 박스를 포함한 모든 도형은 '왼쪽/위쪽/너비/높이'를 지정하는 방식으로 삽입할 수 있습니다. 이에 해당하는 속성은 각각 Left, Top, Width, Height입니다.

    드롭다운 개체의 OnAction 속성을 이용하면 항목을 선택했을 때 특정한 프로시저가 실행되도록 설정할 수 있습니다.

  7. 고유 항목을 추출하여 컬렉션 개체 변수 oItems에 담습니다. 그런 다음, 각 항목을 오름차순으로 정렬하기 위한 준비를 합니다.

  8. For Each rX In rTbl.Columns(2).Cells
        oItems.Add rX, rX
    Next
    ReDim sItems(oItems.Count)    ''' 동적 배열 변수의 크기 지정
    For i = 1 To oItems.Count
        sItems(i) = oItems(i)
    Next

    CODE 4

    컬렉션 개체에 대한 세부 내용은 다음 포스트를 참고하세요.
    컬렉션 개체 이용

    RiDim 문은 동적 배열 변수의 크기를 재할당할 때 사용합니다. oItems 컬렉션 개체의 항목 수만큼 공간을 확보한 다음, 배열 변수 sItems에 하나씩 값을 전달합니다. 컬렉션 개체의 항목 그대로는 정렬할 수 없기 때문에 동적 배열 변수를 이용합니다.

  9. 배열 변수 sItems에 들어 있는 항목을 오름차순으로 정렬합니다. For ~ Next 안에 또 For ~ Next가 들어 있는 중첩 구조를 사용했습니다.

  10. For i = UBound(sItems) To LBound(sItems) Step -1
        For j = 1 To i
            If sItems(j) > sItems(j + 1) Then
                sX = sItems(j)
                sItems(j) = sItems(j + 1)
                sItems(j + 1) = sX
            End If
        Next
    Next

    CODE 5

    바깥쪽 For ~ Next에서는 배열 변수의 가장 큰 요소(즉 마지막 값)부터 작은 요소로 접근한 점에 유의하세요. Bound 앞에 붙어 있는 U와 L은 각각 Upper, Lower를 뜻합니다.

    뒤에 있는 값을 바로 앞에 있는 값과 비교해서 뒤의 값이 앞의 값보다 크다면 서로 위치를 바꿉니다. 이 과정을 모든 요소들에 대해 진행합니다.

  11. 이제 배열 변수 sItems에는 중복되지 않는 고유한 값이 담겨 있습니다. 이 값들을 콤보 박스에 추가합니다.

  12. For i = LBound(sItems) To UBound(sItems)
        If Len(sItems(i)) <> 0 Then 
            oDropDown.AddItem sItems(i)
        End If
    Next
    End Sub

    CODE 6

    배열은 0부터 시작합니다. 따라서 빈 값이 들어 있는 배열 요소를 콤보 박스에 표시하지 않도록 하기 위해 If 문으로 걸러주었습니다. If와 End If를 빼고 실행시켜 보면 차이를 아실 수 있습니다.

  13. 프로시저 내부에 커서를 두고 F5 키를 눌러 프로시저를 실행하면 B의 폼목 중에서 고유한 값들이 오름차순 정렬되어 콤보 박스에 표시됩니다.

  14. 로딩 중...

    프로시저 실행 결과

    코드를 자세히 설명하려고 과정을 나눠서 소개했더니 되려 어수선해 보이는 듯도 합니다. 코드에 넘버링을 해 두었으니 찬찬히 뜯어서 보시기 바랍니다(멤버십 회원님들은 예제 파일의 코드 참고). 앞으로 계속 반복해서 나오니까 지금 모르겠다고 걱정할 필요는 없습니다.

Step 2: 선택한 품목 요약표 만들기

콤보 박스에서 품목을 선택하면 그에 해당하는 요약표를 만드는 코드를 작성합니다. oDropDown 개체의 OnAction 속성에서 호출하는 프로시저이므로 이름이 같아야 합니다.

  1. 등장 인물과 배역을 설정합니다.

  2. Sub filterDropDownItem()
    Dim rWrite As Range    ''' 결과를 출력할 위치
    Dim rRow As Range    ''' rTbl 영역의 각 행에 접근
    Dim sItem As String    ''' 콤보 박스에서 선택한 품목 저장
    Dim iRow As Integer
    
    On Error Resume Next
    Set shtData = Worksheets("연습")
    Set rTbl = shtData.Range("A1").CurrentRegion
    Set rTbl = rTbl.Offset(1).Resize(rTbl.Rows.Count - 1)
    Set oDropDown = shtData.DropDowns("myDropDown")
    Set rWrite = oDropDown.TopLeftCell.Offset(3)
    rWrite.CurrentRegion.Clear    ''' 결과를 표시할 영역 청소

    CODE 1

    콤보 박스가 있는 위치를 기준으로 출력 위치를 결정하고자 합니다. 그러려면 당연히 콤보 박스가 어디 있는 지 알아야겠죠? 이럴 때 사용할 수 있는 것이 TopLeftCell 속성입니다. 말 그대로, 지정한 개체의 윗쪽/왼쪽 위치를 알 수 있습니다. 현재 콤보 박스가 G4:H4 영역에 있으므로 TopLeftCell은 G4 셀이 됩니다.

  3. 콤보 박스에서 선택된 값(항목)과 rRow(rTbl 영역의 각 행)의 2번째 셀 값이 일치하는 지 조건 비교해서 같은 경우 처리를 합니다.

  4. sItem = oDropDown.List(oDropDown.ListIndex)
    rWrite.Offset(-1).Resize(, 4) = Array("분류", "품목", "날짜", "실적")
    For Each rRow In rTbl.Rows
        If rRow.Cells(2) = sItem Then
            rRow.Copy rWrite.Offset(iRow)
            iRow = iRow + 1
        End If
    Next

    CODE 2

    콤보 박스에서 어떤 값이 선택되었는 지 확인해서 변수에 저장할 때, ListIndex 속성으로 위치값을 파악한 다음, List 속성으로 그 값을 가져온다는 점을 눈여겨 보시기 바랍니다.

    sItem = oDropDown.List(oDropDown.ListIndex)
  5. 가독성을 높이기 위해 요약표의 제목과 서식을 지정합니다.

  6. ''' 요약표의 타이틀 작성
    With rWrite.Offset(-5)
        .Value = sItem & " 품목 집계표"
        .Font.Bold = True
        .Font.ColorIndex = 3
    End With
    
    ''' 요약표 각종 서식 지정
    With rWrite.CurrentRegion
        .Borders.LineStyle = xlSolid
        .Rows(1).HorizontalAlignment = xlCenter
        .Rows(1).Interior.ColorIndex = 15
        .Columns(3).ShrinkToFit = True
        .Columns(4).EntireColumn.NumberFormat = "#,###"
    End With

    CODE 3

    직설적(?)인 내용이라 단어만 잘 해석해도 무슨 기능을 하는 건지 알 수 있을 겁니다. 표 내부에 있는 Range 개체에 어떻게 접근하는 지를 봐두시면 되겠습니다.

  7. 요약표 맨 아래쪽에 '합계'를 표시하고 서식을 지정합니다. 여기서는 두 가지에 유의하시면 됩니다.

  8. With rWrite.Offset(rWrite.CurrentRegion.Rows.Count - 1, 2).Resize(, 2)
        .Value = Array("합계", Application.SumIf(rTbl.Columns(2), sItem, rTbl.Columns(4)))
        .Borders.LineStyle = xlSolid
        .Borders.Weight = xlThick
        .Interior.ColorIndex = 6
        .Font.Bold = True
    End With
    End Sub

    CODE 4

    서식을 지정하려면 대상 영역(Range 개체)에 접근하는 것이 먼저입니다. 표의 맨 마지막에 어떻게 접근하려면 어떻게 해야 할까요? rWrite 인접 영역의 행 수를 센 다음, 오른쪽으로 2열 만큼 이동합니다(I열부터 표시하기 위해). Offset과 Resize 속성에 대해 잘 익혀 두면 수많은 변주가 가능합니다.

    rWrite.Offset(rWrite.CurrentRegion.Rows.Count - 1, 2).Resize(, 2)

    다른 계산 작업이 필요할 경우, 워크시트 함수를 이용하면 편리합니다. 여기서는 집계표 마지막에 품목의 합계를 표시했고, SUMIF 함수를 사용했습니다.

    Application.SumIf(rTbl.Columns(2), sItem, rTbl.Columns(4))

    함수 앞에 Application이나 WorksheetFunction 중 하나(또는 둘 다)를 붙여주면 되며, 사용 방법은 워크시트에서 사용할 때와 같습니다. '엑셀에 대해 많이 알수록 VBA에 더 쉽게 다가갈 수 있다'고 Exceller가 노래를 부르다시피 하는 건 그래서입니다.

비교적 간단한 로직이지만 이 정도만 하더라도 함수나 엑셀 기본 기능으로는 처리하기 힘듭니다. 아, Filter 함수를 이용하여 간단한 요약표 만드는 것은 서두에 소개해 드린 영상에 있으니 참고하세요. 멤버십 회원이 아닌 분들은 Filter 함수(Microsoft 365용 함수)를 검색해 보시면 되겠습니다.

#ComboBox #ListIndex #TopLeftCell #SumIf #Offset #Resize