피벗 테이블에서 관계 작업

적용 대상
Microsoft 365용 Excel Excel 2024 Excel 2021 Excel 2019 Excel 2016

피벗 테이블은 일반적으로 테이블 간에 이미 풍부한 연결이 있는 OLAP 큐브 및 기타 복잡한 데이터 원본을 사용하여 구성되었습니다. 그러나 Excel에서는 여러 테이블을 자유롭게 가져와서 테이블 간에 고유한 연결을 작성할 수 있습니다. 이러한 유연성은 강력하지만 관련이 없는 데이터를 쉽게 통합하여 이상한 결과를 초래할 수도 있습니다.

이런 피벗 테이블을 만들어 본 적이 있나요? 지역별로 구매 내역을 작성하려고 했기 때문에 구매 금액 필드를 값 영역에, 판매 지역 필드를 열 레이블 영역에 삭제했습니다. 그러나 결과는 틀렸습니다.

모든 지역에 대해 총총 구매 횟수를 반복하는 잘못된 피벗 테이블

이 문제를 어떻게 해결할 수 있나요?

문제는 피벗 테이블에 추가한 필드가 동일한 통합 문서에 있을 수 있지만 각 열이 포함된 테이블에는 관련성이 없다는 것입니다. 예를 들어 각 판매 지역을 나열하는 테이블과 모든 지역의 구매를 나열하는 다른 테이블이 있을 수 있습니다. 피벗 테이블을 만들고 올바른 결과를 얻으려면 두 테이블 간의 관계를 만들어야 합니다.

관계를 만들면 피벗 테이블은 구매 테이블의 데이터를 지역 목록과 올바르게 결합하며 결과는 다음과 같습니다.

각 지역의 고유한 구매 횟수를 보여 주는 올바른 피벗 테이블

Excel에는 이와 같은 관계 문제를 자동으로 감지하고 해결하기 위해 MSR(Microsoft Research)에서 개발한 기술이 포함되어 있습니다.

맨 위로 이동

자동 검색 사용

자동 검색은 피벗 테이블이 포함된 통합 문서에 추가하는 새 필드를 확인합니다. 새 필드가 피벗 테이블의 열 및 행 머리글과 관련이 없는 경우 피벗 테이블 위쪽의 알림 영역에 관계가 필요할 수 있음을 알리는 메시지가 나타납니다. Excel은 또한 새 데이터를 분석하여 잠재적인 관계를 찾습니다.

계속해서 메시지를 무시하고 피벗 테이블 작업을 할 수 있습니다. 그러나 만들기를 클릭하면 알고리즘이 작업을 시작하여 데이터를 분석합니다. 새 데이터의 값, 피벗 테이블의 크기와 복잡성, 이미 만든 관계에 따라 이 프로세스는 최대 몇 분 정도 걸릴 수 있습니다.

이 프로세스는 다음 두 단계로 구성됩니다.

  • 관계의 검색. 분석이 완료되면 제안된 관계 목록을 검토할 수 있습니다. 취소하지 않으면 Excel은 자동으로 관계를 만드는 다음 단계로 진행합니다.
  • 관계의 생성. 관계가 적용되면 확인 대화 상자가 나타나고 세부 정보 링크를 클릭하여 만들어진 관계 목록을 볼 수 있습니다.

검색 프로세스를 취소할 수 있지만 생성 프로세스는 취소할 수 없습니다.

MSR 알고리즘은 모델의 테이블을 연결하기 위해 "가능한 최선의" 관계 집합을 검색합니다. 알고리즘은 열 이름, 열의 데이터 형식, 열 내의 값 및 피벗 테이블에 있는 열을 고려하여 새 데이터에 대해 가능한 모든 관계를 검색합니다.

그런 다음 Excel은 내부 추론에 의해 결정된 가장 높은 '품질' 점수를 가진 관계를 선택합니다. 자세한 내용은 관계 개요 및 관계 문제 해결을 참조하십시오.

자동 검색에서 올바른 결과를 제공하지 않을 경우 수동으로 관계를 편집 또는 삭제하거나 새 관계를 만들 수 있습니다. 자세한 내용은 두 테이블 간의 관계 만들기 또는 다이어그램 보기에서 관계 만들기를 참조하세요

맨 위로 이동

피벗 테이블의 빈 행(알 수 없는 구성원)

피벗 테이블은 관련 데이터 테이블을 함께 제공하기 때문에 테이블에 키 또는 일치하는 값으로 연결할 수 없는 데이터가 들어 있는 경우 데이터를 어떻게든 처리해야 합니다. 다차원 데이터베이스에서 일치하지 않는 값이 없는 모든 행을 알 수 없는 멤버에 할당하는 방법은 일치하지 않는 데이터를 처리하는 것입니다. 피벗 테이블에서 알 수 없는 멤버가 빈 머리글로 표시됩니다.

예를 들어 판매를 매장별로 그룹화해야 하는 피벗 테이블을 만들었는데 판매액 테이블의 일부 레코드에 매장 이름이 나열되어 있지 않은 경우 유효한 매장 이름이 없는 모든 레코드가 그룹화됩니다.

빈 행으로 끝나는 경우 두 가지 선택 사항이 있습니다. 여러 테이블 간의 관계 체인을 만들어 작동하는 테이블 관계를 정의하거나 피벗 테이블에서 빈 행을 만드는 필드를 제거할 수 있습니다.

맨 위로 이동