突然団体でくるお客のデータをグラフにしたい

質問

飲食店で毎日ある程度決まった時間に１０名以上で来店してくる団体がいるんですが、
その団体のデータをとってグラフを作りたいんですが、いい方法はありますか？
私が持ってるソフトはエクセル２００３だけです。　もしもフリーのソフトとかあれば、なおいいんですが。

下に貼った画像は、黒は入店時間　赤は退店時間です。
縦軸が時刻
横軸が曜日　です。

結構規則性があるんですが、
土曜は全くこなかったり、たまにきたり、木曜日などは結構ばらついたりします

こんな感じでデータをグラフ化する方法やフリーのソフトはありますか？
ご教授よろしくお願いします。

Windows8
エクセル２００３

回答No.9

QoooL · ベストアンサー

■ステップ3-2
＞■ステップ3
＞8:00:00になったら教えてください。
＞はい、そのようになりました　８：00：00です。

そのままでも（秒が表示されても）良いと思えば放置してください。

２つ解決方法があります。

１つは、ステップ５と同じ方法。
セルＡ８だけを選択するのでなく、Ａ列全体を指定してから書式設定をすると、楽でしょう。

もう１つは、Ａ７以外に、8:00　と表示されているセルがあるときに、そのセルの書式設定をまんまコピーしてしまう方法。
例えば、Ａ７は　8:00:00　で、Ａ８は　8:00　のときに、
Ａ８をコピーし、Ａ７を選んでから、形式を選んで貼り付け　を選び、書式にチェックしてからＯＫする。

■
さて、昨日まで毎日ここをのぞいていましたが、お忙しいみたいですね。
私の方もあまりまめに時間は取れないかも知れません。
数日様子を見て、気付いたことからお願いがあります。
今後は、この質問のまま続けるのでなく、要所要所まで進んだら、ベストアンサーを選んだ上で別質問で続きを立ててくださった方がありがたいです。

自分からＢＡを選んで欲しいと言うのはおこがましいし厚かましいですが、２つ理由があります。
締め切りしていないと、いちいち質問をのぞかないと、お礼などのコメントが付いたかどうかがわかりにくい（一応メールは来るんですが不便です）。
もう一つは、ＢＡを選ばずに締め切られると、私のＢＡ率が下がるので、ありがたくない。

また、別質問を立てるのは質問者様にとってもメリットがあります。
私以外からの回答が得られる可能性もある。
写真が付けられるので、途中で疑問に思ったことを聞きやすい。

まあ、特定の人とのやり取りはルールぎりぎりですが、大丈夫でしょう。

新質問のタイトルは、
　　突然団体でくるお客のデータ　その２
のような形で立てておいていただければ、こちらの締め切り後にそちらを必ずのぞきに行きます。

では、今回の課題です。

■ステップ11
少し、より便利になるように仕様を変えます。
同じブックで、新しいシートを準備してください。
新規ブックでシート数３に設定している場合には、一つ余っているはずです。

余っていないときは、下のシートタブで右クリックします。
挿入
を選ぶと、シートが追加されるはずです。
ちょっと、2003の仕様を忘れてしまいましたが、「新しいシート」というのにチェックを入れるのだったかも知れません。

そのシートに、
　　団体記録
と名前を付けます（シートに名前を付けたことないですか？　クリックするだけですよ）。

■ステップ12
そのシートの　セルＡ１　が選ばれている状態で、
7/1
と入力する。たぶん
7月1日
になるはず。

7/1　や　2014/7/1　になったとしても、あまり気にしないでください。

■ステップ13
Ａ１を選んで　右クリックから　書式設定　を選び、
分類で　ユーザー定義　を選ぶ。
種類欄に
yyyy/mm
と入力し、ＯＫする。
Ａ１の表示が　2014/07　に変わる。

■ステップ14
Ｂ３に
6/29
と入力する。
6月29日
になるはず。書式設定は好みで変更する。

■ステップ15
Ｃ３に
=TEXT(WEEKDAY(B3),"aaa")
と入力する。
日　になる。
（今回は、１週間の始まりを日曜にしました。月曜始まりにしたいなら、ステップ14　の　6/29　を　6/30　にするだけです。）

■ステップ16
Ｄ３に
=CELL("address",D3)
と入力する。
$D$3　になる。

■ステップ17
Ｅ３に　入店時間
Ｅ４に　退店時間
と記入する。

■ステップ18
このステップは飛ばして結構です。カスタマイズのご提案です。
Ｅ５に　在店時間
Ｅ６に　人数
Ｅ７に　売上
Ｅ８に　客単価
などと書いて活用すると良いでしょう。
このレッスンでは詳細は省きます。

■ステップ19
セルＢ１０　に
=B3+1
と入力する。
6月30日
になるはず。

■ステップ20
Ｃ３：Ｅ４　の６セルをまとめて　Ｃ１０：Ｅ１１に　コピーペーストする。
Ｃ１０　が　月　になる。

■ステップ21
１０行目　を選択した状態で、マウスでドラッグするか、シフトキーを押しながら↓を押して、
１０行目　～　１６行目　の　７行分を選択した状態にする。

図９

■ステップ22
コピーする。
１７行目を選択してから、２９６行目まで一気に広域を選ぶ。

方法は２通り。
１７行目を選んだ状態でマウスを２９６行目までひたすらドラッグ操作で範囲選択をスクロールする。

または、１７行目を選んだ状態で、２９６行目まで画面をスクロールさせてから、シフトキーを押しながら２９６行目をクリックする。

ペースト（貼り付け）する。

２９６行目まできちんと選んでいると、７の倍数で行が規則的にコピーされて、
Ｂ２９０　は　8月9日　になっているはず。
少しでもずれていると、
１７行目～２３行目までの　７行がペーストされるだけなので、やり直しです。

（わざわざ8月9日まで用意しているのは、第６週まである週に対応するためです。）

ここまでできたら、ファイルを保存して、質問を締め切ってください。
よろしくお願いします。

お礼

はい、今のところご指示のとおりになっていると思います。
では、ご指示のタイトルで今から新たに質問をたてますので続きをよろしくお願いします！

回答No.8

QoooL

■ステップ9
セルＢ7　に
=IF(B$3="",0,IF(B$4="","",IF(B$3<=$A7,IF(B$4>=$A7,1,0),0)))
と入力する。
0　と表示されるはず。

■ステップ10
セルＢ7　右下をダブルクリックして、下までオートフィルする。

図8　ステップ10まで。
それを今後はただ
図8
とだけ書く。今日はここまで。

Ｂ19～Ｂ27　が　1　になって、他は0であることを確かめてください。
前も言ったけど、色塗る話はずっと後です。
まずは、0と1で　なんちゃってグラフが作れれば目的達成です。

お礼

はい、ここまでできました！
とてもわかりやすいご説明なので、マニュアルを読んでいるみたいです！

回答No.7

QoooL

＃５、６です（今後名乗るのを省略させていただきます）。
それでは、毎日少しずつになるので、回答番号は１０超えると思いますがコマ切れにしていきます。
そんなに巨大なファイルにはならないので安心してください。

他の回答者さんは、遠慮はなさらないでください。連投予告ですみません。

Ｑ1　みたいな小質問番号を付けてくれたら、
Ａ1　みたいに、個別になるべく答えます。

例
Ｑ1　シートの名前付け替え方法は？

■ステップ1
新規ブックを用意し、シートは２つ以上にする。
エクセルのバージョンは何でも良い。記憶を頼りに、2003で行けるように解説しているつもりです。
順番はどちらでも良いので、
　　第1週
　　1-月
と名前を付ける。

■ステップ2
1-月　シートを選択する。この回答＃７からしばらくはずっと同じシートの話。
セルＡ1　に　7/7　と入力する。
勝手に　7月7日　という表示に変わる（変わらなくても気にしない）。

「なんちゃってグラフ」作成に関係ないところのセルの説明は省きます。
つまり、セルＢ2　に　月　と書いているのは、値ではなく　数式で表示させているのですが、
とりあえず、なんちゃってグラフの方が完成する方を優先させます。
セルＡ3～Ａ5も、なんて書き込むかは　ご自由にカスタマイズしてください。
図6　で　Ｂ5　を黄色で塗っているのも、本筋には全然関係ありません。

■ステップ3
セルＡ7　に　8：　と入力する。
勝手に　8:00　に変わるはず（8:00:00になったら教えてください）。

■ステップ4
セルＡ8　に
=A7+5/24/60
と入力する。
8:05　になるはず。
0.336806　のような小数になったときのみ次のステップ5を読む。ステップ6へ進む。

■ステップ5
セルＡ8　を選択。右クリック
　　メニュー　セルの書式設定
　　＞　セルの書式設定　ウィンドウ
　　＞　表示形式　タブ
　　＞　分類で　時刻　を選ぶ
　　　　種類欄で
　　　　　13：30
　　　　など好みの表示方法を選ぶ
　　＞　ＯＫ

■ステップ6
セルＡ8　を選択して、右下の■マークを、
セルＡ151　まで　ドラッグ＆ドロップ
Ａ151　は　20:00　になるはず。
151　じゃなくても良い。40くらいで適当に止めても良い。
あとでカスタマイズする。

■ステップ7
セル　Ｂ3～Ｆ3に　図6　の通り
8:59　9:39　9:06　8:57　9:40
と入力する。

セル　Ｂ4～Ｆ4に　図6　の通り
9:43　10:09　10:01　9:48　10:46
と入力する。

もう既に時刻データを用意してあるなら、５列分
　　値のみ貼り付け
をしても良い。この説明には全然影響しない。
ただし　ステップ3～6　で作った時刻の範囲に収まるデータにすること。
（ステップ3の　8:00　をいじっても良い。）

図7　は　ステップ7までの経過見本

■ステップ8
　　名前を付けて保存
でファイル名を適当に付ける。ここでは
団体客来店時間管理201407test.xls
と呼ぶことにする。

お礼

御返事遅くなりました

＞■ステップ3
セルＡ7　に　8：　と入力する。
勝手に　8:00　に変わるはず（8:00:00になったら教えてくださ
＞い）。

はい、そのようになりました　８：00：00です

回答No.6

QoooL

Ｙ9　の説明が抜けている上に画像が切れていましたね。
=IF($W9>=Y$6,1,0)
です。

お礼

続きをよろしくお願いします！

回答No.5

QoooL

＃３、４です。

全て　Excel2003　でできるはずです。手元にありませんが。

＞　No3とNo4の図は同じものですか？

はい。＃４の図の下に小さく　シート
　　第１週
　　月
　　火
　　水
　　木
　　金
　　土
　　日
と表記していますが、
＃４の図　は　月　シートの内容を表したもの、
＃３の図　は　月～日　シートのオレンジの部分だけを「参照」によって表した、
　第１週　シートのもの、
です。

もう少し完成に近づけるには、
月　シート　が２つ以上あるわけにはいかないので、
　　第１週
　　1-月
　　1-火
　　1-水
　　1-木
　　1-金
　　1-土
　　1-日
　　第２週
　　2-月
　　2-火
　　2-水
　　2-木
　　2-金
　　2-土
　　2-日
　　：
のようなシート構成になるでしょう。
（そして、第２～６週シートは第１週シートの隣りに移動させます。）

今、私の仕事が忙しいので少しずつの説明になります。全て終わるには２～３日かかるでしょう。もしよろしければ締め切らないでおいてください。

ステップ１
月　シートの作り方

この図を図５と呼びます（回答番号と合わせる）。
同じような式が繰り返されている行や列は非表示にしまくっているので、
列や行の位置には注意して、
見えていないところは自由に類推してください。

セルＢ2　に　8：30、
セルＦ3　に
=$B$2+(25+(ABS(RAND()*2-RAND())*30))/24/60
セルＦ4　に
=IF(F3=0,"",F3+(25+(ABS(RAND()*2-RAND())*30))/24/60)
と入れてあるのは、
「8：30からの約１時間半に来店するお客さんの、ダミーデータ」
を手作業で準備するのが面倒だったので、
関数にしているだけです。F3:F4の式を左にもコピーしてください。

昼前後、夜前後に来るお客さんについても、どこかＢ2以外のセルに　18：30　などを用意して、とりあえずＥｘｃｅｌとして機能しているかどうかをダミーで試します。
ただ、RAND()関数が入っている限り、セルの内容を書き換える度にデータも更新されます。
それが嫌なら、３行目と４行目を　値のみの貼り付け　で固定してください。

もちろん、実際に仕事で使うようになったら、３行目と４行目　には実際のデータを入力して使います。

条件付き書式はわかりますか？　これは返答待ちにします。
とりあえず、水色やオレンジに色塗りすることより、セルの中身が　1　か　0　になっていることの方に注目してください。

黄色に塗ったセルの式をどう入れているか、をそれぞれの右隣りのセルに書きました。
コピペしやすいようにここにも列記しておきます。
とりあえず、これと同じセルに同じ式を入れて　オートフィル　することを繰り返してください。

セルＡ7　には
8:00:00
を入れてあります。

Ｂ5
=IF(B3="","",B4-B3)

Ａ8
=A7+5/24/60
=A7+"00:05:00"
（Ａ9では　A8+　）
どっちでもいいです。
何千行も計算しているとビミョーな誤差を生じますけど、営業時間程度なら無視していいです。

なんなら、数字をいじれば、５分おきでなく、１分おきの度数分布表も作れます。

Ｘ列はＡ列を参照しているだけ。

式を入れたときに表示が時刻にならずに小数になるなら、書式設定です。

Ｂ7
=IF(B$3="",0,IF(B$4="","",IF(B$3<=$A7,IF(B$4>=$A7,1,0),0)))
これで、
入店時間以降、退店時間以前なら、1
と判定させています。
不等号に＝を入れるか入れないかで、その時刻を含むかどうかを調整できます。

ここまでオートフィルして作った後に、
（私のバージョンでは、Ｂ列～Ｕ列を確保しているので、１日２０組までの団体客の在店時間を、水色のバーで表すことが可能。）
（もっとこれば、列数を減らすことはできる。今回はあくまでも説明用。）
Ｗ列に時刻ごとの水色の数をカウントさせていきます。

Ｗ7
=SUM($B8:$U8)

さて、失礼ながら、同時には１０組の団体客までしか来ないとしましょう。
Y6:AH6
に　1～10　の数値を書き入れたのは、同時に在店している団体数です。

こんどは　緑のセルの、団体数が、上の　1～10　より大きいか小さいか　を判定させています。

これと同じようなものができたら教えてください。

お礼

ありがとうございます
Qoolさんのご指示どおりにやろうとしたんですが、私のレベル的には結構難しいですね。

図のように一応数値データをある程度入力して
式や関数を入力する箇所には、コピーペーストで貼り付けたりしましたが、結構難しい内容なので、お時間お日にちは結構かかっても構わないので、少しずつ、やることを詳細に書いていただけたら幸いです。

例えば、
提示された図の通りに数値を入力せよ
B4、F20に次の式を貼り付けよ

等のように、１つ１つこのOkwaveの回答を見ながらその通りに再現していけば解決するような形式でお願いできますか？

シート数もたくさん使いますし、最終的に巨大なものができあがりそうですね！

補足

Qoolさん返事遅くて申し訳ありません
本業のほうが忙しくすぐに実行を出来ない状態です、週明けにはご指示のとおりすべて再現できると思います。

回答No.4

QoooL

各日

1の数を数えて度数分布表にする。

お礼

No3とNo4の図は同じものですか？

回答No.3

QoooL

フリーソフトでなく、エクセルの自作の表です。

グラフは一切使っていません。
セルを小さくする・表示倍率を下げることにより、色塗りセル（条件付き書式）を棒グラフっぽく見せているだけです。
オーダーの散布図とは違いますが、こんな感じでも良ければ後で少し詳しく説明します。

何時から何時まで客がいたのかを、他の日別シートで管理しています。
画像をもう一つ貼っておきます。

お礼

ありがとうございます
これは見やすいですね！
ちなみにどのようにすればいいのか教えていただけますか？
エクセル２００３でもできますか？

回答No.2

MackyNo1

No1の回答の添付画像に乱れがあったのでもう一度アップします。

ちなみに、前回の添付画像では「月」の表示部分がY軸と同じ位置でしたので、今度の添付画像はX軸の位置を少し修正してみました。

すなわち、前回の添付画像では「月」と表示されている部分が月曜日の午前0：00に当たりますので、たとえば12：00のデータなら右側にちょうど半分だけ火曜日側の位置にプロットされています。
今回は、この軸を右側に12時間分ずらして表示させるようにしました。

すなわち、横軸で右クリックし「軸の書式設定」の目盛タブで最小値を「1.5」、最大値を「9.4」に設定します。
このように設定すれば、入店時間が12：00を中心に表示できますので、比較的バランスの良いグラフになると思います。

＃これまでの回答で、グラフの作成方法をすべて述べたつもりですが、もしかすると操作の一部が落ちている可能性もありますので、ご不明の点がありましたら、補足で質問して下さい（たとえばグラフの縦軸を9,10、・・・のように通常の数字で表示したいなど）。

お礼

同じ表を再現できましたよ！
ありがとうございました！！

回答No.1

MackyNo1

Excelを使ってご希望のグラフを作成するには、かなりノウハウが必要ですが、以下のような散布図を使った手順でご希望のグラフを作成することができます。

A列に日付、C列に入店時間、D列に退店時間を入力したリストを作成しておきます（添付画像参照）。
B列（B2セル）に以下の式を入力し、X軸の値（数式を入力）を表示させます（セルの表示形式を標準にして下さい）。

=WEEKDAY(A2,2)+C2+1

B列からD列のデータ範囲を選択し、グラフウィザードで散布図を作成します。

このグラフの縦軸部分を選択して右クリックから「軸の書式設定」の目盛タブで最大値をたとえば「24：00」、最小値を「9：00」、目盛間隔を「3：00」補助目盛間隔を「1：00」など適宜設定します。

次に横軸を選択し、右クリックから「軸の書式設定」の目盛タブで最小値を「2」、最大値を「8.9」、目盛間隔を「1」に設定し、さらに表示形式タブでユーザー定義にして種類の欄に「aaa」と入力します。

以上の操作で、添付画像のような各日付ごとの入店時間を基準にした散布図グラフが作成できていると思います。

お礼

ありがとうございます
一週間分のデータを取ってみたので、今作成しております！