엑셀 피벗테이블 핵심 규칙, "세로방향 블록쌓기"의 모든 것

그렇지만 엑셀은 사용자가 쉽고 편리하게 사용할 수 있는 다양한 함수와 기능을 제공하기 때문에 꼭 올바른 형태로 데이터를 관리하지 않더라도 손쉽게 데이터를 분석할 수 있는 장점이 있었는데요. 하지만 최근 들어서는 사용자가 관리하는 데이터의 전반적인 규모(Volumn)가 늘어남에 따라 올바르지 않은 형태로 관리되는 데이터 구조로 인해 여러가지 문제가 발생하고 있습니다.

과거 데이터 양 증가 — 실무자가 다루는 데이터의 양(Volume)은 최근 5년간 평균 14배 증가했습니다.

대표적으로 일별, 월별로 누적되는 데이터의 경우, 올바른 형태가 아닐 때 여러 개의 시트와 파일로 나뉘어 관리가 되는데, 그럴 경우 데이터가 점점 누적됨에 따라 자료를 관리하고 분석하는데 어려움을 겪게 됩니다.

월별 시트 나눠서 관리 문제 — 특히 실무에서는 날짜별로 누적되는 데이터 관리에 어려움을 겪게됩니다

오빠두Tip : 매일 또는 매월 누적되는 데이터를 여러 개의 표와 시트로 나누어 관리하면 실무에서 다양한 문제가 발생합니다.

따라서 이번 강의에서는 엑셀에서 올바른 형태로 데이터를 관리하기 위한 3가지 규칙과 더불어 피벗테이블을 활용한 데이터 분석에서 반드시 지켜져야 할 핵심규칙 1가지를 단계별로 살펴보겠습니다.

문제1. 머리글이 두 줄인 경우

모든 데이터의 머리글은 반드시 한 줄로 작성되어야 합니다. 아래 그림처럼 머리글이 2줄로 입력될 경우 피벗테이블을 만들 때 오류가 발생합니다.

오빠두Tip : 원본데이터의 머리글은 반드시 한 줄로 관리합니다.

머리글 두줄일 경우 피벗테이블 오류 — 표의 머리글이 여러 줄일 경우, 피벗테이블 추가 시 오류가 발생합니다.

뿐만 아니라 머리글이 두 줄로 관리되었다는 것은, 데이터 정규화 핵심 규칙인 '세로방향 블록쌓기'가 제대로 지켜지지 않았다는 것을 이야기합니다. 따라서 머리글이 두 줄일 경우, 각 필드를 여러 개의 열로 나누어서 정규화 된 데이터 형태로 관리해야 합니다.

문제2. 셀 병합이 되어있는 경우

엑셀에서 셀병합은 '문서 서식'이나 '문서 양식'을 작성할 때에만 사용해야 합니다. 엑셀에서 관리 중인 원본 데이터에 셀병합을 사용할 경우, 병합된 셀의 데이터를 올바르게 인식하지 못하는 치명적인 문제가 발생하기 때문에 '원본 데이터'에는 셀병합을 절대로 사용하지 말아야 합니다.

엑셀 데이터 관리 셀병합 — 원본데이터에는 병합된 셀이 포함되어 있으면 안 됩니다.

셀 병합 기능에 대한 자세한 설명 및 사용 시 주의사항과 문제 해결방법에 대한 자세한 설명은 아래 셀병합의 모든 것 기초 입문 강의를 참고하세요.

엑셀 셀 병합 기능의 모든 것 - 기초입문강의 (영상강의)

오빠두Tip : 원본 데이터에 병합된 셀이 있으면 피벗테이블 뿐만 아니라 함수로 데이터를 분석할 때도 다양한 문제가 발생합니다. 따라서 원본데이터를 관리할 때에는 셀 병합을 사용하지 않는 것에 주의합니다.

문제3. 집계 데이터가 포함된 경우

엑셀로 데이터를 관리할 때 마지막으로 주의해야 할 점은 바로 '원본데이터'와 '집계데이터'를 구분해서 관리하는 것 입니다. 만약 피벗테이블의 원본데이터 범위에 아래와 같이 집계된 데이터가 포함되어 있으면, 피벗테이블이 만들어지면서 원본데이터 + 집계데이터로 이중 계산되면서 옳지 않은 계산결과가 반환됩니다.

오빠두Tip : 원본데이터에 합계와 소계를 포함한 상태로 피벗테이블을 만들면 피벗테이블의 합계가 x2배로 중복 계산됩니다.

엑셀 원본 데이터 집계 데이터 분리 — 원본데이터에 집계 된 데이터가 포함되면, 피벗테이블에서 중복 계산되는 문제가 발생합니다.

피벗테이블 데이터 관리의 핵심! - 세로방향 블록쌓기

지금까지 알아본 올바른 데이터 구조를 갖추기 위한 3가지 규칙과 더불어, 실무에서 피벗테이블을 올바르게 사용하려면 가장 먼저 지켜줘야 할 핵심 규칙이 있습니다.

피벗테이블 원본 데이터 핵심 규칙
"세로방향 블록쌓기"

우선 세로방향 블록쌓기 규칙이 제대로 지켜지지 않은 데이터를 살펴보겠습니다.

엑셀 가로방향 세로방향 관리 — 가로로 거래처가 누적되고 세로로 메뉴/가격이 누적되는 잘못된 데이터 구조

위와 같이 가로/세로방향으로 동시에 누적되는 데이터를 대상으로 피벗테이블을 만들면 그림처럼 각 거래처마다 피벗테이블의 필드가 만들어지면서 각 거래처의 판매 현황을 분석하는데 어려움을 겪게됩니다.

엑셀 거래처 필드 분리 — 각 거래처가 각각의 필드로 분리되어 데이터를 올바른 형태로 분석할 수 없습니다.

오빠두Tip : 원본데이터에서 가로 방향으로 나열된 거래처가 피벗테이블의 각 필드로 분리되면서 데이터를 분석하는데 문제가 발생합니다.

뿐만 아니라, 데이터가 가로 및 세로 방향으로 동시에 누적되면서, 매월 구매 내역을 별도의 표로 관리하게 되는데요. 이럴 경우, 특정 기간에 판매된 데이터를 분석하려면 사전 작업으로 데이터를 취합하는 별도의 과정이 수반되면서 매번 상당한 작업을 요구하게 됩니다.

엑셀 데이터 월별로 따로 관리 — 매월 데이터를 별도의 표로 관리하다 보니, 매번 데이터를 취합할 때 문제가 발생합니다.

따라서 이럴 경우 세로방향과 가로방향으로 동시에 누적된 데이터를 '세로방향 블록쌓기' 규칙을 지켜서 관리합니다.

엑셀 피벗테이블 핵심 규칙 세로방향 블록쌓기 — 각 필드마다 데이터가 세로로 누적되는 '세로방향 블록쌓기' 규칙을 지켜서 데이터를 관리합니다.

데이터를 [판매월], [메뉴], [가격], [거래처], [수량] 필드로 나누어 데이터 구조를 관리하면, 새롭게 추가되는 데이터는 아래 방향으로만 누적되고, 피벗테이블을 만들어서 데이터를 빠르고 효율적으로 분석할 수 있게 됩니다.

엑셀 1차 데이터 정규화 완료 — 세로방향 블록쌓기 규칙을 지키면, 각 필드의 새로운 데이터는 아래 방향으로 누적됩니다.

세로방향 블록쌓기, 어떻게 구현하나요?

"세로방향 블록쌓기"는 1차 데이터 정규화를 위한 핵심 규칙입니다. 엑셀로 세로방향 블록쌓기가 지켜지지 않은 데이터에 세로방향 블록쌓기를 적용하려면 2가지 방법을 사용할 수 있습니다.

함수를 사용하는 방법

일시적으로 필요한 작업이고, 정규화 할 데이터의 양이 많지 않을 경우에는 엑셀 함수만 사용해서 세로방향 블록쌓기를 구현할 수 있습니다. 함수를 활용한 데이터 1차 정규화에 대한 자세한 내용은 아래 영상강의를 참고하세요.

파워쿼리를 사용하는 방법

파워쿼리가 제공되는 엑셀 2016 이후 버전을 사용중이고, 매번 주기적으로 정규화를 해야 할 경우에는 파워쿼리를 사용하면 새롭게 추가되는 데이터를 자동으로 정규화 할 수 있습니다. 파워쿼리를 활용한 1차 데이터 정규화의 다양한 예제는 아래 엑셀 라이브 강의를 참고해주세요. (라이브 강의 전체영상은 위캔두 멤버쉽 회원에게만 제공됩니다.)

5 15 투표

게시글평점

엑셀 피벗테이블 핵심 규칙, "세로방향 블록쌓기" 꼭 아셔야 합니다!

피벗테이블 활용을 위한 데이터 관리 핵심 규칙! - '세로방향 블록쌓기' 및 올바른 데이터 구조의 3가지 원칙을 하나씩 살펴봅니다.