데이터 정규화 함수공식

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

엑셀로 보고서를 자주 작성하는 분이라면, 한 번쯤은 아래와 같은 의문을 가져보셨을 것입니다.

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

이전 강의에서 여러 차례 강조해 드렸듯이, 엑셀은 [스프레드시트 프로그램]입니다. 즉, ‘데이터 관리’에 중점을 두고 사용해야 하는 프로그램이라는 뜻입니다.

따라서 ‘엑셀 함수나 기능을 능숙하게 다루는 것’ 보다도 ‘데이터를 체계적으로 관리하는 능력’이 엑셀을 더 효율적으로 활용하는 핵심 역량입니다. 데이터가 잘 정리되어 있으면 어려운 함수나 기능 없이도 몇 번의 클릭만으로 원하는 결과를 손쉽게 도출할 수 있습니다.

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

엑셀은 다양한 업무 분야에서 활용되는 무궁무진한 활용도를 가진 프로그램입니다. 사무직 근로자라면 사용 빈도의 차이만 있을 뿐, 엑셀을 한 번도 사용하지 않는 직장인은 거의 없다고 해도 과언이 아닙니다.

데이터 정규화 개념에 익숙하지 않은 사용자는 대부분 아래 그림처럼 ‘가로로 나열된 데이터 형태’로 표를 작성하여 관리합니다. 특히 매출/매입 관리, 재고 입출고 대장과 같이 ‘일자별’ 또는 ‘월별’로 시간의 흐름에 따라 관리되는 자료는 가로 방향으로 항목을 펼쳐서 작성하는 경우가 많습니다.

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

하지만 이러한 데이터가 누적될수록,
언젠가는 데이터를 취합하고 분석하는 단계에서 큰 어려움에 직면하게 됩니다.

이때 엑셀의 VBA나 별도로 맞춤 제작된 추가 기능을 활용하면 데이터를 정규화하여 관리할 수 있지만, 이는 일부 숙련된 사용자에게 한정되는 방법입니다. 실제로 대다수의 사용자는 가로로 나열된 데이터를 정규화할 때 손으로 한 셀씩 옮기는 작업을 반복하게 됩니다.

데이터를 일일이 손으로 옮겨 정규화할 경우 시간이 많이 소요될 뿐만 아니라, 무엇보다도 사용자의 실수로 인한 휴먼 에러가 발생할 가능성이 매우 높아집니다.

이러한 문제를 해결하기 위해 아래 소개해 드리는 ‘데이터 정규화 공식’을 활용하면 보다 편리하고 정확하게 데이터를 정규화할 수 있습니다.

위 데이터 정규화 공식의 동작 원리에 대한 상세한 설명은 아래 관련 포스트를 참고하시기 바랍니다.

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

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

1. 예제파일의 [재고입출고관리대장] 시트로 이동합니다.
2. 제품구분과 제품명이 각 날짜별로 입고·출고·잔여 3개 항목으로 구분되어 입력되어 있는 것을 확인할 수 있습니다.
   * 여기서 ‘잔여’ 수량은 입고 수량과 출고 수량을 통해 자동으로 계산되는 값이므로, 정규화된 데이터에서는 별도로 보관할 필요가 없습니다. 따라서 정규화 이후 자동 필터를 적용하여 잔여 행만 일괄 삭제합니다.
   * 1월 입출고가 시작되기 전의 ‘당월 기초’ 수량은 1월 1일 이전에 입고된 수량이므로, 2018년 12월 31일에 입고된 수량으로 간주하여 정규화 데이터에 입력합니다.

   

3. 시트의 B30셀로 이동한 뒤, 아래 그림과 같이 표의 머리글을 작성합니다.
4. 머리글 작성을 완료했으면, 아래 표를 참고하여 기존 입출고 관리대장의 데이터를 정규화된 표 형태로 옮겨 줍니다.
   (데이터를 효율적으로 옮기는 방법은 영상 강의에서 자세히 다루고 있습니다)

   항목	설명
   날짜	12월 31일 기초재고 수량을 시작으로, 1월 1일부터 1월 31일까지 하루씩 증가하면서 날짜를 입력합니다.
   제품구분/제품명	각 제품구분과 제품명을 반복하여 입력합니다. 한 날짜당 입고·출고·잔여 항목별로 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)

   

9. 공식을 자동 채우기로 적용하면 데이터 정규화가 완료됩니다. 데이터가 올바르게 입력되었는지 확인한 뒤, 문제가 없으면 전체 범위를 복사 ⇨ 선택하여 붙여넣기를 통해 값으로만 붙여넣기하여 추후 수식 오류가 발생하지 않도록 처리합니다.
10. 입고·출고·잔여 수량 중 잔여 수량은 정규화된 데이터에서는 불필요한 값이므로 삭제합니다. 데이터 범위를 선택한 뒤 키보드 CTRL + SHIFT + L 키를 눌러 필터를 적용합니다. 이후 잔여 항목만 선택한 뒤 해당 행 전체를 삭제합니다.
11. 이후의 계산 편의를 위해 입고는 ‘양수(+)’로, 출고는 ‘음수(-)’로 수량 값을 변환합니다. IF 함수를 사용하여 G31셀에 아래 공식을 입력한 뒤 자동 채우기를 적용합니다. 값이 정확히 입력되었는지 확인한 뒤, 복사 - 선택하여 붙여넣기를 통해 E열에 값으로만 붙여넣기합니다.
    =IF(E31="입고",F31,F31*-1)

    

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

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

    

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

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

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

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

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

잔여수량을 보여주는 피벗테이블 만들기

아래 표를 참고하여 피벗테이블의 행·열·값 필드 항목을 배치합니다.

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