데이터 재정리하기 피벗 테이블과 피벗 차트 목표값 찾기와 시나리오 관리자

Presentation on theme: "데이터 재정리하기 피벗 테이블과 피벗 차트 목표값 찾기와 시나리오 관리자"— Presentation transcript:

1 데이터 재정리하기 피벗 테이블과 피벗 차트 목표값 찾기와 시나리오 관리자
11장 미래를 내다보는 안목, 데이터 분석과 예측 데이터 재정리하기 피벗 테이블과 피벗 차트 목표값 찾기와 시나리오 관리자

2 데이터 재정리하기 부분합 - [데이터]-[윤곽선]-[부분합]
데이터 범위의 열 방향의 특정 항목을 기준으로 데이터별로 그룹화 시키고, 그룹화된 부문별로 합계, 평균, 개수, 최대, 최소, 표준편차, 분산 등을 자동으로 계산해주는 기능

3 데이터 재정리하기 부분합 대화상자 윤곽 기호 사용
그룹화할 기준이 기준이 되는 항목 선택(정렬된 필드) 그룹화할 필드에 적용할 함수 선택 함수를 적용해서 계산할 필드 기존 값을 새로운 부분합 값으로 대치할지, 기존 값은 보존하고 새로운 부분합 값을 추가할 지 결정 부분합을 구한 뒤 각 그룹 다음에 페이지 나누기를 자동으로 삽입 요약 결과를 표시할 지 결정 부분합을 해제하고, 원본 데이터 목록을 표시 윤곽 기호 사용 1은 전체결과(총 합계)만 표시, 2는 소계만을 표시, 3은 전체데이터를 표시 기호를 클릭하면 숨겨져 있는 하위 수준(그룹)의 데이터를 표시하며, 기호를 클릭하면 하위 수준(그룹)의 데이터를 숨김

4 데이터 재정리하기 수동으로 그룹 및 그룹 해제 그룹 및 윤곽 표시 기능은 자동으로 그룹 및 윤곽 기호를 표시해 주는 부분합과 달리 직접 데이터 항목의 행 또는 열 방향으로 셀 범위를 묶어서 윤곽 기호를 표시하여 일부 셀 범위를 축소하거나 확대할 수 있다. 데이터 그룹: 데이터를 그룹화 시키려는 행/열 머리글의 범위를 지정하고 [데이터] 탭의 [윤곽선] 그룹의 [그룹] 선택 그룹 해제: 데이터를 그룹 해제하려는 행/ 열 머리글의 범위를 지정하고 [데이터] 탭의 [윤곽선] 그룹의 [그룹 해제]를 선택

5 부분합 기능으로 데이터 요약하기 부분합 부분합 기능을 이용하여 ‘부서별 재고량&실사량 소계’, ‘재고량의 상태별 재고량&실사량 소계’ 를 구하고 데이터를 요약하여 부서별 재고현황표를 만들어 본다

6 임율표 데이터 그룹 지정하기 데이터 그룹 2007년 상반기 임율표에서 인건비, 복리후생비, 기타 범위를 그룹화하여 그룹 윤곽표시를 나타내 본다.

7 피벗 테이블과 피벗 차트 피벗 테이블 피벗 차트 피벗 테이블 만들기
복잡한 데이터를 분석하여 행과 열 방향으로 그룹화된 항목을 정렬 및 요약하여 표 형태의 대화형 테이블 피벗 차트 피벗 테이블의 요약 데이터를 시각화한 차트 피벗 테이블 만들기 피벗 테이블을 만들려면 [삽입] 탭의 [표] 그룹에 있는 [피벗 테이블]을 선택하여 [피벗 테이블 만들기] 대화상자에서 데이터의 범위와 테이블 보고서가 나타날 위치를 지정

8 피벗 테이블과 피벗 차트 피벗 테이블 구성 레이아웃 ❶ ❷ ❹ ❸ ❺ ❻ ❼
필드 목록 : 데이터 범위의 필드 목록이 표시되며 각 필드를 선택/해제하여 피벗 테이블 레이아웃을 설계 보고서 필터 목록 : 전체 데이터 영역을 요약할 보고서 필드 열/행레이블 목록: 열/행 방향으로 그룹화 할 필드 값 목록 : 일반적으로 숫자 값이 들어 있는 필드가 위치하며 행/열 레이블에서 지정한 필드를 분석하여 합계, 평균, 최대, 최소, 개수 등을 계산 ❷ ❹ ❸ ❺ ❻ ❼ 보고서 필터 : 보고서 필터 단추를 클릭하여 필요한 데이터 항목을 선택하면 데이터 영역에 표시되는 요약 정보를 일부 또는 전체를 표시/해제 행/열 레이블 : 행/열 레이블 단추를 클릭하여 필요한 데이터 항목만 선택하여 표시/해제 데이터 값 : 행/열 레이블을 분석하여 계산한 데이터 값이 표시

9 피벗 테이블과 피벗 차트 만들기 피벗 테이블과 피벗 차트
거래처별 판매 현황 데이터에서 일자, 거래처, 제품, 금액 필드를 사용하여 피벗 테이블과 피벗 차트를 만들어 본다.

10 목표값 찾기와 시나리오 관리자 목표값 찾기 – 시나리오 관리자 - [데이터]-[데이터 도구]-[가상 분석]-[목표값 찾기]
① 수식 셀: 수식 셀의 수식을 계산하여 목표 값(찾는 값)으로 변경되는 셀 주소(반드시 수식이 있는 셀이어야 함) ② 찾는 값: 수식 셀에서 도달해야할 목표 값 ③ 값을 바꿀 셀 : 목표값에 도달하기 위해 변경되어야 할 값이 있는 셀 주소(수식이 아닌 셀 값이 있는 셀이어야 함) ❶ ❷ ❸ 시나리오 관리자 - [데이터]-[데이터 도구]-[가상 분석]-[시나리오 관리자] 시나리오 목록 : 시나리오를 추가하여 작성된 시나리오 목록 추가 : 새로운 시나리오를 추가 삭제 : 시나리오 목록에서 선택한 시나리오 삭제 편집 : 시나리오 목록에서 선택한 시나리오 편집 병합 : 다른 통합 문서에 있는 시나리오 병합 요약 : 시나리오 요약 보고서를 보고서/ 피벗 테이블로 작성 표시 : 시나리오 목록에서 선택한 시나리오 지정 내용 워크시트에 표시 ❶ ❷ ❸ ❹ ❺ ❻ ❼

11 총 수익의 손익 분기점 목표값 찾기 목표값 찾기 손익분기점(Breakeven point, BEP)이란 총수익과 총비용이 동일한 조업도 상태에서 이익이 0이 되는 시점의 판매량 또는 매출액을 말한다. 월초에 MP3을 개당 65,000원에 구입하여 100,000원에 판매하려 한다. 이때 임차료, 인건비 등을 포함하여 월 고정원가는 200만원이 소요된다고 했을 때 총수익의 손익 분기점 시점이 언제인지 목표 값 찾기를 이용하여 판매량의 개수를 구해본다

12 할인율에 따른 시나리오 작성하기 시나리오 작성
판매수량에 따른 할인율을 ‘현재 수준’, ‘인하’, ‘인상’ 했을 때의 세가지 상황으로 시나리오로 만들고 그 시나리오에 따라 할인율과 할인율에 따른 할인 단가가 어떻게 변하는지 요약 보고서를 만들어 본다