エクセル「社員の重なっている仕事時間の算出方法について」教えてください。

分かっている元データ、イメージ、求めたい項目を添付画像にまとめています。
A、B、C、D、Eさんの５人がいて、それぞれの年間の仕事開始時間と終了時間（日・時・分）は分かっています。
求めたいのは、５人、４人、３人で一緒に仕事をした年間の回数と時間です。
月・日をまたぐデータとなっています。

ぶしつけなお願いで大変申し訳ありませんが、未だ手作業で求めているため、皆様のお力添えをお願いいたします。使用しているソフトはエクセル2019です。

質問者からの補足コメント

• tatsumaru77　様
  大変厚かましい問題を考えて下さりありがとうございます。恐縮ですが以下のとおり、質問にお答えします。
  １．開始日時と終了日時の日付は必ず同じでしょうか。（終了日時が翌日になることがありますか）→あります。日またぎになる場合もあります。
  ２．日付は、必ず昇順に並んでいますか。→昇降順に並んでいます。
  ３．社員数は５人限定でよいですか。それとも６人以上になることもあるのですか。→5人限定です。
  
  宜しくお願いいたします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2023/02/06 13:08

• tatsumaru77様
  見ず知らずの私の問題に親切、丁寧に考えてくださり、重ね重ね感謝申し上げます。
  恐縮ながら以下の通り回答させていただきます。宜しくお願いいたします。
  Ｎｏ．2の回答をさせていただきます。
  1.ありません。24時間以上はありません。
  2.合っています。内訳に日付を入れ忘れました。
  3.日付が追記できるならお願いします。
  4.カンマ入りでおねがいします。
  5.例のとおりお願いします。
  6.①良いです。②良いです。
  7.添付する画像レイアウトでお願いします。「登録番号」セルの追加をお願いします。
  8.①シート2名称「中間データ」
  ②シート3名称「集計データ」
  
  Ｎｏ．3
  マクロ実行できます。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2023/02/06 21:50

• tatsumaru77　様
  返信遅くなりまして大変申し訳ございません。
  恐縮ながら以下のとおり回答させて頂きます。
  ご査収のほどよろしくお願いいたします。
  No4に対する回答です。
  ＞2.具体的にレイアウトを提示していただけませんでしょうか。
  ◎内訳
  　1回　2022/1/1　3：00～2022/1/1　4:00(A,B,C,D,E)
  2回　2022/1/2　3：00～2022/1/2　4:00(A,B,C,D,E)
  
  ＞3.具体的にレイアウトを提示していただけませんでしょうか。
  ◎内訳
  　1回　2022/1/1　23：00～2022/1/2　8:00(A,B,C,D,E)
  
  ＞7.シート名は、"Sheet1"で良いのでしょうか。
  シート１名：「元データ」でお願いいたします。

  No.5の回答に寄せられた補足コメントです。 補足日時：2023/02/07 15:45

• tatsumaru77　様
  
  本件では大変お世話になっております。
  確認させていただきました。
  心より感謝申しあげます。

  No.6の回答に寄せられた補足コメントです。 補足日時：2023/02/07 17:31

No.6 ベストアンサー 回答者： tatsumaru77 回答日時： 2023/02/07 16:26

最終確認です。

１．レイアウト確認
下記URLに画像をアップしましたので、確認をお願いします。
元データ
https://gyazo.com/8ec77d2eab4a12e6f2ec3981d523dd6a

中間データ
https://gyazo.com/9fefe8a53f29623bcded1d1a6ba2e0a8

集計データ
https://gyazo.com/ea44b6a5719b0f7e8c3972ff50ab6480

オレンジの列が追加の列です。（実際には色はつきません、説明上つけています。以降同様）
回数（黄色の列）ですが、D列全体をを選択して、書式設定でユーザー定義「G/標準"回"」
に設定すると1回、2回、と回が表示されるようになります。
書式設定は最初に1度実行すれば、以降はずっと有効になります。


２．中間データについて
マクロは１行目の見出しは作成しません。
２行目以降にデータを設定します。
見出しはあなたが作成しておいてください。

３．集計データについて
マクロは１、２行目の見出しは作成しません。
３行目以降にデータを設定します。
見出しはあなたが作成しておいてください。

以上、ご確認ください。

No.5 回答者： tatsumaru77 回答日時： 2023/02/06 23:24

No4です。

＞1.ありません。24時間以上はありません。
承知しました。

＞2.合っています。内訳に日付を入れ忘れました。
具体的にレイアウトを提示していただけませんでしょうか。

＞3.日付が追記できるならお願いします。
具体的にレイアウトを提示していただけませんでしょうか。

＞4.カンマ入りでおねがいします。
承知しました。

＞5.例のとおりお願いします。
承知しました。

＞6.①良いです。②良いです。
承知しました。

＞7.添付する画像レイアウトでお願いします。「登録番号」セルの追加をお願いします。
承知しました。
シート名について返信がありませんが
シート名は、"Sheet1"で良いのでしょうか。

＞8.①シート2名称「中間データ」
＞②シート3名称「集計データ」
承知しました。

No.4 回答者： tatsumaru77 回答日時： 2023/02/06 16:14

No3です。

追伸：
マクロで作成する予定です。
あなたの環境がマクロの実行ができない場合、もしくは
マクロでの提供を望まれない場合は、No3の補足要求は無視してください。マクロで良い場合のみ、No3に補足してください。

No.3 回答者： tatsumaru77 回答日時： 2023/02/06 16:10

No2です。

補足ありがとうございました。追加の補足要求です。
１．勤務時間が24時間以上の場合もありますか。
例　開始日時＝2022/1/1 8:00 終了日時=2022/1/2 9:00 のようなケース

２．表２の求めたい値についてですが、表示する情報に日付が含まれていません。
もし、1/1と1/2の勤務時間が全て同じと仮定した場合、
５人が一緒に仕事をした年間の回数と時間は、以下のようになると考えてますがあってますか。
年間回数と時間
２回　2:00

内訳
１回　3:00～4:00（ABCDE）
２回　3:00～4:00（ABCDE）

３．表２の内訳の記述方法ですが、時間が翌日にまたがる場合ですが、どのように記述しますか。
例　23:00～翌日の8:00の場合、23:00～8:00　で良いでしょうか。

４．表２の内訳の記述方法ですが、社員の記述は実際には社員名が設定されるかと思いますが、カンマで
区切らなくて良いのでしょうか。
例(ABCDE) ではなく(A,B,C,D,E)の方が良いかと思いますがいかがでしょうか。


５．表２の年間回数と時間ですが、24時間以上の場合は、どのように記述しますか。
例　27時間の場合、27:00　で良いでしょうか。


６.作業時間について
①終了日時の時間は、その時間は作業を行っていない時間と考えて良いですか。
例　開始時間　8:00 終了時間　9:00 の場合
　9:00を含めると作業時間は1:01 になります。
　従って　8:00～8:59が作業を行っている時間です。

②内訳に表示する時間も最後の時間は作業を行っていない時間になります。
社員A　8:00～12:00
社員B　8:30～9:00
社員C　8:00～12:00
の場合、3人が一緒に仕事をした時間は
8:30～9:00と表示されますが、9:00は正確には３人が一緒に仕事をしていません。
3人が一緒に仕事をしたのは、時間　0:30 8:30～8:59の時間です。
但し、表記上は8:30～9:00と表示されます。

上記の考え方で良いでしょうか。

７．表１　元データの正確なレイアウトが判りません。
下記URLのレイアウトで良いですか。異なる場合は、その旨返信ください。
https://gyazo.com/4156aacf2c7660708cc8bb8974e8f38d
シート名は "Sheet1"で良いですか。異なる場合は、その旨返信ください。

８．出力結果
①中間データとしてSheet2へ以下のようなデータを出力します。
シート名は "Sheet2"です。他のシート名にしたい場合は、その旨返信ください。
https://gyazo.com/7042e2f0a39990a892e0d2f8e8f0b9eb


②表２に該当するデータとして、以下のようなデータを出力します。
シート名は "Sheet3"です。他のシート名にしたい場合は、その旨返信ください。
https://gyazo.com/7a69b78970e7dc7f3f1abdb4da576332
このレイアウトで良いですか。

No.2 回答者： tatsumaru77 回答日時： 2023/02/06 08:37

私が回答できる保証はありませんが、確認です。

１．開始日時と終了日時の日付は必ず同じでしょうか。
（終了日時が翌日になることがありますか）
２．日付は、必ず昇順に並んでいますか。
３．社員数は５人限定でよいですか。それとも６人以上になることもあるのですか。

この回答へのお礼

tatsumaru77　様
見ず知らずの私の問題に対して、親切、丁寧そして真剣に考え下さり、重ね重ね御礼申し上げます。
自分のエクセルスキルのなさを不甲斐なく思っております。
tatsumaru77様のような素晴らしい人がいることに感激しました。

お礼日時：2023/02/06 22:07

No.1 回答者： 銀鱗 回答日時： 2023/02/06 00:43

逆だね。

時間に対して誰がその時間に仕事をしているのかを一覧にして、
複数の人が仕事をしている時間を逆引きするんだ。

・・・

(´・ω・`) ごめんよ。
質問者さん的には「代わりに作ってください」という事なんでしょうけど、
ここは
　”作成依頼をする場所” ではなく
　”自力で作れるようになるためのアドバイスをもらう場所” なんです。
自分で考えることが限界であれば、有料サイトでお金を払って作成依頼をするようにしましょう。

自分なら……お仕事で使うようですので、1年間のアフターフォローを含めて5,000円くらいで引き受けるような内容です。

・・・

「工数管理アプリ」を使ったほうがよさそうに思うんだけどね。


・・・余談・・・

ちなみにこんなイメージになります。

この回答へのお礼

銀鱗　様
本件では親切、丁寧に助言して下さり本当にありがとうございます。

お礼日時：2023/02/06 21:59