18강. 컴활1급 실기 엑셀-피벗 테이블
-피벗 테이블은 작성된 데이터 목록의 필드를 재구성하여 전체 데이터에 대한 통계를 한눈에 파악할 수 있도록 정리된 표로 만드는 기능을 말한다.
피벗 테이블은 정렬, 부분합, 자동 필터 등 엑셀에서 수행할 수 있는 다양한 기능을 실행할 수 있기 때문에 이 기능들을 모두 능숙하게 사용할 줄 알아야 한다.
예시 문제를 통해 피벗 테이블의 기능을 하나하나 알아보도록 하자
-피벗 테이블 만들기 예제
이해를 돕기 위하여 예제를 풀어보도록 하자
다음의 지시에 따라 피벗 테이블 보고서를 작성하라
1. 외부 데이터 가져오기 기능을 이용해 데이터를 가져오고, '제조 순번', '가공일', '가공팀', '단위 당원가', '목표 매출액'열을 이용하시오
2. 피벗 테이블 보고서의 레이아웃을 개요 형식으로 표시하시오
3. 가공일 필드는 월 단위로 그룹을 지정하시오
4. 완성된 피벗테이블 보고서에는 피벗 스타일 보통 6을 지정하시오
5. 가공팀 필드는 제조1팀, 제조 3팀, 제조 5팀만 나타나도록 하고, 단위 당원가와 목표 매출액 필드는 열 합계 비율을 기준으로 표시하시오
6. 값 영역의 표시 형식은 값 필드 설정의 셀 서식을 이용하여 소수 1자리까지 표시하시오
7. 제조3팀의 3월 목표 매출액만 다른 시트에 표시하시오
이제 외부 데이터 가져오기를 시작으로 하나하나 문제를 해결해 보도록 하겠다.
가져올 데이터는 쿼리 데이터이기 때문에 기타 원본에서 데이터 가져오기 후 microsoft query를 선택한다.
다음 데이터 원본선택 창이 표시되면, 다음과 같이 설정한 다음 확인을 누른다.
데이터는 피벗 테이블 보고서로 작성위치를 설정한 다음 확인을 눌러 데이터를 가져온다.
그다음 피벗 테이블의 레이아웃을 설정한다.
필드 목록을 다음과 같이 설정하였다.
행이나 열 영역에 날짜 형식의 필드를 넣으면 연, 분기, 월 등의 필드가 자동으로 생성된다.
또한 값 영역에 두 개 이상의 필드를 넣으면 월 영역에 자동으로 합해진 값이 생성된다.
필드 목록은 숨기거나 나타낼 수 있다.
위의 버튼을 눌러 필드 목록을 나타나게 하거나 숨길 수 있다.
또한 문제에서 원하는 레이아웃 설정은 다음의 버튼으로 할 수 있다.
레이아웃은 문제에서 재시한대로 개요 형식으로 설정한다.
다음과 같이 피벗테이블이 나타나게 된다.
그 다음 값 필드 설정을 이용해 값을 바꾸고 표시 형식을 변경해 소수점 첫째 자리 까지만 표시해보자
값 필드 설정은 원하는 필드인 단위당원가를 선택한 다음 오른 클릭 후 설정창에서 들어갈 수 있다.
여기서 평균을 선택하고, 값 필드 설정은 열 합계 비율로 선택한다.
왼쪽 아래의 표시 형식을 눌러 백분율로 표시형식을 바꾸고, 소수점 첫째 자리까지만 나타나게 하자.
이 과정을 마치면 피벗표시형식은 아래와 같이 변경된다.
다음으로는 그룹화 방식을 알아보자
가공일 필드를 월 단위로 그룹을 지정하기 위해 가공일 필드의 바로 가기 메뉴에서 그룹을 선택한 다음,
일을 선택해제 하여 월만 지정된 상태에서 확인을 누른다.
그럼 이렇게 월 별로 데이터가 그룹화된 모습을 볼 수 있다.
마지막으로 스타일과, 데이터를 따로 표시하기만 하면 된다.
피벗 스타일 보통 6을 선택하고 적용시켜 준다.
그리고 가공팀의 화살표를 눌라 1,3,5팀만 표시하게 해 주면 다음과 같은 결과가 나온다.
그리고 제조3팀의 3월 목표 매출액이 있는 셀을 더블클릭하면 다른 시트에 해당 데이터를 따로 표시할 수 있다.
이렇게 피벗 테이블의 기능을 사용하여, 문제를 해결해 보았다.
주어진 조건들을 잘 읽고 문제를 해결하도록 하자