엑셀 데이터 테이블 구성 방법

• 여러 사람의 엑셀 파일을 취합하여 데이터를 처리 해야 하는 경우
• 엑셀 파일에 여러 시트를 숨김 속성을 주고 Vlookup으로 찾고 계산하고 점점 늘어만 가는 시트.....
• Ctl+C, Ctrl+V가 일상이 되어 버린 엑셀 작업

생각해 보면 엑셀로 데이터를 처리 하기 위해 체계적인 교육을 받아 본 적도 없다. 그져 대학 시절 자격증을 따기 위해 시험에 최적화된 잠시의 추억은 있을뿐.....내 입사일자 보다 오래된 엑셀 파일! 그냥 그렇게 데이터를 입력하고 수식을 적용하고 복사하고 붙여넣는게 짜증은 나지만 어느 교육센터의 과정명 처럼 빨리 한들 퇴근시간이 빨라지는 것도 아니니......

인내의 한계점에 다다러서 SOS 청하는 현장에 가보면 거의 대부분은 초기 데이터 관리에서 부터 문제의 실마리를 풀 수 밖에 없다.

업무의 형태에 따라 복잡한 함수가 필요한 경우도 있지만, 거의 대부분은 그리 고급 함수를 요구하는 상황은 아니다. 데이터를 찾아서 가져오고 중복 검사를 하고 조건에 따라서 수식이 바뀌고 이 정도만으로도 많은 부분은 해결이 가능하지만, 꼬일대로 꼬여버린 엑셀 파일의 공통점은 데이터 정리이다. 이 부분은 어렵다. 왜 냐면 바로 데이터베이스 개념이 들어가기 때문에

거의 대부분의 담당자들은 플랫한 엑셀 파일을 만들게 된다. 플랫한 엑셀 파일의 최종 목적은 피벗 테이블의 원본이 되는 형태의 데이터 구조 인것이다. 전문적인 기술 용어가 나오면 지레 겁을 먹고 어렵다는 선입견의 연막탄을 미리 여기저기 피게 된다. 하지만, 확실한 한가지 데이터베이스 개념은 반드시 필요하고 지극히 논리적인 개념이다. 한번쯤 시간을 내서 정복해 볼만한 가치가 점심 시간의 스타벅스 한잔의 여유와 같은 정도의 시간만으로 충분 하지 않을까?

예를 들어 가장 일반적으로 많이 쓰는 업무 양식중에 하나인 견적서를 기준으로 생각을 해 보자~~~ 

견적서를 구성하는 요소를 보면 다음과 같이 정리 할 수 있을 것 같다.

• 거래처명
• 담당자
• 담당자명
• 담당자 연락처
• 담당자 이메일

---

• 견적일자
• 견적제목
• 보낸 사람

---

• 항목
• 세부내용
• 수량
• 단가
• 비고

많은 현장에서 엑셀을 워드처럼 사용하는 경우는 비일비재 하다. 엑셀은 셀 단위로 움직이니 표를 만들기 편하고 계산도 되니까 너무도 당연한 현상이다. 그런데, 잠시 숨을 고르자 일회성 또는 고정된 양식에 특정 부분의 내용만 그때 그때 변경해서 문서를 만드는 정도의 목적이라면 워드보다 당연히 빠르고 편리하다.

하지만, 지속적인 관리가 필요하고 먼가 추가적인 내용을 뽑아내야 한다면 그때는 지금의 편리함이 깊은 자괴감으로 돌아 올 수도 있다는 생각을 한번 해야 한다. 이전 포스트에서 양식과 데이터를 분리 해야 한다고 했었다. 그 전제는 지속적으로 내용을 관리 하고 그 내용으로 부터 또 무언가를 생산 해 내야 할때는 그런 형태로 워드처럼 쓰면 절대 안된다. 

위의 견적서를 기준으로 만약 이렇게 작성해서 출력 하고 이 출력물을 따로 관리 한다고 하면야 그냥 편한대로 쓰면 된다. 그런데 이 견적서를 기준으로 원가 산정을 하고 세일즈 집계를 하고 예측을 하고 다시 또 그 결과를 바탕으로 마케팅과 연결되고.......이런 순환 구조를 갖게 된다면 효율적인 데이터 관리 방법을 생각 해야만 한다.

이번 내용에서는 어떻게 데이터 테이블을 만들어서 관리 할지에 대한 부분에 대해서 알아 보고 차후 이런 데이터 테이블을 어떻게 활용하는지에 대해서 알아 보도록 하겠다.

견적서의 출력 또는 메일링을 위해서 양식 형태로의 작업은 기본적으로 필요하다는 전제 하에서 그렇다면 어떻게 데이터를 관리 할것인가?

한참 위에서 이야기 한 견적서의 모든 내용을 하나의 엑셀 시트에 마구마구 때려 넣는 플랫한 파일 형태로 만드는 것은 권장 하지 않는다.눈치 빠른분들은 이미 알아 채셨겠지만, 관련성이 있는내용으로 시트로 구분 하는 것이다.

거래처라는 시트에 담당자라는 시트에 견적내용이라는 시트에 각각 그림과 같은 형태로 데이터를 분리해서 관리 하는 방법이다. 데이터를 이렇게 각각의 시트로 변경해서 분리하는 방법이 왜 좋을까? 데이터를 입력하는 관점에서 생각하면 일이 될 수도 있다. 왜냐면 각 시트를 연결해주는 값이 중복해서 입력 되기 때문에 하지만 중복해서 입력되는 값이 아니라 찾을때 기준이 되는 값이라고 생각 해야 한다. 화살표의 1:N은 관계를 나타내는데 1인쪽은 중복된 값이 있으면 안되고 N쪽은 여러번 나올 수 있다는 것이다. 

거래처만 관리하는 시트에는 당연히 중복된 거래처 정보를 입력하지 않을것이다. 하지만 담당자의 경우는 한 회사에 여러명이 있을 수도 있고 담당자가 바뀌는 경우도 있으니 담당자 시트에서 거래처명은 여러번 나올 수 있는 것이다.

다소 복잡하게 보일지 모르지만 이렇게 하나의 시트에 모든 내용을 다 집어 넣는게 아니라 관련된 속성을 기준으로 시트로 분리하고 데이터를 입력해 놓고 각 시트의 연결된 값을 기준으로 각 시트들은 논리적으로 연결되어 있게 되는 것이다. 

이렇게 여러 테이블들이 관계를 맺고 있는 상태를 데이터 모델이라고 부르고 이 모델을 기반으로 거래처 주소를 기준으로 지역별 매출을 추적할 수도 있고  견적내용의 보낸사람을 기준으로 어떤 항목을 얼마나 세일즈 했는지 기간별로 볼 수 있게 되는 것이다.

이렇게 데이터가 아래로 누적 될 것이고 만약 어떤 거래처의 주소가 바뀌거나 사업자 등록번호가 바뀌면 해당 시트에서 해당 항목의 값을 바꾸면 된다. 하지만, 플랫한 파일일 경우 즉 일자별로 계속 누적하여 시트로 분리하지 않고 데이터를 만든다고 하면 이러한 수정 작업은 아무래도 힘들어지게 된다. (그렇다고 불가능 하다는 이야기는 아니다.)

정리하면

지속적으로 자료를 누적하여 관리 하고 다양한 누적된 데이터를 기반으로 정보를 추출하기 위해선 자료를 데이터 형태로 관리 해야 하고 그 방법은 다음과 같다.

• 같은 속성을 가지고 있는 시트로 분리해라
• 분리된 속성은 하나의 상태만 가질수 있도록 잘개 쪼갤수록 좋다.(예를 들어 거래처의 주소1, 주소2와 같이 쪼개는 경우는 주소를 하나로 했을 경우 지역을 기반으로 정보를 뽑으려고 하면 다시 또 쪼개야 하는 번거로움을 피할 수 있다.)
• 분리된 시트를 연결하는 값으로 각 시트는 연결 되어야 한다.
• 위 그림에서 빨간 색으로 표시한 부분이 바로 데이터베이스에서 키 값이라고 하는 다른 테이블의 값을 매칭하는 기준이 되는 값이다.

우선 이정도만 해 놓게 되도 8부 능선은 거뜬히 넘은것이다. 이젠 약간의 기능을 습득하면 데이터를 관리하고 분석하는데 있어서 편리하게 작업을 할 수 있게 된다.