人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

入退出時間から横:時間-縦:合計人数 の図を作成したいと思っています。
当方はエクセルと非常に基本的なスクリプトでしたら作成できますが、アイデアがでてきません。
お知恵拝借頂けないでしょうか。

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

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

1205380873
●拡大する

●質問者: hiromiarts
●カテゴリ:コンピュータ 学習・教育
✍キーワード:00 etc. アイデア イメージ エクセル
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● Gay_Yahng
●27ポイント

考え方だけですが、

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

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列以降の表を作ります。

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

◎質問者からの返答

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


2 ● nandedesune
●27ポイント

グラフを作成するために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列)を選択状態にしてグラフの挿入からグラフを作れば意図したグラフができます。

◎質問者からの返答

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

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

宜しくお願い致します。


3 ● kaiton
●26ポイント

元データが

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までコピーします。

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


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

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

◎質問者からの返答

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ