피벗 테이블(Pivot Table)은 데이터 분석에 강력한 도구입니다. 여기에 계산 필드(Calculated Field)와 다중 데이터 소스(Multiple Data Sources) 같은 고급 기법을 더하면 한층 깊이 있는 인사이트를 얻을 수 있습니다. 이 글에서는 계산 필드를 만드는 방법과 피벗 테이블에서 여러 데이터 소스를 활용하는 방법을 단계별로 자세히 살펴보겠습니다.
피벗 테이블에서 계산 필드 만들기
계산 필드는 기존 데이터를 바탕으로 피벗 테이블 안에 사용자 지정 계산을 추가하는 기능입니다. 새로운 열이 하나 생기고, 그 값은 기존 필드들에서 파생됩니다. 원본 데이터셋을 수정하지 않고도 원하는 계산을 수행할 수 있어 매우 유용합니다.
예를 들어 판매 데이터셋에서 10% 판매 수수료를 계산해 보겠습니다. 계산 필드를 사용하려면 먼저 피벗 테이블을 만들어야 합니다.
1단계: 피벗 테이블 만들기
- 데이터 범위를 선택합니다.
- 삽입(Insert) 탭 → 피벗 테이블(Pivot Table)을 선택합니다.
- 피벗 테이블을 배치할 위치(새 워크시트/기존 워크시트)를 지정합니다.
필요한 필드를 행(Rows), 열(Columns), 값(Values), 필터(Filters) 영역으로 드래그 앤 드롭합니다.
2단계: 계산 필드 삽입
- 피벗 테이블 아무 곳이나 클릭합니다.
- PivotTable 분석(Analyze) 탭 → 필드, 항목 및 집합(Fields, Items & Sets) → 계산 필드(Calculated Field)를 선택합니다.
- 계산 필드 삽입(Insert Calculated Field) 대화 상자에서:
- 이름(Name) 상자에 계산 필드 이름을 입력합니다: Commission on Sales (10%)
- 수식(Formula) 상자에 다음 수식을 입력합니다:
- = 0.01 * 'Total Sales'
- 추가(Add)를 클릭한 뒤 확인(OK)을 눌러 피벗 테이블에 필드를 추가합니다.
이제 새 계산 필드인 Commission on Sales (10%) 열이 피벗 테이블에 추가됩니다.
팁: 계산 필드는 단순 가산형 계산에 가장 적합합니다. 비가산적이거나 더 복잡한 계산이 필요하다면 Power Pivot을 사용하는 것이 좋습니다.
여러 데이터 소스로 피벗 테이블 만들기
여러 테이블(또는 데이터 소스)의 데이터를 하나의 피벗 테이블로 결합하면, 테이블을 일일이 수작업으로 병합하지 않고도 더 포괄적인 데이터셋을 분석할 수 있습니다.
예를 들어 판매 정보를 담은 Sales 데이터와 제품 정보를 담은 Product 데이터, 두 개의 데이터 소스가 있다고 가정해 보겠습니다. 이 두 소스를 피벗 테이블에서 함께 활용하는 방법을 소개합니다.
방법 1: 엑셀 내장 데이터 모델(Data Model) 사용
데이터 모델 기능을 활용하면 피벗 테이블에서 여러 데이터 소스를 사용할 수 있습니다. Excel 2013 이상 버전에서는 이 기능이 기본 제공됩니다.
1단계: 각 테이블을 엑셀에서 설정하기
- 셀 범위 선택 → 삽입(Insert) 탭 → 테이블(Table) 선택
- 이해하기 쉽도록 각 테이블에 이름을 지정합니다 (예: 판매 테이블은 Sales_Data, 제품 테이블은 Products_Table).
2단계: 데이터 모델을 이용해 피벗 테이블 삽입
- 테이블 선택 → 삽입(Insert) 탭 → PivotTable 선택
- PivotTable 만들기(Create PivotTable) 대화 상자에서:
- 피벗 테이블 배치 위치 선택 (새 워크시트/기존 워크시트)
- 이 데이터를 데이터 모델에 추가(Add this data to the Data Model) 클릭
- 확인(OK) 클릭
3단계: 관계(Relationships) 만들기
- 데이터(Data) 탭 → 데이터 도구(Data Tools) → 관계(Relationships) 선택
- 관계 관리(Manage Relationships) 대화 상자에서:
- 새로 만들기(New) 클릭 후 Sales 테이블과 Products 테이블 모두에서 Product_ID를 선택하여 관계를 설정합니다.
- 확인(OK)을 눌러 관계를 저장합니다.
4단계: 피벗 테이블 구성
- 이제 PivotTable 필드 창에 두 테이블의 필드가 모두 표시됩니다.
- Product_Table에서 Category와 Product를 행으로 드래그합니다.
- Sales_Data 테이블에서 Unit sold를 열로 드래그합니다.
- Sales_Data 테이블에서 Total Sales를 값으로 드래그하여 제품별 판매 수량과 매출을 확인합니다.
관계만 설정해 주면 피벗 테이블이 두 테이블의 필드를 모두 가져와서, 수동 병합 없이도 매출 요약·제품명·카테고리 정보를 한눈에 보여줍니다.
방법 2: Power Pivot으로 여러 데이터 소스 활용
Excel 2016, 2019, Office 365 Professional & Enterprise 등의 버전에서 제공되는 Power Pivot 추가 기능을 사용하면 서로 다른 소스의 테이블 간에 관계를 만들 수 있습니다. 이 방식을 통해 여러 테이블을 기반으로 피벗 테이블을 구축하고, 전체 데이터를 아우르는 통합 분석을 수행할 수 있습니다.
1단계: Power Pivot 사용 설정 (아직 활성화하지 않은 경우)
- 파일(File) 탭 → 옵션(Options) → 추가 기능(Add-ins) 선택
- 창 하단의 관리(Manage) 드롭다운에서 COM 추가 기능(COM Add-ins) 선택 → 이동(Go) 클릭
- Microsoft Power Pivot for Excel 체크 → 확인(OK) 클릭
2단계: Power Pivot으로 데이터 불러오기
- 데이터(Data) 탭 → 데이터 가져오기(Get Data)를 선택해 여러 시트, 외부 데이터베이스, CSV 파일 등 다양한 소스에서 데이터를 가져옵니다.
- 데이터 소스를 선택(Excel 통합 문서, SQL Server, 텍스트/CSV 등)한 후 Load To를 클릭합니다.
- 데이터 가져오기(Import Data) 대화 상자에서:
- 테이블(Table) 선택
- 새 워크시트(New Worksheet) 선택
- 이 데이터를 데이터 모델에 추가(Add this data to the Data Model) 체크
참고: 불러온 각 테이블에는 테이블 간 관계를 맺을 수 있도록 고유 식별자(제품 ID, 고객 ID 등)가 반드시 있어야 합니다.
3단계: 테이블 간 관계 만들기
- Power Pivot 탭 → 관리(Manage)를 선택해 Power Pivot 편집기를 엽니다.
- 다이어그램 보기(Diagram View)로 이동하면 모든 테이블을 시각적으로 확인할 수 있습니다.
- 테이블 간에 필드를 드래그 앤 드롭하여 관계를 만듭니다. 예를 들어 Sales 테이블의 Product ID를 Product 테이블의 Product ID에 연결합니다.
- Power Pivot의 홈(Home) 탭 → PivotTable 선택
- 피벗 테이블을 배치할 위치 선택(예: 새 워크시트) 후 확인(OK) 클릭
- PivotTable 필드 목록에 모든 테이블의 필드가 표시됩니다. 이 필드들을 활용하면 여러 소스의 데이터를 한 번에 끌어오는 보고서를 만들 수 있습니다.
- Product_Table에서 Product를 행으로 드래그
- Sales_Data에서 Unit Sold를 열로 드래그
- Sales_Data에서 Total Sales를 값으로 드래그
마무리
이러한 고급 기법을 활용하면 분석을 자유롭게 사용자 지정하고 여러 데이터 소스를 손쉽게 통합할 수 있어, 피벗 테이블의 활용 가치가 한층 높아집니다. 계산 필드를 능숙하게 다루고 엑셀 피벗 테이블에서 여러 데이터 소스를 하나로 묶는 방법을 익히면 복잡한 데이터셋도 효율적으로 분석할 수 있습니다. 이 기법들을 통해 더 빠르게 인사이트를 얻고 데이터를 더 체계적으로 관리해 보세요.
Image by councilcle from Pixabay