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

엑셀 데이터 1차 정규화 이론과 1차 정규화 된 데이터를 기반으로 한 피벗테이블 기초 사용법을 살펴봅니다.

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

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

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

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

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

영상강의
큰 화면으로 보기

예제파일 다운로드

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

  • [기초레벨업] 엑셀 데이터 1차 정규화 기초
    완성파일
  • [기초레벨업] 엑셀 데이터 1차 정규화 기초
    E-Book

.

다음 강의 바로가기 👇👇

1차 정규화에 이어서 2,3차 정규화란 무엇인지 실전 예제와 함께 하나씩 살펴봅니다.


1. 정규화란 무엇인가요?

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

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

엑셀 정규화 기초
정규화의 기본적인 흐름을 설명한 흐름도. 영어로는 'Data Normalization' 이라고 합니다.
정규화는 공식적으로 총 6단계까지 존재합니다.

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

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

엑셀 정규화 기초 설명
정규화 3단계의 흐름과 각 단계에서의 목적은 위와 같습니다.

2. 정규화 되어있지 않은 표 살펴보기

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

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

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

엑셀 정규화 기초 1
정규화 되지 않은 거래처별 제품 목록 표 입니다.

문제점1. 열의 순서가 바뀌면 표를 인식하는데 문제가 발생

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

엑셀 정규화 기초 문제점1
담당자 열의 위치를 바꾸었더니 어떤 담당자가 어떠한 물건을 담당하는지 이해하는데 문제가 발생합니다.

문제점2. 데이터 정렬하는데에 문제가 발생

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

엑셀 정규화 기초 문제점2
공급가별로 자료를 정렬하고 싶지만, 공급가 항목이 중복되어 있기 때문에 전체 항목에 대해서 한번에 정렬이 불가능 합니다. 한번에 모든 값을 정렬하기 위해서는 사용자가 임의로 자료를 취합하는 작업을 해줘야 합니다.

문제점 3. 데이터 삽입에 문제가 발생

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

엑셀 정규화 기초 문제점3
데이터를 삽입하는데 문제가 발생합니다. 온전한 자료 관리를 위해서는, '열'의 갯수는 고정한 채 '행'(아래로)으로만 자료를 추가하는 방법으로 관리하는 것이 좋습니다.

3. 문제점 해결을 위한 1차 데이터 정규화

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

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

엑셀 정규화 기초 1차 정규화
기존 테이블의 문제점 3가지를 1차 정규화 한번으로 해결 할 수 있습니다.
  • [문제점 1] 해결: 열의 순서가 바뀌어도 표를 이해하는데 문제가 없게 됩니다.
  • [문제점 2] 해결: 각 열 별로 모든 행에 대하여 오름차순/내림차순 정렬을 할 수 있습니다.
  • [문제점 3] 해결: 새로운 데이터 삽입 시, 열의 구조는 그대로 유지한 채 아래로만 데이터를 삽입합니다.

4. 1차 정규화 된 표를 바탕으로 피벗테이블 작성

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

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

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

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

엑셀 정규화 피벗테이블 1
피벗테이블로 변경하고자 하는 표 (1차 정규화가 진행된) 를 머리글까지 포함하여 선택합니다. (마우스로 드래그 또는 Ctrl + A 두번 !)

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

엑셀 피벗테이블 입력
피벗테이블 입력을 위해 마우스로 버튼을 클릭하거나, 키보드 단축키를 이용해 피벗테이블 만들기 창을 불러옵니다.

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

피벗테이블 만들기
피벗테이블 만들기 창이 뜨면, [확인]버튼을 눌러줍니다. [데이터모델에 이 데이터 추가] 기능은 차후에 [파워쿼리] 기능을 이용할 때 쓰는 버튼으로, 시트위에 자료를 표시하지 않고 데이터모델을 따로 생성하여 '자료처리' 만을 목적으로 관리하기 위해 추가하는 것을 이야기합니다.

4.8 49 투표
게시글평점
guest
57 댓글
Inline Feedbacks
모든 댓글 보기
정건영
정건영
2020년 1월 10일 7:46 오전
게시글평점 :
     

좋은 강의 감사합니다

DJDJ
DJDJ
2020년 2월 5일 6:43 오후
게시글평점 :
     

정말 많이 배웁니다.

환경랑
환경랑
2020년 2월 10일 6:35 오후
게시글평점 :
     

감사합니다.

야나두엑셀
야나두엑셀
2020년 2월 16일 12:44 오후
게시글평점 :
     

정규화 된 데이터 중 거래처명 ID..! 중요한건 아니지만여 ㅎㅎ

제이피이
제이피이
2020년 2월 16일 7:01 오후
게시글평점 :
     

좋은 내용 감사드립니다

바보상자
바보상자
2020년 2월 17일 3:12 오후
게시글평점 :
     

굿~~

쑤기얌
쑤기얌
2020년 2월 17일 4:01 오후
게시글평점 :
     

중간쯤(3:49)에 해당 열 선택에서 말씀은 Ctrl+shift(2)번 이라고 했는데... 화면에는 Ctrl+space(2)번이라고 나와있어욤...

쑤기얌
쑤기얌
2020년 2월 17일 4:06 오후
게시글평점 :
     

좋은정보 감사합니다....^^

호야다
호야다
2020년 2월 18일 8:24 오후
게시글평점 :
     

좋은 강의 감사합니다.

ucc
ucc
2020년 2월 23일 9:06 오전
답글 남기기  호야다

놀랍습니다

찰스킴
찰스킴
2020년 3월 19일 1:08 오후
게시글평점 :
     

감사합니다. 최고예요

항상평안
항상평안
2020년 3월 20일 1:08 오후
게시글평점 :
     

강의도 군더더기 없이 깔금하게 너무 잘하시네요 . 감사합니다!

MAXSON
MAXSON
2020년 3월 21일 2:05 오후
게시글평점 :
     

감사합니다.

kbooks94
kbooks94
2020년 3월 21일 5:05 오후
게시글평점 :
     

알찬 강의 감사합니다

sksnadl
sksnadl
2020년 3월 22일 6:44 오전
게시글평점 :
     

좋은 내용 감사 합니다.

형들아빠
형들아빠
2020년 3월 22일 10:25 오전
게시글평점 :
     

좋은 강의 감사합니다.

붉은곰
붉은곰
2020년 4월 6일 6:45 오후
게시글평점 :
     

감사합니다

사람만들기
사람만들기
2020년 4월 7일 1:42 오후
게시글평점 :
     

와...
잘배우고 갑니다.

고재봉
고재봉
2020년 4월 7일 10:31 오후
게시글평점 :
     

잘봤읍니다.~~~`

JMON
JMON
2020년 4월 10일 8:35 오전
게시글평점 :
     

하... 데이터 정규화는 역시 어렵네요

고재봉
고재봉
2020년 4월 13일 9:24 오후
게시글평점 :
     

감사합니다.

엑린이
엑린이
2020년 4월 19일 6:30 오후
게시글평점 :
     

감사합니다

초롱아비
2020년 4월 25일 12:24 오후
게시글평점 :
     

감사합니다. 엑셀을 통해 뇌의 노화를 막아보려 합니다 ^^

Novrain
Novrain
2020년 5월 6일 2:59 오후
게시글평점 :
     

감사합니다. 피상적으로 데이터 정리를 해야 피벗 기능을 이용할 수 있다고 알고 있었는데, 정규화라는 개념을 바탕으로 데이터를 설계한다는 접근이 인상적입니다. 도움이 많이 되었습니다

윤영숙
2020년 5월 28일 1:34 오전
게시글평점 :
     

최고예요!!

피피포포
피피포포
2020년 6월 4일 10:08 오후
게시글평점 :
     

잘 봤습니다~

하니
2020년 6월 20일 5:59 오후
게시글평점 :
     

감사합니다. 실무에 도움이 되는 강의였습니다.

사상의창
사상의창
2020년 7월 20일 11:37 오후
게시글평점 :
     

항상 감사드립니다^^

맥스
맥스
2020년 7월 25일 8:55 오후
게시글평점 :
     

이해하기 쉽게 설명해주셔서 감사합니다.

hae****
hae****
2020년 7월 30일 2:43 오후
게시글평점 :
     

제가 완전 초보라서요.... 선생님의 설명을 여러번 들어도 잘 이해가 안가네요...ㅠㅠㅠ 6분 19초에서 어떻게 그다음 단계로 가는지 잘 모르겠어요? 비정규화테이블에서 정규화테이블로 어떻게 바뀐 것인지??? 그리고 갑자기 오른쪽을 누르면 +가 나온다고 했는데....… 더보기 »

hae****
hae****
2020년 7월 30일 2:59 오후
게시글평점 :
     

저는 주신 예제를 풀때 각각 거래처ID,거래처명, 거래처위치를 옆에 있는 열에 복사해서 넣고 각각 열이 같도록 한다음 전체 행을 다시 복사해서 아래로 붙여넣기를 했는데.... 뭔가 다른 방법이 있을 것 같아요...

hae****
hae****
2020년 7월 30일 3:09 오후
게시글평점 :
     

제가 풀어본 예제가 맞았는지 어떻게 확인할 수 있을까요?

워터멜로나
워터멜로나
2020년 8월 13일 7:48 오후
게시글평점 :
     

감사합니다!

켄타로
켄타로
2020년 9월 17일 10:17 오후
게시글평점 :
     

좋은 강의 감사합니다.

yim****
yim****
2020년 10월 14일 6:45 오전
게시글평점 :
     

감사합니다

영덜이
영덜이
2020년 10월 20일 1:14 오후
게시글평점 :
     

좋은 강의 감사합니다!

미성
미성
2020년 10월 22일 4:01 오후
게시글평점 :
     

잘 배우고 갑니다

Yooon
Yooon
2020년 11월 5일 5:07 오후
게시글평점 :
     

선생님 정말 감사합니다 🙂 정주행할게요 취뽀해서 제가 효도하겠습니다

san****
san****
2020년 11월 18일 7:45 오전
게시글평점 :
     

좋은 강의 감사합니다.

강안개
강안개
2020년 11월 26일 1:43 오후
게시글평점 :
     

너무 감사합니다.

오우진
오우진
2020년 11월 30일 6:20 오후
게시글평점 :
     

진짜 레전드강의

추동박사
추동박사
2020년 12월 25일 12:32 오후
게시글평점 :
     

좋은 강의 감사합니다!

JMT
JMT
2020년 12월 28일 3:23 오후
게시글평점 :
     

감사합니다!!

희찬
희찬
2021년 1월 3일 4:50 오후
게시글평점 :
     

잘 활용하겠습니다

k****
k****
2021년 1월 15일 9:06 오후
게시글평점 :
     

감사합니다.

한반장
한반장
2021년 1월 29일 3:08 오후
게시글평점 :
     

감사합니다. 저도 업무에서 희한하게 작성된 자료를 이용해야 되는데...
이제 첫걸음을 내딛은 기분입니다.

pear****
pear****
2021년 2월 3일 3:28 오후
게시글평점 :
     

좋은 강의 감사합니다.

배움
배움
2021년 2월 7일 10:25 오전
게시글평점 :
     

설마 정규화가 +만 눌러도 되는 간단한 것은 아니지요?

박천호
박천호
2021년 2월 8일 11:52 오전
게시글평점 :
     

좋은 글 감사하빈다.

loobyloo
loobyloo
2021년 2월 27일 9:05 오후
게시글평점 :
     

무료 자료 다운받아 영상보고 따라하고 있어용~~
유튜브로 눈팅만 하다 따라 해보며 미숙한 실력이 좀씩 늘고 있겠죠
좋은 강의 감사합니다.

비너스
비너스
2021년 3월 18일 1:08 오후
게시글평점 :
     

감사합니다.

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