엑셀 피벗테이블 핵심 규칙, "세로방향 블록쌓기" 살펴보기

실습 가이드

엑셀은 '스프레드시트'라는 프로그램의 한 종류입니다. 스프레드시트는 '데이터베이스'에서 파생된 프로그램이기 때문에, 엑셀을 효율적으로 사용하려면 데이터를 올바른 구조로 관리하는 것이 매우 중요합니다.

엑셀은 다양한 함수와 기능을 제공하기 때문에 데이터가 정규화된 형태가 아니더라도 비교적 손쉽게 분석 작업을 수행할 수 있다는 장점이 있습니다. 그러나 최근에는 사용자가 다루는 데이터의 전반적인 규모(Volume)가 빠르게 늘어나면서, 정규화되지 않은 데이터 구조로 인해 다양한 문제가 발생하고 있습니다.

대표적으로 일별·월별로 누적되는 데이터를 시트나 파일 단위로 분리해 관리할 경우, 데이터가 점차 쌓이면서 자료를 통합하고 분석하는 데 점점 더 많은 시간이 소요됩니다.

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

엑셀 데이터 관리, 3가지 핵심 규칙

1] 머리글은 반드시 한 줄로 관리한다

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

또한 머리글이 두 줄로 작성되었다는 것은, 데이터 정규화의 핵심 규칙인 '세로방향 블록쌓기'가 제대로 지켜지지 않았다는 의미이기도 합니다. 따라서 머리글이 두 줄로 관리되고 있다면, 각 항목을 별도의 열로 분리해 정규화된 데이터 구조로 재정렬해야 합니다.

2] 셀 병합은 절대 사용 금지

엑셀에서 셀 병합은 '문서 서식'이나 '문서 양식'을 작성할 때에만 사용해야 합니다. 원본 데이터에 셀 병합을 적용하면 함수와 피벗테이블이 병합된 셀의 값을 정확하게 인식하지 못해 치명적인 오류가 발생할 수 있으므로, '원본 데이터'에는 셀 병합을 절대 사용하지 않습니다.

셀 병합 기능의 동작 원리와 사용 시 주의사항, 문제 해결 방법은 아래 셀 병합의 모든 것 기초 입문 강의에서 자세히 정리했으니 함께 참고하세요.

3] 집계 데이터를 명확히 구분한다

엑셀로 데이터를 관리할 때 마지막으로 주의해야 할 점은, '원본 데이터'와 '집계 데이터'를 명확하게 구분해서 관리하는 것입니다. 피벗테이블의 원본 범위에 아래와 같이 합계나 소계 같은 집계 데이터가 포함되어 있으면, 피벗테이블이 만들어지는 과정에서 원본과 집계가 함께 합산되며 잘못된 결과가 반환됩니다.

데이터 관리 제 1규칙! "세로방향 블록쌓기"

지금까지 살펴본 올바른 데이터 구조의 3가지 규칙과 함께, 실무에서 피벗테이블을 정확하게 활용하기 위해 반드시 가장 먼저 지켜야 할 핵심 규칙이 있습니다.

"세로방향 블록쌓기"

먼저, 세로방향 블록쌓기 규칙이 지켜지지 않은 데이터의 모습을 살펴봅니다.

위와 같이 가로·세로 양방향으로 동시에 누적되는 데이터로 피벗테이블을 만들면, 그림처럼 각 거래처가 별도의 필드로 분리되면서 거래처별 판매 현황을 분석하는 데 어려움이 발생합니다.

또한 데이터가 가로·세로 양방향으로 동시에 누적되면, 매월 구매 내역을 별도의 표로 분리해 관리하게 됩니다. 이런 구조에서는 특정 기간의 판매 데이터를 분석할 때마다, 흩어진 표를 하나로 취합하는 사전 작업이 매번 반복되어 상당한 시간이 소요됩니다.

이런 경우에는 가로·세로 양방향으로 누적된 데이터를 '세로방향 블록쌓기' 규칙에 맞춰 재구성합니다.

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

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

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

함수를 사용하는 방법

일회성 작업이고, 정규화할 데이터의 양이 많지 않을 경우에는 엑셀 함수만으로 세로방향 블록쌓기를 구현할 수 있습니다. 함수를 활용한 1차 데이터 정규화 방법은 아래 영상 강의에서 자세히 다루니 함께 확인해보세요.

파워쿼리를 사용하는 방법

파워쿼리가 제공되는 엑셀 2016 이후 버전을 사용 중이고, 정기적으로 정규화 작업을 반복해야 할 경우에는 파워쿼리를 활용해 새롭게 추가되는 데이터까지 자동으로 정규화할 수 있습니다. 파워쿼리를 활용한 1차 데이터 정규화의 다양한 예제는 아래 엑셀 라이브 강의에서 사례별로 확인할 수 있습니다. (라이브 강의 전체 영상은 위캔두 멤버십 회원에게만 제공됩니다.)