[01] 숨어지내는 Personal.xls(b) 파일 활용기 - 고유항목필터링

[이현욱]

[시작]에서 간단히 Personal 파일이 어디서 어떤 역활을 하는지 잠시 살펴보았습니다.

간단히 요약하면 XLSTART 라는 오피스 특수 폴더에서 엑셀을 실행할때 숨겨진 상태로 열려서 어떤 작업을 도와 준다 는 것입니다.

그런데 어떤 작업을 도와 줄 수 있을까요?

그것은 사용자한데 달려 있습니다.

VBA 한 번이라도 사용해 보신 분들을 위주로 진행하겠습니다. 간단한 설명은 하겠지만 기본은 VBA 강좌에서 공부하십시오.

그럼 첫 번째 팁을 넣어 보며 진행해 보도록 하겠습니다.

1 . 고유항목 필터링

아래 참고 그림을 보면 꽃이름이 나열되어 있습니다. 여기에는 중복된 항목이 많이 있는데 이중에서 고유항목을 간편하게 마우스 오른쪽 버턴을 누르고 쉽게 필터링을 해 보겠습니다.

우선 첫 셀의 영역에는 헤드 값이 있어야 합니다. 물론 헤드 값에는 값이 없어도 됩니다. 즉, 선택한 영역의 데이터 중에서 첫 셀을 제외한 고유한 항목을 필터링해 보겠습니다.

엑셀의 고급필터를 이용하여 VBA를 작성하겠습니다.

아래와 같이 매크로가 기록되었습니다.

Sub 선택영역고유항목필터링()

'

' 선택영역고유항목필터링 Macro

    Range("A1:A20").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

        Range("A1:A20"), Unique:=True

End Sub

 

 아래와 같이 수정해보겠습니다. 변수를 설정하고 사용자가 언제든지 영역을 설절할 수 있게 R 이라는 Range 변수를 만들겠습니다.

R 이라는 Range 변수에 현재 선택 영역에 따라 반환 영역을 활당합니다.

선택한 내용이 없으면 Selection 속성은 Nothing을 반환합니다. 이때 오류가 발생하겠죠! 이를 무시하기 위해 프로시저 선두에 On Error Resume Next 문을 넣었습니다. 오류가 나타나면 무시하고 진행해라라는 문입니다.

 

Sub 고유항목필터링() '현재 선택영역 고유항목 고급필터링

    Dim R As Range

    On Error Resume Next

    Set R = Selection

    R.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=R, Unique:=True

End Sub

 

아주 간단하죠! 고급필터 과정을 매크로로 기록한후 편집했습니다.

Range.AdvancedFilter 메서드 참고하시고 공부 하세요.

 

그럼 이것을 쉽게 사용하려면 메뉴에 넣어야하는데 리본메뉴에 넣으면 좀 불편하겠죠.

그래서 아래 그림과 같이 셀메뉴어 넣어 보겠습니다.

이과정은 좀 어려우니 참고만 하세요.

프로시저 이름은 아래와 같이 했습니다.

Sub 메뉴등록_고유항목필터링()

   Dim oCtl As CommandBarButton

   On Error Resume Next

 

   Application.CommandBars("Cell").Controls("현재영역고유항목필터링").Delete '기존 메뉴를 지우고

   Set oCtl = Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, ID:=1, temporary:=True, Before:=1)

   With oCtl

       .FaceId = 560

       .Caption = "현재영역고유항목필터링"

       .Style = msoButtonIconAndCaption

       .OnAction = ThisWorkbook.Name & "!고유항목필터링"

       .BeginGroup = True

   End With

 

End Sub

별로 어려워보이진 않습니다.

CommandBars 개체를 도움말에서 공부하시고 셀메뉴에 넣는 방법이니 이것을 통째로 모듈형식으로 저장해두었다가 사용하시면 됩니다. 대부분의 VBA사용자들이 코드를 다 외워서 사용하진 않습니다.

마지막으로 엑셀에서 이메뉴를 사용할 수 있게 Personal파일이 열릴때 메뉴를 등록할 수 있도록 Auto_Open 이라는 특수 프로시저를 생성하고 이곳에 위 "메뉴등록_고유항목필터링" 프로시저를 호출하도록 작성해 보겠습니다.

Auto_Open 프로시저는 엑셀 일반 모듈 어떤 곳에 넣어 두면 이 모듈이 있는 엑셀 파일이 열릴때 프로시저를 실행합니다.

Sub Auto_Open()

    Call 메뉴등록_고유항목필터링 '선택영역 고유항목필터링

End Sub

 

이파일을 닫을 때도 실행하는 프로시저도 있겠죠. 아래와 같습니다.

이 곳에 셀메뉴를 해제하는 코드를 넣어 둡니다.

 

Sub Auto_close()

    On Error Resume Next

    Application.CommandBars("Cell").Controls("현재영역고유항목필터링").Delete '선택영역 고유항목필터링메뉴를 지웁니다.

End Sub

 

그럼 실행해 볼까요?

아래 데이터를 엑셀에 워크시트에 붙여넣고 Personal 파일 모듈에 위 프로시저 3개를 넣고 Auto_Open을 한번 실행 해준 후 워크시트에서 영역을 선택하고 마우스 우측 버턴을 눌러 보세요.

 

아주 간단한 코드 몇 줄로 엑셀을 편리하게 사용할 수 있습니다. 이것이 Personal.xls 라는 파일이 하는 일이죠. 물론 추가기능파일로 제작해도 상관은 없습니다.

 

아마 정말 편리하고 자주 쓰일겁니다.

 

꽃이름

개나리 

갯버들 

국화 

그로키니시아 

글라디올러스 

개나리 

금연화 

금잔화 

개나리 

꽃창포 

개나리 

나팔꽃 

난초 

국화 

노송나무 

단풍나무 

국화 

다알리아 

달맞이꽃 

그럼 이만...

[윤슬]

안녕하세요?

멋진 강좌입니다...
(팁이라기 보단 강좌에 가깝군요...)
그리고 어차피 2007 버젼이라면 위와 같은 경우라면 2007에 추가된 중복된 항목 제거를 이용하는것도 나쁘지 않을듯 합니다.
Selection.RemoveDuplicates Selection.Columns.Count, xlNo

멋진 강좌 다시한번 감사합니다...!

[이현욱]

감사합니다.
RemoveDuplicates 메서드 추가항목 제거도 좋은 기능인데 현재영역에서 고유항목을 확인한 후 이를 복사 등으로 이용할 수 있고, 원본이 유지 된다는 것이 장점입니다.
쉽게 마우스로 영역을 선택하고 고유항목이 있는지 확인하고 보이는 영역만 선택하여 활용할 수 있지요.
원본시트에서 삭제되면 곤란합니다.ㅎㅎ

[윤슬]

원본을 그대로 두고자 해서 그리 적용한 것이었군요...
역시 DB관리, 활용을 많이 해 본 냄새가 풀풀 나는것이... ^^*

[이현욱]

ㅎㅎ 엑셀 기능이 많다보니 친한 기능만 사용하는 귀차니즘이 발동합니다. 일반기능 어렵게 반복적으로 사용하다보며 이산화 탄소 배출량이 증가하거든요.
그리고 참고로 냄새만 납니다.ㅎㅎㅎ^^

[smkim]

오, 이거 완전 좋네요. 중복값 제거로 저는 기능을 좀 바꿨는데요 아주 유용하네요. 근데요 제가 이 매크로가 들어있는 파일에서는 메뉴등록되어서 잘 되는데요 다른 엑셀파일을 열면 그 메뉴가 없어지는데 이걸 항상 다른 파일에서도 메뉴가 보이게 하려면 어떻게 해야 하나요?