ごみステーションをIoT化してみたの続き「データ分析編」です。今回は、SORACOM LTE-M Button Plusから出力されるデータと以下のExcel関数を使い編集していきます。
使用関数：CEILING.MATH（数値[,基準値][,モード]）,COUNTIF(範囲,検索条件),TEXT(値,表示形式)
特に「CEILING.MATH」はあまり使わない関数に分類していたため、忘備録としてまとめてあります。
最初に
SORACOM Harvestにデータを蓄積するとコンソール上簡易グラフが確認できます。さらに見やすくしたいとデータ自体をCSV形式でダウンロード後、Excelを使用しグラフ化することもできます。組み合わせとして温・湿度＋時間や振動＋時間、距離＋時間などのデータはそのまま使用できます。しかしLTE-M Buttonの場合は、「__time」「batteryLevel」「clickType」になり、そのままデータ使用するとわかりにくいグラフになります。この現象を回避するためデータをある程度の時間帯でまとめ、分布が取れる状態まで持っていきます。
・CSV(テキスト)データ
・出力データ（項目別）
CSV(テキスト)データの取得、データをセルに分割（出力データ）、一定タイミングでまとめていく作業が以下になります。
データ取得方法（ユーザーコンソールにログイン後）
1.ユーザーコンソールから対象SIMを選択
2. 詳細をクリックしデータ確認を選択
3. 右上取得したい範囲の日時を設定 更新
4. 時刻横のチェックボックスをクリック
5. ダウンロードからグラフ用データをダウンロード（CSV形式）を選択
6. 「ダウンロードを開始します」で名前を付けない場合は「ChartData.csv」が指定場所(ﾀﾞｳﾝﾛｰﾄﾞ)へ保存される(Macの場合は「クリックして保存」のみ。ファイル名「ChartData.csv」がダウンロードへ保存）
Excel側操作方法（新規ファイル立ち上げ後）
- TAB 「データ」を選択 テキストまたはCSVからを選択
- ダウンロードしたCSVファイルを読み込み、ウイザードに従い項目別にデータを分割する
3. 読み込み場所を選択し、「OK」を押す。
4. フィルターがかかっている場合 TAB 「データ」を選択 「フィルター」クリックしフィルター解除を行う
（この時バッテリー残量を確認し、直近「0.25」の場合は電池交換を検討する）
5. Ａ・Ｂ・D列を削除 E列のみデータ消去する
6. １・２行を選択し 行の挿入を行う
7. A1へ「取得間隔」入力 B1へ「0:10」入力
8. B列 セルの書式設定表示形式時刻から 「13:30」を選択
9. A4から最終行まで選択し 選択上で右クリック 並び替え_昇順 を選択
10. 変換元データ 2020/6/1 10:30:30 の場合い B4へ
=CEILING.MATH(TEXT(A4,"hh:mm"),$B$1)と入力B4をコピーし B5へ移動後
A列と同じ行数分選択しペーストを行う
11. B列をコピーし D列へ「書式設定」のみペーストする
12. D4へ「0:00」（もしくはスタート時間） E3へ「分布」を入力する
13. D5へ =CEILING.MATH(D4+$B$1,$B$1)を入力
14. 分単位で時間計算(5:00+0:10)した場合、何故かCOUNTIFで検出できなデータがいたためD5をコピーし必要数下行へペーストする
15. E4へ =COUNTIF($B$4:$B$56,D4) データ範囲を入力
16. E4をコピーし、D列と同じ行数ペーストする
17. COUNTを使いB列件数と「SAM」を使いE列分布件数のデータ数が同じか確認する
違う場合は、データ選択範囲が外れているか、時間は範囲が不足していないか確認する
18. これで分布が取れているので「取得間隔」を変更し分布を確認してみてください。
19. あとは好きなグラフを選択すればいいのですが、おすすめは以下の３点です
・ウオーターフォール
・ヒストグラム（パレード図の方ＱＣ7つ道具の一つ）
・積上げ縦棒（１日単位で時間分布を積み上げるとパターンが分り易い）
以下のグラフは上記３点を使用した作成例になります。
今回の集計は２４時間以内の分布をまとめるものになります。
１日ごとの分布集計を行う場合は下記変更内容を確認し、8・10・12項を変更してください。
変更内容
8.B列 セルの書式設定表示形式日付から 「3/14」を選択
10.変換元データ 2020/6/1 10:30:30 の場合
B4へ =TEXT(A4,"m/d")と入力 出力=6/1
B4へ =TEXT(A4,"d")と入力 出力=1
12.D4へ「6/1」←（開始日入力） E3へ「分布」を入力する
D4より下は連続日を入力してください
使用データ及、数式説明
＞データ例
A4 ：2020/7/24 18:59
$B$1 ：０：１５
B4 ：=CEILING.MATH(TEXT(A4,"hh:mm"),$B$1)
B4の数式は最初にTEXTを使いA4内の時間部分を取り出します。結果「18:59」になります。ここからCEILING.MATHを使うと$B$1が０：１５単位になっているため、１９：００へ繰り上がります。ちなみに１９：０１の場合は１９：１５へ繰り上がる様になります。
まとめ
関数を組み合わせることだけで、ノンコーティングで見やすい可視化ができました。短期的にデータを取って報告書にまとめるだけでも十分使える様に私は思います。例えば２４時間単位の行動パターンや使用タイミングなどの繰り返しが多いものは、ピーク確認にも使え使用機器のタイミングコントロールにも役立つかもしれません。追加で設定した１日単位での毎月の繰り返しも見やすくなると思います。