| |
▼あい さん：
こんにちは。
投稿が大分わかりやすくなってきましたが・・・、
>以前質問させていただいたのですが、さらに追加がありますので再度質問させていただきます。
関連しているのでリンクを貼ったほうが見ている方がもっと下記の内容を
連想しやすいと思いますよ！！
>Sheet1に下記のようなデータがあります。
>
>A列 B列 C列 D列 E列 F列 G列
>番号 部屋番号 ON日付 ON時刻 OFF日付 OFF時刻 売上
>1 1 2006/05/01 22：30 2006/05/02 09：00 \1000
>2 1 2006/05/02 13：00 2006/05/02 17：05 \800
>3 1 2006/05/02 18：00 2006/05/03 03：10 \2000
>4 2 2006/05/02 15：50 2006/05/02 19：50 \100
>5 2 2006/05/02 20：35 2006/05/03 09：50 \1400
>6 3 2006/05/01 19：00 2006/05/02 05：45 \1100
>7 3 2006/05/02 08：23 2006/05/02 11：39 \800
>8 3 2006/05/02 13：24 2006/05/03 14：58 \3500
>・
>・
>・
前にも書きましたが、せっかく入力データは上記のように明確にされたのですから、
結果もわかりやすく記述してください。
>このデータはサーバから取得したい日付を指定して取ってきているのでA列が何行目まであるかはその時々によって変わります。
>今回はこのデータを元に1時間ごとの利用率を算出したいのです。
>実際の利用率の値が上記のデータの時どうなるかはわかりませんが、考え方は以下のとおりです。
>2006/05/02の16：00〜17：00までをみると、部屋番号1,2,3全て利用されています。そうなるとこの時間帯の利用率は100％となります。
>また2006/05/02の12：00〜13：00の利用率は全ての部屋は利用されていない為0％となります。
>なお1日ごとに利用率を算出し、一覧表示とグラフ表示を考えています。
↑この説明はよかったと思います。
Ａ列から、Ｇ列は、あい さんが提示されたようなデータが入っていた場合
（日付、時間は、シリアル値とします。尚、ソートはされていなくてもよいです）、
標準モジュールに以下のコードを記述してください。
Ｈ列とＩ列は、コードが作業列として使います（2行目以降）。
集計したい日付をセルＨ１にシリアル値で指定して下さい
例。2006/5/2
Ｈ１に日付を指定した後に、mainを実行してみて下さい。
結果の一覧表は、ＪからＬ列に表示します。
'=================================================================
Sub main()
Const 日付 = "$h$1"
Const 一覧表 = "$J$1:$k$24"
Dim idx As Long
Dim 部屋数 As Long
Dim wkadd As String
Dim セル範囲 As Range
Set セル範囲 = Range("a2", Cells(Rows.Count, 1).End(xlUp))
If セル範囲.Row > 1 Then
With セル範囲
wkadd = セル範囲.Offset(0, 1).Address
部屋数 = Evaluate("=sum(1/(countif(" & wkadd & "," & wkadd & ")))")
.Offset(0, 7).Resize(, 2).Formula = _
Array("=MAX(C2+D2," & 日付 & ")", "=MIN(E2+F2," & 日付 & "+1)")
Names.Add "日付", "=" & Range(日付).Address(, , xlR1C1, True)
Names.Add "開始時刻", "=" & .Offset(0, 7).Address(, , xlR1C1, True)
Names.Add "終了時刻", "=" & .Offset(0, 8).Address(, , xlR1C1, True)
With Range(一覧表)
For idx = 1 To 24
.Range("a" & idx).Resize(, 2).Value = Array(TimeSerial(idx - 1, 0, 0), TimeSerial(idx, 0, 0))
Next
.NumberFormat = "h:mm"
.Range("c1").FormulaArray = _
"=SUM(IF((開始時刻>日付+K1)+(終了時刻<日付+J1)>0,0," & _
"(IF(終了時刻>=日付+K1,日付+K1,終了時刻)-IF(開始時刻<=日付+J1,日付+J1,開始時刻))/(K1-J1)))/" & 部屋数
.Range("c1").AutoFill Destination:=.Range("c1:c24"), Type:=xlFillDefault
.Range("c1:c24").NumberFormat = "0.0%"
End With
End With
End If
End Sub
上記のコードは、数式を設定しているだけのコードです。
つまり、数式でも可能と言うことです。
試してみて下さい。
|
|
ichinose
- 06/5/26(金) 14:59 -
