[패스트캠퍼스] 컴퓨터활용능력 1급 정리 - 8

 

 

8. 시나리오, 피벗테이블,데이터 통합, 차트, 매크로, 정보함수, 재무함수, 배열수식

 

 

시나리오(결과를 예측할 수있다는 것)

다양한 가상의 환경에 따른 결과값을 비교 분석
워크시트 데이터를 자동으로 바꿀 수 있는 값의 집합으로 워크시트 모델의 결과예측가능
한 페이지에 A,B,C,D를 나타내 피벗테이블을 만들수있음
보고서관리자추가기능으로 보고서도 만들 수있고
결과셀은 없어도 되지만 시나리오 피벗테이블보고서에는 결과셀을 반드시 지정
변경셀과 결과셀에 이름을 지정한 후 시나리오 요약 보고서를 작성하면 결과에 셀 주소 대신 지정한이름표시
[데이터] 탭에 [데이터 도구]그룹에서 가상분석 단추 클릭, [시나리오관리자] 선택

하나의 시나리오 변경셀을 최대 32개까지 지정가능
시나리오 병합을 통해 다른 통합문서나 다른 워크시트에 저장된 시나리오를 가져올 수있다.
입력된 자료들을 그룹별로 분류하고 해당 그룹별로 원하는 함수를 이용한 계산 결과를 볼 수없다. (시나리오와 맞지않음)

시나리오 추가 화면
시나리오 이름
변경셀
설명
보호

데이터-데이터도구-가상분석-시나리오 관리자 -추가
'설명'은 시나리오에 대한 추가적인 설명으로 입력하지 않아도 된다.
'보호'의 체크박스들은 [검토]-변경내용-시트보호를 설정한 경우에만 적용되는 항목들

 

피벗테이블

다양한 데이터로 보고서를 만들 때 사용

원본데이터가 행이나 열 위치를 사용자 임의로 변경하여 데이터를 표시할 수있는 기능으로 많은 양의 데이터를 손쉽게 요약할 수있음

각 필드에 다양한 조건을 지정할 수있고
그룹별로 데이터 집계가 가능하다

다양한 수치함수를 사용할 수있음

피벗테이블의 보고서 필터, 행레이블, 열레이블, 값을 추가 또는 삭제할 수있음
피벗 테이블 작성 후 사용자가 새로운 수식을 추가로 표시
원본데이터가 변경되면 데이터를 새로고침기능을 이용해서 데이터가 변경된다.

값 영역의 특정 항목을 마우스로 더블클릭하면 그 셀에 대한 세부적인 내용이 새로운 시트로 보여짐.
행을 열로 또는 열을 행으로 이동하여 다양한 방식으로 요약하여 표시
피벗테이블과 피벗 차트는 별개이기에 피벗테이블을 삭제하면 피벗차트도 자동으로 삭제되는것은 아니다. 즉 서로 영향을 주지 않는다.

 

 

데이터 통합

동일한 워크시트에서도 되고 다른 워크시트에 있는 데이터들을 모아서 원하는 표에 결과값을 만들어내는 것.
즉 여러개의 데이터를 하나의 데이터파일로 합치는 기능
여러 시트에 있는 데이터나 '다른 통합문서에 입력된 데이터'도 통합할 수도있음
각 워크시트에 입력된 데이터의 위치나 영역의 이름( 항목)에 의해서 이루어짐
원본 영역에 있는 항목 레이블이 워크시트마다 다른 경우에도 가능
위치를 기준으로 통합할 수도 있고 영역 이름을 정의하여 통합 가능
원본 데이터에 연결기능은 통합할 데이터가 있는 워크시트와 통합 결과가 작성될 워크시트가 같은 통합문서에 있는 경우와 다른 통합문서에 있는 경우에도 적용 가능
여러 시트에 있는 데이터나 다른 통합 문서에 입력되어 있는 데이터를 통합할 수있다.

 

차트(그래프)

세로막대형
꺾은선형 데이터 추세 
원형 하나의 데이터 계열 중요요소 강조
가로막대형
영역형 시간에 따른 변동크기 강조
분산형 불규칙한 간격이나 묶음
주식형 주식 가격 동향
도넛형 원형차트 개선
거품형 세 값의 집합을 비교
방사형 

 

매크로

MS visual Basic 모듈로 저장
반복적인 작업이나 시간이 많이 걸리는 작업을 보다 신속하게 처리하기 위해서 사용
즉 자주 사용하는 기능을 저장하여 자동으로 작동하게 하는 것.
바로가기 키나 명령 단출 기록하여 작업을 보다 빠르게 실행
선택된 셀 위치에서 매크로가 실행되도록 하려면 상대 참조로 기록

엑셀에서 기본적으로 사용하는 통합문서는 매크로 제외 통합문서이기때문에 다른이름으로 저장할 땐 엑셀 매크로 사용 파일(.xlsm)으로 저장
매크로 작성의 프로그래밍 언어는 visual basic
VBA(visual basic for application)을 이용하여 작성가능
작성된 매크로는 비주얼 베이직 편집기를 이용해서 수정하거나 편집 가능

매크로 이름을 만들때
앞부분은 반드시 문자가 들어가야하고 (공백도 안됨)
언더바_를 사용하여야함. -가 들어가면 안됌.

 

---

 

정보함수

 

ISBLANK(인수) 셀이 공백이면 TRUE
CELL(유형,셀) 셀 주소,행,열, 파일 이름 등을 정보반환
***ISERROR(인수) 많이 사용됨. 1급 실기에서 특히많이 사용하는데 검사 대상이 에러값인지 조사
***ISERR(인수) 오류값을 가지고 있으면 TRUE 그러나 (#N/A 제외)

ISEVEN(인수) 인수가 짝수이면 TRUE를 반환
ISLOGICAL(인수) 인수가 논리값이면 TRUE
ISNONTEXT(인수) 인수가 텍스트가 아니면 TRUE
ISNUMBER(인수) 인수가 숫자이면 TRUE
ISODD(인수) 인수가 홀수이면 TRUE
ISTEXT(인수) 인수가 텍스트이면 TRUE
N(인수) 인수에 대한 숫자값으로 반환
TYPE(인수) 인수의 "데이터 형식"에 대한 '숫자값'

 

재무함수

FV(이자,기간,금액,현재가치,납입시점)
매월 일정한 금액을 불입할 경우 만기일에 받을 원금과 이자율 계산
PV(이자,기간,금액,미래가치,납입시점)
일정한 금액을 대출받았을 때 이자를 포함하여 매월 상환해야하는 금액 계산
PMT(이자,기간,금액,현재가치,미래가치,납입시점)
일정한 금액을 대출받았을 때 이자를 포함하여 매월 상환해야하는 금액계산
NPV(할인율, 금액1, 금액2,....)
할인율과 앞으로의 지출과 수입을 이용하여 투자의 현재가치 계산
DB(취득액, 잔존가치, 수명년수, 기간, 월수)
정율법을 사용하여 특정기간동안 자산의 감가상각액 계산
SLN(취득액,잔존가치,수명년수)
정액법을 사용하여 특정기간동안 자산의 감가상각액 계산

 

---

 

배열 수식 (1급에만 출제)

하나 이상의 값 집합에 대하여 여러가지 계산을 수행하고, 하나 또는 여러개의 결과를 반환하는 수식으로 셀 수식을 사용할 때 메모리를 적게 사용

수식을 입력하고 Ctrl+Shift+Enter 키를 동시에 누르면 수식 앞 뒤에 중괄호{}가 자동으로 입력

두개이상의 값 집합에 대해 수행되는 각각의 배열 인수는 동일한 개수의 행과 열을 가져야 함
예) {=B2:B5*C2:C5} 
범위지정하고 범위지정해서 지정된범위에 결과값 나타냄

잘못된 인수나 피연산자를 사용했을 때 #VALUE!에러가 발생하며, 빈칸은 0으로 계산