[파워쿼리 6강] 엑셀 질문지를 예쁘게 만들어보자 _ 엑셀 자동화(피벗)

회사 동기의 아이디어로 제작하였습니다.

 

[상황] 협력사에서 질문 문항에 대한 데이터를 엑셀로 제공했는데, 특이하게도 언피벗된 형태로 주었다. 

테이블은 다음과 같다. 

※ 사람 이름은 랜덤하게 뽑아주는 사이트에서 임의로 추출하였습니다. 

 

협력사에서 준 데이터 테이블 형태

질문에 대해서 각 사람별로 데이터를 취합해서 준 것이다.

취합할 때 예쁘게 하여 전달주면 가장 좋겠지만 일이라는 게 마음처럼 잘 풀리진 않는다. 

 

그래서 우리는 이 데이터를 각 사람당 질문 1 2 3 이런 형태로 보고싶다. 

예를 들면 이런 형태이다. 

우리가 보고 싶은 테이블 형태

협력사에서 준 테이블은 각 사람 당 질문 1, 2, 3이 있어서 한 눈에 보기가 불편했던 반면, 이런 형태로 주면 각 사람당 질문에 대한 답을 한 눈에 볼 수 있게 된다.

 

엇.. 뭔가 쉬운데 피벗테이블로 할 수 있을 것 같은데라는 생각이 스친다. 

그러나 피벗테이블로 할 경우,

텍스트 데이터는 보이지 않게 된다. 

그렇다. 피벗테이블은 숫자로만 보여주었던 것이다..! (사실 여기서도 DAX함수를 사용하여 보여줄 수 있을 것 같으나 오늘 배울 파워쿼리가 훨씬 쉽게 해결할 수 있다) 

 

01 표 만들기

파워쿼리의 시작은 '표'만들기 이다.

데이터가 있는 곳에 'Ctrl + T' 를 누른 후 머릿 글 포함을 체크해준다. 

또는, 삽입 탭의 '표' 클릭 후 '머릿 글 포함' 체크 해준다. 

데이터를 표로 변환

---

※ (참고사항)

표 → 셀로 변경하는 방법 ( 파워쿼리 쓸 땐 SKIP하세요)

표를 만든 경우, 탭이 하나 더 생긴다. '범위로 변환' 클릭 시 원래상태로 돌아간다.  

표를 셀로 변환하는 방법(참고사항)

---

02 파워쿼리 편집기

이제 표를 만들었으니 파워쿼리 편집기로 이동한다. 

데이터 → 테이블에서를 클릭하면 된다. 

파워쿼리 편집기로 넘어가는 준비단계

 

 

그럼 아래와 같은 형태로 나타난다. 겁먹지 말자. 우리는 파워쿼리 편집기를 이제 자주 봤다.

우리는 질문에 대해서 답변을 각 열로 보고 싶은 것이다. 

빨간 색 박스를 파란 색 박스처럼 보고 싶다. 

이제 열을 어떻게 돌리는 지 알려주니 집중이 필요하다!!

03 파워쿼리 피벗열 사용

 

1번 : '질문' 열을 클릭한 상태에서 

2번 : '변환'탭을 클릭

3번 : 피벗 열 클릭

4번 : 질문에 대한 답을 함께 돌리고 싶으니 '값 열'을 답변으로 설정

5번 : 집계함수에서 집계 안 함

 

4번과 5번이 핵심인데, '질문'을 피벗 할 때 따라 움직이는 값이 '답변' 열의 값이므로 답변을 설정하여야 하며
5번에서 '집계 안 함'이라는 뜻은 텍스트 데이터를 그대로 들고 온다는 뜻이다.
피벗테이블은 '집계 안 함'이 없이 모든 데이터를 집계한 상태로 가져온다.(count, sum, average 등)
그래서 파워쿼리가 피벗테이블보다 강점이 여기에 있다. 데이터를 그대로 가져온다. 

파워쿼리 열 피벗

그럼 결과는..??

 

이제 우리가 원하는 형태로 데이터가 잘 나타난다.

엑셀로 잘 불러주기만 하면 된다. 

 

04 파워쿼리 저장

 

홈 ▶ 닫기 및 로드 ▶ 닫기 및 로드

시트에는 아래처럼 불러진다. 

디자인이 너무 예쁘지 못하다는 생각이 들면 본인 스타일에 맞게 변경이 가능하다. 

 

디자인 탭에서 '표 스타일'을 클릭하여 변경할 수 있다. 

디자인 변경

 

이제 협력사에서 이상한 형태로 데이터를 주더라도 예쁘게 변경할 수 있다. 

 

05 파워쿼리 자동화 확인

마지막으로, 파워쿼리의 강점은 '자동화'라는 점이다. 

기존 원본 테이블에서 추가로 데이터가 들어간다고 하더라도 '업데이트'만 누르면 쉽게 데이터가 업데이트 된다. 

 

전산팀 답변이 추가된 경우에도 자동으로 업데이트가 된다.

위 처럼 전산팀 직원의 답변이 추가된 경우에도 파워쿼리로 만든 곳으로 돌아가 '새로고침'만 누르면 자동으로 업데이트가 된다. 

 

자동 업데이트 !!

이제 한번만 만들어두면 협력사에서 준 이상한 데이터 형태를 클릭 한번에 예쁘게 변경할 수 있다.