정약용에게 배우는 '올바른 엑셀 데이터 활용법'
모든 엑셀 기능보다 중요한 데이터의 기본 원리와 활용 방법
이 글은 '된다! 최반장의 실무 엑셀 with 피벗 테이블'의 일부 내용으로 'IT출판 온라인 콘퍼런스 리드잇콘 2022 (readITcon 2022)'에 기고한 글입니다.
1789년 조선의 왕 정조는 현륭원(지금의 융릉)에 7년 동안 조경 사업으로 나무를 심었습니다. 조경 사업을 모두 마친 어느 날, 정조가 다산 정약용을 불러 말했습니다.
“이번에 심은 나무가 몇 7년 동안(1789~1795) 8읍(수원·광주·용인·과천·진위·시흥·안산·남양)에서 현륭원에 심은 나무의 장부가 수레에 실으면 소가 땀을 흘릴 정도인데, 그 공로는 누가 더 많으며, 나무의 숫자는 얼마인지 아직도 명백하지 않으니 1권이 넘지 않은 범위에서 네가 명백하게 정리하라. 몇 그루나 되는지 책 1권이 넘지 않게 명백하게 정리하라.”
현륭원에 나무를 심을 때마다 식목부에 기록이 남아 있긴 했지만 셀 수 없이 많은 공문서의 무더기일 뿐 명백한 정리는 쉽지 않은 일이었습니다.
다산은 먼저, 공문을 고을별로 분류하고 각 묶음을 날짜순으로 정렬해서 8 묶음으로 만들고 날짜별로 정렬되어 8개 고을별로 묶인 공문서를 '표'에 옮겨 적는 작업을 시작했습니다. 8장으로 이루어진 이 표는 각 장마다 상단에 고을 이름이 적혀 있고, 그 아래에는 세로 칸에 나무 이름이, 가로 칸에 연도별로 날짜를 적게 만든 빈 표였습니다. 고을별로 요약, 집계한 것이죠.
이렇게 요약, 집계된 8개의 표를 다시 1개의 표에 옮겨 적기 시작했습니다. 그 표에는 세로 칸에는 8개의 고을 이름이, 가로 칸은 연도별로 2칸씩 적혀 있었습니다. 즉, 고을 및 반기별로 나무의 수를 한 장으로 집계한 것이었고 이것은 정확히 정조가 바라는 결과물이었습니다.이것을 본 정조는 "권으로 상세하게 기록할 수 없을 것으로 알았는데, 그렇게 많은 분량의 문서를 너는 종이 한 장에다 마무리하였으니, 참으로 훌륭하다." 라고 하며 크게 기뻐했다고 합니다.
이 일화에서 다산 정약용이 어명에 따라 수행한 작업은 200년이 지난 오늘 우리가 회사에서 매일 수도 없이 마주하는 상황과 매우 비슷합니다. 하지만 엑셀은 커녕 계산기조차도 없던 시절에 처리한 것 이라고는 믿기지 않습니다. 엑셀 피벗 테이블의 작동 원리가 그대로 구현되었기 때문입니다.
최근 수 년간 사회 전반적으로 ‘데이터’를 통해 얻을 수 있는 ‘인사이트’에 대한 효과와 중요성이 크게 강조되고 있습니다. 엑셀이 ‘입사하면 일을 잘할 수 있다’는 자격을 증명하기 위한 수단을 넘어서 ‘진짜로 잘해야’ 회사와 개인 모두가 살아 남을 수 있는 시대입니다.
‘엑셀을 잘한다’라는 것은 무엇일까요? 어느 분야이든 잘한다고 하는 것은 ‘기대한 (또는 그 이상의) 성과를 만드는 상태’를 말합니다. 엑셀을 잘한다는 것은 결국 엑셀을 이용해서 만들고자 하는 성과를 정확하고 빠르게 만드는 상태를 말할 수 있겠습니다. 그러면 엑셀을 잘하기 위해서 무엇을 해야 할까요? 함수? VBA?
엑셀을 잘하기 위해서는 데이터를 데이터 답게 활용하는 방법부터 배우고 익혀야 합니다.
필자는 운영, 물류, 통관분야에서 십 수년간 일해온 실무자이면서 동시에 엑셀 강의를 하고 있는 강사이기도 합니다. 최근 ‘최반장의 실무 엑셀 with 피벗 테이블’ 이라는 도서를 집필하면서 약 4분의 1가량의 분량에 이 내용을 담았습니다. 독자 여러분들과 엑셀을 잘하는 것을 넘어서 일을 잘하기 위해서 데이터를 데이터 답게 활용하는 방법을 공유하고 싶었습니다. ‘Garage In Garage Out’이라는 표현이 있습니다. 쓰레기를 넣으면 결과물도 쓰레기일 수밖에 없습니다. 데이터 분석을 활용한 업무가 바로 이렇습니다. 좋은 품질의 데이터를 넣을 수 있어야만 좋은 품질의 결과를 기대할 수 있습니다. 엑셀의 그 어떤 고급 기능을 익히더라도 데이터 그 자체에 대한 이해가 부족하다면 성과 있는 좋은 분석을 할 수 없습니다.
다산이 어명을 받고 제일 처음 한 것은 공문 무더기를 고을별로 그룹화하고 그것을 ‘고을 별 표’로 정리한 것입니다. 즉 공문이라는 문서를 ‘고을 별 표’라는 데이터로 변환시킨 뒤에 비로소 분석을 본격적으로 시작했습니다. 회사에서 데이터 분석이 어려운 경우의 상당 수는 '문서'와 '표'가 구분 없이 진행된 업무에서 인사이트를 찾아야 하는 경우입니다.
데이터와 문서를 구분하고 목적에 맞게 분리해서 사용할 수 있어야 합니다. 이것은 엑셀의 그 어떤 함수나 기능을 익히는 것보다도 중요하고 우선되어야 합니다. 엑셀로 만들었다고 해서 모두 분석할 수 있는 데이터가 아닙니다. 분석에 적합한 표의 형식과 내용을 갖추어야 비로소 분석을 할 수 있습니다. 특히 흔히 ‘기초 데이터’, ‘마스터 데이터’ 라고 부르는 각 업무 영역에서 중추적 역할을 하는 데이터는 철저하게 잘 기획하고 관리되어야 합니다. 인사팀의 임직원 인명부, 운영팀의 거래처 목록, 영업팀의 상품 목록 등은 데이터 품질이 최대한으로 유지되어야 합니다.
그렇다면, 데이터를 데이터 답게, 분석에 적합하게 만드는 표의 구조는 어떤 것을 말할까요? 아래의 그림의 2개의 표는 동일한 내용을 담고 있습니다. 하지만 왼쪽의 표는 분석이 불가능하고 오른쪽의 표는 여러 가지 분석이 가능합니다. 독자 여러분들의 이해를 돕기 위해 조금 과장해서 만들었지만 실무에서는 왼쪽과 같이 만들어진 데이터가 넘쳐 납니다. 몇 가지 규칙만 지켜도 충분히 분석하기 좋은 데이터가 될 수 있습니다. 지금부터 기본적인 분석하기 좋은 데이터의 구조적 요건을 소개해 드리겠습니다. 이 몇 가지만 이해하고 익히고 그렇게 만들어진 데이터로 엑셀에서 제공하는 강력한 기능을 활용한다면 우리의 일은 한층 더 높은 단계로 뛰어오를 수 있을 것입니다.
1. 리스트 형태로 만들기
분석을 위한 원본 데이터는 리스트 형태이어야 합니다. 왼쪽 표는 크로스 탭(cross tab) 형태로, 행과 열에 원본 데이터가 아니라 분석의 결과물 또는 가공된 데이터(합계)가 들어 있습니다. 이런 표는 요약, 집계, 분석 과정을 거친 결과물로써 기초 원본 데이터로 부적절합니다. 반면 오른쪽 표는 리스트(list) 형태로, 시스템에서 데이터를 저장하는 기본 구조입니다. 리스트 형태의 데이터는 다양하게 분석할 수 있을 뿐 아니라 데이터(행)나 변수(열)를 계속 추가하더라도 분석 결과에 반영할 수 있습니다.
2. 셀 병합하지 않기
2개 이상의 셀을 하나로 합치는 기능을 병합(merge)이라고 합니다. 이 기능은 보고서처럼 한눈에 알아보기 쉽도록 표를 만들 때 많이 사용합니다. 하지만 병합은 데이터 분석에서 오류를 발생시키거나 결과값을 왜곡시키는 대표적인 기능입니다. 엑셀에서 셀은 데이터를 저장하는 최소 단위로, 행과 열이 교차하는 모든 지점을 주소로 갖습니다. 예를 들어 [C] 열과 [5] 행이 교차하는 지점의 셀은 [C5]라는 주소를 갖습니다. 이것은 매우 중요한 원칙인데, 셀 병합을 하면 이 원칙이 깨집니다.
3. 머리글은 직관적이고 정확하게 중복 없이 사용하기
데이터 표는 다음과 같이 행과 열로 구성되고, 열에는 반드시 머리글이 있어야 합니다. 부서, 사번, 이름, 직위 등 열의 제목을 ‘머리글’이라고 합니다. 데이터 분석 과정에서 열은 하나의 변수로써 열과 열을 계산·조합하거나 그 관계를 알아볼 때 사용하므로 열의 이름인 머리글은 곧 변수의 이름으로 중요한 역할을 합니다. 따라서 한글 단어를 영어 발음기호로 표기한다 거나 사용자가 알기 어려운 약어를 쓰는 등의 직관적이지 않은 사용은 피해야 합니다.
4. 요약(합계) 행 사용하지 않기
보고서에서 표를 사용할 때는 각 열이나 행의 합계나 평균 등 대푯값을 표시하는 것이 일반적입니다. 하지만 데이터 분석에서 사용하는 원본 데이터에는 요약 행이 없어야 합니다.
5. 비어 있는 셀 채우기 ― 결측치 제거
데이터가 있어야 할 위치에 데이터가 없는 경우를 결측치(missing value)라고 합니다. 결측치는 ‘공백값’이라고도 하는데, 결측치가 생기는 원인은 데이터베이스 시스템 오류 또는 조작한 사람의 실수일 수도 있고, 측정하지 못해 비었을 수도 있습니다. 여기서 주의해야 할 점은 ‘0’은 결측치가 아니라는 것입니다. ‘0’은 그 자체로 데이터이므로 데이터가 없는 것과는 전혀 다른 상황입니다. 여기서는 어떠한 데이터도 없이 셀이 공백인 경우를 의미합니다.
6. 중복 값 파악하기
속성에 따라 같은 데이터가 2개 이상 있으면 안 되는 열도 있고, 같은 데이터가 있을 수밖에 없는 열도 있습니다. 오른쪽 화면에서 [A] 열은 사내 직원을 식별할 수 있는 [직원 ID] 열로, 중복 값이 있으면 안 됩니다. 하지만 부서, 연령, 성별 등은 직원들끼리 같을 수 있으므로 당연히 중복 값이 있을 수 있습니다.
7. 숫자 데이터를 ‘단위’와 함께 사용하지 말기
숫자 데이터의 특징은 숫자 외에 다른 글자가 포함되면 모두 텍스트 데이터로 바뀐다는 것입니다. 대표적인 예로 500원, 60kg, 25세 등과 같이 숫자를 단위와 함께 사용하는 경우를 들 수 있습니다. 이러한 데이터는 연산에 활용할 수 없으므로 텍스트를 제거하고 숫자만 남겨야 합니다.
8. 같은 열의 데이터는 하나의 데이터 형식으로 통일하기
같은 열의 데이터는 반드시 숫자 또는 텍스트 둘 중 하나의 형식으로 통일돼야 합니다. 특히 실무에서는 여러 회사, 부서, 담당자의 데이터를 취합해 만들어진 데이터로 분석하는 경우가 많습니다. 이러한 데이터에서 자주 발생하는 문제는 데이터 형식이 뒤죽박죽 되는 경우가 많다는 것입니다.
9. 하나의 행에는 하나의 데이터만 사용하기
데이터 표에서 열은 Field 또는 Column이라고 하고, 행은 Row 또는 Record라고 합니다. 데이터 표는 어떠한 사실을 필드별로 기록해서 구성됩니다. 즉, 개별 사실은 행으로 ‘기록’되는데 이 때 행을 구성하는 항목들이 열입니다. 따라서 하나의 행에 2개 이상의 데이터가 들어가는 것은 데이터 표를 구성하는 기본 원칙에 위배됩니다. 사이즈가 여러 개인 티셔츠를 하나의 행으로 테이블을 구성하면 그 어떤 분석도 할 수 없습니다. 각각의 사이즈가 하나의 ‘사실’이기 때문입니다. 따라서 반드시 하나의 행에는 하나의 데이터 즉, 하나의 기록만 사용되어야 합니다.
지금까지 설명을 들으신 분들께서 의아하게 생각하실 수 있는 부분이 있을 것입니다. 바로 데이터는 보기에 좋지 않다는 점입니다. 맞습니다. 데이터는 기본적으로 사람이 컴퓨터와 소통하기 위한 형식이지 사람과 사람 사이의 소통을 위한 형식이 아니기 때문입니다. 그래서 좋은 실무 데이터 작업은 데이터는 데이터대로 좋은 품질로 관리하면서 이를 활용한 보고서는 별개로 만드는 방식입니다. 즉, 데이터와 문서를 분리하는 것입니다. 이것은 엑셀 피벗 테이블의 기본 원리입니다. 피벗 테이블은 원본 데이터를 보존한 채로 별도의 캐시 메모리에 데이터를 저장한 뒤에 원본 데이터를 ‘피벗’ 즉, 변환해서 요약, 합계 분석합니다. 그래서 빠르고 강력합니다.
데이터를 데이터 답게 그리고 데이터와 보고서를 분리해서 사용하게 될 때 비로소 우리는 더욱 빠르고 정확하며 성장하는 일을 할 수 있습니다.
