올바른 데이터 구조를 이해하는 것과 더불어, 본인의 업무에서 핸들링 할 수 있는 원본 데이터의 관리 수준을 이해하는 것도 매우 중요한데요. 예제와 함께 알아보겠습니다.
만약 '주 단위'로 관리하는 매출 데이터는 원본 데이터가 맞을까요? (상황에 따라 다르겠지만) 주 단위 데이터는 올바른 원본데이터라고 하기에는 많이 부족합니다. 가장 큰 이유는 바로, '집계된 데이터'이기 때문인데요.
주 단위로 집계된 데이터는 그 하위 단계 세부 정보를 확인할 수 없습니다.
집계된 데이터이기 때문에, 세부 단계인 일자별, 고객별 데이터를 검색하려면 더 하위 단계의 데이터가 필요합니다. 따라서 위 데이터의 올바른 원본 데이터는 아래와 같이 각 시간대별, 고객이 구매한 항목이 정리된 데이터라고 할 수 있습니다.
고객, 구매시간, 제품명 등이 정리된 데이터가 올바른 원본데이터 입니다.
데이터 관리 수준을 이해하지 못하면 어떤 문제가 발생할까요? 만약 평소 '주 단위' 데이터를 다루고있고, 특정 보고서 작성을 위해 하위 단계의 데이터가 필요할 경우, 데이터 관리 수준을 이해하지 못한다면 IT팀이나 상위부서에 올바른 요청을 하지 못하고 주어진 데이터 조차 제대로 활용하지 못하는 문제가 발생합니다.
이번 강의에서는 입고 내역을 가로로 관리하는 잘못된 재고 관리 데이터를 세로방향 블록쌓기가 지켜진 올바른 데이터 구조로 변경하는 방법에 대해 알아보겠습니다. 파워쿼리 기초 및 기본 기능에 대한 자세한 설명은 아래 영상 강의를 참고해주세요.
만약 파워쿼리를 지원하지 않는 일부 버전 (엑셀 2013 이전, 엑셀 2016 Professional Plus 등..)일 경우, 엑셀 함수를 사용하는 데이터 정규화 관련 강의를 참고해주세요.
범위를 표로 변환하기: 예제파일 실행 후, [실습] 시트로 이동합니다. 이후 데이터가 입력된 A1:O7 범위를 선택 후, [삽입] 탭 - [표] 를 클릭하거나, 단축키 Ctrl + T 를 눌러 표 만들기를 실행합니다. 표 만들기 대화상자가 나오면 '머리글 포함'을 체크 한 후 [확인] 버튼을 눌러 범위가 표로 변환됩니다.
범위를 표로 변환합니다.
파워쿼리 실행하기: 표를 선택 후, [데이터] 탭으로 이동합니다. 리본메뉴 왼쪽 '데이터 가져오기 및 변환' 그룹에서 [테이블 및 범위에서] 또는 [시트에서] 버튼을 클릭하면 파워쿼리 편집기가 실행됩니다.
표를 선택 후, 데이터 - 테이블/범위에서(또는 시트에서)를 클릭합니다.
오빠두Tip : 데이터 탭의 레이아웃은 사용 중인 엑셀 버전에 따라 조금씩 다를 수 있습니다. 버튼 위치는 조금씩 다르나, [테이블 및 범위에서] 버튼을 찾아 클릭하면 파워쿼리 편집기를 실행할 수 있습니다.
불필요한 데이터 제거하기: 제품 목록 중 '합계'는 집계된 데이터입니다. 따라서 제품 필터를 클릭 후, '합계'를 목록에서 제거한 뒤 [확인] 버튼을 클릭합니다.
제품 열에서 합계를 제거합니다.
데이터 정규화, 열 피벗해제: 제품 열을 선택 후, 파워쿼리 편집기에서 [변환] 탭 - [열 피벗 해제]에서 확장(▼) 버튼을 클릭합니다. 그리고 [다른 열 피벗 해제] 버튼을 클릭하면 제품열을 제외한 나머지 필드가 피벗 해제 되면서 데이터가 정규화됩니다.
제품 열을 선택 - 편집 - 다른 열 피벗 해제를 클릭합니다.
머리글 변경 및 마무리: 머리글을 더블클릭 하거나 F2키를 눌러 머리글을 각각 "날짜, 수량"으로 변경합니다. 또한 데이터 타입이 '문자' 와 '정수'가 아닐 경우, 각각 데이터 형식을 문자와 숫자로 변경합니다.
머리글과 데이터 형식을 수정합니다.
오빠두Tip : 엑셀 2019 이전 버전 사용자는 머리글 좌측으로 데이터 형식이 표시되지 않으므로, 열을 선택 - [홈] 탭에서 [데이터 형식]을 확인합니다.
파워쿼리 편집기에서 [파일] 탭 - [닫기 및 다음으로 로드]를 선택한 후, 기존 '실습'시트의 A10셀 위로 쿼리를 출력합니다.
