[ 엑셀 Tip ] 실전 피벗테이블 만들기_( 1 )

실제 데이터를 활용해서 피벗테이블을 만들어 보겠습니다.

데이터 출처는 아래와 같습니다. 따라 해 보실 분들은 아래 사이트에서 데이터 다운로드 하시면 될 것 같습니다.

사용할 데이터는 배달음식 서비스 소비 트렌드입니다.

아래의 데이터는 19 ~ 22.9월까지의 데이터가 있어서 COVID19 전후의 데이터 분석이 가능할 것 같습니다.

datus

---

 

우선 데이터를 살펴보겠습니다. prtym.....음... 무엇을 의미하는 걸까요?

데이터를 보면 그래도 대충 이해를 할 수 있을 것 같습니다. 

첫 번째는 년월, 두 번째는 배달, 세 번째는 지역, 네 번째는 성별, 다섯 번째는 연령, 여섯 번째는.... 정답을 보러 가야겠습니다. 

데이터 상품 내 데이터 정보를 보면 해당 컬럼이 정확히 무엇을 의미하는지 알려 줍니다.

 

컬럼명을 알아보기 쉽게 변경을 해 주었습니다.

데이터 분석을 위해 표를 활용하는 것이 유용하기 때문에 테이블을 표로 만들어 주겠습니다.

[ 엑셀 Tip ] 표만들기 및 표 기능 활용 방법

 

 

표를 만들어 주면 데이터 확장이 용이하기 때문에 주료 사용합니다. 계속 보시다 보면 왜 맨날 다이나믹이니 이런 이야기를 했는지 아실 수 있으실 겁니다.

우선 피벗테이블을 만들어 주겠습니다.

[ 엑셀 Tip ] 피벗테이블 활용_( 1 )( feat. 표기능 )

---

연월별 결제 금액

행에 년월을 넣고 값에 결제금액을 넣었더니 년월별 합계 금액이 만들어졌습니다.

숫자의 가독성이 떨어지니 ctrl+ shift + 1을 눌러서 콤마를 넣어 주겠습니다.

 

처음 경험하는 큰 숫자에 끝에서부터 일, 십, 백, 천, 만.......천만, 억, 십억, 백억 시간이 너무 걸려서 백만단위로 변경해 주겠습니다.

 

사용자 지정 서식에 콤마를 두 개를 붙이자 보이시나요? 천 원 단위에서 백만 단위로 변경되는 것을 볼 수 있습니다.

 

이제 숫자의 가독성이 한결 좋아졌습니다. 그런데 숫자만 있다 보니 월별 비교가 한눈에 들어오지 않습니다.

시계열 분석을 위한 피벗 차트를 삽입하여 라인그래프를 넣어 보도록 하겠습니다.

 

 

22년 6월 최저를 찍었다가 다시 올라간 후 9월에는 최저 금액을 찍었습니다. 왜 그럴까요?

---

연령대별 사용금액

배달음식을 가장 많이 사용하는 연령은 누구일까요?

 

년월 대신 연령을 집어넣으니 40대에서 가장 많이 비용을 지출하는 것을 볼 수 있습니다.

 

 

범례의 성별을 추가해주니 남성보다는 여성의 사용 금액이 더 많은 것을 알 수 있습니다. 왜 그럴까요?

---

지역별 사용금액

축에 지역을 넣고 결제금액 우클릭 후 정렬 내림차순으로 정렬해 주었더니 다음과 같이 사용금액이 높은 곳이 나왔습니다.

경기 ▶ 서울 ▶ 부산 ▶ 경남 ▶ 인천

아마도 인구가 많은 지역에서 금액이 많이 발생하는 것 같다는 예상이 됩니다.

 

 

지역별 연령대별 히트맵을 만들어 상관관계를 보니 서울, 경기 30 ~ 40대 사용 금액이 높은 것을 볼 수 있습니다.

[ 엑셀 Tip ] 피벗테이블을 이용한 히트맵 만들기 ( feat. 조건부 서식 )

---

배달 건당 매출 

피벗테이블에서 건당 매출을 구하기 위해 계산 필드를 삽입해 줍니다.

 

건당금액은 단위가 작기 때문에 ctrl + shift + 1을 눌러 원단위로 만들어 줍니다.

 

 

건당금액 검증을 위해서 수식을 넣어 줬더니 맞습니다. 사용해도 될 것 같습니다.

건당 매출은 30대와 60대 이상에서 건당 매출이 높게 나타나는 것을 알 수 있습니다. 결제 금액은 크지 않지만 객단가가 높음을 알 수 있습니다.

반응형

지역별로 보니 결제 금액과 다르게 경기, 제주, 강원, 인천, 서울 순으로 나오고 있습니다.

제주, 강원은 아마도 관광상권이다 보니 소비가 크게 발생되는 것으로 예상이 됩니다.

연령별 건당 매출을 비교하려고 하니 금액차이가 적어서 축을 변경해 보도록 하겠습니다.

 

 

최소값을 20000으로 변경하자 아래와 같이 그래프가 바뀌었습니다.

 

 

다른 연령보다 30대에서 건당 매출이 7 ~ 9월에 급격하게 증가하는 것을 볼 수 있습니다.

거리 두기가 해제되면서 휴가를 떠나면서 금액을 사용해서 그런 걸까요?

지금 데이터는 한정적이지만 그래도 나름의 다양한 분석이 가능할 것 같습니다.

[ 엑셀 Tip ] 실전 피벗테이블 만들기_( 2 )