자습서: Excel 2013에서 데이터 모델을 사용한 피벗 테이블 데이터 분석

1시간 안에 Excel에서 여러 테이블의 데이터를 결합하는 피벗 테이블 보고서를 작성할 수 있습니다. 이 자습서의 전반부에서는 데이터를 가져오고 탐색하는 단계를 안내합니다. 후반부에서는 파워 피벗 추가 기능을 사용하여 보고서의 백그라운드에서 작동하는 데이터 모델을 구체화하고 계산, 계층 구조, Power View 보고를 위한 최적화를 추가하는 방법을 알아봅니다.

몇 가지 데이터를 가져오는 것부터 시작하겠습니다.

  1. 이 자습서의 예제 데이터(ContosoV2)를 다운로드합니다. 자세한 내용은 DAX 및 데이터 모델 자습서에 대한 예제 데이터 가져오기를 참고하세요. 다운로드 또는 내 문서와 같이 쉽게 액세스할 수 있는 위치에 데이터 파일을 추출하고 저장합니다.

  2. Excel에서 빈 통합 문서를 엽니다.

  3. 데이터 > 외부 데이터 가져오기 > Access를 클릭합니다.

  4. 예제 데이터 파일이 포함된 폴더로 이동하여 ContosoSales를 선택합니다.

  5. 열기를 클릭합니다. 여러 테이블이 포함되어 있는 데이터베이스 파일에 연결하기 때문에 가져올 테이블을 선택할 수 있도록 테이블 선택 대화 상자가 나타납니다.

    테이블 선택 대화 상자

  6. 테이블 선택에서 여러 테이블 선택 사용을 선택합니다.

  7. 모든 테이블을 선택하고 확인을 클릭합니다.

  8. 데이터 가져오기에서 피벗 테이블 보고서를 클릭하고 확인을 클릭합니다.

    참고 사항: 

    • 아직 모르고 있을 수도 있지만 방금 데이터 모델을 만들었습니다. 이 모델은 동일한 피벗 테이블 보고서에서 여러 테이블을 가져오거나 동시에 작업하면 자동으로 만들어지는 데이터 통합 계층입니다.

    • 모델은 대개 Excel에서 보이지 않지만 사용자는 파워 피벗 추가 기능을 사용하여 직접 모델을 보고 수정할 수 있습니다. Excel에서 피벗 테이블 필드 목록에 테이블 모음이 표시되면 데이터 모델이 존재한다고 확신할 수 있습니다. 모델을 만드는 데는 여러 가지 방법이 있습니다. 자세한 내용은 Excel에서 데이터 모델 만들기 를 참고하세요.

피벗 테이블을 사용하여 데이터 탐색

피벗 테이블 필드 목록의 , , 영역으로 필드를 끌면 데이터를 쉽게 탐색할 수 있습니다.

  1. 필드 목록에서 아래로 스크롤하여 FactSales 테이블을 찾습니다.

  2. SalesAmount를 클릭합니다. 이 데이터는 숫자이기 때문에 SalesAmount가 자동으로 값 영역에 배치됩니다.

  3. DimDate에서 CalendarYear를 열로 끌어옵니다.

  4. DimProductSubcategory에서 ProductSubcategoryName을 행으로 끌어옵니다.

  5. DimProduct에서 BrandName을 행으로 끌어 하위 범주 아래에 배치합니다.

피벗 테이블이 다음 화면과 같이 표시되어야 합니다.

예제 데이터를 보여 주는 피벗 테이블

최소한의 노력으로 서로 다른 4개 테이블의 필드가 포함된 기본 피벗 테이블을 갖게 되었습니다. 테이블 간에 관계가 이미 존재했기 때문에 이 작업을 쉽게 수행할 수 있었습니다. 원본에 테이블 관계가 존재했고 모든 테이블을 한 번의 작업으로 가져왔기 때문에 Excel에서 모델의 해당 관계를 다시 만들 수 있었습니다.

가져온 데이터의 원본이 서로 다르거나 데이터를 나중에 가져온 경우에는 일반적으로 일치하는 열을 기준으로 관계를 만들어 새 데이터를 통합할 수 있습니다. 다음 단계에서는 추가 테이블을 가져오고 및 새 관계를 만들기 위한 요구 사항 및 단계에 대해 알아봅니다.

테이블 추가

테이블 관계를 설정하는 방법을 알아보려면 작업에 사용할 연결되지 않은 테이블이 더 필요합니다. 이 단계에서는 추가 데이터베이스 파일을 하나 가져오고 다른 두 통합 문서의 데이터를 붙여 넣어 이 자습서에서 사용할 나머지 데이터를 가져오겠습니다.

제품 범주 추가

  1. 통합 문서에서 새 시트를 엽니다. 이 시트에 추가 데이터를 저장합니다.

  2. 데이터 > 외부 데이터 가져오기 > Access를 클릭합니다.

  3. 예제 데이터 파일이 들어 있는 폴더로 이동하여 ProductCategories를 선택합니다. 열기를 클릭합니다.

  4. 데이터 가져오기에서 를 선택하고 확인을 클릭합니다.

지역 데이터 추가

  1. 다른 시트를 삽입합니다.

  2. 예제 데이터 파일에서 Geography.xlsx를 열고 A1 셀에 커서를 놓은 다음 Ctrl+Shift+End를 눌러 모든 데이터를 선택합니다.

  3. 데이터를 클립보드로 복사합니다.

  4. 방금 추가한 빈 시트에 데이터를 붙여 넣습니다.

  5. 표 서식을 클릭하고 원하는 스타일을 선택합니다. 데이터 서식을 표로 지정하면 이름을 지정할 수 있으므로 이후 단계에서 관계를 정의할 때 편리합니다.

  6. 표 서식에서 머리글 포함이 선택되어 있는지 확인합니다. 확인을 클릭합니다.

  7. 테이블 이름을 Geography로 지정합니다. 표 도구 > 디자인에서 테이블 이름에 Geography를 입력합니다.

  8. Geography.xlsx를 닫아 작업 영역에서 지웁니다.

매장 데이터 추가

  • Stores.xlsx 파일에 대해 이전 단계를 반복하여 파일 내용을 빈 시트에 붙여 넣습니다. 테이블 이름을 Stores로 지정합니다.

이제 4개의 시트가 있습니다. Sheet1에는 피벗 테이블이 포함되어 있고, Sheet2에는 ProductCategories가, Sheet3에는 Geography가, Sheet4에는 Stores가 포함되어 있습니다. 각 테이블에 이름을 지정하는 데 시간을 들였으므로 관계를 만드는 다음 단계는 훨씬 간단할 것입니다.

새로 가져온 테이블의 필드 사용

방금 가져온 테이블의 필드를 곧바로 사용할 수 있습니다. Excel에서 필드를 피벗 테이블 보고서에 통합하는 방법을 결정할 수 없는 경우 사용자에게 기존 모델에 속한 테이블과 새 테이블을 연결하는 테이블 관계를 만들라는 메시지가 표시됩니다.

  1. 피벗 테이블 필드 위쪽에서 모두를 클릭하여 사용 가능한 테이블의 전체 목록을 봅니다.

  2. 목록의 아래쪽으로 스크롤합니다. 여기에서 방금 추가한 새 테이블을 찾을 수 있습니다.

  3. Stores를 확장합니다.

  4. StoreName을 필터 영역으로 끕니다.

  5. Excel에서 관계를 만들라는 메시지가 표시됩니다. 이 알림은 모델과 관련되지 않은 테이블의 필드를 사용했기 때문에 나타납니다.

  6. 만들기를 클릭하여 관계 만들기 대화 상자를 엽니다.

  7. 테이블에서 FactSales를 선택합니다. 사용 중인 예제 데이터에서 FactSales에는 Contoso의 비즈니스에 대한 자세한 판매 및 비용 정보뿐만 아니라 이전 단계에서 가져온 Stores.xlsx 파일에도 있는 매장 코드를 비롯한 다른 테이블에 대한 키가 포함되어 있습니다.

  8. 열(외래)에서 StoreKey를 선택합니다.

  9. 관련 표에서 Stores를 선택합니다.

  10. 관련 열(기본)에서 StoreKey를 선택합니다.

  11. 확인을 클릭합니다.

Excel에서는 전체 통합 문서의 여러 피벗 테이블, 피벗 차트 또는 Power View 보고서에 사용할 수 있는 데이터 모델을 백그라운드에서 만듭니다. 이 모델의 근간은 피벗 테이블 보고서에 사용되는 탐색 및 계산 경로를 결정하는 테이블 관계입니다. 다음 작업에서는 방금 가져온 데이터를 연결하기 위해 수동으로 관계를 만듭니다.

관계 추가

가져오는 모든 새 테이블에 대해 테이블 관계를 체계적으로 만들 수 있습니다. 통합 문서를 동료와 공유할 계획이며 동료들이 데이터에 대해 잘 모르는 경우 미리 정의된 관계가 있으면 유용합니다.

수동으로 관계를 만들 때는 한 번에 두 테이블을 사용하여 작업합니다. 각 테이블에서는 다른 테이블의 관련 행을 조회하는 방법을 Excel에 지시하는 열을 선택합니다.

브라우저가 비디오를 지원하지 않습니다. Microsoft Silverlight, Adobe Flash Player 또는 Internet Explorer 9를 설치하세요.

ProductSubcategory와 ProductCategory의 관계 설정

  1. Excel에서 데이터 > 관계 > 새로 만들기를 클릭합니다.

  2. 테이블에서 DimProductSubcategory를 선택합니다.

  3. 열(외래)에서 ProductCategoryKey를 선택합니다.

  4. 관련 표에서 Table_ProductCategory.accdb를 선택합니다.

  5. 관련 열(기본)에서 ProductCategoryKey를 선택합니다.

  6. 확인을 클릭합니다.

  7. 관계 관리 대화 상자를 닫습니다.

피벗 테이블에 범주 추가

데이터 모델이 추가 테이블 및 관계를 포함하도록 업데이트되었지만 아직 이러한 테이블 및 관계가 피벗 테이블에서 사용되지 않습니다. 이 작업에서는 피벗 테이블 필드 목록에 ProductCategory를 추가합니다.

  1. 피벗 테이블 필드에서 모두를 클릭하여 데이터 모델에 있는 테이블을 표시합니다.

  2. 목록의 아래쪽으로 스크롤합니다.

  3. 행 영역에서 BrandName을 제거합니다.

  4. Table_DimProductCategories.accdb를 확장합니다.

  5. ProductCategoryName을 행 영역으로 끌어 ProductSubcategory 위에 놓습니다.

  6. 피벗 테이블 필드에서 활성을 클릭하여 방금 사용한 테이블이 피벗 테이블에서 현재 사용 중인지 확인합니다.

확인 사항: 배운 내용 복습

이제 여러 테이블의 데이터가 포함된 피벗 테이블을 만들었으며, 이러한 테이블 중 몇 개는 이후 단계에서 가져왔습니다. 이 데이터를 통합하기 위해 Excel에서 행의 상관 관계를 지정하는 데 사용하는 테이블 관계를 만들어야 했습니다. 또한 일치하는 데이터를 제공하는 열이 있어야 관련 행을 조회할 수 있음을 배웠습니다. 예제 데이터 파일의 모든 테이블에는 이런 용도로 사용할 수 있는 열이 포함되어 있습니다.

피벗 테이블이 작동하긴 하지만 개선할 수 있는 몇 가지 사항을 발견했을 수도 있습니다. 피벗 테이블 필드 목록에는 Contoso의 비즈니스와 관련 없는 추가 테이블(DimEntity) 및 열(ETLLoadID)이 있습니다. 그리고, 아직 지역 데이터를 통합하지 않았습니다.

다음 단계: 파워 피벗을 사용하여 모델 보기 및 확장

다음 작업에서는 Microsoft Excel 2013의 Power Pivot 추가 기능을 사용하여 모델을 확장합니다. 이 추가 기능에서 제공하는 다이어그램 뷰를 사용하면 보다 쉽게 관계를 만들 수 있습니다. 또한 추가 기능을 사용하여 계산 및 계층 구조를 만들고, 필드 목록에 나타나지 않아야 하는 항목을 숨기며, 추가 보고를 위해 데이터를 최적화합니다.

참고: Microsoft Excel 2013의 Power Pivot 추가 기능은 Office Professional Plus에서 사용할 수 있습니다. 자세한 내용은 Microsoft Excel 2013의 PowerPivot 추가 기능을 참고하세요.

파워 피벗 추가 기능을 설정하여 Excel 리본 메뉴에 파워 피벗을 추가합니다.

  1. 파일 > 옵션 > 추가 기능으로 이동합니다.

  2. 관리 상자에서 COM 추가 기능 > 이동을 클릭합니다.

  1. Microsoft Excel 2013의 Power Pivot 상자를 선택한 다음 확인을 클릭합니다.

이제 리본 메뉴에 파워 피벗 탭이 나타납니다.

파워 피벗의 다이어그램 뷰를 사용하여 관계 추가

  1. Excel에서 Sheet3 시트를 클릭하여 활성화합니다. Sheet3에는 이전에 가져온 Geography 테이블이 있습니다.

  2. 리본 메뉴에서 파워 피벗 > 데이터 모델에 추가를 클릭합니다. 이 단계에서는 Geography 테이블을 모델에 추가합니다. 또한 이 작업의 나머지 단계를 수행하는 데 사용하는 파워 피벗 추가 기능도 열립니다.

  3. 파워 피벗 창에 Geography 테이블을 비롯한 모델의 모든 테이블이 표시됩니다. 몇 개의 테이블을 클릭합니다. 추가 기능에 모델에 포함되어 있는 모든 데이터가 표시됩니다.

  4. 파워 피벗 창의 뷰 구역에서 다이어그램 뷰를 클릭합니다.

  5. 다이어그램의 모든 개체를 볼 수 있도록 슬라이드 막대를 사용하여 다이어그램 크기를 조정합니다. 다이어그램의 나머지 테이블과 관련이 없는 두 테이블은 DimEntity와 Geography입니다.

  6. DimEntity를 마우스 오른쪽 단추로 클릭하고 삭제를 클릭합니다. 이 테이블은 원본 데이터베이스의 아티팩트이며 모델에는 필요하지 않습니다.

  7. 모든 필드를 볼 수 있도록 Geography를 확대합니다. 슬라이더를 사용하여 테이블 다이어그램을 더 크게 만들 수 있습니다.

  8. Geography에는 GeographyKey가 있습니다. 이 열에는 Geography 테이블의 각 행을 고유하게 식별하는 값이 포함되어 있습니다. 모델의 다른 테이블에도 이 키가 사용되는지 찾아보겠습니다. 찾는 경우 Geography를 모델의 나머지 테이블에 연결하는 관계를 만들 수 있습니다.

  9. 찾기를 클릭합니다.

  10. 메타데이터 찾기에서 GeographyKey를 입력합니다.

  11. 다음 찾기를 여러 번 클릭합니다. Geography 테이블과 Stores 테이블에 GeographyKey가 표시되는 것을 확인할 수 있습니다.

  12. Geography 테이블이 Stores 옆에 놓이도록 위치를 변경합니다.

  13. Stores의 GeographyKey 열을 Geography의 GeographyKey 열로 끕니다. 파워 피벗에서 두 열 사이에 관계를 나타내는 선을 그립니다.

이 작업에서는 테이블을 추가하고 관계를 만드는 새로운 방법을 알아봤습니다. 이제 모든 테이블이 Sheet1의 피벗 테이블에 연결되어 사용 가능한 완전히 통합된 모델을 갖게 되었습니다.

팁:  다이어그램 뷰에서 몇몇 테이블 다이어그램이 완전히 확장되어 ETLLoadID, LoadDate, UpdateDate 등의 열을 표시합니다. 이러한 특정 필드는 원본 Contoso 데이터 웨어하우스의 아티팩트로서, 데이터 추출 및 로드 작업을 지원하기 위해 추가되었으며 모델에서는 필요하지 않습니다. 필드를 제거하려면 필드를 강조 표시하고 마우스 오른쪽 단추로 클릭한 후 삭제 를 클릭합니다.

계산된 열 만들기

파워 피벗에서 DAX(데이터 분석 식)를 사용하여 계산을 추가할 수 있습니다. 이 작업에서는 총 수익을 계산하고, 다른 테이블의 데이터 값을 참조하는 계산된 열을 추가합니다. 그런 다음, 참조되는 열을 사용하여 모델을 단순화하는 방법을 알아봅니다.

  1. 파워 피벗 창에서 데이터 보기로 다시 전환합니다.

  2. Table_ProductCategories accdb 테이블을 보다 친숙한 이름으로 바꿉니다. 다음 단계에서 이 테이블을 참조하므로, 이름이 짧으면 계산을 더 쉽게 읽을 수 있습니다. 테이블 이름을 마우스 오른쪽 단추로 클릭한 후 이름 바꾸기를 클릭하고 ProductCategories를 입력한 다음 Enter 키를 누릅니다.

  3. FactSales 테이블을 선택합니다.

  4. 디자인 > > 추가를 클릭합니다.

  5. 테이블 위의 수식 입력줄에 다음 수식을 입력합니다. 자동 완성을 사용하면 정규화된 열 및 테이블 이름을 입력하고 사용 가능한 함수를 나열할 수 있습니다. 열을 클릭하기만 하면 파워 피벗에서 열 이름이 수식에 추가됩니다.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. 수식 작성을 마쳤으면 Enter 키를 눌러 수식을 적용합니다.

    계산된 열의 모든 행에 값이 채워집니다. 테이블을 아래로 스크롤하면 각 행에 있는 데이터에 따라 이 열에서 행마다 서로 다른 값을 포함할 수 있음을 알 수 있습니다.

  7. CalculatedColumn1을 마우스 오른쪽 단추로 클릭하고 열 이름 바꾸기를 선택하여 열의 이름을 바꿉니다. Profit을 입력하고 Enter 키를 누릅니다.

  8. 이제 DimProduct 테이블을 선택합니다.

  9. 디자인 > > 추가를 클릭합니다.

  10. 테이블 위의 수식 입력줄에 다음 수식을 입력합니다.

    = RELATED(ProductCategories[ProductCategoryName])

    RELATED 함수는 관련 테이블에서 값을 반환합니다. 이 경우 ProductCategories 테이블에는 제품 범주의 이름이 포함되어 있으며 이 이름은 범주 정보를 포함하는 계층 구조를 만들 때 DimProduct 테이블에 있으면 유용합니다. 이 함수에 대한 자세한 내용은 RELATED 함수(DAX)를 참고하세요.

  11. 수식 작성을 마쳤으면 Enter 키를 눌러 수식을 적용합니다.

    계산된 열의 모든 행에 값이 채워집니다. 테이블을 아래로 스크롤하면 이제 행마다 제품 범주 이름이 있는 것을 볼 수 있습니다.

  12. CalculatedColumn1을 마우스 오른쪽 단추로 클릭하고 열 이름 바꾸기를 선택하여 열의 이름을 바꿉니다. ProductCategory를 입력하고 Enter 키를 누릅니다.

  13. 디자인 > > 추가를 클릭합니다.

  14. 테이블 위의 수식 입력줄에 다음 수식을 입력한 다음 Enter 키를 눌러 수식을 적용합니다.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. CalculatedColumn1을 마우스 오른쪽 단추로 클릭하고 열 이름 바꾸기를 선택하여 열의 이름을 바꿉니다. ProductSubcategory를 입력하고 Enter 키를 누릅니다.

계층 구조 만들기

대부분의 모델에는 기본적으로 계층적인 데이터가 포함되어 있습니다. 일반적인 예로는 일정 데이터, 지리 데이터, 제품 범주 등이 있습니다. 계층 구조를 만들면 같은 필드를 여러 번 결합하고 순서를 지정할 필요 없이 한 항목(계층)을 보고서로 끌어올 수 있기 때문에 유용합니다.

  1. 파워 피벗에서 다이어그램 뷰로 전환합니다. 모든 필드를 보다 쉽게 볼 수 있도록 DimDate 테이블을 확장합니다.

  2. Ctrl 키를 누른 채 CalendarYear, CalendarQuarter, CalendarMonth 열을 클릭합니다(테이블을 아래로 스크롤해야 함).

  3. 세 개의 열을 선택한 상태에서 그 중 하나를 마우스 오른쪽 단추로 클릭하고 계층 구조 만들기를 클릭합니다. 부모 계층 노드인 계층 1이 테이블 아래쪽에 만들어지고, 선택한 열이 계층 아래에 자식 노드로 복사됩니다.

  4. 새 계층 구조의 이름으로 Dates를 입력합니다.

  5. 계층 구조에 FullDateLabel 열을 추가합니다. FullDateLabel을 마우스 오른쪽 단추로 클릭하고 계층 구조에 추가를 선택합니다. Date를 선택합니다. FullDateLabel에는 연도, 월, 일을 포함한 전체 날짜가 있습니다. FullDateLabel이 계층 구조의 마지막에 나타나는지 확인합니다. 이제 연도, 분기, 월 및 개별 달력 일이 포함된 다단계 계층 구조가 생깁니다.

  6. 다이어그램 뷰가 활성화된 상태에서 DimProduct 테이블을 가리킨 다음 테이블 머리글에서 계층 구조 만들기 단추를 클릭합니다. 빈 계층 부모 노드가 테이블 아래쪽에 표시됩니다.

  7. 새 계층 구조의 이름으로 Product Categories를 입력합니다.

  8. 계층 자식 노드를 만들려면 ProductCategory 및 ProductSubcategory를 계층 구조로 끕니다.

  9. ProductName을 마우스 오른쪽 단추로 클릭하고 계층 구조에 추가를 선택합니다. Product Categories를 선택합니다.

지금까지 계층 구조를 만드는 여러 방법을 알아봤습니다. 이제 피벗 테이블에서 이러한 방법을 사용해 보겠습니다.

  1. Excel로 돌아갑니다.

  2. Sheet1(피벗 테이블이 들어 있는 시트)에서 행 영역에 있는 필드를 제거합니다.

  3. 해당 필드를 DimProduct의 새로운 Product Categories 계층 구조로 바꿉니다.

  4. 마찬가지로, 열 영역의 CalendarYear를 DimDate의 Dates 계층 구조로 바꿉니다.

이제 데이터를 탐색해 보면 계층 구조를 사용하는 이점을 쉽게 확인할 수 있습니다. 피벗 테이블의 여러 영역을 독립적으로 확장하고 닫을 수 있으므로 사용 가능한 공간을 이용하는 방법을 보다 효율적으로 제어할 수 있습니다. 뿐만 아니라 행 및 열에 단일 계층 구조를 추가하면 비슷한 효과를 얻기 위해 여러 필드를 쌓을 필요 없이 강력하고 즉각적인 드릴다운을 사용할 수 있습니다.

열 숨기기

이제 Product Categories 계층 구조를 만들어 DimProduct에 배치했으므로 피벗 테이블 필드 목록에서 DimProductCategory 또는 DimProductSubcategory가 더 이상 필요하지 않습니다. 이 작업에서는 피벗 테이블 필드 목록에서 공간을 차지하는 불필요한 테이블과 열을 숨기는 방법을 알아봅니다. 테이블 및 열을 숨기면 데이터 관계 및 계산을 제공하는 모델에 영향을 주지 않고 보고 환경을 향상시킬 수 있습니다.

브라우저가 비디오를 지원하지 않습니다. Microsoft Silverlight, Adobe Flash Player 또는 Internet Explorer 9를 설치하세요.

개별 열, 열 범위 또는 전체 테이블을 숨길 수 있습니다. 테이블 및 열 이름은 모델을 사용하는 보고 클라이언트에 숨겨짐을 나타내기 위해 회색으로 나타납니다. 숨겨진 열은 모델에 회색으로 표시되어 숨김 상태를 나타내지만, 데이터 보기에는 계속 표시되므로 해당 열을 계속 사용할 수 있습니다.

  1. 파워 피벗에서 데이터 보기가 선택되어 있는지 확인합니다.

  2. 아래쪽 탭에서 DimProductSubcategory를 마우스 오른쪽 단추로 클릭하고 클라이언트 도구에서 숨기기를 클릭합니다.

  3. ProductCategories에 대해 이 과정을 반복합니다.

  4. DimProduct를 엽니다.

  5. 다음 열을 마우스 오른쪽 단추로 클릭하고 클라이언트 도구에서 숨기기를 클릭합니다.

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. 인접한 열을 여러 개 선택합니다. ClassID부터 ProductSubcategory까지 선택합니다. 마우스 오른쪽 단추를 클릭하여 숨깁니다.

  7. 다른 테이블에 대해 이 과정을 반복하여 이 보고서에 사용하지 않을 ID, 키 또는 기타 세부 정보를 제거합니다.

Excel로 다시 전환하여 Sheet1에서 피벗 테이블 필드 목록의 차이를 확인해 보면 테이블의 수가 줄었고 DimProduct에는 판매를 분석할 때 사용할 가능성이 높은 항목만 있음을 알 수 있습니다.

Power View 보고서 만들기

피벗 테이블 보고서가 데이터 모델을 이용하는 유일한 보고서는 아닙니다. 여기서 만든 동일한 모델을 사용하여 Power View 시트를 추가해 Power View에서 제공하는 레이아웃을 확인해 볼 수 있습니다.

  1. Excel에서 삽입 > Power View를 클릭합니다.

    참고:  이 컴퓨터에서 Power View를 처음 사용하는 경우에는 먼저 추가 기능을 설정하고 Silverlight를 설치하라는 메시지가 나타납니다.

  2. Power View 필드에서 FactSales 테이블 옆에 있는 화살표를 클릭하고 SalesAmount를 클릭합니다.

  3. Geography 테이블을 확장하고 RegionCountryName을 클릭합니다.

  4. 리본 메뉴에서 지도를 클릭합니다.

  5. 지도 보고서가 표시됩니다. 모서리를 끌어 크기를 조정합니다. 지도에서 다양한 크기의 파란색 원은 여러 국가 또는 지역의 판매 실적을 나타냅니다.

Power View 보고를 위한 최적화

모델에서 간단한 몇 가지를 변경하면 Power View 보고서를 디자인할 때 보다 직관적인 결과를 얻을 수 있습니다. 이 작업에서는 여러 제조업체의 웹 사이트 URL을 추가한 다음 URL 주소가 링크로 표시되도록 해당 데이터를 웹 URL로 분류하겠습니다.

먼저 통합 문서에 URL을 추가합니다.

  1. Excel에서 새 시트를 열고 다음 값을 복사합니다.

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. 셀을 표로 서식 지정한 다음 테이블 이름을 URL로 지정합니다.

  2. URL과 제조업체 이름이 포함된 테이블인 DimProduct 사이에 관계를 만듭니다.

    1. 데이터 > 관계를 클릭합니다. 관계 만들기 대화 상자가 나타납니다.

    2. 새로 만들기를 클릭합니다.

    3. 테이블에서 DimProduct를 선택합니다.

    4. 열에서 Manufacturer를 선택합니다.

    5. 관련 표에서 URL을 선택합니다.

    6. 관련 열(기본)에서 ManufacturerID를 선택합니다.

이전 및 이후 결과를 비교하려면 새 Power View 보고서를 시작하고 FactSales | SalesAmount, dimProduct | Manufacturer, URL | ManufacturerURL을 보고서에 추가합니다. URL이 정적 텍스트로 표시됩니다.

URL을 활성 하이퍼링크로 렌더링하려면 범주화해야 합니다. 열을 범주화하려면 파워 피벗을 사용합니다.

  1. 파워 피벗에서 URL을 엽니다.

  2. ManufacturerURL을 선택합니다.

  3. 고급 > 보고 속성 > 데이터 범주: 범주화되지 않음을 클릭합니다.

  4. 아래쪽 화살표를 클릭합니다.

  5. 웹 URL을 선택합니다.

  6. Excel에서 삽입 > Power View를 클릭합니다.

  7. Power View 필드에서 FactSales | SalesAmount, dimProduct | Manufacturer, URL | ManufacturerURL을 선택합니다. 이제 URL이 실제 하이퍼링크로 표시됩니다.

다른 Power View 최적화에는 각 테이블에 대한 기본 필드 집합을 정의하고 반복 데이터의 행이 독립적으로 나열되는지 아니면 집계되는지를 결정하는 속성을 설정하는 작업이 포함됩니다. 자세한 내용은 Power View 보고서의 기본 필드 집합 구성Power View 보고서의 테이블 동작 속성 구성을 참고하세요.

계산 필드 만들기

두 번째 작업인 '피벗 테이블을 사용하여 데이터 탐색'에서는 피벗 테이블 필드 목록에서 SalesAmount 필드를 클릭했습니다. SalesAmount는 숫자 열이기 때문에 피벗 테이블의 값 영역에 자동으로 배치되었습니다. 그런 다음 어떤 필터를 적용하든 합계: SalesAmount를 통해 해당 판매액을 계산할 수 있었습니다. 이 예제의 경우 처음에 필터가 없지만 이후에 CalendarYear, ProductSubcategoryName, BrandName이 사용됩니다.

여기에서 실제로 수행한 작업은 FactSales 테이블의 판매액을 제품 범주, 지역, 날짜 등의 다른 필드와 비교하여 쉽게 분석할 수 있도록 암시적 계산 필드를 만든 것입니다. 암시적 계산 필드는 필드를 값 영역으로 끌거나 SalesAmount 등과 같은 숫자 필드를 클릭하는 경우 Excel에서 만들어집니다. 암시적 계산 필드는 SUM, COUNT, AVERAGE 등과 같은 표준 집계 함수를 사용하는 수식이며 자동으로 만들어집니다.

다른 유형의 계산 필드도 있습니다. 파워 피벗에서 명시적 계산 필드를 만들 수 있습니다. 만들어진 피벗 테이블에서만 사용할 수 있는 암시적 계산 필드와 달리, 명시적 계산 필드는 통합 문서의 모든 피벗 테이블에서 사용하거나 데이터 모델을 데이터 원본으로 사용하는 모든 보고서에서 사용할 수 있습니다. 파워 피벗에서 만든 명시적 계산 필드를 사용하면 자동 합계를 사용하여 표준 집계를 사용하는 계산 필드를 자동으로 만들거나 DAX(데이터 분석 식)를 사용하여 만든 수식을 사용하여 고유한 필드를 만들 수 있습니다.

계산 필드를 만들면 다양하고 강력한 방법으로 데이터를 분석할 수 있습니다. 지금부터 계산 필드를 만드는 방법을 알아보겠습니다.

자동 합계를 사용하면 쉽게 파워 피벗에서 계산 필드를 만들 수 있습니다.

  1. FactSales 테이블에서 Profit 열을 클릭합니다.

  2. 계산 > 자동 합계를 클릭합니다. Profit 열 아래에 있는 계산 영역의 셀에 합계: Profit이라는 새 계산 영역이 자동으로 만들어집니다.

  3. Excel에서 Sheet1의 필드 목록에 있는 FactSales합계: Profit을 클릭합니다.

모두 마쳤습니다. 이런 간단한 방법으로 파워 피벗에서 표준 집계를 사용하는 계산 필드를 만들 수 있습니다. 몇 분 안에 합계: Profit 계산 필드를 만들고 이 필드를 피벗 테이블에 추가하여 적용하는 필터에 따라 쉽게 수익을 분석할 수 있습니다. 이 예제에서는 합계: Profit이 제품 범주 및 날짜 계층 구조를 기준으로 필터링되어 표시됩니다.

특정 채널, 제품 또는 범주에 대한 판매 건수와 같이 보다 자세한 분석을 수행해야 하는 경우에는 적용된 필터에 따라 FactSales 테이블에서 행 수를 계산하는 다른 계산 필드를 판매별로 한 개씩 만들어야 합니다.

  1. FactSales 테이블에서 SalesKey 열을 클릭합니다.

  2. 계산에서 자동 합계 아래쪽 화살표를 클릭하고 개수를 클릭합니다.

  3. 계산 영역에서 SalesKey의 개수를 마우스 오른쪽 단추로 클릭한 다음 이름 바꾸기를 선택하여 새 계산 필드의 이름을 바꿉니다. Count를 입력하고 Enter 키를 누릅니다.

  4. Excel에서 Sheet1의 필드 목록에 있는 FactSalesCount를 클릭합니다.

새로운 열인 Count가 피벗 테이블에 추가되어 적용된 필터에 따라 판매 건수를 보여줍니다. 합계: Profit 계산 필드의 경우와 마찬가지로 Count 열도 제품 범주 및 날짜 계층 구조를 기준으로 필터링됩니다.

다른 계산 필드를 만들어 보겠습니다. 이번에는 특정 컨텍스트 또는 필터의 총 판매액 백분율을 계산하는 계산 필드를 만듭니다. 단, 자동 합계를 사용하여 만든 이전의 계산 필드와 달리 수동으로 수식을 입력하겠습니다.

  1. FactSales 테이블의 계산 영역에서 빈 셀을 클릭합니다. 팁: 왼쪽 위 셀부터 계산 필드를 배치하는 것이 좋습니다. 이렇게 하면 계산 필드를 쉽게 찾을 수 있습니다. 계산 영역 내에서 계산 필드를 이동할 수 있습니다.

  2. 수식 입력줄에서 수식을 입력하면서 IntelliSense를 사용하여 다음 수식을 만듭니다. Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Enter 키를 눌러 수식을 적용합니다.

  4. Excel에서 Sheet1의 필드 목록에 있는 FactSalesPercentage of All Products를 클릭합니다.

  5. 피벗 테이블에서 Percentage of All Products 열을 여러 개 선택합니다.

  6. 탭에서 표시 형식 > 백분율을 클릭합니다. 소수점 이하 두 자리를 사용하여 새로운 각 열의 서식을 지정합니다.

이 새 계산 필드는 주어진 필터 컨텍스트에 대한 총 판매액의 백분율을 계산합니다. 이 예제에서 필터 컨텍스트는 제품 범주 및 날짜 계층 구조입니다. 예를 들어 컴퓨터에 대한 총 제품 판매 비율이 몇 년 동안 증가한 것을 볼 수 있습니다.

Excel 수식 만들기에 익숙한 사용자라면 계산된 열과 계산 필드 모두에 대한 수식을 상당히 쉽게 만들 수 있습니다. Excel 수식에 대해 잘 알고 있는지 여부와 관계없이, DAX 수식의 기본 사항에 대해 배우려면 퀵 스타트: 30분 내에 DAX 기본 익히기에서 설명하는 단계를 따르세요.

작업 내용 저장

다른 자습서 또는 추가 탐색에 사용할 수 있도록 통합 문서를 저장합니다.

다음 단계

Excel에서 데이터를 쉽게 가져올 수 있지만 파워 피벗 추가 기능을 사용하여 가져오는 것이 더 빠르고 효율적인 경우가 많습니다. 가져올 데이터를 필터링하여 필요 없는 열을 제외할 수 있으며, 데이터를 검색하기 위해 쿼리 작성기 또는 쿼리 명령을 선택할 수도 있습니다. 다음 단계에서는 이러한 다른 방법인 파워 피벗의 데이터 피드에서 데이터 가져오기Analysis Services 또는 PowerPivot에서 데이터 가져오기에 대해 알아봅니다.

Power View 보고는 여기에서 만든 것과 유사한 데이터 모델과 작동하도록 디자인되었습니다. Power View가 Excel에 구현하는 풍부한 데이터 시각화에 대한 자세한 내용은 Excel 2013에서 Power View 시작Power View: 데이터 탐색, 시각화, 프레젠테이션을 참고하세요.

더 효율적인 Power View 보고서를 만들기 위해 데이터 모델을 향상시키는 방법은 자습서: Power View 보고에 데이터 모델 최적화를 참고하세요.

Office 기술 확장
교육 살펴보기
새로운 기능 우선 가져오기
Office Insider 참여

이 정보가 유용한가요?

의견 주셔서 감사합니다!

피드백을 주셔서 감사합니다. Office 지원 에이전트와 연락하는 것이 도움이 될 것 같습니다.

×