Excelで温度分布を視覚化してみよう｜T&D ラボ

1.はじめに

ある特定範囲の温度分布などを視覚的に把握する手法に、値を色分けして表示する方法があります。
例えば、気象庁が公開している日本全体の温度分布は、高温は赤、低温は青、中間の温度は黄色というように色分けされています。

気象庁｜最新の気象データ (jma.go.jp)

今回、おんどとりで測定した室内の温度分布も色分けして表示させてみました。
このように分布データを色分けして表現する手法をヒートマップと呼ぶそうなので、この記事の中でもヒートマップと呼んでいます。

[図1]は、オフィスフロアの温度分布です。フロア内を9×9のマス目に区切り、高温は赤、低温は青、中間は黄色で表しています。太線の四角形はデスクなど設備の配置を示しています。

このようなヒートマップをどうやって作成したのかを、以下で紹介していきたいと思います。

2.動作概要

ヒートマップは下記の手順で作成しました。

1.ヒートマップの元となる温度を測定する
2.温度の測定値のデータを一元的にまとめる
3.まとめたデータからヒートマップを作成する

1.ヒートマップの元となる温度を測定する

ヒートマップを作成するエリアは、オフィスの四角い空間としました。
その空間に[図2]のように温度計を9個配置し、各場所の温度を測定しました。

温度測定にはRTR500BシリーズのRTR502Bを使用しました。
※RTR500Bシリーズについてはこちらをご覧ください。

TR7Aシリーズを使ってもヒートマップを実現することができます。
※TR7シリーズについてはこちらをご覧ください

2. 温度の測定値のデータを一元的にまとめる

9カ所の温度は、RTR500Bシリーズの親機(RTR500BWなど)によりおんどとり Web Storage に送信されます。
おんどとり Web Storageは、温度などの測定値を保存し閲覧できるクラウドサービスで、保存されているデータを外部のシステムからも活用可能にするおんどとり Web Storage API(以下、公開APIと記述)も公開されています。
おんどとり Web Storageについては、「3. おんどとり Web Storage について」の章で改めて説明します。

3. まとめたデータからヒートマップを作成する

今回はExcelのマクロによりおんどとり Web Storage の公開APIを使って温度の測定値を取得し、Excelのシート上にヒートマップを作画します。
Excelのマクロは、VBA(Visual Basic for Applications)というプログラミング言語で記述します。VBAはExcelがあれば他に特別なツールなど不要で手軽にプログラム開発することができるとても便利なツールです。

1～3で説明した全体の動作を[図3]に示します。

ヒートマップを作成するためのExcelファイルについては「4. Excelファイルについて」で、Excelのマクロの内容については「5. VBA(Visual Basic for Applications)のマクロについて」で説明しています。

3.おんどとり Web Storageについて

3-1. おんどとり Web Storageとは

おんどとり Web Storageは、T&Dのデータロガー「おんどとり」シリーズの測定データを保存し、グラフ表示によるデータの閲覧、機器の設定、アラート通知、データのダウンロードなどの機能を提供するサービスです。

更に、公開APIを使用することで現在値やグラフデータを取得することが可能です。

おんどとり Web Storageは、アカウントを作成するだけで無料で利用できます。
このサービスに対応しているデータロガーについては https://ondotori.webstorage.jp の「シリーズ別対応機器一覧」に記載されています。

3-2. アカウントの作成方法

アカウントを作るには、まず、おんどとり Web Storage(https://ondotori.webstorage.jp)にアクセスし、[新規ユーザ登録する]を押します。（既にアカウントを取得済みの場合は[ログインする]ボタンを押してログインし、「3-3. 公開APIの使い方」へ進んでください）
メールアドレスとパスワードを設定し、「ご利用規約及びプライバシーポリシーに同意」にチェックを入れた後、[送信する]ボタンを押すと、入力したメールアドレスにおんどとり Web Storageから確認メールが届きます。メール文中に記載されているURLをクリックすることでユーザ登録が完了します。

メールに記載されているユーザID(txxx####: xは小文字アルファベット、#は数字)とパスワードはExcelファイルの初期設定シートに記述するので控えておいてください。

3-3. 公開APIの使い方

おんどとり Web Storageにログインし、メニューから[アカウント管理]を選択し、[開発者向けAPI管理]ボタンを押してください。

開発者向けAPI管理画面から下記の手順でAPI KEYが発行されます。

1.[API KEYを発行する]ボタンを押す。
2.[API KEY]を発行しましたと表示される。
3.[アカウント管理へ戻る]ボタンを押す。
4.[開発者向けAPI管理]ボタンを押す。
5.利用可能なAPI KEY: xxxxxxxxxxxxxxxxxxxxxx と表示される。

このAPI KEYは、Excelファイルの初期設定シートに記述するので控えておいてください。
公開APIには、おんどとり Web Storageに登録されている機器の最新の現在値を一括して取得する「現在値の取得」と、1台の機器を指定して一定期間のグラフデータを取得するコマンドなどが用意されています。

今回のマクロでは「現在値の取得」の公開APIを使用します。
https://ondotori.webstorage.jp/docs/api/reference/devices_device.html

公開API全般については下記を参照してください。
https://ondotori.webstorage.jp/docs/api/index.html

4.Excelファイルについて

4-1. ダウンロードと使い方

「2. 動作概要」で説明した通り、今回のヒートマップはExcelで作成します。
ヒートマップを作成するExcelのファイルは下記からダウンロードできますので、まずはダウンロードしてみてください。

ダウンロード

今回のExcelファイルではVBA-JSON(JsonConverter)というライブラリを使用しています。
おんどとり Web Storageの公開APIはデータの送受信にJSON形式を使用しており、JSON形式のエンコード及びデコードの為に使用しています。
https://github.com/VBA-tools/VBA-JSON

使い方の流れは下記の通りです。

1.おんどとり Web Storageのアカウントを作成し、温度データをアップロードする設定をする
2.「WSS_ヒートマップ.xlsm」をExcelで開く
3.[初期設定]シートに必要事項を入力する
4.[現在値データ]シートの[開始する]ボタンをクリック
5.[ヒートマップ]シートを開くとヒートマップが作成されている
6.[ヒートマップ]シートの[履歴再生]ボタンをクリックすると、過去24時間の推移が再生される

Excelファイルを開いた時に「セキュリティリスク　このファイルのソースが信頼できないため、Microsoftによりマクロの実行がブロックされました。」と表示される場合は、以下の操作をお試しください。
ファイルを右クリック→[プロパティ]→[全般]タブ「セキュリティ」の「許可する」にチェック→[OK] を選択

この後、Excelで使用している各シートについて説明します。

4-2. 初期設定シート

ヒートマップを作成するための初期設定値を入力します。
設定する内容は2つあります。

一つ目は、おんどとり Web Storage の公開APIを使用するためのアカウント情報などで、具体的には下記の3項目です。

api-key	おんどとり Web Storage のAPI KEY (3-3. 公開APIの使い方参照)
login-id	おんどとり Web Storage のユーザID (3-2. アカウントの作成方法参照)
login-pass	おんどとり Web Storage のログインパスワード (3-2. アカウントの作成方法参照)

二つ目は、9カ所の温度計の配置を指定します。
温度計は、「2. 動作概要」でも説明した通り、[図4]のような配置を前提としています。
この時の、[HM_1_1〜HM_3_3]に配置する9個の温度計のシリアルNo.を入力します。

※シリアルNo.は、数字及び大文字アルファベットA～Fで構成された8桁の番号です。

4-3. 現在値データシート

公開APIから取得した温度測定値が記入されていくシートです。
一行目の右側にボタンがあり、起動時は[開始する]と表示されていますが、クリックすると[稼働中]に変わります。
[稼働中]の場合、30分毎に公開APIから取得した値がシートの末尾に追記されていきます。

※　[開始する]をクリックする前に、必ず「4-2. 初期設定シート」の設定を行ってください。
※　[稼働中]の場合、Windowsの電源設定の「デバイスをスリープ状態にする」設定を「なし」にしてください。

4-4. ヒートマップシート

ヒートマップを表示するシートです。
実際の測定値が入力されるのは四隅・四辺中央・マップ中央の合計9点で、間のセルは線形補完で埋めています。
補完したセルも含めて、色で温度分布が分かるようになっています。使用時の温度範囲に合わせて右下の「色最小値」「色最大値」を調整することで、ヒートマップの色を調整可能です。

マップのセル幅や高さは、配置した空間の形状に合わせて自由に変更できます。
また、ヒートマップが表示されているセルに重ねて、Excelの機能の挿入→図→図形で測定空間の設備などを書いています。こちらも自由に変更可能です。

一行目の右側に[履歴再生]ボタンがあります。
このボタンをクリックすると、[現在値データ]シートの直近の48個の古いデータからヒートマップを作成していきます。
継続して30分間隔で測定値を取得している場合、過去一日分のヒートマップの推移を見ることができます。

4-5. optionsシート

ヒートマップを見た時に、分割が粗すぎるなど、解像度が思った通りでないケースがあるかもしれません。そこで、データ間の補完セル数を調整できるようにしました。
optionsシートの「行解像度」セルの値を変更し、＜解像度変更手順＞を踏むことで、この分割数を変えられるようにしてあります。

本ファイルでは、機能を分かりやすく限定する為に、行数と列数は同じ前提で「行解像度」のセルの値だけが指定可能になっています。(図5)
応用として、行数と列数が異なる場合(例えば行数4・列数6など)でも、＜解像度変更手順＞の手順で対応できると思います。

5. VBA (Visual Basic for Application)のマクロについて

5-1. ソースリスト

本章ではExcelファイルに含まれるマクロを説明していきます。

Excelでファイルを開き、メニューから[開発]→[Visual Basic]を選択すると[Visual Basic for Application](VBA)が起動します。
その中の[標準モジュール]→[Module 1]に今回使用したマクロのほとんどが記載されています。
マクロのソースを[リスト1]に示しました。

リスト1

ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Dim EvntTim As Variant 'イベントの起動時刻を保存する

Sub Get_WSS_Data()
    
    Dim cnt As Integer
    Dim api_url As String                                       '公開APIのアドレスを保存する変数宣言
    api_url = "https://api.webstorage.jp/v1/devices/current"    '公開APIのアドレス
    
    Dim seri_tbl(8) As String
    Dim temp_tbl(8) As String
    Dim d_date As String
    
    '##### 初期設定シートの公開APIのリクエスト条件を読み出し、リクエストで送信する変数に設定にする #####
    Dim api_key As String
    Dim login_id As String
    Dim login_pass As String
    api_key = Worksheets("初期設定").Cells(2, 2)                '初期設定シートのapi-keyを読みだす
    login_id = Worksheets("初期設定").Cells(3, 2)               '初期設定シートのlogin-idを読みだす
    login_pass = Worksheets("初期設定").Cells(4, 2)             '初期設定シートのlogin-passを読みだす
    
    Dim payload As New Dictionary                               'WebAPIのBodyで送信する情報を作る変数宣言
    payload.Add "api-key", api_key
    payload.Add "login-id", login_id
    payload.Add "login-pass", login_pass


    '##### 初期設定シートから測定対象機器のシリアルNo.のリスト読み出し #####
    For cnt = 0 To 8
        seri_tbl(cnt) = Worksheets("初期設定").Cells(cnt + 8, 2)
    Next cnt


    '##### 公開APIにリクエスト送信し、そのレスポンスを受信する #####
    Dim objHTTP As Object                                       'HTTP通信をする為のオブジェクト作成
    Set objHTTP = CreateObject("msxml2.xmlhttp")                'HTTP通信をする為のオブジェクト作成
    objHTTP.Open "POST", api_url, False                         '公開APIをPOSTでリクエストする設定
    objHTTP.setRequestHeader "X-HTTP-Method-Override", "GET"    '公開APIリクエストのヘッダを設定する
    objHTTP.setRequestHeader "Content-Type", "application/json" '公開APIリクエストのヘッダを設定する
    objHTTP.send JsonConverter.ConvertToJson(payload)           '公開APIリクエストのBodyにpayload(JSONに変換して)を送信する
    
    If objHTTP.Status <> 200 Then                               'Statusが200以外なら失敗している
        Debug.Print JsonConverter.ConvertToJson(objHTTP.Status)
        Debug.Print objHTTP.responseText
        MsgBox "公開APIの実行に失敗しました"             '失敗のダイアログを表示
        End                                             '公開APIの実行に失敗したら終了
    End If
    
    '##### 以下はWebAPIのリクエストとレスポンス受信が成功した時の処理 #####
    Dim lm_remaining
    On Error Resume Next
    lm_remaining = objHTTP.getResponseHeader("x-ratelimit-remaining")   '公開APIレスポンスの"x-ratelimit-remaining"の値を保存
    Dim Device As New Dictionary
    Dim Parse As Object                                                 '取得できた全機器のJSONデータを入れるオブジェクト作成
    Set Parse = JsonConverter.ParseJson(objHTTP.responseText)           'Parseに全機器のJSONデータを入れる
    Dim D_Parse As Object                                               '機器一台分のJSONデータを入れる変数宣言
    
    disp_row = Worksheets("現在値データ").Cells(Rows.Count, 1).End(xlUp).Row + 1
    For Each Device In Parse("devices")                         '登録機器の台数分繰り返す
        Debug.Print JsonConverter.ConvertToJson(Device)
        ttt = JsonConverter.ConvertToJson(Device)               '機器一台分のデータを抜き出してttt→D_Parseと入れる
        Set D_Parse = JsonConverter.ParseJson(ttt)              'D_Parseに機器一台分のデータを入れる
            
        '##### JSONデータから対象機器のシリアルNo.を検索し測定値を取得する #####
        For cnt = 0 To 8
            If seri_tbl(cnt) = D_Parse("serial") Then
                temp_tbl(cnt) = D_Parse("channel")(1)("value")
            End If
        Next cnt
    Next


    d_date = Now   '測定値を取得した日時
    Worksheets("現在値データ").Cells(disp_row, 1) = d_date
    For cnt = 0 To 8
        Worksheets("現在値データ").Cells(disp_row, cnt + 2) = temp_tbl(cnt)
    Next cnt

    For cnt = 0 To 9    '最新の現在値データをヒートマップにコピー
        data_buf = Worksheets("現在値データ").Cells(disp_row, cnt + 1)
        If (data_buf <> "Communication Error") And (data_buf <> "Channel Busy") Then
            Worksheets("ヒートマップ").Cells(16, cnt + 12) = Worksheets("現在値データ").Cells(disp_row, cnt + 1)
        End If
    Next cnt


End Sub

'##### 次に測定値を取得するスケジュールを設定する
Sub ScheduleSetting()
    If Sheet2.Moniter_start.Caption = "稼働中" Then
        EvntTim = Now + TimeValue("00:30:00")
        Application.OnTime EvntTim, "ScheduleSetting"
        Call Get_WSS_Data
    Else
        If EvntTim <> 0 Then Application.OnTime EvntTim, "ScheduleSetting", , False
    End If


End Sub

'##### ヒートマップの履歴を再生する
Sub PlayHeatMap()
    Dim cnt
    Dim my_row
    Dim data_buf

    end_row = Worksheets("現在値データ").Cells(Rows.Count, 1).End(xlUp).Row '最終行No.取得

    If end_row > 51 Then
        my_row = end_row - 48
    Else
        my_row = 3
    End If

    Do
        For cnt = 0 To 9
            data_buf = Worksheets("現在値データ").Cells(my_row, cnt + 1)
            If (data_buf <> "Communication Error") And (data_buf <> "Channel Busy") Then
                Worksheets("ヒートマップ").Cells(16, cnt + 12) = Worksheets("現在値データ").Cells(my_row, cnt + 1)
            End If
        Next cnt
        my_row = my_row + 1
        Application.Wait [Now() + "00:00:00.3"]     '300msecウェイト
    Loop While (my_row <= end_row)
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー

[リスト1]のModule 1は、Get_WSS_Data()、ScheduleSetting()、PlayHeatMap() という3個のブロック(関数と言います)で構成されています。
以下で、それぞれについて説明します。

5-2. Get_WSS_Data について

この部分は、「おんどとり Web Storageの公開APIにより取得した測定値を[現在値データ]シートに記入し、最新の測定値だけ[ヒートマップ]シートにもコピーする」という処理を行っています。
全体のフローチャートは[図6]を参照してください。
公開APIにアクセスするためのログイン情報を[初期設定]シートから取得するので、使用する前に必ず[初期設定]シートを編集しておく必要があります。

測定値を[ヒートマップ]シートにコピーすることで、Excelの機能を使い、測定値によってセル色が変わるようにしています。

5-3. ScheduleSetting について

[現在値データ]シートの[開始する]ボタンをクリックした時に実行される関数で、この中では次の2つの処理を行っています。

1.30分後に再びScheduleSettingが実行されるタイマーを起動する。
2.Get_WSS_Dataを実行する。

上記2つの処理により、30分毎に自動的にGet_WSS_Dataが実行され、公開APIにより測定値を取得します。

5-4. PlayHeatmap について

[ヒートマップ]シートの[履歴再生]ボタンをクリックすると実行される関数です。

[現在値データ]シートに記録されている測定値を、過去から最新まで48回分(24時間分)読み出し、順番にヒートマップを表示させます。
過去24時間の推移を見ることができます。

6.最後に

今回は、おんどとり Web StorageのデータをExcelで活用しました。
おんどとり Web Storageのデータは、ブラウザや弊社のアプリで閲覧できるだけでなく、公開APIを介して別のシステムから取得することも可能です。

記事の内容を参考に自由な発想でおんどとりやおんどとり Web Storageを今以上に便利に使っていただくきっかけとなれば嬉しいです。

Excelで温度分布を視覚化してみよう