| |
利用率の計算式はそちらでやって下さい。
とりあえず全ての時間帯の、各室の利用状況を表示する表を作る
ところまでのマクロを提示しておきます。
表は I列 以降に作られます。AB:AC列を作業列として使います。
Sub Mk_Room_Schedule()
Dim MxR As Long, Tm As Long
Dim i As Long, j As Long
Dim Get1 As Long, Get2 As Long, MyC As Long
Dim StD As String, Edt As String
Dim MxDy As Date, MiDy As Date
Dim C As Range
Range("B1", Range("B65536").End(xlUp)).AdvancedFilter _
xlFilterCopy, , Range("I1"), True
With Range("I2", Range("I65536").End(xlUp))
.Copy
Range("J1").PasteSpecial , , , True
.ClearContents
End With
MxR = Range("A65536").End(xlUp).Row
With Range("AB2:AB" & MxR)
.Resize(, 2).NumberFormat = "h:mm:ss"
.Formula = _
"=FLOOR($D2,TIMEVALUE(""1:00:00""))"
.Offset(, 1).Formula = _
"=CEILING($F2,TIMEVALUE(""1:00:00""))"
End With
With Application
MxDy = .Max(Range("C2:C" & MxR & ", E2:E" & MxR))
MiDy = .Min(Range("C2:C" & MxR & ", E2:E" & MxR))
End With
Tm = (MxDy - (MiDy - 1)) * 24
With Range("I2")
.Resize(Tm + 1).NumberFormat = "@"
.Value = Format(MiDy, "yyyy/m/d") & TimeSerial(0, 0, 0)
j = 1
For i = 1 To Tm
If i Mod 24 = 0 Then
j = 0: MiDy = MiDy + 1
End If
.Offset(i).Value = Format(MiDy, "yyyy/m/d") & _
TimeSerial(j, 0, 0)
j = j + 1
Next i
End With
With Range("I1")
.Value = "↓日付・時刻; " & .Value & "→"
.EntireColumn.AutoFit
.Select
End With
With Application
For Each C In Range("B2:B" & MxR)
MyC = .Match(C.Value, Rows(1), 0) - 9
Sdt = C.Offset(, 1).Text & C.Offset(, 26).Text
Edt = C.Offset(, 3).Text & C.Offset(, 27).Text
Debug.Print Sdt & " : " & Edt
Get1 = .Match(Sdt, Range("I:I"), 0)
Get2 = .Match(Edt, Range("I:I"), 0)
Range("I" & Get1 & ":I" & Get2).Offset(, MyC) _
.Value = "入室"
Next
End With
End Sub
|
|
Kein
- 06/5/26(金) 16:04 -
