ITQ 엑셀 피벗테이블을 만들어보자

안녕하세요~ 탑직업전문학교 쭌강사입니다.

오늘은 ITQ엑셀 작업중에서 제3작업인 피벗 테이블에 대해서 알아보겠습니다.

피벗테이블은 배점 80점이나 되는 중요한 작업부분이니 꼼꼼히 블로그 내용을 보면서 익히시기 바랍니다.

먼저, 문제에보면 "제1작업" 시트를 이용하여 "제3작업" 시트에 조건에 따라 <<출력형태>>와 같이 작업하시오. 라고 되어있죠?

그럼 제1작업 시트로 이동해서 데이터를 선택해보도록 하겠습니다.

자 위의 그림과 같이 제1작업 시트에서 B4셀인 관리번호 부터 H12셀 230,000까지 블록을 잡아줍니다.

그다음 엑셀상단 삽입메뉴를 클릭한후 맨처음에 위치한 피벗테이블 버튼을 클릭해 줍니다.

그러면 위와 같은 '피벗 테이블 만들기' 창이 뜨게 됩니다.
'피벗 테이블 만들기' 창에서 기존 워크시트를 선택한후 아래 위치 글자 오른쪽 끝에  아이콘을 눌러준 후 제3작업 시트의 B2셀을 선택해 줍니다.
여기서 기존워크시트를 선택한 이유는 문제에서 제3작업 시트에 피벗테이블을 나타내라고 나와있기 때문입니다.

제3작업 시트의 B2셀을 선택하면 위의 그림과 같이 피벗 테이블 만들기 창에 '제3작업!B$2'라고 나오게 됩니다. 엔터를 누른후 확인 버튼을 눌러줍니다.

 

확인을 누르면 위의 그림과 같이 피벗테이블1, 보고서를 작성하려면 피벗 테이블 필드 목록에서 필드를 선택하십시오.란 화면이 나오게 됩니다.
우측에는 피벗테이블필드목록이라고 해서 보고서에 추가할 필드 선택, 보고서 필터, 열 레이블, 행 레이블, 시그마 값이란 항목을 넣을 수 있게되는 조그만 창들이 보이게 됩니다.

이제 피벗테이블에 들어갈 필드를 보고서에 추가할 필드선택 항목에서 아래 값들을 넣을 수 있는 조그만 창으로 끌어넣도록 하겠습니다.
문제를 보니 행은 시작일, 열은 과정이 들어가고 값에는 캠프명의 개수와 신청인원의 개수가 들어가게 됩니다.

위의 그림은 열에 과정, 행에 시작일, 값에 캠프명의 개수, 신청인원을 넣은 화면입니다.
그런데 위에서도 말씀드렸지만 신청인원은 합계가 아니라 평균이라고 했죠?
자, 그러면 값부분에 합계 : 신청인원(명) 옆에 ▼펼침버튼을 눌러줍니다.

펼침버튼을 누르면 위와 같이 메뉴가 나오게 되는데 맨하단에 '값 필드 설정'을 클릭해 줍니다.

값 필드 설정 창에서 요약에 사용할 계산 유형을 선택하십시오.에서 평균을 선택해주고 확인 버튼을 클릭해 줍니다.
확인버튼을 누르면 피벗테이블 내용안에 신청인원 값부분이 합계에서 평균으로 바뀌는것을 확인하실 수 있으실 겁니다.

자 다시 문제로 돌아와서 <<조건>>부분을 보도록 하겠습니다. <<조건>> (1)에 '시작일 및 과정별 캠프명의 개수와 신청인원(명)의 평균값을 구하시오'라고 되어 있는데 (1)번은 처음 피벗테이블을 만들었을때 값에 넣어주었기 때문에 작업이 이미 되어 있는 것이 됩니다.
그럼 다음 (2)번을 살펴볼까요? (2)번 처음에 '시작일을 그룹화하고'라고 되어 있고, 출력화면에 보면 날짜가 7월, 8월, 9월로 되어 있습니다.
이것은 무엇을 의미할까요? 그렇습니다. 날짜를 월로 그룹화하라는 이야기가 되는 것입니다.
자 그럼 그룹화하는것을 살펴볼까요?

날짜 아무곳이나 선택한 후 오른쪽 버튼을 누르면 위의 그림과 같이 그룹메뉴가 보일것 입니다. 그룹메뉴를 클릭해 줍니다.

그룹화 창에서 '월'로 그룹화 하기 위하여 월을 선택한후 확인을 선택해 줍니다.

그러면 위의 그림과 같이 7월, 8월, 9월 이렇게 그룹화가 되어지게 됩니다.
자 그러면 계속해서 문제를 볼까요? 문제에서 그룹화하고 옆을 보니 '레이블이 있는 셀 병합 및 가운데 맞춤으로 설정하시오'라고 되어있는데
이건 또 어떻게 하는지 살펴보도록 하겠습니다.

피벗테이블 안에 아무셀이나 선택하고 오른쪽 버튼을 누르면 위의 그림과 같이 메뉴가 뜨게 되는데 하단에서 2번째에 있는 '피벌 테이블 옵션'을 선택합니다.

피벗 테이블 옵션창의 첫번째 탭인 '레이아웃 및 서식'에서 '레이블이 있는 셀 병합 및 가운데 맞춤'을 선택해 줍니다.
그리고 문제에서 (3)번 문항을 보면 빈셀은 '**'로 표시하라고 나왔기 때문에 피벗 테이블 옵션창 중간에서 약간 밑쪽에 '빈 셀 표시'를 선택해주고 값에다가 '**'을 입력해 줍니다.

다음으로 문제 (4)번을 보면 '행의 총합계를 지우고'라고 되어 있으므로 피벗테이블 옵션창의 요약 및 필터탭으로 이동하여 '행 총합계 표시' 항목을 체크해제 해줍니다.

자 확인 버튼을 누르면 위의 그림처럼 나오시나요?
만약 위의 그림처럼 나오지 않으신다면 다시한번 위에서부터 꼼꼼하게 빠진 작업이 없는지 확인해 보시기 바랍니다.

이제 어느정도 문제의 결과화면과 비슷해진 것 같습니다. 그렇죠?
자 이제 끝을 향해서 한번 달려가 보도록 하겠습니다.
위의 그림을 보니 열 레이블, 행 레이블이 있죠? 그런데 결과화면에는 과정, 시작일로 되어 있을 것입니다. 그렇죠?
이 부분은 그냥 타이핑을 해주면 되는 부분입니다. 쉽죠?

자 작업을 하셨으면 이어서 계속 보도록 하겠습니다.
작업한 화면을 보니 문제의 결과화면과 다른게 문제의 결과화면에는 체험, 리더십, 과학순으로 항목이 나왔는데 저희가 작업한 화면에는
과학, 리더십, 체험으로 나와있죠?
눈치빠르신분들은 알아차리셨겠지만 이건 정렬이 아직 안되어 있기 때문입니다.
자 그럼 정렬방법을 알려드리도록 하겠습니다.

정렬은 시작일우측에 ▼펼침버튼을 누르면 쉽게 하실 수 있습니다.
펼침버튼을 누르면 '날짜/시간 오름차순 정렬'과 '날짜/시간 내림차순 정렬'이 있는데
우리는 체험, 리더십, 과학순으로 내림차순(하→가)으로 정렬할거기 때문에 '날짜/시간 내림차순 정렬'을 선택해 줍니다.

이제 거의다 완성이 되가는데요?
마지막으로 해야할것이 아직 하나 남아 있습니다.
빈셀에 표시하도록 설정한 '**' 셀의 가로정렬을 중앙정렬을 해야하고, 소수점으로 나온 값들을 제대로 나오도록 정리해 주어야 합니다.

먼저 컨트롤키를 누른상태에서 '**'로 되어 있는 부분을 선택하여 중앙정렬을 눌러줍니다.

자 이제 마지막으로 소수점으로 나온 값들을 정수처리를 해주도록 하겠습니다.
소수점이 있는 22.3333333과 32.66666667, 28.5 이 세가지를 컨트롤키를 눌러 선택해준 후 오른쪽 버튼을 눌러 셀서식을 클릭해 줍니다.

 

셀서식을 누르면 범주가 일반으로 선택되어 있는데요?
일반에서 숫자로 선택을 변경해준 뒤 확인 버튼을 누릅니다.

자 드디어 피벗테이블이 완성되었습니다.
잘 따라와주신 여러분 모두 수고하셨습니다.
피벗테이블은 과정은 복잡해보여도 하나하나 작업들을 따라하시다보면 그렇게 어렵진 않다는것을 느끼실 것입니다.
어려우시다고 느끼신 분들은 하나하나 처음부터 여러번 반복해서 따라하시다보면 익숙해지실 것입니다.

여기까지 ITQ 엑셀 - 피벗테이블 강좌를 마치도록 하겠습니다.
감사합니다.