[개미의 걸음 2과목 스프레드시트 일반] 분석작업② 매크로

매크로

엑셀에서 다양한 명령들을 순서대로 기록해두었다가 지정한 키나 도구를 사용하면 기록해 둔 처리 과정이 수행되도록 하는 기능

• 매크로 기록에 사용되는 명력과 함수는 Visual Basic모듈에 저장
       → Visual Basic Editor를 사용하면 내용 추가, 삭제, 변경 가능
       → Visual Basic Editor는 [개발도구 → 코드 → Visual Basic]을 선택하거나 [Alt + F11]을 눌리면 실행됨
• 매크로 기록 작업 중에 셀을 선택하면 셀은 기본적으로 절대 참조로 기록됨
       → [개발도구 → 코드 → 상대참조로 기록]을 이용하면 상대 참조로 기록 가능
• 매크로를 절대 참조로 기록하면 매크로를 실행할 때 선택한 셀의 위치를 무시하고 매크로가 셀을 선택
• [보기 → 매크로 → 매크로 → 매크로 기록] 혹은 [개발도구 → 코드 → 매크로 기록]을 눌림
  매크로 단축키는 [Alt + F8]
  매크로 기록 대화 상자에서 매크로이름, 매크로 저장 위치 등을 설정한 후 확인을 눌리면 실행
• 매크로 작성을 종료할 때는 [보기 → 매크로 → 매크로 → 기록중지] 혹은 [개발도구 → 코드 → 기록중지]를 눌림

매크로 기록 대화상자

매크로 기록 대화상자에서는 매크로이름, 바로가기키, 매크로 저장위치, 설명을 지정 가능

매크로 이름	'매크로1, 매크로2 ...'등과 같이 자동으로 부여되는 이름을 지우고 사용자가 임의로 이름 지정 가능 첫 문자는 반드시 문자로 지정, 두번째부터는 문자, 숫자, 언더바(_)등을 사용 가능      / ? ' ' . - 등의 문자나 공백은 매크로 이름으로 사용 불가 하나의 통합 문서에는 이름이 동일한 매크로 존재 불가 매크로 이름을 'Auto_Open'으로 지정하면 해당 파일을 열 때마다 기록된 매크로가 자동으로 실행됨
바로가기키	영문자만 가능[바로가기 키를 입력하지 않더라도 매크로 기록 가능] 바로가기 키는 Ctrl과 조합하여 사용 [대문자를 지정할 때는 Shift가 자동으로 덧붙여짐] 엑셀에서 이미 사용하고 있는 바로가기키를 매크로 바로가기키로 사용 가능     → 바로가기키가 동일할 경우 매크로 실행 바로가기키가 우선 바로가기키 지정 후 수정 가능
매크로 저장위치	개인용 매크로 통합 문서 : XLSTART폴더 안에 PERSONAL.XLSB에 저장됨 [엑셀 실행할때마다 사용 가능]     → XLSTART폴더에 있는 모든 파일은 엑셀이 실행될 때 자동으로 열림 새 통합 문서 : 새로운 통합 문서에 매크로를 만들어서 사용 현재 통합 문서 : 현재 작업하고 있는 통합 문서에만 적용시킬 때 사용[기본 저장 위치]
설    명	매크로에 대한 간략한 설명을 기재하는 것으로 사용자가 임의로 지정 가능

 

 

데이터 통합

비슷한 형식의 여러 데이터를 하나의 표로 통합*요약하여 표시해주는 도구

• 사용할 데이터의 형태가 다르더라도 같은 이름표를 사용하면 항목을 기준으로 통합 가능
• 사용할 수 있는 함수에는 합계, 개수, 평균, 최대값, 최소값, 곱, 숫자 개수, 표본 표준 편차, 표준 편차, 표본 분산, 분산이 있음
• 다른 워크시트나 통합 문서의 데이터를 사용할 수 있음
• 지정한 항목을 기준으로 통합하거나 통합 데이터의 순서와 위치가 동일할 경우 위치를 기준으로 통합

'데이터 통합' 대화상자

함    수	사용할 함수를 선택
참    조	통합할 데이터 범위를 지정
추    가	참조에서 지정한 데이터 범위를 추가
삭    제	'모든 참조 영역'에 추가된 영역 중 선택하여 삭제
사용할 레이블	첫 행 : 참조된 데이터 범위의 첫 행을 통합된 데이터의 첫 행(열 이름)으로 사용 왼쪽 열 : 참조된 데이터 범위의 왼쪽 열을 통합된 데이터의 첫 행(행 이름)으로 사용
원본 데이터에 연결	원본 데이터가 변경될 경우 통합된 데이터에도 반영

 

 

시나리오

다양한 상황과 변수에 따른 여러 가지 결과값의 변화를 가상의 상황을 통해 예측하여 분석하는 도구

• 변경 셀에는 데이터를 변경할 셀의 범위를 지정
• 결과 셀에는 반드시 변경 셀을 참조하는 수식으로 입력
• '변경 셀'과 '결과 셀'에 이름을 지정한 후 시나리오 요약 보고서를 작성하면 셀 주소 대신 지정한 이름이 표시됨
• 이자율, 손익 분기점, 주가 분석 등에 많이 사용됨
• 시나리오 결과는 요약보고서나 피벗 테이블 보고서로 작성 가능
• 시나리오 병합을 통해 다른 통합 문서나 다른 워크시트에 저장된 시나리오를 가져올 수 있음
• '시나리오 관리자'대화상자에서 시나리오를 삭제해도 이미 작성된 시나리오 요약 보고서는 삭제되지 않음!
  시나리오 요약 보고서를 삭제해도 시나리오는 삭제되지 않음

시나리오 관리자

추가	시나리오 이름과 변경 셀을 입력할 수 있는 대화상자를 표시 변경 셀에는 데이터를 변경할 셀의 범위를 지정 하나의 시나리오는 최대 32개까지의 변경 셀을 지정 가능
삭제	선택한 시나리오를 삭제
편집	선택한 시나리오를 변경할 수 있는 대화상자 표시
병합	선택한 시나리오를 병합
요약	시나리오를 보고서로 작성     보고서의 종류와 결과 셀을 지정     보고서의 종류에는 시나리오 요약과 시나리오 피벗 테이블 보고서가 있음     결과 셀에는 변경 셀을 참조하는 수식으로 입력되어 있는 셀을 지정해야 됨
표시	선택한 시나리오 값을 워크시트에 표시
닫기	대화상자를 닫음

 

 

목표값 찾기

수식에서 원하는 결과 값은 알고 있지만 입력값을 모를 경우에 사용하는 도구

• 목표값 찾기는 변하는 데이터를 하나만 지정 가능
• 목표값 찾기에서 목표값은 사용자가 원하는 데이터를 직접 입력
• 결과값은 입력값을 참조하는 수식으로 작성되야 함!

목표값 찾기 대화상자

수식 셀	결과값이 출력되는 셀 주소 해당셀에는 반드시 수식을 입력해줘야함!
찾는 값	목표값을 입력
값을 바꿀 셀	목표값을 만들기 위해 변경되는 값이 들어 있는 셀의 주소

 

 

 

 

예제 문제

• 이 문제를 푸는 2가지 방법
    1. [개발 도구 → 코드 → 매크로 기록]을 선택하여 작업한 후 단추 생성  
    2. 단추를 생성한 후 매크로명 지정한 후 기록 클릭하여 작업
• 셀 포인터의 위치에 관계없이 매크로를 실행 시킬려면 매크로 기록 전과 후 셀 포인터는 반드시 결과가 나올 곳이 아닌 셀을 선택하고 매크로 기록 혹은 기록 중지를 눌려야함

 

1번 방식[매크로 기록 후 단추 생성]

1. 매크로명 지정

• 셀 포인터는 결과가 나올 셀과 관계없는 셀을 선택
• [개발 도구 → 코드 → 매크로 기록]을 선택한 뒤 매크로 이름 지정 후 확인 선택
      → 매크로 기록이 시작됨

 

2. 매크로 기록

• 결과가 나올 셀을 범위 지정
• 문제에서 데이터 통합 기능을 사용해라고 했으므로 [데이터 → 데이터 도구 → 통합]을 선택
• 첫행과 왼쪽 열 레이블을 가져와야 하므로 모두 체크
• [표2] 전체 범위 설정 후 추가
      → [표3], [표4], [표5]도 동일한 방법으로 추가한 후 확인 선택
• 셀 포인터를 표 밖의 임의의 셀 선택한 후, [개발 도구 → 코드 → 기록 중지] 선택

자동필터 기능을 사용하는 문제의 경우 기록 중지 후 반드시 자동 필터 해지를 선택해야됨!!

 

3. 단추 만들기

• [개발 도구 → 컨트롤 → 삽입]에서 단추 선택
• 문제에서 주어진 곳에 단추 생성
      → Alt키를 눌린채 사이즈를 조정하면 셀에 딱 맞춘 크기의 단추를 생성하기 용이
• 생성한 기록합산 매크로를 선택
• 단추 이름을 문제에서 주어진 '기록 합산'으로 변경

 

 

 

2번방식[단추 생성 후 매크로 기록]

1. 단추 생성

• [개발 도구 → 컨트롤 → 삽입]에서 단추 선택
• 문제에서 주어진 곳에 단추 생성
      → Alt키를 눌린채 사이즈를 조정하면 셀에 딱 맞춘 크기의 단추를 생성하기 용이
• 매크로 이름을 문제에서 주어진 '기록합산'으로 한 후 기록 선택 

 

2. 매크로명 지정

• 셀 포인터는 결과가 나올 셀과 관계없는 셀을 선택
• [개발 도구 → 코드 → 매크로 기록]을 선택한 뒤 매크로 이름 지정 후 확인 선택
      → 매크로 기록이 시작됨

3. 매크로 기록

• 결과가 나올 셀을 범위 지정
• 문제에서 데이터 통합 기능을 사용해라고 했으므로 [데이터 → 데이터 도구 → 통합]을 선택
• 첫행과 왼쪽 열 레이블을 가져와야 하므로 모두 체크
• [표2] 전체 범위 설정 후 추가
      → [표3], [표4], [표5]도 동일한 방법으로 추가한 후 확인 선택
• 셀 포인터를 표 밖의 임의의 셀 선택한 후, [개발 도구 → 코드 → 기록 중지] 선택
• 단추명 '단추 1'을 문제에서 주어진 '기록 합산'으로 변경

자동필터 기능을 사용하는 문제의 경우 기록 중지 후 반드시 자동 필터 해지를 선택해야됨!!