エクセルの関数を教えてください

質問

はじめまして。
お客様が店舗に来店された時間ごとの人数をつかみたいのですがどういう関数を使ってすればいいかおしえてください。Ａ列に入店した時刻、Ｂ列に退出した時刻が年月日時分秒（数値はシリアル値）で書かれており、同じ時間を共有したお客様つまり入店して退出する間の時間が共通しているお客様の人数を1時間ごとに区切って数えたいと思いますがそういったことは可能でしょうか？分かりにくい質問かもしれませんが回答をよろしくおねがいします。
例：　　入店時刻（Ａ列）　　　　　　　　　　　退出時刻（Ｂ列）　
2010年5月6日10時30分40秒　　　　2010年5月6日11時52分56秒　→客Ａとする
2010年5月6日11時45分12秒　　　　2010年5月6日15時26分02秒　→客Ｂとする
2010年5月6日12時04分38秒　　　　2010年5月6日14時09分55秒　→客Ｃとすると
１０時台は1人、１１時台は２人、１２時台は３人、１３時・１４時台は２人、１５時台は１人という感じです。

回答No.3

imogasi · ベストアンサー

質問の希望が関数でと書いてあるのは読んでいる。
しかしこういう分析的なことを行うのに関数でというのは、適切でない。
関数の熟達者ならともかく、複雑な式を丸写し絵して、ありがとうで済ますよりは、より目的に近いツールである、
ピボットテーブルを勉強するべきだ。
そこで時間帯でのグループ化が出来ないか考える。
場合によっては時間帯を区別するコードを関数でコード化するとか（併用）も考えては。
ピボットは結果をグラフに直結も出来る。
ーーー
もうひとつ、関数らしからぬ複雑な処理をしてくれる、
FREQUENCY関数を勉強にこと。
これも第二引数の区間配列をどう作るかが、知恵が要ると思うが。
時間帯コードが別列に数字で出せれば考えやすい
時間帯別で複数日（例１ァ月）対象ならHOUR関数で時刻を数値化して、その数字でFREQUENCY関数を使ってはどうだろう。
ーー
初心者はそれしか出来無いと思うのか、関数ばかり聞きたがるが、関数でやる場合でも区間の配列をどう作るかが案外難しく、それから先に質問すべきだと思うが、気づいてない場合が多い。
これ（区間）が無いと関数式の複写を用いて全体のデータの式が作れないのではないかな。
もしこの質問のデータが数年分もあって、１時間ごとに考えるとなると、データ数が多くなって大変だが、質問には分析ロットは書いてない（不完全）。
ーー
そういう場合（データ数が多数の場合）はVBAででもやらないと大変でしょう。
はっきり言って、商売に絡んで多量のデータを扱うには、VBAの利用などが必須で、現状では質問者には、荷が重過ぎるのでは。
いきなり本番と言うか公式試合の出場といった風だが、そこまでの練習プロセスと言うものを普通は踏む。
もっとエクセル利用を経験しないと、こうい問題ではだめでしょう。

お礼

imogasi様
ご回答とアドバイスを有り難うございます。
ご指摘のとおり初心者でありましてこの処理は大変荷が重く、ましてや提出期限が決められているので、もう気合でやるしかありません。ピボットテーブルは社員別や商品別の売上集計などで使ってはいるのですが、社員名や商品名のように固定値（絶対値？）だとできるのですが、今回のように全部バラバラな値ですと見ただけでピボットテーブルはあきらめてしまいました。
FREQUENCY関数に関してはいまひとつわかりませんでしたが挑戦してみます。ありがとうございました。
ちなみに、データの個数は3万ぐらいです。

回答No.6

layy

参考

ポアソン分布と指数分布
http://www.kogures.com/hitoshi/webtext/stat-poisson-bunpu/index.html

関連キーワードは他に「待ち行列」です。

お礼

layy様
参考資料を教えていただき有難うございます。これは実数がはっきりして平均値などのある程度の数字が出たあと分析するためのものですね。エクセルの能力から言っても知識の量から言っても自分にはかなり難しいと思います。そこに到達するまでの予備知識をつけてから臨みたいと思います。
ありがとうございました。

回答No.5

layy

＞お客様が店舗に来店された時間ごとの人数をつかみたい

統計というのであれば、単にｘｘ時台にｘｘ人というのでなくて、
グラフを描けるようにした方が良い

ｘｘ時間帯にｘｘ人、も知りたいだろうし
ｘｘ時間帯に増加傾向にある、も知りたいだろうし
スタッフもどうだったかまで記録すれば、緻密な計画ができる。
顧客ストレスも変わるだろう。

どんな配置にしたら良いか、
データ情報があれば必要な行列だけ他シートへ抽出し表示もできる、
ことを思えば、最初はまとまりが無くてもなんとかなる。

集計の単位はｘ時台、なら、
「2010年5月6日10時30分40秒」を１０時台、にすることから考える。

2010年5月6日10時30分40秒　　　　2010年5月6日11時52分56秒　→客Ａとする
を
１０時台入店　１１時台退出　Ａ　女性
とか。
入店をプラス、退出をマイナス、というとらえ方でどうか、も考える。

お礼

layy様
ご意見を有り難うございます。
本当にまとまりがありませんでしたが皆さんのおかげで光が見えてきました。
入出店をプラスマイナスで考えるのは妥当だと思うのですが、今回の作業ではお客様が同時に店にいる時間がポイントなので、すれ違いになった客数をカウントしないというところが難しいと思いました。

回答No.4

MackyNo1

入店時刻のデータが「2010/5/6 10:34:40」のようなシリアル値で入力されているなら以下のような数式で時間ごとの人数を集計できます。

D2セルに「2010/5/6 10:00」、D3セルに「2010/5/6 11:00」と入力して下方向にオートフィルします。

E2セルに以下の式を入力し下方向にオートフィルします（D列よりも1つ少なめにオートフィル）。

=SUMPRODUCT(($A$2:$A$100<D3)*($B$2:$B$100>=D2))

お礼

MackyNo1様
ご回答いただきありがとうございます。
足し算と引き算に固執していましたが掛け算でも答えが出るのですか。算数は奥深いです。実際に式を入力してみました。
式の意味を勉強して応用できるようにしたいと思います。
有り難うございました。

回答No.2

wisemac21

添付図参照
C2に　=IF(AND(HOUR($A2)<=C$1,HOUR($B2)>=C$1),1,0)
式を右、下へコピー
各列の数値を合計すると時間帯の人数になります。

お礼

wisemac21様
ご回答を有り難うございます。よく理解できました。
このあと分や秒単位までアレンジして重複する滞在顧客を算出してみます。
どうもありがとうございました。

回答No.1

keithin

出入りの人数で整理してみます。

たとえば10時台に「少なくとも１分でも」居てる人の数は，
＝11時より前に入った人数－10時より前に出た人数
＝１－０　で１人

たとえば11時台に「少なくとも１分でも」居てる人の数は，
＝12時より前に入った人数－11時より前に出た人数
＝２－０　で２人

たとえば12時台に「少なくとも１分でも」居てる人の数は，
＝13時より前に入った人数－12時より前に出た人数
＝３－１　で３人じゃなく２人
です。

それぞれの人数は，いずれもCOUNTIFで計算できます。
=COUNTIF(A:A,"<"&(DATE(年,月,日)+TIME(13,0,0)))-COUNTIF(B:B,"<"&(DATE(年,月,日)+TIME(12,0,0)))

DATE(年,月，日)の部分やTIME(時,分,秒)の部分は，実際にあなたが整理する表の項目を上手く利用して合理的に作成してみてください。

お礼

keithin様
ご回答をありがとうございます。
とっかかりとして、そのような計算方法で考えるのは思いもしませんでした。
まずは時間帯の人数をつかんでみます。
あとはその時間帯のどの部分でお客様が重複しているかを分単位で
段階的に算出していこうと思います。ありがとうございました。