엑셀에서 '데이터 구조화'라고 하면 뭔가 어려울 것 같은 느낌이지만 그렇게 어렵지 않습니다. 약간의 노력을 들여서 알아두면 엑셀의 새로운 세계를 만날 수 있습니다. 과거부터 사용하던 복잡한 엑셀 양식 때문에 수식이 복잡하게 꼬여서 고생하고, 수식으로도 안돼서 VBA까지 써야 했던 고통으로부터 벗어날 수 있습니다.
수식이 복잡하고 어려운 대부분의 경우는 데이터를 구조화하지 않아서 생기는 문제입니다. 구조화를 하면 엑셀이 쉬워지고 업무 능률이 올라갑니다.
1. 구조화되지 않은 엑셀 자료
수식을 어렵고 복잡하게 만드는 구조화되지 않은 엑셀 자료 몇 가지를 살펴보겠습니다.
1) 엑셀 양식에 불필요한 요소가 포함되어 있는 형태
아래 그림의 자료는 실무 현장에서 많이 보는 자료의 구조입니다. 없어도 되는 제목이 2행에 표시되어 있고, 그 다음에 머리글이 나오는데 그룹별로 일부는 병합이 되어 있습니다.
이 양식은 다음과 같이 여러 문제가 있습니다.
- 실제 자료는 4행부터 시작하므로 정렬, 필터 등을 사용하려면 항상 범위를 지정해 주어야 한다.
- 데이터를 정렬하기가 불편하다. 정렬을 할 수 있긴 하지만 머리글을 제대로 못 가져오는 문제가 있다.
- 머리글이 병합되어 있어서 일부 필드는 필터기능을 사용할 수 없다.
- 머리글이 병합되어 있어서 피벗테이블을 사용할 수 없다.
- 머리글이 병합되어 있어서 일부 필드는 부분합 기능을 사용할 수 없다.
- 거래처는 두 가지 정보가 포함되어 있어서 제대로 사용할 수 없다. 텍스트 나누기를 해야 한다.
- 빈 행이 중간에 포함되어 있어서 빈 행을 계산에 포함할 때 일부 계산이 잘못될 수 있다.
위의 양식을 아래와 같은 구조로 바꾸면 위에 언급한 엑셀의 기능을 제대로 쓸 수 있습니다.
자료의 구조를 바꾸면 다음과 같은 집계표도 쉽게 만들 수 있습니다.
집계함수 몇개만 사용하면 됩니다. 어려운 배열 함수 같은 것은 쓸 일이 없습니다.
2) 데이터와 보고서 양식이 혼재되어 있는 형태
실무 현장에서 보면 과거부터 사용하던 인쇄된 보고서 형식의 자료를 엑셀로 그대로 옮겨서 쓰는 바람에 아래와 같은 모양의 엑셀 양식을 쓰는 경우가 많습니다. 주 단위로 시트를 하나씩 만드는 방식입니다. 이 자료로 월간, 분기 단위의 집계 또는 보고서 작성 작업을 해야 한다면 난감해집니다. 시간을 상당히 들여서 수작업하거나 복잡하고 꼬인 수식을 쓰든지 해야 합니다.
'근무표' 뿐만 아니라 건설현장의 작업일보, 생산현장의 생산일보도 비슷한 포맷으로 작성되고 있고, 겪고 있는 문제도 비슷합니다.
이 '주간 근무표'가 가진 문제점을 정리해 보면 다음과 같습니다.
- 단기간만 쓸 때는 금방 만들수 있고, 간단해서 좋겠지만 한달만 넘어가면 시트가 4~5개씩 만들어 지고 기간이 늘어날 수도록 자료에 파묻힌다.
- 주간 단위 관리를 위한 포맷이므로 월간,분기 집계/보고 등 주간 단위 이외의 자료 처리에는 엄청난 시간과 노력을 들여야 한다.
- 점심시간 계산은 되지도 않으니 필드를 추가하거나 근무시간에서 일괄적으로 1을 빼는 수식을 작성해야 하고, 혹시 연장 근무 계산이 필요하면 다 뜯어 고쳐야 한다.
- 주간단위 근무 시간 합계 같은 간단한 집계도 배열 수식을 써야한다({=SUM((E7:K7)-(E6:K6))*24})
위 양식은 아래와 같이 구조화된 형태로 바꿔야 합니다.
그래야 위에서 이야기한 문제점을 해결할 수 있습니다. 구체적인 방법은 아래의 엑셀 데이터 구조화에서 다룹니다.
위와 같이 엑셀을 구조화하면 집계함수 몇 개로 간단하게 집계/보고 양식을 만들 수 있습니다. 이 뿐만 아니라 다음과 같이 피벗테이블을 이용하면 수식을 하나도 사용하지 않고 원하는 포맷을 만들 수 있습니다. 자료를 구조화하면 복잡하기만 하던 업무가 간단해 집니다.
2. 엑셀 자료 구조화 원칙
위에서 살펴본 내용에 더해서 엑셀자료를 구조화하는 원칙을 다음과 같이 정리해 보겠습니다.
1) 데이터와 분석/보고용 양식 분리하기
엑셀 자료를 구조화하는 첫 번째 원칙은 데이터와 분석/보고용 양식이 혼재되어 있는 업무 양식에서 데이터와 분석/보고용 양식으로 분리하는 것입니다.
업무 양식에서 데이터를 분리하여 구조화된 형태로 만들면 다음과 같은 잇점이 있습니다.
- 데이터가 구조화되어 있어야 엑셀의 기능(피벗테이블, 필터, 정렬 등)과 함수, 수식을 쉽게 사용할 수 있다.
- 구조화된 데이터로부터 다양한 형태의 분석 및 보고용 양식을 손쉽게 만들 수 있다.
- 업무에 필요한 항목이 있을 때 필드(열)를 손쉽게 추가할 수 있으며 기존의 데이터와 기존의 수식에 영향을 미치지 않는다.
2) 데이터 구조는 데이터베이스 구조로 만들기
과거에 종이에 업무 내용을 기록해야했던 시절에는 종이의 여백을 두지 않고 최대한 공간을 활용하는 방식이었으므로 가로로 펼쳐서 기록을 했지만 엑셀에서는 그렇게 할 필요가 없습니다. 데이터베이스와 같은 방식으로 데이터 구조를 만들고 자료를 입력하면 됩니다.
엑셀의 시트는 사실 데이터베이스와 거의 동일한 구조입니다. 행과 열이 모인 것이 시트이고 데이터베이스도 동일한 구조로 되어 있습니다. 용어만 약간 다를 뿐입니다. 행은 보통 row(사실 '행'의 영어 표현), 열은 필드 또는 속성이라고 부릅니다.
데이터베이스 구조로 만드는 원칙을 정리해 보면 다음과 같습니다. 이 원칙으로 데이터 구조가 만들어져야 복잡하고 불필요한 작업없이 간단히 데이터를 처리할 수 있습니다.
① 위 그림의 상단 1~3행은 불필요하므로 삭제한다. 머리글이 1행, 1열([A1]셀)부터 시작하도록하고 제목은 시트이름으로 대신한다.
② 세로로 반복되는 사번, 이름 필드는 그대로 세로로 옮긴다.
③ 날짜와 함께 요일이 가로로 펼쳐진 부분은 확장에 문제가 있으므로(1주일이 아니라 1개월을 관리해야 한다면 31개까지 열을 추가해야 함) 근무일 필드와 요일 필드를 추가하고 데이터는 세로로(행단위)로 추가될 수 있도록 한다.
④ 출근시간과 퇴근시간은 하나의 자료(행단위)에 기록되어야 하는데 2개의 행으로 나누어져 있다. 출근, 퇴근 필드를 추가한다.
⑤ 합계 등 계산 필드는 삭제한다. 계산 필드는 집계, 분석, 보고용 시트에서 따로 계산할 수 있으므로 불필요한 필드이다.
더 알아보기) '데이터베이스로 구조로 만들기'에 대해
데이터베이스 구조로 만드는 작업은 위에 설명한 것보다 훨씬 더 복잡합니다. 정규화라는 작업을 해야하고, Primary key를 식별해야 하는 등 많은 작업이 있는데 엑셀을 사용자 입장에서는 필요이상의 범위이므로, 위에서는 필요한 만큼만 간단히 설명하였습니다. 추후 데이터 분석등 좀 더 전문적으로 데이터를 다뤄야 하는 경우라면 추가적인 학습이 필요할 수 있습니다.
3) 데이터베이스 구조로 만들었으면 데이터베이스처럼 사용하기
여기에는 기본 원칙을 설명합니다. 자세한 방법은 이어지는 각각의 절에서 설명합니다.
- 하나의 필드(열)에는 데이터 유형을 통일한다. 날짜 필드이라면 날짜 형식으로만 넣는다. 텍스트로 '9월 15일'의 형식으로 입력하면 안된다.
- 하나의 셀에는 하나의 정보만 포함한다. 위의 '거래처별 판매이력'의 예처럼 거래처명과 대리점 유형을 같은 필드에 넣어 놓으면 필드별로 데이터 처리가 불가능하다.
- 중간에 빈 셀, 빈 행이 있으면 안된다. 보기 좋도록 하기 위해 비운 것인지 실제 값이 빈 것인지 판별이 불가능할 수 있다. → 빈 셀, 빈 행은 이동옵션을 이용하여 삭제할 수 있다.
- 유효한 데이터만 들어가게 처리한다 → 데이터유효성 검사를 이용하여 유효한 데이터만 들어가게 할 수 있다.
- 폰트 크기 지정, 배경색 지정 등 서식을 사용하지 않는다. 색으로 정렬도 할 수 있지만 데이터 처리에는 그다지 유용하지 못하다.
데이터 구조화에 관한 참고 : Guidelines for organizing and formatting data on a worksheet - https://support.microsoft.com/en-us/office/guidelines-for-organizing-and-formatting-data-on-a-worksheet-90895cad-6c85-4e02-90d3-8798660166e3
엑셀 수식 강좌
[ 수식 다루기 ]
[ 엑셀에서 입력과 표시방법 ]
[ 수식에서 셀과 범위 참조 방식 ]
[ 엑셀을 제대로 쓰는 데이터처리 ]
- 엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
- 수식으로 한 번에 텍스트 나누기
- 엑셀 고급필터로 다양하게 자료 필터링하기
- 정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
- 엑셀 오류데이터 처리하기
- 파워 쿼리(Power Query) 소개
- 파워쿼리로 외부 데이터 가져오기
- 엑셀에서 중복 데이터 제거하기
- 엑셀 텍스트 나누기의 모든 것
- 이동옵션으로 원하는 셀 한번에 선택하고 처리하기
- 데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
- 사용자 지정 수식으로 데이터 유효성 검사하기
- 데이터 유효성 검사로 허용된 데이터만 입력
- 엑셀 데이터 다루기 첫 단추는 데이터 구조화
[ 엑셀 이름정의와 표기능 ]
[ 수식을 분석하고 오류 해결하기 ]
- 엑셀 순환참조 문제 해결하기
- 수식 계산 단계 알아보기
- 엑셀 수식 오류 검사하고 추적하기
- 엑셀에서 참조 셀 추적하기
- 엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
