[ITQ엑셀10] 제3작업 피벗테이블

[계란으로 PC깨기?]

ITQ엑셀 : 제3작업 - 피벗테이블

요즘들어 부쩍 힘들어졌음을 느낍니다.

(ㅠㅠ 나이 탓은 아닙니다. 아직은 젊다구요.. 그래두... )

 

독서량이 1주일에 1권으로 줄어든 것만 봐도.. 일이 많아졌음을 실감하게 됩니다.

(그래도 즐거워서 하는 일이라 버티나봅니다.)

 

다행히 수업이 어려운 것 없이 무난한 수업들이라.. 

수업준비에 많은 시간이 사용되지 않아서 그나마 다행이네요..

(앗, 이러면.. 수업준비 소홀하다고 눈치채실텐데... 쉿!!!)

 

이번에는 ITQ엑셀 500점 만점 중에 80점을 차지하는 제3작업을 다루겠습니다.

한 개의 강의로 다 끝날 수 있을지 모르겠군요.. 

일단은 시작합니다. 

 

그동안 완성했던 파일은 아래에 첨부파일로 등록해 두었습니다. 

다운 받아서 따라해 주세요.

20120801-김원택.xlsx

문제파악

 

3작업은 피벗테이블 문제입니다.

피벗테이블은 그동안 정리해 두었던 데이터를 요약해주는 표를 만드는 겁니다. 

실제 회사에서 엑셀에 능숙한 상사는 피벗테이블을 잘 활용하는데.. 그렇게 되면 하사(?)는 불행해지죠.. ㅡㅡ

 

어쨌든 알아두면 정말 유용한 엑셀의 요약기능입니다.

 

우리가 풀어야 할 문제는 아래와 같습니다.

 

피벗테이블을 만들고.. 그룹화.. 정렬.. 기타 등등.. 우스운(?) 문제들 뿐입니다.. 

쉽다는 얘기죠..

이번에도 역시 소설책 읽듯 읽어가며 해결하면 됩니다. 

피벗테이블 작성

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

     기본적인 피벗테이블을 만들라는 말입니다.

우선 블럭설정을 합니다. 

"제1작업"시트의 원본데이터가 입력된 부분만 필요합니다. [B4:H12]까지만 블럭설정 합니다.

 

피벗테이블 기능을 실행합니다.

[삽입] - [피벗테이블]을 선택하면 됩니다.

[피벗테이블 만들기] 대화상자가 나오면 삽입 위치를 지정해 줍니다. 

차트는 자동으로 떠~억 하고 자리를 잡지만 얘는 시작 위치를 정확히 지정해 주어야 합니다. 

그래서 [기존 워크시트]를 선택하고, 위치 공란을 클릭하고 "제3작업"시트 의 "B2"셀까지 끝까지 선택해 줍니다.

뭔가 나오긴 하는데 모두 빈 공란만 있네요. 왼쪽부분은 자동으로 만들어 질 곳이니까 신경 끄시고..

오른쪽부분을 문제에 맞춰 설정하면 기본 피벗테이블은 완성됩니다.

항목 삽입

(1) 단가(단위:원) 및 종류별 상품명의 개수와 주문수량의 평균을 구하시오.

     항목을 잘 채워 넣어라~ 라고 요구하는 거죠?

문제의 <출력형태> 그림을 보면, 

가장 왼쪽에 "단가"가 있습니다. 우리도 왼쪽(=행레이블)에 "단가..."를 끌어다 놓습니다.

가장 위쪽에는 "종류"가 있네요. 역시 우리도 위쪽(=열레이블)에 "종류"를 끌어다 놓으면 됩니다.

그리고 문제를 보면 "상품명의 개수"와 "주문수량의 평균"을 구하라고 하니까 "값"이라는 곳에 

이 둘을 끌어다 놓으세요.

참고로 보고서 필터는 시험에 잘 안나오니까 항상 비워둔다 생각하세요. 

하나씩 갖다 놓을 때마다 왼쪽의 원본이 계속 변경되는 모습이 보입니다. 신기하죠?

다 끌어다 놓고 보니 주문수량이 "평균"이 아닌 "합계"라고 되어 있어서 수정해 줄 필요가 있겠네요.

"합계:주문수량"을 클릭하면 메뉴가 나오는데.. 

가장 아래에 있는 "값 필드 설정"을 선택해 줍니다.

그리고 목표한 대로 "평균"을 선택하고 닫아 주세요.

그룹설정

"단가(단위:원)를 그룹화하고 ...

     문제 그림을 보니까 0-1999, 2000-3999, ... 하며 뭉쳐 놓았네요.. 그거 하는 겁니다.

가장 왼쪽 줄인 B열이 그룹설정 되어 있으니까 B열의 아무 항목에서나 오른쪽 마우스를 누릅니다.

그러면 바로 "그룹"이라는 메뉴가 보여요. 선택해 주세요.

3개의 숫자를 입력하라고 나오는데.. 쉬워요.

문제의 그림 중에서 가장 작은 숫자를 "시작"에 써 넣고, 

가장 큰 숫자를 "끝"에 쓰고, 

얼마씩 커지는지를 "단위"에 쓰면 됩니다.

이 문제에서는 순서대로 0, 10000, 2000 이군요. That's all. 그게 다예요.

옵션설정

... 레이블이 있는 셀 병합 및 가운데 맞춤으로 설정하시오. ... 빈셀은 "***"로 표시하시오...

     보기좋게 바꾸라는 건데.. 가 봅시다. 

     문제(2)와 (3)에 있는 것을 처리하는 겁니다. "정렬"은 안했네!! 하고 기억해 두시고요..

[옵션]-[옵션]으로 들어갑니다.

(오른쪽 마우스 눌러도 "피벗테이블 옵션"이라고 나옵니다.)

바로 또 보이죠? "레이블이 있는 셀병합 및 가운데 맞춤" 하라고 하니까 체크 해 주시고,

"빈 셀 표시"에는 문제에서 요구하는 ***(별3개)를 입력해 주세요.

(4) 행의 총합계를 지우고...

     얘도 옵션에 있는 거니까 나가지 말고 바로 해버립니다.

두번째 탭 [요약 및 필터]라는 곳에 가면 행/열 총합계 표시를 선택할 수 있습니다.

문제에 맞게 설정해 주세요.

다 한게... 아닙니다..

아까 은근 슬쩍 넘어간 정렬도 마저 해야겠죠?

정렬

(3) 종류를 <출력형태>와 값이 정렬하고, ...

     옛날 버전에서는 정렬이 조금 어렵게 났는데.. 이젠 거저먹기 입니다.

정렬하라는 항목(지금은 이름이 다르지만) 옆의 삼각형을 누르면 바로 정렬하는 메뉴가 나옵니다. 

가나다.. 순이면 오름차순, 하파타... 순이면 내림차순입니다. 

설정을 해 주시죠.. 

 

틀린그림 찾기

나머지 사항은 <출력형태>에 맞게 작성하시오.

     이거 나올 줄 이제 예상하셨죠? ^^ 틀린그림 찾기..

틀린그림이 2개 있는데 찾으셨나요? 

아까 한번 나왔던 제목 2개가 일단 틀리고.. 글자 정렬 상태도 좀 틀립니다.

우선 글자부터 수정하죠. 그냥 클린한 후 입력하면 됩니다.

안쪽의 숫자들과 빈칸이어서 별3개씩 있는 데이터들을 모두 선택하고, 가운데정렬 및 쉼표스타일을 지정합니다. 

 

후아.. 두번에 나눠서 할까도 생각했는데.. 

다행히 한 강의로 다 끝났네요..

사실 제3작업에는 "피벗테이블" 뿐 아니라, "부분합" 이라는 것도 나오는데..

다음 강의에 그 부분합을 다룰까, 아니면 2작업으로 넘어갈까 고민을 좀 해야겠습니다. 

고생많으셨구요.. 다음 강의에서 다시 인사드리겠습니다.