엑셀 데이터 관리 규칙, 정말 중요합니다! - 5분 핵심 요약

실습 가이드

실무에서 마주치는 엑셀 문제의 대부분은 함수나 기능의 한계가 아니라 잘못된 데이터 구조에서 비롯됩니다. 따라서 엑셀을 능숙하게 다루기 위해서는 함수와 기능을 익히는 것보다 올바른 데이터 구조를 먼저 이해하는 것이 더 중요합니다.

실무에서 반드시 지켜야 할 올바른 데이터 구조의 핵심 규칙은 다음 3가지입니다.

위 3가지 규칙과 더불어, 가장 우선적으로 지켜야 할 원칙은 '세로방향 블록쌓기'입니다. 세로방향 블록쌓기 규칙에 대한 자세한 설명은 아래 영상 강의를 참고해주세요.

올바른 데이터 구조를 이해하는 것과 함께, 본인이 다루는 원본 데이터의 관리 수준을 정확히 파악하는 것 또한 매우 중요합니다. 예제와 함께 살펴보겠습니다.

예를 들어 '주 단위'로 관리되는 매출 데이터는 원본 데이터로 적합할까요? 상황에 따라 다르겠지만, 주 단위 데이터는 원본 데이터로 보기에는 부족한 경우가 많습니다. 이미 집계된 형태의 데이터이기 때문입니다.

집계된 데이터에서는 일자별, 고객별 데이터를 검색하려면 더 하위 단계의 원본 데이터가 필요합니다. 따라서 위 데이터의 올바른 원본은 아래와 같이 시간대별, 고객별로 구매 항목이 정리된 데이터여야 합니다.

데이터 관리 수준을 이해하지 못하면 어떤 문제가 생길까요? 평소 '주 단위' 데이터를 다루는 상황에서 보고서 작성을 위해 하위 단계 데이터가 필요해질 경우, 관리 수준을 이해하지 못하면 IT팀이나 상위 부서에 정확한 요청을 하지 못하고, 주어진 데이터마저 제대로 활용하지 못하는 문제가 발생합니다.

이번 강의에서는 입고 내역을 가로 방향으로 관리한 잘못된 재고 관리 데이터를 세로방향 블록쌓기 규칙에 맞는 올바른 데이터 구조로 변환하는 방법을 알아보겠습니다. 파워쿼리의 기초 개념과 기본 기능은 아래 영상 강의를 참고해주세요.

만약 파워쿼리를 지원하지 않는 버전(엑셀 2013 이전, 엑셀 2016 Professional Plus 등)을 사용하는 경우에는, 엑셀 함수를 활용한 데이터 정규화 강의를 참고해주세요.

파워쿼리 열 피벗해제 기초

1. 범위를 표로 변환하기 : 예제파일을 실행한 뒤 [실습] 시트로 이동합니다. 데이터가 입력된 A1:O7 범위를 선택한 후, [삽입] 탭 - [표] 를 클릭하거나 단축키 Ctrl + T 를 눌러 표 만들기를 실행합니다. 표 만들기 대화상자가 나타나면 '머리글 포함'을 체크한 뒤 [확인] 버튼을 눌러 범위를 표로 변환합니다.
2. 파워쿼리 실행하기 : 변환된 표를 선택한 후 [데이터] 탭으로 이동합니다. 리본 메뉴 왼쪽의 '데이터 가져오기 및 변환' 그룹에서 [테이블 및 범위에서] 또는 [시트에서] 버튼을 클릭하면 파워쿼리 편집기가 실행됩니다.
   오빠두Tip : 데이터 탭의 레이아웃은 사용 중인 엑셀 버전에 따라 다소 차이가 있을 수 있습니다. 버튼 위치는 조금씩 다를 수 있으나, [테이블 및 범위에서] 버튼을 찾아 클릭하면 파워쿼리 편집기를 실행할 수 있습니다.
3. 불필요한 데이터 제거하기 : 제품 목록에 포함된 '합계' 항목은 집계된 데이터이므로 분석 대상에서 제외해야 합니다. 제품 필터를 클릭한 후, '합계' 항목의 체크를 해제하고 [확인] 버튼을 클릭합니다.
4. 데이터 정규화, 열 피벗 해제 : 제품 열을 선택한 후, 파워쿼리 편집기의 [변환] 탭으로 이동합니다. [열 피벗 해제] 버튼의 확장(▼) 아이콘을 클릭한 뒤 [다른 열 피벗 해제] 를 선택하면 제품 열을 제외한 나머지 필드가 피벗 해제되며 데이터가 세로 방향으로 정규화됩니다.
5. 머리글 변경 및 마무리 : 머리글을 더블클릭하거나 F2 키를 눌러 각 머리글을 "날짜", "수량"으로 변경합니다. 또한 데이터 형식이 '문자' 와 '정수'가 아닌 경우, 각 열의 데이터 형식을 문자와 숫자로 변경합니다.
   오빠두Tip : 엑셀 2019 이전 버전을 사용하는 경우, 머리글 좌측에 데이터 형식 아이콘이 표시되지 않습니다. 이때는 열을 선택한 뒤 [홈] 탭에서 [데이터 형식] 을 확인합니다.
6. 파워쿼리 편집기에서 [파일] 탭 - [닫기 및 다음으로 로드] 를 선택한 후, 기존 '실습' 시트의 A10셀 위치로 쿼리 결과를 출력합니다.
7. 세로 방향 구조에 맞춰 올바른 원본 데이터로의 정규화가 완료되었습니다.

실시간 분석 대시보드 만들기

이제 정규화된 데이터를 활용해 피벗테이블과 슬라이서로 실시간 매출 분석 보고서를 작성해보겠습니다.

1. 피벗테이블 만들기 : 쿼리로 출력된 표를 선택한 후, [삽입] 탭 - [피벗테이블] 을 클릭합니다. 이후 실습 시트의 E10셀 위치에 피벗테이블을 생성합니다.
2. 피벗테이블 필드 영역에서 제품을 행 영역으로, 수량을 값 영역으로 이동합니다.
3. 슬라이서 추가하기 : 피벗테이블을 선택하면 상단에 [피벗테이블 분석] 탭이 나타납니다. [피벗테이블 분석] 탭 - [슬라이서 삽입] 을 클릭한 뒤, '날짜' 슬라이서를 추가합니다.
4. 추가된 슬라이서에서 날짜를 선택하면 해당 날짜의 입고 내역이 실시간으로 필터링되어 표시됩니다.
   오빠두Tip : 이 기능을 응용하면 다양한 형태의 엑셀 대시보드를 구성할 수 있습니다. 대시보드 제작에 대한 자세한 설명은 아래 영상 강의를 참고해주세요.