PC操作ログの集計について良い方法を教えてください

パソコンの操作ログをツールで収集したCSVがあります。

ユーザごとの、1日最初の時間と、最後の時間を抽出したいです。
ログの種類に「電源ON」と「電源OFF」があるものは容易ですが、
放置や画面を閉じてスリープになった場合はログが残りません。
代わりに「ファイル操作」「WEBアクセス」という種類のログを手掛かりにしたいのですが、
1時～3時までファイル操作が続き、一旦途絶えて、6時～9時まで操作というのを、
ブロック単位で取得したいです。
例えば、30分以上間隔があかなければ操作中、それ以上間隔が空いたら離籍中という場合も
1時操作開始、3時操作終了、6時操作開始、9時操作終了というように区別するにはどんな式で行えるでしょうか？

ログは、こんな風に入っています。

ログ取得日時、ユーザ名、操作ログ種別、操作種別
2021/8/5 19:31、PC0001、PC稼働、電源OFF
2021/10/13 9:02、PC0002、Webアクセス、Webアクセス
2021/10/15 12:10、PC0001、ファイル操作、削除

No.2 回答者： fujillin 回答日時： 2021/11/09 18:53

No1です。

少し時間が取れたので、簡単に試してみました。
出力をどのような形式でなさりたいのか不明なので、ご参考までに適当に決めてあります。
（私がやるなら、マクロで行ってしまうところですが、『どんな式で～』とのご質問なので、多分、関数をお望みなのかと解釈しました。）

事前準備として、全体のデータをソートしておくものとします。
（第一キーが「ユーザ名」、第二キーが「日時」です）
※ また、元データの日時は、エくセルのシリアル値になっているものと仮定しています。

添付図では、A～D列が元データをソートしたものと仮定しています。
また、F~H列に操作の区分を分けて列挙するようにしてあります。
（30分内に連続して操作が無い場合の1回だけの操作は、1区分と判断されて、開始時刻と終了時刻に同じ時刻が表示されます）

添付図ではF2～H2に以下の式をそれぞれ入力し、下方にフィルコピーしています。
◇F2の式
=IF(H2="","",B2)

◇G2の式
=IFERROR(AGGREGATE(15,6,A$2:A$20/(B$2:B$20=B2)/((B$2:B$20<>B$1:B$19)+((B$2:B$20=B$1:B$19)*(A$2:A$20-A$1:A$19>1/48))),COUNTIF($B$1:$B2,B2)),"")

◇H2の式
=IFERROR(AGGREGATE(15,6,A$2:A$20/(B$2:B$20=B2)/((B$2:B$20<>B$3:B$21)+(B$2:B$20=B$3:B$21)*(A$3:A$21-A$2:A$20>1/48)),COUNTIF($B$1:$B2,B2)),"")

※ 対象データ範囲はひとまず2～20行としていますが、範囲を正しく修正すれば、範囲の増加には対応可能です。
※ 添付図ではA1セルが空白になっていますが、実はA1セルが空白（または、数値でA2と30分以上差のある値）でないと、G列の計算が部分的に正しい結果になりません。
（No1です。

少し時間が取れたので、簡単に試してみました。
出力をどのような形式でなさりたいのか不明なので、ご参考までに適当に決めてあります。
（私がやるなら、マクロで行ってしまうところですが、『どんな式で～』とのご質問なので、多分、関数をお望みなのかと解釈しました。）

事前準備として、全体のデータをソートしておくものとします。
（第一キーが「ユーザ名」、第二キーが「日時」です）
※ また、元データの日時は、エくセルのシリアル値になっているものと仮定しています。

添付図では、A～D列が元データをソートしたものと仮定しています。
また、F~H列に操作の区分を分けて列挙するようにしてあります。
（30分内に連続して操作が無い場合の1回だけの操作は、1区分と判断されて、開始時刻と終了時刻に同じ時刻が表示されます）

添付図ではF2～H2に以下の式をそれぞれ入力し、下方にフィルコピーしています。
◇F2の式
=IF(H2="","",B2)

◇G2の式
=IFERROR(AGGREGATE(15,6,A$2:A$20/(B$2:B$20=B2)/((B$2:B$20<>B$1:B$19)+((B$2:B$20=B$1:B$19)*(A$2:A$20-A$1:A$19>1/48))),COUNTIF($B$1:$B2,B2)),"")

◇H2の式
=IFERROR(AGGREGATE(15,6,A$2:A$20/(B$2:B$20=B2)/((B$2:B$20<>B$3:B$21)+(B$2:B$20=B$3:B$21)*(A$3:A$21-A$2:A$20>1/48)),COUNTIF($B$1:$B2,B2)),"")

※ 対象データ範囲はひとまず2～20行としていますが、範囲を正しく修正すれば、範囲の増加には対応可能です。
※ 添付図ではA1セルが空白になっていますが、実はA1セルが空白（または、数値でA2と30分以上差のある値）でないと、G列の計算が部分的に正しい結果になりません。
（PC0001の開始時刻だけ1行ずれて表示されます。）
関数式でこれを回避することも可能とは思いますが、そのためには数式がメチャクチャ長くなる可能性があるので、横着をさせて頂きました。
※ もしも、タイトルが無いのが気持ち悪ければ、2行目を空白行にしてしまい、実データを3行目からにすることでも式の変更はなく対応が可能と思います。（2行目を非表示にしておくことも可能でしょう）

この回答へのお礼

ありがとうございます。
そんなに試してくれてとても恐縮です。
AGGREGATEを見て引いてしまいました(笑
（使い道の分からない関数だったので）

最初から言えよ、と怒られそうで言いづらいですが、
PCの台数は400台位あります。
各スタッフの出退勤はWEBからの自己申告入力なので、
PC使用のログと比較して、出勤時間と退勤時間の大きな乖離がない事を、
毎月チェックするために必要な集計を行いたかったんです。
(例えば、20時に退勤と言っているのに、15時に電源OFFだったら確認が必要)

ほぼほぼ9割の人は、朝に電源ON、夕方に電源OFFの1セットです。
中には、電源切らずに画面を閉じてノートを持ち帰って自宅で深夜作業する人もいるでしょう。
テレワークで、午前中集中作業して、午後休で夜間作業する人もいるかもしれません。
そういうイレギュラーの方、すなわちfujillin さんご提示の方法は、あえて目検で調査する場合非常な有効な方法と思います。

それ以外の通常ルーチンチェックとしては、
当日の初回電源ON時間と、最後の電源OFF時間だけ抽出して、
WEBの勤怠データの横に並べて差分を出す、みたいな事にしたいです。

当方が考えていたほぼ自動になる方法は、
PC操作ログCSVと勤怠CSVを所定のフォルダに上書きして、
Excel側はPowerクエリで取り込んで集計表を更新かな、、、と考えました。
操作ログ上で出勤と判断するのは、「電源ON、ファイル操作、WEB操作」いずれかの一番最初のログ、退勤は同じく一番最後のログかなと思ってますが、
まだ実現してません。
テーブル上に数式を入れるので、勝手に一番下まで数式が浸透する方法が楽かなと思ってます。(構造化というんでしたっけ)

自身の頭の整理も兼ねて書き連ねてしまいました。

お礼日時：2021/11/10 11:25

No.1 回答者： fujillin 回答日時： 2021/11/09 12:25

こんにちは

考え方のみになりますが・・・

まずは、PCごとにデータを分けましょう。
次に、日付（時刻）順にソートしておきます。
各行の時刻の差を求め、30分以上の差があれば、そこが連続操作の切れ目ということになります。

関数でやるなら、作業列に「時間差が30分以上か？」を表示するようにしておけば、その両端を連続操作の時間とすることができます。
一発で求めることもできないではないでしょうけれど、考え方は同様ですね。

マクロで行う場合も、考え方はほぼ同様にして行うことができると思います。
メモリが使えるので、事前操作を行わなくても直接結果を求めることも可能でしょう。

この回答へのお礼

ありがとうございます。
ロジックは理解できた気がします。

一作業なら、PC毎にリスト自体を分けて力技で出来そうな気がするのですが、今回は毎月出てくるログの集計をルーチン化させないといけません。
しかも別の人に運用させないといけません。

一つの表内で処理する方法を考えてますが、
PC名&日付（時刻）でソートして、連続判定をするのかな？
のかな？というざっくりイメージです。
作業列を挿入して、一つ上の行の時間と自分の行の時間の差の大小判定してくんでしょうか。。。頑張ってみます。
もし追加アドバイスがあったらお願いします。

お礼日時：2021/11/09 15:04