Excel 2010ピボットテーブルを構成する方法

by ティムパーデュー

01/15

最終結果

これは、このステップバイステップチュートリアルの最終結果です。画像をクリックすると、フルサイズのバージョンが表示されます。

マイクロソフトエクセルと最上位のビジネスインテリジェンス（BI）プラットフォームとの間には長年にわたってギャップがありました。 Microsoft Excel 2010 Pivot Tableの拡張機能とその他のいくつかの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ピボットテーブルを使用した簡単なシナリオについて説明します。近い将来、PowerPivot for Excel 2010でData Analysis Expressions（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ドライバをダウンロードする必要がある場合は、Webサイトを確認してください。

このチュートリアルでは、SQL Server 2008 R2（SQL Expressの無料版）に接続しています。

A - ピボットテーブルの作成フォームは、SQL Serverへの接続を作成する最初のフォームです。 [外部データソースを使用する]を選択し、[接続の選択]ボタンをクリックします。新しいワークシートを作成して配置する場合を除き、ピボット・テーブルの配置場所を変更しないでください。
B - [既存の接続]フォームには、現在のワークブック、コンピュータ、および現在接続しているネットワーク上のすべての接続が一覧表示されます。既存の接続は、実際には、特定のデータソースにアクセスするために必要な接続情報を持つテキストファイルです。ここでは、新しいデータソースを作成します。 Browse for Moreボタンをクリックします。
C - [新しいソース]ボタンをクリックすると、データ接続ウィザードが起動します。
D - [Microsoft SQL Server]を選択し、[次へ]をクリックします。
E - サーバー名とログオン情報を入力します。適切な認証方法を選択します。使用する方法がわからない場合は、データベース管理者に連絡してください。
- Windows認証を使用する：この方法では、ネットワークログインを使用してSQL Serverデータベースにアクセスします。
- 次のユーザー名とパスワードを使用します。SQL Serverがスタンドアロンユーザーでデータベースにアクセスするように構成されている場合に使用します。
F - このステップでは、テーブルをプレースホルダとして選択します。私たちはExcelワークブックに必要なデータを正確に提供するカスタムSQLでテーブルを置き換えようとしています。
- 接続先のデータベースを選択します。この例では、Microsoftが提供するAdventureWorksサンプルデータベースに接続しています。特定のテーブルに接続し、最初のテーブルを選択します。この表からデータを取得するわけではありません。
- [完了]をクリックすると、ウィザードが閉じてワークブックに戻ります。カスタムSQLクエリのプレースホルダテーブルを交換します。

ピボットテーブルの作成フォーム（A）に戻ります。 [OK]をクリックします。

04/15

SQLテーブルに一時的に接続されたピボットテーブル

ピボットテーブルはプレースホルダテーブルを使用してSQL Serverに接続します。

この時点で、プレースホルダテーブルに接続し、空のピボットテーブルがあります。左側にはピボットテーブルが表示され、右側には利用可能なフィールドの一覧が表示されています。

05/15

接続のプロパティを開く

「接続プロパティー」フォームを開きます。

ピボットテーブルのデータを選択する前に、SQLクエリへの接続を変更する必要があります。 [オプション]タブにあることを確認し、[データ]セクションの[データソースの変更]ドロップダウンをクリックします。接続プロパティを選択します。

これにより、Connection Propertiesフォームが表示されます。定義タブをクリックします。 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、
Production.Product.Name、
Sales.vIndividualCustomer.StateProvinceName、Sales.vIndividualCustomer.CountryRegionName、
Sales.Customer.CustomerType、
Production.Product.ListPrice、
Production.Product.ProductLine、
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

[OK]をクリックします。

07/15

受信接続警告

Yes to Connection Warningをクリックします。

Microsoft Excel警告ダイアログボックスが表示されます。これは、接続情報を変更したためです。最初に接続を作成したときに、情報は外部の.ODCファイル（ODBCデータ接続）に保存されました。手順6でテーブルコマンドタイプからSQLコマンドタイプに変更するまで、ワークブックのデータは.ODCファイルと同じでした。この警告は、データが同期しなくなり、ワークブック内の外部ファイルへの参照が削除されることを示しています。これで結構です。 [はい]をクリックします。

08の15

クエリでSQL Serverに接続されたピボットテーブル

ピボットテーブルでデータを追加できます。

これは、ピボットテーブルが空のExcel 2010ブックに戻ります。使用可能なフィールドが異なるようになり、SQLクエリのフィールドに対応していることがわかります。これでピボットテーブルにフィールドを追加できるようになりました。

09/15

ピボットテーブルにフィールドを追加する

ピボットテーブルにフィールドを追加します。

ピボットテーブルフィールドリストで、ProductCategoryをRow Labels領域、OrderDateをColumn Labels領域、TotalDue to Values領域にドラッグします。画像に結果が表示されます。ご覧のように、日付フィールドには個別の日付が設定されているため、PivotTableは各固有の日付の列を作成しています。幸いにも、Excel 2010には、日付フィールドを整理するのに役立つ機能が組み込まれています。

10/15

日付フィールドのグループ化を追加する

日付フィールドのグループ化を追加します。

グループ化機能を使用すると、年月、月、四半期などに日付を整理することができます。これにより、データの要約やユーザーとのやりとりが容易になります。日付列ヘッダーの1つを右クリックし、「グループ化」を選択すると、「グループ化」フォームが表示されます。

11の15

値によるグループ化を選択

日付フィールドのグループ化項目を選択しました。

グループ化しているデータの種類によっては、フォームが少し違って見えます。 Excel 2010では、日付、数字、選択したテキストデータをグループ化できます。このチュートリアルではOrderDateをグループ化していますので、フォームに日付のグループ化に関するオプションが表示されます。

Months and Yearsをクリックし、OKをクリックします。

12/15

ピボットテーブルを年単位および月単位でグループ化

日付フィールドは年と月でグループ化されます。

上記の画像でわかるように、データは年ごとに、月ごとにグループ化されています。それぞれにプラス記号とマイナス記号があり、データをどのように表示するかによって、拡大したり折りたたんだりすることができます。

この時点で、ピボットテーブルは非常に便利です。各フィールドをフィルタリングすることはできますが、問題はフィルタの現在の状態に関する視覚的な手がかりがないことです。また、ビューを変更するには数回のクリックが必要です。

13の15

スライサーを挿入する（Excel 2010の新機能）

スライサーをピボットテーブルに追加します。

スライサーはExcel 2010の新機能です。スライサーは、基本的に既存のフィールドのフィルターを視覚的に設定し、フィルター処理するアイテムが現在のピボットテーブルビューにない場合にレポートフィルターを作成するのと同じです。スライサーについてのこの素晴らしい点は、ユーザーがピボットテーブル内のデータビューを変更し、フィルターの現在の状態に関するビジュアルインジケーターを提供することが非常に簡単になることです。

スライサーを挿入するには、[オプション]タブをクリックし、[並べ替えとフィルター]セクションから[スライサーの挿入]をクリックします。 Insert Slicerを選択してInsert Slicersフォームを開きます。利用可能なフィールドの数を確認してください。この例では、Years、CountryRegionNameおよびProductCategoryを追加しました。スライサーを必要な場所に配置する必要があります。デフォルトでは、フィルタが適用されていないことを示すすべての値が選択されています。

14の15

ユーザーフレンドリースライサーによるピボットテーブル

スライサーを使用すると、ユーザーはピボットテーブルを簡単にフィルタできます。

ご覧のように、スライサーはすべてのデータを選択したものとして表示します。ピボットテーブルの現在のビューにどのようなデータが含まれているかは、ユーザーにはっきりとわかります。

15の15

ピボットテーブルを更新するスライサーから値を選択する

スライサーの組み合わせを選択して、データの表示を変更します。

さまざまな値の組み合わせをクリックし、ピボットテーブルの表示がどのように変化するかを確認します。 Slicerで一般的なMicrosoftをクリックすると、Control + Clickを使用して複数の値を選択したり、Shift +クリックして値の範囲を選択したりすることができます。各スライサーには選択された値が表示され、ピボットテーブルの状態がフィルターの意味でどのようなものかが明確になります。 [オプション]タブの[スライサー]セクションで[クイックスタイル]ドロップダウンをクリックして、スライサーのスタイルを変更できます。

Slicersの導入により、ピボットテーブルの使い勝手が大幅に向上し、Excel 2010はプロフェッショナルなビジネスインテリジェンスツールに近づいています。 Excel 2010でピボットテーブルが大幅に改善されました。新しいPowerPivotと組み合わせると、非常に高性能な分析環境が作成されます。