[char no=”3″ char=”Aさん”]種類の異なるテーブルが複数ありますが、これらをまとめて集計/分析するための良い方法はないですかね？
関数のVLOOKUPとかで全部まとめるのも大変ですし、データ量も多くなりそうなんです・・・。[/balloon]
テーブルが複数あり、データ量が大きくてもピボットテーブルと近しい操作感で集計/分析が可能です。
では、詳細について解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな方を挙げてみます。
- Excelで複数種類のテーブルでデータ集計/分析を行う機会が多い方
- Excelで集計/分析を行う対象データ数が多い方（数十万レコード以上）
- パワーピボットがどういう機能か知りたい方
前提条件
この記事のテクニックを使うためには、以下の条件を満たす必要があります。
- ExcelのバージョンはExcel2010以降（Microsoft365含む）が必須
- Excel2010ユーザーの場合、事前にMicrosoft社公式HPよりExcel 2010 用 Power Pivot アドインのダウンロードが必要
- ピボットテーブルとパワークエリの概要を理解していること
→まず、以下の記事で概要を把握することをおすすめします。
ピボットテーブルとパワークエリの詳細を知りたい方は下記記事をご参照ください。 ピボットテーブルとはどんな機能か？活用の流れや手順、使い方まとめ [char no="3" char="Aさん"]Excelでいろいろな切り口で集計するのに、いちいち関数を組み直すのが大変です。。もっと楽に集計できる方法ってな･･･ 【Excel】パワークエリ（Power Query）とは？できることや使い方入門【動画あり】 - Excelを制する者は人生を制す Excelで集計前のデータの収集や整形に苦労している方は、パワークエリ（Power Query）を活用することで、マウス操作を中心に一連の作業を自動化できます。この記事では、Excel本著者が初心者向けにパワークエリの概要や基本の使い方を図解・動画付きで解説します。
Power Pivot（パワーピボット）とは
パワーピボットとは、ピボットテーブルの強化版であり、データモデルを元データとした集計/分析ができる機能のことです。
なお、データモデルとは、Excelブック内の新しい格納先であり、複数テーブルをリレーションシップ（連携）させ、仮想的に1つのテーブルに集約した元データを構築できます。
ちなみに、データモデルはデータを圧縮して格納できるため、従来のExcelワークシート以上のデータ数を扱うことができ、仕様上の1テーブルあたりで管理可能なレコード上限数は約20億（1,999,999,997）です。
Excel2007以降のワークシートは1,048,576行のため、約1,900倍にもなります。
データモデル内のデータはExcelワークシートではなく、Power Pivotウィンドウで確認・編集が可能です。（詳細は以降で解説）
データモデルの仕様については、Microsoft社の公式HPもご参照ください。 データ モデルの仕様と制限 - Microsoft サポート アップロードにおけるファイル サイズの制限と Excel 2013 のブック データ モデルのレンダリングについて説明します。 許容されているオブジェクト数、文字列の最大長、接続、要求について見ていきます。
データモデルの基本は「スタースキーマ」
リレーションシップを設定する際は、どのようにテーブル間を関連付けるかの設計（モデリング）が大事です。
一般的なのは「スタースキーマ」という星形にテーブル間を連携させていくモデルです。
星形の中心に位置するのが「ファクトテーブル（またはトランザクション）」です。
こちらがベースとなるテーブルであり、出来事の記録を更新していくためにレコードが順次増えていくものです。
そして、周辺にあるのが「ディメンションテーブル（またはマスタ）」です。
各主キーの一意のデータを管理するテーブルであり、データ集計/分析時の切り口にもなります。
主キーとは、そのレコードが一意（重複していない）であることを示すためのコードや番号のことです。（代表例として、社員番号や商品コードなど）
この2種類のテーブルの関係性は、各テーブルをつなぐ主キーの線のつなぎ目にある「1」と「N」でも判別できます。
（「1」は一意、「N」は複数）「1」はディメンション、「N」がファクトになります。
こうしたテーブル間の連携をさせることで、次のメリットを得ることが可能です。
- 全体のデータ量を最小化できる
- ディメンションのメンテナンスもしやすい
- データ集計/分析の速度向上が期待できる
上記の恩恵を受けるためにも、ファクトとディメンションで重複するフィールド（列）は、ファクト側を削除しておきましょう。
スタースキーマおよびその他のモデリングについて、Microsoft社の公式HPもご参照ください。 スター スキーマと Power BI での重要性を理解する - Power BI スター スキーマの設計と、パフォーマンスと使いやすさのために最適化された Power BI セマンティック モデルの開発との関連性について説明します。
Power Pivot（パワーピボット）を使う際の作業ステップ
パワーピボットですが、大枠のイメージは次の5ステップで設定していきます。
では、各ステップの詳細を順番に解説していきましょう。
【STEP0】「Microsoft Power Pivot for Excel」アドインをON
パワーピボットを使う前準備として、「Microsoft Power Pivot for Excel」アドインをONにしましょう。
このアドインをONにすることでリボン上に「Power Pivot」タブが表示されます。
アドインをONにする手順は以下の通りです。
この設定は最初の1回のみで、今後はどのExcelブックを開いても表示されるようになります。
なお、Excel2016以降（Microsoft365含む）の場合は、以下の手順でも良いです。
【STEP1】ブック内に全テーブルを集約
まず、連携対象のすべてのテーブルを同じExcelブックに集約することから始めましょう。
集約にあたり、方法は次の3通りあります。
- ワークシート上に手入力のテーブルを用意
- 外部ファイルのテーブルをパワークエリで取得し、ワークシート上に読み込み
- 外部ファイルのテーブルをパワークエリで取得し、「接続の作成のみ」で読み込み
※ワークシート上に表示されない状態
なお、個人的にはこのタイミングで全テーブルをデータモデルに追加しておくことをおすすめします。（後工程が楽になるため）
データモデルへの追加については、次の2通りあります。
- パワークエリの「データのインポート」ダイアログ
- リボン「Power Pivot」タブ
それぞれの手順は以下の通りです。
【STEP2】テーブル間のリレーションシップ設定
テーブルを集約したら、それらを連携させていきましょう。
先述の通り、テーブル間の連携は同じ「主キー」を基準に行います。
このリレーションシップの設定方法は2通りあります。
- ワークシート上の「リレーションシップ」コマンドで設定する
- Power Pivotウィンドウのダイアグラムビューで設定する
この設定手順は別記事で解説予定ですが、1よりも2の方がドラッグ＆ドロップで設定できて簡単です。（ただし、事前にデータモデルに追加が必要）
いずれの場合も最終的にはPower Pivotウィンドウを起動し、ダイアグラムビューを用いて問題なくテーブル間が連携されているかを確認しましょう。
これで、データモデルに追加されていないテーブルがなく、かつすべてのテーブルが連携されていれば問題ありません。
【STEP3】データモデルを編集
続いて、Power Pivotウィンドウ上でデータモデルに追加が必要なデータがあれば、編集（前処理）を行いましょう。（なければこのステップは飛ばしてください）
代表的な編集内容は以下の内容が挙げられます。（それぞれの詳細は別記事で解説予定）
- 計算列の追加
※テーブル間の計算をさせた列を追加する等 - 日付テーブルの作成
- 「日付テーブルとしてマーク」を設定
なお、日付テーブルとありますが、実はパワーピボットで時系列を切り口に集計/分析を行う場合、次の要件を満たす日付テーブルを用意しておく必要があります。
- データ型が「日付」もしくは「日付/時刻」の列（日付列）が必要
- 日付列は1年分以上の連続した日付が必要（すべて一意かつ空白なし）
- 日付テーブルには日付テーブルとしてマークされていることが必要
この日付テーブルをPower Pivotウィンドウだと簡単に作成できるコマンドが用意されています。
また、自前でワークシート等に日付テーブルを用意した場合は、上記の要件を満たした上で、「日付テーブルとしてマーク」の設定をPower Pivotウィンドウ上で行う必要があります。
日付テーブルの詳細について、Microsoft社の公式HPもご参照ください。 Power BI Desktop での日付テーブルの設計ガイダンス - Power BI Power BI Desktop で日付テーブルを作成するための手法とガイダンスです。
【STEP4】ワークシート上にピボットテーブル挿入
データモデルの準備が整ったら、ワークシート上にピボットテーブルを挿入します。
パワーピボットの場合、このピボットテーブルのデータソースが、ワークシート上のテーブルや範囲ではなく、データモデルとなります。
ちなみに、「ピボットテーブルのフィールド」ウィンドウのフィールドセクション（テーブル名・フィールド名の部分）は、テーブルの数だけ表示領域が狭まり視認性が低くなるため、レイアウトをフィールドセクションは左、エリアセクションは右に変えています。
（具体的な手順は別記事で解説予定）
また、テーブル名のところに円柱マークがあるものが、データモデルに追加されていることの目印です。
円柱マークがないものはワークシート上に存在するテーブルを示し、データモデルとワークシートの両方にあるテーブル名は2つとも表示されます。
なお、ブック内にリレーションシップが設定されているか否かは区切り線で確認できます。
区切り線の中に一つのテーブルのみの場合は単独なものだと判断してください。
【STEP5】ピボットテーブルで集計/分析
ワークシート上にピボットテーブルが挿入されたら、任意の集計条件を設定していきましょう。
基本的には、「値」ボックスに設定する数値はファクトテーブル、集計条件となる「行」・「列」ボックスに設定するのはディメンションテーブルのフィールド（列）となります。
なお、パワーピボットでは既存のピボットテーブルの機能の一部（集計フィールドや日付/時刻を除くグループ化等）が使用不可となる一方、「メジャー」という計算や処理を行う機能を新たに設定することが可能です。
このメジャーとPower Pivotウィンドウの計算列は、いずれもDAX（Data Analysis Expressions/ダックス）というパワーピボット専用の関数を用いて設定します。
このメジャーをうまく活用することで、列データを増やさずに計算を行うことが可能となります。
さいごに
いかがでしたでしょうか？
必要な前提知識やスキルが多く、難易度は高いものの、複数テーブルをまとめて集計/分析したい場合にパワーピボットは非常に便利です。
実務でビッグデータをExcelで管理する機会がある方は、今後の必須スキルになることでしょう。
なお、パワーピボットの概要は、私の拙著でも解説していますので、こちらも参考にしてみてください。
ただし、メジャー等の解説は薄いため、Microsoft社の公式HP、もしくはより詳細を解説した書籍をご参照ください。
データ分析式 (DAX) リファレンス - DAX
データ分析式 (DAX) は、数式式を構築するために組み合わせることができる関数と演算子のライブラリです。
docs.microsoft.com
ちなみに、テーブルの種類が少ないなら、パワークエリ→ピボットテーブルで済ませた方が分かりやすい場面もありますので、他の主要機能もしっかり学んでおくことをおすすめします。
事務作業の時短効果が高いExcel（エクセル）の5大機能まとめ
[char no="3" char="Aさん"]事務作業でExcelをよく使うので、もっとスキルアップしたいです。ただ、Excelはたくさん機能があって、何を学･･･
Excelを制する者は人生を制すまた、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします！
特典として、Excelワークシート関数一覧（計22スライド）やExcelショートカットキー一覧（計25スライド）等をプレゼント！
ご参考になれば幸いですm(_ _)m
まだまだ参考となる書籍は少なく、職場で知っている人も見かけたことがないため、ビジネスの現場に浸透するにはまだ時間はかかりそうですね。
ただし、パワーピボットを学ぶことで、RDBやデータシェアハウス的なデータのモデリングに通ずる考え方を学ぶきっかけにもなるため、今後Excel以外のBIツール（tableau等）を設計・運用する際にも役立つはずです。
実務で役立ちそうだなと思ったら、ぜひチャレンジしてみてくださいね！
コメント Comments
コメント一覧
コメントはありません。
トラックバックURL
https://excel-master.net/data-aggregation/power-pivot-summary/trackback/