これまで開発してきた簡易IoTシステム（表示灯の点灯状況を、Webページで確認するシステム）では、Webページで機械の稼働履歴（表示灯の点灯履歴）を確認することができます。
また、先日は、この稼働履歴の情報をCSVファイルとしてダウンロードする仕組み、および、それを活用するためのExcelテンプレートを作成しました。
このExcelテンプレートでは、稼働履歴を表示するだけではなく、日次の稼働率も集計できるようになっていますが、今回は、この集計の部分をもっと融通のきくものにしたいと考え、Excelテンプレートを改良することにしました。
具体的には、現状は、集計対象の時間帯が、0時〜24時の24時間で固定になっていますが、これを、例えば9時〜18時など、一部の時間帯に限定できるようにします。
以下のとおり作業しました。
- 「セル」>「書式」>「シート保護の解除」を選択し、シートの保護を一旦解除しました。
- 集計対象の時間帯（開始時間の列名と終了時間の列名）を入力するセルを準備しました。
- 稼働率の表の右横に、上記で入力した開始時間列名と行番号を結合したセルを準備しました（CONCATENATE関数を使用）。同様に終了時間列名と行番号を結合したセルも準備し、これらを使って稼働率を計算します。
- INDIRECT関数を使用して、開始時間〜終了時間のセル範囲を選択し、COUNTIF関数とCOUNTBLANK関数を使用して、稼働率を計算しました。式は以下のようになります。全ての表示灯の色と全ての日付について、同様に処理しました。
= COUNTIF(INDIRECT($IP5):INDIRECT($IQ5),"red") / ( COUNTIF(INDIRECT($IP5):INDIRECT($IQ5),"*") + COUNTBLANK(INDIRECT($IP5):INDIRECT($IQ5)) ) * 100
- 稼働率の表の右横に準備した、開始時間セル名と終了時間セル名が記述されたセルについて、「セルの書式設定」>「ユーザー定義」を使用して、文字が表示されないようにしました。
- 稼働率の表をコピペして活用する際に便利なように、稼働率の表の左横に日付を追加しました。また若干、体裁を変更しました。
- CSVを貼り付ける予定のエリア、および、開始時間列名、終了時間列名を入力するエリアを選択し、「セル」>「書式」>「セルのロック」をOFFにした上で、「シートの保護」でOKをクリックします。
以下のようなものができあがりました。
上記のテンプレートは、稼働履歴を6分間隔で表示する場合のものですが、今回の作業のついでに、稼働履歴を1分間隔で表示する場合のテンプレートも準備しておきました。
（データ量が多くなってしまい、取り扱いが面倒になるので、どの程度有用かは分かりませんが。）
- 6分用テンプレートのシートを1分用テンプレートとしてコピーした後、「セル」>「書式」>「シート保護の解除」を選択し、シートの保護を一旦解除しました。
- 履歴の時刻表示欄は30列が結合されています（6分間隔なので3時間分）が、これを60列を結合（1分間隔なので1時間分）するように変更しました。この状態で4時間分の表が準備されていることになります。
- この表をコピペして、24時間分にしました。
- 「シートの保護」でOKをクリックし、シートの書式を変更できないようにしました。