엑셀 피벗 테이블로 보기 좋은 레폿 자료 만들기

엑셀에는 다양한 기능들이 많이 있습니다. 

그중에서도 가장 간편하면서도 가성비가 좋은 기능이 있다면 피벗 테이블 입니다.

 

광범위한 RAW DATA 속에서 원하는 값으로 묶어서 합산이 가능하고

한눈에 보기 좋게 표현해 낼 수 있기 때문에 레폿 자료에 자주 등장 합니다.

 

간단한 예시를 통해서 피벗 테이블을 활용하는 방법을 알아 보고자 합니다.

 

 

데이터 준비:
피벗 테이블을 생성하기 전에 분석하고자 하는 데이터를 엑셀 시트에 준비합니다. 데이터는 열(컬럼)과 행으로 구성된 테이블 형태여야 합니다.

예시로 준비한 테이블은 판매 실적 테이블 입니다.

어떤 상품이 어떤 사이즈로 얼만큼 팔렸는지

어떤 상품이 어느 지역으로 얼만큼 팔렸는지

어떤 색상이 더 많이 팔렸는지

어떤 담당자가 상품을 많이 팔았는지 등 다양하게 분석을 해 볼 수 있습니다.

 

데이터 선택:
데이터가 포함된 범위를 선택한 다음, '삽입(Insert)' 탭에서 '피벗 테이블(PivotTable)'을 클릭합니다.

혹은, 제목줄이 아닌 아래 항목에 커서가 위치 하게 한 다음 '삽입'에서 '피벗 테이블'을 클릭해 줘도 동일한 작업을 확인 할 수있습니다.

피벗 테이블 대화 상자:
피벗 테이블을 생성하기 위해 '피벗 테이블 만들기(PivotTable Create)' 대화 상자가 나타납니다.

테이블 범위가 맞는지 다시 선택을 할 수 있으며, 만약 맨 아래 부분에 합계 등이 나와 있다면 그 부분을 제외하고 범위를 선택해 주어야 합니다.

 

피벗 테이블의 위치를 설정해 줄 수 있는데

'새 워크시트'를 선택하면 새로운 SHEET에 생성이 되고 

'기존 워크시트'를 선택하면 해당 테이블이 있는 곳 중에서 원하는 곳을 클릭해서 생성하게 됩니다.

보통 새 워크시트에 자료를 생성하여 원본이 편집 되지 않도록 하는 것을 추천합니다.

 

데이터 범위 지정:
대화 상자에서는 이미 선택한 데이터 범위가 표시됩니다. 데이터 범위가 올바른지 확인하고, 데이터 범위가 맞지 않는 경우에는 수동으로 범위를 지정할 수 있습니다.

피벗 테이블 필드 선택:
대화 상자의 하단에는 '테이블 분류란에서 데이터 범위 선택' 옵션이 있습니다. 이 옵션을 선택한 후, 피벗 테이블 필드 목록이 나타납니다.

'확인'을 누르니 새 워크시트에 아래와 같은 화면이 생겼습니다.

가장 윗 부분을 보면 '피벗 테이블 분석'이라는 탭이 지정이 되었고,

오른쪽 윗부분에 활성화 된 '필드목록'이라는 것이 생성되면서 오른쪽에 '피벗 테이블 필드'가 생겼습니다.

이곳에서 원하는 값을 행으로, 혹은 값으로 보내서 편집을 할 수 있습니다.

행, 열, 값 추가:
피벗 테이블 필드 목록에서 원하는 데이터를 '행(행 레이블)', '열(열 레이블)', '값(요약할 데이터)' 영역에 끌어다 놓습니다. 이렇게 하면 데이터가 해당 영역에 표시됩니다.

값 설정:
'값' 영역에 데이터를 끌어다 놓으면 기본적으로 데이터가 합계로 요약됩니다. 필요한 경우, 요약 함수를 변경하거나 다른 함수를 적용하여 데이터를 요약할 수 있습니다(예: 평균, 최대값, 최소값 등).

행 부분에 상품명을 놓고 

열 부분에는 사이즈를 놓고

값 부분에 수량을 넣어 주었더니 위와 같이 상품별, 어떤 사이즈가 몇개나 판매가 되었는지 분석이 가능하게 되었습니다.

 

상품명과 납품가(값)을 설정해 주었더니, 상품별 얼만큼 판매가 이뤄졌는지 합산이 되었습니다.

그럼 거래처별로 판매 현황을 한번 살펴 보겠습니다.

 

행에 거래처를 입력하고 납품가를 값에 넣었습니다.

거래처 별로 판매가격 합산이 되었습니다.

납품가 부분에서 마우스 우클릭을 한 뒤 '정렬'을 '내림차순'으로 설정 해 주었더니

높은 금액순으로 정렬이 되었습니다.

 

일등상사가 41만원, 신라유통이 33만원, 삼등상사 26만원, 고려무역2만원 순으로 거래 되었네요.

총 102만원이 판매 되었습니다.

숫자는 마우스로 지정한 뒤 'Shift+Ctrl+1'을 눌러 주면 세자리 수마다 콤마(,)가 생성되어 보기 좋습니다.

서식 설정:
생성된 피벗 테이블을 선택한 후 '피벗 테이블 도구(PivotTable Tools)' 탭에서 '서식(Format)' 옵션을 사용하여 테이블의 서식을 설정할 수 있습니다.

위와 같이 생성된 피벗 테이블을 바로 상단의 '피벗 테이블 분석 ->도구 에서 '피벗 차트''를 클릭해서 원하는 차트로 만들어 표현이 가능합니다. 

이 거래처 판매실적 같은 경우에는 원형보다는 막대형이 표현하기 좋을 것 같습니다.

상기와 같이 거래처별 매출 현황이라는 테이블을 바로 맨들어 내었습니다.

보고자료에 붙여 넣기만 하면 될 것 같습니다.

 

피벗 테이블 분석:
생성된 피벗 테이블을 사용하여 데이터를 분석하고 요약합니다. 필요한 경우, 행과 열을 드래그 앤 드롭하여 데이터의 다양한 관점을 분석할 수 있습니다.

업데이트와 필터링:
피벗 테이블 내용이 변경되거나 업데이트되어야 할 경우, 원본 데이터를 업데이트한 후 피벗 테이블을 업데이트하려면 '데이터(Datas)' 탭에서 '모든 업데이트 업데이트(Refresh All)'를 선택합니다. 또한, 필요한 경우 필터를 적용하여 원하는 데이터만 볼 수 있습니다.

즉, 만약 상기의 고려무역 같은 경우에 2만원이 아니라 20만원이데 데이터가 잘못 들어간 경우라면

원본 테이블로 돌아가 데이터를 수정 한 뒤에 비벗 테이블이 있는 곳으로 돌아와 '새로고침'을 눌러 주면 비벗테이블에 반영이 되겠습니다.

 

이렇게 하면 기본적인 엑셀 피벗 테이블을 설정하고 사용하는 방법을 이해하실 수 있을 것입니다. 피벗 테이블은 데이터 분석을 효율적으로 수행하는 데 매우 유용한 도구입니다.

다양한 분석이 가능한 비벗테이블로 이해하기 쉬운 발표 자료 만들어 보시기 바랍니다.