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

실전 피벗테이블 분석을 이어 나가 보도록 하겠습니다.

먼저 올린 글에 사용 데이터가 있으나 읽어 보셔야 이번 이야기를 이해하실 수 있습니다.

앞에서 코로나 전후의 트렌드를 볼 수 있다고 이야기 드렸는데 22년도 데이터만 가지고 피벗테이블을 만든 것을 볼 수 있습니다.

19 ~ 22.9월까지의 데이터가 있다고 말씀 드려서 데이터를 하나로 통합해서 사용해 보도록 하겠습니다.

우선 데이터 통합은 파워쿼리를 이용해서 진행하면 편하게 진행할 수 있는데 이번에는  표를 활용한 통합을 해 보도록 하겠습니다.

파워쿼리를 이용한 데이터 합치기를 알고 싶으시면 아래 글을 참고해 주시면 됩니다.

[Power BI] 파워쿼리를 이용한 파일 합치기

---

 

년월을 추가 할 수 있도록 데이터를 추가해 보도록 하겠습니다. 표를 사용해서 데이터를 합치는 방법입니다.

 

 

여기 다운 받은 데이터가 있는데 22년 표 아래 추가해 보도록 하겠습니다.

 

21년도 데이터를 추가하기 위해서 데이터를 복사한 후에 22년도 데이터 아래 붙여 넣어 줍니다.

 

 

22년 데이터 맨 아래 21년 데이터를 붙여 넣어 주면 아래와 같이 자동으로 색이 변한 것을 볼 수 있습니다.

 

 

피벗테이블로 돌아와서 새로 고침을 눌러주면 자동으로 데이터가 업데이트가 된 것을 볼 수 있습니다.

이제 19, 20년까지 업데이터를 해 보도록 하겠습니다.

 

간단히 붙여넣으면 데이터가 업데이트가 된 것을 볼 수 있습니다. 

이제 4년간의 데이터를 보면서 배달 트렌드를 보도록 하겠습니다.

---

 

데이터를 보니 년월이 시계열로 되어 있지 않아 정렬을 진행해 보도록 하겠습니다.

피벗테이블 우클릭 ▶ 정렬 ▶ 숫자 오름차순 정렬 후 피벗차트를 삽입해 보도록 하겠습니다.

 

COVID19 발생 한 시점이 기억나신다면 이 그래프가 무엇을 의미하는지 잘 알 수 있으실 겁니다.

근데 축 금액을 알 수가 없으니 10억 단위로 변경해 보도록 하겠습니다.

 

19. 2 ~ 3월에 코로나가 시작되었는데 배달음식의 트렌드도 그와 함께 증가한 것을 볼 수 있습니다.

22년 정점을 찍고 어느 수준에 도달했다고 생각이 들게 만드는 그래프입니다.

---

계절에 따른 매출의 상관관계

계절에 따른 매출의 상관관계를 만들어 보도록 하겠습니다.

원래 중첩 IF문을 사용해야 하나 배열을 이용한 살짝 고급 스킬을 사용해 보도록 하겠습니다.

 

H 컬럼에 month를 입력하자 컬럼의 색이 변경된 것을 볼 수 있습니다.

right 함수를 사용하여 월을 구해 보도록 하겠습니다.

prtym의 규칙을 보시면 2022(년) 01(월) 인데 합쳐져 있는 구조입니다. 그럼 오른쪽에서 2개를 가져오면 월이 만들어질 것 같습니다.

[ 엑셀 Tip ] left, mid, right 함수를 이용한 날짜 형식 변경 ( yyyymmdd → yyyy-mm-dd )

 

month에 아래와 같은 수식을 입력하자 자동으로 식이 채워지는 것을 확인할 수 있습니다.

 

 

피벗테이블에서 새로고침을 해주자 month가 생성이 되었습니다.

 

 

행 레이블에 month를 넣어주니 1 ~ 12월까지의 값이 생성이 되고 그래프가 변경이 되었습니다.

금액이 증가하는 구역은 7 ~ 9월인데 이때는 여름으로 생각됩니다. 아마 피서나 더우니까 시켜 먹다 보니 증가한 것 같습니다.

그럼 계절을 구해 보도록 하겠습니다.

3 ~ 5월	6 ~ 8월	9 ~ 11월	12 ~ 2월
봄	여름	가을	겨울

 

우측과 같은 별도의 테이블을 만들어 vlookup을 사용해서 계절을 구해야 하나

[엑셀] 나 좀 한다 말하고 싶을 때 ...vlookup

 

선택된 부분에서 F9 단축키를 눌러주면 배열이 만들어지게 됩니다. 이때 엔터를 바로 치면 다음과 같이 수식이 변경이 되는데

이제 별도의 테이블이 없어도 계절을 가져오는 것이 가능합니다.

 

피벗테이블에서 새로고침을 하자 season 컬럼이 생성되는 것을 확인하고 행에 season을 넣어주니 그래프가 만들어졌습니다.

 

 

그럼 해마다 여름이 배달음식이 금액이 가장 높은지 알아보겠습니다.

 

left 함수를 사용해서 year을 추가해 준 뒤 피벗테이블에서 새로고침을 하고 다음과 같이 레이아웃을 해 줍니다.

조건부 서식을 이용해 19년도의 서식을 설정 후 서식 복사를 해서 20 ~ 22년도를 선택해 줍니다.

 

22년도는 9월까지 있어서 가을은 판단하기 어렵지만 가을에 금액이 높다가 21년부터는 여름에 증가하는 것을 볼 수 있습니다.

히트맵을 잘 사용하지 않지만 상관관계를 보기 위한 히트맵 사용은 매우 유용합니다.