엑셀 데이터 분석을 위한 데이터 정규화 공식 실전 사용법
엑셀 데이터 정규화 공식 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 엑셀 데이터 정규화 공식 실전예제예제파일[엑셀고급] 엑셀 데이터 정규화 공식 실전예제완성파일
엑셀 데이터 분석, 나는 왜 안될까?
여러분 중, 엑셀로 보고서를 자주 작성하시는 분이라면, 한번쯤은 아래와 같은 질문을 가져본 적이 계실겁니다.
왜 똑같은 자료를 사용해도, 다른 사람처럼 원하는 결과가 나오지 않는 것일까?
이전 강의에서도 여러 번 말씀드렸지만, 엑셀은 [스프레드시트 프로그램]입니다. 즉, 기본적으로 ‘데이터 관리’에 중점을 두고 사용해야 하는 프로그램인데요.
따라서 ‘엑셀 함수나 기능을 잘 사용하는 것’ 보다도 ‘데이터를 체계적으로 잘 관리하는 것’이 엑셀을 더 효율적으로 사용하는데 더 중요한 능력입니다. 데이터를 체계적으로 잘 관리할 수 있다면, 어려운 함수나 기능을 사용하지 않고도 몇 번의 클릭만으로 원하는 데이터를 뽑아낼 수 있게 되는 것이죠.
엑셀도 점점 진화하면서, 함수나 기능의 중요성 보다 데이터 관리의 중요성이 점점 커지고 있습니다. 데이터 정규화 관련 기초 강의 목록
가로로 나열된 데이터, 엑셀공식 하나로 해결!
엑셀은 다양한 업무분야에서 사용되는 무궁무진한 활용도를 가진 프로그램입니다. 사무직 근로자라면 ‘사용빈도’만 다를 뿐 엑셀을 한 번도 사용하지 않는 직장인은 없을 것이라고 생각하는데요.
데이터 정규화에 대한 내용을 잘 모르는 사용자라면, 대부분의 경우 아래와 같이 ‘가로로 나열된 데이터 형태’로 표를 작성하여 관리하게 됩니다. 특히 매출/매입관리, 재고 입출고대장과 같이 ‘일자별’ 또는 ‘월별’로 시간의 흐름에 따라 관리되는 파일을 엑셀로 관리할 경우 가로로 나열된 형태로 작성하여 관리하는 경우가 많습니다.
엑셀로 자료를 입력/편집하여 관리할 경우 위와 같이 가로로 입력된 형태로 자료를 관리하게 됩니다. 위 그림과 같이 가로로 나열된 데이터는 정규화 된 데이터보다 사용자가 보기에 편리하다는 장점이 있습니다. 따라서 실무자가 보다 빠르고 편리하게 자료를 입력하고 편집할 수 있다는 장점이 있죠.
하지만 이러한 데이터가 하나둘씩 쌓이게되면,
언젠가는 데이터를 취합하고 분석하는 데에 큰 어려움를 직면하게 됩니다.그럴 경우 엑셀의 VBA나 다른 맞춤제작된 추가기능을 사용하여 데이터를 정규화하여 관리할 수 있겠지만, 이는 일부 사용자에게만 해당되는 내용이겠지요. 실제 대다수의 사용자는 가로로 나열된 데이터를 정규화 할 때, 손으로 하나하나 작업을 하게 될겁니다.
데이터를 일일히 손으로 옮겨 정규화 할 경우 시간의 낭비가 발생하는 문제점도 있지만, 무엇보다도 사용자의 실수로 인한 휴먼에러가 발생하기 아주 쉬워집니다.
따라서 아래에 적어드린 ‘데이터 정규화 공식’을 사용하면 보다 편리하고 정확하게 데이터를 정규화 할 수 있습니다.
=INDIRECT("R"&(ROW()+(데이터시작행번호-입력시작셀행번호)-(데이터행개수*ROUNDDOWN((ROW()-입력시작셀행번호)/데이터행개수,0)))&"C"&(ROUNDDOWN((ROW()-입력시작셀행번호)/데이터행개수,0)+데이터시작열번호),0)위 데이터 정규화 공식 사용법에 대한 자세한 설명은 아래 관련 포스트를 참고하세요.
재고 입출고 관리대장 데이터 정규화 실전예제
예제 파일의 재고 입출고관리 대장 양식을 정규화 된 데이터로 변환해보겠습니다.
- 예제파일의 [재고입출고관리대장] 시트로 이동합니다.
예제파일에서 사용한 2019년 1월 재고 입출고 관리 대장 파일입니다. - 제품구분과 제품명이 각 날짜에 따라 3개의 항목(입고/출고/잔여)로 나뉘어 입력 된 것을 확인할 수 있습니다.
* 여기서 ‘잔여’ 수량은 입고수량과 출고수량만 알면 자동으로 계산되는 수량이므로 정규화 된 데이터에서는 굳이 필요하지 않는 값입니다. 따라서, 정규화 이후 자동필터를 걸어 삭제합니다.
* 1월 입출고가 시작되기 전, ‘당월기초’ 수량은 1월 1일 이전에 입고된 수량이므로 2018년 12월 31일에 입고된 수량으로 간주하여 정규화 데이터를 입력합니다.
각 열의 머리글을 작성합니다. - 시트의 B30셀로 이동한 뒤, 아래 그림과 같이 표의 머리글을 작성합니다.
- 머리글을 작성하였으면, 아래 표와 같이 기존 입출고관리 대장의 데이터를 정규화된 표로 옮겨줍니다.
(데이터를 편리하고 쉽게 옮기는 방법은 영상강의에서 자세히 다뤄드렸습니다)항목 설명 날짜 12월 31일 기초재고 수량을 시작으로, 1월 1일부터 1월 31일까지 1일씩 증가하면서 날짜가 입력합니다. 제품구분/제품명 각 제품구분과 제품명이 반복하여 입력합니다. 1개의 날짜에 입고/출고/잔여 항목별 3번씩 반복해서 입력합니다. 입출고 각 제품구분/제품명 한 묶음 당 ‘입고’ / ‘출고’ / ‘잔여’ 를 반복하여 입력합니다. 수량 정규화 데이터로 변환하는 공식을 사용하여 가로로 나열된 데이터를 세로로 변환하여 입력합니다. - 1월 1일부터 데이터 입력을 시작합니다. 12월 31일(기초재고)수량은 나머지 데이터가 온전히 옮겨진 뒤 마지막으로 데이터를 입력해줍니다.
- 각 항목의 첫번째 묶음을 작성 완료하였으면, 자동채우기로 나머지 반복되는 부분을 채워줍니다. 보다 빠르게 진행하기 위해, 자동채우기를 하기 전 정규화 된 데이터의 마지막 행번호를 계산합니다.
(‘데이터 개수’ x ‘입출고 항목 개수’ x ‘날짜 수’) + ‘입력 시작셀 행번호’ - 1
항목 값 데이터 개수 20 입출고 항목 개수 3 (입고, 출고, 잔여) 날짜 수 31 입력 시작셀 행번호 31 - 위 수식을 계산하면, 마지막 행 번호로 1890이 계산됩니다. ‘E1890’ 셀로 이동한 뒤 위쪽의 범위를 선택하여 자동채우기를 합니다. 자동채우기를 완료하면 아래 그림과 같이 수량을 제외한 정규화 데이터가 완성됩니다.
자동채우기를 하면 위와 같이 각 항목이 반복되면서 정규화 데이터의 틀이 완성됩니다. - 이제 수량 값을 입력해줍니다. F31셀에 아래 수식을 입력합니다. 정규화 공식의 동작 원리는 관련 포스트에서 자세히 설명해드렸습니다.
=INDIRECT("R"&(ROW()+(6-31)-(20*ROUNDDOWN((ROW()-31)/20,0)))&"C"&(ROUNDDOWN((ROW()-31)/20,0)+6),0)
공식을 F31셀에 입력한 뒤 자동채우기합니다. - 공식을 자동채우기하면 데이터 정규화가 완료됩니다. 데이터가 올바르게 입력되었는지 확인한 뒤, 올바르게 입력되었으면 전체범위를 복사 ⇨ 선택하여 붙여넣기를 통해 값으로만 붙여넣기하여 이후에 오류가 일어나지 않도록 방지합니다.
- 입고/출고/잔여 수량 중, 잔여수량은 정규화된 데이터에서는 필요 없는 값이므로 삭제합니다. 데이터 범위를 선택한 뒤, 키보드 CTRL + SHIFT + L 키를 눌러 필터를 적용합니다. 이후 잔여에 해당하는 항목만 선택한 뒤 전체 행을 삭제합니다.
잔여에 해당하는 값은 필터링하여 정규화 데이터 목록에서 삭제합니다. - 보다 쉬운 계산을 위하여 입고는 ‘양수(+)’로, 출고는 ‘음수(-)’로 수량 값을 변경합니다. IF함수를 사용하여 G31셀에 아래 공식을 입력한 뒤 자동채우기합니다. 값이 올바르게 입력되었는지 확인한 뒤, 값을 복사 - 선택하여 붙여넣기로 E열에 값 형태로 붙여넣기 합니다.
=IF(E31="입고",F31,F31*-1)
입고수량은 양수, 출고수량은 음수로 값을 변환해줍니다. - 마지막으로 1월 기초재고 수량을 데이터에 추가합니다. 1월 기초재고 수량은 12월 31일에 입고된 수량으로 가정하여, 데이터 아랫부분에 ‘2018년 12월 31일’ 입고 재고로 데이터를 추가합니다.
(영상강의에서는 데이터 윗부분에 셀을 삽입하여 추가하였지만, 데이터 아랫부분에 추가해도 무방합니다.)
정규화 된 데이터 마지막 부분에 12월 31일 입고재고 수량을 추가합니다. (1월 기초재고 수량)
정규화 데이터 분석을 위한 피벗테이블 만들기
재고입출고관리대장의 데이터 정규화가 완료되었습니다. 이렇게 데이터를 정규화하여 관리하면 이후에 발생할 입출고내역이 추가되더라도 손쉽게 자료를 취합하고 분석할 수 있습니다.
피벗테이블을 생성하기 위하여 정규화 된 데이터 범위를 선택한 뒤, [삽입] - [피벗테이블] 버튼을 클릭하거나 키보드 ALT – N – V 로 피벗테이블을 추가합니다.
입/출고 현황을 보여주는 피벗테이블 만들기
피벗테이블을 선택하면 우측에 피벗테이블 필드 옵션창이 나타납니다. 각 항목를 아래 표와 같이 열/행/값 필드로 이동하면 입/출고 현황을 보여주는 피벗테이블이 완성됩니다.
필드 항목 열 필드 날짜, 입출고 행 필드 제품구분, 제품명 값 필드 수량 
행/열/값 필드에 각 항목을 끌어다 놓으면 일별 입/출고 내역을 확인하는 피벗테이블이 완성됩니다. 잔여수량을 보여주는 피벗테이블 만들기
아래 표와 같이 피벗테이블의 행/열/값 필드 항목을 이동합니다.
수량을 클릭하여 값 필드설정으로 이동한 뒤, 표시형식을 ‘날짜별 누계’로 변경합니다. 필드 항목 열 필드 날짜 행 필드 제품구분, 제품명 값 필드 수량 (표시형식 : 날짜별 누계) 수량의 표시형식을 ‘날짜별 누계’로 변경합니다.
각 일별로 잔여 재고수량 변화를 피벗테이블을 통해 손쉽게 출력할 수 있습니다.
