엑셀 함수보다 100배 중요한 데이터 관리 규칙 4가지
엑셀 데이터 관리 규칙 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 데이터 관리 4가지 핵심 규칙예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
규칙1. 세로방향 블록쌓기 꼭 지키기
엑셀에서 데이터를 다룰 때, 실무자가 가장 많이 하는 실수는 데이터의 '쌓는 방향'을 지키지 않는 것입니다.
- 예를 들어, 예제파일의 [별다방매출] 시트에 있는 매출 현황 표를 살펴보겠습니다. 아래 그림에서 새로운 날짜(예: 1월 4일) 데이터는 오른쪽 방향으로 쌓입니다.
매출 현황 표에서 새로운 데이터는 오른쪽 방향으로 쌓입니다. - 그리고 같은 방식으로 1월 5일, 1월 6일, .., 1월 30일까지 데이터가 누적되면, 그림과 같이 한 화면에 담기지 않을 정도로 표가 가로로 길어지는 문제가 생깁니다.
데이터가 가로로 누적되면 관리에 치명적인 문제가 발생합니다. - 이러한 경우, 아래와 같이 표를 여러 개로 나눠서 관리하는 방법도 있지만 이는 근본적인 해결책이 아닙니다.
표를 여러개로 나눌 수도 있으나, 데이터 취합이 어려운 문제가 있습니다. - 실무에서 발생하는 잘못된 데이터 구조의 대부분은 '쌓는 방향'만 바꿔도 손쉽게 해결할 수 있습니다. 예제파일의 [별다방DB] 시트에는 매출현황 표와 동일한 데이터가 '세로 방향 블록 쌓기' 규칙이 지켜진 형태로 작성되어 있습니다.
DB 시트에는 세로방향 블록쌓기가 지켜진 올바른 데이터가 있습니다. - 이렇게 세로 방향 블록쌓기 규칙을 지켜서 데이터를 관리하면, 1월 4일, 1월 5일, ... 로 데이터가 아무리 누적되어도 세로로만 쌓여 데이터를 편리하게 관리할 수 있습니다.
세로방향 블록쌓기가 지켜진 데이터는 자료가 아무리 누적돼도 편리하게 관리할 수 있습니다.
- 예를 들어, 예제파일의 [별다방매출] 시트에 있는 매출 현황 표를 살펴보겠습니다. 아래 그림에서 새로운 날짜(예: 1월 4일) 데이터는 오른쪽 방향으로 쌓입니다.
규칙2. 머리글은 한 줄로 관리하기
올바른 데이터라면 반드시 지켜야 할 '절대 규칙'이 있습니다. 바로 머리글은 "한 줄"로 관리하는 것 입니다. 더존이나 SAP 등 ERP 프로그램에서 데이터를 받으면, 머리글이 한 줄로 작성된 것을 확인할 수 있습니다. 원본 데이터의 머리글을 왜 한 줄로 관리해야 하는지, 그 중요성을 알아보겠습니다.
- 예제파일에서 잘못된 데이터인 [별다방매출], [김밥천국매출], [출퇴근표] 를 살펴보면 모두 머리글이 2줄이나 3줄로 작성된 것을 볼 수 있습니다.
잘못된 데이터의 대부분은 여러 줄로 작성된 머리글에서 시작됩니다. - 반면 올바른 데이터인 [별다방DB], [김밥천국매출_수정], [출퇴근표_수정]의 데이터는 모두 머리글이 1줄로 올바르게 작성되어 있습니다.
올바른 데이터는 머리글이 한 줄로 작성됩니다. - 이 차이는 어디에서 발생할까요? [별다방매출] 시트에서 한 번 살펴보겠습니다. 시트에서 1행을 우클릭 한 후, [숨기기]로 행을 숨깁니다.
별다방매출 시트에서 1행을 숨깁니다. - 그러면 머리글이 한 줄로 변하지만, "커피-커피.. ", "티/스무디-티/스무디, ..." 등 동일 항목이 중복으로 표시되어 어느 날짜의 매출인지 확인할 수 없게 됩니다.
각 카테고리별 매출이 어느 날짜의 값인지 확인할 수 없습니다. - 그렇다보니, 어쩔 수 없이 머리글을 여러 줄로 나눠서 어느 날짜의 매출인지 구분을 해야하고, 이러한 이유로 머리글이 두 줄, 세 줄로 쌓이게 됩니다. 이는 다른 데이터에서도 동일하게 적용됩니다.
중복된 필드를 구분하기 위해, 머리글이 여러 줄로 쌓이게 됩니다. - 따라서, "한 줄"로 된 머리글로 데이터를 올바르게 관리하려면 첫번째 규칙인 '세로방향 블록쌓기'를 반드시 지켜서 관리하는 것이 중요합니다.
세로방향 블록쌓기 규칙을 지키면, 한 줄 머리글로 데이터를 올바르게 관리할 수 있습니다.
규칙3. 원본 데이터에 셀 병합 금지
엑셀로 보고서를 작성해보셨다면, '셀 병합' 기능이 얼마나 편리한지 아실 겁니다. 하지만 단언컨대, 원본 데이터에서 만큼은 '셀 병합'을 절대 사용해선 안 됩니다.
- 예제파일의 [별다방매출] 시트에서 구분으로 "R, S, K" 에는 셀 병합이 적용되어 있습니다. 구분이 입력된 A2:A8 범위를 선택한 후, [홈] 탭 - [병합하고 가운데 맞춤]을 클릭해 셀 병합을 해제합니다.
별다방매출 시트에서 구분에 적용된 셀 병합을 해제합니다. - 그러면 겉으로 보기에는 모든 범위에 값이 입력된 것 같았지만, 실제로는 첫번째 셀에만 데이터가 입력되고 나머지 범위는 빈 칸으로 처리된 것을 확인할 수 있습니다.
셀 병합된 범위는 첫번째 셀에만 데이터가 남고, 나머지는 빈 칸으로 처리됩니다. - 따라서, 원본 데이터에 셀 병합을 사용하면 빈 칸으로 인해 데이터가 누락되는 문제가 발생하므로 원본 데이터에서 만큼은 셀 병합을 절대로 사용하지 않는 것을 꼭 기억합니다. 만약 셀 병합이 꼭 필요한 상황이라면, 아래 영상에서 소개해드린 트릭으로 데이터가 모두 채워진 셀 병합 서식을 만들 수 있습니다. 자세한 내용은 아래 영상 강의를 참고하세요!👇
규칙4. 집계 값은 원본에서 제외하기
엑셀로 표 형식의 보고서를 작성하다 보면, 보고서의 가독성을 위해 중간에 '소계'를 넣거나 표 마지막에 '합계'를 넣어서 관리하는 경우가 많습니다. 하지만 이 친절한 집계 값이 데이터를 분석하는 과정에서는 치명적인 오류를 유발할 수 있습니다.
- 예제파일의 [잘못된데이터] 시트에 있는 표를 참조해 피벗테이블을 생성해보겠습니다. 표가 작성된 A1:L9 범위를 선택한 후, [삽입] 탭 - [피벗테이블]을 클릭합니다. 새 창이 실행되면 '기존 워크시트'를 선택한 후, 출력할 위치로 비어있는 셀을 선택하고 [확인] 버튼을 클릭해 피벗테이블을 추가합니다.
잘못된 데이터 시트에서 표를 선택한 후, 피벗테이블을 생성합니다. - 피벗테이블이 생성되면, 분류를 행으로, 합계를 값으로 이동합니다. 그러면 각 항목별 합계가 집계된 피벗테이블 보고서가 완성됩니다.
분류별 합계를 집계하도록 피벗테이블 필드를 이동합니다. - 하지만 집계된 보고서의 합계를 살펴보니, 원본 데이터에 있는 "9,171,700" 의 두 배로 계산된 것을 확인할 수 있습니다. 그 원인은 원본 데이터에서 이미 합계로 계산된 결과를 다시 집계해서 데이터가 2배로 뻥튀기 된 것 입니다. 따라서, 엑셀의 원본 데이터는 반드시 날 것(Raw Data) 그 자체로 관리하는 것이 중요합니다. 만약 "합계, 평균" 등 집계된 값이 있다면, 원본에서 분리하거나 제외하는 것을 꼭 주의합니다.
원본에 집계값이 포함된 경우 피벗테이블의 합계가 n배로 집계됩니다.
피벗 ↔ 피벗 해제 차이 이해하기
자, 이제 실무자가 꼭 알아야 할 엑셀의 데이터 관리 4가지 핵심 규칙을 모두 살펴봤습니다. 하지만, 이런 의문이 드실겁니다.
"규칙은 이제 잘 알겠는데... 이렇게 엉망진창인 데이터 (또는 가로로 길게 늘어선 데이터)를 언제 다 세로로 바꾸죠?"
이 질문에 대한 해답이 바로 "피벗 해제(UnPivot)"입니다.
- 예제파일에서 [올바른데이터] 시트로 이동한 후, 표를 선택하고 [삽입] 탭 - [피벗테이블] 을 클릭해 기존 워크시트 비어있는 곳에 피벗테이블을 생성합니다.
올바른 데이터 시트에서 피벗테이블을 생성합니다. - 피벗테이블 필드 목록에서 분류를 행으로, 승인일을 열로, 금액을 값으로 이동해 피벗테이블을 완성합니다.
행에는 분류, 열에는 승인일, 값에는 금액을 이동합니다. - 그러면 놀라운 일이 벌어집니다. 우리가 이전에 봤던 '잘못된 데이터'의 정확하게 동일한 표가 피벗테이블로 생성된 것을 볼 수 있습니다. 즉, 평소에 잘못된 구조로 관리하던 보고서는 사실 '피벗테이블'로 만들었어야 하는 표이고, 그 뒤에는 세로방향 블록쌓기가 지켜진 올바른 구조의 원본 데이터가 있다는 것입니다.
평소 잘못된 구조로 관리하던 데이터는, 사실 피벗테이블로 만들었어야 하는 보고서입니다. - 따라서, 피벗테이블 형식의 잘못된 보고서를 세로방향 블록쌓기가 지켜진 올바른 구조의 데이터로 변경하려면 "피벗(Pivot)"의 반대인 "피벗 해제(UnPivot)"을 적용하면 됩니다. 이 피벗해제 기능을 사용하는 방법 및 파워쿼리 기능의 자세한 설명은 아래 파워쿼리 3시간 무료 챌린지에서 알기 쉽게 정리했으니, 아래 링크를 확인하세요!👇
