[엑셀 VBA] 자동화 - 데이터 관리의 기본과 고급필터의 유용성

작성자
dra****
작성일
2021-06-10 19:07
조회
41

질문에 올라오는 내용들을 살펴보면, 많은 분들이 데이터를 체계적으로 관리를 않고 주먹구구식으로 그때 그때 마다 열을 추가하여 무엇인가를 처리하는 경우를 많이 보았습니다. 데이터가 몇 개 안되면 대수롭지 않지만, 규모가 좀 있는 경우라면 체계적으로 관리를 하는 것이 무엇보다 중요합니다. 엑셀 파일 관리하는 것만 보아도 그 회사의 수준이 어느 정도 인지 알 수 있습니다.

필드를 구성할 때, 나중을 위해서라도 아이디나 품번, 대,중,소 분류 정도는 해 두는 것이 좋습니다. 그리고 분류를 하는 부분은 데어터 유효성 검사 목록을 사용하는 것이 좋습니다. 그래야 일관성 있는 데이터가 만들어 지니까요.

그리고 데이터가 있는 시트에 직접 작업을 하다보면 데이터를 유실하는 경우가 많이 있습니다. 특별한 경우가 아니라면 조회를 해서 데이터를 가져오고, 그 데이티를 가공하던 수정을 하던 해서 원본 데이터를 업데이트 시키는 것이 기본입니다. 파워쿼리를 해보시면 아시겠지만, 원본 데이터를 건드리지 않고 새로 테이블을 만들어 시트에 뿌려줍니다. 이것이 똑같은 원리입니다.

오늘의 주제는 "고급 필터의 활용"입니다. 이전에 SQL을 이용해서 직책별로 직원들을 뽑아내는 것을 보여드렸습니다.

이를 활용해서 직원들 가불, 보험금, 초과근무를 입력하는 폼을 만들고 소스 엑셀 파일에 업데이트를 합니다.

왼쪽의 폼을 이용해 오른쪽에 있는 원본 시트에 정보를 추가합니다. 폼을 사용하 수 밖에 없는 이유는 직책별로 직원들을 뽑아와서 삽입해야 하기 때문입니다. 아니면 원본에 직접 입력하도록 해야 하는데 급여와 관련된 부분이기 때문에 아무나 원본을 손대지 못하게 방지해야 하는 기능도 있어야 하기 때문입니다. 즉, 로그인 시스템으로 허가 받은 관리자만 할 수 있도록 해야 합니다.

그리고 이미 있는 직원들의 정보를 일일히 또 입력하는 것도 무지하게 번거로운 일일 것입니다. 폼의 등록 버튼 클릭하면 알아서 자동으로 추가가 됩니다. 할일은 날짜 설정, 직원 선택, 등록 클릭, 이게 다입니다. 이도 귀찮으면 위에 모두 등록버튼 클릭하면 전부 등록해 줍니다. 우측의 원본은 엑셀에 보여지도록 하기 위함이고, 나중에 엑세스에 백업 할 때는 [아이디], [지급일자], 단 2개의 필드만 넣어 줍니다. 나중에 직원 테이블과 조인해서 정보를 볼 수 있는 쿼리 하나만 만들면 되기 때문입니다.

상기 회사는 2주에 한 번씩 급여를 줍니다. 따라서 월급 계산 시 6월1일 ~15일 / 6월 16일~30일 이런식으로 데이터를 가져와야 합니다. 위의 폼에서 자동으로 2주를 선택하고 그 날짜값에 아래의 시트 Criteria셀에 자동으로 넣도록 합니다.

데어터 위에 있는 헤더(필드명)가 원본 소스의 헤터와 철자가 같아야 합니다. 필터링 할 때 "[EID] [TOTAL]" 이렇게 2개만 헤더에 두면 2개만 가져옵니다. 그리그 순서가 바뀌면 안됩니다. 내부적으로 배열을 이용해서 접근하기 때문에. 저는 원래 헤더 그대로 두었습니다.

이제 조건이 완성이 되었이니 고급 필터링을 할 차례입니다.  각각의 데이터를 원본으로부터 필터를 해서 가져온 값을 원하는 시트에 붙여 넣기를 합니다.

Public Sub Filter_BEN()
   Dim rngCriteria   As Range   'Criteria의 값을 담을 범위 - 이름 정의 해 놓음
   Dim rngPaste      As Range   '붙여넣을 범위
   Dim rngSrc        As Range    '소스의 범위
 
   Dim RowLast       As Long
   RowLast = sh.GetLastRow("B", wsDB.wsBenefits)    '소스의 마지막 행
   If RowLast < 2 Then Exit Sub    '소스에 값이 없으면 종료
 
   Set rngCriteria = wsYS.wsTempFilter.Range("FILTER_BEN_DATE")
   Set rngPaste = wsYS.wsTempFilter.Range("Y6:AJ6")   '헤더 범위 지정 - 헤더를 인지하여 해당 열에 값을 뿌려줌
 
   rngPaste.CurrentRegion.Offset(1, 0).ClearContents    '기존의 필터된 데이터가 있으면 삭제
 
   Set rngSrc = wsDB.wsBenefits.Range("A1:L" & RowLast)    '소스 범위 지정
 
   rngSrc.AdvancedFilter xlFilterCopy, rngCriteria, rngPaste    '필터링을 하고 가져온 값을 붙여 넣기
End Sub

이제 필터링이 끝났습니다. 실행해 보면 아래와 같은 결과를 보게 됩니다.

엑셀에서 원하는 데이터를 조회하는 방법이 여러가지가 있겠으나, 개인적으로는 SQL과 AdvancedFilter 이 두 가지를 선호 합니다. 데이터가 1000개가 넘어가면 SQL을, 그 이하면 AdvancedFilter를 사용합니다.

다음에는 마소 FORM를 통해 수집된 출퇴근 시간으로 근무시간 산정하는 부분을 포스팅 하려 합니다.

 

.

회원등급 : 열매2단계
포인트 : 1238 EP
전체 0

전체 402
번호 제목 작성자 작성일 추천 조회
358
New [엑셀 VBA] 자동화 - FORMS를 이용한 직원 근무시간과 급여 계산하기
dra**** | 2021.06.13 | 추천 0 | 조회 12
dra**** 2021.06.13 0 12
357
New 고맙습니다
가자 ! 하자!! 아자!!! | 2021.06.13 | 추천 0 | 조회 7
가자 ! 하자!! 아자!!! 2021.06.13 0 7
356
New 방가워요
dfgh**** | 2021.06.11 | 추천 0 | 조회 10
dfgh**** 2021.06.11 0 10
355
[엑셀 VBA] 자동화 - 데이터 관리의 기본과 고급필터의 유용성
dra**** | 2021.06.10 | 추천 1 | 조회 41
dra**** 2021.06.10 1 41
354
방문 상담 요청 방법?
dr**** | 2021.06.10 | 추천 0 | 조회 30
dr**** 2021.06.10 0 30
353
모두 오빠두엑셀 덕분입니다. (2)
DollShe365 | 2021.06.10 | 추천 4 | 조회 56
DollShe365 2021.06.10 4 56
352
[APP] 심플한 날짜 계산기 첨부파일
dra**** | 2021.06.09 | 추천 0 | 조회 32
dra**** 2021.06.09 0 32
351
빌게이츠가 한글을 사랑한다는 말이 사실일까? 엑셀에서 확인
dra**** | 2021.06.08 | 추천 0 | 조회 44
dra**** 2021.06.08 0 44
350
엑린이(?)입니다 (2)
투덜이스머프 | 2021.06.06 | 추천 3 | 조회 48
투덜이스머프 2021.06.06 3 48
349
[마소 FORMS] 무료로 마소 서버를 이용하여 필요한 정보 수집하기 - 매우 쉬움 (3)
dra**** | 2021.06.06 | 추천 0 | 조회 47
dra**** 2021.06.06 0 47
348
[액셀 VBA] 자동화 - 직책별로 뽑은 직원들을 폼에 연동하여 수정 조회 하기
dra**** | 2021.06.03 | 추천 1 | 조회 79
dra**** 2021.06.03 1 79
347
이제야 알게 되어 아쉽다. (6)
wshm**** | 2021.06.03 | 추천 8 | 조회 86
wshm**** 2021.06.03 8 86
346
Q&A 채택하고 싶은데 어떤식으로 채택해야 하나요? (4)
0328 | 2021.06.01 | 추천 0 | 조회 49
0328 2021.06.01 0 49
345
벽돌쌓기 첨부파일 (6)
눈사람 | 2021.06.01 | 추천 5 | 조회 74
눈사람 2021.06.01 5 74
344
[엑셀 VBA] 자동화 - ONEDRIVE를 서버로 활용하기 - 화상영어 회사 자동화 사례 (3)
dra**** | 2021.05.30 | 추천 2 | 조회 80
dra**** 2021.05.30 2 80
343
[엑셀 함수] WEEKNUM()으로 구한 몇 주차로 다시 날짜 구하기
dra**** | 2021.05.29 | 추천 0 | 조회 53
dra**** 2021.05.29 0 53
342
[엑셀 VBA] 자동화 - 1. 데이터 입력, 2. 양식에 자동으로 대입 3. 이메일 자동 발송 (2)
dra**** | 2021.05.28 | 추천 1 | 조회 86
dra**** 2021.05.28 1 86
341
VBA 이메일 자동발송 개별발송 질문드립니다 (4)
소람 | 2021.05.27 | 추천 1 | 조회 66
소람 2021.05.27 1 66
340
"최대 이익을 내는 판매가격의 예측" 강의 관련 문의입니다!!!! (1)
naver_5ef1913e089b5 | 2021.05.27 | 추천 0 | 조회 37
naver_5ef1913e089b5 2021.05.27 0 37
339
[엑셀 함수] 엑셀에 쓰이는 모든 함수들입니다. 첨부파일 (8)
dra**** | 2021.05.26 | 추천 18 | 조회 157
dra**** 2021.05.26 18 157
338
[엑셀 VBA] 자동화의 딜레마 (8)
dra**** | 2021.05.25 | 추천 6 | 조회 149
dra**** 2021.05.25 6 149
337
[엑셀 VBA] 데이터베이스 다루기 (3)
dra**** | 2021.05.24 | 추천 2 | 조회 116
dra**** 2021.05.24 2 116
336
[엑셀 VBA]-[팁] 콤보박스에 시트에 있는 값을 자동으로 집어 넣기 (1)
dra**** | 2021.05.22 | 추천 0 | 조회 71
dra**** 2021.05.22 0 71
335
[엑셀 VBA]-[팁] TextBox, ComboBox 등에 숫자만 입력 받기 (1)
dra**** | 2021.05.21 | 추천 1 | 조회 76
dra**** 2021.05.21 1 76
334
디지털 시계 첨부파일 (8)
눈사람 | 2021.05.17 | 추천 10 | 조회 140
눈사람 2021.05.17 10 140