[합입엑셀_수식편]데이터 가공을 위한 전처리하기(Feat. 파워쿼리)
- 데이터 전처리의 필요성 알기
- 엑셀 파워쿼리의 이해와 사용
데이터 전처리, 가공이라하면 다소 멀게 느껴질 수 있지만, 우리는 일상업무에서도 굉장히 많은 데이터를 분석하고 있습니다. 만족도조사 집계, 각종 회의나 계약 건수 등 데이터를 여러 기준으로 변형하여 집계할 경우가 많은데 실제로 우리가 맨처음 접할 때의 데이터는 전처리 되지 않은 비정형 데이터인 경우가 많고 더군다나 자료가 방대할 수록 데이터를 다루기가 어려워집니다.
이때 엑셀을 빠르고 편리하게 데이터베이스 형식으로 목록화할 수 있는 방법을 알아봅시다.
파워쿼리란
파워쿼리의 ‘쿼리’는 특정한 조건을 만족하는 데이터만 추출하여 새로운 표(Table)를 생성하는 것으로, 엑셀의 고급 필터와 비슷합니다. 비정형 데이터를 가공하기 쉽게 정형화된 데이터로 전처리하는 도구이며, 파워 쿼리를 이용하면 pc의 데이터는 물론이고 웹 등 다양한 소스에서부터 데이터를 가져와 변형할 수 있습니다.
대신 가공할 수 있도록 처리된 데이터들은 우리가 흔히 보는 예쁘게 만들어진 표는 아닙니다. 각종 보고에 사용되는 깔끔한 표와 통계자료를 만들기 위한 베이스라고 보시면 될 것 같습니다.
파워 쿼리의 개념 이해가 어렵다면, 이렇게 생각해보세요.
우리가 데이터를 가공할 때 ETL자동화라는 도구를 사용하는데 대표적으로 파이썬이나 SQL 등 프로그래밍 언어가 있으나 엑셀의 파워 쿼리를 사용하는 방법이 있습니다.
ETL자동화에서 ETL 이란,
E: Extraction: 추출T: Transformation: 변환L: Loading: 적재입니다. 데이터를 가공할 수 있도록 추출하고, 변환(가공)을 하고, 변형된 데이터를 특정 시스템에 적재(적용)하는 것입니다. 즉 파워 쿼리는 위의 ETL과정을 수행하는 하나의 도구입니다.
파워 쿼리의 활용
자, 여기 최초의 데이터가 있습니다.
A, B열은 병합되어 있고 월별 협의회 건수가 있는 곳도 없는 곳도 있습니다.
오늘은 파워 쿼리를 이용하여 표 목록을 변환하고 다음 시간에 변환한 표를 이용하여 보고용 집계표를 만들어보도록 하겠습니다.
우선 A~E1,2를 범위지정하여 셀병합 해제를 해줍니다.
그리고 1행의 데이터를 2행에 복붙하고 1행을 날려버립니다.
1행을 전체 지정하여 [Ctrl]+[-]로 편리하게 행 제거를 할 수 있습니다.
아무 셀이나 선택한 뒤 [홈]-[스타일]그룹-[표서식]
표범위는 지정되는 대로 하시면 되지만, 혹시나 그렇게 안 될 경우 전체표를 박스지정해주면 됩니다.
표가 만들어지면 또 다시 아무 셀이나 지정하고
[데이터]-[데이터 가져오기 및 변환]-[테이블/범위에서] 선택합니다.
그럼 이렇게 파워 쿼리 창이 띄워집니다.
우선 [분야]와 [구분]은 기존에 병합된 셀이었기 때문에 파워 쿼리를 실행시켰을 때 빈칸이 많이 뜹니다.
[분야]열을 클릭 후 Shift를 누른 상태에서 [구분]열을 클릭합니다.
지정된 열 머리글에 마우스 우클릭, [채우기]-[아래로]를 클릭합니다. [빈셀에 데이터 채우기]
데이터가 이렇게 채워지는 것을 볼 수 있습니다.
이번엔 [집계 시작일]머릿글에 아이콘을 클릭해 [날짜]로 바꿔줍니다. [열 형식 변경]
그럼 이제 열 피벗을 해제하여 월별 협의회 횟수를 행 목록으로 변환해보겠습니다.
1월 머리글 클릭-Shift 누른 상태에서 6월 머리글 클릭합니다.
그리고 마우스 우클릭 후 [열 피벗 해제] 선택합니다.
행 목록이 만들어지면서 기존 28개였던 행이 87개로 늘어났습니다.
머릿글 제목도 손보고, 필요없는 열은 제거도 하면서 데이터를 다듬습니다.
데이터 편집이 완료되었습니다.
편집 완료된 데이터는 다시 워크시트로 내보내야 합니다.
[홈]-[닫기]그룹-[닫기 및 로드]를 선택합니다.
새로운 시트가 생기면서 편집된 데이터가 워크시트로 들어왔습니다.
로드된 쿼리는 표 형식으로 되어 있는데, 이때 셀 참조를 하면 일반 셀주소인 'A1', 'B1'의 형식으로 사용하지 못하므로 일반범위로 변환해줍니다.
[표디자인]-[도구]그룹-[범위로 변환]을 선택-'계속하면 시트에서 쿼리 정의가 영구히 제거되고 표가 정상 범위로 변환됩니다. 계속하시겠습니까?'메세지에 [확인] 선택합니다.
파워 쿼리를 이용한 데이터 편집이 완료되었습니다.
조금 어렵고 생소한 기능이지만,
엑셀로 데이터 가공을 하기 위해 꼭 익혀야만 하는 도구입니다.
그럼 다음 시간엔 이렇게 변환된 데이터를 어떻게 활용하여 보고용 집계를 완성하는지 알아보겠습니다.
감사합니다.