피벗 테이블은 데이터 분석에 강력하며, 계산된 필드 및 다중 데이터 원본과 같은 고급 기술을 사용하면 더 많은 통찰력을 얻을 수 있습니다. 이 문서에서는 계산된 필드에 중점을 두고 피벗 테이블의 여러 데이터 소스를 사용하는 고급 피벗 테이블 기술을 설명하고 보여줍니다.
피벗 테이블에 계산된 필드 만들기
계산된 필드는 기존 데이터를 기반으로 피벗 테이블 내에 사용자 정의 계산을 만듭니다. 계산된 필드는 기존 필드에서 값이 파생되는 피벗 테이블에 새 열을 추가합니다. 이는 원본 데이터세트를 수정하지 않고 데이터에 대한 계산을 수행하려는 경우에 유용합니다.
피벗 테이블의 계산된 필드를 사용하여 판매 데이터 세트에서 판매에 대한 10% 수수료를 계산해 보겠습니다. 계산된 필드를 사용하려면 먼저 피벗 테이블을 만들어야 합니다.
1단계:피벗 테이블 만들기 .
- 데이터 범위를 선택하세요.
- 삽입으로 이동 탭>> 피벗 테이블을 선택합니다. .
- 피벗 테이블을 배치할 위치를 선택하세요(새 워크시트/기존 워크시트).
이제 필요한 필드를 행으로 끌어서 놓습니다. , 열 , 값 및 필터 섹션.
2단계:계산된 필드 삽입 .
- 피벗 테이블 내부 아무 곳이나 클릭하세요.
- 피벗 테이블 분석으로 이동 탭>> 필드, 항목 및 세트에서>> 계산된 필드를 선택합니다. .
- 계산된 필드 삽입 대화 상자:
- 이름 상자에 계산된 필드의 이름(판매 수수료(10%))을 입력합니다.
- 수식에서 상자에 다음 수식을 삽입합니다.
- =0.01 * '총 판매량'
- 추가 를 선택하세요.>> 확인을 클릭하세요. 피벗 테이블에 필드를 추가합니다.
이제 새로운 계산된 필드 판매 수수료(10%) 열이 피벗 테이블에 추가됩니다.
도움말: 계산된 필드는 덧셈 계산에 가장 적합하지만, 비덧셈 계산이나 더 복잡한 계산에는 파워 피벗을 사용하세요.
여러 데이터 소스에서 피벗 테이블 만들기
단일 피벗 테이블에서 여러 테이블(또는 소스)의 데이터를 결합하면 테이블을 수동으로 병합하지 않고도 보다 포괄적인 데이터 세트를 분석할 수 있습니다.
하나는 판매 정보를 포함하는 판매 데이터이고 다른 하나는 제품 정보를 포함하는 제품 데이터인 여러 데이터 소스가 있다고 가정해 보겠습니다. 피벗 테이블에서 이러한 여러 데이터 소스를 사용하는 방법을 보여 드리겠습니다.
방법 1:Excel에 내장된 데이터 모델 사용
데이터 모델 기능을 사용하면 피벗 테이블에서 여러 데이터 소스를 사용할 수 있습니다. Excel 2013 이상 버전에서는 이 기본 제공 데이터 모델 기능을 제공합니다.
1단계:Excel에서 각 표 설정 .
- 셀 범위 선택>> 삽입으로 이동 탭>> 표 선택 .
- 더 나은 이해를 위해 각 테이블의 이름을 지정할 수 있습니다(예:Sales_Data Sales 테이블 및 Products_Table 제품 테이블의 경우).
2단계:데이터 모델을 사용하여 피벗 테이블 삽입 .
- 표 선택>> 삽입으로 이동 탭>> 피벗 테이블 선택 .
- 피벗 테이블 만들기 에서 대화 상자,
- 피벗 테이블을 배치할 위치를 선택하세요(새 워크시트/기존 워크시트).
- 데이터 모델에 이 데이터 추가를 클릭합니다. .
- 확인을 클릭하세요. .
3단계:관계 만들기 .
- 데이터로 이동 탭>> 데이터 도구>> 관계를 선택하세요. .
- 관계 관리에서 대화 상자:
- 새로 만들기를 클릭하세요. 을 클릭한 다음 Product_ID를 선택하세요. 판매 모두에서 및 제품 관계를 구축하기 위한 테이블입니다.
- 확인을 클릭하세요. 관계를 유지하기 위해.
4단계:피벗 테이블 만들기 .
- 이제 피벗 테이블 필드에서 창에는 두 테이블의 필드가 모두 표시됩니다.
- 카테고리 드래그 및 제품 Product_Table에서 행으로 .
- 판매 수량을 드래그하세요. 판매_데이터에서 표를 열로 .
- 드래그 총 매출 판매_데이터에서 표를 값으로 제품별 총 수량 및 판매량을 확인하실 수 있습니다.
관계가 설정되면 이제 수동 병합 없이도 피벗 테이블에서 두 테이블의 필드를 가져와 판매 요약, 제품 이름 및 카테고리를 표시할 수 있습니다.
방법 2. 여러 데이터 소스에 Power Pivot 사용
Excel의 Power Pivot 추가 기능은 Excel 2016, 2019, Office 365 Professional 및 Enterprise와 같은 버전에서 사용할 수 있으며, 이를 통해 다양한 소스의 테이블 간에 관계를 만들 수 있습니다. 이 접근 방식을 사용하면 여러 테이블을 기반으로 피벗 테이블을 만들고 테이블 전체에 걸쳐 분석을 수행할 수 있습니다.
1단계:Power Pivot 활성화 (아직 활성화되지 않은 경우).
- 파일로 이동 탭>> 옵션 선택>> 추가 기능을 선택합니다. .
- 창 하단의 관리 드롭다운>> COM 추가 기능 선택>> 이동을 클릭하세요. .
- Microsoft Power Pivot for Excel 확인>> 확인을 클릭하세요. .
2단계:Power Pivot에 데이터 로드 .
- 데이터로 이동 탭>> 데이터 가져오기 선택 다양한 소스(예:여러 시트, 외부 데이터베이스, CSV 파일)에서 데이터를 가져옵니다.
- 데이터 소스(Excel 통합 문서, SQL Server, Text/CSV 등)를 선택한 다음 다음으로 로드를 클릭합니다. .
- 데이터 가져오기에서 대화 상자:
- 표 선택 .
- 새 워크시트를 선택합니다. .
- 데이터 모델에 이 데이터 추가 확인란을 선택합니다. .
참고 :로드하는 각 테이블에는 테이블 간의 관계를 활성화하기 위한 고유 식별자(예:제품 ID 또는 고객 ID)가 있어야 합니다.
3단계:테이블 간 관계 만들기 .
- 파워 피벗으로 이동 탭>> 관리를 선택하세요. Power Pivot 편집기를 열려면 .
- 다이어그램 보기로 이동 모든 테이블을 시각적으로 볼 수 있습니다.
- 테이블 간에 필드를 드래그 앤 드롭하여 관계를 만듭니다. 제품 ID를 연결하세요. 판매 테이블에서 제품 ID 제품 표 ).
- 홈으로 이동 파워 피벗의 탭>> 피벗 테이블을 선택합니다. .
- 피벗 테이블을 배치할 위치(예:새 워크시트)를 선택하고 확인을 클릭합니다. .
- 피벗 테이블 필드 목록에는 모든 테이블의 필드가 표시됩니다. 이 필드를 사용하여 여러 소스에서 데이터를 가져오는 보고서를 만드세요.
- 제품 드래그 Product_Table에서 행 .
- 판매 수량 을 드래그하세요. 판매_데이터에서 열 .
- 총 매출 을 드래그하세요. 판매_데이터에서 값 .
결론
이러한 고급 기술을 사용하면 여러 데이터 소스의 분석 및 통합을 사용자 정의하여 피벗 테이블을 더욱 통찰력 있게 만들 수 있습니다. 계산된 필드를 마스터하고 Excel의 피벗 테이블에서 여러 데이터 소스를 통합하면 복잡한 데이터 세트를 효율적으로 분석하는 데 도움이 됩니다. 이러한 기술을 사용하면 더욱 빠르게 통찰력을 얻고 데이터를 보다 효율적으로 관리할 수 있습니다.
Pixabay에서 가져온 Councilcle님의 이미지