엑셀 데이터 관리 규칙, 정말 중요합니다! - 5분 핵심 요약

엑셀 함수보다 더 중요한 핵심 규칙! 편리한 엑셀 사용을 위한 데이터 관리 핵심 규칙 5분 요약!

# 엑셀시작하기 # 데이터분석 # 파워쿼리/파워피벗

작성자 :
오빠두엑셀
최종 수정일 : 2021. 11. 22. 23:20
URL 복사
메모 남기기 : (16)

엑셀 데이터 관리 규칙, 정말 중요합니다! - 5분 핵심 요약

엑셀 데이터 관리 규칙 목차 바로가기
영상 강의

큰 화면으로 보기

예제파일 다운로드

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

  • [기초레벨업] 엑셀 데이터 관리 핵심 규칙, 핵심 요약
    예제파일
  • [기초레벨업] 엑셀 데이터 관리 핵심 규칙, 핵심 요약
    완성파일

엑셀 데이터 관리 핵심 - 세로 방향 블록쌓기!

실무 엑셀에서 기본 함수나 피벗테이블로 해결할 수 없는 문제의 대부분은 '잘못된 데이터 구조'에서 발생합니다. 따라서 엑셀을 잘 다루려면 함수나 기능을 익히는 것 보다도 올바른 데이터 구조를 이해하는 것이 더욱 중요하고 선행되어야 하는데요.

엑셀 원본 데이터 이해 중요
원본 데이터를 이해하는 것이 엑셀 함수나 기능보다 더욱 중요합니다.

실무에서 반드시 지켜야 할 올바른 데이터 구조의 핵심 규칙 3가지는 아래와 같습니다.

  1. 머리글은 반드시 한 줄로 작성되어야 합니다.
  2. 병합된 셀이 없어야 합니다.
  3. 집계된 데이터가 없어야 합니다. (소계, 합계 등..)

위 3가지 규칙과 더불어 가장 우선적으로 지켜져야 할 규칙은 바로 '세로방향 블록쌓기' 인데요. 이 세로방향 블록쌓기 규칙에 대한 자세한 설명은 아래 영상 강의를 참고해주세요.

원본 데이터 관리 수준 이해하기

올바른 데이터 구조를 이해하는 것과 더불어, 본인의 업무에서 핸들링 할 수 있는 원본 데이터의 관리 수준을 이해하는 것도 매우 중요한데요. 예제와 함께 알아보겠습니다.

만약 '주 단위'로 관리하는 매출 데이터는 원본 데이터가 맞을까요? (상황에 따라 다르겠지만) 주 단위 데이터는 올바른 원본데이터라고 하기에는 많이 부족합니다. 가장 큰 이유는 바로, '집계된 데이터'이기 때문인데요.

엑셀 원본 데이터 관리 수준
주 단위로 집계된 데이터는 그 하위 단계 세부 정보를 확인할 수 없습니다.

집계된 데이터이기 때문에, 세부 단계인 일자별, 고객별 데이터를 검색하려면 더 하위 단계의 데이터가 필요합니다. 따라서 위 데이터의 올바른 원본 데이터는 아래와 같이 각 시간대별, 고객이 구매한 항목이 정리된 데이터라고 할 수 있습니다.

엑셀 데이터 관리 수준 이해
고객, 구매시간, 제품명 등이 정리된 데이터가 올바른 원본데이터 입니다.

데이터 관리 수준을 이해하지 못하면 어떤 문제가 발생할까요? 만약 평소 '주 단위' 데이터를 다루고있고, 특정 보고서 작성을 위해 하위 단계의 데이터가 필요할 경우, 데이터 관리 수준을 이해하지 못한다면 IT팀이나 상위부서에 올바른 요청을 하지 못하고 주어진 데이터 조차 제대로 활용하지 못하는 문제가 발생합니다.

엑셀 데이터 구조 보고서 요청
데이터 구조를 이해하면 시기 적절하게 필요한 데이터를 요청할 수 있습니다.

파워쿼리 데이터 정규화 (기초)

이번 강의에서는 입고 내역을 가로로 관리하는 잘못된 재고 관리 데이터를 세로방향 블록쌓기가 지켜진 올바른 데이터 구조로 변경하는 방법에 대해 알아보겠습니다. 파워쿼리 기초 및 기본 기능에 대한 자세한 설명은 아래 영상 강의를 참고해주세요.

만약 파워쿼리를 지원하지 않는 일부 버전 (엑셀 2013 이전, 엑셀 2016 Professional Plus 등..)일 경우, 엑셀 함수를 사용하는 데이터 정규화 관련 강의를 참고해주세요.

  1. 범위를 표로 변환하기 : 예제파일 실행 후, [실습] 시트로 이동합니다. 이후 데이터가 입력된 A1:O7 범위를 선택 후, [삽입] 탭 - [표] 를 클릭하거나, 단축키 Ctrl + T 를 눌러 표 만들기를 실행합니다. 표 만들기 대화상자가 나오면 '머리글 포함'을 체크 한 후 [확인] 버튼을 눌러 범위가 표로 변환됩니다.

    엑셀 범위를 표로 변환
    범위를 표로 변환합니다.
  2. 파워쿼리 실행하기 : 표를 선택 후, [데이터] 탭으로 이동합니다. 리본메뉴 왼쪽 '데이터 가져오기 및 변환' 그룹에서 [테이블 및 범위에서] 또는 [시트에서] 버튼을 클릭하면 파워쿼리 편집기가 실행됩니다.
    엑셀 파워쿼리 실행
    표를 선택 후, 데이터 - 테이블/범위에서(또는 시트에서)를 클릭합니다.
    오빠두Tip : 데이터 탭의 레이아웃은 사용 중인 엑셀 버전에 따라 조금씩 다를 수 있습니다. 버튼 위치는 조금씩 다르나, [테이블 및 범위에서] 버튼을 찾아 클릭하면 파워쿼리 편집기를 실행할 수 있습니다.
  3. 불필요한 데이터 제거하기 : 제품 목록 중 '합계'는 집계된 데이터입니다. 따라서 제품 필터를 클릭 후, '합계'를 목록에서 제거한 뒤 [확인] 버튼을 클릭합니다.

    파워쿼리 불필요 데이터 제거
    제품 열에서 합계를 제거합니다.
  4. 데이터 정규화, 열 피벗해제 : 제품 열을 선택 후, 파워쿼리 편집기에서 [변환] 탭 - [열 피벗 해제]에서 확장(▼) 버튼을 클릭합니다. 그리고 [다른 열 피벗 해제] 버튼을 클릭하면 제품열을 제외한 나머지 필드가 피벗 해제 되면서 데이터가 정규화됩니다.

    파워쿼리 정규화 열 피벗 해제
    제품 열을 선택 - 편집 - 다른 열 피벗 해제를 클릭합니다.
  5. 머리글 변경 및 마무리 : 머리글을 더블클릭 하거나 F2키를 눌러 머리글을 각각 "날짜, 수량"으로 변경합니다. 또한 데이터 타입이 '문자' 와 '정수'가 아닐 경우, 각각 데이터 형식을 문자와 숫자로 변경합니다.
    엑셀 파워쿼리 머리글 변경
    머리글과 데이터 형식을 수정합니다.
    오빠두Tip : 엑셀 2019 이전 버전 사용자는 머리글 좌측으로 데이터 형식이 표시되지 않으므로, 열을 선택 - [홈] 탭에서 [데이터 형식]을 확인합니다.
  6. 파워쿼리 편집기에서 [파일] 탭 - [닫기 및 다음으로 로드]를 선택한 후, 기존 '실습'시트의 A10셀 위로 쿼리를 출력합니다.

    엑셀 파워쿼리 보고서 출력
    파워쿼리를 시트 위로 출력합니다.
  7. 세로방향이 지켜진 올바른 데이터로 정규화가 완료되었습니다.

    엑셀 데이터 정규화 완료
    데이터 정규화가 완료되었습니다.

실시간 분석 보고서 만들기

이제 간단한 피벗테이블과 슬라이서를 활용하여 매출 분석 보고서를 작성해보겠습니다.

  1. 피벗테이블 만들기 : 쿼리로 출력 된 표를 선택 후, [삽입] 탭 - [피벗테이블]을 클릭합니다. 이후 실습시트의 E10셀에 피벗테이블을 생성합니다.

    엑셀 피벗테이블 만들기
    표를 선택 - 삽입 탭 - 피벗테이블을 클릭하여 피벗테이블을 추가합니다.
  2. 제품을 행으로, 수량을 값으로 이동합니다.

    엑셀 피벗테이블 보고서 이동
    각 필드를 행과 값 영역으로 이동합니다.
  3. 슬라이서 추가하기 : 피벗테이블을 선택하면 위쪽에 [피벗테이블 분석] 탭이 생깁니다. [피벗테이블 분석] 탭 - [슬라이서 삽입] 을 선택 한 뒤, '날짜' 슬라이서를 추가합니다.

    엑셀 슬라이서 삽입
    피벗테이블 선택 - 피벗테이블 분석 - 슬라이서 삽입에서 날짜 슬라이서를 삽입합니다.
  4. 추가된 슬라이서에서 날짜를 선택하면 해당 날짜의 입고 내역이 실시간으로 필터링됩니다.
    엑셀 재고관리 보고서 완성
    버튼을 클릭하면 데이터가 실시간으로 필터링됩니다.
    오빠두Tip : 이 기능을 활용하면 다양한 엑셀 대시보드를 만들 수 있습니다. 대시보드 만들기에 대한 보다 자세한 설명은 아래 영상강의를 참고해주세요.

4.9 14 투표
게시글평점
16 댓글
Inline Feedbacks
모든 댓글 보기
16
0
여러분의 생각을 댓글로 남겨주세요.x