컴퓨터활용능력1급 피벗테이블 공략

피벗테이블 은 엑셀 파트에서 까다로운 파트입니다

 

피벗테이블의 점수는 10점입니다

 

피벗테이블은 한 가지 실수라도 해버린다면 부분점수 없이 10점이 한 번에 없어집니다

 

실제 시험 치는 분들이 피벗테이블에서 많이 좌절하십니다

 

 

피벗테이블은 결과 그림이 주어집니다

 

결과 그림과 똑같이 만들어야 하는데요

 

글씨 하나라도 틀리면 틀린 처리가 돼버립니다

 

 

피벗테이블 하시면서 중요한 점을 알려드리겠습니다

 

 

첫 번째

 

문제 지문에서 오름차순 하라고 했는데

 

결과 그림에는 내림차순으로 되어 있는 경우

 

이런 경우에는 문제 지문을 우선으로 해줍니다

 

결과 그림이 다르더라도 문제 지문이 우선이라서 

 

문제 지문에 따라줍니다

 

 

 

두 번째

 

문제에서 피벗테이블 위치를 결과 그림을 보고 알아서 지정하라고 나옵니다

 

또는 위치를 알려주기도 하는데요

 

위치를 안 알려준 경우

 

피벗테이블 위치를 결과 그림을 통해서 확인한 후에 만드셔야 합니다

 

위치를 확인하는 법은

 

 

이렇게 표의 왼쪽 상단 모서리 셀 기준으로  빨간 네모 박스를 쳐준 곳 ( L26 ) 이 위치입니다 

 

간혹

 

보고서 필터를 기준으로 두시는 분도 계시는데요

 

보고서 필터와는 상관없습니다

 

표의 왼쪽 상단 모서리 셀 기준으로 빨간 네모 박스 쳐진 곳을 클릭 후에  만드시면 됩니다

 

그리고 

 

피벗테이블을 모두 만들었는데 위치를 잘못 줘버렸다 하시는 분들도 계십니다

 

이런 경우에는 피벗테이블을 한번 클릭 후에 옵션에서

 

피벗테이블 이동을 눌러서 원하시는 위치에 놓아주셔도 됩니다

 

또 내 위치를 확인하고 싶을 때는 이곳에서 확인이 가능합니다 

 

 

 

세 번째

 

피벗테이블에서 계산 필드를 만들라고 합니다

 

계산 필드는 피벗테이블 내에 있는 데이터들을 이용해서 계산을 한 다음에 결과값을

 

새로운 필드 즉 계산 필드로 만들라는 것입니다

 

계산필드로 들어가는 방법은 피벗테이블을 한번 클릭 후에

 

위쪽 상단 옵션에서 들어가 주시면 됩니다

 

 

계산 필드에서 함수를 이용하는 경우도 있습니다

 

SUM함수 와 AVERAGE함수 STDEV함수입니다

 

여기서 STDEV함수는 표준편차를 구하는 함수입니다

 

피벗테이블 계산 필드 만드는 작업에서 자주 나오는 함수 이므로 알고 가시는 게 좋습니다

 

피벗테이블의 나오는 STDEV함수의 식 쓰임 형태는 STDEV(필드)입니다

 

필드 여러 개로 STDEV함수 쓰는 거면 STDEV(필드, 필드) 이런 식으로 하시면 됩니다

 

 

 

네 번째

 

문제 지문에서 보고서 레이아웃을 개요 형식 , 테이블형식, 압축형식 중에서 하라고 나옵니다

 

또는 결과 그림을 보고 알아서 하라고도 나올 수 있습니다

 

이때 각 형식들이 어떤 특징이 있는지 알으셔야 합니다

 

개요 형식은  아래그림처럼 나눠져 있는게 개요 형식의 특징입니다

 

 

 

테이블 형식은  아래그림처럼 붙어서 나오는 특징이 있습니다

 

 

 

압축 형식은 필드 이름이 나오지 않고 행 레이블, 열 레이블로 나오는 특징이 있습니다

 

 

 

문제에서 어떤 레이아웃을 하라는 설정이 없다면 결과 그림을 보고 판단하셔서 설정해주시면 됩니다

 

 

 

다섯 번째

 

∑ 값의 위치를 잘 지정해줘야 합니다

 

∑ 값이 열 레이블에 있는 상태에서 결과 그림을 보고 내가 작업한 피벗테이블과 다르다면

 

∑의 위치를 행 레이블로 옮겨보시면 결과 그림과 같을 겁니다 

 

 

∑ 값이 열 레이블에 있다면 아래 그림처럼 나옵니다

 

열레이블

 

 

∑ 값이 행 레이블에 있다면 아래 그림처럼 나옵니다

 

행레이블

문제 결과 그림을 잘 보시고 ∑값의 위치를 잘 지정해주시면 됩니다

 

 

 

여섯 번째

 

그룹을 잘 지정해줘야 합니다

 

문자 형식을 그룹을 지정해줄 때는 반드시 범위를 지정해주셔야 합니다

 

문자 형식 그룹을 지정해주시면 이렇게  새로운 그룹 영역이 오른쪽 그림처럼 나옵니다

 

 

 

숫자 그룹을 지정해줄 때는 반드시 범위를 잡지 않습니다 하나의 셀만클릭후 그룹을 지정해주시면

 

그룹화 창이 나옵니다 문제 지문에 맞게 지정해주시거나 결과 그림을 보고 시작, 끝 , 단위를 입력해주시면 됩니다

 

여기서 시작과 끝에 체크가 되어 있는데요

 

이 부분은 시작 , 끝 값에 입력을 해주셨다면 해제를 꼭 해주셔야 합니다

 

그리고

 

숫자 형식으로 되어있는 것을 문자 형식처럼 나오게 하는 경우도 있습니다

 

이경우에는 숫자를 범위를 잡고 문자 형식 하는 것처럼 해주시면 됩니다

 

그리고

 

날짜 를 그룹 지정해줄 때는 숫자 형식 하는 것과 같습니다

 

범위를 잡지 않고 한셀만 클릭 후 그룹에 들어가면

 

그룹화 창이 나옵니다

 

데이터에는 지문에서 제시한 것으로 적어주시면 됩니다

 

마찬가지로 시작, 끝에 체크는 풀어주셔야 합니다

 

 

 

 

일곱 번째

 

피벗테이블의 데이터 범위에 형식을 지정을 잘해야 합니다

 

데이터 영역에 \가 있다면 \를 넣어야 하고 세 자릿수마다 , 표시가 있다면

 

모두 잘 넣어줘야 합니다

 

 

데이터 영역을 범위를 한 번에 지정한 후에 컨트롤키+1을 눌러서 셀 서식에 들어가서

 

사용자 서식 에들어가서 문제 결과 그림에 맞게 넣어주면 됩니다

 

 

 

여덟 번째

 

정렬입니다

 

오름차순, 내림차순 지정을 해줄 때는

 

범위를 잡지 마시고 한셀만클릭후에 오름차순, 내림차순 지정을 해주셔야 합니다

 

범위를 잡고 오름차순 내림차순을 해버리면 데이터가 엉켜버립니다

 

그래서 오름차순 내림차순을 할 때는 해당하는 셀 하나만 지정 후에 해주시면 됩니다

 

간혹 예를 들어

 

문제 지문에서 체육, 과학, 수학, 영어, 국사 순으로 정렬하시오 라고 나옵니다

 

이경우에는 오름차순도, 내림차순도 아닙니다

 

그래서 직접 데이터를 마우스로 끌고 와서 정렬을 해줘도 됩니다

 

근데 그렇게 안 되는 경우도 있습니다

 

이경우에는 직접 사용자 지정 정렬 기능을 이용해서

 

풀이해주셔야 합니다 

 

 

하는 법은 시트 아무 곳에  아무 말이나 적습니다 그리고 마우스 오른쪽 버튼 눌러서 사용자 지정 정렬로 들어간 후

 

정렬에서 사용자 지정 목록을 누르시고 체육, 과학, 수학, 영어, 국사를 적어주신 후

 

추가를 누르고 취소 버튼을 눌러서 빠져나옵니다 

 

그런 뒤 정렬할 셀을 클릭한후 기타 정렬 옵션에 들어가면

 

이렇게 정렬 창이 나옵니다 여기서 기타 옵션에 들어가신 후 

 

보고서가 업데이트될 때마다 자동으로 정렬을 체크해제 하셔야지만

 

아래 상자가 활성화됩니다 그곳에서 내가 지정한 서식을 찾으신 다음에 확인을 누르시고

 

오름차순 기준으로 해주셔야 합니다

 

여기서 오름차순은 내가 체육, 과학, 수학, 영어, 국사 순으로 오름차순을 주겠다는 겁니다

 

그리고 사용자 지정 정렬을 하기 위해 시트에 아무거나 적었던 것도 꼭 지워줍니다

 

 

 

 

 

아홉 번째

 

피벗테이블 내에 속성입니다

 

지문에서 예를 들어

 

빈셀에는 *가 나와라  

 

또는 결과 그림의 빈셀에 *가 표시되어 있을 경우 

 

 

피벗테이블을 한번 클릭 후에 마우스 오른쪽 버튼을 눌러서 설정에 들어간 후

 

빈셀표시 란에 *을 입력해주시면 됩니다

 

 

그리고

 

피벗테이블에서 결과 그림에는 열 합계 비율만 표시되어있는 경우

 

행 합계 비율을 지워야 하는데요

 

이때는

 

피벗테이블을 클릭 후에 설정에 들어가서 열 합계 비율 행 합계 비율을 결과 그림에 맞게

 

설정해주시면 됩니다

 

그리고

 

값행표시

값이라고 적혀 있는 곳이 나타나게 해주고 싶다면

 

옵션에 들어가서  값행 표시를 체크해주시면 됩니다

 

 

 

 

 

열 번째

 

피벗테이블 문제에서 해당되는 데이터만 새로운 시트로 나오게 하라고 나올 수 있습니다

 

문제에서 어디에 있는 데이터를 새 시트로 나오게 해라 라고 나옵니다

 

해당되는 데이터를 더블클릭하면 새로운 시트가 나옵니다

 

엑셀에서는 새 시트를 자기 맘대로 나오게 하면 실격처리가 됩니다

 

근데 피벗테이블에서 이런 문제는 상관없습니다 다만

 

반드시 하나의 시트 만나오게 하면 됩니다

 

해당되는 셀을 더블클릭을 계속해버리면 새로운 시트는 계속 나오므로

 

하나의 새로운 시트만 나오게 해 줍니다

 

 

 

열한 번째

 

피벗테이블에서

 

양수는 음수는 0은 문자는 이렇게 지문에 제시를 해주는 경우

 

해당 데이터 영역을 범위를 잡으시고 셀 서식 창에 사용자 서식에서

 

양수;음수;0일 때;문자 방식으로 지정해주시면 됩니다

 

예를 들어

 

지문에서 음수와 0은 *로 나오게 하시오  라고 나오면

 

양수 자리에는 결과 그림 형식에 맞게 해 줍니다

 

예를 들어 결과 그림 형식의 양수가 8,215 라면 

 

#,###;"*";"*" 이렇게 해주시면 됩니다 뒤에 문자일 때 넣어주는 자리는 생략해줘도 됩니다

 

예를 들어

 

지문에서 0일 때는 *로 나오게 해라 라고 나오면

 

#,###;-#,###;"*" 이렇게 해주시면 됩니다 음수 자리에 -를 해줘야 합니다

 

 

 

 

열두 번째

 

값 필드 설정입니다

 

값 필드 설정을 쓰는 경우에는 해당하는 필드의 범위를 잡은 후

 

값 필드 설정에 들어가서 지문에서 제시한 것을  설정해주시면 됩니다

 

 

 

열세 번째 

 

컨트롤+1 눌러서 나오는 셀 서식 창과

 

값 필드 설정에 있는 표시 형식 창과 혼동하시는 분들이 많습니다

 

여기서 많은 분들이 둘이 똑같이 생겨서 셀 서식 창으로 해버리시는데요

 

이런 경우에 피벗테이블 점수는 10점 다 깎입니다

 

그래서 지문에서 반드시 값 필드 설정의 표시 형식이라고 언급이 나오면

 

컨트롤+1번눌러서 셀 서식 창을 켜는 것이 아니라

 

값 필드 설정의 표시 형식의 셀 서식 창을 켜서

 

빨간 박스 쳐진 표시 형식 에들어가서 설정을 해줘야 합니다