はじめに
皆さんはExcelのアドインの1つであるExcel PowerPivotはご存知でしょうか。
Excel標準機能だけでは限界があるけどデータベースを作るほどでもない時に活躍します。
手段として知っておくだけでも役に立つこと間違いなしの便利アドインです。
この記事を通じてまずはPowerPivotに触れ、便利さを体感してもらえれば嬉しいです。
例題：あなたならどうしますか？
さっそくですが例題です。
『株式会社キータ製作所』には以下のような売上データがあるとします。
【売上データ】
| 社員コード | 計上日 | 売上金額 |
|---|---|---|
| S0001 | 2022/06/25 | 5000円 |
| S0001 | 2022/06/28 | 2000円 |
| S0002 | 2022/06/28 | 1500円 |
| S0004 | 2022/07/01 | 6500円 |
| S0003 | 2022/07/01 | 1200円 |
| S0001 | 2022/07/05 | 1200円 |
| S0004 | 2022/07/13 | 1300円 |
| S0001 | 2022/07/18 | 4200円 |
| S0003 | 2022/07/21 | 550円 |
| S0004 | 2022/07/29 | 8000円 |
| S0002 | 2022/08/12 | 150円 |
| S0002 | 2022/08/12 | 800円 |
経理部のあなたは2022年7月の売上を部門ごとに集計してグラフ化する必要があります。
なお、社員情報については人事部の知り合いから入手しています。（以下参照）
【社員表】
| 社員コード | 社員名 | 所属部コード |
|---|---|---|
| S0001 | 山田 太郎 | B01 |
| S0002 | 田中 二郎 | B02 |
| S0003 | 吉田 三郎 | B02 |
| S0004 | 加藤 四郎 | B03 |
【部表】
| 部コード | 部名 | 所属本部コード |
|---|---|---|
| B01 | プラスチック部 | H1 |
| B02 | 溶剤部 | H1 |
| B03 | 計測器部 | H2 |
【本部表】
| 本部コード | 本部名 |
|---|---|
| H1 | 化学品本部 |
| H2 | 機械本部 |
図にするとこんな感じですね。
あなたならどんな方法を使って集計／グラフ作成をしますか？
解法1：一時表を作る
以下のような一時表（ビュー）を作り、対象期間（今回なら2022年7月）に絞ってグラフ化。
データ量が少なければ手入力で作れますし、そのほうが早い場合もあります。
ただそれなりのデータ量になるとXLOOKUP関数（VLOOKUP関数）を酷使することになります。
検索関数（XLOOKUPなど）はとても便利ですが、処理が重いExcelの原因にもなります。
解法2:Excel PowerPivotを使う
PowerPivotを使えば複数の表にまたがるデータモデルを作成し、柔軟な集計／グラフが作成できます。
ぜひ例題にある「売上データ」「社員表」「部表」「本部表」をExcelの任意のセルにコピー＆ペーストし、
以下の手順を進めてみましょう。
手順① PowerPivotアドインの有効化
[開発]タブ→[COMアドイン]を押下し、「Microsoft Power Pivot for Excel」を有効化（チェック）してください。
[PowerPivot]タブが新たに表示されればOKです。
もし[開発]タブ自体が表示されていない場合
[ファイル]タブから[オプション]→[リボンのユーザー設定]に移動します。
[メイン タブ]の下の[開発]チェックボックスをオンにします。
手順② データを「テーブル化」
データとして使用したい表範囲を選択し、[ホーム]タブ→[テーブルとして書式設定]を押下。（もしくはCtrl+T）
テーブル内のセルを選択した状態で[テーブルデザイン]タブからテーブル名をわかりやすい名前に更新しておく。
今回は「売上データ」「社員表」「部表」「本部表」をすべてテーブル化しておきます。
手順③ PowerPivotデータモデルに追加
前手順で作成したテーブル内のセルを選択した状態で[PowerPivot]タブから[データモデルに追加]を押下。
PowerPivotウィンドウが自動的に立ち上がるかと思いますが一旦は最小化しておいてください。
同様に「売上データ」「社員表」「部表」「本部表」をすべてデータモデルに追加してください。
手順④ データモデルの作成
前手順でPowerPivotウィンドウが自動的に開かれていると思います。
（開かれていなければ[Power Pivot]タブ→[管理]を押下してください。）
PowerPivotウィンドウにて[ホーム]タブ→[ダイアグラムビュー]を押下。
前手順でデータモデルに追加したテーブルが並んでいると思います。
今回は例えば売上データ.担当社員コードと社員表.社員コードを関連付ける必要があります。
どちらか一方の項目名をもう一方の項目名の上にドラッグアンドドロップすると関連付け（リレーション）が作成できます。
同じ要領で、
・社員表.所属部コード = 部表.部コード
・部表.所属本部コード = 本部表.本部コード
のリレーションも作成しておきましょう。
手順⑤ グラフ化
[挿入]タブ→[ピボットグラフ]を押下。
「このブックのデータモデルを使用する」にチェックをつける。
右サイドメニューに「ピボットグラフのフィールド」が表示されるので、グラフの完成形をイメージしながら項目を配置していく。
今回なら2022年7月（フィルタ）における本部（軸）ごとの売上金額（値）を積み上げ棒グラフで描写しています。
おまけで凡例に部名を設定しています。
PowerPivotのメリット
-
(XLOOKUP等の検索関数に比べ) 動作が軽い
→ 大量データ、大量テーブルでも圧倒的に軽いです -
ピボットグラフ（テーブル）の柔軟な組み換えが可能
→ やっぱり社員ごとの売上を可視化したい、特定の部は除いて集計したい、といった上司の無茶振りにもすぐ対応可能。 -
様々なデータソースを扱える
→ 今回はExcelデータを使う例しか紹介していませんが、Accessを始めOracleなどのデータベースやCSVファイルもインプットデータにできます。
※後日このあたりは別記事にしようかと思います
PowerPivotのデメリット
-
テーブル間の複雑な結合条件は指定できない
→ PowerPivotは単一のキーによる1:n結合しか指定できません。複数項目をキーにしたリレーションやn:n結合はできません。
（実は工夫次第ではできますが思い通りに扱うのは難易度が高いです。） -
他人の作ったPowerPivot定義が読み取りづらい
→ データモデルがER図に近いですが、注釈をつけたりすることはできません。
複雑になものを他人と運用していく場合はちょっとした設計書（ER図やデータモデル定義）が必要になってくるかもしれません。
おわりに
いかがでしたでしょうか。
Excel標準機能では足りないけどデータベースを作るほどでもない、そんな時にはぜひ活用を検討してみてください。