EXCELで同時アクセス数をカウントしたい

外部から接続してくる機器の「通信開始時間」と「終了時間」、「機器ID」のデータを左画像の様なEXCELにまとめています。
ここから右側の表を自動生成したいのですが、関数ややり方が分かりません。

右表は1分毎の時間軸に対する接続機器数を表しています。
例えばA0555は04:55と04:56に接続があるので、それを「1」と右表に記載します。
同時刻帯に接続してくる機器の「1」と合わせると、04:55の同時接続数は「2」、04:56の同時接続数は「5」となります。

左表から右表を自動作成する方法について教えて下さい。

No.2 ベストアンサー 回答者： 銀鱗 回答日時： 2019/03/01 10:02

必要な情報を知らないと話になりませんので、少し遠回りになりますが「シリアル値」について説明します。

日付や時間が入力されているセルの表示形式を「標準」にすると、意味不明な数値になってしまった経験はありませんか？
これは「シリアル値」と言う数値で「日付」や「時間」を示すものです。

「シリアル値」は1900年1月1日を「１」、翌1月2日を「２」とする連続した数値です。
2019年3月1日なら「43525」となります。

これは日付だけではありません。
時間もこの「シリアル値」になります。
2019年3月1日 12時00分 は、
　「43525.5」
になります。
時間は小数点以下の数字になるんですね。
しかも24時間で「１」になるようになっています。

そんなわけで日付と時間は「シリアル値」という「数値」で管理されています。


ここまでが「シリアル値」についての説明。
・・・

従って日付を含む時間から時間だけを取り出すには
　”小数点以下の数字だけを取り出せばよい”
という事になります。
その取り出した数値を24倍すれば時間（0.5だったら12時）になります。
これ、面倒ですよね。
でも心配ありません。
「0.5」と入力されれたセルに対して ”セルの表示形式” を「時刻」にする事で「12:00」と表示させることができます。
特に計算させる必要はなく「0.5」を「シリアル値」として扱うようExcelに命令すれば良いのです。

なお、日付のないシリアル値は小数点以下だけの数値になります。（「0」から「0.9999999999999999」まで）
これを無理やり日付表示させると「1900年1月0日 〇〇時〇〇分」のようになります。

さて、それでは次にどうやって「2019年3月1日 12時00分」のシリアル値から「0.5」を取り出すかです。
これは至って簡単。
割り算して余りを求めるという ”関数” があります。
これを使うと良いでしょう。
MOD関数がこれになります。
　＝MOD(数値,除数)
　＝MOD(43525.5,1)　…1で割った余り「0.5」が値として返ります。
のように使います。

なお、シリアル値から「時」「分」「秒」をそれぞれ取り出す関数もありますが、取り出した後「時」と「分」を合わせる手間がかかりますのでここではパス。


・・・
さて、ここまで説明すれば、「4:55」から1分ずつ増やすことは簡単ですよね。
難しく考える必要はありません。
「1分」分の「シリアル値」を足していけば良いんです。

え？「1分」分の「シリアル値」はいくつになるかって？
そんなかたに耳寄りな情報。

Excelには計算式の中に「シリアル値」に直すことのできる「文字列」を勝手に「シリアル値」として扱い計算を行う機能があります。
　＝"12:00"+"4:00"
なんて計算ができるという事です。（ちなみに答えは"16:00"を示すシリアル値になります）
A1セルに1分足すなら、
　=A1+"0:01"
で十分ってこと。
難しくないでしょ。（それどころか見やすい）

がんばってください。

No.1 回答者： 銀鱗 回答日時： 2019/03/01 00:27

え？

質問者さんはすでにやりたいことができる表を作っているで張りませんか。

・・・
左の表の時間と、右の表の時間を比較すれば良い。
そんだけじゃないの？

　右の時間よりも左の通信開始時刻のほうが小さく
　右の時間よりも右の通信終了時刻のほうが大きい
この二つの条件を満たしたら右の時間にアクセスしているという事だよね。
それを数式にすればいい。

さて、質問者さんはこの手順の中の何が分かりませんか？
二つの条件を数式にする事ができないというのであれば、まずは自身で試行錯誤しましょう。
　=IF(AND(右＞=開始 , 右＜＝終了),1,0)
こんな数式になる。
意図しない結果が返るのであれば、その原因を調べよう。
分からないのであれば、何が原因であるかを質問すればいい。

がんばれ。


・・・余談・・・

この手の物は紙の上で手書きで作業するって順をそのまま数式にすれば良いだけです。
手順を曖昧にしないことが大切です。
これらのことがちゃんとできるようになったら、数式をシンプルになるよう応用すれば良いのです。

この回答へのお礼

回答ありがとうございます。

EXCELは簡単な使い方しか出来ず、手作りで右の表を作るスキル位しかなくて困っています。
（表は非常に大きいモノで、手で記入して行くには限界なレベルになっています）

まずわからないのは、時刻の取扱いです。
分からないのは
1．開始　2019/2/25 4:55:05から終了　2019/2/25 5:01:35　から
何分なのかを自動計算して「04:55」「04:56」「04:57」「04:58」「04:59」「05:00」「05:01」の
7つの時刻を転記する方法
2．その1分毎の「時刻行」に「1」を立てていく
この部分が長い時間トライアンドエラーを繰り返していますが、解決できずに困っています。

お礼日時：2019/03/01 05:06