고급필터를 이용한 회사별 시트만들기

아이엑셀러

Step 1: 어떻게 해결할까요?

(1) 데이터를 회사별, 월별 순으로 정렬을 한 다음 시트를 회사 수만큼 삽입한 다음 각 시트에 '복사-붙여 넣기'를 한다? 아주 노동집약적인(과격하게 표현하면 아주 무식한 ^^) 방법입니다.

(2) 피벗 테이블을 사용하여 회사명을 쪽 구분 기준으로 설정한다? 어느 정도까지는 가능하지만 별도의 시트로 추려내기가 어렵습니다.

(3) Exceller에게 해결해 달라고 떼를 쓴다. 어지간한 떼(?)에 넘어갈 Exceller가 아니지요? 힌트는 조금 얻고 숙제를 한보따리 받으시게 될 것입니다. ^^

그럼 어떻게 하라고요?

이럴 때에는 우리가 익히 알고있는 고급 필터 기능을 잘 활용하시면 됩니다. "말되 안돼! 고급 필터를 사용해서 어떻게 시트별로 데이터를 분리해욧?!" 이렇게 생각하시는 분은 아래의 버튼을 눌러 보세요.

뭐가 휙휙 거리긴 했는데... 화면 하단의 시트탭을 보니 어느새 각 회사별 시트가 만들어 졌지요? 이렇게 VBA를 알면 한방에 해결이 되는데 아직도 VBA를 배우지 않으려는, 혹은 그런 것이 있는 줄도 모르는 분이 있다는 것은 참으로 안타까운 일입니다.

컴퓨터 보유 대수만 세계에서 몇 등이고, 초고속 통신망 보급율이 세계에서 1~2위를 다투면 뭐합니까? 아무리 인프라가 잘 구축되어 있어도 그것을 어떻게 사용하느냐가 관건이지요. 짱짱한 컴퓨터를 가지고 맨날 게임이나 하고 있고(그렇다고 게임을 비하하는 것은 아니지만...) 인터넷으로 쇼핑이나 하고 이상한 사이트를 귀신같이 발견하는 것을 정보검색이라고 생각하고 있다면 그것은 오산입니다. 강좌하다말고 또 흥분을... ^^;

다시 제자리로 돌아가서... 코드를 보도록 하지요.

Step 2: 공통 변수와 Master 프로시저

Dim shtSource As Worksheet
Dim shtFilter As Worksheet
Dim rngSource As Range
Dim rngTarget As Range
'''여러 프로시저에서 공통적으로 사용되는 변수들을 프로시저 외부에 선언해
'''주었습니다. 이런 변수를 전역 변수라고 합니다.

Sub Master()
    Call ExtractUnique
    Call IsSheetExist
    Call MakeCompanySheet
    '''Master라는 프로시저 내에서 세 개의 외부 프로시저를 호출합니다.
End Sub

Step 3: 회사명 뽑아내기 (ExtractUnique)

먼저 ExtractUnique라는 프로시저가 먼저 실행됩니다. 이것은 Source 시트에 있는 데이터들 중에서 회사명을 중복되지 않도록 하나씩 뽑아내는 프로시저 입니다.

Sub ExtractUnique()
    Dim intNum As Integer
    Dim Msg As String
    Set shtSource = Worksheets("Source")
    Set shtFilter = Worksheets("Filter")
    Set rngSource = shtSource.[A2]
    Set rngTarget = shtFilter.[A1]

    rngTarget.CurrentRegion.Clear
    rngSource.CurrentRegion.Columns(1).AdvancedFilter action:=xlFilterCopy, _
                                                  copytorange:=rngTarget, _
                                                  unique:=True
   '''고급 필터를 통해 중복되지 않은 자료를 추출할 때 '고유 레코드만' 항목에
   '''체크를 해 주었지요? 이것을 코드로 표현하면 위와 같이 되는 것입니다.

    intNum = Application.CountA(shtFilter.Columns(1)) - 1
    Msg = Msg & "모두 " & intNum & " 개의 Unique 항목을 추출하였습니다" & vbCr
    Msg = Msg & """확인""" & "을 누르시면 회사별 시트를 만듭니다"
    MsgBox Msg, , "회사명 추출 완료//By Exceller"
End Sub

Step 4: 같은 이름의 시트 삭제하기 (IsSheetExist)

두번째로 실행하는 IsSheetExist 프로시저는 회사별 시트를 만들기 전에 현재의 워크북 중에 같은 시트가 이미 만들어져 있는지를 검사하여 삭제하는 프로시저 입니다.

Sub IsSheetExist()
    Dim rngCell As Range, rngStart As Range
    Dim shtName As Worksheet
    Set rngStart = Sheets("Filter").[A2]
    Set rngTarget = Sheets("Filter").Range(rngStart, rngStart.End(xlDown))

    For Each rngCell In rngTarget
        For Each shtName In Sheets
            If rngCell = shtName.Name Then
                Application.DisplayAlerts = False
                MsgBox shtName.Name & "시트는 이미 만들어진 것이 있으나 지우겠습니다." _
                    , , "동일 시트 삭제//By Exceller"
                shtName.Delete
            End If
            '''순환문을 두번 돌면서 회사이름과 같은 시트명이 있는지를 검사해서
            '''있으면 삭제를 합니다.

        Next shtName
    Next rngCell
    Application.DisplayAlerts = True
End Sub

Step 5: 회사별 시트 만들기 (MakeCompanySheet, FilterOut)

아래의 코드가 오늘의 핵심 부분입니다.

Sub MakeCompanySheet()
    Dim rngCell As Range, rngTarget As Range
    Dim rngCriteria As Range
    Dim i As Integer
    Dim strSheetname As String, Msg As String
    Application.DisplayAlerts = False
    Set shtSource = Worksheets("Source")
    Set rngSource = shtSource.[A2].CurrentRegion

    With shtFilter.[A1]
        If IsEmpty(.Offset(1, 0)) Then
            Application.DisplayAlerts = True
            Exit Sub
        '''Filter 시트의 A열에 데이터가 없으면 프로시저를 종료합니다.

        Else
            Set rngTarget = shtFilter.Range(.Offset(1, 0), .End(xlDown))
            .Offset(0, 2) = .Value
            Set rngCriteria = shtFilter.Range(.Offset(0, 2), .Offset(1, 2))
            '''우선 회사별 데이터를 만들기 위해서는 고급 필터를 다시 한번 사용해야
            '''합니다. 그러기 위해 Criteria, 즉 필터링의 기준을 작성합니다.

            For Each rngCell In rngTarget
                strSheetname = rngCell
                .Offset(1, 2) = strSheetname
                Call FilterOut(rngSource, rngCriteria, strSheetname)
               '''Filter 시트의 A열에 있는 회사 수만큼 순환문을 돌면서 FilterOut 이라는
            '''외부 프로시저를 수행합니다.

            Next rngCell
        End If
    End With

    Application.DisplayAlerts = True
    Application.Goto Sheets("Preface").[A55], True
    MsgBox "시트별 출력 작업을 완료하였습니다", vbExclamation, "작업 완료//By Exceller"
End Sub

Sub FilterOut(ByVal rngSource As Range, ByVal rngCriteria As Range, ByVal strSheetname As String)
'''MakeCompanySheet 프로시저로부터 세 개의 인수를 넘겨받아 회사별 시트를
'''만드는 코드입니다. 여기서도 고급 필터를 사용하였습니다. 코드는 크게 어려운
'''것이 없습니다.

    Worksheets.Add after:=Sheets(Sheets.Count)
    With ActiveSheet
        Set rngTarget = .[A1]
            rngSource.AdvancedFilter action:=xlFilterCopy, _
                                 criteriarange:=rngCriteria, _
                                 copytorange:=rngTarget, _
                                 unique:=False
        rngTarget.Parent.Name = strSheetname
        '''여기서 Parent라는 새로운 것이 나왔지요? 이것은 Parent 즉, 문자 그대로
        '''아버지 속성이라고 할 수 있습니다. rngTarget이 레인지 오브젝트이니까
        '''이것의 아버지(상위) 속성은... 워크시트 속성이 될 것입니다.
        '''"왜 그렇지요?" 하시는 분은 아직 엑셀의 계보에 대해 공부하셔야 할 부분이
        '''많은 분입니다. 그런 분은 도움말에서 Excel의 Hierarchy에 대한 부분을 좀더
        '''공부하시기 바랍니다.

    End With
End Sub

마무리

오늘은 코드가 길군요. 코드가 길 경우에는 이처럼 토막을 내어서 각 프로시저별 업무분장을 지정해 놓으면 나중에 다시 보았을 때에 이해하기 쉬은 것은 물론이려니와 유지보수 등 여러 가지 측면에서 효율적이라 할 수 있습니다.

다음 시간에...

정리 — 회사별 시트 만들기 핵심

자주 묻는 질문 (FAQ)

마치며

고급 필터를 두 번 활용하고 프로시저를 나누어 작성하면 회사별 시트 만들기도 한 번에 끝낼 수 있습니다.