いつもはいろんなデータをどうやって使いやすいデータに整形するかというテーマで書いているのですが、今回はちょっと違った切り口でお届けします。
題して、「パワークエリで作ったデータモデルをExcel関数で呼び出す方法」です。
Contents
今回やりたいこと
今回やりたいことはこんな感じです。
会計システムから月次推移データを取り出しそのデータをもとに報告書を作るのですが、構成比率を計算したり利益計算するのでピボットテーブルは使わずにExcelの関数を使って呼び出したい、そんなケースです。
事前準備
まず、会計システムから取得した月次推移データをパワークエリで読み込み、分類集計できるように変換します。
これで、月次推移データをピボット解除してデータベース形式のテーブルに変換します。
ここまでは簡単ですね。
データタブ「取得と変換」から読込み、項目以外の列をピボット解除、それだけです。
ここからが今日の本題です。
Excel関数でデータモデルを呼び出す方法
クエリの読み込み方
まずはクエリを読み込みからです。
クエリ編集画面のホームタブから「閉じて次に読み込む」を選択します。
そしてピボットテーブルレポートを選択し、「このデータをデータモデルに追加する」にチェックを入れます。
ピボットテーブルの展開
クエリ編集画面が閉じられ、ピボットテーブルのフィールドが展開されますので、作りたい表の近い形にレポートを作成します。
ここで行と列の総計は使いませんので、デザインタブ「総計」ボタンより集計を行わないようにします。
ピボットテーブルレポートの数式化
ここでピボットテーブルを数式に変換します。
ピボットテーブルの分析タブよりOLAPツールを選択し、「数式に変換」を選択します。
するとこのようにピボットテーブルが解除され、数式が入ったことが分かります。
CUBE関数の解説
ここで、数式を確認しておきましょう。
まず、科目と月についてです。
科目と月については、CUBEMEMBER関数が使われています。
これが、ピボットテーブルにおける行・列の項目名を呼び出す関数です。
そして合計値を呼び出す関数がこちら。
このように、CUBEVALUE関数が使われています。
ちなみに、この項目をむやみに変えないでください。
例えば「1月」を手入力すると1月の金額がエラーになりました。
回避する方法はあるのですが、ここでは触らない方がいいでしょう。
触らぬ神にたたりなしです。
次にCUBEVALUE関数ですが、A1を参照しています。
一方でこの1列目は使わないので消したい、これをどうするかです。
ここでやっていることは、集計方法の指定です。
ピボットテーブルフィールドでいえば右下の箱を指定しているようなものですね。
そして、行項目と列項目を指定しているのが「$A3」、「B$2」となります。
ここの置き換えについて、重要なのでもう一度確認しておきましょう。
A1のCUBEMEMBER関数とCUBEVALUE関数の関係はこんな感じです。
そして、置き換えた後のCUBEVALUE関数はこの通りです。
これができたら他のセルもこの数式に置き換えます。
ちなみに、この集計方法を書き換える前に1行目を消すとこうなります。
ここまでで作成したデータモデルをピボットテーブルを使ってエクセルに書き込み、さらにそれを数式化する方法を書きました。
ここまでできれば、あとはこれを編集するだけです。
報告書の作成
数式化したものをベースに科目を並び替え、段階損益行と構成比列を追加し、追加した列に色を付けるとこんな感じになりました。
ここで気を付けるのは、CUBEVALUE関数の参照元との関係が切れないように気を付けるだけです。
カットアンドペーストなどで参照セルがずれないように気を付けましょう。
項目を参照する方法
さて、先ほど項目名を手入力するとエラーになると書きました。
では、他のセルに項目名を入力し、それを参照させることはできないのでしょうか。
できます。
こんな感じに、A列・1行目に入力した内容を参照するようにするにはどうしたらいいでしょうか。
項目名の数式を見れば分かるのですが、この「売上高」とあるところをA列参照に変えればいいだけです。
具体的にはこう書きます。
売上高をA4と書き換えるのですが、もともとこの員数はダブルクォーテーションで囲まれています。
ですので単純に置き換えるだけではだめで、A4の前後でダブルクオーテーションを閉じ、またダブルクオーテーションでテキスト化する必要があります。
ですので、書き方としてはこうなります。
・・・[売上高]” → ・・・[“&A4&”]”
このようになります。
月名も同じ要領で書き換えることで1行目を参照するように変更できます。
これで、A列や1列目に項目名を参照させることができました。
以上がパワークエリを使って作ったデータモデルを数式で呼び出し、自由に編集する方法でした。
CUBE関数を直接書けばいいのかもしれませんが、要所要所でダブルクオーテーションが必要なことやいったんピボットテーブルを作成しないと使えないなど、クセが強い関数ですので自分で書こうとせずにOLAPツールから数式化して使うなど、極力入力しなくて済むような使い方がいいかもしれませんね。
最後に
いかがだったでしょうか。
ここまで書いておいて、最後の最後にちゃぶ台返ししますね。
SUMIFS関数で十分OK!
そう、こんな方法を使わなくても、作ったクエリを適当なシートに張り付けてSUMIFS関数で処理すれば全く同じことが出来るのです。
とはいえ、こんなこともできるって知っていればいつか使えるかもねってことで書いてみました。
最後まで読んでもらったのにすみませんでしたぁ！！笑
