「育休中における夫婦年収ごとの世帯手取りをヒートマップで見たい。」
一見すると単純な集計に見えますが、実務では以下のようなやり取りになることが結構あります。
夫婦それぞれの年収を軸にして、育休中の世帯の手取りをヒートマップで見たいな。まずは100万円刻みで作ってみて。
わかりました。Excelで作ってみます。
（数時間後）
できました！
ありがとう。でも少し大雑把だね。50万円刻みにしてもらえる？
……はい。
（さらに数時間後）
いい感じ。ただ、やっぱり10万円刻みの方が境界が見えそうだね。
……。
Excelは一方向の集計や計算には非常に強力です。
しかし、「縦×横」の組み合わせを大量に作る分析になると、一気に作業量が増えてしまいます。
さらに困るのは、粒度の変更だけではありません。
例えば保育料のように、「夫婦それぞれの年収を計算した結果を合算し、その値からさらに別の値を求める」ようなクロス集計では…
「この組み合わせはどの保育料区分だったかな？」
と表を目で追いながら数式を作ることになり、分析よりも表作りにかける時間の方が長くなってしまいます。またミスも増えます。
実は、私が公開した育児休業給付金シミュレーションの記事も、最初はExcelで試作しました。
しかしやりにくさやミスが増えたため、途中でPower Queryへ切り替えたところ、
と分析内容をどんどん増やしても、モデルを作り直すことなく対応できました。
今回は、その時に実際に使った方法を一般化し、
Power Queryでデータを作り、Power BIでヒートマップを作る方法
をご紹介します。
完成イメージ
今回作成するのはこちらです。
※本記事では、行と列に2つのパラメータを配置したマトリクスを条件付き書式で色付けしたヒートマップを、便宜上「クロスヒートマップ」と呼びます。
縦軸・横軸に自由なパラメータを配置し、交点となる値を色で表現します。
今回の例では
- 縦軸：（育休前の）夫の年収
- 横軸：（育休前の）妻の年収
- 色：育休中の世帯手取り
ですが、この方法は
- 売上 × 利益率
- 気温 × 湿度
- 商品 × 地域
- 設備 × 生産速度
など様々な分析へも応用可能です。
全体の流れ
作業は次の6ステップです。
- List関数でパラメータを作る
- 一人分（クロス結合前）の計算列を追加する
- クエリをコピーする
- クロス結合する
- クロス結合後の計算列を追加する
- マトリクス＋条件付き書式でヒートマップ化する
一つずつ見ていきます。
① List関数でパラメータを作る
まずは縦軸となるパラメータを作ります。
今回は年収100万円〜1,500万円を50万円刻みで作成しました。
Power QueryのList.Numbers（開始値, 個数, 刻み幅）を使うと連続した値を生成できます。
ここで注意が必要なのは、第2引数が「終了値」ではなく「生成する個数」だという点です。今回のように「100万円から1,500万円まで」という感覚で指定したい場合、そのままだと毎回
(終了値 - 開始値) ÷ 刻み幅 + 1
を手計算する必要があり、範囲や刻みを変えるたびに計算し直すのは面倒です。そこで、開始値・終了値・刻み幅を変数にして、個数はその変数から自動計算させる形にしておきます。
let
開始値 = 100,
終了値 = 1500,
刻み幅 = 50,
個数 = Number.RoundUp((終了値 - 開始値) / 刻み幅) + 1,
Source=List.Numbers(開始値,個数,刻み幅),
Table=Table.FromList(Source, Splitter.SplitByNothing(), {"年収(万円)"})
in
Tableこうしておけば、範囲や粒度を変えたい時は「開始値」「終了値」「刻み幅」の3つの数字を書き換えるだけで済み、個数を毎回手計算する必要がなくなります。
具体的には、Power Queryを開き、
「クエリ」エリアを右クリック
「新しいクエリ」→「空のクエリ」と進み
上記コードを下の画像のように貼り付けるだけです。
このように設定すると、以下の一覧表が完成します。
Excelなら入力やコピーなど煩雑な作業が必要ですが、
Power Queryで変数として設定しておけば「100万円刻み→50万円刻み→10万円刻み」と変更しても「刻み幅」を書き換えるだけで済みます。
② 一人分（クロス結合前）の計算列を追加する
続いて、年収から必要な項目を計算していきます。
例えば
- 月給
- 通常手取り
- 育休手取り
- 住民税
などです。
ここは通常のPower Query操作です。後でクロス結合するため、一人分の計算を完成させておきます。
③ クエリをコピーする
ここからが今回操作の重要ポイントです。
まずは、完成したクエリをコピーします。
今回は、夫年収を「複製」し、妻年収としました。
④ クロス結合する
片方のクエリへ
カスタム列
を追加します。そして、
= 妻年収
と入力します。
これを展開すると…
夫年収 × 妻年収となるすべての組み合わせが完成します。
つまり、「夫100万円×妻100万円」「夫100万円×妻110万円」…というように、すべての組み合わせが自動生成されます。
100万円から1500万円まで10万円刻みなら
141 × 141
約2万件の組み合わせが、自動生成される、ということです。
⑤ クロス結合後の計算列を追加する
クロス結合後は自由に計算列を追加できます。例えば
- 夫手取り＋妻手取り → 世帯手取り
- 夫住民税＋妻住民税 → 住民税合算
さらに
- 保育料
- 可処分所得
- 育休割合
など、分析したい指標を追加できます。
今回の育休シミュレーションでは、ここに保育料計算まで追加しました。
前回の記事で「別記事で扱う」としていた保育料も、この構造に計算列を追加するだけで計算できました。
Excelでは「この住民税合算はいくらだから保育料はこの区分…」と目で確認していましたが、Power Queryでは一度条件列を作れば、すべて自動計算されます。
⑥ マトリクスでヒートマップ化する
最後はPower BIです。
マトリクスを配置し、
- 行：夫年収
- 列：妻年収
- 値：世帯手取り
を設定します。
さらに
条件付き書式 → 背景色
を設定すると…
ヒートマップの完成です。色の変化を見るだけで、数値だけでは気付きにくい
- 壁
- 崖
- 境界
が見えるようになります。
この方法の一番のメリット
一番のメリットは、ヒートマップではなく、分析条件を簡単に変更できることです。
例えば上司から
「やっぱり100万円刻みじゃなくて50万円刻みで。」
と言われたら、Excelなら作り直しで膨大な時間がかかり、ミスも起きやすいです。
一方で、今回のモデルでは、夫年収と妻年収それぞれの「刻み幅」を変更して更新するだけです。例えば10万円刻みから50万円刻みに変更する場合も、夫・妻それぞれのパラメータの刻み幅を変更するだけです。
個数は自動で再計算され、クロス結合、計算列、ヒートマップまで連動して更新されます。
つまり、分析条件が変わっても「作り直す」のではなく「パラメータを変更して更新する」だけになるのが、Power Queryでモデル化するメリットです。
応用例
この方法は今回のような年収計算だけではなく、「2つのパラメータの組み合わせを可視化したい」という分析なら、ほぼ応用できます。軸2つを決めて、その組み合わせから何を色として表示するか（値）を考えヒートマップを作れば簡単に可視化できます。
例えば…
| 縦軸 × 横軸 | 値（色）の候補 |
|---|---|
| 売上 × 利益率 | 営業利益額、案件の優先度スコア |
| 商品 × 地域 | 売上高、在庫回転率 |
| 気温 × 湿度 | 体感温度（WBGT指数）、熱中症リスクレベル |
| 年齢 × 所得 | 保険料率、与信スコア |
| 設備番号 × 生産速度 | 不良率、稼働率 |
| 温度 × 圧力 | 反応収率、物質の相（気体・液体・固体） |
特に「温度×圧力→物質の相」は、研究や製造の現場で使われる相図（phase diagram）とまったく同じ構造です。また、「設備番号×生産速度→不良率」のように、どの設備をどの速度で動かすと不良が増えるかが一目で分かる形にできると、製造現場での意思決定にもそのまま使えます。
私は今回、育児休業給付金の分析をきっかけにこの方法を使いましたが、実務でも十分使えるテクニックだと感じています。
まとめ
Power BIでクロスヒートマップを作る手順はシンプルです。
- List関数でパラメータを作る
- 一人分（クロス結合前）の計算列を追加する
- クエリをコピーする
- クロス結合する
- クロス結合後の計算列を追加する
- マトリクス＋条件付き書式で表示する
この方法の価値は、ヒートマップが作れることだけではありません。
分析の途中で
となっても、モデルを作り直す必要がないことです。
Excelでは「表を作ること」に時間を使いがちでした。Power Queryでは最初にデータモデルを作ることで、その後は「何を分析するか」を考える時間に集中できます。クロス集計やシミュレーションを繰り返す業務ほど、その効果を実感できるはずです。
Excel作業で苦しんでいる方は、一度Power Queryでモデル化してみてはいかがでしょうか？
コメント