excel 並行時間の計算

質問

エクセルで例えば
Aさんは10：00から3分間電話。（10：00～10：03）
Bさんは10：01から3分間電話。（10：01～10：04）
Cさんは10：02から3分間電話。（10：02～10：05）

上記の電話が最繁している数を割り出したいです。
10：00→1人、10：01→2人、10：02→3人、10：03→3人、10：04→2人、10：05→1人

具体的な例にいうと、
B4：B10に電話を開始した時間（YYYYMMDDhhmmss）
C4:C10に通話を終了した時間（YYYYMMDDhhmmss）

⇒　D列等に１秒毎の最大通話人数を求めたいです

＃＃COUNTIFやCOUNTIFSでうまいことできなかったため、よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

１秒毎の最大通話人数を求める方法ではなく、例えば、通話人数が同じ時間帯毎に区切ってデーターを表示させるという方法は如何でしょうか？
　例えば、

Dさん　10:00～10:05
Eさん　10:03～10:05
Fさん　10:03～10:08
Gさん　10:02～10:10

という例ですと、

10:00:00～10:02:00→1人
10:02:00～10:03:00→2人
10:03:00～10:05:00→4人
10:05:00～10:08:00→2人
10:08:00～10:10:00→1人

という具合になります。

　今仮に、I1セルを作業用のセルとして使用し、E2以下に時間帯の始まりの時間、G2以下に時間帯の終わりの時間、H2以下に通話人数を表示させるものとします。

　まず、I1セルに次の関数を入力して下さい。

=IF(AND(COUNT($B:$B),COUNT($C:$C)),MAX($C:$C),"")

　次に、I1セルの値が見えていたのでは見苦しいので、以下の様な操作を行って、I1セルを非表示にして下さい。（尚、添付画像では、非表示にしてしまっては解り難くなりますので、敢えて非表示とはせずに、代わりに薄い青色で表示させております）

I1セルにカーソルを合わせてからマウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

;;;

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、E2セルに次の関数を入力して下さい。

=IF(AND(COUNT($B:$B),COUNT($C:$C)),MIN($B:$B),"")

　次に、F2セルに次の関数を入力して下さい。

=IF(INDEX($G:$G,ROW())="","","～")

　次に、G2セルに次の関数を入力して下さい。

=IF(OR($E$2="",$E$2>=("0"&$I$1)+0),"",SMALL($B:$C,2))

　次に、H2セルに次の関数を入力して下さい。

=IF(INDEX($G:$G,ROW())="","",COUNTIFS($B:$B,"<="&INDEX($E:$E,ROW()),$C:$C,">="&INDEX($G:$G,ROW())))

　次に、E3セルに次の関数を入力して下さい。

=IF(INDEX($G:$G,ROW())="","",INDEX($G:$G,ROW()-1))

　次に、G3セルに次の関数を入力して下さい。

=IF(INDEX($G:$G,ROW())="","",INDEX($G:$G,ROW()-1))

　次に、F2セルをコピーして、F3セルに貼り付けて下さい。
　次に、H2セルをコピーして、H3セルに貼り付けて下さい。
　次に、E3～H3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　以上です。

お礼

すみません。下記の部分でつまづいてしまいました：：
なんか違う気がしました。

＞　次に、G3セルに次の関数を入力して下さい。
＞=IF(INDEX($G:$G,ROW())="","",INDEX($G:$G,ROW()-1))

あと下記の「>=("0"&$I$1)+」は「>=("0"&$I$2)+」なきがしました。
＞=IF(OR($E$2="",$E$2>=("0"&$I$1)+0),"",SMALL($B:$C,2))

回答No.1

kagakusuki

>D列等に１秒毎の最大通話人数を求めたいです

との事ですが、例えば、電話をし始めた時刻の中で最も古いものが2013/1/1の0:00:01で、電話をし終えた時刻の中で最も新しいものが2013/1/13の3:16:18だった場合、これら2つの時刻の間は1048577秒にもなってしまいます。
　D列中に含まれているセルの数は1048576個しかありませんから、これでは1秒ごとのデータを表示させる事は出来ません。
　仮に表示させる事が可能であったとしましても、100万個以上ものデータを計算する際には、非常に長い時間を要すると思われますし、結果を保存する際にも、時間がかかる事は無論の事、保存しなければならないデータ量も無闇に大きなものとなってしまいます。
　ですから、別のやり方にされた方が宜しいのではないでしょうか？