[스프레드시트] 데이터 분석(부분합, 시나리오, 시나리오요약보고서, 피벗테이블, 피벗차트보고서, 목표값찾기)

오늘 공부할 내용은

​

- 부분합

- 시나리오/시나리오 요약 보고서

- 데이터 표

- 피벗 테이블/피벗 차트 보고서

- 목표값 찾기

 

---

부분합

​

1) 첫 행에는 열 이름표가 있어야 함 = 기준이 될 필드(열) 먼저 정렬

오름차순, 내림차순이던 정렬 필요하며 결과는 같음 => 같은 열에 있는 자료에 대하여 여러개 함수 중 다중 함수 부분합 작성 가능

2) SUBTOTAL 사용하여 합계나 평균 등의 요약 함수를 계산

3) 부분합 제거 : 부분합 + 삽입된 윤곽 및 페이지 나누기 모두 제거

4) 부분합 결과 차트 : 화면에 보이는 데이터에 대해서만 차트 작성

5) 세부 정보가 있는 행 아래 요약 행 : 데이터 아래에 요약 표시 항목 선택

​

​

​

​

# 부분합 작업 과정

1) 정렬 지정 우선 - 오름차순, 내림차순, 사용자지정 3개 중 선택

2) [데이터] - [윤곽선] - [부분합]

- 1개 부분합 작업 : ‘새로운 값 대치’ 체크가 되어있는 상태에서 작업

- 2개 이상으로 중첩 작업 : ‘새로운 값 대치’ 체크를 다시 해제 (매번 해제 작업 해줘야함)

​

⇒ 중첩 부분합 : 이미 작성된 부분합 그룹 내에 새로운 부분합 그룹 추가하는 것

- 사용 할 수 있는 함수 : 합계, 개수, 평균, 최대값, 최소값, 곱, 숫자개수, 표준편차, 표본표준편차, 표본분산, 분산

​

---

 

시나리오

: 워크시트 모델의 결과를 예측 가능

​

변경 셀 : 데이터를 변경할 셀의 범위 지정

결과 셀 : 반드시 변경 셀을 참조하는 수식 입력

​

1) 별도의 파일 저장X 워크시트에 저장, 변경할 값을 수동으로 입력한 것

2) 시나리오 결과 : 요약 보고서, 피벗 테이블 보고서로 작성 가능

3) 시나리오 병합을 통해 다른 통합 문서나 다른 워크시트에 저장된 시나리오 가져오기 가능

4) 이자율, 손익 분기점, 주가분석 등에 많이 사용

​

- ‘요악 보고서’ 만들 때에는 결과셀을 반드시 지정할 필요X

- ‘피벗 테이블 보고서’ 만들 때에는 결과셀 지정하지 않으면 오류 메시지 뜸 - 결과 셀을 반드시 지정​해야함

- 여러 시나리오를 비교하기 위해 시나리오를 한 페이지의 피벗 테이블로 요약 가능

---

시나리오 요약 보고서

​

1) 2개의 시나리오로 작성한 시나리오 요약보고서는 새 워크시트에 표시

2) 시나리오 삭제 - 이미 작성된 시나리오 요약 보고서 삭제X

시나리오 요약 보고서 삭제 - 시나리오 삭제 X

3) 시나리오가 작성된 원본 데이터 변경 - 이미 작성된 시나리오 보고서에는 반영 X

4) 시나리오의 변경셀/결과셀에 이름 지정 = 시나리오 요약 보고서 작성하면 셀 주소 대신 지정한 이름 표시

5) 시나리오 요약 보고서 내의 모든 내용은 수정 가능, 자동으로 설정된 윤곽도 지우기 가능

​

---

 

데이터 표

: 특정 값의 변화에 따른 결과 값의 변화 과정을 표의 형태로 표시해주는 도구

​

​

행 입력 셀 : 행에 있는 변화되는 값을 사용할 주소 지정

열 입력 셀 : 열에 있는 변화되는 값을 사용할 주소 지정

​

​

1) 지정한 특정 값의 수에 따라 단일 표와 이중 표로 구분

2) 결과 값 중 일부분만을 수정할 수 없음

3) 결과값은 반드시 변화하는 특정값을 포함한 수식

4) 수식이 입력될 부분을 범위로 설정한 후 데이터 표 실행

​

---

피벗 테이블

​

- 구성 : 보고서 필터, 열 레이블, 행 레이블, 값

- 엑셀 목록, 데이터베이스, 외부데이터, 다른 피벗테이블 등의 데이터 사용 가능

​

1) 각 필드에 다양한 조건 지정 가능

2) 일정한 그룹별로 데이터 집계 가능

3) 원본데이터 변경될 때 자동으로 피벗 테이블 내용 변경X

= [데이터] → [새로고침] 이용해서 피벗테이블 데이터 변경

5) 작성한 피벗 테이블 필드 단추를 다른 열/행 으로 드래그하면 변경 가능

​

​

​

​

# 피벗 테이블 구성 요소

- 행 영역에 지정한 필드는 하나의 열에 모두 표시

열 영역에 지정한 필드는 하나의 행에 모두 표시

- 행/열 영역에 표시된 데이터는 수정O

값 영역에 표시된 데이터는 수정X

- 값 영역에 두 개 이상의 필드를 지정하면 열 영역 또는 행 영역에 값 필드 생성 (∑)

- 값 필드를 열 영역과 행 영역 중 어느에 삽입하느냐에 따라 값 영역에 추가된 필드 표시 방향 결정

​

​

​

​

# 피벗 테이블 그룹화

1) 문자, 숫자, 날짜, 시간 등 모든 필드에서 사용

2) 숫자나 날짜 필드일 경우에는 ‘그룹화’ 대화상자에서 시작, 끝, 단위 지정

3) 문자 필드에 그룹 만들기 : 그룹 이름 자동 부여 [그룹1, 그룹2 등등]

4) 그룹을 지정하려면 그룹을 묶고자 하는 데이터 기준으로 먼저 정렬

=> 정렬 방식 : 오름차순, 내림차순 상관 X

​

---

 

피벗 차트 보고서

​

- 피벗 차트 작성하면 자동으로 피벗 테이블도 작성됨

=> 피벗 테이블 만들지 않으면 피벗 차트 작성 불가

​

1) 피벗 테이블에서 항목이나 필드에 변화 → 피벗 차트도 변경

피벗 차트에서 변경 → 피벗 테이블도 변화

2) 피벗 테이블을 작성할 때 함께 작성하거나, 이미 작성된 피벗 테이블을 이용하여 작성

3) 피벗 차트는 피벗 테이블을 사용 = 피벗 테이블을 만들지 않고 피벗 차트 작성

4) 피벗 차트 추가 : 피벗 테이블이 있는 워크시트에 삽입

5) 피벗 테이블 삭제 : 피벗차트 → 일반차트 변경

피벗차트 삭제 : 피벗 테이블에는 변화X

6) 표준 차트의 항목, 계열 데이터가 피벗 차트 보고서에서 축필드(항목), 범례필드(계열), 값필드(데이터)에 해당

7) 분산형, 거품형, 주식형 차트는 피벗차트 보고서 작성X

​

---

 

목표값 찾기

​

수식 셀 : 결과값이 출력되는 셀 주소

찾는 값 : 목표로 하는 값

값을 바꿀 셀 : 목표값을 만들기 위해 변경되는 값 들어있는 셀 주소

​

1) 변하는 데이터를 하나만 지정 가능

2) 목표값은 사용자가 원하는 데이터를 직접 입력

3) 결과 값은 입력값을 참조하는 수식으로 작성되어야 함

 

---

 

컴퓨터활용능력 1급 필기,

2과목 스프레드시트 '데이터관리' 배우러가기

[스프레드시트] 데이터 관리(레코드관리, 정렬, 자동필터, 고급필터) (tistory.com)