Excelの点検台帳をPower BIで一括管理!点検期限ダッシュボードの作り方

Excelの点検台帳をPower BIで一括管理!点検期限ダッシュボードの作り方 DX・業務改善

Excelのフロン管理台帳をPower BIで一括管理!点検期限の見える化からTeams共有まで

機器ごとにExcelで管理しているフロン管理台帳。書式が統一されていても、機器が増えると「最後に点検したのはいつか」「次の点検期限はいつか」を一つずつ確認するのは大変です。 今回は、既存のExcel帳票を活かしながら、Power BIで全機器の点検状況を一覧化する仕組みを作成しました。本記事では、構築の流れと、途中でつまずいた点、運用上の注意点をまとめます。

完成イメージ

1.実現したかったこと

もともとの管理方法は、1機器につき1つのExcelファイルに点検履歴を入力するというものです。

目指したのは、次のような管理でした。

  1. Excelの帳票レイアウトや入力方法は基本的に変えない
  2. 全機器の最新定期点検日を一覧表示する
  3. 機器ごとの点検周期から次回期限を計算する
  4. 定期点検対象外の機器も一覧に残す
  5. 期限超過や期限が近い機器を見つけやすくする
  6. Excelの更新内容をPower BIに反映する
  7. 最終的にはTeamsから閲覧できるようにする

そのために、Excelを入力・記録用、Power BIを集約・確認用として使い分けました。

2.Excelの読み込み環境を整える

①台帳をフォルダにまとめる

Power BIで取り込むExcelを、指定のフォルダにまとめます

フォルダ保存画面

今回のPower Queryでは、指定フォルダ直下のExcelファイルだけを対象にしました。

Folder.Files()はサブフォルダも読み込むため、フォルダパスで対象を絞っています。また、Excelを開いているときに作成される「~$から始まる一時ファイル」は除外しました。

②シート名を統一する

読み込み対象のシート名は、すべて**「点検記録簿」**に統一しました。シート名が統一されていると、各ファイルに同じ抽出処理を適用しやすくなります。なお、シート名を変更する場合は、既存の数式やマクロへの影響も確認が必要です。

3.帳票形式のExcelから必要な項目を抽出する

今回のExcelは、セル結合などがある帳票形式です。

そのまま全ファイルを縦につなげるだけでは、機器情報と点検履歴を扱いにくくなります。 そこで、Power Queryで各ファイルから必要な情報を抽出し、1機器1行にまとめる方法にしました。

項目使用目的
管理No機器の識別・並べ替え
設置場所対象機器の確認
機器区分エアコンと冷凍・冷蔵機器の区別
圧縮機の電動機定格出力点検周期の判定
最新定期点検日次回期限の計算
基準日定期点検履歴がない場合の確認
状態期限超過・期限接近などの表示

全ファイルのレイアウトが同じため、固定位置から値を取得する処理を利用しました。

ただし、Power Queryで読み込んだ表の行位置とExcelのセル位置が一致しているかは、実際の台帳と照合する必要があります。

4.最新の「定期点検日」を取得する

点検履歴には、定期点検以外にも設置時点検や初期充填などの記録が含まれています。

そのため、すべての作業日の中から最新日を選ぶのではなく、次の手順で求めました。

1.点検履歴部分を取り出す

2.点検区分が「定期点検」の行を抽出する

3.作業年月日を日付型に変換する

4.有効な日付の最大値を取得する 例えば、次の履歴であれば、

作業年月日区分
2019/6/4設置時点検
2020/9/17定期点検
2023/10/11定期点検

最新定期点検日は2023/10/11になります。

また、定期点検履歴がない機器も管理対象です。各ファイルから機器情報を先に取得することで、点検履歴がなくても一覧から消えない構成にしました。

5.機器区分と定格出力から点検周期を判定する

点検周期は、出力だけでなく機器区分も含めて判定します。

フロン排出抑制法の通常の定期点検について、基本となる区分は次のとおりです。

機器区分圧縮機の電動機定格出力定期点検頻度
エアコン7.5kW以上50kW未満3年に1回以上
エアコン50kW以上1年に1回以上
冷凍・冷蔵機器7.5kW以上1年に1回以上
上記の機器7.5kW未満定期点検の対象外

**定期点検の対象外でも、簡易点検は原則として必要です。**そのため、画面上でも単に「点検不要」とせず、「定期点検対象外」と表示します。

実運用では、対象機器や適用条件を最新の公的資料で確認することが必要です。

6.定期点検履歴がない場合の処理

次の条件に該当する機器は、帳票内の別の日付を基準に、次回期限を表示する処理を追加しました。

•点検要否が「定期点検対象」

•最新定期点検日が空白

日付がA15にあるファイルとA16にあるファイルがあったため、A16を優先し、A16が空白ならA15を使うルールにしています。

A15A16採用する値
日付あり日付ありA16
空白日付ありA16
日付あり空白A15
空白空白要確認

A16に値があっても日付へ変換できない場合は、A15へ自動的に切り替えず、要確認として残しました。採用したセル、基準日、変換エラー、期限計算元も列として持たせることで、計算根拠を確認できます。

社内管理日と法定期限は区別する

今回の設定では、定期点検履歴がない対象機器について、基準日から3年後の月末を表示する処理にしました。ただし、これは今回指定した社内管理用の計算ルールです。

•A15・A16が設置日ではなく充填日である可能性がある

•1年周期の機器に一律3年は適用できない

•月末へ延ばすと、本来の点検周期を超える場合がある

そのため、この計算結果をそのまま法定期限として扱わないことが重要です。

年月だけ見せたい場合は、実際の期限日を変更せず、表示形式をyyyy年M月にする方法もあります。

7.一覧表とダッシュボードを作る

データの抽出と計算ができたら、「閉じて適用」でレポート画面に戻ります。

完成形は、次のような構成です。

ダッシュボードに表示させる一覧

一覧表を整える

見やすくするため、次の設定を行います。

•管理Noを数値として扱い、昇順に並べる

•管理Noと定格出力を「集計しない」にする

•日付を「日付の階層」ではなく、通常の日付で表示する

•不要な合計行を非表示にする

管理Noに先頭ゼロや英字が含まれる場合は、文字列のままにして、別の並べ替え用列を作る方法が適しています。

メジャーで台数を集計する

メジャーとは、台数や件数を計算するための式です。 例えば、1機器1行になっていれば、登録機器数は次の式で求められます。

期限超過件数は、次のように計算できます。

期限超過件数=
COALESCE(
        CALCULATE(
           COUNTROWS('PowerBI用'),
           'PowerBI用'[状態]="期限超過"
        ),
        0
)

作成したメジャーをカードに配置すると、台数や件数を大きな数字で表示できます。

さらに、状態や機器区分のスライサーを追加すれば、確認したい機器だけに一覧を絞り込めます。

8.設定時につまずいた点

構築中には、次のような問題がありました。

問題対応
シートが見つからないシート名と読み込み対象を確認
フォルダ変更後にエラーが出る接続先パスや個別ファイルの固定参照を見直す
A15の日付が取得できない読み取った値を確認列に表示し、A16も確認
日付の見出しが年・月・日に分かれる日付階層から通常の日付へ切り替える
管理Noが数値順に並ばないデータ型を整数に変更し、昇順で表示
保存できない一時編集フォルダを避け、保存可能な通常のフォルダを指定

特に役立ったのは、エラーを空白で隠すのではなく、元の値とエラー内容を見えるようにすることでした。

9.Teamsで共有する方法

共有には、通常、作成者と閲覧者にPower BI Proなどの適切なライセンスが必要です。利用する容量によって条件が変わるため、社内のIT担当者へ確認します。

また、**Teamsのメンバーであるだけでは、Power BIレポートの閲覧権限は付与されません。**Power BI側でもアクセス権の設定が必要です。

個人で試す場合は「マイ ワークスペース」、部署で運用する場合は管理者が指定する共有用ワークスペースを利用します。

なお、社内台帳の共有に、認証なしでインターネット公開する「Webに公開」は使用しません。

10.完成後の更新方法

今回の仕組みでは、Excelを保存した瞬間にPower BIの表示が切り替わるわけではありません。

Desktopでの基本的な更新手順は、次のとおりです。

1.Excelを更新して保存する

2.必要に応じてBoxの同期を待つ

3.Power BI Desktopで「更新」を実行する

4.共有用レポートへ反映する場合は再発行する

PC上のBoxフォルダを参照している場合、Power BI Serviceから直接そのパスを読み取ることはできません。

自動更新するには、ゲートウェイを利用する構成や、SharePointフォルダー接続への変更などを検討します。

期限超過などの「状態」もデータ更新時点で再計算されるため、更新頻度まで含めた運用設計が必要です。

まとめ

今回の取り組みでは、機器ごとのExcel台帳を活かしながら、Power BIで最新点検日と次回期限を集約できました。

構築のポイントは、次の5つです。

1.シート名と帳票レイアウトをそろえる

2.必要項目を抽出し、1機器1行にまとめる

3.点検周期を機器区分と定格出力で判定する

4.空白や読取エラーを確認できるようにする

5.共有権限と更新方法まで含めて運用を設計する

Excelへの入力は続けながら、確認作業はPower BIへ集約する。

この役割分担によって、帳票を一つずつ開かなくても、全機器の点検状況を把握できるようになりました。

コメント

タイトルとURLをコピーしました