블로그 이미지
주로 인재개발원 등의 사이버학습을 정리, 요약하는 상시학습 블로그입니다. 깨비형
« 2017/12 »
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            

Archive»

체험 블로그 마케팅 서비스 OLPOST

Category»

Notice»

Statistics Graph

 

'매크로'에 해당되는 글 2

  1. 2012.10.31 15. 매크로 활용 및 관리하기 (1)
  2. 2012.10.19 14. 매크로와 VBA 이해하기 (4)
 

1. 부서별 실적 집계 매크로


  1) 매크로로 자동화할 작업 이해하기

  매크로를 잘 활용하기 위해서는 직접 해보지 않으면 배우기가 힘들다.

  그래서 이번에는 부분합 기능을 사용하여 부서별 상반기, 하반기, 충 매출액의 합을 자동으로 계산하는 매크로를 작성하는 것을 배워보자.

  아래과 같이 천천히 따라하면서 매크로 활용법을 익혀보자.


○ 원본 데이터 상태


○ 매크로 자동화를 완료한 상태


  2) 부분합 따라하기

가. 정의

▷ 데이터베이스의 특정 필드를 기준으로 합계, 평균, 최대, 최소, 개수등을 자동으로 계산해 주는 기능을 말한다.


나. 부분합 수행하기

① 부분합 계산 전에 부분합의 기준이 되는 필드로 먼저 정렬이 되어 있어야 함

i) 먼저 정렬하기 위해 아무 필드에서나 커서를 위치하고,


ii) [데이터] 탭 - [정렬 및 필터] 그룹 - [정렬] 아이콘을 선택한다.


iii) 정렬 기준을 부분합의 기준이 될 부서로 지정하고 확인 버튼을 클릭한다.


iv) 정렬을 마친 상태


② 이제 본격적으로 부분합을 구해보자.

i) [데이터] 탭 → [윤곽선] 그룹 → [부분합] 아이콘 클릭하여 실행


ii) 매뉴박스가 나오면 아래와 같이 체크하고 확인을 누르면 된다.


※ 각 항목에 대한 설명은 아래 표를 참고하면 된다.

 옵   션

 설              명

 ①그룹화할 항목   부분합 계산의 기준이 되는 필드를 지정
 이 필드를 기준으로 데이터가 정렬되어 있어야 함 
 ②사용할 함수  부분합을 계산할 함수를 선택
 합계, 평균, 개수, 최대, 최소 등 여러 가지 함수 사용 가능 
 ③부분합 계산 항목  선택한 필드의 부분합 계산
 ④새로운 값으로 대치   선택 : 부분합 실행 시 기존 부분합을 새로운 부분합 계산 값으로 대치
 해제 : 기존 부분합에 새로운 부분합을 추가 
 ⑤그룹 사이에서 페이지
 나누기 
 부분합이 계산된 그룹 사이에 자동으로 페이지 나누기를 삽입함 
 ⑥데이터 아래에 요약 표시   선택 : 부분합 계산 행을 데이터 아래에 표시
 해제 : 부분합 계산 행을 데이터 위에 표시
 ⑦모두 제거 

 부분합 제거 기능
 부분합을 제거하면 부분합과 함께 목록에 삽입된 윤곽 및 페이지 나누기도

 모두 제거됨 


③ 부분합 요약이 완료되면, 데이터 윤곽기호를 사용하여 데이터 표시 방법 변경 가능


다. 부분합 제거하기

[데이터] 탭 → [윤곽선] 그룹 → [부분합] 아이콘 클릭 한뒤 부분합 대화상자에서 [모두 제거]를 클릭


  3) 실적 집계 매크로 작성 단계 정리하기 

① [개발 도구] 탭 → [코드] 그룹 → [매크로 기록] 아이콘 클릭


② [데이터] 탭 → [윤곽선] 그룹 → [부분합] 아이콘 클릭, 

부서별로 상반기, 하반기, 총 매출액 합계를 계산하는 부분합 작업 실행 



③ [개발 도구] 탭 → [코드] 그룹 → [기록 중지] 아이콘 클릭


④ [삽입] 아이콘 → [양식 컨트롤] 범주의 [단추] 아이콘 사용하여 매크로 실행 단추 작성 


⑤  버튼위에 마우스를 올려 오른쪽 버튼을 클릭하고 버튼을 떼면 아래와 같이 편집가능한 상태로 변하는데 원하는 버튼 이름으로 바꾼다.


⑥ 버튼에 마우스 오른쪽 버튼으로 클릭한 뒤 아래와 같이 [먀크로 지정] 항목을 선택하여 아까 작성한 매크로를 지정해 주면 된다.




2. 데이터 조회 매크로


  1) 매크로로 자동화 할 작업 이해하기

  고급 필터 기능 사용하여 조건 영역에서 조건을 지정한 뒤 [조회] 단추를 클릭하면 데이터가 검색되고, 다시 [모두표시] 단추를 클릭하면 필터를 해제하고 모든 레코드를 자동으로 표시하기



  2) 고급 필터 

가. 필터란?

○ 다량의 데이터에서 조건에 만족하는 데이터만 뽑아 보는 기능 

○ [데이터] 탭 – [필터] 그룹 – [필터]나 [고급] 아이콘 활용 



나. 고급 필터 사용 방법

① 필터 할 조건을 조건 영역에 입력

② 데이터베이스 내부 임의의 셀을 선택

③ [데이터] 탭 → [정렬 및 필터] 그룹 → [고급] 아이콘을 클릭


④ [고급 필터] 대화상자가 표시되면 원하는 필터 옵션을 지정하고, <확인> 단추 클릭



※ 각 항목에 대한 설명은 아래 표를 참고하면 된다.

옵  션 

설          명 

 ①결과   현재 위치에 필터 : 데이터베이스가 있는 현재 위치에 필터 결과 표시
 다른 장소에 복사 : 필터 결과를 [복사 위치]에서 지정한 위치에 표시 
 ②목록 범위   데이터를 필터 할 데이터베이스 전체 범위 지정 
 ③조건 범위   조건이 입력된 셀 범위 지정 

 ④복사 위치 

 필터된 결과를 복사할 위치 지정 

 ⑤동일한 레코드는

 하나만 

 선택하면 필터 결과에 중복된 레코드가 있는 경우 결과가 하나만 표시됨 


⑤ 조건에 만족하는 데이터가 필터 되고, 행 머리글이 파랑색으로 표시됨


⑥ 필터 결과 제거 : [데이터] 탭 → [정렬 및 필터] 그룹 → [지우기] 아이콘


  

  3) 데이터 조회 매크로 작성 단계 정리하기

① [개발 도구] 탭 → [코드] 그룹 → [매크로 기록] 아이콘 클릭


② 고급 필터 기능을 사용하여 데이터 필터 작업 실행



③ [개발 도구] 탭 → [코드] 그룹 → [기록 중지] 아이콘 클릭


④ [개발 도구] 탭 → [컨트롤] 그룹 → [삽입] 아이콘, [양식 컨트롤] 범주의 [단추] 아이콘을 사용하여 매크로 실행 단추 작성



3. 매크로 관리


  1) 매크로 코드 확인하기

① [개발 도구] 탭 → [코드] 그룹 → [매크로] 아이콘 클릭


② [매크로] 대화상자에서 코드를 확인하고자 하는 매크로를 선택하고 [편집] 버튼클릭


③ Visual Basic 편집기 창이 실행되고 기록한 매크로 코드가 표시


  

  2) 매크로 코드 수정하기

고급필터 적용한 매크로에 필터 적용한 뒤에 '조회가 완료되었습니다.' 라는 메시지 박스 추가


아래와 같이 End Sub 바로 윗 줄에 빈줄을 추가하여

MsgBox "조회가 완료되었습니다." 라는 구문을 입력한다.


아래 고급필터 매크로인 조회 버튼을 클릭하면 고급필터가 적용되고 아래와 같이 메시지 박스가 추가된 것을 확인할 수 있다.


 [매크로가 끝난 뒤 커서를 특정 셀에 두고 싶을 때 추가되는 줄]

   Range("원하는 셀주소").Select


  예) 위 화면에서 커서를 B6에 두고 싶을 때 →  Range("B6").Select

           

  3) 매크로 삭제하기

① [매크로] 아이콘 사용하여 삭제

i) [개발 도구] 탭 → [코드] 그룹 → [매크로] 아이콘 클릭


ii) [매크로] 대화상자가 나타나면 삭제하고자 하는 매크로를 선택하고 [삭제] 버튼 클릭


② Visual Basic 편집기 창에서 코드 삭제

i)[개발 도구] 탭 → [코드] 그룹 → [Visual Basic] 아이콘 클릭 


ii) Visual Basic 편집기 창에서 작성되어 있는 매크로 코드(Sub Procedure)를 선택한 후 [Delete]키 누름




 


저작자 표시 동일 조건 변경 허락
신고

1. 매크로와 VBA

 

  1) 매크로

○ 매크로란 : 반복되는 엑셀 작업을 기록해 두었다가 필요할 때마다 단축키를 누르거나 버튼만 누르면 해당 작업을 자동으로 실행할 수 있는 기능(매크로 기능은 엑셀에만 있는 것이 아니가 다양한 프로그램에 내재되어 있어 하나의 프로그램에서 그 기능을 배워두면 쉽게 타 프로그램에서도 응용이 가능하다.)

○  사용 예 : 아래와 같이 항상 제목으로 사용하는 서식을 매크로로 기록해두면 해당 매크로만 실행해도 글꼴과 크기, 스타일 및 색을 지정된 서식으로 변경해준다.

 

 

  2) VBA 란?

○ 매크로 기능 사용 시 → Visual Basic 코드(프로그래밍 언어)로 자동 기록 됨

○ VBA : Visual Basic 코드를 사용자의 필요에 따라 수정(코딩) 하는 것

▷ Visual Basic for Application의 약자 → 특정 Application (Excel, Access, Word와 같은 응용프로그램)에서만 사용할 수 있는 VB(Visual Basic) 코드를 의미. 예) 엑셀 → 엑셀 VBA, 액세스 → 액세스 VBA

 

(1) VB (Visual Basic)

▷ 프로그래밍 언어로 단어의 의미처럼 아주 쉽게 작성 가능

▷ 영어 문장과 같은 형태로 구성되어 이해가 수월함

: Microsoft 사에서 개발된 오피스 제품(Excel, Access, Word, PowerPoint 등)에서 매크로를 기록할 때 Visual Basic을 사용하도록 되어 있음

 

  3) 매크로와 VBA를 사용하여 할 수 있는 일

(1) 반복 실행되는 엑셀 작업을 자동화
(2) 엑셀에서 제공되지 않는 새로운 기능 생성
(3) 엑셀에서 제공하지 않는 함수 생성
(4) 전문적인 업무 프로그램을 개발

 

  4) 매크로 작성 준비 사항

(1) 리본 메뉴에 [개발 도구] 탭 표시(엑셀2007 기준입니다)

▷ [오피스 단추] - [Excel 옵션] - [기본 설정] - [리본 메뉴에 개발 도구 탭 표시] - [확인] 을 선택


▷ 그러면 다음과 같이 리본 메뉴에 개발도구 탭이 생기는 것을 볼 수 있음


(2) 매크로가 포함된 통합 문서 저장

▷ 엑셀 문서에 매크로가 기록된 경우 →‘매크로 사용 통합 문서(*.xlsm)’형식으로 저장

▷ [오피스 단추] -  [다른 이름으로 저장] 항목 → [매크로 사용 통합 문서] 항목


(3) 매크로 보안 설정

▷  [오피스 단추] - [Excel 옵션] - [보안 센터] - [메크로 설정] - [모든 메크로 포함] - [확인] 을 선택

 


 2. 매크로 기록 및 실행

 

  1) 매크로 기록 단계

① 매크로로 기록할 엑셀 작업 정리

② 매크로 기록 시작 명령

③ 엑셀 작업 실행

④ 매크로 기록 중지

 

   2) 매크로 기록 방법

①-1. 기록 시작 : [개발 도구] 탭 → [코드] 그룹 → [매크로 기록] 아이콘 클릭


①-2. [보기] 탭 → [매크로] 그룹 → [매크로 기록] 아이콘 클릭


② [매크로 기록] 대화상자 표시 → 각 항목 지정 → <확인> 버튼 클릭, 매크로 기록 시작


  매크로 이름 영문, 숫자, 한글로 지정하되 첫 글자에 “_” 나 숫자를 사용할 수 없고 공백 . , ! “ # & ( ) + ~ 등의 특수문자를 사용할 수 없음


  바로 가기 키 알파벳 소문자 a~z, 대문자 A~Z를 지정할 수 있고, 대문자로 지정할 경우 Shift 키를 누르고 알파벳을 입력하면 왼쪽에 표시되어 있던 Ctrl이 Ctrl+Shift로 자동으로 변경됨


  매크로 저장 위치

 ① 개인용 매크로 통합 문서 : 매크로를 저장하면 현재 매크로를 기록하고 있는 문서뿐 아니라 모든 통합 문서에서 사용 가능

② 새 통합 문서 : 매크로를 저장하면 [새 문서]를 만들 때 새 문서에 매크로가 포함되어 있음

③ 현재 통합 문서 : 매크로를 저장하면 현재 매크로를 기록하는 통합 문서에서만 매크로를 사용 가능


③ 기록 중지

▷ [개발 도구] 탭 → [코드] 그룹 → [기록 중지] 클릭

▷ [보기] 탭 → [매크로] 그룹 → [매크로 기록] 아이콘 클릭


 Tip : 개인용 매크로 통합 문서
<개인용 매크로 통합 문서>

  개인용 매크로 통합 문서에 매크로를 저장하면 ‘PERSONAL.XLSB’ 라는 ‘개인용 매크로 통합 문서’가 자동으로 생성되어 그 파일에 매크로가 저장된다.
  PERSONAL.XLSB는 엑셀 문서가 열릴 때 숨김 파일로 함께 열린다. 그렇게 숨겨진 상태로 열린 PERSONAL.XLSB 파일에 저장된 매크로를 다른 엑셀 통합 문서에서 사용할 수 있는 것이다. 
  PERSONAL.XLSB 파일의 저장 경로는 ‘C:\Users\<사용자이름>\AppData\Roaming\Microsoft\Excel\XLSTART’이다.


  3) 매크로 실행 방법

(1) 바로 가기 키 : 매크로 기록 시 지정한 [바로 가기 키]를 눌러 매크로 실행

(2) 매크로 명령

① [개발 도구] 탭 → [코드] 그룹 → [매크로 보기] 클릭

② [매크로] 대화상자에서 실행할 매크로 선택 후 [실행] 버튼 클릭


(3) 양식 단추

① [개발 도구] 탭 → [컨트롤] 그룹 → [컨트롤 삽입] 아이콘→ [양식 컨트롤] 범주 → [단추]

② [매크로 지정] 대화상자에서 실행할 매크로를 선택한 후 <확인> 클릭

 tip! _ 양식 단추의 위치 및 크기 고정 시키기

양식 단추는 행/열 작업에 따라 위치와 크기가 변한다. 
예를 들어, 양식 단추가 위치한 열의 너비를 늘리한 단추의 너비도 함께 늘어나게 된다. 
그러므로 워크시트에서 행이나 열 관련 작업을 해도 단추가 영향을 받지 않고 항상 같은 위치와 크기로 존재하게 하려면 다음처럼 속성을 지정해주어야 한다.

① 양식 단추에서 마우스 오른쪽 단추를 클릭하고 [컨트롤 서식]을 선택한다.
  

② [속성] 탭에서 [변하지 않음]을 설정한다.


(4) 그리기 도형

① [삽입] 탭 → [일러스트레이션] 그룹 → [도형] 아이콘 클릭, 원하는 도형을 워크시트에 작성


② 도형에서 마우스 오른쪽 버튼을 클릭하고 [매크로 지정] 클릭


③ [매크로 지정] 대화상자에서 실행할 매크로를 선택하고, [확인] 버튼 클릭


(5) 빠른실행 도구 모음에 추가

- [빠른 실행 도구 모음] 영역에 아이콘을 추가하여 실행 가능





저작자 표시 동일 조건 변경 허락
신고