[VBA] 양식 컨트롤을 이용한 데이터 추출 프로그램 만들기(고급 필터)

 

# 목표
 - 고급 필터와 양식 컨트롤을 사용하여 데이터 검색/추출 결과를 깔끔하고 효율적으로!!
 - Index, If 등의 함수를 통해 엑셀 내 데이터를 분류하고, 필요한 VBA 속성을 이해한다. 
 - 엑셀 WorkSheet에서 사용하는 함수를 VBA 코드에서도 적용시킬 수 있다.

 

[ 검색필터링을 통한 필요한 데이터 추출 구현 VBA ]

 

 

---

 

1. 데이터 확인 및 검색 Form 만들기

위의 영상처럼 데이터 검색/추출하는 프로그램을 본격적으로 만들기 전에 실습에서 사용되는 데이터를 확인해보고, 해당 데이터를 검색하는 형식(Form)을 먼저 만들어보자. ex) 인사고과 검색 결과

1-1. 데이터 파악

- 아래 예시로 첨부한 이미지에는 ' 1) 인사고과 데이터  2) 조건필터 Sheet  3) 데이터 검색 Sheet '로 구성 
- 인사고과 데이터에는 가상 직원의 사번, 성명, 인사/근태 고과 등의 개인 정보가 있으며, 조건 시트에는 인사고과 데이터를 기반으로 검색결과 Sheet에 원하는 데이터를 추출하기 위한 징검다리 역할을 하는 조건필터가 포함되어 있고, 마지막으로 검색결과 시트는 최종적으로 데이터가 추출되는 Sheet이다.
 → 조건 시트 내, '조건필터 조건', '양식컨트롤 연결'은 아래 실습을 통해 채운다.

 

[ Sheet 분석 - 인사고과 데이터, 조건 시트, 검색결과 시트 ]

1-2. 검색폼(Form) 생성하기 

데이터 검색하는 Form은 사용자의 목적과 취향에 따라서 다르게 생성할 수 있지만, 아래 사진에는 기본적인 도형과 그룹박스로 Form의 형태를 잡고, '콤보상자(소속, 직급)', '옵션단추(평가)'를 통해 표현했다.

 

 

- 그룹 상자 : '개발 도구 - 삽입 - 양식 컨트롤 - 그룹상자' 를 삽입 후 텍스트 입력
 → 소속, 직급, 평가 영역 박스
- 콤보 상자 : '개발 도구 - 삽입 - 양식 컨트롤 - 콤보상자' 선택
 → 소속, 직급에 추가
- 옵션 단추 : '개발 도구 - 삽입 - 양식 컨트롤 - 옵션단추' 선택
 → 평가에 추가

- 하나의 상자/단추를 생성하고, 'Ctrl + Shift' 눌러서 동일 선상에 추가할 수 있다. 
- 5개의 옵션단추는 그룹 상자 안에 생성해야 한다. 바깥쪽으로 나간다면, 그룹에서 벗어나게 된다.

 

2. 고급 필터 생성 및 데이터 연결하기

검색 결과 시트에서 콤보상자(소속, 직급)을 눌렀을 때, 해당하는 데이터가 목록으로 보이고 목록에서 선택된 데이터가 조건 시트(2번째 시트) 내 '고급필터 조건'과 '양식 컨트롤 연결' 셀에 자동으로 입력될 수 있도록 해보자.

- 이 작업은 검색결과 시트에서 데이터를 자동으로 추출하게 하는 매크로 생성하는 데 사용
- 데이터 목록 내 데이터들 '이름 지정' 하면 고급 필터와 연결이 수월하다

 ※ 이름 지정 : '수식 - 이름 관리자' 에서 이름 설정 및 변경할 수 있다.
   → 실습 데이터에는 소속, 직급, 평가를 이름 지정함

2-1. 양식 컨트롤 연결

생성한 '검색 결과 Form'에서 콤보상자(소속, 직급, 평가)를 클릭했을 때, 해당 카테고리에 있는 데이터들을 선택할 수 있도록 연결해보자.

 

[ 콤보 박스에 데이터 연결하기 ]

 

위 사진처럼 콤보박스에 목록을 추가 및 연결할 수 있다. ( '소속 - 우클릭 - 컨트롤 서식' ) - 직급도 동일

- 입력 범위 : 소속 (저장한 데이터 이름 - 조건으로 목록화할 데이터 범위)
- 셀 연결 : A7 (데이터를 선택했을 때, 어느 셀에 연결할 것인지 설정)
 → 콤보 상자 목록에서 데이터를 선택하면, 그 데이터의 문자값이 아니라 순번(Index값)으로 연결됨.
 → 해당 데이터 정보를 고급 필터 및 매크로에 이용하기 위해 'Index 함수'를 사용해야 한다 (2-2 참고)
- 목록 표시 줄 수 : 한번에 표시할 목록의 수 설정

 

 

[ 양식 컨트롤과 연결된 셀 예시 ]

 

위의 사진에서 '전산기술팀(소속)', '부장(직급)', 'C이상(평가)'를 선택했을 때, 각 데이터에 해당하는 Index값인 (2, 2, 3)이 양식 컨트롤 연결셀에 출력되는 것을 확인할 수 있다. 

2-2. 고급 필터 설정 (INDEX, IF 함수 사용)

2-1. 까지 검색 양식(Form)을 만들고, 데이터를 분류하기 위한 목록을 바탕으로 양식 컨트롤까지 연결했다. 이젠 양식 컨트롤 표에 있는 Index값을 통해 해당하는 데이터 이름을 가져와야 고급 필터의 조건으로 사용해보자.

 

[ 완성된 조건 시트 ]

 

양식 컨트롤 연결셀과 고급필터 조건셀을 연결함으로써 위에서 선택했던 '전산기술팀', '부장', '평가'의
index값이 데이터값으로 변환되어 표기되는 것을 확인할 수 있으며, 사용한 함수식은 다음과 같다.

 

1) 고급필터 조건셀 내 함수식 (소속 & 직급) 
 : Index 함수만으로 데이터값을 불러올 수 있지만, 모든 데이터를 가져오는 '전체'를 불러오면 실제로 데이터 전체를 불러오는 것이 아니라 '전체'라는 이름을 가진 데이터를 찾는다. 따라서 이 때 IF 함수를 사용해서 조건문을 추가해야 한다.
 → '전체'에 해당하는 Index값인 1이 들어오면, 고급필터 조건에서는 ' * ' 혹은 빈 셀(" ")로 설정!
 → 최종 함수식 : ' =IF(A7=1, "", INDEX(소속, A7)) '
 : A7(소속 index값이 포함된 셀)값이 1(전체)라면 빈 셀을 출력하고, 그 외 해당 데이터값을 출력!

# Index 함수
 : 정해진 범위 내의 행과 열번호에 맞는 값을 표시하는 역할을 한다. 
 → 형식 : ' =INDEX(셀 범위, 행 번호, 열 번호) '  or  INDEX(범위, 행 번호)

 

2) 고급필터 조건셀 내 함수식 (평가)
 : 평가 영역에서는 '소속'과 '직급'처럼 1대1 매칭이 아니라, "A이상, B이상 ..."의 조건이 붙어있기 때문에 조건식을 적용해야 한다.
 → 최종 함수식 : ' ="<="&INDEX(평가, C7) '
 : 평가 데이터목록 중, C7(평가 index값이 포함된 셀)에 해당하는 데이터값을 출력.

※ "<=" 로 표시 : Index값이 작을수록 실제 평가는 높아지므로.
 → ex, 'A이상의 index' = 1, 'C이상의 index' = 3 

 

3. 자동 매크로 기록하고 코드 수정 및 보완하기 (고급 필터 사용)

최종적으로 지금까지 준비한 고급필터와 조건시트를 바탕으로, 인사고과에 있는 데이터들을 설정하는 조건에 맞춰서 '검색 결과' Sheet에 추출하는 매크로를 구현해보자.

3-1. 자동 매크로 기록 

먼저 자동 매크로를 통해 구현하고 미흡한 부분을 VBA 코드 수정으로 보완해보자. 

- '데이터 - 고급 - 다른 장소에 복사' 선택 후, 아래 값을 설정하고 기록 중지!
 → 목록 범위 : 추출할 데이터들이 있는 모든 데이터 선택 (Ctrl + A) 
 → 조건 범위 : 생성한 '고급 필터 조건' 셀 선택 (조건 시트)
 → 복사 위치 : 출력하고 싶은 셀 선택 

3-2. 고급 필터 매크로 편집 (VBA 코드 수정)

매크로를 조금 더 편리하게 사용하기 위해서 '검색 시작'라는 도형에 매크로를 지정하고, 몇 가지 보완할 사항에 대해 자동 생성되었던 자동 매크로에 대한 VBA 코드를 수정하면서 완성도를 높이자.

1) 도형에 매크로 지정
 : 도형에 매크로를 지정함으로써 조금 더 편리하게 매크로를 실행시킬 수 있다.
 → '도형 우 클릭 - 매크로 지정 - 지정할 매크로 선택'
 → 실습예제에서는 '검색 시작' 라는 사각형 도형에 매크로 지정함.

2) 개체 서식 변하지 않도록 설정
 : 추출할 데이터들이 업데이트될 때마다 열너비와 행높이가 변함에 따라 Form이 변경되는 것을 방지!
 → '홈 - 찾기 및 선택 - 개체 선택' → 전체 서식을 드래그로 선택
 → '우클릭 - 크기 및 속성 - 도형 서식 - 속성 - 변하지 않음'

 

[ 열너비와 행높이에 따른 서식 위치 변하지 않도록 설정 ]

 

3) 고급 필터 VBA 코드 수정
 : 자동 생성한 VBA 코드에서 불필요한 코드를 삭제하고, 보완할 사항에 대해 코드를 추가
 - 기존에 출력되었던 데이터들을 제거 후, 새롭게 추출하는 작업 추가 (Clear 메서드)
 - 검색 작업 매크로가 완료되면 메시지창 띄우기 (msgbox) - ex, "6명 검색 완료"

 

 

위의 사진은 '고급 필터' 매크로 VBA 실행 코드이며, 자세한 내용은 아래와 같다.

- Rows("10:400").Clear  : 10~400행까지 데이터 제거 (400은 임의의 숫자)
 → Clear : 데이터 지우기만 하는 기능
 → Delete : 행 삭제 (삭제하면 바로 밑에 있는 데이터가 위로 올라옴) - 속도가 떨어질 수 있다.
  
- Range("A1").CurrentRegion.AdvancedFilter Action ~ 
 : A1셀이 포함된 영역을 모두 선택(Ctrl + A) - 'CurrentRegion' 속성 추가
 → 만약 데이터가 추가되어서 셀 범위가 변경될 수 있으므로, 변경된 영역을 자동 반영될 수 있도록 함

- CriteriaRange:= ~   : 조건 시트에 해당하는 셀 범위 지정
- CopyToRange:= ~  : 데이터를 출력할 셀 범위 지정
- Columns("A:K").AutoFit  : 열 너비 자동 맞춤  ( Select와 EntireColumn 속성은 불필요 )

- Msgbox WorksheetFunction.CountA(Columns("A")) - 1 & "명 검색완료"
 : 데이터 추출 실행 후, 검색된 결과(추출된 A열을 기준으로)에 대한 데이터 갯수 msgbox 출력
 → 엑셀에서 사용한 CountA 함수(데이터 카운팅하는 역할)를 VBA 환경에서도 동일하게 사용하게 위해서는 'WorksheetFunction' 라는 속성을 이용해야 한다. 
 →  CountA 함수에서 ' -1 '를 하는 이유는 추출된 데이터 중 맨 위 범주명을 제외하기 위함이다.

 

 

[ 최종 매크로 실행 결과 ]