Excel Power Pivotは、使いやすく、習得も容易なデータ分析機能です。そして何よりも重要なのは、膨大なデータやカスタムニーズに対応できる柔軟性と汎用性です。少しの知識と計画があれば、熟練したデータベース開発者に頼ることなく、データを有益な情報に変換できます。データベース技術に取って代わるものではありませんが、そうしたリソースを持たないユーザーにとって強力なツールとなります。さらに、処理速度も速く、すぐに結果が得られます。
Excel 2010をお使いの場合は、Power Pivotをダウンロードしてインストールする必要があります。Excel 2013または2016をお使いの場合は、Power Pivotはパッケージに含まれています。私はWindows 10 64ビットシステムでExcel 2016（デスクトップ版）を使用しています。以前のバージョンでも手順は同様です。ご参考までに、デモ用の.xlsxファイルをダウンロードできます。
まずは、Excelのデータモデルを使って関連データを意味のある情報に変換する方法をご覧ください。この記事では、2つのデータセット間に1つのリレーションシップを作成する手順を解説しながら、この機能の基本的な概要を説明します。さらに、複数のリレーションシップを作成し、計算列を追加する手順もご紹介します。
参照: Microsoft Power BI: データ視覚化入門 (無料 PDF) (TechRepublic)
データ
サンプルブックには、顧客、注文、注文明細（AccessデータベースのProductInventory.accdbからコピー）という3つの関連データシートがあります。図Aに示すように、これらのデータセットは共通フィールドによって関連付けられています。
- Orders.Customer と Customers.Company
- Orders.ID と Order Details.Order ID
図A
2 つのリレーションシップを使用して、3 つのテーブルのデータを結合します。
「注文」シートの各レコードは、それぞれ1つの注文を表します。このシートの各注文は、「顧客」シートの顧客と関連付けられており、注文の詳細が「注文詳細」シートに記載されています。どのシートも、売上の全体像を把握できるものではありません。売上を確定するには、3つのシートすべてのデータが必要です。
この時点で明らかなのは、データを意味のある情報に変換することは不可能だということです。少なくとも、簡単には不可能です。たとえば、「注文」シートを見ると、注文 1 の小計 (送料と税金の前) は $477.20 です。この合計を構成する製品の数や、各製品の単価や購入数量はわかりません。「注文の詳細」シートを見ると、各売上を構成するすべての品目を確認できますが、誰が購入したかはわかりません。「顧客」シートには各顧客に関する情報が保存されていますが、購入履歴はありません。ここで Power Pivot が役立ちます。3 つのデータ セット間にリレーションシップを構築することで、意味のある方法でデータをすばやく分析できます。
Excelのデータモデルを使って関連データを意味のある情報に変換する方法でも触れましたが、複雑な数式を使ってデータを検索し、1枚のシートに完全なレコードを作成するという方法もあります。これは手間がかかるだけでなく、数式を使うとメモリを大量に消費し、データ量が多い場合は処理速度が低下します。しかし、PowerPivotを使えば、そのような手間はかかりません。
テーブルオブジェクトに変換する
数式を使ってシートを関連付ける代わりに、PowerPivotを使ってテーブル間のリレーションシップを作成します。こうすることで、数式を一切使わずに済みます。ただし、PowerPivotはTableオブジェクトしか認識しません。そのため、まずは各データセットをTableに変換し、以下のように名前を付けます。
- データ セット内の任意の場所をクリックします。
- [挿入]タブをクリックし、[表]グループの[表]をクリックします。
- 表示されるダイアログで、「テーブルにヘッダーを含める」オプションを必要に応じてオンまたはオフにします。サンプルデータセットにはすべてヘッダーが含まれています。
- [OK]をクリックします。
- コンテキスト デザイン タブをクリックします。
- リボンの左側にある「テーブル名」コントロールをクリックし、テーブルにわかりやすい名前を入力してEnterキーを押します。テーブルに名前を付けることは必須ではありませんが、後で役立ちます。
3つのテーブルオブジェクト（Customers、OrderDetails、Orders）を作成します。完了したら、次のステップに進みます。
参照: Excel のデータ検証の力を活用する (無料 PDF) (TechRepublic)
関係を築く
データ セットをテーブル オブジェクトに変換したら、リレーションシップを作成できます。リレーションシップを作成するには、テーブル内の任意の場所をクリックし、[PowerPivot] タブをクリックして PowerPivot ウィンドウを開きます。[テーブル] グループの [データ モデルに追加] をクリックします。これにより、データの別のビューが生成されます。Excel データは安全です。PowerPivot ビューでデータを編集することはできません。3 つのテーブル オブジェクトすべてを PowerPivot に追加します。図 B は、PowerPivot の [顧客] ビューを示しています。各ビューにはタブがあります (PowerPivot では、データ セットをビューと呼びます)。
図B
3 つの Table オブジェクトすべてを Power Pivot に追加します。
では、最初のリレーションシップを作成しましょう。具体的には、CustomersとOrdersの関係を作成します。
- PowerPivot 内で、「デザイン」タブをクリックします（必要な場合）。次に、「顧客（ビュー）」タブ（下部）をクリックします。
- 「リレーションシップ」グループの「リレーションシップの作成」をクリックします。PowerPivot にビューのフィールドとレコードのサンプルが表示されます。
- 空のテーブルのドロップダウン (下記) から [Orders] (図 C ) を選択すると、ピボットテーブルにそのフィールドとレコードが表示されます。
- 次に、2つのビューで共有するフィールドを指定する必要があります。今回の場合は、CustomersテーブルのCompanyフィールドとOrdersテーブルのCustomerフィールドです。両方のフィールドを選択してください（図D）。
- [OK]をクリックします。
図C
テーブル オブジェクトを選択します。
図D
2 つのビューを共通フィールドで関連付けます。
上記の手順と図 E をガイドとして使用し、ID フィールドと Order ID フィールドに基づいて Orders と OrderDetails の関係を作成します。
図E
Orders と OrderDetails の間に関係を構築します。
この時点で、3つのTableオブジェクトに基づく3つのビューが作成されています。図Fに示すように、3つのビューの間には2つの関係があります。これを確認するには、「ホーム」タブをクリックし、「表示」グループの「ダイアグラムビュー」オプションをクリックします。
図F
ビュー間の関係を表示します。
これで、売上データを迅速に分析するための強固な基盤が整いました。数式を使わずに、わずかな労力で有意義な情報を作成できます。
基礎の上に築く
それでは、Power Pivot ビュー間のリレーションシップを活用してみましょう。具体的には、顧客ごとに各商品のコストを返すピボットテーブルを作成し、その結果を顧客の州と販売を行った社内担当者でフィルター処理してみましょう。
先に進む前に、少し立ち止まって、すべてのデータがどこから来ているのかを確認しましょう。
- 顧客ビューから会社フィールドと州/県フィールドを追加します。
- OrderDetails ビューから製品と数量を追加します。
- 「注文」ビューから従業員フィールドを追加します。
図 G は、フィールド リストと結果のピボットテーブルを示しています。フィルターは行 1 と 2 にあります。州と従業員でフィルターできますが、現時点では各項目の合計コストはありません。
図G
結果のピボットテーブルでは、必要な完全な結果が得られません。
解決策は計算フィールドですが、次のようにビューに追加する必要があります。
- [Power Pivot] タブをクリックし、[管理] ([データ モデル] グループ内) をクリックします。
- OrderDetails ビュー タブを選択します。
- 列の追加で最初のセルを選択します。
- 数式を作成するには、「=」を入力し、「数量」フィールドをクリックして「*」を入力し、「単価」フィールド (図 H ) をクリックして、Enter キーを押します。
- 列の名前を Total に変更します (これは厳密には必須ではありませんが、意味のある名前を付けると作業がしやすくなります)。
図H
購入した各商品の合計金額を返す数式を入力します。
計算フィールドを配置したら、ピボットテーブルに戻り、「値」に新しい合計フィールドを追加します。図Iは、新しい列に通貨形式を追加した結果を示しています。
図I
ピボットテーブルには、会社別の個別商品の合計販売価格が表示されるようになりました。
顧客の州と、販売を担当した社内従業員で結果をフィルタリングできます。例えば、図Jはワシントン州の顧客（A社のみ）の内訳を示しています。2つ目のフィルタリングされたセットは、Laura Giussaniが担当したワシントン州での購入のみを示しています。代わりに、スライサーを追加してピボットテーブルをフィルタリングすることもできます。
図J
フィルターを使用して特定のカテゴリに焦点を絞ることができます。
これはこれらの結果を達成する唯一の方法ではありませんが、リレーションシップが確立されたことで、3つのビューすべてから情報を素早く抽出して意味のある情報に変換し、計算フィールドを追加して価値を高めることができます。Power Pivotは、共通フィールドに基づいて関連するデータセット間のリレーションシップを作成するクエリエンジンと考えることができます。これは少し単純化しすぎていますが、適切な説明です。
Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットをお送りいただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングをして、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果にならない理由を教えてください」といった質問であれば、返答があるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
以下も参照:
- Word 2016 文書で見出しに番号を付ける方法 (TechRepublic)
- Office Q&A: Excel の貼り付けタスクで貼り付け先のセルの書式が上書きされないようにする方法 (TechRepublic)
- Office ドキュメントにチェックマークを挿入する 5 つの方法 (TechRepublic)
- Microsoft Word の番号付きリストで間隔と配置を制御する方法 (TechRepublic)
- Excel のデータ モデルを使用して関連データを意味のある情報に変換する方法 (TechRepublic)
