Power Pivot はその名の通り、Excelのピボットテーブルがよりパワフルになったものです。
ピボットテーブル自体かなり高機能で様々な集計ができるのですが、それでも割と簡単な計算ができないという一面がありました。
しかし、Power Pivot では DAX を使うことで計算できる幅が広がります。しかも、動作が非常に速く、複雑な集計でもピボットテーブルのユーザー操作に追従して遅延無く集計してくれます。
この記事では Power Pivot を作るところから始めて、DAXを使って複雑な計算をするところまでを解説します。
また、DAXにおいて必修とも言えるコンテキストについても分かりやすく解説してみたいと思います。
DAXに興味がある方は、是非知識を身に付けてその強力な計算パワーを使いこなせるようになって下さい。
Power Pivot を作る
Power Pivot を作る際に避けて通れないキーワードがあります。それは「データモデル」です。Power Pivot を作るためにはこの「データモデル」を作る必要があります。
最も適切なデータモデルの作成方法は Power Query の出力先をデータモデルに設定する方法です。
図のようにPower Query でデータを収集、整形、加工して集計に使えるデータに仕上げたものを「データモデル」に出力して、 Power Pivot で集計するという流れです。
次の項でこの操作を解説します。
Power Query 作成、続いてデータモデルを作成する手順
実際の手順を追って解説していきます。
テーブルから Power Query を作成する
まず、集計対象となる適当なテーブルを準備して下さい。サンプルファイルも用意しましたので、必要な方は以下のリンクからダウンロードして下さい。
用意したテーブルのどこかのセルを選択した状態で、メニューから「データ」＞「テーブルまたは範囲から」をクリックしてください。
すると Power Query エディター のウィンドウが開いたと思います。
ここでは Power Query で少しだけデータを加工することにします。Power Query エディターのメニューから「列の追加」＞「カスタム列」を押して、出てきた小ウィンドウに図のように入力します。
これは日付から年度を生成するM関数の式になっています。
Power Query エディターを閉じる時に設定
何気なく Power Query エディターを操作してバツ印のボタンでウィンドウを閉じてしまうとデータモデルを作成するステップが飛ばされてしまいますので要注意です。
データモデルを作成する場合は、Power Query エディターを閉じる際に下記手順で行います。
操作手順
- メニューから「ホーム」＞「閉じて読み込む」の下三角マーク をクリックします
- 出てきたメニューから「閉じて次に読み込む…」 をクリックします
- 出てきた小ウィンドウで下の図のように設定します
ここで「データモデル」のキーワードが出てきましたね。「このデータをデータ モデルに追加する(M)」にチェックを入れることでPower Query の出力をデータモデルに追加することができるのです。
これでデータモデルを作成することができました。
Power Query エディターを設定せずに閉じてしまった？大丈夫です！
うっかり Power Query エディターを何も設定せずに閉じてしまっても後から設定を変えられますので安心して下さい。
まず、Power Query の一覧を表示させます。メニューから「データ」＞「クエリと接続」をクリックしてください。
すると、ウィンドウ右側にサイドウィンドウが開いて Power Query の一覧が表示 されたと思います。
対象となる Power Query を右クリック してサブメニューを出します。
ここで「読み込み先…」をクリックします。
すると先ほどの小ウィンドウが出てきますので、ここで「このデータをデータ モデルに追加する(M)」にチェックを入れます。
これで無事データモデルを作成することができました。
テーブルからいきなりデータモデルを作る方法
その他に、手っ取り早くExcelのテーブルから直接データモデルを作成 する方法もあります。
対象となるテーブルのどこかのセルを選択した状態で、メニューから「挿入」＞「ピボットテーブル」をクリックして下さい。
すると図のような小ウィンドウが出るので、ここで「このデータをデータ モデルに追加する(M)」にチェックを入れます。
この方法でもデータモデルを作成することができました。
データモデルから Power Pivot を作成する
データモデルができてしまえば Power Pivot は簡単に作成することができます。
ここまでの操作で既にデータモデルができていると思いますので、メニューから「挿入」＞「ピボットテーブル」＞「データモデルから(D)」をクリックして下さい。
出てきた小ウィンドウは基本的にそのまま図のように「OK」を押します。
すると新しいウィンドウにピボットテーブルが作成されます。これが Power Pivot です。
一見いつものピボットテーブルに見えますが、機能が強化されていますので、次の項からこの強力な機能を触っていきましょう。
メジャーを作成して DAXを記述する
Power Pivot では DAX というプログラミング言語で強力なデータ処理ができるようになっています。
この DAX は Power Pivot では「メジャー」というピボットテーブルにおける列項目のようなものを作成するのに使用します。
まずは簡単な例からやってみましょう。
図のようにデータモデルの名前を右クリックして出てきたサブメニューから「メジャーの追加…」をクリックします。
すると小ウィンドウが開きますので、下の図のように入力して下さい。キー入力に伴い自動補完機能が働いてくれるので活用しましょう。
これは見ての通り売上個数を合計するという内容ですが、表示を百個単位にするために100で割っています。更にカンマ区切りの表示形式の設定もしています。
従来のピボットテーブルでは列の集計することはできましたが、この例のように計算を加えることはできませんでした。
同じことをする場合は、わざわざパワークエリ上で売上個数を100で割った列を追加しておく必要がありました。しかし、Power Pivot ではこれを メジャーの中で記述することで実現できます。
早速作成した 「売上個数（百個）」を使って集計させてみましょう。下の図のように百個単位での集計ができました。
ここでは単純な合計の計算と割り算をDAXで記述しましたが、DAXには様々な関数が用意されているので、多様な計算が可能です。
メジャー作成のメリット
DAXの詳しい解説に入る前にメジャーを作成することのメリットについて少しご紹介したいと思います。
集計操作の１ステップが不要になる
これまでのピボットテーブルでは集計したい列名を集計フィールドにドロップすることで集計することができました。しかし、メジャーは集計にのみ使うものであることから、選択するだけで自動で集計フィールド（右下の 「値 Σ」 のカラム）に入ります。これで僅かではありますが、わずらわしい操作が不用になって便利です。
名称を自由に設定できる
また、メジャーは名前が自由に設定できるので、Power Pivot の表やグラフを作成した時の見易さが向上します。これまでのピボットテーブルでは「合計／売上個数」などと少々くどい表示になっていたので、表を作成した際もあまり美しくありませんでした。Power Pivot では「売上個数」のようにスッキリと分かりやすい表を作成することが可能になりました。
表示形式の再設定が不要
更にメジャーでは設定した数値の表示形式が保存されるので毎回設定し直すということも不用となり、これも操作の手間を減らすことが出来ます。
DAXの活用事例
続いてDAXの活用事例を紹介していきます。これらは全てこれまでのピボットテーブルではどうやっても実現出来なかったものばかりです。DAX活用により様々なことができることがご理解頂けると思います。
では早速見ていきましょう。
集計値同士の計算
これまでのピボットテーブルでは1つの列の集計は累計や、差分など様々な計算の設定ができましたが、2つの列の計算をすることは出来ませんでした。
そんな計算の例として、利益率の計算があります。利益率とは 利益の合計 を 売上の合計 で割った比率になります。式で表すと sum(利益) / sum(売上) と、とても簡単です。
しかし、この計算はこれまでのピボットテーブルではどうやってもできませんでした。
これが、Power Pivot なら簡単に実現できます。DAXは図のように作成します。
DIVIDE関数
ここで、DIVIDEという関数が登場しました。DIVIDE は英語で「割る」ことで、ここでわ「割り算」の意味です。なぜ、単純に「/」（スラッシュ）で割り算の計算をしないかと言いますと、通常の割り算の計算ではゼロで割ってしまうとエラーになってしまうのですが、このDIVIDE関数を使用するとこのゼロで割るエラーを回避できるためです。
「割り算する時はDIVIDE関数を使う」と覚えておきましょう。
さて、話は戻って、、、、
サンプルファイルには「売上」と「利益」は無く、「売上個数」、「値段」、「利益／個」しかありません。
ここから、「売上個数」ｘ「値段」＝「売上」、「売上個数」×「利益／個」＝「利益」の計算がしたいのですが、どうしたら良いでしょうか？
これには次の SUMX関数 を使います。
SUMX
これは見ての通りただのSum関数ではありません。「X」つまり「何か」を合計する関数 です。
この「何か」とは元データの1行の中にある値を使った計算式です。
例えば、売上のデータに「売上個数」と「値段」のみが入っていたとしましょう。売上額を計算するには、「売上個数」×「値段」を計算し、更にこれらを合計する必要があります。
これまでのピボットテーブルなら、パワークエリのデータの加工に戻って、「売上個数」×「値段」の「売上額」の計算列を作成しておいて、この売上額の列をピボットテーブルで合計する必要がありました。
しかし、sumx関数１つでこの両方の処理が出来てしまいます。
操作内容
この売上額合計のメジャーは図のように作成します。
解説
sumx関数で最初に指定するのはテーブル名で、サンプルデータの場合、売上データが入っているのが「テーブル1」でしたので、テーブル1を指定しています。
また、DAXではテーブルは「’」（シングルクオテーション）で括ることになっているので、シングルクオテーションをキー入力すると補完機能が働いて選択できるリストが現れますので活用してみてください。
sumx関数で次に指定するのは計算式ですので、ここでやりたかった「売上個数」x「値段」の計算式を入力しています。
SUMX関数の仲間たち
ちなみに、このXがついた関数は以下のようなものがありますが、これらは全て同じような機能で、計算結果の平均値、最小値、最大値、空ではないデータ個数を出力するなど、統計処理の方法が異なるものになります。
- AVGX
- MINX
- MAXX
- COUNTX
DISTINCT
distictは「はっきりと異なる」という意味の単語です。機能としては、同じ列の中に何種類のデータがあるか、その個数を返す関数になります。
これは例えば1日あたりの売上個数を計算する時に使えます。
ある期間の売上個数の合計をお店が開いている日数で割れば良い訳ですが、distinct count関数は、このお店が開いている日数を計算してくれます。
操作内容
これは図のようにメジャーを作成することで計算することが出来ます。
解説
このメジャーでは DISTINCTCOUNT 関数で日付のデータが何種類あるか、つまり日数を計算して、売上額の合計を日数で割っています。
このように DAX は様々な集計のニーズに対応できるところが凄いところです。
CALCULATE
DAXには様々な関数がありますが、多くは集計方法の違いです。
しかし、CALCULATE関数はそれらの概念とは一線を画する関数です。
CALCULATE関数は一言で表現すると「コンテキストを自在に変える関数」です。
「データモデル」に続いて「コンテキスト」という新しい言葉が登場しました。「コンテキスト」はDAXを使いこなす上で、是非とも知っておく必要があるキーワードですので、次の項で解説させて頂きます。
コンテキストとは何か？
Context とは英語で文脈の意味です。DAXの世界でコンテキストは計算プログラムから「見えている範囲」と言えます。
例えば下の図ピボットテーブルの集計表を見てみます。赤枠で囲ったセルは元のデータから、支店名を「名古屋」、年度を「2023」にフィルターをかけた表の売上列を集計したのと同じです。
このフィルターがかかった表がこの赤枠のセルにおけるコンテキストになります。
ピボットテーブルの計算プログラムにとっては、赤枠のセルにいる時は 図の右の表が「見えている範囲」になります。
例えば支店が「神戸」のデータは赤枠セルにいる間は「見えない」訳です。
ところがCALCULATE関数を使うとこのピボットテーブルのコンテキストに制限されることなく「見える範囲」を変更することができるようになります。
1番簡単な例はフィルターを全て取り払ってしまう計算です。メジャーを図のように作成します。するとピボットテーブルのどのセルにも全体の合計値が表示 されるようになりました。
これではまるで使い物にはなりませんが、これを少し使い物になるように変更してみます。
図のようにメジャーを変更してみましょう。
売上の合計を、CALCULATE関数で計算した売上全体の合計で割っています。すると、今度は全体の売上に対する比率が表示されるようになりました。
このように、CALCULATE 関数は計算プログラムから「見えている範囲」を自在に変えることで様々な計算を可能にしています。
次の項からこの CALCULATE 関数の他の活用例を解説します。
ALLSELECTED
前の例で全体の合計値との比率を計算しました。しかし、ピボットテーブルでフィルターを操作して「神戸」支店を外してみます。すると図のように比率の集計の合計が100%にならなくなってしまいました。
こういう出力ではなく、フィルターをかけ後でも全体が100%になるように計算したいとします。
そこで使うのがALLSELECTEDフィルターです。これを使うとピボットテーブル全体のコンテキストで 手動でフィルターがかけてある範囲全体 を指定することができます。
ではメジャーを図のように作成してみます。すると今度は売上比率の総計が100%となり想定通りの計算となりました。
口座の末残高
ここにある銀行口座の明細データがあります。口座の残高を年ごと、月ごとに末の残高を表示させたいケースを考えます。
明細のデータから月末、年末のものを特定するのは、日付が決まっている訳では無いので案外難しいものです。
ここでコンテキストの考え方を思い出してみましょう。このケースは「見えている範囲」の最後の行の値を取得すれば良い訳です。
もう少し言い換えると「見えている範囲」に更にフィルターをかけて「見えている範囲」で行番号が最大の残高を集計すれば良いということになります。
実際にメジャーを図のように作成します。するとうまい具合に末残高を表示してくれました。
ここでご紹介したExcelファイルは以下のリンクから入手できますので、参考とされたい方はダウンロードしてみて下さい。
まとめ
この記事では Power Pivot の作成の仕方から、DAXの使い方までを紹介しました。
更に、DAXを使いこなす上で理解が必須となる「コンテキスト」の概念について解説させて頂きました。
これらの知識を組み合わせることで多様な計算をすることが可能になります。
実際にはここで紹介したこと以上に複雑な計算も DAX では実現できると思いますので、皆さんの業務で是非活用してみて下さい。
