-데이터 표는 특정 값의 변화를 표의 형태로 보여주는 기능이다.
점수나 금액 같은 값이 변화할 경우 총점이나 평균이 어떻게 변화할지를 쉽게 파악할 수 있게 해주는 역할을 한다.
그와 비슷하게 시나리오는 다양한 상황과 변수에 따른 여러 가지 결괏값의 변화를 가상의 상황을 통해 예측하여 분석하는 기능이다.
이자율, 손익분기점, 주가 분석 등에 많이 사용하는 기능으로써 메크로를 통해 구현하는 문제가 나오기도 하였다.
데이터 통합은 동일 시트 또는 다른 여러 시트에 각각 입력된 데이터를 일정한 기준에 의해 합쳐서 요약하고 계산하는 기능이다. 각 지점별 판매 현황표를 모아 보는 등 데이터 통합 기능을 이용할 수 있다.
목표값 찾기는 시나리오의 반대 개념으로서, 만약 수학을 70점을 맞는다면 평균이 어떻게 될까?를 알아보는 기능인 시나리오와 달리 평균을 80점을 맞을려면 수학을 몇점을 맞아야 할까?를 찾는 기능이다.
즉 수식의 결과값을 알고있을 때, 그 결과를 계산하기 위한 목표값을 찾는 과정이다.
-데이터 표
우선 데이터표의 기능을 이용하여 문제를 해결해보자.
영아, 수학, 국어 점수의 합계를 계산한 영역을 참조하여, 수학과 국어 점수의 변동에 따른 합계의 변화를 데이터 표 기능을 이용하여 아래 영역에 계산하여 보자.
우선 B5셀에 있는 수식을 복사하여 B 10셀에 붙여 넣기 한다.
데이터 표를 적용할 영역을 블록으로 지정한 후 데이터 표를 선택한다.
그다음 데이터 표 대화 상자에서 행 입력 셀을 선택하고 B4셀을 선택한 다음, 열 입력 셀을 선택하고 B3를 선택한다.
주소는 자동으로 절대 주소로 변경된다.
다음과 같이 수학과 국어점수의 변동에 따라 합계가 변화하는 모습을 볼 수 있다.
이렇게 간단하게 데이터 표를 이용한 수학, 국어 점수의 변동에 따른 총점표를 만들 수 있다.
또한 이런식으로 구구단표를 작성할 수 있다.
-시나리오
다음으로 시나리오 기능을 활용하여 문제를 해결해보자
시트의 A, B, C물품의 반품 수량이 변동하는 경우 매출액 합계의 변동 시나리오를 작성해보자
먼저 시나리오에 사용될 변경 셀과 셀의 이름을 정의해야 한다.
이름을 정의해야 시나리오 요약이 표시될 때 E5셀, E6셀이 아닌 A반품 수량 등으로 표시된다.
변경할 E5셀을 클릭한 후 이름 상자에 A반품 수량을 입력한 후 같은 방법으로 다른 셀 또한 이름을 정의한다.
그다음 데이터-예측-가상분석-시나리오 관리자를 선택한다.
추가 버튼을 눌러 시나리오 추가 대화 상자를 연 다음, 이름과 영역을 지정한다.
그다음 반품 수량이 10이 적어지도록 값을 설정한다.
비슷하게 '수익률의 감소' 시나리오를 추가하여 반품 수량의 값을 10씩 늘려서 추가로 시나리오를 작성한다.
다음 대화상자에서 요약을 선택한다.
그리고 마지막으로 H15를 선택한 다음, 시나리오 요약을 선택한 다음 확인을 누른다.
그러면 다음과 같이 시나리오 결과가 새로운 시트에 출력된다.
이렇게 시나리오를 만드는 방법을 살펴보았다.
-데이터 통합
마지막으로 데이터 통합 기능을 살펴보자
아래의 데이터를 합쳐서 목표량과 판매량, 판매액의 평균을 서울/부산 대리점 판매현황 표의 영역에 계산하도록 하자
우선 결과를 나타낼 C17:E20영역을 선택한 다음
데이터 통합을 선택한다.
그럼 다음과 같은 대화창이 나타난다.
함수를 평균으로 선택한 다음, 참조 영역으로는 합쳐서 평균을 내길 원하는 데이터 표를 지정한다.
그럼 다음과 같이 두 가지 영역이 선택된다.
사용할 레이블을 첫 행, 왼쪽 열 모두 선택한 다음 확인을 누르면
다음과 같이 계산 결과가 나타난다.
이렇게 간단하게 데이터 끼리의 합과 평균 등을 구할 수 있다.
-목표값 찾기
아래의 표에서 김은혜 과장의 연봉이 3500만원이 되려면 기본급이 얼마여야 하는지 목표값 찾기 기능을 이용하여 알아보자.
데이터-가상분석-목표값 찾기를 선택한 다음, 아래의 대화상자에 내용을 기입한다.
수식 셀에 구하길 원하는 연봉이 적혀있는 셀을 선택한 다음, 찾는 값인 3500만원을 적고, 3500만원을 받기 위해서는 기본급이 얼마인지를 알아야 하므로 값을 바꿀 셀을 기본급의 셀로 선택한다.
그러면 다음과 같이 기본금의 데이터 값이 변한걸 볼 수 있다.
이렇게 목표값 찾기를 이용해 기본급이 얼마여야 연봉이 목표액수가 되는지 알아보았다.
-데이터 표와 시나리오는 이렇듯 값에 따라 달라지는 결과를 표시한다는 것은 같다.
하지만 사용 방법이 약간씩 다르고, 표시하는 기능도 다르기 때문에
두 가지의 기능을 사용하는 방법과 결과를 잘 알아두도록 하자.
데이터 통합은 단순하게 각각 다른 데이터 표의 값을 합치거나 평균을 내는 기능이다.
간단하기 때문에 가볍게 알아두도록 하자
목표값 찾기또한 직관적으로 필요한 셀을 선택하여 바로바로 나타낼 수 있다.
피벗테이블은 무조건 출제되고 나머지에서 데이터 표, 통합, 시나리오, 목표값 찾기 중 하나가 나오기 때문에, 피벗 테이블을 세세한 옵션까지 자세히 공부하고 나머지는 간단하게 공부하고 넘어가도록 한다.
'컴활1급 실기 공부' 카테고리의 다른 글
| 21강. 컴활1급 실기 엑셀-매크로와 프로시저 작성 (0) | 2021.06.27 |
|---|---|
| 20강. 컴활1급 실기 엑셀-차트 (0) | 2021.06.26 |
| 18강. 컴활1급 실기 엑셀-피벗 테이블 (0) | 2021.06.22 |
| 17강. 컴활1급 실기 엑셀-부분합과 정렬 (0) | 2021.06.21 |
| 16강. 컴활1급 실기 엑셀-데이터 유효성 검사 (0) | 2021.06.20 |
