2019101회차 ITQ엑셀 제3작업 - 피벗테이블

2019101회차 제3작업인  피벗테이블에 대한 문제를 풀어보도록 하겠습니다.
엑셀에서 피벗테이블은 복잡한 대량의 데이터를 정렬 및 요약할 때 유용하게 사용됩니다. 피벗테이블은 업무처리에서도 유용하게 사용되기 때문에 ITQ엑셀 자격증 시험에 자주 등장하므로 필수적으로 확실하게 익혀두어야 합니다.

기능적인 부분이므로 익숙해질 때까지 반복학습이 필요한 부분입니다. 하나의 문제를 몇번 반복한 후 다른 문제를 풀어 보시기 바랍니다.

 

 

[제3작업] 피벗테이블 (80점)

 

☞ “제1작업”시트를 이용하여 ”제3작업”시트에 조건에 따라 출력형태와 같이 작업하시오.

≪조건≫
(1) 가격 및 구분별 제품코드의 개수와 판매수량(단위:EA)의 평균을 구하시오.
(2) 가격을 그룹화하고 레이블이 있는 셀 병합 및 가운데 맞춤으로 설정하시오.
(3) 구분을 ≪출력형태≫와 같이 정렬하고 빈 셀은 ‘**’로 표시하시오.
(4) 행의 총합계를 지우고 나머지 사항은 ≪출력형태≫에 맞게 작성하시오.

 

≪출력형태≫

 

 

조건 1번의 문제를 풀기 위해 다음과 같이 피벗테이블을 형성하도록 합니다.

 

 

위의 그림과 같이 순서대로 제1작업 시트의 B2에서 J12까지 범위를 지정한 후 삽입메뉴의 [피벗테이블[을 선택합니다.

 

 

피벗테이블 만들기 박스가 나타나면 첫째 범위가 제대로 지정되었는지 확인하고 기존워크시트를 선택 후 위치는 이미 만들어 놓은 제3작업시트를 선택 후 다시 B2시트를 선택하면 번과 같이 자동으로 입력되어 집니다.

 

 

이제 피벗테이블에 들어갈 내용을 열레이블과 행레이블 그리고 값에 위에 있는 필드를 하나씩 선택하여 마우스로 드래그 하여 끌어놓습니다.

 

 

열레이블엔 구분을 행레이블엔 가격, 값에는 제품코드와 판매수량을 차례로 끌어다 놓습니다.

모두 마우스로 드래그 하여 완성하였다면 판매수량이 합계가 아닌 평균으로 바꿔 주어야 합니다. 이 부분의 콤보박스(▼)를 클릭하여 [값 필드 설정]을 선택합니다.

 

 

[값 필드 요약 기준]을 평균으로 지정한 후 사용자 지정이름을 보완합니다. 즉, (단위:EA)를 추가로 입력하고 확인을 누릅니다. 조건설정의 1번 문제가 완성되었습니다. 이제 조건 2번 문제의 가격을 그룹화 할 차례입니다.

 

 

가격에서 우측 마우스버튼을 눌러 단축메뉴의 그룹을 선택합니다.

 

 

그룹화 대화상자에서 출력형태를 참고하여 시작은 2001, 끝은 11000으로 지정하고 단위는 3000을 입력하고 확인을 누릅니다. 가격그룹화가 완성되었습니다.

 

다음으로 레이블이 있는 셀을 병합하여 가운데 정렬을 하여야 하는데 이는 메뉴의 병합하고 가운데 맞춤을 선택하는 것이 아니라 피벗테이블에서는 이 기능이 자체적으로 별도로 준비되어 있습니다. 단품을 선택하고 마우스 우측버튼의 [피벗테이블옵션]에서 설정합니다.

 

 

피벗테이블 옵션의 [레이아웃 및 서식] 탭에서 레이블이 있는 셀 병합 및 가운데 맞춤에 체크표시를 합니다. 조건설정 2번 문제가 완성되었으므로 3번 문제를 해결하도록 하겠습니다.

 

 

구분을 출력형태와 같이 정렬하라는 말은 구분이 현재 단품-사이트-세트와 같이 정렬되어 있는데 실제 문제에서 요구한 출력형태는 내림차순으로 정렬되어 있습니다. 열레이블 옆의 콤보박스(▼)를 클릭하여 텍스트 내림차순 정렬을 선택합니다.

 

 

위의 동그라미 친 부분을 보면  내림차순인 세트-사이드-단품으로 정렬되었습니다.
다음 순서로 빈 셀에 “**”표시를 해야 합니다. 피벗테이블의 아무데서나 우측버튼을 클릭하여 [피벗테이블 옵션]의 [레이아웃 및 서식]탭에서 빈셀표시의 공란에 “**”를 입력합니다.

 

 

4번문제에서는 총합계를 지우고 출력형태처럼 나머지를 지정해 줍니다. 우선 “전체개수:제품코드”즉 I4셀을 선택 후 마우스우측버튼을 클릭하여 피벗테이블 옵션으로 들어갑니다.

 

 

나타나는 [피벗테이블 옵션]의 총합계에서 총합계 표시를 체크해제 해줍니다. 확인도 눌러야 겠지요.

 

 

마지막으로 C2셀과 B4셀에 있는 행레이블과 열레이블을 구분과 가격으로 수정합니다.
다음으로 전체를 범위 지정 후 콤마(,)를 설정하면 위의 그림과 같이 소수점 이하는 자동으로 반올림 되어집니다. 해설을 보지 않고 스스로 빠르게 풀 수 있도록 반복 실습이 가장 중요합니다. 이상으로 피벗테이블에 대한 문제풀이를 마칩니다