Power Query でデータを収集・加工した後はデータの分析を行います。
データの分析方法は無限にあると言っても過言ではない程多様です。
例えば日毎の売上データがあったとした場合、当月の集計、前月集計との比、四半期の集計、前年四半期との比、年間の集計、など少し例を挙げただけでもいくつもの分析の視点が挙がります,
データ分析者に求められるのは、多様な視点のデータ集計に的確に対応する能力です。
この記事では Power Query により提供されたデータを分析する方法とその特徴を解説します。
データ分析作業に取り掛かる前に分析方法の特徴を良く理解して賢く使い分けできるようになりましょう。
では、早速いってみましょう！
Power Pivot による集計
Power Pivot の基本機能の活用
Power Pivot の特徴は何と言ってもその スピード感 です。下の動画のようにデータ分析者の思考に合わせてマウス操作だけで次々と集計結果を出力できるところが従来の Excel の機能とは大きく違うところです。
スピード感のメリットとデメリット
この動画を見て分かるように Power Pivot での集計はグラフの 目盛りや表示形式を自動で設定 して、表示している値を適切に見えるようにしてくれています。ですから、データ分析者の思考が途切れること無く分析を続けることができます。
これは裏を返すと グラフの設定が簡単に変更されてしまう ということでもあります。グラフ表示の形式をユーザで色々と設定していても Power Pivot 更新時に設定がリセットされてしまうケースがあるということは頭に入れておいて下さい。
様々な集計方法が選択可能
Power Pivot の 基本的な使い方 は Excel の SUM関数と同様に値の合計値を計算させるものです。合計の他には図に示すようにデータの個数や平均などの集計が可能です。
上の図の集計方法を選択するウィンドウは、下の図のように操作をして出すことができます。
色々な集計方法がありますが、一般的に良く使われるのは「合計」の計算だと思います。
Power Pivot の「計算の種類」
Power Pivot では更に 計算の種類 というオプション機能があります。
先ほどと同じ小ウィンドウを出して、図のように操作してオプションを選択します。
ここでも様々なオプションが選択できることが分かります。なかなか複雑な設定のものも多いですが、 累計 や 基準値との差分 は一般的にも良く使われるものだと思います。
ここで累計計算の事例をご紹介します。図のように設定すると、売上の累計値が右肩上がりに計算されるのが分かると思います。これは年間の売上合計の状況などを表示する時に良く使う機能だと思います。
更なる応用 DAX関数
Power Pivot は名前が示す通り従来ののピボットテーブルの機能強化版になります。ここまで説明した使い方は従来のピボットテーブルの機能と同じものです。
しかし、Power Pivot には DAX というデータ分析言語 の機能が追加されています。プログラム言語によりユーザーのニーズに合わせた集計が可能になっています。
DAXの関数はExcel の関数のように一つの値をインプットに一つの値をアウトプットするのでなく、データを丸ごと塊として扱う ことを関数で表現しているため 理解し難いのが欠点 です。
一方でDAXで組んだ計算式は Power Pivot の操作で次々と形を変えて計算させることができ、やはりスピード感は大きなメリットです。
DAXでしか実現できない計算
DAXでなければ実現できない計算は色々とありますが、単純な例としては 集計値同士の計算 です。
利益の合計 を 売上の合計 で割った 利益率 の計算は単純に見えてDAXでなければ実現できない計算です。
しかも次のような単純な DAX関数式で計算ができてしまいます。
=divide(sum([利益]),sum([売上]))divide は割り算を行う DAX 関数です。わざわざ関数にしてあるのは、ゼロで割るケースがあると通常ではエラーになってしまうのでこれを回避するためにこの関数を使います。
この設定だけでピボットテーブルに利益率を簡単に表示することができるようになります。
分析ニーズの複雑さと DAX の難しさ
実務をやっていると、ピボットテーブルのオプションまで使いこなしても実現できない計算が色々とあることに気付くと思います。
これはピボットテーブルのオプションが力不足なのでは無く、分析のニーズがそれだけ多様で複雑 だということです。その多様で複雑なニーズを満たすために Microsoft が用意したのが DAX 関数と捉えることができます。
DAX関数は頭の良い人が良く考えて作り込まれた関数だと思います。しかし、これを全て身に付けて日常的に使いこなすのは、なかなかハードルが高いと筆者は考えます。先に述べたようにデータを塊で処理する DAX関数というものがなかなか理解し難いためです。
スピード重視で次々とデータ分析を進めるニーズがある時により高度な DAX を勉強してニーズを満たす計算式を組むのが良いと思います。
DAXは理解できる範囲で使いこなすだけでも十分に有用ですから、まずはそれを実践することをオススメします。
ちなみにDAX関数をもう少し詳しく知りたい方は次の記事で使い方を解説していますので参考にしてみて下さい。
Excel関数による集計
Power Query と相性の良い Excel関数
Excel 関数 は通常はセルのアドレスを指定してセルの値を参照しますので、その動きは 固定的 です。一方で、Power Query はデータを収集してまとめていくので、データの中身は常に変動 します。
ですので、通常では Excel 関数と Power Query は相性が良くありません。しかし、一部の Excel 関数はこの変動する Power Query に追従できる機能を持っています。
具体的には以下の関数になります。これらは Power Query により出力される テーブルの列名を指定するだけ でその列全体を処理してくれます。
- SUMIF / SUMIFS （合計を計算）
- AVERAGEIF / AVERAGEIFS （平均を計算）
- COUNTIF / COUNTIFS （データの数を計算）
ここでよく似た名前の関数を紹介しましたが、2つ目の関数にはスペルの最後に “S” がついています。これは１つ目の関数では集計の条件を１つしか指定できないのに対して、２つ目の関数では集計の条件を複数指定できるものになっています（つまり複数形の “S” なんですね）。
大は小を兼ねる ので基本的には２つ目の “S” がついた関数を利用することをオススメ します。
この関数の具体的な使い方は次のようになります。
=SUMIFS(テーブル1[売上],テーブル1[支店名],"駒場支店",テーブル1[日付],">=2024/4/1")ここで作成した Excel 関数式は以下の集計を行います。
- テーブル1の 売上 の値を集計
- 集計条件1：支店名は 駒場支店
- 集計条件2：日付は 2024年4月1日 以降
この Excel関数式を入力したセルは Power Pivot の出力が変動しても変動に合わせて適切に計算をしてくれます。
Excel関数の意外なメリット
実はここでご紹介する Excel 関数での集計方法は以上です。たったこれだけ？そうなんです。とてもシンプル。一つのセルに Power Query のデータの集計結果を出力する方法１つだけです。
ですが、このシンプルさが最強 なのです。これはつまり、ユーザのニーズに応じて Excel のワークシートのどんな場所にも Power Query のデータの集計結果を入れることができるということです。
このシンプルな事実は次のようなメリットがあります。
ユーザが細かく設定したグラフ表示形式が壊れない
Power Pivot の冒頭でグラフが次々と変化する図をご覧頂きましたが、反対にユーザーが細かくグラフの表示設定を施したものを数値だけ Power Query のデータで更新して表示したい場合はこの Excel 関数を使う方式が最適です。
グラフが参照するセルの値が自動で更新されるのみで、グラフの設定には何も影響しません。これで苦労して設定したグラフの表示形式が壊れてしまう心配は無くなります。
設定が簡単に変わらないというメリット
Power Pivot で表示しているものは、マウス操作で簡単に変えることができてしまうので、ユーザはつい設定をいじって見え方を変えてしまいます。
そうすると、KPI管理 のようにある値を確実に表示して欲しい時には困ったことになります。大事に値は 固定的 に表示したいので、Excel 関数の特徴である 固定的 という点が逆にメリットになるのです。
そこに見に来たら大事な数値が変わらずに表示されているという安心感は意外に重宝します。
DAXでも表現できない複雑な計算が可能
ピボットテーブルや DAX でも実現できないような複雑なステップを踏んだ計算もこの Excel関数を使った方法なら可能になります。
更に、Excel 関数は DAX 関数のように頭をひねって悩む必要は無く、今までの Excel 関数の知識で数式を作成することが可能です。
特に、複雑な計算ならステップ毎に計算式を作って最終的な値を導き出すというやり方にすることで難易度はかなり下がります。
まとめ
この記事では Power Pivot で収集・加工したデータを分析する方法を色々と紹介しました。Power Pivot でスピーディーに集計する方法 と、Excel関数で固定的に集計値を得ていく方法 はそれぞれメリットとデメリットがあります。
メリットとデメリットは表裏一体なので、むしろそれぞれの特徴を良く理解してユーザ側が最適な手段を選択 するというのが大切なのだと思います。
みなさんもこの知識を踏まえて最適な分析方法を使い分けて、データ分析者としてのスキルを磨きをかけて頂ければと思います。
