| |
こんにちわ
Sheet1のデータを集計し、Sheet2へ表出力するところまで作ってみました
表の体裁、グラフへの出力は考えてませんのでそこはカスタマイズしてください
Sub Test()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim TCnt() As Long
Dim SDate As Date
Dim EDate As Date
Dim MDate
Dim OnDate
Dim OfDate
Dim OnTime
Dim OfTime
Dim TM As Long
Dim ws As Worksheet
Const RCNT As Integer = 3 '総部屋数、ここでは仮に３としている
Set ws = ThisWorkbook.Worksheets("Sheet1")
SDate = Application.WorksheetFunction.Min(ws.Range(ws.Range("C2"), ws.Range("C65535").End(xlUp)))
EDate = Application.WorksheetFunction.Max(ws.Range(ws.Range("E2"), ws.Range("E65535").End(xlUp)))
MDate = DateDiff("d", SDate, EDate)
ReDim TCnt(MDate, 23)
For i = 2 To ws.Range("C65535").End(xlUp).Row
OnDate = DateDiff("d", SDate, ws.Cells(i, 3))
OfDate = DateDiff("d", SDate, ws.Cells(i, 5))
For j = OnDate To OfDate
If j = OnDate Then
OnTime = Hour(ws.Cells(i, 4))
Else
OnTime = 0
End If
If j = OfDate Then
OfTime = Hour(ws.Cells(i, 6))
Else
OfTime = 23
End If
For k = OnTime To OfTime
If OnDate = OfDate And OnTime = OfTime Then
TM = Minute(ws.Cells(i, 6)) - Minute(ws.Cells(i, 4))
ElseIf j = OnDate And k = OnTime Then
TM = Minute(ws.Cells(i, 4))
ElseIf j = OfDate And k = OfTime Then
TM = Minute(ws.Cells(i, 6))
Else
TM = 60
End If
TCnt(j, k) = TCnt(j, k) + TM
Next k
Next j
Next i
Set ws = Nothing
Set ws = ThisWorkbook.Worksheets("Sheet2")
For j = 0 To MDate
ws.Cells(j + 2, 1) = DateAdd("d", j, SDate)
For k = 0 To 23
ws.Cells(j + 2, k + 2) = TCnt(j, k) / (60 * RCNT) * 100
Next k
Next j
Set ws = Nothing
End Sub
|
|
ハト
- 06/5/26(金) 14:50 -
