1. このページで体験できること
2. 環境セットアップ
3. 基本操作
4. 「A. 商品区分（部門）別売上ランキング表」を作ってみる
5. 「B. 時間帯別来店者数一覧（グラフ）」を作ってみる
6. 「C. 仕入先別利益率管理表」を作ってみる
7. 「D. 店舗（地域）別売上バラツキ分析表」を作ってみる
8. SharePoint Server 2010 へアップロードして共有する
9. 参考情報

PowerPivot for Excel は下記サイトより、無償でダウンロードすることができます。

Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010 - RTM

なお、ダウンロードは、まず、HTMLファイルをダウンロードし、そこに記載されているURLから行います。32bit、64bitの2つのバージョンが存在しますので、お使いの環境に適したバージョンをダウンロードください。

ダウンロードしたファイルを実行し、インストールを行います。インストール後、Excel を起動すると、PowerPivot for Excel をインストールするか確認画面が表示されますので、「インストール」をクリックし、インストールを完了させます。

インストールが正常に完了すると、Excel のリボンコントロールに「PowerPivot」タブが追加されます。これで、PowerPivot for Excel を利用する準備が整いました。

まず、Excel を起動します。次に、リボンメニューから、「PowerPivot」タブを選択します。左端に表示される「PowerPivot ウインドウ」をクリックします。すると、PowerPivot ウインドウが表示されます。

PowerPivot for Excel の利用に際しては、まず、このPowerPivot ウインドウで、データの読み込み、調整を行い、それらをExcel シート上で分析（ピボットテーブル、グラフなどを利用）するといった流れになります。

では、まず、分析の対象となるデータを、SQL Server から読み込んでみましょう。データの取り込み処理は、「テーブルのインポートウィザード」を利用して簡単に行うことができます。

PowerPivot ウインドウの「ホーム」タグを選択し、さらに、「データベース」プルダウンから、「SQL Server 」を選択します。

「Microsoft SQL Server データベースへの接続」画面で、読み取り先のデータベースを指定します。「サーバー名」および「データベース名」を指定します。ここでは、サーバー名としてlob.msbi.jp 、データベース名としてNorthwindJforXp を指定します。「次へ」をクリックします。ここでは、サーバ名の入力においては、サーバ名に加えポート番号も指定しています。

データをインポートする方法を選びます。取得の方法としては、GUIを利用して取得するデータを選択するか、SQL文でクエリを記述するかの2つが選べます。ここでは、GUIを利用するため、上段の、「インポートするデータをテーブルとビューの一覧から選択する」を選択します。「次へ」をクリックします。

取得対象として指定したデータベースのテーブルとビューの一覧が表示されます（ビューは生成されていないと表示されません）。ここでは、まず、分析の主対象となる「POSデータ」テーブルを選択します。続いて、リストの下部にある「関連テーブルの選択」ボタンをクリックします。すると、「商品」、「天気」、「店舗」の3つのテーブルが自動的に選択されます。さらにもう一度、「関連テーブルの選択」をクリックします。すると、さらに「仕入先」、「受注明細」、「商品区分」3つのテーブルが追加されます。

この「関連テーブルの選択」は、「POSデータ」テーブルに蓄積されたデータに関連付けられたデータを、他のテーブルから取得するために行います。例えば。「POSデータ」テーブルには、「商品コード」は含まれていますが、「商品名」や「単価」が保持されていません。その為、「商品名」や「単価」を保持する「商品」テーブルを「関連テーブルの選択」により、取得したわけです。さらに、2回押したのは、「商品」テーブルには、「区分コード」は含まれていますが、「区分名」は含まれていません。その為、それを保有する「商品区分」テーブルを「関連テーブルの選択」を再度クリックすることで取得しました。他のテーブルに関しても同様です。

「完了」をクリックします。

完了をクリックすると、データの取り込み処理が実行されます。「成功」と表示されるので、「閉じる」をクリックします。これで、データの取り込みは終了です。

では、取り込まれたデータを確認してみましょう。取り込んだ各テーブルは、PowerPivot ウインドウ下部にあるタブとして列挙され、それらを選択することで、各テーブルにアクセスすることができます。ここでは7つのテーブル（タブ）が確認できます。では、「POSデータ」テーブルを見てみましょう。先ほど、説明したとおり、「POSデータ」テーブルには、「商品コード」は含まれていますが、「商品名」や商品の「単価」などは存在していません。

次に、「商品」テーブルを見てみましょう。こちらには「商品コード」はもちろん、「商品名」、「単価」などの項目が存在していることが確認できます。

お気づきかもしれませんが、この2つのテーブルは「商品コード」を通じて関連づけられています（リレーショナルが組まれています）。また、もし、あらかじめ関連付けが行われていない場合にも、PowerPivot for Excel 上で関連付けを行うことが可能です（関連付けの方法については後述 (4.6.2 で紹介) します）。

では、分析の主対象となる「POSデータ」テーブルを改めて見てみましょう。データ分析を行うための最低限度の情報は含まれているようですが、数字を中心にした情報が多く、直感的に意味を理解しづらい状態です。また、分析上重要となる各商品の小計なども含まれていません。ここでは、分析に先立ち、あらかじめ取扱いやすいようにデータの調整を行っておきましょう。

「POSデータ」テーブルを見やすくするために、関連付けられたテーブルから、データを取得してみましょう。まず、「商品」テーブルから「商品名」を取得し表示してみましょう。

右端のタイトル欄に 「列の追加」と表示された（最も上の）セルを選択し、「=RELATED('商品'[商品名])」（「」は入力しません）と入力します。入力が完了したら、エンターを押します。すると、「商品コード」に紐づけられた「商品名」が取得され表示されます。列名が「CalculatedColumn1」と自動付与されますが、わかりにくいので「商品名」と変更します。

RELATEDはDAX (Data Analysis Expression) 式と呼ばれるもので、Excel の式のように利用することができ、データに対する様々な処理を記述することができます。RELATED は、関連づけられたテーブル、列から、情報を取得する式です。

次に、「単価」も取得します。「商品名」と同様に「列の追加」の1つ下のセルを選択し、「=RELATED('商品'[単価])」と入力します。すると「単価」が表示されます。列名を「単価」と変更しておきましょう。

最後に、「POSデータ」テーブルに存在していた販売「数量」と、「商品」テーブルから取得し追加した「単価」を掛け合わせて小計を表示してみましょう。

「列の追加」の1つ下のセルを選択し、「=[単価]*[数量]」と入力します。計算が行われ小計が表示されます。列名を「小計」と変更しておきましょう。

データの調整は以上です。

PowerPivot ウインドウの「ホーム」タブを選択し、表示されるボタンから「ピボットテーブル」をクリックすると「ピボットテーブルの作成」ダイアログが表示されるので、「新しいワークシート」を選択します。

ピボットテーブルを含む、新しいワークシートが生成されました。ピボットテーブルを選択すると右に表示されるPowerPivot フィールドの一覧が表示されます。これらのフィールドを選択し、適切な要素（行ラベルや列ラベルなど）にドラッグ＆ドロップすることで、直感的かつ簡単にデータ分析を行うことができます。

では、まず、ピボットテーブルの縦の列を設定しましょう。ここでは商品の売上ランキング一覧を作りたいので、その主要素となる商品名を追加します。

「行ラベル」に「商品」テーブルの「商品名」フィールドをドラッグ＆ドロップします。

商品名が縦に列挙されました。

ランキングを計算するために必要なデータは、各商品の売上合計ですので、ここでは、先ほど基本操作で「POSデータ」テーブルに追加した「小計」列を利用します。「POSデータ」テーブルから「小計」フィールドを「Σ 値」へドラッグ＆ドロップします。商品毎の売上合計が表示されました。小計の合計列の任意のセルを選択し、「降順」で並べ替えを行ってみてください。本題からはそれますが、ここまでの操作で、全商品、全店における売れ筋ランキングは完成しています。後は、商品区分での整理、店舗の制限（東京店のみ表示）を行うだけです。

全行品が対象になったランキングを区分名により整理します。その為には「行ラベル」に「商品区分」テーブルから「区分名」を追加します。なお、追加の順番は、「商品名」より先（上）でなくてはなりません。そこで、区分名をドラッグし、商品名の上部でドロップします。これで、商品区分名別のランキングは表示されました。

なお、区分名を追加すると小計の合計が各区分毎に表示されますが、必要ないので、ここでは非表示にします。「ピボットテーブルツール」のデザインを開き、「小計」をプルダウンさせ、「小計を表示しない」に設定します。

「店舗」テーブルの「店舗名」を「垂直スライサー」にドラッグ＆ドロップします。するとピボットテーブルの左側に店舗名から成るボタンが表示されます。このボタンを押すことでボタンに表示された条件にデータを制限することができます。ここでは「東京店」ボタンをクリックします。レポートフィルターを用いても同じ効果が得られますが、スライサーの方が直感的かつ操作が簡単です。

なお、スライサーをOFFにしたい場合は、スライサー画面の右上にある、「ロードに×印」アイコンをクリックします。

ここまでは、SQL Server から取得したデータのみを元に、分析を行ってきましたが、PowerPivot for Excel を利用すれば簡単に分析データを追加し、独自の視点での分析を行うことができます。ここでは、Webサイト上にある「商品ランキングデータ」をデータソースとして取得し、自社のランキングと比較を行う例を試してみたいと思います。

Webに限らず、データベース以外からデータを取り込み、分析を行う手順は、データの取得、（既存データとの）関連付け設定、分析の3つのステップとなります。

データの取り込みと言っても難しいことは何一つありません。基本的にコピーペーストを行うだけです。Web上で、必要なデータをコピーします。

なお、サンプルのHTMLファイルはこちらで取得できます。

その後、PowerPivot ウインドウの「貼り付け」を選択すると、コピーした情報が貼り付けプレビューとして表示されます。

テーブル名を「全国ドリンクランキング」に設定しOKをクリックします。新しいタブが追加されました。

新規のデータを活用するためには、既存のデータとの関連づけが必要になります。ここでは、商品の名前をキーとして関連付を行います。「全国ドリンクランキング」の「商品」列を選択した状態で、PowerPivot ウインドウの「デザイン」タグを選択し、さらに、「リレーションシップの作成」を選択します。

「リレーションシップの作成」ダイアログが表示されるので、「関連する参照テーブル」を「商品」に、「関連する参照列」を「商品名」にセットし、作成をクリックします。これで、関連付けが行われました。関連づけるデータの名前が違っても問題はありません。ただし、データ内容は同じルールに従っている必要があります。

先ほどのピボットテーブルを開きます。PowerPivot ウインドウ側でデータが変更されたため、PowerPivot フィールド一覧に「PowerPivot データが更新されました（最新の情報に更新）」ボタンが表示されていますのでクリックしデータを更新します。

これで、先ほど追加した「全国ドリンクランキング」テーブルと関連付けデータが読み込まれます。

PowerPivot フィールド一覧に追加された「全国ドリンクランキング」から、「順位」を「Σ 値」にドラッグ＆ドロップします。すると、全国におけるドリンクランキングが、自社店舗における飲料区分売上ランキングにマップされ、比較が可能になりました。ここでは、飲料だけを対象にしましたが、データさえあれば、どのような分析も可能です。

これで完成です。

ここでは、「POSデータ」テーブルに「精算時間帯」という列を追加します。精算時間帯は、「POSデータ」テーブルの「精算日時」からDAX式により時間を抽出することで取得します。

ここでは来店者数を取得するための新しい項目（メジャー）を追加します。メジャーの追加は、「PowerPivot タブ」で表示されるメニューの中から選択（PowerPivot ウインドウアイコンの右隣り）するか、「PowerPivot フィールド一覧」において「POSデータ」を右クリックし、表示されるプルダウンの一番下の「新しいメジャーの追加．．．」を選択することで行います。

表示される「メジャーの設定」ダイアログに下記の表を参考に値を入力します。

レシートナンバーは重複しているため、DISTINCT により重複を排除し、その上でCOUNTROWS により、数を数えています。レシート数は客数と一致していると想定しています。そして、その数を時間帯別に分離し、時間帯別の来店者数を算出し、時間帯による混雑度合の把握しようとしています。

「精算時間帯」を追加

「来店者数」を追加

垂直スライサーに「店舗名」を追加。東京店を選択。

ピボットテーブルツールのオプションで、「ピボットグラフ」を選択すると「グラフ挿入」ダイアログが開くので、テンプレートから縦棒を選択し、デフォルト値（左上）のテンプレートを選択し、OKをクリック。

時間帯別の来店者数が棒グラフで表示されます。これで完成です。

完成イメージとパラメータ。パラメーター配置等の参考にしてください。

この分析のためには、利益率データが必要なため、「商品」テーブルの「仕入原価」と「単価」を利用して「商品」テーブルに「利益率」列を1つ追加します。

「仕入先名」を追加

「利益率」を追加し、集計の方法を平均に変更

集計方法の変更は、「Σ 値」に追加したフィールド名を右クリックし表示される「集計の方法」プルダウンにより行います。

利益率の平均の書式を%、小数点1桁に設定。

「利益率の平均」列の任意のセルを選択し、並び替え「昇順」を選択します。こうすることで、利益率が悪い仕入先順に票がソートされます。

これで完成です。

完成イメージとパラメータ。パラメーター配置等の参考にしてください。

既にあるデータだけで分析が可能なため、特にデータの調整は必要ありません。

「商品名」を追加

「店舗名」を追加

「数量」を追加

Excel 関数であるSTDEV() を利用します。商品、店舗毎の数量合計の標準偏差を取得します。（値が大きいほど、店舗毎の商品の売れ行きにバラツキが大きい目安となる）

標準偏差列に、データバーを適用し、バラツキの多い商品を直感的にわかるようにします。

完成イメージとパラメータ。パラメーター配置等の参考にしてください。

アップロードはSharePoint Server 上のサイトの、PowerPivot ギャラリー、ドキュメントタブに存在する「ドキュメントのアップロード」をクリックして行います。

アップロードされたファイルは、Web ブラウザ上で閲覧することが可能です。