엑셀 고급필터와 매크로기능 실무예제

엑셀 고급필터와 매크로기능 실무예제 

앞에서 자동필터와 고급필터에 대해서 자세히 알아보았습니다. 오늘은 고급필터와 매크로기능이 합해질경우 반복적인 업무를 더 빠르고 효율적으로 처리할수 있습니다. 간단한 실무예제를 통해  기본개념을 익힐수 있도록 하겠습니다. 

 

 

썸네일

엑셀 고급필터와 매크로기능 실무예제

 

 

엑셀 고급필터 복습

 

어제 배운 고급필터기능을 복습해보면 맨위의 두행은 조건을 둔것이고, 아래 행들은 상품등록데이터로 원본데이터입니다.  고급필터를 적용하면 상품명이 남성이고 판매가가 50000 초과이고 기본배송비가 3000원인 데이터를 지정해둔 영역에 복사를 수행할것입니다. 

 

 

위의 그림은 데이터를 복사할 영역입니다.  행열을 빠뜨리고 편집했습니다.

 

 

리스트 레인지는 원본데이터의 전체범위를 나타내고,, 조건범위는 위의 A2셀부터 D3셀까지의 조건영역을 나타냅니다.다른곳에 복사를 선택후 복사할 머리글행을 선택하면 조건에 맞는 데이터가 머리글행을 참조하여 그 아래행부터 나타납니다. 이것들은 어제 배운 내용이며 어제 배운 내용을 학습하고 싶다면 아래링크를 클릭해보세요.

 

>>>엑셀 자동필터와 고급필터, 차이점과 장단점 활용사례<<<--클릭하세요

 

 

 

매크로기능익히기

단순히 고급필터기능만 사용하면 매번 단순한 일을 반복적으로 해야하는 번거로움이 있어, 고급필터에 매크로를 달아주면 아주 효율적입니다. 시간도 단축될것입니다.  매크로와 더불어 심플한 VBA를 배워 활용하면 좋습니다.

 

 

매크로 버튼을 만들기전에 먼저 매크로를 기록하고 기록한 코드를 통해 입맞에 맞게 편집하는 방법을 알아둡니다. 메뉴에 Developer(개발자) 클릭해보세요.. 혹시 개발자 메뉴가 보이지 않은 경우 맨위의 리본영역에 마우스 우측버튼클릭시 Customize the Ribbon이 보이면 클릭하여 개발자를 활성화시켜줍니다. 개발자의 Record Macro를 클릭하여 매크로를 기록하여줍니다. 

 

 

그럼 위의 이미지처럼 Macro name입력난에 고급필터와매크로를 입력후 확인을 누릅니다.. 매크로이름은 위의 이미지처럼 빈공간이 있으면 안되고 이름을 붙여서 써주어야합니다. 이제부터는 원본데이터에 고급필터를 적용해야합니다. 데이터에 고급필터버튼을 클릭해줍니다.

 

 

위에서 배운 고급필터를 적용했던 방법대로 고급필터버튼을 클릭하여 팝업창이 뜨면 차례로 원본데이터의 전체범위와 조건범위와 필터링하여 복사할 영역을 지정해준후 확인을 누릅니다.. 

 

그럼 L7셀부터 필터링되어 복사된것을 확인할수 있을것입니다.

 

상품명이 남성으로 시작하면서 판매가가 50000을 넘고 기본배송비가 3000인 상품리스트들을 L7행부터 나타난것을 확인할수 있습니다. 결과가 나왔으니, 이제는 기록된 매크로를 정지하고, 기록된 매크로를 편집하여 사용자입맛에 맞게 고치는 방법에 대해서 알아보겠습니다.. 

 

기록된 매크로를 불러오기 위해 리본영역의 개발자의 매크롤를 클릭합니다... 그럼 위와 같은 VBA편집창이 나오고 위와같은 코드들이 매크로 기록을 통해 저장된것을 확인할수 있습니다.

 

위의 코드를 분석해보면 Range("A6:J218").AdvancedFilter  Action:=xlFilterCopy, 는 A6셀부터 J218열 즉 원본데이터 전체영역을 의미하며, 원본데이터를 고급필터를 통해 필터링하여 복사하겠다는 의미입니다.

 

위의 이미지와 내용이 좀 다릅니다만 ,CriteriaRange:=Range("A2:D3").CurrentRegion 조건영역인 A2셀부터 D3셀까지의 영역을 의미합니다. 복사할 영역은 L6셀부터 R6셀까지의 머리글을 참조하여 그다음행 즉 L7행부터 필터링하겠다는 의미입니다..

 

그런데 저 영역들은 정해진 영역이고 원본데이터라는것이 더 많을 수도 있고 더 적을수도 있고 유동적이어서 고정된 범위안에서는 우리가 원하는 데이터를 얻는데 제한될수 있습니다.. 그래서 CurentRegion이라는 속성을 사용하여 유동적인 범위를 커버할 때 사용할수 있습니다. 그래서 위의 코드들을 편집해보면 

 

Sub 고급필터()

  Range("A6").CurrentRegion.AdvancedFilter _
  Action:=xlFilterCopy, _
  CriteriaRange:=Range("A2").CurrentRegion, _
  CopytoRange:=Range("L6").CurrentRegion.Rows(1), _
  Unique:=False 

 

End Sub

 

A6셀부터 연결된 모든 데이터를 나타나며 A2셀부터 연결된 셀을 조건으로 셋팅하고 L6행의 머리글을 참조하여 L7행부터 복사하겠다는 의미입니다. 

버튼을 만들어 매크로와 연결하기 

 

위의 그림처럼 매크로버튼을 만들어보겠습니다.

삽입(Insert)- shape-둥근사각형을 선택한후 적당히 F2셀위에 그려줍니다. 도형에 색상을 넣고 우측마우스버튼을 눌러 Text를 편집합니다.. 매크로버튼이라 입력한후, 버튼을 마우스우측버튼으로 클릭후 하면 위의 그림처럼 팝업창이 뜨면서 Assign Macro가 보입니다.

 

그럼 고급필터와매크로를 클릭후 확인버튼을 클릭합니다. 이제 버튼에 매크로기능이 셋팅이 됬습니다. 

매크로버튼을 클릭하여 봅니다. 그러면 아까 버튼만들기전 고급필터를 적용한 결과와 동일한 결과가 나옵니다..

 

 

이제 다른 조건을 걸어봅니다.. 위의 그림은  두행으로 조건을 걸었지만, 그이상도 가능합니다.. 조건행이 늘어날수록 필터링된 데이터는 더 많아질겁니다.. 왜냐하면 같은 행에서는 And로  행과 행사이는 OR로 논리식이 적용되기때문입니다. 

위의 조건은 상품명이 종근당으로 시작하면서 판매가가 50000미만이고 기본배송비가 무료인 데이터이거나 상품명이 겨울을 포함하면서 재고수량이 2000을 넘고 기본배송비가 3000인 데이터를 복사하여 지정된 곳에 복사하는 것도 가능할것입니다.

 

L7행에 위의 조건에 맞는 결과값이 출력된 모습을 알수 있습니다.

 

여기에 더해  지정된 영역에 복사된 데이터를 삭제하는 반복적인 작업도 매크로로 기록하여 초기화버튼을 눌렀을 경우 데이터를 삭제하는 기능도 시도해볼수 있습니다.

 

힌트를 주자면 복사할 지정된 영역을 선택한후 글상자에 삭제영역 혹은 초기화영역이라고 정해두고 지정된영역을 F5키를 누르면 팝업창이 뜨는데, 거기서 초기화영역을 선택후 del키를 쿨러 삭제하는것을 매크로로 기록후 버튼을 만들어 좀전 만든 매크로를 적용해볼수 있을것입니다.

 

 끝맺는말 

지금까지 실무예제를 통해 엑셀 고급필터와 매크로기능에 대해서 자세히 알아보았습니다..
다음시간에도 실용적인 실무예제를 통해  고급 엑셀을 학습하겠습니다.