엑셀의 혁명 파워쿼리(2) – 열 피벗 해제(Unpivot)로 데이터 구조 변경

(Mode 2에서 작성 예정)

보기 좋은 표가 엑셀에게는 ‘최악의 데이터’인 이유

안녕하세요. 여러분의 데이터 가공 시간을 획기적으로 줄여드리는 엑셀 공장장 직장인 엑셀 꿀팁 저장소입니다.

지난 시간에는 폴더 내의 파일 100개를 한 번에 취합하는 파워쿼리의 마법을 경험했습니다. 그런데 취합은 잘 했는데, 막상 그 데이터를 가지고 피벗 테이블을 만들려고 보니 뭔가 이상하지 않던가요?

우리가 보고서용으로 흔히 만드는 표는 이런 식입니다.

• 가로축(열): 1월, 2월, 3월, 4월…
• 세로축(행): 제품A, 제품B, 제품C…

사람이 보기에는 아주 직관적이고 예쁜 표입니다. 하지만 컴퓨터(엑셀)에게는 최악의 구조입니다. 왜냐하면 ‘월’이라는 하나의 정보가 여러 열(Column)에 흩어져 있기 때문입니다. 이 상태로는 피벗 테이블도, 차트도 제대로 만들 수 없습니다.

오늘은 이 ‘보기 좋은 떡(보고서용 표)’을 ‘먹기 좋은 떡(분석용 데이터)’으로 바꿔주는 파워쿼리의 필살기, 열 피벗 해제(Unpivot)에 대해 알아봅니다.

Unpivot(열 피벗 해제)이란? 가로 본능을 세로 본능으로

용어가 조금 어렵게 느껴질 수 있습니다. 쉽게 설명해 드리겠습니다.

• 피벗(Pivot): 세로로 길게 쌓인 데이터를 가로/세로 표 형태로 요약하는 것. (분석의 최종 단계)
• 피벗 해제(Unpivot): 가로로 넓게 퍼진 데이터를 다시 세로로 길게 쌓는 것. (분석의 준비 단계)

즉, 우리가 엑셀 기능을 제대로 쓰려면 데이터는 무조건 세로로 길어야 합니다.
[제품명, 1월, 2월, 3월…] 처럼 옆으로 늘어지는 구조를 -> [제품명, 월, 판매량] 딱 3개의 열로 압축해서 위에서 아래로 길게 쌓는 작업, 그것이 바로 Unpivot입니다.

준비물: 월별 실적이 가로로 나열된 ‘나쁜 표’

실습을 위해 가장 흔하게 볼 수 있는 ‘나쁜 표’를 준비했습니다. (실무에서는 나쁜 표가 아니라 ‘보고서 양식’이라고 부르죠.)

A열에는 제품명이 있고, B열부터 1월, 2월, 3월… 순으로 매출액이 적혀있는 표를 상상해 보세요. 이 표를 엑셀 시트에 입력하고 아무 셀이나 클릭한 상태에서 시작합니다.

실전 1단계: 표를 파워쿼리 편집기로 납치하기

데이터를 수술대(파워쿼리) 위에 올려야 합니다.

1. 데이터 범위 내의 셀을 하나 클릭합니다.
2. 상단 메뉴 [데이터] 탭에서 [테이블/범위에서]를 클릭합니다.
3. ‘머리글 포함’에 체크가 된 상태로 확인을 누릅니다.

잠시 후 엑셀 창이 회색으로 바뀌며 [파워쿼리 편집기]가 실행됩니다. 이제 수술 준비 완료입니다.

데이터 구조화 이론(Tidy Data)이 궁금하다면 MS 공식 열 피벗 해제 가이드를 참고하세요.

실전 2단계: 고정할 열 잡고 ‘다른 열 피벗 해제’ 클릭

여기서부터가 핵심입니다. 버튼을 누르는 방법은 3가지가 있지만, 저는 가장 안전하고 강력한 방법 하나만 알려드리겠습니다. 바로 [다른 열 피벗 해제] 기능입니다.

1. 고정할 기준 열(Key Column)을 선택합니다. 우리의 예제에서는 ‘제품명’ 열이 되겠죠.
   
   (이 열은 Unpivot을 해도 그대로 유지되어야 하니까요.)
2. 선택된 ‘제품명’ 열 제목 위에서 마우스 우클릭을 합니다.
3. 메뉴 중간에 있는 [다른 열 피벗 해제(Unpivot Other Columns)]를 클릭합니다.

⚠️ 주의: 그냥 [열 피벗 해제]를 누르면 안 되나요?
안 되는 건 아니지만 비추천합니다. [다른 열 피벗 해제]를 써야 나중에 4월, 5월, 6월 데이터가 옆으로 추가되어도 파워쿼리가 “아, 제품명 빼고 나머지는 싹 다 돌려버리면 되는구나?”라고 똑똑하게 인식해서 자동으로 처리해 줍니다.

실전 3단계: 이름 바꾸고 엑셀로 로드하기

버튼을 누르는 순간 마법이 일어납니다. 옆으로 길게 퍼져있던 1월, 2월, 3월 열들이 싹 사라지고, 단 2개의 열로 변신했을 겁니다.

• 특성(Attribute): 여기에 ‘1월’, ‘2월’ 같은 머리글이 들어옵니다.
• 값(Value): 여기에 실제 매출액 숫자가 들어옵니다.

이제 마무리를 해봅시다.

1. ‘특성’ 열을 더블 클릭해서 ‘월’로 이름을 바꿉니다.
2. ‘값’ 열을 더블 클릭해서 ‘매출액’으로 이름을 바꿉니다.
3. 좌측 상단 [닫기 및 로드]를 누릅니다.

엑셀 시트에 [제품명, 월, 매출액] 딱 3개의 열로 깔끔하게 정리된 세로형 테이블(Database format)이 생성되었습니다.

왜 이렇게 바꿔야 하죠? (피벗 테이블과 차트의 자유)

이제 여러분은 자유를 얻었습니다. 이렇게 세로로 변환된 표를 클릭하고 [삽입] – [피벗 테이블]을 눌러보세요.

• ‘월’을 필터에 넣을 수도 있고,
• ‘월’을 행으로 내릴 수도 있고,
• ‘제품명’ 별로 슬라이서를 걸 수도 있습니다.

원본이 가로로 되어있을 때는 꿈도 못 꾸던 자유로운 분석이 가능해집니다. 이것이 바로 데이터 구조 변경(Normalization)의 힘입니다.

고수의 팁: 4월 데이터가 추가되면 어떻게 되나요?

이 기능의 백미는 ‘자동화’입니다.
원본 표(가로형)에 ‘4월’ 열을 추가하고 데이터를 입력했다고 칩시다.

1. 파워쿼리로 만든 결과 표(세로형) 위에서 마우스 우클릭합니다.
2. [새로 고침]을 누릅니다.

놀랍게도 파워쿼리가 늘어난 ‘4월’ 열을 자동으로 감지해서, 세로 데이터의 맨 아래에 4월 데이터를 착착 쌓아줍니다. 우리가 아까 [다른 열 피벗 해제] 기능을 썼기 때문입니다. “제품명 말고 나머지는 다 돌려!”라는 명령이 4월에도 적용된 것이죠.

마무리하며

오늘은 엑셀 데이터 분석의 가장 큰 걸림돌인 ‘크로스탭(가로형) 데이터’를 파워쿼리의 Unpivot 기능으로 완벽하게 해결하는 법을 배웠습니다. 이 기능 하나만 알아도 여러분이 다룰 수 있는 데이터의 범위가 확연히 달라집니다.

다음 시간에는 데이터를 다뤘으니 이제 비즈니스 의사결정을 할 차례입니다. “목표 매출 1억을 달성하려면 몇 개를 더 팔아야 할까?”를 자동으로 계산해 주는 목표값 찾기와 시나리오 관리자 편으로 찾아오겠습니다. 여러분의 칼퇴를 꿀팁 저장소가 응원합니다!