excel 来客の店内在留時間帯を求めたい

質問

EXCLEデータで、来店時刻と退店時刻が入力してあります。
形式は yyyy/mm/dd hh:mm です。
営業時刻帯の来客数を求めたいのですが、EXCELでカウントするにはどのようにすればよろしいでしょうか？

回答No.5

kkkkkm · ベストアンサー

回答No.3、No.4は勘違いしてました。

総数ではなくてそれぞれの時間ごとのカウントを表示したいという事だったのですね。

失礼しました。

回答No.4

kkkkkm

回答No.3の一部訂正です。

A1に入店時間
B1に退店時間

は

A列に入店時間
B列に退店時間
を記載していて
A1,B1からデータが始まっているとして

でした。

回答No.3

kkkkkm

各1時間内に何人いたかをカウントするということでしたら

A1に入店時間
B1に退店時間
を記載しているとして
C1に
=IF(A1="","",HOUR(B1)-HOUR(A1)+1)
として書式を数値にして
必要なだけ下にコピーします。

全体の総数を出したいセルに
=SUM(C:C)
としてみてください。

バージョンによってはC列の式は無くても総数を出したいセルだけに
1行目から10行目までとした場合です。

=SUM(IF(A1:A10="","",HOUR(B1:B10)-HOUR(A1:A10)+1))

としても同じ結果が出ると思います。

回答No.2

luka3

すみません、ちょっと勘違い？
と、滞在者数で修正を書き込もうとしたらちょうど補足が・・・
「営業時間帯」と思い込んでいましたが、そりゃ全員営業時間内に来ますよね。
下記をお試しください。

もし来店者の時刻ごとの滞在者数を調べたいのであれば、
A列が来店時刻、B列が退店時刻だったとして、
12:00から13:00の時刻帯にいた人数の求め方は、

=SUMPRODUCT((TIMEVALUE("12:00")<=TIMEVALUE(TEXT($B$2:$B$100,"h:mm")))*(TIMEVALUE(TEXT($A$2:$A$100,"h:mm"))<=TIMEVALUE("12:59"))*($A$2:$A$100<>""))

A2からB100の範囲にデータがあって、今回A列が空欄になっている行は無視するようにしています。

"12:00"を式の中に入れるのは煩雑なので、仮に、C2セルに「12:00」を入れて使うといった場合は、TIMEVALUE("12:00") を C2 に置き換えます。
この時、12:00 の前に日付が入らないようにしてください。
（12:00のセルの表示形式を「標準」にすると 0.5 と表示されるはずです。1以上になることはありません）
C2セルに「文字列」の"12:00"(「'12:00」でも可)を入れて、TIMEVALUE(C2) としてもいいです。

補足を読んで、各00分ちょうどの人数ということであれば、

=SUMPRODUCT((TIMEVALUE("12:00")<=TIMEVALUE(TEXT($B$2:$B$100,"h:mm")))*(TIMEVALUE(TEXT($A$2:$A$100,"h:mm"))<=TIMEVALUE("12:00"))*($A$2:$A$100<>""))

といった感じになります。

お礼

likaさま 色々とありがとうございました。 私の技量ではついていけそうにありません。IF関数で、地道に時刻の範囲内にフラク「１」を立てて、最終的には立て計で集計します。ありがとうございました。

Powered by GRATICA

回答No.1

luka3

時間が A2:A100 のセル範囲にあって、開店が9:00、閉店が21:00として

=SUMPRODUCT((TIMEVALUE(TEXT(A2:A100,"h:mm"))>=TIMEVALUE("9:00"))*(TIMEVALUE("21:00")>=TIMEVALUE(TEXT(A2:A100,"h:mm"))))

セル範囲が毎回変わるのであれば、B1に A2:A100 と入力しておいて

=SUMPRODUCT((TIMEVALUE(TEXT(INDIRECT(B1),"h:mm"))>=TIMEVALUE("9:00"))*(TIMEVALUE("21:00")>=TIMEVALUE(TEXT(INDIRECT(B1),"h:mm"))))

別のシートにある時間でも Sheet1!A2:A100 と指定すればカウントできます。

お礼

早速のご教示、ありがとうございます。 取り急ぎ、実施してみます。

Powered by GRATICA

補足

すみません。
質問の内容の説明が不十分だったようです。
日々の中で、９時から２０時までの間に、例えばＡさんが午前１０時ちょっと過ぎに来店し、１２時過ぎまで店内に居たとして、時刻単位にそれぞれの時間ごとに１カウントするとして３カウントというように、１０人の人がそれぞれ◯時から□時まで居たとして、それぞれの店内の滞在人数を求めたいのです。
来店時刻のカウントはたくさん紹介されているのすが、時刻ごとの滞在人数を求める参考がないので、お伺いした次第です。
よろしくお願いいたします。