강의소개 :: 엑셀 데이터 정규화 2탄 :: 2/3차 정규화 기초
이번 강의에서는 데이터 2차/3차 정규화에 대해 알아봅니다. 1차 정규화 강의를 못보고 오신 분은 이전 강의를 참고해주세요.
2차 정규화 이상의 작업은 데이터베이스(DB)단에서 작업을 하는것이 보편적입니다. 따라서 데이터 2차/3차 정규화는 일반적으로 ERP나 POS와 같은 프로그램에서 이루어지는 작업입니다. (엑셀은 1차 정규화만 진행해도 대부분의 경우 문제없이 사용할 수 있습니다!)
다만, 상황에따라 엑셀안에서 많은 데이터를 관리해야 할 때가 있습니다. 그럴경우, 1차 정규화만으로는 체계적인 데이터 관리가 어려울 때가 종종 있는데요. 이번 강의에서는 그런 상황에 대비하여 데이터 2차/3차 정규화가 무엇인지 그 기초를 알아보고, 엑셀에 어떻게 적용하는지 실전예제를 통해 알아보겠습니다.
더불어 엑셀의 표기능과 같이 사용하면 아주 유용한 함수인 LOOKUP 함수 사용법도 같이 알아봅니다.
다시 강조드리자면, 엑셀은 각 테이블의 관계 형성을 기반으로 한 데이터 구축이 어려운 툴 입니다. (파워피벗/파워쿼리를 이용하여도 속도나 효율성 측면에서 기존 데이터베이스 대비 많이 부족합니다.) 따라서, 각 테이블의 관계가 3단계 이상으로 올라가는 데이터구조라면, SQL 또는 엑세스와 같은 데이터 관계형성을 기반으로 한 데이터관리 프로그램 사용을 권장합니다.
영상강의
예제파일 다운로드
일부 예제파일은 오빠두엑셀 홈페이지 회원에게만 제공됩니다.
- [기초레벨업] 엑셀 데이터 2차/3차 정규화예제파일
다음 강의 바로가기 👇👇
"세로방향 블록쌓기(정규화)"와 엑셀 데이터 관리 3가지 규칙에 대해 알아봅니다.
1. '정규화' 너무 어려워하지 마세요!
1-A. 정규화는 글로 배우는 것이 아닙니다. 직접 해보면서 배우는 것이 가장 빠릅니다.
이번 강의에서는 2단계, 3단계 정규화에 대해 알아봅니다. 2차/3차 정규화는 엑셀을 효율적으로 다루고자 한다면 '되도록이면' 이해하는 것이 좋습니다.
하지만 별도로 사용하는 ERP 프로그램이 있고, 해당 프로그램에서 원본데이터(Raw Data)를 받아 엑셀로 처리하는 경우가 대부분이라면, 정규화 1단계(중복된 데이터 제거)만 이해하셔도 엑셀에서 문제없이 작업하실 수 있습니다.
1-B. 하지만 엑셀도 점점 진화하고 있습니다.
이제 엑셀은 '스프레드시트'의 기준점을 넘어서 데이터베이스로도 이용할 수 있는 프로그램으로 진화하고 있습니다. 파워쿼리 및 파워피벗의 기능은 앞으로 점점 진화할 것입니다.(반면, MS Access (엑세스) 프로그램은 2013버전부터는 업데이트가 중단된 것이 기정 사실입니다.)
오피스 365 및 엑셀 2019 부터는 동적배열함수가 추가되었고, 사용자 편의기능 또한 꾸준히 개선되고 있습니다. 이제 엑셀을 잘 쓴다는 것은 수식/공식을 잘 쓰는 것이 아닌, 데이터를 체계적으로 관리하면서 처리하는 것으로 바뀌어가고 있습니다. 즉, 데이터만 잘 갖추어져 있다면, 엑셀의 기본함수와 기본기능을 통해 다양한 방식의 데이터 분석이 가능합니다.
2-A. 1차 정규화 후 남아있는 문제점 알아보기
아래 표를 보면, 물품명과 물품분류는 서로 '종속'되어 있습니다. 즉, 물품명이 바뀔 경우 물품분류는 해당 물품에따라 자동으로 갱신되어야 합니다. 따라서, 데이터 관점에서는 하나의 데이터 변경이 있을시 두번의 작업이 이루어지고, 이 종속된 데이터의 갯수가 많아질수록 변경 작업은 기하급수적으로 증가하게 됩니다.
물품명과 물품분류는 서로 종속되어 있습니다. 물품명 정보가 변경되면 물품분류 정보도 같이 변경되줘야 합니다. 
관계도를 보시면 위와 같습니다. 2-B. 종속된 데이터 사용시, 엑셀에서 어떤 문제가 있나요?
엑셀에서는 종속된 데이터 처리를 위해 VLOOKUP 함수, LOOKUP 함수, MATCH/INDEX 함수 등을 사용합니다.하지만 이렇게 함수를 사용할 경우에 아래 3가지 문제점이 있습니다.
- 파일 크기가 비약적으로 커집니다.
- 참조하는 값을 변경할 시, #N/A 오류가 발생할 수 있습니다.
- 파일 크기에 따라 데이터 처리속도가 느려질 수 있습니다.
따라서 많은 양의 데이터를 다룰 경우, 효율적인 데이터 관리를 위해 아래 2차/3차 정규화를 고려할 수 있습니다.
3-A. 정규화데이터를 진행한 거래처별 제품 목록표
2차 정규화를 진행하면 아래와 같이 데이터가 분리되며, 각각의 흐름은 아래 설명과 같습니다.
[거래처 납품 제품] 표에서 첫번째 항목인 [물품명ID]를 1에서 2로 바꿀경우, 물품분류는 [물품명] 테이블에서 자동으로 갱신됩니다. 4-A. 데이터 처리과정의 3단계
이전 강의에서, 데이터 처리과정은 삽입/변경/삭제 3단계로 이루어진다고 말씀드렸습니다.
- 데이터 삽입의 문제점 : 1차 정규화로 해결
- 데이터 변경의 문제점 : 2차 정규화로 해결
하지만 "데이터 삭제는 2차 정규화를 진행해도 완벽히 해결되지 않습니다" 아래 예제를 같이 보겠습니다.
4-B. 데이터 삭제시 발생하는 문제점
예를들어, 동서식품에서 코카콜라를 더이상 납품받지 않게 되어, 거래처명ID 6번 항목을 삭제한다고 가정해보겠습니다.
이 경우, 우리는 동서식품의 코카콜라 항목을 삭제하고 싶었지만, 회사에서 아직 잘 근무하고있는 '이경규'라는 직원의 데이터까지 동시에 삭제가 됩니다.
같은 원리로, 7번 항목인 롯데푸드의 데이터 삭제 시, 롯데푸드라는 거래처와 함께 정준하 직원의 데이터가 같이 삭제되는 문제가 있습니다.
특정 데이터를 삭제할 경우 원치 않는 데이터까지 같이 영구적으로 삭제가 됩니다. 3차 정규화는 '독립적인' 데이터를 그룹으로 나눠, 각기 다른 테이블로 나눠주는 작업입니다. 3차 정규화가 최종적으로 이루어진 표는 아래와 같습니다.
3차 정규화가 이루어진 최종 테이블의 구조는 위와 같습니다. 거래처별/제품별/담당자가 독립적인 테이블로 나눠졌습니다. 5-A. 독립적인 데이터를 나눠주는 이유는 무엇인가요?
우리가 보고 있는 데이터의 목적은, '거래처별로 납품받는 제품을 각 회사 담당자별로 구분'하는 것입니다. 따라서 해당 데이터에는 총 3개의 데이터 그룹이 필요합니다.
- 거래처별 데이터
- 제품별 데이터
- 담당자별 데이터
위 3개의 데이터 그룹이 독립적으로 존재하고, 제품별 데이터의 경우 물품분류와 물품명이 종속될 것입니다.
만약 거래처별로 거래처 구분을 따로 둬서, '제빵류 거래처', '음료수 거래처'로 구분을 둔다면 거래처구분도 거래처명 데이터와 종속적인 관계를 맺게 되겠죠?
이렇게 데이터를 독립적으로 나누게 되면 데이터를 관리가 훨씬 수월해지고, 데이터 파일 크기도 가벼워지는 장점이 있습니다.
6-A. Lookup 함수 2가지 형태
LOOKUP 함수는 2가지 형태로 '벡터형'과 '배열형'이 있습니다. 형태는 중요하지 않으며, 아래 공식 하나만 기억하셔도 충분합니다.
= LOOKUP ([참조하고자 하는 값], [참조할 범위], [값을 찾을 범위])
LOOKUP 함수의 사용법은 간단합니다. 아래 표를 예시로 사용법을 살펴보겠습니다.
LOOKUP 함수를 이용해서 데이터를 참조하는 방법을 보기위한 예제테이블입니다. - 표에 사용되는 기호 연산자 설명
- [@열이름] : 선택된 셀과 같은 행의 하나의 값을 참조합니다.
- 표[열이름] : 표의 전체 열을 참조합니다. - [물품명]에 들어간 함수
= LOOKUP ( [@물품명ID] , tbl물품명[물품명ID] , tbl물품명[물품명] )
- [담당자명]에 들어간 함수
= LOOKUP ( [@담당자ID] , tbl담당자[담당자ID] , tbl담당자[담당자명]) - [직통번호]에 들어간 함수
= LOOKUP ( [@담당자ID] , tbl담당자[담당자ID] , tbl담당자[직통번호]) - [물품분류]에 들어간 함수
= LOOKUP ( LOOKUP ( [@물품명ID], tbl물품명[물품명ID], tbl물품명[물품분류ID] ), tbl물품분류[물품분류ID], tbl물품분류[물품분류] )
7. Lookup함수 사용시 주의점
1. [참조할범위]는 반드시 '오름차순'으로 정렬되어 있어야 합니다.
2. LOOKUP 함수는 '#N/A' 값 (찾는 값이 없음)을 반환하지 않습니다.
LOOKUP 함수는 참조값이 참조범위에 없을 시, 참조값에 가장 근접한 작은 값을 반환하거나, 참조값이 가장 작은 값일 경우 #N/A를 반환합니다.
예를 들어 '10'이라는 값이 참조범위에 없을경우, 10,9,8,7... 순으로 그보다 작은 값을 차례대로 찾습니다. 그러다 값이 있으면 그 값을 반환하고, 0까지 가도 없을 경우에는 #N/A 값을 반환합니다.
즉, 참조범위에 찾을값이 없음에도 불구하고 에러값이 안나온다는 점은 '참조함수'에서는 매우 치명적인 문제이나, 우리가 이야기하는 데이터베이스에서는 발생할 수 없는 문제이므로 고려하지 않습니다.
[링크] LOOKUP 함수 설명 - MS 홈페이지 링크
8. LOOKUP 함수와 테이블을 같이 이용할 경우 매우매우 편리합니다.
8-A. '=테이블이름[열이름]'을 이용해서 간단하게 범위 지정이 가능합니다.
VLOOKUP 함수나 INDEX/MATCH 함수는 사용자가 범위를 기억하고 지정해야 합니다. 하지만 표기능과 LOOKUP함수를 같이 사용하면, 코딩하듯이 열 이름으로 범위를 참조하여 수식을 입력할 수 있습니다. (오피스 365 사용자일 경우, XLOOKUP 함수를 사용하면 열이름을 참조하여 수식을 입력할 수 있습니다.)
8-B. 참조범위 왼쪽으로도 조회 가능합니다.
VLOOKUP 함수는 참조범위 오른쪽으로만 조회 가능합니다. 즉 찾을범위가 참조범위에 맨 왼쪽에 위치할때만 함수를 사용할 수 있습니다. 반면 LOOKUP함수는 참조범위의 왼쪽/오른쪽 모두 조회 가능하므로, 찾을범위 위치 상관없이 함수를 사용할 수 있습니다.
8-C. 참조하는범위/찾는범위의 열위치를 마음껏 변경가능합니다.
VLOOKUP 함수는 3번째 인수로 '열번호'가 고정값으로 입력됩니다.(MATCH 함수나 COLUMNS 함수를 사용하여 자동으로 열번호가 추적되도록 할 수도 있습니다.)
따라서 VLOOKUP 함수의 열번호를 고정값이고, 출력범위의 위치가 변경된다면, VLOOKUP 함수는 옳지않은 결과를 반환합니다. VLOOKUP 함수에 대한 자세한 설명은 여기를 참고하세요.
