엑셀 데이터 정규화 기초 1탄 :: 피벗테이블 효율 높이기

강의소개 :: 엑셀 데이터 정규화 1탄 :: 1차 정규화 

엑셀 데이터 정규화 목차 바로가기

이번 강의에서는 엑셀을 엑셀답게 사용하기 위하여 반드시 알아야 할 필수 스킬! 데이터 정규화에 대해 알아봅니다. 엑셀로 많은양의 자료를 다루고 데이터의 효율적인 관리가 필요하다면, 데이터 정규화, 특히 1차 정규화는 반드시 숙지하셔야 합니다.

따라서 이번 강의에서는 '데이터 1차 정규화' 작업과, 엑셀에서 제공되는 가장 강력한 기능중 하나인 '피벗테이블'의 기초 사용법을 알아봅니다. 엑셀 정규화가 많이 익숙하지 않으시더라도, 1차 정규화 기본만 이해한다면 피벗테이블의 효율성을 200%이상 끌어올릴 수 있습니다.

영상강의

예제파일 다운로드

일부 예제파일은 오빠두엑셀 홈페이지 회원에게만 제공됩니다.

정규화는, 엑셀을 포함한 모든 스프레드시트 또는 관계형데이터베이스에서 데이터를 설계하는 과정을 이야기합니다. 궁극적으로 '데이터의 테이블 (엑셀에서의 각 시트)'를 최대한 작고 잘 조직되도록 관계를 만들어 주는 과정을 데이터 정규화라고 합니다. 데이터 정규화에 대한 상세 설명은 아래 링크를 참고해주세요.

[링크] 데이터베이스 정규화 이해하기

정규화는 공식적으로 총 6단계까지 존재합니다.

하지만 3단계까지만 진행하여도 '정규화되었다' 라고 보편적으로 이야기하며, 정규화 3단계만 진행해도 문제없이 데이터 관계 형성이 가능합니다. 따라서 이번강의와 다음강의에서는 데이터의 3단계 정규화 과정을 알아봅니다.

엑셀로만 데이터를 관리한다고 하여도, 엑셀 피벗테이블을 더욱 효율적으로 사용하기 위해서는 1차 정규화의 개념만큼은 반드시 이해하시길 권장합니다.

2-A. 거래처별 제품 목록

1차 정규화를 알아보기 위해 각 거래처별 제품목록 테이블을 살펴보겠습니다. 1차 정규화의 가장 큰 목적은 '중복된 데이터 제거' 입니다. 흔히 피벗테이블 이용시 발생하는 문제점의 절반 이상은 중복되는 데이터에서 발생을 하는데요. 그 문제점을 알아보겠습니다.

아래 정규화 되지 않은 테이블을 보시면, 각 담당자/직통번호/물품분류 등의 중복되는 데이터가 존재하는 것을 알 수 있습니다.

담당자 열을 다른 위치로 옮겨 보았습니다. 담당자 열의 위치가 바뀌니 어느 담당자가 어떠한 물품을 관리하는지 이해하는데에 문제가 발생합니다.

공급가 데이터가 중복되어 존재하기 때문에 모든 공급가에 대해서 오름차순 또는 내림차순으로 정렬하는데에 문제가 발생합니다. 모든 공급가에 대해 정렬하기 위해서는, 사용자가 임의로 자료를 취합한 뒤 자료를 정렬해야 합니다.

아래 표에서, ID가 5번인 오뚜기의 경우는 우측에 자리가 비어있으므로 새로운 물품이 추가 될 경우 우측에 데이터를 삽입하면 됩니다.
반면 ID가 1번인 남양유업의 경우 새로운 데이터 삽입이 필요할 경우 어떻게 자료를 입력해야 할까요? 우측에 새로운 열을 추가해서 삽입해야 할까요? 아니면 아래에 새로운 행을 만들어 자료를 삽입해야 할까요?

3-A. 1차 정규화를 한 거래처별 제품목록

엑셀 정규화를 진행해보겠습니다. 아래는 1차 정규화가 진행된 표 입니다. 1차 정규화를 진행함으로써, 정규화 되지 않았던 표에서 발생했던 3가지 문제점을 해결할 수 있습니다.

엑셀에서 데이터 정규화 작업으로 얻을 수 있는 가장 좋은 장점은, 1차 정규화 한번으로 피벗테이블의 기능을 100% 활용 할 수 있게 된다는 점입니다. 피벗테이블을 이용하는 방법은 아주 간단합니다.

4-A. 엑셀 피벗테이블 삽입하기

1. 피벗테이블로 변경하고자 하는 범위를 선택합니다.

피벗테이블을 만들 시, 동적범위 또는 표로 만드는 것을 권장드립니다. 동적범위나 표를 이용할 경우 이후 새로 추가되는 데이터를 자동으로 인식하여 피벗테이블의 원본 데이터를 아주 쉽게 새로고침할 수 있습니다. 동적범위에 관련 강의는 링크에서 확인하세요.

2. [삽입] -> [피벗테이블] 버튼 클릭 (단축키 : Alt + N + V)

3. [피벗테이블 만들기] 팝업창이 뜨면, [확인] 버튼 클릭.