気温の変化をヒートマップで可視化する_エクセル

今年も35℃を超える猛暑日が増えてきましたね。
毎年猛暑、酷暑と言っていますが、過去からどれくらい気温の変化があるのかをヒートマップで可視化されている下記ブログが面白かったので、エクセル（パワークエリ、パワーピボット）でまねしてみました。

https://note.com/kazukio/n/n8b25774258a7


# データの取得
気象庁のページから過去データを取得しています。
https://www.data.jma.go.jp/risk/obsdl/index.php
今回取得したデータは、「大阪府」「日別値」「日平均気温、日最高気温
、日最低気温」の条件です。
一度でのデータ取得上限があるので、期間を調整しつつcsvダウンロードします。
csvを一つのフォルダへまとめておきます。

# エクセルでヒートマップ
## パワークエリでcsv読込み
**エクセル2019での内容です。**
- 「データ」-「データの取得」-「ファイルから」-「フォルダーから」で、csvを保存した場所を選択します
- 「データの結合と変換」で、「OK」
- これで複数のcsvファイルを一つにまとめてパワークエリで取得できました
- この内容はシートに書き出す必要は無いので、「閉じて次に読み込む」で「接続の作成のみ」と「このデータをデータモデルに追加する」にチェックして「OK」
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2383487/5e1e52f1-2839-6b0c-5590-3ccfd7dea6d0.png)

## データの変換
パワークエリのエディターでの作業
- ダウンロードしたcsvのままでは不要な情報があるので削除する
- 以下は結合されたソースではなく、「ヘルパークエリ」-「サンプルファイルの変換」で作業する
- 下記はGUI操作で生成されたソースそのままです（詳細エディターの内容）

```:サンプルファイルの変換
let
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=10, Encoding=932, QuoteStyle=QuoteStyle.None]),
    削除された他の列 = Table.SelectColumns(ソース,{"Column1", "Column2", "Column5", "Column8"}),
    削除された最初の行 = Table.Skip(削除された他の列,6),
    #"名前が変更された列 " = Table.RenameColumns(削除された最初の行,{{"Column1", "日付"}, {"Column2", "平均気温"}, {"Column5", "最高気温"}, {"Column8", "最低気温"}})
in
    #"名前が変更された列 "
```

- 上記の変更をすると、結合したソースの方でエラーになります
- 列名を変えたり、列削除したりしたのでエラーになっています
- エラーになっている「変更された型」を削除して、適当な型へ手動で変更する
- `"C:\Users\xxx保管場所xxx\過去の天気_気象\data"`はcsvを保存しているフォルダのこと
```:data
let
    ソース = Folder.Files("C:\Users\xxx保管場所xxx\過去の天気_気象\data"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    削除された列 = Table.RemoveColumns(展開されたテーブル列1,{"Source.Name"}),
    変更された型 = Table.TransformColumnTypes(削除された列,{{"日付", type date}, {"平均気温", type number}, {"最高気温", type number}, {"最低気温", type number}})
in
    変更された型
```

- これで複数csvをまとめた気温の一覧ができました

## 日付テーブルをつくる
- 日付テーブルを別でつくることにします
- 「データ」-「データモデルの管理」を開く
- 「デザイン」-「日付テーブル」-「新規作成」で日付テーブルができる
- 「リレーションシップの作成」で、気温データの日付と日付テーブルのリレーションを設定する
- 日付テーブルに「日」だけを取り出した列を追加する
  - 列の追加で`=FORMAT([Date],"d")`として「データ型」を「整数」に設定する

## シート上に表示する
パワーピボットでの内容です。
- 「挿入」-「ピボットテーブル」-「データモデルから」で「OK」
- 「ピボットテーブルのフィールド」を設定する
  - 列：MonthNumber、dayNo
  - 行：Year
  - Σ値：合計/平均気温　　⇒最終的に：fx区分
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2383487/f7aeb221-d89c-af77-5f23-fdb3b4c82d91.png)


## ピボットテーブルの見た目を設定
- 小計/総計とかは不要なので非表示にする
- 「ホーム」-「条件付き書式」で「新しいルール」をつくる
  - ルールの適用対象："Year"と"dayNo"の"fx区分"値が表示されているすべてのセル
  - ルールの種類：セルの値に基づいてすべてのセルを書式設定
  - ルールの内容を編集：書式スタイル：3色スケールで青（最小値）、黄（百分位_50）、赤（最大値）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2383487/c094254f-a663-191a-6602-b82cbbbf6021.png)

## おかしな値
- おかしな値がでている
  - csvで重複した日付データがあったようで合計されていた
  - パワークエリの方で、「重複の削除」を追加しておく

## 月スライサー
- 「挿入」-「スライサー」で「MonthNumber」を追加
  - これで6,7,8,9月だけにフィルターしたり変える
  - 色のグラデーションはフィルターされた内容での「最小～百分位_50～最大」となるのでいい感じになってくれる
- スライサーで切り替えると値の幅で自動調整されるのがイヤな時は、「ピボットテーブルオプション」から「更新時に列幅を自動調整する」のチェックを外す

## 集計切り替えスライサーの追加
- 平均気温、最高気温、最低気温をスライサーで切り替えられるようにする
- 「空のクエリ」を追加して以下を詳細エディターで入力
```:区分
let
    ソース = #table({"区分"}, {{"平均気温"}, {"最高気温"}, {"最低気温"}})
in
    ソース
```
  - 「接続先の作成のみ」と「このデータをデータモデルに追加する」にチェックして「OK」
  - このテーブルにはリレーションシップは付けないで、独立したテーブルのままにします
  - それぞれ集計用のメジャーを作っておきます

```:dax
メジャー名：fx平均気温
  =MAX('data'[平均気温])
メジャー名：fx最高気温
  =MAX('data'[最高気温])
メジャー名：fx最低気温
  =MAX('data'[最低気温])
```

- スライサーの選択で上記のメジャーを切り替えるものを作ります

```:dax
メジャー名：fx区分
=IF(
COUNTROWS(VALUES('区分'[区分])) = 1,
SWITCH(
VALUES('区分'[区分]),
"平均気温", 'data'[fx平均気温],
"最高気温", 'data'[fx最高気温],
"最低気温", 'data'[fx最低気温]
)
)
```

- `fx区分`のみをΣ値へ入れます
- 「挿入」-「スライサー」で「区分」を表示する
- 区分スライサーを変更する事で内容も変更されます！
- 使用するメジャーを変えたので、条件付き書式が無くなるので付けなおす


# 完成したヒートマップ
データ量も多くて、条件付き書式を設定しているので、とても重くなります。
エクセルでやる事ではないですね。
- 平均気温 6-9月
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2383487/93a9a5ea-7d44-3e25-f648-15eeecd781b3.png)

- 範囲 （最高気温 - 最低気温） 7-8月
- 青色：一日での差が小さい　赤色：一日での差が大きい
- 昔より、一日の中での気温差が小さくなっていると見えなくもない？
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2383487/b997abdb-1108-1323-cb47-f08c35df121a.png)

- という2つのヒートマップより、平均気温が上がっていて、暑いままの日が多い

## 可視化の集計方法
行を年で、列を月/日で表すというのがおもしろい方法で知れてよかった！
これまでは日付の場合、ただ横とか縦に伸びていく表現方法しか考えた事なかった。


# 参考
- [Power Queryのヘルパークエリは良いやつだし、カスタム関数はデバッグできる](https://qiita.com/k_maki/items/cab7a340ffc717ef1a4d)
- [スター スキーマと Power BI での重要性を理解する](https://learn.microsoft.com/ja-jp/power-bi/guidance/star-schema?WT.mc_id=DP-MVP-5002429)
- [選択肢テーブルによる集計の切り替え](https://modernexcel7.hatenablog.com/entry/ParameterTable)