こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

投稿日時 - 2011-02-04 00:21:36

QNo.6496289

困ってます

質問者が選んだベストアンサー

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

投稿日時 - 2011-02-04 10:18:31

お礼

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

投稿日時 - 2011-02-05 00:46:30

ANo.3

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

参考

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

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

投稿日時 - 2011-02-05 08:52:22

お礼

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

投稿日時 - 2011-02-05 22:27:05

ANo.5

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

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

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

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

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

2010年5月6日10時30分40秒    2010年5月6日11時52分56秒 →客Aとする

10時台入店 11時台退出 A 女性
とか。
入店をプラス、退出をマイナス、というとらえ方でどうか、も考える。

投稿日時 - 2011-02-05 00:01:18

お礼

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

投稿日時 - 2011-02-05 02:00:20

ANo.4

入店時刻のデータが「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))

投稿日時 - 2011-02-04 23:05:13

お礼

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

投稿日時 - 2011-02-05 01:45:13

ANo.2

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

投稿日時 - 2011-02-04 07:30:42

お礼

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

投稿日時 - 2011-02-05 00:29:09

ANo.1

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

たとえば10時台に「少なくとも1分でも」居てる人の数は,
=11時より前に入った人数-10時より前に出た人数
=1-0 で1人

たとえば11時台に「少なくとも1分でも」居てる人の数は,
=12時より前に入った人数-11時より前に出た人数
=2-0 で2人

たとえば12時台に「少なくとも1分でも」居てる人の数は,
=13時より前に入った人数-12時より前に出た人数
=3-1 で3人じゃなく2人
です。

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

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

投稿日時 - 2011-02-04 00:42:40

お礼

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

投稿日時 - 2011-02-05 00:22:59

あなたにオススメの質問