엑셀 파워쿼리를 활용한 자료 행, 열 변환

테이블(Table), 크로스탭(Crosstab), 템플릿(Template)의 차이

엑셀에서 작성한 모든 자료는 표 라고 부를수 있다. 하지만 엑셀을 분석도구로 사용하려면 표 의 의미를 한정할 필요가 있다. 먼저 표, 테이블, Table 은 피벗테이블로 분석가능한 데이터베이스 형태의 자료라고 정의하자. 이 글에서는 테이블이라고 부르겠다. 테이블 자료를 활용해서 피벗테이블 등으로 분석하여 자료의 현황 등을 일목요연하게 정리하면 크로스탭, Crosstab 이 된다. 이 크로스탭이 보고서 등의 워드 자료로 직접 쓰이는 경우가 많기 때문에 엑셀을 처음 접하고 자료를 만들 떄 대부분 이 크로스 탭을 만든다. 크로스탭을 더 보기 좋게 정리하여 매출 전표 같은 복잡한 이쁜 워드 결과물로 만든 문서가 템플릿, Tempalte 이다.

이 차이를 잘 모르고 자료입력을 크로스탭, 심지어 템플릿 형태로 하는 경우가 많다. 이렇게 하면 이쁘긴 한데 진짜 자료를 분석하려면 이걸 다시 테이블로 만들어야 하는데 여간 귀찮지가 않다. 애초에 자료를 잘 입력하는 게 좋다. 하지만 현실은 그렇지 않다. 내가 근무하는 직장도 크로스탭 형태로 만들어진 자료가 무척 많다. 이 자료들을 가지고 뭔가를 분석하고 생각을 정리하려면 수정, 변환이 필요하다. 그 때마다 내가 했던 작업들을 위해 필요한 엑셀 기능들을 정리한다.

분석용 테이블(Table) 만들기

빈 셀만 선택 Ctrl - G

이런 크로스탭이 있고 이 자료를 영원히 이런 형태로만 출력해서 본다면 문제가 없지만 행과 열을 바꾸거나 다른 할 일이 생기면 복사, 삽입, 붙이기 로 처리하기에는 너무 많은 노동이 필요하다. 처음부터 피벗테이블 사용이 가능한 표(이게 결국은 데이터베이스 자료다)로 자료를 입력해 두어야 후일이 편해지지만 이왕 만들어진 자료니 이걸 활용해서 엑셀용 테이블을 작성한다. 제목 셀은 한 행만 사용하고 열 하나에 모든 데이터가 들어가도록 구성해야 한다. 먼저 자료 분석이 안되는 병합된 셀을 제거해야 한다.

셀 병합을 해제하면 아래와 같이 되는데, 1행에 있는 '년도'는 지운다. '어종'과 '지역'은 2행으로 내리고 1행은 삭제한다. 1행의 아무 셀이나 지정해서 Shift - Space 를 치면 행 전체가 선택된다. Ctrl - (숫자패드에 있는) - 를 치면 삭제된다. 이 상태에서 비어있는 셀들의 자료를 모두 채워주어야 한다. 일일이 복사해서 붙이기에는 너무 귀찮다. 표 내부의 아무 셀이나 지정한 뒤에 표 전체를 선택, Ctrl - A 한다.

홈 탭 - 찾기 및 선택 - 이동 옵션 - 빈 셀 로 찾아가거나 Ctrl - G 를 누르고 옵션 - 빈 셀 을 선택해도 된다. 그러면 표 내부의 비어있는 셀들만 선택된다.

빈 셀에 같은 자료 복사 Ctrl - Enter

이렇게 비어있는 셀을 지정하고 첫번째 셀에 하나의 값을 입력한 후에 Ctrl - Enter 를 치면 나머지 셀에 동일한 값이 복사된다. 그림처럼 A3 셀에 A2 셀 값을 복사한 후에 Ctrl - Enter 를 누르면 멸치, 고등어 등 비어있던 셀들에 값들이 채워진다. 이제 한 고비는 넘었다.

파워쿼리를 활용한 자료 변환

파워쿼리는 엑셀에서 사용할 데이터를 불러오는 아주 강력한 도구다. 웹, DB, 텍스트 화일 등 다양한 자료를 자기 입 맛대로 불러와서 사용할 수 있다. 윈도우용의 경우 2007 버전까지는 없는 기능이고 2010에서 2013 버전은 따로 설치해야하는 추가 기능이고 2016부터는 기본으로 설치되어 있다. 맥은 2021 버전부터 설치되어 있다. 마이크로소프트와 애플이 절친이 아닌게 확실하다.

엑셀 표 등록

지금까지 만든 테이블을 엑셀에서 사용하도록 하려면 표로 등록해야 한다. 테이블을 선택 Ctrl - A 해서 홈 탭 - 표 서식 에서 마음에 드는 표를 하나 고른다. 그 후 나타나는 팝업 창에서 (이상 없겠지만) 표의 범위가 맞는지 확인하고 머리글 포함 을 선택한다. 대략 아래와 같이 된다.

파워쿼리로 편집

여기서 사용할 기능은 고급(?) 기능이 아니라 단순히 크로스탭을 목록으로 바꾸는 일이다. 먼저 파워쿼리 편집기를 실행하기 위해서 자료를 불러와야 하는데 데이터 - 가져오기 및 변환 - 테이블에서 를 선택하면 파워쿼리 편집기 창이 열린다. 이건 2016 그림이고 버전마다 메뉴 위치 등이 약간 다르긴 하지만 여기까지 읽었으면 그 정도는 쉽게 찾을 수 있을거라 믿는다.

편집기 창에서 연도 자료를 따로 열로 정리해야하기 때문에 Shift 를 누른 상태에서 마우스로 2017 - 2021년 열을 선택한다. 그 후 변환 탭 - 열 - 열 피벗 해제 를 누른다.

그러면 변환된 결과가 나오는데 그 상태에서 파일 탭 - 닫기 및 로드 를 누르면 새 워크시트에 결과물이 나온다. 끝. 이 자료를 활용해서 분석을 하면 된다. 물론 자료 저장은 꼭 해야한다.