Excel Power Pivot：ステップバイステップガイド

大きな Excel ファイルを分析すると、動作が重くなりがちです。

Power Pivot は異なるアプローチを提供します。テーブル同士を接続し、パフォーマンスを損なうことなく計算を処理します。VLOOKUP() の連鎖や補助列と格闘する代わりに、Excel に組み込まれた構造化システムで作業できます。

このガイドでは、データモデルの設定、テーブルのリレーションシップ作成、DAX での計算式作成、そして Power Pivot を使ったインタラクティブなレポートの作り方を学びます。

Power Pivot とは？なぜ便利なのか

Power Pivot は Excel に標準搭載されているデータモデリングエンジンです。より大きなデータセットを取り込み、複数のテーブルを接続し、従来のワークシートで起こりがちな動作の重さを回避しながら複雑な計算を実行できます。

Power Pivot が違う点

シートに直接データを保存するのではなく、Power Pivot はすべてを Excel の内部データモデルに読み込みます。

標準のワークシートはおよそ 100 万行で上限に達し、通常はその前に動作が遅くなります。Power Pivot はデータを圧縮し、別管理にすることでこの制限を回避し、数千万行のデータでもブックのパフォーマンスを維持したまま扱えます。

VLOOKUP の連鎖ではなくリレーショナル構造

データをモデルに取り込んだら、軽量なデータベースのようにキーを使ってテーブル同士を関連付けられます。すべてを 1 枚の巨大なシートに平坦化し、入れ子の VLOOKUP() 関数で無理やり結合する必要はありません。Power Pivot なら、リンクしたテーブルを並べて、クリーンかつ確実に分析できます。

DAX による強力な計算

Power Pivot は DAX（Data Analysis Expressions） という、分析作業に特化して設計された数式言語を使います。単純な合計から、時系列指標、比率、ローリングウィンドウなどの高度な計算まで、標準のピボットテーブルを超えるメジャーを作成できます。

活用シナリオの例

企業が業務で Power Pivot を活用する例を 2 つ紹介します。

• 売上パフォーマンスのトラッキング：受注履歴、商品テーブル、顧客属性を組み合わせ、手作業の結合なしで前年比売上や顧客生涯価値の DAX メジャーを作成。
• オペレーションレポート：在庫、出荷、サプライヤーデータをリンクし、同一モデルで充足率、リードタイム、予測との差異を算出。

要するに、Power Pivot は Excel 内でデータベースのような体験を提供します。大規模または複数テーブルのデータを扱う場合、混乱しがちなレポート作業を、高速で拡張性のあるモデルに変えられます。

Excel で Power Pivot を設定する

ここから、Excel で Power Pivot を使い始める手順を見ていきます。

Power Pivot を有効化する

Power Pivot をダウンロードする必要はありません。Excel にすでに含まれています。有効化の手順は次のとおりです。

1. Excel シートを開く
2. リボンのファイルをクリック
3. 続いてオプション > アドインを選択
4. ドロップダウンからCOM アドインを選び、実行をクリック
5. ポップアップが表示されるので、Microsoft Power Pivot for Excelにチェックを入れ、OK をクリック

これでリボンに Power Pivot が表示されます。

Excel で Power Pivot アドインを有効にする。画像提供：著者

注意：Power Pivot が動作するのは Excel Professional Plus または Microsoft 365 のみです。有効化してもタブが表示されない場合、お使いの Excel バージョンに含まれていない可能性があります。

複数ソースからデータをインポートする

Excel ファイル、CSV、SQL Server データベースなど、さまざまなソースからデータを取り込めます。

ここでは、.xlsb ファイル内に次の 2 つのデータセットがある例を使用します。

1. sales.xlsb

2. customer.xlsb

これらを Power Pivot に取り込むには：

1. リボンのPower Pivot タブで 管理を選択。新しいウィンドウが開きます
2. 次に ホーム > 外部データの取り込み > その他のソースから をクリック
3. 下へスクロールして Excel ファイル をクリック

他のソースからデータを取得。画像提供：著者

4. ポップアップで 参照 をクリックし、customer.xlsb を選択

5. 「先頭行を列見出しとして使用する」にチェックを入れ、次へ をクリック

Excel ファイルを Power Pivot に取り込む。画像提供：著者

次の画面で プレビューとフィルター をクリックして、インポート前のデータの見え方を確認します。問題なければ OK をクリック。すべての行が正常に転送された旨が表示されたら、閉じる をクリックします。

選択したデータをプレビュー。画像提供：著者

同じ手順で sales.xlsb も取り込みます。画面下部に両方のファイルがインポート済みとして表示されます。ダブルクリックして名前を変更します。

両方のファイルをインポート済み。画像提供：著者

リレーションシップとデータモデルの構築

データを Power Pivot に読み込んだら、テーブル同士のつながりを Excel に理解させるためにリンクします。この工程がレポート全体の土台になります。

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

ここでは、Sales と Customers テーブルのリレーションシップを作ります。

1. 「ホーム」タブで ダイアグラム ビュー をクリック。インポートした 2 つのテーブルが表示されます
2. 「Sales」テーブルの CustomerID をクリック
3. それを「Customer」テーブルの CustomerID にドラッグしてリレーションシップを作成

注意：リレーションシップを編集したい場合は、線を右クリックして リレーションシップの編集.. をクリック。ウィンドウで、関連付けに使う列を選択します。

テーブル間のリレーションシップを作成。画像提供：著者

この関係では、1 人の顧客は Sales テーブルに複数回登場しますが、Customers テーブルでは各顧客は 1 回だけ登場します。これは 1 対多のシンプルな関係で、ピボットテーブルで両方のテーブルのフィールドを使い、参照関数なしで計算を行えます。

スター・スキーマで設計する

スター・スキーマは Power Pivot モデルを構造化する最も簡単な方法のひとつです。テーブルが整理され、計算も予測どおりに動作します。

まずファクトテーブルを選びます。このケースでは、Sales が日付、顧客、商品、数量、金額といった取引レコードを保持するため、ファクトテーブルとなります。

次に、ディメンションテーブルを特定します。これは Sales のデータを説明するテーブルです。一般的な例として：

• Customers（主キー：CustomerID）
• Products（主キー：ProductID）
• Regions（主キー：RegionID）

各ディメンションテーブルは主キーを持ちます。ファクトテーブル内の対応する外部キーと接続します。

• Customers.CustomerID → Sales.CustomerID
• Products.ProductID → Sales.ProductID
• Regions.RegionID → Customers.RegionID

リンクすると、中央に Sales があり、その周囲にディメンションテーブルが放射状に並ぶ形になります。これがスター型です。モデルが明快になり、計算が高速化し、レポートの一貫性も高まります。

スター・スキーマを作成。画像提供：著者

計算列を追加する

リレーションシップを設定したら、データモデル内で新しいフィールドを作成できます。

1. 「データ ビュー」に切り替え

2. テーブル末尾の空の 列の追加 フィールドを選択

3. = [TotalAmount] / [Qty] と入力して Enter。Excel が列全体に反映します

4. ヘッダー名を PricePerUnit に変更

このように、計算列はテーブル自体の一部になります。モデル内に保存され、データ更新に合わせてリフレッシュされ、後で作成するピボットテーブルや DAX メジャーでも利用できます。

計算列を追加。画像提供：著者

分析のための DAX 公式を作成する

モデルの準備ができたら、DAX でデータ分析用の数式を作っていきます。これらの数式は、合計、比較、時系列の計算をレポート内で実現します。

メジャーを作成する

ピボットテーブル内で自動的に更新される計算が必要な場合は、メジャーを使用します。

メジャーを作成する手順：

1. Power Pivot ウィンドウを開く

2. 「ホーム > 計算 > 新しいメジャー」へ

3. = SUM(Sales[TotalAmount]) のような数式を入力

4. 名前を Total Sales にして OK を選択

メジャーを作成。画像提供：著者

全体に占める割合のメジャーを追加する

次の数式で、全体に占める割合のメジャーも追加できます。

= DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Regions)))

各地域の全体売上に対する比率を表示できます。

合計に対する割合のメジャーを追加。画像提供：著者

タイムインテリジェンスを使う

タイムインテリジェンス関数は、日・月・四半期・年にまたがるデータの動きを理解する DAX の機能群です。年初来合計の算出、前期間との比較、トレンド評価を、フィルターを手作業で調整せずに実現します。

これらの関数をモデルで使うには、まず適切な日付テーブルが必要です。

日付テーブルを設定する

手順：

1. 「Power Pivot > データモデルに追加」へ
2. Power Pivot でテーブルを選択し、デザイン > 日付テーブルとしてマーク を選ぶ

日付テーブルを作成。画像提供：著者

3. 「ホーム > ダイアグラム ビュー」で、Date[Date] → Sales[OrderDate] を関連付ける。

Date Table[Date] を Sales[OrderDate] にリンク。画像提供：著者

タイムインテリジェンスのメジャーを作成する

Date テーブルができたら、期間をまたいだパフォーマンスを評価するメジャーを作成できます。

年初来合計（YTD）：

Total Sales YTD :=
TOTALYTD([Total Sales], 'Date Table'[Date])

前年比比較：

Sales Last Year :=
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date Table'[Date]))

時系列計算を実行。画像提供：著者

メジャーができたら、Excel に戻ってデータモデルを使って ピボットテーブル を作成し、行エリアに日付テーブルのフィールドを配置、値に Total Sales、Total Sales YTD、Sales Last Year を追加します。

これで、モデル内の Date テーブルとタイムインテリジェンスのメジャーの動作が確認できます。

Total Sales、YTD、Last Year を表示するピボットテーブル。画像提供：著者

よく使う DAX パターン

手早くデータを分解し、よくある問いに答えるために、頻出の DAX 公式があります。多くのモデルで有効な 2 つのパターンを紹介します。

カテゴリ別の平均：

Average Sales Per Category :=
AVERAGE(Sales[TotalAmount])

日付に沿った累計（ランニングトータル）：

Running Total Sales :=
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]))
)

メジャーを作る際は、次の習慣を心がけてください。

• 名称を明確にする
• 数式を読みやすく保つ
• 長くなる場合は変数（VAR）を使う

後からモデルを見直すときの理解がぐっと楽になります。

モデルの可視化と操作

モデルとメジャーが作成できたら、データをリアルタイムに探れるビジュアルへと変換しましょう。

ピボットテーブルとピボットグラフを作成する

接続済みテーブルを直接扱えるよう、データモデルからピボットテーブルを挿入する手順です。

1. Excel シートを開く
2. 「挿入 > ピボットテーブル > データモデルから」へ
3. 「新規ワークシート」を選択

「ピボットテーブルのフィールド」ウィンドウで、任意のテーブルからフィールドを選べます。例：

• Regions テーブルの RegionName を 行 へドラッグ
• メジャーの Total Sales を 値 へ

事前にリレーションシップを構築しているため、Excel が自動的に結び付けて集計します。

Power Pivot のデータでピボットテーブルを作成。画像提供：著者

グラフが必要な場合は、ピボットテーブル内をクリックして 挿入 > ピボットグラフ を選び、（集合縦棒 など）グラフ種類を選択して確定します。グラフはピボットテーブルとリンクされ、連動して更新されます。

ピボットグラフを追加。画像提供：著者

スライサーとフィルターを追加する

スライサーは、レポートをインタラクティブにするボタン型のクイックフィルターです。追加方法：

1. ピボットテーブルをクリック
2. 「挿入 > スライサー」へ
3. たとえば RegionName や ProductName を選択

スライサーはシート上のボックスとして表示されます。項目をクリックすると、ピボットテーブルやグラフが即座に更新されます。複数のピボットテーブルがある場合は、1 つのスライサーをすべてに接続して、ページ全体で一貫したフィルタリングを行えます。

スライサーを追加。画像提供：著者

KPI を作成する

KPI は、シートに追加の計算を増やさずに、目標に対する達成状況を可視化するのに役立ちます。作成手順：

1. Power Pivot ウィンドウで KPI > 新しい KPI
2. ベースメジャーに Total Sales を設定
3. 「絶対値」を使い、目標値（例：4000）を入力し、しきい値を調整、アイコンスタイルを選択
4. 最後に OK をクリック

メジャーの KPI を設定。画像提供：著者

5. ピボットテーブルのフィールドで Sales テーブルを展開し、Total Sales をさらに展開
6. そこから Total Sales と Status を 値 にドラッグ

これで、しきい値に対する目標達成状況が確認できます。

Excel のピボットテーブルで KPI ステータスを表示。画像提供：著者

Power Pivot のパフォーマンス最適化

モデルが完成したら、高速で扱いやすい状態を維持したいものです。Power Pivot は大規模データセットにも対応しますが、いくつかの工夫で、特にデータが増えたときでも応答性を保てます。

モデルサイズを削減する

軽いモデルほど高速に動作するため、不要なものは削除します。

データ ビューで未使用列を削除できます。ピボットテーブルで使わない列でもメモリを消費するため、削ることでモデルをクリーンに保てます。

新しいデータを取り込む際は、Power Query を使ってモデルに読み込む前に行や列をフィルタリングしましょう。必要なフィールドだけを読み込むことで、全体がすっきりします。

計算列は必要な場合を除き、できるだけ避けます。各行ごとに値を保存するため、ファイルサイズがすぐに大きくなります。対してメジャーは、ピボットテーブルが必要とするときにのみ計算されるため、より効率的です。

効率的なデータ型を選ぶ

Power Pivot はデータ型によって異なる圧縮を行います。適切な型を使うと、体感できる差が出ます。

「データ ビュー」で列を選択し、リボンの データ型 から最も正確な型を選びます。例：

• 整数 → 整数
• 小数 → 小数
• 計算に使わない ID やコード → テキスト

正しい型を選ぶと圧縮効率が上がり、サイズ削減と計算の高速化につながります。

適切なデータ型を確認・使用。画像提供：著者

リフレッシュと計算の問題に対処する

ピボットテーブルに最新データが反映されない場合は、Power Pivot タブで すべて更新 をクリックします。ソースファイルからすべて再読み込みされます。

数値がおかしい場合は、ダイアグラム ビュー を開いてリレーションシップを確認してください。欠落や破損があると、合計が跳ねたり誤ってフィルタされることがあります。

複雑なメジャーで DAX エラーが出る場合、間接的な自己参照が原因のことがよくあります。その際は、より単純なロジックに書き換えるか、VAR ブロックで循環参照を解消してください。

Power Query と Power BI との統合

Power Pivot の強みのひとつは、Microsoft のデータスタックと容易に連携できる点です。Power Query でモデルに取り込む前にデータを整形・クレンジングしたり、インタラクティブなダッシュボードが必要になったときにモデル全体を Power BI に移行したりできます。

Power Query でデータを整形・変換する

Power Query は、Power Pivot に読み込む前にデータを準備するのに最適な場所です。前処理でクレンジング、フィルタリング、整形を行い、モデルの整理を保ちます。

Power Query は、データ > テキスト/CSV から > 変換 から開きます。エディターで次の操作が可能です。

• 重複行の削除
• 列名の変更や順序入れ替え
• 不要な値のフィルタリング
• モデルに入る前にデータ型を変更

Power Query はウィンドウ右側に各ステップを記録します。つまり、ファイルを更新するたびにクレンジングが自動的に実行されます。

準備が整ったら、閉じて読み込む > データモデル を選択します。クレンジング済みデータが Power Pivot に直接読み込まれます。

モデルを Power BI にエクスポートする

より豊かなビジュアルや共有ダッシュボードが必要な場合、Power Pivot のモデルを Power BI に持ち込むこともできます。手順：

1. Excel ブックを保存
2. Power BI Desktop を開く
3. 「データの取得 > Excel ブック」へ
4. ファイルを選択

Power BI は、Power Pivot に存在する通りにテーブルとリレーションシップを取り込みます。そこからダッシュボードを作成し、チームで共同作業を行い、スケジュール更新を設定して、手作業なしで最新状態を維持できます。

持続的に使えるモデルのベストプラクティス

モデルが大きくなるほど、整理整頓が重要になります。更新、デバッグ、拡張がしやすくなるからです。長期にわたりモデルをクリーンで信頼できる状態に保つための習慣をいくつか紹介します。

命名規則と整理

明快な名前は、数週間や数か月後にファイルへ戻ったときに大きな差を生みます。たとえば Total_Sales、Total_Quantity、Profit_Margin のように読みやすいメジャー名を使い、何を表すか一目でわかるようにしましょう。

また、Power Pivot ウィンドウで関連メジャーを 表示フォルダー にまとめることもできます。モデルが大きくなったとき、目的の計算を探しやすくなります。

データ検証

数値を信頼する前に、簡単なチェックを行いましょう。

• ソースデータの合計とピボットテーブルの合計を照合する
• 次のようなシンプルな DAX でチェックする：

• COUNTROWS() でテーブルの行数を確認

• DISTINCTCOUNT() で顧客や商品の固有数を検証

こうした小さなテストで、欠落したリレーションシップや誤ったフィルター、データ問題を、重大化する前に見つけられます。

モデルの保守と更新

新しいデータが届いたら、Power Pivot タブの 更新 または すべて更新 を選択します。接続されたソースからすべて再読み込みされます。

新しいリレーションシップの追加や主要メジャーの書き換えなど、大きな構造変更を行う前には、バックアップコピーを保存しましょう。問題が起きた場合の安全な戻し先になります。

まとめ

Power Pivot はデータを一箇所に集約し、明快で信頼できるレポート作成を支援します。モデルを構築したら、数値を探索し、ビジュアルを作成し、ワンクリックの更新で一括アップデートできます。

Excel の全ツールセットを学ぶなら、Data Analysis with Excel Power Tools トラックと、もちろん Power Pivot in Excel コースもぜひご覧ください。