엑셀 함수보다 100배 중요한 데이터 관리 규칙 4가지 | 직장인 필수!

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.

규칙1. 세로방향 블록쌓기 꼭 지키기

엑셀에서 데이터를 다룰 때 실무자가 가장 자주 범하는 실수는 데이터를 쌓는 방향을 일관되게 지키지 않는 것입니다. 데이터의 누적 방향만 바로 잡아도 표가 무한정 길어지거나 분석이 어려워지는 문제 대부분을 예방할 수 있습니다.

1. 예제파일의 [별다방매출] 시트에 있는 매출 현황 표를 살펴보겠습니다. 아래 그림에서 새로운 날짜(예: 1월 4일) 데이터는 오른쪽 방향으로 쌓이는 것을 확인할 수 있습니다.

   

2. 같은 방식으로 1월 5일, 1월 6일, ..., 1월 30일까지 데이터가 누적되면, 아래 그림과 같이 한 화면에 담을 수 없을 만큼 표가 가로로 길어지는 문제가 발생합니다.

   

3. 이러한 경우 표를 여러 개로 나눠서 관리하는 방법도 있지만, 데이터가 분산되어 관리만 더 복잡해질 뿐 근본적인 해결책은 아닙니다.

   

4. 실무에서 발생하는 잘못된 데이터 구조의 대부분은 '쌓는 방향'만 바꿔도 손쉽게 해결됩니다. 예제파일의 [별다방DB] 시트에는 매출현황 표와 동일한 데이터가 '세로 방향 블록 쌓기' 규칙에 맞춰 작성되어 있습니다.

   

5. 이렇게 세로 방향 블록쌓기 규칙을 지키면, 1월 4일, 1월 5일과 같이 데이터가 아무리 누적되어도 세로 한 방향으로만 쌓여 데이터를 안정적으로 관리할 수 있습니다.

   

규칙2. 머리글은 한 줄로 관리하기

올바른 데이터라면 반드시 지켜야 할 절대 규칙이 있습니다. 바로 머리글은 "한 줄"로 관리하는 것입니다. 더존이나 SAP 등 ERP 프로그램에서 데이터를 받아보면 머리글이 한 줄로 작성되어 있는데, 원본 데이터의 머리글을 왜 한 줄로 관리해야 하는지 그 이유를 살펴보겠습니다.

1. 예제파일에서 잘못된 데이터인 [별다방매출], [김밥천국매출], [출퇴근표] 시트를 살펴보면 모두 머리글이 2줄 또는 3줄로 작성되어 있는 것을 볼 수 있습니다.

   

2. 반면 올바른 데이터인 [별다방DB], [김밥천국매출_수정], [출퇴근표_수정] 시트의 데이터는 모두 머리글이 1줄로 정리되어 있습니다.

   

3. 이러한 차이는 어디에서 발생할까요? [별다방매출] 시트에서 직접 확인해보겠습니다. 시트의 1행을 마우스 우클릭한 후, [숨기기]를 선택해 행을 숨깁니다.

   

4. 그러면 머리글은 한 줄로 보이지만, "커피-커피", "티/스무디-티/스무디"와 같이 동일 항목이 중복으로 표시되어 어느 날짜의 매출인지 식별할 수 없게 됩니다.

   

5. 그렇다 보니 어쩔 수 없이 머리글을 여러 줄로 나눠서 어느 날짜의 매출인지 구분해야 하고, 이런 이유로 머리글이 두 줄, 세 줄로 늘어나게 됩니다. 다른 데이터에서도 동일한 원리가 적용됩니다.

   

6. 따라서 "한 줄" 머리글로 데이터를 올바르게 관리하려면 첫 번째 규칙인 '세로방향 블록쌓기'를 반드시 함께 지키는 것이 중요합니다.

   

규칙3. 원본 데이터에 셀 병합 금지

엑셀로 보고서를 작성해본 분이라면 '셀 병합' 기능이 얼마나 편리한지 잘 아실 겁니다. 하지만 단언컨대, 원본 데이터에서 만큼은 '셀 병합'을 절대로 사용해서는 안 됩니다.

1. 예제파일의 [별다방매출] 시트에서 구분 열의 "R, S, K" 항목에는 셀 병합이 적용되어 있습니다. 구분이 입력된 A2:A8 범위를 선택한 후, [홈] 탭 - [병합하고 가운데 맞춤]을 클릭해 셀 병합을 해제합니다.

   

2. 그러면 겉으로 보기에는 모든 셀에 값이 입력된 것처럼 보였지만, 실제로는 첫 번째 셀에만 데이터가 입력되어 있고 나머지 범위는 빈 칸으로 처리된 것을 확인할 수 있습니다.

   

3. 이렇게 원본 데이터에 셀 병합을 사용하면 빈 칸으로 인해 데이터가 누락되는 문제가 발생하므로, 원본 데이터에서 만큼은 셀 병합을 사용하지 않는 것을 반드시 기억해야 합니다. 만약 셀 병합이 꼭 필요한 상황이라면, 아래 영상에서 소개해드린 트릭을 활용해 데이터가 모두 채워진 셀 병합 서식을 만들 수 있습니다. 자세한 내용은 아래 강의를 참고해보세요.👇
   

규칙4. 집계 값은 원본에서 제외하기

엑셀로 표 형식의 보고서를 작성하다 보면, 가독성을 위해 중간에 '소계'를 넣거나 표 마지막에 '합계' 행을 추가하여 관리하는 경우가 많습니다. 하지만 이렇게 친절하게 넣어둔 집계 값이 데이터를 분석하는 단계에서는 치명적인 오류를 유발할 수 있습니다.

1. 예제파일의 [잘못된데이터] 시트에 있는 표를 참조해 피벗테이블을 만들어보겠습니다. 표가 작성된 A1:L9 범위를 선택한 후, [삽입] 탭 - [피벗테이블]을 클릭합니다. 새 창이 실행되면 '기존 워크시트'를 선택한 다음 출력할 위치로 비어 있는 셀을 지정하고 [확인] 버튼을 클릭해 피벗테이블을 추가합니다.

   

2. 피벗테이블이 생성되면 분류를 행으로, 합계를 값으로 이동합니다. 그러면 각 항목별 합계가 집계된 피벗테이블 보고서가 완성됩니다.

   

3. 그런데 집계된 보고서의 합계를 살펴보면 원본 데이터의 합계인 "9,171,700"의 두 배로 계산된 것을 확인할 수 있습니다. 그 원인은 원본 데이터에 이미 합계로 계산된 값이 포함된 상태에서 다시 집계가 이루어져 결과가 2배로 부풀려졌기 때문입니다. 따라서 엑셀의 원본 데이터는 반드시 가공되지 않은 상태(Raw Data) 그대로 관리하는 것이 중요하며, "합계"나 "평균" 등 집계된 값이 있다면 원본에서 분리하거나 제외해 두어야 합니다.

   

피벗 ↔ 피벗 해제 차이 이해하기

지금까지 실무자가 꼭 알아야 할 엑셀 데이터 관리 4가지 핵심 규칙을 모두 살펴봤습니다. 다만 이쯤에서 다음과 같은 의문이 드실 겁니다.

"규칙은 잘 알겠는데... 이렇게 엉망진창인 데이터(또는 가로로 길게 늘어선 데이터)를 언제 다 세로로 바꾸죠?"

이 질문에 대한 해답이 바로 "피벗 해제(UnPivot)"입니다.

1. 예제파일에서 [올바른데이터] 시트로 이동한 후, 표를 선택하고 [삽입] 탭 - [피벗테이블]을 클릭해 기존 워크시트의 빈 영역에 피벗테이블을 생성합니다.

   

2. 피벗테이블 필드 목록에서 분류를 행으로, 승인일을 열로, 금액을 값으로 이동해 피벗테이블을 완성합니다.

   

3. 그러면 흥미로운 결과가 나타납니다. 우리가 앞서 살펴봤던 '잘못된 데이터'와 정확히 동일한 형태의 표가 피벗테이블로 생성된 것을 확인할 수 있습니다. 즉, 평소에 잘못된 구조로 관리하던 보고서는 사실 '피벗테이블'로 만들어졌어야 하는 표이고, 그 뒤에는 세로방향 블록쌓기가 지켜진 올바른 구조의 원본 데이터가 존재한다는 뜻입니다.

   

4. 따라서 피벗테이블 형태의 잘못된 보고서를 세로방향 블록쌓기가 지켜진 올바른 구조의 데이터로 변경하려면, "피벗(Pivot)"의 반대인 "피벗 해제(UnPivot)"를 적용하면 됩니다. 피벗 해제 기능 사용법과 파워쿼리 활용법은 아래 파워쿼리 3시간 무료 챌린지에서 자세히 정리해 두었으니, 아래 링크에서 확인해보세요.👇