(엑셀) 피벗테이블 사용법 - 고급편

지난번에 설명드렸던 피벗테이블에서 추가적으로 더 설명드릴 부분을 고급편이라는 이름으로 포스팅합니다.

datawithnosense.tistory.com/17

(엑셀) 피벗테이블 사용법 - 기본편

 

엑셀 피벗테이블(PIVOT TABLE) 사용법 - 기본편

엑셀 함수의 경우 제가 지난번까지 설명드렸던 IF, SUMIF, COUNTIF, VLOOKUP 등 외에...

blog.naver.com

이번 시간에 추가 설명드릴 기능은 크게 『필터』, 『값필드 요약(합계 이외 평균 구해보기)』, 『그룹화』 3가지 입니다.

언제나처럼 예를들어 설명드릴테니 잘 따라와주세요. 먼저 필터에 대해서 설명드리겠습니다. 피벗테이블에서 필터는 항목이 많아 행과 열에 넣어도 보기가 복잡할 경우 원하는 대상만 간추려서 보기 위해 필요한 기능입니다. 아래보시는 것 처럼 일자별 건어물 제품 판매 현황이 있습니다. 제가 보고 싶은 건 일자별 제품 판매현황입니다. 추가적으로 『대구상회』의 정보만 보고 싶습니다. 

아래와 같이 테이블을 Ctrl+A를 이용해 선택해서 피벗테이블을 만들어줍니다.

아래처럼 저는 행에 판매일자를 넣었고, 열에 제품명을 넣었습니다. 그리고 값에 판매가격을 넣었습니다. 그리고 거래처를 필터에 넣었습니다. 그러면 아래와 같이 피벗테이블이 생성됩니다.

이 때 『대구상회』만의 정보를 보기 위해서 필터(B1)의 아래세모(▼)를 선택하면 아래처럼 거래처의 모든 리스트가 보여집니다. 여기서 대구상회만 클릭하고 싶지만 클릭이 안됩니다. 

그래서 아래와 같이 『여러 항목 선택』을 체크해주신 뒤 부산상회, 서울상회를 체크박스에서 해제해주시면 대구상회만 남게 됩니다. 이제 확인버튼을 눌러주세요. (만약 리스트가 많을 경우에는 (모두) 앞의 체크박스를 해제해주시면 모든 체크박스가 해제되기 때문에 그 상태에서 선택하고 싶은 대상만 선택해주시면 됩니다.)

거래처가 대구상회인 제품들의 판매정보가 아래와 같이 나타났습니다.

이제 다시 처음으로 돌아가서 『값 필드 요약』에 대해서 설명드리겠습니다. 필터에 거래처를 『모두』로 선택해주세요. 『값 필드 요약』은 피벗테이블에서 Data의 『합계』뿐만아니라 『평균』, 『최소값』, 『최대값』 등을 구할 수 있게 해주는 기능입니다. 예를들어 설명드릴게요. 여기서 구하고 싶은 건, 제품별 판매가격의 평균입니다. 사실 제품별 판매가격의 평균은 결국 제품 한 개당 판매가격인데, 그냥 한번 구해봅시다. ^^;; 

아래와 같이 『피벗 테이블 필드』의 『값』 부분에 『판매가격』 아래에 『판매일자』를 넣습니다. 『판매일자』의 경우 속성이 『합계』가 아닌 『개수』로 정의되는데 그러면 그날 판매된 제품 개수랑 동일한 값을 가지게 됩니다. 『판매일자』를 넣는 이유는 안넣어도 되는데 설명을 위해서 넣었습니다. ^^;; 일단 따라와 보세요. 

『판매일자』 옆에 이제 우리가 원하는 판매가격의 평균을 위치 시킬 겁니다. 『피벗 테이블 필드』의 『값』 부분에 『판매일자』 아래에 『판매가격』을 또 넣습니다. (판매가격은 처음에 위치시킨 거랑 지금꺼랑 해서 총 2개가 들어갔습니다.)

이제 이상태에서 아래처럼 『피벗 테이블 필드』에서 마지막으로 추가한 『판매가격』을 클릭해주면 여러가지 메뉴가 뜨고 『값 필드 설정』을 클릭하면 『값 필드 설정』 화면이 뜹니다. 이 상태에서 『값 요약 기준』을 『평균』으로 변경한 뒤 확인버튼을 눌러주세요.

그러면 아래처럼 『판매일자』 오른쪽에 『판매가격』은 앞에 『합계』가 아닌 『평균』으로 바뀐 것을 확인하실 수 있습니다. 실제로 계산(판매가격 합계 / 판매일자 개수)해보면 평균이 정상적으로 출력된 것임을 확인할 수 있습니다. (이렇게 확인하려고 일부러 『판매일자』를 넣었습니다.)

이제 그룹에 대해서 알아보겠습니다. 아마도 이 기능을 사용할 일이 많을 듯 합니다. 그룹은 말그대로 어떤 특정 항목을 원하는대로 그룹화시킬 수 있는 기능인데요. 예를들어 설명드리겠습니다. 아래처럼 결과가 있는데 『생선류』와 『오징어류』로 그룹을 지어 구분해서 정보를 보고 싶다고 합시다. 일단 다시멸치, 아귀포, 쥐포는 생선이니 생선류고, 반건조오징어는 오징어니 『오징어류』 입니다. 여기서 『생선류』 그룹 지정을 위해 다시멸치, 아귀포, 쥐포를 Ctrl키를 누른채로 클릭해서 선택해 줍니다.

그리고 마우스 오른쪽키를 누르면 메뉴가 뜨는데, 여기서 『그룹』을 선택해주시면 됩니다. (여기선 그룹지으려는 대상이 텍스트이기 때문에 아래처럼 나오나 숫자일 경우에는 단위를 지정해서 그룹을 지을 수 있습니다. 예를들어 1부터 20가지 숫자가 있다면 1~5, 6~10, 11~15, 16~20 이렇게 5단위로 구분해서 그룹지정이 가능합니다.) 

그러면 아래처럼 다시멸치, 아귀포, 쥐포가 『그룹1』로 지정되었음을 알 수 있습니다. 나머지는 자동으로 『반건조오징어』라는 그룹으로 지정되었습니다. 이때 그룹이름은 F2키를 이용해 변경이 가능합니다.

『그룹1』은 『생선류』로 변경해줍니다.

그리고 『반건조오징어』는 『오징어류』로 변경해줍니다. 그러면 원하는대로 그룹별로 정보를 알 수 있지만 뭔가 부족합니다. 바로 그룹별 합계정보가 없기 때문인데요. 그건 추가해주면됩니다. 

메인메뉴의 『디자인』에 들어가서 『부분합』을 선택한 뒤 그룹 상단에 부분합을 표시할지 하단에 표시할지 선택하면 됩니다. 저는 개인적으로 상단이 보기 편해 『그룹 상단에 모든 부분합 표시』를 선택했습니다. 

그러면 아래와 같이 그룹별로 부분합이 상단에 표시된 걸 확인하실 수 있습니다.

여기서 좀 더 눈에 보이 좋게 하려면 지난 포스트에서 알려드린 『보고서 레이아웃』을 조정하면 됩니다. 저는 개인적으로 『테이블 형식으로 표시』가 편해서 아래와 같이 바꿔줍니다.

어떤가요? 아래가 좀더 보기 낫지 않나요? ^^;;

마지막으로 피벗테이블에서 항목순서 변경하는 방법에 대해서 알려드리겠습니다. 보고서를 작성하다보면 항목 순서를 바꾸고 싶은데 몰라서 파워포인트로 옮긴 다음에 표에서 행추가하고 지우고를 반복해서 순서를 조정하곤 하는데요. 피벗테이블 자체에서 간단하게 항목 순서 조정이 가능합니다. 아래처럼 예를들어 아귀포를 제일위로 올리고 싶다면 아귀포셀(B5)를 선택하시고, 마우스 왼쪽키를 누른상태에서 드래그로 3행과 4행 사이로 올리시면 됩니다.

바뀌었죠? 이건 계속 연습 좀 하시면 쉽게 적응되실 겁니다.

이제까지 피벗테이블 나름 고급편에 대한 설명을 마쳤습니다.

강의자료를 첨부하였으니 참고하시고 이해가 안가는 부분은 댓글 남겨주시기 바랍니다.

 

감사합니다.

 

170424_노센스의_엑셀강의_피벗테이블-고급편.xlsx

0.04MB