Excel 2010 피벗 테이블을 구성하는 방법

by 팀 퍼듀

01/15

최종 결과

이 단계별 튜토리얼의 최종 결과입니다. 이미지를 클릭하면 전체 크기 버전을 볼 수 있습니다.

수년 동안 Microsoft Excel과 최상위 계층 BI (Business Intelligence) 플랫폼 간에는 격차가있었습니다. Microsoft Excel 2010 피벗 테이블의 향상된 기능과 몇 가지 다른 BI 기능으로 인해 엔터프라이즈 BI의 진정한 경쟁자가되었습니다. Excel은 전통적으로 독립형 분석 및 모든 사람이 최종 보고서를 내보내는 표준 도구에 사용되었습니다. 전문 비즈니스 인텔리전스는 전통적으로 SAS, Business Objects 및 SAP 등을 위해 예약되었습니다.

Microsoft Excel 2010 (Excel 2010 피벗 테이블 포함)은 SQL Server 2008 R2, SharePoint 2010 및 무료 Microsoft Excel 2010 추가 기능 "PowerPivot"과 함께 고급 비즈니스 인텔리전스 및보고 솔루션을 제공합니다.

이 자습서에서는 간단한 SQL 쿼리를 사용하여 SQL Server 2008 R2 데이터베이스에 연결된 Excel 2010 피벗 테이블의 간단한 시나리오를 다룹니다. 저는 또한 Excel 2010의 새로운 시각적 필터링을 위해 슬라이서 (Slicers)를 사용하고 있습니다. 가까운 미래에 Excel 2010 용 PowerPivot에서 데이터 분석 식 (DAX)을 사용하여보다 복잡한 BI 기술을 다루겠습니다. 이 최신 Microsoft Excel 2010 릴리스는 사용자 커뮤니티에 진정한 가치를 제공 할 수 있습니다.

02/15

피벗 테이블 삽입

피벗 테이블을 원할 때 커서를 정확하게 위치시키고 삽입 | 피벗 테이블.

새 또는 기존 Excel 통합 문서에 피벗 테이블을 삽입 할 수 있습니다. 커서를 위로부터 몇 줄 아래로 배치하는 것이 좋습니다. 이렇게하면 워크 시트를 공유하거나 인쇄 할 때 헤더 나 회사 정보를 저장할 공간이 생깁니다.

새 또는 기존 Excel 2010 통합 문서를 열고 피벗 테이블의 왼쪽 위 모서리를 원하는 셀을 클릭합니다.
삽입 탭을 클릭하고 표 섹션에서 피벗 테이블 드롭 다운을 클릭하십시오. 피벗 테이블을 선택하십시오. 피벗 테이블 만들기 대화 상자가 시작됩니다.

03/15

피벗 테이블을 SQL Server (또는 기타 데이터베이스)에 연결

SQL 쿼리를 만든 다음 SQL Server에 연결하여 연결 데이터 문자열을 Excel 스프레드 시트에 포함합니다.

Excel 2010은 모든 주요 RDBMS (관계형 데이터베이스 관리 시스템) 공급자로부터 데이터를 검색 할 수 있습니다. SQL Server 드라이버는 기본적으로 연결에 사용할 수 있어야합니다. 그러나 모든 주요 데이터베이스 소프트웨어는 ODBC (Open Database Connectivity) 드라이버를 만들어 연결을 허용합니다. ODBC 드라이버를 다운로드해야하는 경우 해당 웹 사이트를 확인하십시오.

이 자습서의 경우 SQL Server 2008 R2 (SQL Express 무료 버전)에 연결합니다.

A - 피벗 테이블 만들기 (Create PivotTable) 양식은 SQL Server에 대한 연결을 만드는 첫 번째 양식입니다. "외부 데이터 소스 사용"을 선택하고 연결 선택 버튼을 클릭하십시오. 새 워크 시트를 작성하여 배치하려는 경우가 아니면 피벗 테이블을 배치 할 위치를 그대로 둡니다.
B - Existing Connections (현재 연결) 양식에는 현재 통합 문서, 컴퓨터 및 현재 연결되어있는 네트워크의 모든 연결이 나열됩니다. 기존 연결은 실제로 특정 데이터 소스에 액세스하는 데 필요한 연결 정보가있는 텍스트 파일입니다. 여기에서는 새로운 데이터 소스를 생성하려고합니다. Browse for More 버튼을 클릭하십시오.
C - New Source (새 소스) 버튼을 클릭하면 데이터 연결 마법사가 시작됩니다.
D - Microsoft SQL Server를 선택하고 다음을 클릭하십시오.
E - 서버 이름과 로그온 자격 증명을 입력하십시오. 적절한 인증 방법을 선택하십시오. 어떤 방법을 사용해야하는지 잘 모르겠 으면 데이터베이스 관리자에게 문의하십시오.
- Windows 인증 사용 : 이 방법은 네트워크 로그인을 사용하여 SQL Server 데이터베이스에 액세스합니다.
- 다음 사용자 이름 및 암호를 사용하십시오. 이 방법은 독립 실행 형 사용자가 데이터베이스에 액세스하도록 SQL Server를 구성한 경우 사용됩니다.
F -이 단계에서는 테이블을 자리 표시 자로 선택합니다. Excel 통합 문서에서 원하는 데이터를 정확하게 제공 할 수있는 사용자 지정 SQL로 테이블을 대체하려고합니다.
- 연결할 데이터베이스를 선택하십시오. 이 예에서는 Microsoft가 제공하는 AdventureWorks 예제 데이터베이스에 연결합니다. 특정 테이블에 연결을 선택하고 첫 번째 테이블을 선택하십시오. 이 테이블에서 데이터를 검색하지 않을 것임을 기억하십시오.
- 마침을 클릭하면 마법사가 닫히고 통합 문서로 돌아갑니다. 우리는 커스텀 SQL 질의를위한 자리 표시 자 테이블을 교체 할 것입니다.

피벗 테이블 만들기 (A) 양식으로 돌아갑니다. 확인을 클릭하십시오.

04 / 15

일시적으로 SQL 테이블에 연결된 피벗 테이블

PivotTable은 SQL Server에 자리 표시 자 테이블과 연결됩니다.

이 시점에서 자리 표시 자 테이블에 연결되어 있고 비어있는 피벗 테이블이 있습니다. 왼쪽에는 피벗 테이블이 있고 오른쪽에는 사용할 수있는 필드 목록이 있습니다.

15 중 15

연결 등록 정보 열기

연결 등록 정보 양식을 엽니 다.

피벗 테이블에 대한 데이터를 선택하기 전에 SQL 쿼리에 대한 연결을 변경해야합니다. 옵션 탭에 있는지 확인하고 데이터 섹션에서 데이터 소스 드롭 다운 변경을 클릭하십시오. 연결 속성을 선택하십시오.

그러면 연결 특성 양식이 나타납니다. 정의 탭을 클릭하십시오. 여기에는 SQL Server에 대한 현재 연결에 대한 연결 정보가 표시됩니다. 연결 파일을 참조하는 동안 데이터는 스프레드 시트에 실제로 포함됩니다.

06/15

쿼리로 연결 속성 업데이트

테이블을 SQL 쿼리로 변경하십시오.

명령 유형을 테이블에서 SQL로 변경하고 기존 명령 텍스트를 SQL 쿼리로 덮어 씁니다. AdventureWorks 예제 데이터베이스에서 작성한 쿼리는 다음과 같습니다.

SELECT Sales.SalesOrderHeader.SalesOrderID를 선택하십시오.
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.Freight,
Sales.SalesOrderHeader.TotalDue,
Sales.SalesOrderDetail.SalesOrderDetailID,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.LineTotal,
생산. 제품명,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
생산. 제품. 제품 라인,
Production.ProductSubcategory.Name AS ProductCategory
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

확인을 클릭하십시오.

07/15

수신 연결 경고

Yes to Connection Warning을 클릭하십시오.

Microsoft Excel 경고 대화 상자가 나타납니다. 우리가 연결 정보를 변경했기 때문입니다. 원래 연결을 만들었을 때 외부 .ODC 파일 (ODBC 데이터 연결)에 정보를 저장했습니다. 통합 문서의 데이터는 # 6 단계에서 테이블 명령 유형을 SQL 명령 유형으로 변경할 때까지 .ODC 파일과 동일합니다. 경고는 데이터가 더 이상 동기화되지 않으며 통합 문서의 외부 파일에 대한 참조가 제거된다고 알려줍니다. 괜찮습니다. 예를 클릭하십시오.

8 중 15

쿼리로 SQL Server에 연결된 피벗 테이블

피벗 테이블에 데이터를 추가 할 준비가되었습니다.

이 작업은 빈 피벗 테이블이있는 Excel 2010 통합 문서로 다시 이동합니다. 사용 가능한 필드가 이제 다르며 SQL 쿼리의 필드에 해당하는 것을 볼 수 있습니다. 이제 피벗 테이블에 필드를 추가 할 수 있습니다.

09/15

피벗 테이블에 필드 추가

피벗 테이블에 필드를 추가하십시오.

피벗 테이블 필드 목록에서 ProductCategory를 Row Labels 영역, OrderDate를 Column Labels 영역 및 TotalDue to Values 영역으로 끕니다. 이미지에 결과가 표시됩니다. 보시다시피 날짜 필드에는 개별 날짜가 있으므로 피벗 테이블에 각 고유 날짜에 대한 열이 만들어집니다. 다행히도 Excel 2010에는 날짜 필드를 구성하는 데 도움이되는 일부 기능이 내장되어 있습니다.

10/15

날짜 필드에 그룹화 추가

날짜 필드에 그룹화 추가.

그룹화 기능을 사용하면 년, 월, 분기 등으로 날짜를 구성 할 수 있습니다. 이렇게하면 데이터를 요약하고 사용자가 쉽게 상호 작용할 수 있습니다. 날짜 열 머리글 중 하나를 마우스 오른쪽 단추로 클릭하고 그룹화를 선택하면 그룹화 양식이 나타납니다.

11/15

값으로 그룹화 선택

날짜 필드에 대한 그룹화 항목을 선택하십시오.

그룹화 할 데이터의 종류에 따라 양식이 약간 다르게 보일 것입니다. Excel 2010에서는 날짜, 숫자 및 선택한 텍스트 데이터를 그룹화 할 수 있습니다. 이 자습서에서 OrderDate를 그룹화하므로 양식에 날짜 그룹화와 관련된 옵션이 표시됩니다.

월과 년을 클릭하고 확인을 클릭하십시오.

12/15

년 및 월별 그룹화 된 피벗 테이블

날짜 필드는 연도 및 월별로 그룹화됩니다.

위 이미지에서 볼 수 있듯이 데이터는 연도별로 먼저 그룹화되어 다음 달에 그룹화됩니다. 각 데이터에는 데이터를 보려는 방식에 따라 확장하거나 축소 할 수있는 더하기 및 빼기 기호가 있습니다.

이 시점에서 피벗 테이블은 매우 유용합니다. 각 필드는 필터링 할 수 있지만 문제는 필터의 현재 상태에 대한 시각적 단서가 없음을 나타냅니다. 또한보기를 변경하려면 클릭하는 데 몇 번 걸립니다.

13/15

슬라이서 삽입 (Excel 2010의 새로운 기능)

슬라이서를 피벗 테이블에 추가하십시오.

슬라이서는 Excel 2010의 새로운 기능입니다. 기본적으로 슬라이서는 기존 필드의 필터를 시각적으로 설정하고 필터링 할 항목이 현재 피벗 테이블보기에없는 경우 보고서 필터를 만드는 것과 같습니다. 슬라이서에 대한 좋은 점은 사용자가 피벗 테이블에서 데이터보기를 변경하고 필터의 현재 상태에 대한 시각적 인 표시기를 제공하는 것이 매우 쉽다는 것입니다.

슬라이서를 삽입하려면 옵션 탭을 클릭하고 정렬 및 필터 섹션에서 슬라이서 삽입을 클릭하십시오. Insert Slicer를 선택하면 Insert Slicers 창이 열립니다. 사용 가능한 필드 수만큼 선택하십시오. 이 예에서는 Years, CountryRegionName 및 ProductCategory를 추가했습니다. 슬라이서를 원하는 위치에 배치해야 할 수도 있습니다. 기본적으로 필터가 적용되지 않았 음을 나타내는 모든 값이 선택됩니다.

14/15

사용자 친화적 인 슬라이서로 피벗 테이블

슬라이서를 사용하면 사용자가 피벗 테이블을 더 쉽게 필터링 할 수 있습니다.

보시다시피 슬라이서는 모든 데이터를 선택하여 표시합니다. 피벗 테이블의 현재보기에있는 데이터가 사용자에게 정확히 전달됩니다.

15/15

피벗 테이블을 업데이트하는 슬라이서 값 선택

슬라이서 조합을 선택하여 데이터보기를 변경하십시오.

다양한 값 조합을 클릭하고 피벗 테이블보기 변경 방법을 확인하십시오. 슬라이서에서 일반적인 Microsoft 클릭을 사용하면 Ctrl + 클릭으로 여러 값을 선택하거나 Shift + 클릭으로 값 범위를 선택할 수 있습니다. 각 슬라이서에는 선택한 값이 표시되므로 피벗 테이블의 상태가 필터라는 점이 무엇인지 명확하게 알 수 있습니다. 옵션 탭의 슬라이서 섹션에서 빠른 스타일 드롭 다운을 클릭하여 원하는 경우 슬라이서 스타일을 변경할 수 있습니다.

슬라이서 (Slicers)의 도입으로 피벗 테이블 (PivotTables)의 유용성이 크게 향상되었으며 Excel 2010은 전문적인 비즈니스 인텔리전스 도구에 훨씬 가까워졌습니다. Excel 2010에서 피벗 테이블이 상당히 향상되었으며 새로운 PowerPivot과 함께 사용하면 매우 높은 성능의 분석 환경을 만들 수 있습니다.