[VBA] 양식 컨트롤을 이용한 데이터 추출 프로그램 만들기(고급 필터)
728x90
반응형
# 목표
- 고급 필터와 양식 컨트롤을 사용하여 데이터 검색/추출 결과를 깔끔하고 효율적으로!!
- Index, If 등의 함수를 통해 엑셀 내 데이터를 분류하고, 필요한 VBA 속성을 이해한다.
- 엑셀 WorkSheet에서 사용하는 함수를 VBA 코드에서도 적용시킬 수 있다.
1. 데이터 확인 및 검색 Form 만들기
위의 영상처럼 데이터 검색/추출하는 프로그램을 본격적으로 만들기 전에 실습에서 사용되는 데이터를 확인해보고, 해당 데이터를 검색하는 형식(Form)을 먼저 만들어보자. ex) 인사고과 검색 결과
1-1. 데이터 파악
- 아래 예시로 첨부한 이미지에는 ' 1) 인사고과 데이터 2) 조건필터 Sheet 3) 데이터 검색 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 '를 하는 이유는 추출된 데이터 중 맨 위 범주명을 제외하기 위함이다.
'Data Analyst > Excel VBA' 카테고리의 다른 글
[VBA] 데이터 편집과 서식 설정 자동화하는 프로그램 만들기 (시간 단축!) (0) | 2022.07.24 |
---|---|
[VBA] 엑셀 Raw Data 자동 편집 프로그램 만들기 (업무 효율↑) (0) | 2022.07.19 |
[VBA] 많은 표 데이터 중 "보고 싶은 데이터"만 추출하는 방법(매크로) (0) | 2022.07.18 |
[VBA] 엑셀 매크로와 VBA에 꼭 필요한 기초 자동화 (초급) (0) | 2022.07.06 |
댓글