最近はエクセルの奥の深さを改めて考えさせられる日々を送る・・・
インチキシステム管理者の管理人です。
最近、遅ればせながらようやく・・・・
POWER QUERY
POWER PIVOT
を使う機会があり一生懸命勉強したので忘備録として掲載しておきます。
やりたいこと
まずやりたいことですが・・・
複数のシートの正規化されたデータを結合して・・・
ピボットテーブルで集計することです。
下の写真では・・・
3つのシート
- 売上データ
- 支店コード
- 担当コード
を結合して・・・
最後にピボットで支店別・担当者別の売り上げを集計しています。
VLOOKUPを使わなくてもよい
この方法を知らなければ・・・・
VLOOKUP関数を使って・・・
別にシートを作ってから・・・
最後にピボットテーブルを作るという方法になりますが・・・
マスタが多くなると・・・
いちいちVLOOKUP関数で紐づけするのも結構面倒です・・・
エクセルの機能(テーブル化・パワークエリ・パワービボット)を使えば・・・
毎回こういうテマが省けますし・・・
データが追加されても・・・
テーブル化されていればビボットテーブルの範囲を修正する必要もありません。
使えるようになれば非常に便利な機能です。
まずは準備
まずは・・・
パワークエリとパワーピボットが使える様に準備します。
下の図のように・・・
デザイン → データモデルの管理
を選択します。
【この機能を使うには、データ分析アドインをオンにします。】と表示されますので、
迷わず【有効化】を選択します。
次に・・・
開発 → COMアドイン
を選択します。
- Microsoft Power Map for Excel
- Microsoft Power Pivot for Excel
が追加されていることが確認できればOKです。
各シートのデータをテーブル化する
次に各シートのデータを全てデータ化します。
データが入力されている任意のセルを選択した状態で・・・
デザイン → テーブルとして書式設定
を選択します。
必ず全てのシートのデータをテーブル化してください。
各テーブルをデータモデルに追加する
次に各シートのテーブルを全てデータモデルに追加します。
テーブルのデータが入力されている任意のセルを選択した状態で・・・
Power Pivot → データモデルに追加
を選択します。
必ず全てのシートのテーブルをデータモデルに追加してください。
各テーブルのリレーションを作る
ACCESSを使える方であればお馴染みですが・・・
リレーションを作成します。
全てのテーブルをデータモデルに追加したら・・・
Power Pivot → 管理(データモデル)
を選択し・・・
下の画面で・・・
【ダイアグラムビュー】を選択します。
下の図のような画面が表示されますので・・・
【それぞれのテーブル間で紐づけする項目から項目】へマウスをドラッグさせます。
すると下図の様に・・・
リレーションが作成されます。
これもACCESSを使ったことがある方であれば、お馴染みだと思います。
この手の作業はいつもACCESSを使ってやっていましたので・・・
EXCELにこんな機能があることを知らなかった管理人は目からうろこです。
ピボットテーブルを追加する
最後にピボットテーブルを追加します。
挿入 → ピボットテーブル
を選択します。
次に・・・
外部データソースを使用
を選択します。
下の図の画面が表示されるので・・・
【テーブル】を選択し・・・
【ブックのデータモデルのテーブル】を選択します。
後は通常のピボットテーブルの追加と同じように・・・
列・行・値とするカラムを選択して集計させます。
これまでと違うところは・・・
フィールドリストに各テーブルからカラムを選択できるように表示変更されています。
これはかなり便利です。
是非、いつ何時でも使いこなせるようになりたいものです。
PIVOTグラフ
最後にPIVOTグラフです。
挿入タブからPIVOTグラフを選択してグラフを作成できます。
PIVOTテーブルと連動していますので・・・
スライサーと連動ができるという優れものです。
コメント