【完全ガイド】ピボットテーブルの計算フィールドと複数データソースの活用術

ピボットテーブルはデータ分析に欠かせない強力なツールですが、「計算フィールド」や「複数データソース」といった高度なテクニックを組み合わせることで、さらに深いインサイトを引き出すことができます。本記事では、計算フィールドの作成方法と、複数のデータソースをピボットテーブルで活用する方法を中心に、実践的なテクニックをわかりやすく解説します。

ピボットテーブルで計算フィールドを作成する

計算フィールドとは、既存のデータをもとにピボットテーブル内で独自の計算を行うための機能です。新しい列として追加され、その値は既存のフィールドから自動的に算出されます。元のデータセットを変更することなく計算結果を得たい場合に非常に便利です。

ここでは、売上データセットを例に、計算フィールドを使って売上の10%にあたる手数料を計算する方法を紹介します。計算フィールドを使用するには、まずピボットテーブルを作成しておく必要があります。

ステップ1：ピボットテーブルを作成する

• データ範囲を選択します。
• 「挿入」タブ＞＞「ピボットテーブル」を選択します。
• ピボットテーブルの配置先（新しいワークシート／既存のワークシート）を選択します。

続いて、必要なフィールドを「行」「列」「値」「フィルター」エリアへドラッグ＆ドロップします。

ステップ2：計算フィールドを挿入する

• ピボットテーブル内の任意のセルをクリックします。
• 「ピボットテーブル分析」タブ＞＞「フィールド、アイテム＆セット」＞＞「計算フィールド」を選択します。

• 「計算フィールドの挿入」ダイアログボックスで以下を設定します。
  • 「名前」ボックスに計算フィールドの名前を入力します：売上手数料（10%）
  • 「数式」ボックスに次の数式を入力します：
    • = 0.1 * 'Total Sales'
  • 「追加」をクリックし、「OK」をクリックしてピボットテーブルにフィールドを追加します。

これで、新しい計算フィールド「売上手数料（10%）」の列がピボットテーブルに追加されました。

ワンポイント：計算フィールドは加算型の計算に最適です。非加算型やより複雑な計算が必要な場合は、Power Pivotの活用を検討しましょう。

複数のデータソースからピボットテーブルを作成する

複数のテーブル（またはソース）のデータを1つのピボットテーブルにまとめることで、手動でテーブルを結合することなく、より包括的なデータセットを分析できるようになります。

ここでは、売上情報を含む「Sales（売上）」データと、製品情報を含む「Products（製品）」データという2つのデータソースがあると仮定し、これらをピボットテーブルで活用する方法を順を追って説明します。

方法1：Excelの組み込み「データモデル」を使う

データモデル機能を使えば、複数のデータソースを1つのピボットテーブルで扱えます。この機能はExcel 2013以降のバージョンに標準搭載されています。

ステップ1：各テーブルをExcelで準備する

• セル範囲を選択＞＞「挿入」タブ＞＞「テーブル」を選択します。

• 後ほど作業しやすいように、各テーブルに名前を付けておきましょう（例：売上テーブルはSales_Data、製品テーブルはProducts_Table）。

ステップ2：データモデルを使ってピボットテーブルを挿入する

• テーブルを選択＞＞「挿入」タブ＞＞「ピボットテーブル」を選択します。
• 「ピボットテーブルの作成」ダイアログボックスで以下を設定します。
  • ピボットテーブルの配置先（新しいワークシート／既存のワークシート）を選択します。
  • 「このデータをデータモデルに追加」にチェックを入れます。
  • 「OK」をクリックします。

ステップ3：リレーションシップを作成する

• 「データ」タブ＞＞「データツール」グループ＞＞「リレーションシップ」を選択します。

• 「リレーションシップの管理」ダイアログボックスで以下を行います。
  • 「新規」をクリックし、「Sales（売上）」テーブルと「Products（製品）」テーブルの双方からProduct_IDを選択してリレーションシップを確立します。
  • 「OK」をクリックしてリレーションシップを保存します。

ステップ4：ピボットテーブルを構築する

• 「ピボットテーブルのフィールド」ウィンドウに、両方のテーブルのフィールドが表示されます。
• Products_TableからCategory（カテゴリー）とProduct（製品）を「行」へドラッグします。
• Sales_DataテーブルからUnit Sold（販売数量）を「列」へドラッグします。
• Sales_DataテーブルからTotal Sales（合計売上）を「値」へドラッグすると、製品ごとの数量と売上の合計を確認できます。

リレーションシップを設定すれば、ピボットテーブルは両方のテーブルからフィールドを取得できるようになります。手動での結合作業なしに、売上サマリー・製品名・カテゴリーをひとつのレポートにまとめられるのが大きなメリットです。

方法2：Power Pivotで複数のデータソースを使う

ExcelのPower Pivotアドインは、Excel 2016、2019、Office 365 Professional ＆ Enterpriseなどのバージョンで利用でき、異なるソースのテーブル間にリレーションシップを作成できます。この手法を使えば、複数のテーブルに基づくピボットテーブルを作成し、それらを横断した高度な分析が可能になります。

ステップ1：Power Pivotを有効化する（未有効の場合）

• 「ファイル」タブ＞＞「オプション」＞＞「アドイン」を選択します。
• ウィンドウ下部の「管理」ドロップダウンで「COM アドイン」を選択＞＞「設定」をクリックします。
• 「Microsoft Power Pivot for Excel」にチェックを入れ＞＞「OK」をクリックします。

ステップ2：Power Pivotにデータを読み込む

• 「データ」タブ＞＞「データの取得」を選択し、さまざまなソース（複数のシート、外部データベース、CSVファイルなど）からデータをインポートします。
• データソース（Excel ブック、SQL Server、テキスト/CSVなど）を選択し、「読み込み先」をクリックします。
• 「データのインポート」ダイアログボックスで以下を設定します。
  • 「テーブル」を選択します。
  • 「新規ワークシート」を選択します。
  • 「このデータをデータモデルに追加」にチェックを入れます。

注意：読み込む各テーブルには、テーブル間のリレーションシップを確立できるよう、一意の識別子（製品IDや顧客IDなど）を必ず用意してください。

ステップ3：テーブル間のリレーションシップを作成する

• 「Power Pivot」タブ＞＞「管理」を選択して「Power Pivot ウィンドウ」を開きます。

• 「ダイアグラム ビュー」に切り替えると、すべてのテーブルを視覚的に確認できます。
• テーブル間でフィールドをドラッグ＆ドロップしてリレーションシップを作成します。たとえば、Sales Table（売上テーブル）のProduct IDを、Product Table（製品テーブル）のProduct IDに接続します。
• Power Pivotの「ホーム」タブ＞＞「ピボットテーブル」を選択します。
• ピボットテーブルの配置先（例：新しいワークシート）を選択し、「OK」をクリックします。

• ピボットテーブルのフィールドリストには、すべてのテーブルのフィールドが表示されます。これらのフィールドを使って、複数のソースからデータを取得するレポートを作成しましょう。
  • Products_TableからProduct（製品）を「行」へドラッグします。
  • Sales_DataからUnit Sold（販売数量）を「列」へドラッグします。
  • Sales_DataからTotal Sales（合計売上）を「値」へドラッグします。

まとめ

今回紹介した高度なテクニックを活用すれば、分析のカスタマイズと複数データソースの統合が容易になり、ピボットテーブルからより深い洞察を得られるようになります。計算フィールドの習得と、ExcelのデータモデルやPower Pivotを活用した複数データソースの統合によって、複雑なデータセットでも効率的に分析できるようになるでしょう。これらのテクニックを身につければ、意思決定に必要なインサイトをより速く、より正確に引き出せるようになります。

 
画像提供：Pixabay / councilcle

無料の高度なExcel演習問題と解答はこちら！