엑셀 데이터 분석을 위한 데이터 정규화 공식 사용법

효율적인 데이터 관리를 하기 위한 필수단계인 '데이터 1차 정규화' 작업을 손쉽게 도와주는 '정규화 함수 공식'의 이론 및 사용방법을 단계별로 살펴봅니다.

홈페이지 » 엑셀 데이터 분석을 위한 데이터 정규화 공식 사용법

엑셀 데이터 분석을 위한 데이터 정규화 공식 실전 사용법

엑셀 데이터 정규화 공식 목차 바로가기
영상강의


예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [엑셀고급] 엑셀 데이터 정규화 공식 실전예제
    예제파일
  • [엑셀고급] 엑셀 데이터 정규화 공식 실전예제
    완성파일

엑셀 데이터 분석, 나는 왜 안될까?

여러분 중, 엑셀로 보고서를 자주 작성하시는 분이라면, 한번쯤은 아래와 같은 질문을 가져본 적이 계실겁니다.

왜 똑같은 자료를 사용해도, 다른 사람처럼 원하는 결과가 나오지 않는 것일까?

이전 강의에서도 여러 번 말씀드렸지만, 엑셀은 [스프레드시트 프로그램]입니다. 즉, 기본적으로 ‘데이터 관리’에 중점을 두고 사용해야 하는 프로그램인데요.

따라서 ‘엑셀 함수나 기능을 잘 사용하는 것’ 보다도 ‘데이터를 체계적으로 잘 관리하는 것’이 엑셀을 더 효율적으로 사용하는데 더 중요한 능력입니다. 데이터를 체계적으로 잘 관리할 수 있다면, 어려운 함수나 기능을 사용하지 않고도 몇 번의 클릭만으로 원하는 데이터를 뽑아낼 수 있게 되는 것이죠.

엑셀도 점점 진화하면서, 함수나 기능의 중요성 보다 데이터 관리의 중요성이 점점 커지고 있습니다.

데이터 정규화 관련 기초 강의 목록

가로로 나열된 데이터, 엑셀공식 하나로 해결!

엑셀은 다양한 업무분야에서 사용되는 무궁무진한 활용도를 가진 프로그램입니다. 사무직 근로자라면 ‘사용빈도’만 다를 뿐 엑셀을 한 번도 사용하지 않는 직장인은 없을 것이라고 생각하는데요.

데이터 정규화에 대한 내용을 잘 모르는 사용자라면, 대부분의 경우 아래와 같이 ‘가로로 나열된 데이터 형태’로 표를 작성하여 관리하게 됩니다. 특히 매출/매입관리, 재고 입출고대장과 같이 ‘일자별’ 또는 ‘월별’로 시간의 흐름에 따라 관리되는 파일을 엑셀로 관리할 경우 가로로 나열된 형태로 작성하여 관리하는 경우가 많습니다.

가로로 나열된 데이터 분석
엑셀로 자료를 입력/편집하여 관리할 경우 위와 같이 가로로 입력된 형태로 자료를 관리하게 됩니다.

위 그림과 같이 가로로 나열된 데이터는 정규화 된 데이터보다 사용자가 보기에 편리하다는 장점이 있습니다. 따라서 실무자가 보다 빠르고 편리하게 자료를 입력하고 편집할 수 있다는 장점이 있죠.

하지만 이러한 데이터가 하나둘씩 쌓이게되면,
언젠가는 데이터를 취합하고 분석하는 데에 큰 어려움를 직면하게 됩니다.

그럴 경우 엑셀의 VBA나 다른 맞춤제작된 추가기능을 사용하여 데이터를 정규화하여 관리할 수 있겠지만, 이는 일부 사용자에게만 해당되는 내용이겠지요. 실제 대다수의 사용자는 가로로 나열된 데이터를 정규화 할 때, 손으로 하나하나 작업을 하게 될겁니다.

데이터를 일일히 손으로 옮겨 정규화 할 경우 시간의 낭비가 발생하는 문제점도 있지만, 무엇보다도 사용자의 실수로 인한 휴먼에러가 발생하기 아주 쉬워집니다.

따라서 아래에 적어드린 ‘데이터 정규화 공식’을 사용하면 보다 편리하고 정확하게 데이터를 정규화 할 수 있습니다.

=INDIRECT("R"&(ROW()+(데이터시작행번호-입력시작셀행번호)-(데이터행개수*ROUNDDOWN((ROW()-입력시작셀행번호)/데이터행개수,0)))&"C"&(ROUNDDOWN((ROW()-입력시작셀행번호)/데이터행개수,0)+데이터시작열번호),0)

위 데이터 정규화 공식 사용법에 대한 자세한 설명은 아래 관련 포스트를 참고하세요.

재고 입출고 관리대장 데이터 정규화 실전예제

예제 파일의 재고 입출고관리 대장 양식을 정규화 된 데이터로 변환해보겠습니다.

  1. 예제파일의 [재고입출고관리대장] 시트로 이동합니다.

    재고입출고 관리대장 데이터 분석 예제
    예제파일에서 사용한 2019년 1월 재고 입출고 관리 대장 파일입니다.

  2. 제품구분과 제품명이 각 날짜에 따라 3개의 항목(입고/출고/잔여)로 나뉘어 입력 된 것을 확인할 수 있습니다.
    * 여기서 ‘잔여’ 수량은 입고수량과 출고수량만 알면 자동으로 계산되는 수량이므로 정규화 된 데이터에서는 굳이 필요하지 않는 값입니다. 따라서, 정규화 이후 자동필터를 걸어 삭제합니다.
    * 1월 입출고가 시작되기 전, ‘당월기초’ 수량은 1월 1일 이전에 입고된 수량이므로 2018년 12월 31일에 입고된 수량으로 간주하여 정규화 데이터를 입력합니다.

    재고입출고 관리대장 항목 입력
    각 열의 머리글을 작성합니다.

  3. 시트의 B30셀로 이동한 뒤, 아래 그림과 같이 표의 머리글을 작성합니다.
  4. 머리글을 작성하였으면, 아래 표와 같이 기존 입출고관리 대장의 데이터를 정규화된 표로 옮겨줍니다.
    (데이터를 편리하고 쉽게 옮기는 방법은 영상강의에서 자세히 다뤄드렸습니다)
    항목 설명
    날짜 12월 31일 기초재고 수량을 시작으로, 1월 1일부터 1월 31일까지 1일씩 증가하면서 날짜가 입력합니다.
    제품구분/제품명 각 제품구분과 제품명이 반복하여 입력합니다. 1개의 날짜에 입고/출고/잔여 항목별 3번씩 반복해서 입력합니다.
    입출고 각 제품구분/제품명 한 묶음 당 ‘입고’ / ‘출고’ / ‘잔여’ 를 반복하여 입력합니다.
    수량 정규화 데이터로 변환하는 공식을 사용하여 가로로 나열된 데이터를 세로로 변환하여 입력합니다.
  5. 1월 1일부터 데이터 입력을 시작합니다. 12월 31일(기초재고)수량은 나머지 데이터가 온전히 옮겨진 뒤 마지막으로 데이터를 입력해줍니다.
  6. 각 항목의 첫번째 묶음을 작성 완료하였으면, 자동채우기로 나머지 반복되는 부분을 채워줍니다. 보다 빠르게 진행하기 위해, 자동채우기를 하기 전 정규화 된 데이터의 마지막 행번호를 계산합니다.
    (‘데이터 개수’ x ‘입출고 항목 개수’ x ‘날짜 수’) + ‘입력 시작셀 행번호’ - 1
    항목
    데이터 개수 20
    입출고 항목 개수 3 (입고, 출고, 잔여)
    날짜 수 31
    입력 시작셀 행번호 31
  7. 위 수식을 계산하면, 마지막 행 번호로 1890이 계산됩니다. ‘E1890’ 셀로 이동한 뒤 위쪽의 범위를 선택하여 자동채우기를 합니다. 자동채우기를 완료하면 아래 그림과 같이 수량을 제외한 정규화 데이터가 완성됩니다.

    데이터분석 재고입출고 틀 완성
    자동채우기를 하면 위와 같이 각 항목이 반복되면서 정규화 데이터의 틀이 완성됩니다.

  8. 이제 수량 값을 입력해줍니다. F31셀에 아래 수식을 입력합니다. 정규화 공식의 동작 원리는 관련 포스트에서 자세히 설명해드렸습니다.
    =INDIRECT("R"&(ROW()+(6-31)-(20*ROUNDDOWN((ROW()-31)/20,0)))&"C"&(ROUNDDOWN((ROW()-31)/20,0)+6),0)

    데이터 분석 재고입출고대장 정규화 완료
    공식을 F31셀에 입력한 뒤 자동채우기합니다.

  9. 공식을 자동채우기하면 데이터 정규화가 완료됩니다. 데이터가 올바르게 입력되었는지 확인한 뒤, 올바르게 입력되었으면 전체범위를 복사 ⇨ 선택하여 붙여넣기를 통해 값으로만 붙여넣기하여 이후에 오류가 일어나지 않도록 방지합니다.
  10. 입고/출고/잔여 수량 중, 잔여수량은 정규화된 데이터에서는 필요 없는 값이므로 삭제합니다. 데이터 범위를 선택한 뒤, 키보드 CTRL + SHIFT + L를 눌러 필터를 적용합니다. 이후 잔여에 해당하는 항목만 선택한 뒤 전체 행을 삭제합니다.

    잔여 항목 삭제
    잔여에 해당하는 값은 필터링하여 정규화 데이터 목록에서 삭제합니다.

  11. 보다 쉬운 계산을 위하여 입고는 ‘양수(+)’로, 출고는 ‘음수(-)’로 수량 값을 변경합니다. IF함수를 사용하여 G31셀에 아래 공식을 입력한 뒤 자동채우기합니다. 값이 올바르게 입력되었는지 확인한 뒤, 값을 복사 - 선택하여 붙여넣기로 E열에 값 형태로 붙여넣기 합니다.
    =IF(E31="입고",F31,F31*-1)

    재고입출고 입고 양수 출고 음수
    입고수량은 양수, 출고수량은 음수로 값을 변환해줍니다.

  12. 마지막으로 1월 기초재고 수량을 데이터에 추가합니다. 1월 기초재고 수량은 12월 31일에 입고된 수량으로 가정하여, 데이터 아랫부분에 ‘2018년 12월 31일’ 입고 재고로 데이터를 추가합니다.

    (영상강의에서는 데이터 윗부분에 셀을 삽입하여 추가하였지만, 데이터 아랫부분에 추가해도 무방합니다.)

    기초재고 추가
    정규화 된 데이터 마지막 부분에 12월 31일 입고재고 수량을 추가합니다. (1월 기초재고 수량)

정규화 데이터 분석을 위한 피벗테이블 만들기

재고입출고관리대장의 데이터 정규화가 완료되었습니다. 이렇게 데이터를 정규화하여 관리하면 이후에 발생할 입출고내역이 추가되더라도 손쉽게 자료를 취합하고 분석할 수 있습니다.

피벗테이블을 생성하기 위하여 정규화 된 데이터 범위를 선택한 뒤, [삽입] - [피벗테이블] 버튼을 클릭하거나 키보드 ALTNV 로 피벗테이블을 추가합니다.

입/출고 현황을 보여주는 피벗테이블 만들기

피벗테이블을 선택하면 우측에 피벗테이블 필드 옵션창이 나타납니다. 각 항목를 아래 표와 같이 열/행/값 필드로 이동하면 입/출고 현황을 보여주는 피벗테이블이 완성됩니다.

필드 항목
열 필드 날짜, 입출고
행 필드 제품구분, 제품명
값 필드 수량
입출고현황 피벗테이블 만들기
행/열/값 필드에 각 항목을 끌어다 놓으면 일별 입/출고 내역을 확인하는 피벗테이블이 완성됩니다.
잔여수량을 보여주는 피벗테이블 만들기

아래 표와 같이 피벗테이블의 행/열/값 필드 항목을 이동합니다.

잔여재고 피벗테이블 값표시형식 변경
수량을 클릭하여 값 필드설정으로 이동한 뒤, 표시형식을 ‘날짜별 누계’로 변경합니다.
필드 항목
열 필드 날짜
행 필드 제품구분, 제품명
값 필드 수량 (표시형식 : 날짜별 누계)

수량의 표시형식을 ‘날짜별 누계’로 변경합니다.

잔여재고 피벗테이블 완성
각 일별로 잔여 재고수량 변화를 피벗테이블을 통해 손쉽게 출력할 수 있습니다.
4.9 13 투표
게시글평점
guest
19 댓글
Inline Feedbacks
모든 댓글 보기
sseung ss
sseung ss
2019년 10월 6일 3:23 오후

안녕하세요., 선생님 강의는 최고에요! 혹시 궁금한 부분이 있는데요, 서로 다른 sheet에서 시계열 자료를 합쳐야하는 경우가 있는데요. 이때 손쉽게 데이터 정규화가 필요한 경우는 어떻게 해야하나요? 문제는 열의 속성을 잘못 지정을 해서,… 더보기 »

sseung ss
sseung ss
2019년 10월 6일 3:23 오후

안녕하세요., 선생님 강의는 최고에요! 혹시 궁금한 부분이 있는데요, 서로 다른 sheet에서 시계열 자료를 합쳐야하는 경우가 있는데요. 이때 손쉽게 데이터 정규화가 필요한 경우는 어떻게 해야하나요? 문제는 열의 속성을 잘못 지정을 해서,… 더보기 »

오빠두엑셀
오빠두엑셀
2019년 10월 12일 3:57 오후
답글 남기기  sseung ss

안녕하세요? 오빠두엑셀입니다. 🙂 이미지를 보니.. 행열 전환시 맨 위에 있는 값들은 반복해서 출력해주고 싶으신건가요? 가장 단순한 방법으로 생각되는것은 size 열 각 그룹의 첫번째 값만 채워주세요. 그럴경우 0.350um 한번 .. 밑으로는… 더보기 »

림수
림수(@1515)
2020년 4월 13일 10:13 오후
게시글평점 :
     

감사합니다!

토우카
토우카
2020년 4월 15일 3:44 오후
게시글평점 :
     

감사합니다. 너무 빨라서.. 자세히 보아야 겠네요

토우카
토우카(@1455)
2020년 4월 15일 3:51 오후
게시글평점 :
     

너무 빨라서.. 자세히 공부해야 하겠네요..
보통 Table로 많이 사용하죠...
정규화하면 피벗사용하기도 좋긴한데... 또 Data로 다르게 활용도 할 수 있고....
시간이 필요하지만... 감사합니다.

jgkim32504
jgkim32504
2020년 4월 18일 9:31 오후
게시글평점 :
     

안녕하세요 강사님 강의를 너무 잘보고 있습니다. 좋은강의 감사합니다^^

아이둘
아이둘(@aidulnaver-com)
2020년 5월 5일 11:58 오전
게시글평점 :
     

자료 자체를 정규화된 형태로 구성을 해서 사용할 일이 없는데, 정규화가 필요한 형태로 만들어진 것에 아주 유용하겠네요. 좋은 자료 감사합니다.

그린 마운틴
그린 마운틴(@vicsi-1hanmail-net)
2020년 5월 11일 2:34 오후
게시글평점 :
     

좋은 자료 감사합니다

엑린이
엑린이(@1125)
2020년 5월 31일 5:15 오후
게시글평점 :
     

감사합니다

jasmine
jasmine(@jasmine2daynaver-com)
2020년 6월 3일 9:36 오전
게시글평점 :
     

강의 항상 잘보고있어요~~

m6789****
m6789****(@m67895046naver-com)
2020년 8월 24일 3:25 오후
게시글평점 :
     

감사합니다. 너무 빨라서.. 자세히 보아야 겠네요

David
David
2020년 12월 7일 10:29 오전
게시글평점 :
     

최고에요 ㅎㅎ

Last edited 1 년 전 by David
희찬
희찬(@bnsuk7hanmail-net)
2021년 1월 3일 4:56 오후
게시글평점 :
     

많이 배우고 갑니다 감사합니다

총장
총장(@-5286)
2021년 3월 5일 6:00 오후
게시글평점 :
     

환상 입니다 ㅠ

날
(@jnr1229kakao-com)
2021년 3월 28일 3:27 오후
게시글평점 :
     

1일자 증가가 아니라 월말로 증가할때는 어떻게 더해야할까요.

우리우리누리
우리우리누리(@dmswlqopnaver-com)
2021년 4월 29일 5:41 오후
답글 남기기 

방법찾으셨나요? 월증가는 어떻게 하는지 아시나요?

러스티
러스티(@minsky13naver-com)
2021년 4월 30일 1:36 오후
답글 남기기 

eomonth 함수 이용해보세요

이창원
이창원(@pringpanaver-com)
2021년 7월 3일 1:17 오전
게시글평점 :
     

엘셀의 마법사 오빠두 감사합니다.

19
0
여러분의 생각을 댓글로 남겨주세요.x