엑셀 피벗테이블 사용법 ERP 수주 매출현황 모델별 판매현황을 빠르게 추출

엑셀 피벗테이블을 활용하면 고객별 매출현황, 쇼핑몰 사이트별 주문현황, 제품 모델별 판매현황, 월간, 주간, 연간 단위 출고현황 등을 빠르게 계산할 수 있습니다.

 

ERP를 사용하는 기업이나 온라인 쇼핑몰로 사업을 영위하는 업체들이라면 출고 이력이나 판매 이력 또는 매출현황을 엑셀 형식으로 된 상세한 원시데이터를 추출할 수 있습니다. 그러한 원시데이터는 특정 모델이 22년 한 해 동안 판매된 수량을 확인한다던가, 월별 매출을 계산한다던가 하는 식의 자료를 파악할 수 있게 도와줍니다. 그같은 내용은 원시데이터를 단순히 다운받기만 해도 확인할 수 있지만, 특정 고객이 특정 모델을 특정 기간동안 몇 개 구매하였는지, 그 구매액이 얼마인지 등의 자료를 선택적으로 한 눈에 알아볼 수는 없습니다. 특히 ERP나 SCM의 원시 데이터의 항목과 크기가 방대할 경우 수작업으로 데이터를 수정, 가공하는 것을 사실상 불가능에 가까워집니다. 

 

다행스럽게도 마이크로소프트는 엑셀 피벗테이블이라는 기능을 제공하여 ERP나 쇼핑몰 SCM 에서 추출한 원시 데이터를 활용하여 원하는 자료를 다양한 형태로 제공하는 기능입니다. 

 

엑셀 피벗테이블 메뉴 확인하기 

그러면 엑셀 피벗테이블은 어떻게 실행하는지 알아보도록 하겠습니다. 먼저 ERP에서 추출한 예시용 엑셀 원시 데이터를 살펴보겠습니다. A부터 J열까지 10개의 항목이 있고, 출고일자, 고객명, 모델명, 출고수량, 외화단가 및 외화금액 등으로 이루어져 있습니다.

엑셀 원시데이터

 

 

피벗테이블 메뉴는 엑셀 1행과 A열 사이에 있는 삼각형 모양의 셀을 클릭하여 전체 행과 열을 선택합니다. 이어서 상단의 삽입 메뉴를 선택하면 바로 아래에 나타나는 피벗테이블 메뉴를 클릭합니다.

피벗테이블 들어가기

아래와 같은 피벗테이블 만들기 창이 뜨면 바로 확인을 클릭해 줍니다.

피벗 테이블 만들기

새로운 시트 Sheet3에 아래와 같은 피벗테이블이 생성되었습니다.

피벗테이블 생성

 

 

고객별 구매모델과 매출액의 합산을 피벗테이블로 빠르게 계산하기

이제 예시 원시데이터에 피벗테이블 기능을 활용하여, 각 고객이 어떤 모델을 구매하였고, 구매한 모델의 외화금액 합산액을 자동으로 추출하는 표를 엑셀 피벗테이블로 빠르게 완성해 보도록 하겠습니다.

 

먼저 우측 보고서에 추가할 필드 선택란에서 고객, 모델, 외화금액을 체크합니다.

보고서에 추가할 필드 선택

그 다음 오른쪽 아래 행 레이블 창에 뜬 체크항목 중 모델 항목을 열 레이블 필드로 드래그 앤 드롭을 하고, 외화금액 항목을 값 필드로 끌어 놓습니다. 값 필드로 외화금액 항목을 옮긴 다음 클릭을 해주면 값 필드 설정(N)이라는 팝업메뉴가 나타나는데요, 그것을 클릭해 줍니다.

값 필드 설정

아래와 같은 값 필드 설정 창에서 합계를 선택한 뒤 확인을 눌러줍니다. 엑셀 2016인 경우에는 이미 합계로 설정되어 있을 수도 있습니다. 

값 필드를 합계로 변경

설정이 다 끝났다면 아래와 같이 업체별로 출고된 모델과 합계액이 알아보기 쉽도록 표 형태로 나타나게 됩니다.

피벗테이블로 원하는 형태로 데이터 추출 완료

피벗테이블

 

 

피벗테이블을 활용하여 ERP 원시 데이터를 가공하는 예시 영상

 

이상으로 엑셀 피벗테이블 메뉴를 활용하여 ERP나 SCM에서 다운로드한 원시데이터를 통해 고객별로 출고된 제품과 매출액을 빠르게 요약하는 방법을 간단하게 알아보았습니다.