1205380873 入退出時間から横:時間-縦:合計人数 の図を作成したいと思っています。

当方はエクセルと非常に基本的なスクリプトでしたら作成できますが、アイデアがでてきません。
お知恵拝借頂けないでしょうか。

いまあるデータは以下のような入退出データです。
Aさん: 10:05-15:25
Bさん: 13:36-14:25
Cさん: 16:44-17:30
etc.
これを横軸09:00-17:30として縦軸を各時間の合計人数にしたいです。
できれば1分刻みに合計人数の表が作成したいと思っています。
イメージとしては添付のような図になります。

宜しくお願い致します。
以上

回答の条件
  • 1人2回まで
  • 登録:2008/03/13 13:01:15
  • 終了:2008/03/20 13:05:02

回答(3件)

id:Gay_Yahng No.1

Gay_Yahng回答回数724ベストアンサー獲得回数262008/03/13 15:02:03

ポイント27pt

考え方だけですが、

データをセルに入れておきます

A列    B列    C列

Aさん   10:05   15:25

Bさん   13:36   14:25

Cさん   16:44   17:30

 

横軸データを例えば30分ごとに作ります。

     A列    B列    C列    D列   E列  F列・・・

1行                    9:00  9:30  10:00・・17:30

2行   Aさん   10:05   15:25

3行   Bさん   13:36   14:25

4行   Cさん   16:44   17:30

D2を B2<D1<C2 ならば1、そうでなければ0とします。

同様にD列以降の表を作ります。

最後に各列の合計を取ってその時間帯に居る人の合計を取って、グラフにします。

 

id:hiromiarts

解答ありがとうございます。そうですね。30分毎の場合この考え方で作成ますね。11分毎の場合はさらに細かくすればできると思いますので、お手軽に実行する場合はこの方法で実行したいと思います。 

2008/03/18 09:02:58
id:nandedesune No.2

nandedesune回答回数11ベストアンサー獲得回数02008/03/13 16:01:46

ポイント27pt

グラフを作成するためにVBAでデータを変更してみたいと思います。

VBAの使い方はこちら


標準モジュールにそのまま次のコードを貼り付けてください。

>|vb|

Sub Macro()

Dim i As Long

Dim j As Long

Dim myTime

Dim startTime

Dim endTime

Dim p As Integer

myTime = TimeValue("9:00")

For j = 0 To 510

Cells(j + 1, 2).Value = myTime + j / 1440

i = 1

While Cells(i, 1).Value <> ""

p = InStr(1, Cells(i, 1).Value, "-")

startTime = TimeValue(Left(Cells(i, 1).Value, p - 1))

endTime = TimeValue(Mid(Cells(i, 1).Value, p + 1))

If startTime <= myTime + j / 1440 And myTime + j / 1440 <= endTime Then

Cells(j + 1, 3).Value = Cells(j + 1, 3).Value + 1

End If

i = i + 1

Wend

Next j

End Sub

||<


実行すると、A列に

10:05-15:25

13:36-14:25

16:44-17:30

とデータが入っていれば、B列に時間、C列にそのとき出社している人数が入ります。


このデータ(B列とC列)を選択状態にしてグラフの挿入からグラフを作れば意図したグラフができます。

id:hiromiarts

ありがとうございます。VBAはちゃんとさわったことはありませんでしたが、イメージはつきましたので、実行させて頂きます。

またわからないことがあったら別の質問で質問させて頂くかもしれません。

宜しくお願い致します。

2008/03/18 09:11:41
id:kaiton No.3

kaiton回答回数260ベストアンサー獲得回数342008/03/18 12:57:19

ポイント26pt

元データが

A B C
名前 入室 退室
Aさん 10:05 15:25
Bさん 13:36 14:25
Cさん 16:44 17:30
・・・ ・・ ・・

として(分刻みで秒のデータは無しと想定)


E列 F列
時刻 人数
9:00
9:01
・・
17:30

のように9:00-17:30の表を作成(E512セルまで)


F2={SUM(IF($B$2:$B$21<=E2,IF($C$2:$C$21>=E2,1,0),0))} データが20件の例

数式が{}で囲まれているのは、配列数式なので Ctrl+Shift+Enter で確定します。

そしてオートフィルなどでF512までコピーします。

これで、グラフの元データができていると思います。


配列数式なので、データ量が多いと応答時間が遅いかもしれませんので、

普段はオプションで計算方法を手動にしておいた方が良いかもしれません。

id:hiromiarts

さっそっく、実施してみましたが非常に簡単に期待していた図ができました。

ありがとうございます。大変参考になりました。

2008/03/19 12:50:24
  • id:kaiton
    元データがA,B,C列に名前、入室、退室とあって(1行目は見出し)

    E2:E512に9:00~17:30まで1分ごとに
    F2={=SUM(IF($B$2:$B$21<=E2,IF($C$2:$C$21>=E2,1,0),0))}
    前後の {} は配列数式なので Ctrl+Shift+Enter で確定

    20人ぐらいならすぐに計算できましたが、人数が多くなると応答時間が遅いかも?
    計算方法を手動にするなども検討してください。

    こんな回答をしました。
  • id:kaiton
    例示のE列ですが、こちらでは E2=9:00、E3=9:01としてオートフィルで試行データを作ったのですが
    微妙に誤差があるようで、件数がずれる場合があるようです。
    (もしかしたら、でるかなとは思ったのですが..)

    =TIME(9,ROW()-2,0) のようにした方が確実かと思います。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません