読者です 読者をやめる 読者になる 読者になる

備忘録2

' H列(氏名)が重複している場合、以下の優先度に従って行を抽出し、
' 別シートのコピーする
' 条件1:AJ列の値が高い行を優先
' 条件2:AJ列が同じ値であった場合はAK列の高い行を優先
' 条件3:AJ列・AK列ともに同じ値であった場合は上の行を優先
Sub 集計()
'シート名を取得
Dim tmpSheet As Worksheet
Set tmpSheet = Worksheets(Sheets(1).Name)

'作成対象のシートが存在すれば削除しておく
Dim tmpSheet2 As Worksheet
For Each tmpSheet2 In Worksheets
If tmpSheet2.Name = Sheets(1).Name & "結果" Then
Application.DisplayAlerts = False
tmpSheet2.Delete
Application.DisplayAlerts = True
End If
Next tmpSheet2

Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Sheets(1).Name & "結果"
Set tmpSheet2 = Worksheets(ActiveSheet.Name)

'ヘッダー行をコピー
tmpSheet.Rows(1).Copy
tmpSheet2.Rows(1).PasteSpecial

'一意の氏名リストを格納する
Dim nameList As New Collection

'氏名を一意になるようにnameListに取得
On Error Resume Next
For i = 1 To 200
nameList.Add tmpSheet.Cells(i + 1, 8).Value, tmpSheet.Cells(i + 1, 8).Value
Next
On Error GoTo 0

'コピー先のシートの行を保持
Dim cnt As Integer
cnt = 1

'1行目から200行目まで、値を取得してコピーする
'ただし同じ氏名の行がある場合は条件に従って比較し、優先度の高い行をコピーする
For i = 1 To nameList.Count
'元シートを検索
Dim nameCnt As Integer
nameCnt = WorksheetFunction.CountIf(tmpSheet.Range("H2:H201"), nameList(i))

'元シートに値がある場合のみ実行
If nameCnt > 0 Then
'元シートに値が複数ある場合
If nameCnt > 1 Then

Dim foundCell As Range
Dim firstCell As Range
Dim targetRow As Integer
Dim valFirstAJ As String
Dim valFirstAK As String

'最初に検索されたセルの行の、AJ列とAK列の値を取得
Set firstCell = tmpSheet.Range("H2:H201").Find(what:=nameList(i))
valFirstAJ = tmpSheet.Cells(firstCell.Row, 36).Value
valFirstAK = tmpSheet.Cells(firstCell.Row, 37).Value

'2つめ以降に検索されたセルの行の、AJ列をAK列の値を取得。その後、比較して優先度の最も高い行をコピー
For j = 0 To nameCnt
Set foundCell = tmpSheet.Range("H2:H201").FindNext(firstCell)
valFoundAJ = tmpSheet.Cells(foundCell.Row, 36).Value
valFoundAK = tmpSheet.Cells(foundCell.Row, 37).Value

'AJ列とAK列の値を比較して、どの行を採用するか決定する
If valFirstAJ > valFoundAJ Then
targetRow = firstCell.Row
ElseIf valFirstAJ < valFoundAJ Then
targetRow = foundCell.Row
ElseIf valFirstAJ = valFoundAJ Then
If valFirstAK > valFoundAK Then
targetRow = firstCell.Row
ElseIf valFirstAK < valFoundAK Then
targetRow = foundCell.Row
ElseIf valFirstAK = valFoundAK Then
targetRow = firstCell.Row
End If
End If
Next

tmpSheet.Rows(targetRow).Copy
tmpSheet2.Rows(cnt + 1).PasteSpecial
cnt = cnt + 1
Else
'元シートに値が一つのみの場合はそのままコピーする
tmpSheet.Rows(i + 1).Copy
tmpSheet2.Rows(cnt + 1).PasteSpecial
cnt = cnt + 1

End If

End If


Next

End Sub

備忘録

' 共通変数・定数定義
Private sheetTejunsyo As Worksheet
Private sheetYotei As Worksheet
Private sheetJisseki As Worksheet
Private sheetSyuukei As Worksheet
Private sheetKyuujitsu As Worksheet
Private sheetHayamihyo As Worksheet
Private sheetZangyoHayamihyo As Worksheet
Private sheetHoliday As Worksheet

Private statusCodeAsc As Variant
Private statusNameAsc As Variant
Private statusCodeCopc As Variant
Private statusNameCopc As Variant

' 入力された年月に基づき、シート【予定】【実績】【集計】【残業シフト】に以下を設定する
'  1行目 → 日付
'  2行目 → 曜日
Private Sub 予定表を作成する()

Dim lastDay As Date
Dim inputDay As Date
Dim startDayCell As String

'初期値設定
Set sheetYotei = Worksheets("【予定】")
Set sheetJisseki = Worksheets("【実績】")
Set sheetSyuukei = Worksheets("【集計】")
Set sheetKyuujitsu = Worksheets("【残業シフト】")
Set sheetHayamihyo = Worksheets("【早見表】")
Set sheetZangyoHayamihyo = Worksheets("【残業時間早見表】")
startDayCell = "H1"

'入力値のチェック
If Range("B7").Value = "" Or Range("D7").Value = "" Then
MsgBox ("日付を正しく入力してください")
Exit Sub
End If

'入力された年月の末日(=入力された年月の翌月の前日)を取得
inputDay = CDate(Range("B7").Value & "/" & Range("D7").Value & "/" & "01")
lastDay = DateSerial(Year(inputDay), Month(inputDay) + 1, 1) - 1

'入力された日付から日と曜日の情報を取得してシート【予定】にセット
Dim cnt As Integer
Dim cnt2 As Integer
cnt = CInt(Day(lastDay))
cnt2 = 0

For i = 1 To 31
If i > cnt Then
'【予定】余計な日付・曜日をクリア
sheetYotei.Cells(1, i + cnt2 + 7).Value = ""
sheetYotei.Cells(2, i + cnt2 + 7).Value = ""
'【実績】余計な日付・曜日をクリア
sheetJisseki.Cells(1, i + cnt2 + 7).Value = ""
sheetJisseki.Cells(2, i + cnt2 + 7).Value = ""
'【集計】余計な日付・曜日をクリア
sheetSyuukei.Cells(1, i + cnt2 + 7).Value = ""
sheetSyuukei.Cells(2, i + cnt2 + 7).Value = ""
'【残業シフト】余計な日付・曜日をクリア
sheetKyuujitsu.Cells(1, i + cnt2 + 7).Value = ""
sheetKyuujitsu.Cells(2, i + cnt2 + 7).Value = ""
'【早見表】余計な日付・曜日をクリア
sheetHayamihyo.Cells(1, i + 7).Value = ""
sheetHayamihyo.Cells(2, i + 7).Value = ""
'【残業時間早見表】余計な日付・曜日をクリア
sheetZangyoHayamihyo.Cells(1, i + 7).Value = ""
sheetZangyoHayamihyo.Cells(2, i + 7).Value = ""
Else
'【予定】日付・曜日をセット
sheetYotei.Cells(1, i + cnt2 + 7).Value = i
sheetYotei.Cells(2, i + cnt2 + 7).Value = WeekdayName(Weekday(inputDay))
'【実績】日付・曜日をセット
sheetJisseki.Cells(1, i + cnt2 + 7).Value = i
sheetJisseki.Cells(2, i + cnt2 + 7).Value = WeekdayName(Weekday(inputDay))
'【集計】日付・曜日をセット
sheetSyuukei.Cells(1, i + cnt2 + 7).Value = i
sheetSyuukei.Cells(2, i + cnt2 + 7).Value = WeekdayName(Weekday(inputDay))
'【残業シフト】日付・曜日をセット
sheetKyuujitsu.Cells(1, i + cnt2 + 7).Value = i
sheetKyuujitsu.Cells(2, i + cnt2 + 7).Value = WeekdayName(Weekday(inputDay))
'【早見表】日付・曜日をセット
sheetHayamihyo.Cells(1, i + 7).Value = i
sheetHayamihyo.Cells(2, i + 7).Value = WeekdayName(Weekday(inputDay))
'【残業時間早見表】日付・曜日をセット
sheetZangyoHayamihyo.Cells(1, i + 7).Value = i
sheetZangyoHayamihyo.Cells(2, i + 7).Value = WeekdayName(Weekday(inputDay))

inputDay = DateAdd("d", 1, inputDay)
End If
cnt2 = cnt2 + 1
Next

MsgBox ("予定表を作成しました")

End Sub

' 会社名・期・役職・氏名が入力されているすべての行の
' 勤務開始時間・終了時間、ステータスを入力する
Private Sub 勤怠予定の初期化()

'初期値を設定
Set sheetTejunsyo = Worksheets("手順書")
Set sheetYotei = Worksheets("【予定】")
Set sheetSyuukei = Worksheets("【集計】")
Set sheetKyuujitsu = Worksheets("【残業シフト】")

sheetYotei.Range("H3").Value = "001"
sheetYotei.Range("I3").Value = "110"
sheetYotei.Range("H4").Value = "09"
sheetYotei.Range("I4").Value = "00"
sheetYotei.Range("H5").Value = "~"
sheetYotei.Range("H6").Value = "17"
sheetYotei.Range("I6").Value = "00"

Dim cnt As Integer
Dim cnt2 As Integer

Dim strYear As String
Dim strMon As String

strYear = sheetTejunsyo.Cells(7, 2).Value
strMon = sheetTejunsyo.Cells(7, 4).Value

cnt2 = 0

'1行目の初期値を入力する
For i = 1 To 31
If sheetYotei.Cells(1, i + cnt2 + 7) = "" Then
'余計な値をクリア
sheetYotei.Cells(3, i + cnt2 + 7).Value = ""
sheetYotei.Cells(3, i + cnt2 + 8).Value = ""
sheetYotei.Cells(4, i + cnt2 + 7).Value = ""
sheetYotei.Cells(4, i + cnt2 + 8).Value = ""
sheetYotei.Cells(5, i + cnt2 + 7).Value = ""
sheetYotei.Cells(6, i + cnt2 + 7).Value = ""
sheetYotei.Cells(6, i + cnt2 + 8).Value = ""
ElseIf sheetYotei.Cells(2, i + cnt2 + 7) = "土曜日" Or sheetYotei.Cells(2, i + cnt2 + 7) = "日曜日" Then
'土日はブランクをセット
sheetYotei.Cells(3, i + cnt2 + 7).Value = ""
sheetYotei.Cells(3, i + cnt2 + 8).Value = ""
sheetYotei.Cells(4, i + cnt2 + 7).Value = ""
sheetYotei.Cells(4, i + cnt2 + 8).Value = ""
sheetYotei.Cells(5, i + cnt2 + 7).Value = ""
sheetYotei.Cells(6, i + cnt2 + 7).Value = ""
sheetYotei.Cells(6, i + cnt2 + 8).Value = ""
ElseIf isHoliday(strYear & "/" & Format(strMon, "00") & "/" & Format(sheetYotei.Cells(1, i + cnt2 + 7), "00")) Then
'祝日はブランクをセット
sheetYotei.Cells(3, i + cnt2 + 7).Value = ""
sheetYotei.Cells(3, i + cnt2 + 8).Value = ""
sheetYotei.Cells(4, i + cnt2 + 7).Value = ""
sheetYotei.Cells(4, i + cnt2 + 8).Value = ""
sheetYotei.Cells(5, i + cnt2 + 7).Value = ""
sheetYotei.Cells(6, i + cnt2 + 7).Value = ""
sheetYotei.Cells(6, i + cnt2 + 8).Value = ""
Else
'値をセット
sheetYotei.Cells(3, i + cnt2 + 7).Value = "001"
sheetYotei.Cells(3, i + cnt2 + 8).Value = "110"
sheetYotei.Cells(4, i + cnt2 + 7).Value = "09"
sheetYotei.Cells(4, i + cnt2 + 8).Value = "00"
sheetYotei.Cells(5, i + cnt2 + 7).Value = "~"
sheetYotei.Cells(6, i + cnt2 + 7).Value = "17"
sheetYotei.Cells(6, i + cnt2 + 8).Value = "00"
End If
cnt2 = cnt2 + 1
Next

cnt2 = 0

'1行目の内容を200行分コピーする(氏名の入力がある行のみ)
For i = 1 To 200
If i <> 1 Then
'氏名の入力がある場合のみコピーする。入力がない場合はクリア
If sheetYotei.Cells(i + cnt2 + 2, 7) <> "" Then
sheetYotei.Range(sheetYotei.Cells(i + cnt2 + 2, 8), sheetYotei.Cells(i + cnt2 + 5, 69)).Value = sheetYotei.Range("H3:BQ6").Value
Else
sheetYotei.Range(sheetYotei.Cells(i + cnt2 + 2, 8), sheetYotei.Cells(i + cnt2 + 5, 69)).Value = ""
End If
End If
cnt2 = cnt2 + 3
Next

'【集計】のA~D列を【予定】からコピーする
sheetSyuukei.Range("A3:G802").Value = sheetYotei.Range("A3:G802").Value
'【残業シフト】のA~D列を【予定】からコピーする
sheetKyuujitsu.Range("A3:G802").Value = sheetYotei.Range("A3:G802").Value

MsgBox ("勤怠予定の初期化が完了しました")

End Sub

' シート【残業シフト】の内容を、シート【予定】に反映させる
Private Sub 残業シフトの反映()

Dim cnt As Integer
Dim cnt2 As Integer

'初期値設定
Set sheetYotei = Worksheets("【予定】")
Set sheetKyuujitsu = Worksheets("【残業シフト】")

cnt = 0
cnt2 = 0

For i = 1 To 200
If sheetYotei.Cells(i + cnt2 + 2, 7) <> "" Then
For j = 1 To 31
'「○」の場合は終了時刻が「18:00」、「※」の場合は終了時刻が「17:30」に設定する
If sheetKyuujitsu.Cells(i + cnt2 + 2, j + cnt + 7).Value = "○" Then
sheetYotei.Cells(i + cnt2 + 4, j + cnt + 7).Value = "~"
sheetYotei.Cells(i + cnt2 + 5, j + cnt + 7).Value = "18"
sheetYotei.Cells(i + cnt2 + 5, j + cnt + 8).Value = "00"
ElseIf sheetKyuujitsu.Cells(i + cnt2 + 2, j + cnt + 7).Value = "※" Then
sheetYotei.Cells(i + cnt2 + 4, j + cnt + 7).Value = "~"
sheetYotei.Cells(i + cnt2 + 5, j + cnt + 7).Value = "17"
sheetYotei.Cells(i + cnt2 + 5, j + cnt + 8).Value = "30"
End If
cnt = cnt + 1
Next
End If
cnt = 0
cnt2 = cnt2 + 3
Next

MsgBox ("残業シフトの反映が完了しました")

End Sub

' シート【予定】の入力値が正しいかチェックする
Private Sub チェック1()
Call チェック("REL", "【予定】")
End Sub

' シート【予定】の入力値が正しいかチェックする
Private Sub チェック2()
Call チェック("NTT", "【予定】")
End Sub

' シート【予定】の入力値が正しいかチェックする
Private Sub チェック3()
Call チェック("TMJ", "【予定】")
End Sub

' シート【実績】の入力値が正しいかチェックする
Private Sub 実績チェック1()
Call チェック("REL", "【実績】")
End Sub

' シート【実績】の入力値が正しいかチェックする
Private Sub 実績チェック2()
Call チェック("NTT", "【実績】")
End Sub

' シート【実績】の入力値が正しいかチェックする
Private Sub 実績チェック3()
Call チェック("TMJ", "【実績】")
End Sub

' シート【予定/実績】の入力値が正しいかチェックする
Private Function チェック(company As String, sName As String)

Dim cnt As Integer
Dim cnt2 As Integer
Dim rangeFrom As String
Dim rangeTo As String

Dim statusAsc As String
Dim timeFrom As String
Dim timeTo As String
Dim minFrom As String
Dim minTo As String

Dim tmpStrDate As String

'初期値設定
Set sheetName = Worksheets(sName)

'ステータスを設定
Call setStatus

cnt = 0
cnt2 = 0

For i = 1 To 200

'該当の会社の行のみチェックする
If sheetName.Cells(i + cnt2 + 2, 1) = company Then

For j = 1 To 31
'範囲指定用
rangeFrom = sheetName.Cells(i + cnt2 + 2, j + cnt + 7).Address(RowAbsolute:=False, ColumnAbsolute:=False)
rangeTo = sheetName.Cells(i + cnt2 + 5, j + cnt + 8).Address(RowAbsolute:=False, ColumnAbsolute:=False)

'値を取得
statusAsc = sheetName.Cells(i + cnt2 + 2, j + cnt + 7).Value
statusCopc = sheetName.Cells(i + cnt2 + 2, j + cnt + 8).Value
timeFrom = sheetName.Cells(i + cnt2 + 3, j + cnt + 7).Value
timeTo = sheetName.Cells(i + cnt2 + 5, j + cnt + 7).Value
minFrom = sheetName.Cells(i + cnt2 + 3, j + cnt + 8).Value
minTo = sheetName.Cells(i + cnt2 + 5, j + cnt + 8).Value

'日付を取得
tmpStrDate = sheetName.Cells(1, j + cnt + 7).Value

'-----------------------------------------
'値のチェック開始
'-----------------------------------------

'単項目チェック(ステータス)
'入力値が定義されたステータスに存在しない場合はエラーとする

'ASC
Dim varResult As Variant
varResult = Filter(statusCodeAsc, status)
If statusAsc <> "" And UBound(varResult) = -1 Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ASCステータスを正しく入力してください")
Exit Function
End If

'COPC
varResult = Filter(statusCodeCopc, status)
If statusCopc <> "" And UBound(varResult) = -1 Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("COPCステータスを正しく入力してください")
Exit Function
End If

'複数項目 相関チェック

'チェック1 ステータスが通常の場合に時間のいずれかがブランクならエラー(ASC・COPCいずれも)
If statusAsc = "001" Or statusCopc = "110" Then
If timeFrom = "" Or timeTo = "" Or minFrom = "" Or minTo = "" Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ステータスが通常の場合は、開始時刻・終了時刻を全て入力してください")
Exit Function
End If
End If

'チェック2 欠勤なのに時間が入力されている場合はエラー(ASC:002、003、004 COPC:100、150)
If statusAsc = "002" Or statusAsc = "003" Or statusAsc = "004" Then
If timeFrom <> "" Or timeTo <> "" Or minFrom <> "" Or minTo <> "" Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ステータスが欠勤の場合は、開始時刻・終了時刻を入力しないでください")
Exit Function
End If
End If

If statusCopc = "100" Or statusCopc = "150" Then
If timeFrom <> "" Or timeTo <> "" Or minFrom <> "" Or minTo <> "" Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ステータスが欠勤の場合は、開始時刻・終了時刻を入力しないでください")
Exit Function
End If
End If

'チェック3 遅刻なのに定時出社より早い時間になっている場合はエラー(ASC:005 COPC:120、140)
If statusAsc = "005" Then
If getCorrectDatetime(tmpStrDate, "09", "00") >= getCorrectDatetime(tmpStrDate, Format(timeFrom, "00"), Format(minFrom, "00")) Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ステータスが遅刻の場合は、開始時刻を定時以降にしてください")
Exit Function
End If
End If

If statusCopc = "120" Or statusCopc = "140" Then
If getCorrectDatetime(tmpStrDate, "09", "00") >= getCorrectDatetime(tmpStrDate, Format(timeFrom, "00"), Format(minFrom, "00")) Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ステータスが遅刻の場合は、開始時刻を定時以降にしてください")
Exit Function
End If
End If

'チェック4 早退なのに定時退社より遅い時間になっている場合はエラー(ASC:006 COPC:130、140)
If statusAsc = "006" Then
If getCorrectDatetime(tmpStrDate, "17", "00") <= getCorrectDatetime(tmpStrDate, Format(timeTo, "00"), Format(minTo, "00")) Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ステータスが早退の場合は、終了時刻を定時以前にしてください")
Exit Function
End If
End If

If statusCopc = "130" Or statusCopc = "140" Then
If getCorrectDatetime(tmpStrDate, "17", "00") <= getCorrectDatetime(tmpStrDate, Format(timeTo, "00"), Format(minTo, "00")) Then
sheetName.Activate
sheetName.Range(rangeFrom & ":" & rangeTo).Select
MsgBox ("ステータスが早退の場合は、終了時刻を定時以前にしてください")
Exit Function
End If
End If

'★★★ このあたりにチェックを追加していく ★★★

cnt = cnt + 1
Next

End If

cnt = 0
cnt2 = cnt2 + 3
Next

MsgBox (company & "のチェックが完了しました")

End Function

' シート【予定】の内容を、シート【実績】にコピーする
Private Sub 予定を確定する()

'初期値を設定
Set sheetYotei = Worksheets("【予定】")
Set sheetJisseki = Worksheets("【実績】")

'予定を実績にコピーする
sheetJisseki.Range("A3:BQ802").Value = sheetYotei.Range("A3:BQ802").Value

MsgBox ("予定の確定が完了しました")

End Sub

' 集計結果を表示する
Private Sub 実績を集計する()

Dim cnt As Integer
Dim cnt2 As Integer
Dim rangeFrom As String
Dim rangeTo As String

Dim yStatusAsc As String
Dim yStatusCopc As String
Dim yTimeFrom As String
Dim yTimeTo As String
Dim yMinFrom As String
Dim yMinTo As String
Dim yPosition As String

Dim jStatusAsc As String
Dim jStatusCopc As String
Dim jTimeFrom As String
Dim jTimeTo As String
Dim jMinFrom As String
Dim jMinTo As String

Dim stdTimeFromAsc As Date
Dim stdTimeToAsc As Date
Dim stdTimeFromCopc As Date
Dim stdTimeToCopc As Date

Dim jStatusCountAsc1 As Integer
Dim jStatusCountAsc2 As Integer
Dim jStatusCountAsc001 As Integer
Dim jStatusCountAsc002 As Integer
Dim jStatusCountAsc003 As Integer
Dim jStatusCountAsc005 As Integer
Dim jStatusCountAsc006 As Integer
Dim jStatusCountAsc007 As Integer
Dim jStatusCountAsc008 As Integer
Dim jStatusCountAsc010 As Integer
Dim jStatusCountAsc011 As Integer
Dim jStatusCountAsc012 As Integer
Dim yStatusCountCopc As Integer
Dim yStatusCountCopcSp As Integer
Dim okCntAsc As Integer
Dim okCntCopc As Integer
Dim ngCntCopcSp As Integer

Dim lteCntAsc As Date
Dim elyCntAsc As Date
Dim lteCntCopc As Date
Dim elyCntCopc As Date

Dim lteCntTotalAsc As Date
Dim elyCntTotalAsc As Date
Dim lteElyCntTotalAsc As Date

Dim weekendTotalCount As Integer

Dim strYear As String
Dim strMon As String

Dim lteTotal As Integer
Dim elyTotal As Integer
Dim lteElyTotal As Integer

'初期値設定
Set sheetTejunsyo = Worksheets("手順書")
Set sheetYotei = Worksheets("【予定】")
Set sheetJisseki = Worksheets("【実績】")
Set sheetSyuukei = Worksheets("【集計】")

strYear = sheetTejunsyo.Cells(7, 2).Value
strMon = sheetTejunsyo.Cells(7, 4).Value

'ステータスを設定
Call setStatus

cnt = 0
cnt2 = 0

lteTotal = 0
elyTotal = 0
lteElyTotal = 0

For i = 1 To 200
For j = 1 To 31
'範囲指定用
rangeFrom = sheetYotei.Cells(i + cnt2 + 2, j + cnt + 7).Address(RowAbsolute:=False, ColumnAbsolute:=False)
rangeTo = sheetYotei.Cells(i + cnt2 + 5, j + cnt + 8).Address(RowAbsolute:=False, ColumnAbsolute:=False)

'値を取得(予定)
yStatusAsc = sheetYotei.Cells(i + cnt2 + 2, j + cnt + 7).Value
yStatusCopc = sheetYotei.Cells(i + cnt2 + 2, j + cnt + 8).Value
yTimeFrom = sheetYotei.Cells(i + cnt2 + 3, j + cnt + 7).Value
yTimeTo = sheetYotei.Cells(i + cnt2 + 5, j + cnt + 7).Value
yMinFrom = sheetYotei.Cells(i + cnt2 + 3, j + cnt + 8).Value
yMinTo = sheetYotei.Cells(i + cnt2 + 5, j + cnt + 8).Value
yPosition = sheetYotei.Cells(i + cnt2 + 2, 2).Value

'値を取得(実績)
jStatusAsc = sheetJisseki.Cells(i + cnt2 + 2, j + cnt + 7).Value
jStatusCopc = sheetJisseki.Cells(i + cnt2 + 2, j + cnt + 8).Value
jTimeFrom = sheetJisseki.Cells(i + cnt2 + 3, j + cnt + 7).Value
jTimeTo = sheetJisseki.Cells(i + cnt2 + 5, j + cnt + 7).Value
jMinFrom = sheetJisseki.Cells(i + cnt2 + 3, j + cnt + 8).Value
jMinTo = sheetJisseki.Cells(i + cnt2 + 5, j + cnt + 8).Value

'-----------------------------------------
'OK/NG 判定処理開始
'-----------------------------------------

'予定と実績に入力がない場合は集計対象外のためブランクを設定
If yStatusAsc = "" And yStatusCopc = "" And jStatusAsc = "" And jStatusCopc = "" Then
'ASC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 7).Value = ""
'COPC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = ""
Else

'ルール1:(ASC)予定が正出勤で実績が正出勤以外ならNG
If yStatusAsc = "001" And jStatusAsc <> "001" Then
'ASC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 7).Value = "NG"
'★★★ このあたりにElseIfで判定処理を追加していく ★★★
Else
'ASC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 7).Value = "OK"
End If

'ルール2:(COPC)予定が正出勤で実績が正出勤以外ならNG
If yStatusCopc = "110" And jStatusCopc <> "110" Then
'COPC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = "NG"
ElseIf yStatusCopc = "100" Then
'COPC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = ""
'-----例)予定が130且つ実績が140の場合はNG-----
ElseIf yStatusCopc = "130" And jStatusCopc = "140" Then
'COPC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = "NG"
'-----例)ここまで-----
'★★★ このあたりにElseIfで判定処理を追加していく ★★★
Else
'COPC
sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = "OK"
End If

'ブランクのセルはOKとする(土日の処理などは今後詰める必要あり)
' If sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 7).Value = "" Then
' sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 7).Value = "OK"
' End If
'
' If sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = "" Then
' sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = "OK"
' End If

End If

'-----------------------------------------
'遅刻・早退 計算処理開始
'-----------------------------------------

'予定・実績の時間すべてに入力がある場合のみ計算
If yTimeFrom <> "" And yMinFrom <> "" And yTimeTo <> "" And yMinTo <> "" And _
jTimeFrom <> "" And jMinFrom <> "" And jTimeTo <> "" And jMinTo <> "" Then

'日付を取得
Dim tmpStrDate As String
tmpStrDate = sheetYotei.Cells(1, j + cnt + 7).Value

'基準時刻を設定(通常日は9~17時、土日祝は10~18時)
If sheetSyuukei.Cells(2, j + cnt + 7).Value = "土曜日" Or sheetSyuukei.Cells(2, j + cnt + 7).Value = "日曜日" Then
stdTimeFromAsc = getCorrectDatetime(tmpStrDate, "10", "00") 'ASC開始時刻は固定
stdTimeToAsc = getCorrectDatetime(tmpStrDate, "18", "00") 'ASC終了時刻は固定
ElseIf isHoliday(strYear & "/" & Format(strMon, "00") & "/" & Format(sheetSyuukei.Cells(1, j + cnt + 7), "00")) Then
stdTimeFromAsc = getCorrectDatetime(tmpStrDate, "10", "00") 'ASC開始時刻は固定
stdTimeToAsc = getCorrectDatetime(tmpStrDate, "18", "00") 'ASC終了時刻は固定
Else
stdTimeFromAsc = getCorrectDatetime(tmpStrDate, "09", "00") 'ASC開始時刻は固定
stdTimeToAsc = getCorrectDatetime(tmpStrDate, "17", "00") 'ASC終了時刻は固定
End If

stdTimeFromCopc = getCorrectDatetime(tmpStrDate, Format(yTimeFrom, "00"), Format(yMinFrom, "00")) 'COPC開始時刻は予定の入力値
stdTimeToCopc = getCorrectDatetime(tmpStrDate, Format(yTimeTo, "00"), Format(yMinTo, "00")) 'COPC開始時刻は予定の入力値

Dim tmpDate As Date

tmpDate = getCorrectDatetime(tmpStrDate, jTimeFrom, jMinFrom)
If tmpDate > stdTimeFromAsc Then
lteCntAsc = tmpDate - stdTimeFromAsc 'ASC遅刻時間を計算
sheetSyuukei.Cells(i + cnt2 + 4, j + cnt + 7).Value = CStr(lteCntAsc)
Else
sheetSyuukei.Cells(i + cnt2 + 4, j + cnt + 7).Value = CStr("00:00:00")
End If

tmpDate = getCorrectDatetime(tmpStrDate, jTimeTo, jMinTo)
If tmpDate < stdTimeToAsc Then
elyCntAsc = tmpDate - stdTimeToAsc 'ASC早退時間を計算
sheetSyuukei.Cells(i + cnt2 + 5, j + cnt + 7).Value = CStr(elyCntAsc)
Else
sheetSyuukei.Cells(i + cnt2 + 5, j + cnt + 7).Value = CStr("00:00:00")
End If

tmpDate = getCorrectDatetime(tmpStrDate, jTimeFrom, jMinFrom)
If tmpDate > stdTimeFromCopc Then
lteCntCopc = tmpDate - stdTimeFromCopc 'COPC遅刻時間を計算
sheetSyuukei.Cells(i + cnt2 + 4, j + cnt + 8).Value = CStr(lteCntCopc)
Else
sheetSyuukei.Cells(i + cnt2 + 4, j + cnt + 8).Value = CStr("00:00:00")
End If

tmpDate = getCorrectDatetime(tmpStrDate, jTimeTo, jMinTo)
If tmpDate < stdTimeToCopc Then
elyCntCopc = tmpDate - stdTimeToCopc 'COPC早退時間を計算
sheetSyuukei.Cells(i + cnt2 + 5, j + cnt + 8).Value = CStr(elyCntCopc)
Else
sheetSyuukei.Cells(i + cnt2 + 5, j + cnt + 8).Value = CStr("00:00:00")
End If

Else
sheetSyuukei.Cells(i + cnt2 + 4, j + cnt + 7).Value = ""
sheetSyuukei.Cells(i + cnt2 + 5, j + cnt + 7).Value = ""
sheetSyuukei.Cells(i + cnt2 + 4, j + cnt + 8).Value = ""
sheetSyuukei.Cells(i + cnt2 + 5, j + cnt + 8).Value = ""

End If

'-----------------------------------------
'個人の集計パラメータ算出
'-----------------------------------------

'ASCの当欠当有出勤率の分母をカウント
If jStatusAsc = "001" Or jStatusAsc = "005" Or jStatusAsc = "006" Or jStatusAsc = "007" Or jStatusAsc = "008" Or _
jStatusAsc = "010" Or jStatusAsc = "011" Or jStatusAsc = "012" Then
jStatusCountAsc1 = jStatusCountAsc1 + 1
End If

'COPCの欠勤率の分母をカウント
If yStatusCopc = "110" Then
yStatusCountCopc = yStatusCountCopc + 1
'トータルの欠勤率計算用
If yPosition = "SP" Then
yStatusCountCopcSp = yStatusCountCopcSp + 1
End If
End If

'ASCの当欠当有出勤率の分子をカウント
If jStatusAsc = "001" Or jStatusAsc = "005" Or jStatusAsc = "006" Or jStatusAsc = "010" Or jStatusAsc = "011" Or jStatusAsc = "012" Then
jStatusCountAsc2 = jStatusCountAsc2 + 1
End If

'COPCの欠勤率の分子をカウント
If sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = "OK" Then
okCntCopc = okCntCopc + 1
ElseIf sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 8).Value = "NG" Then
'トータルの欠勤率計算用
If yPosition = "SP" Then
ngCntCopcSp = ngCntCopcSp + 1
End If
End If

'ASCのステータス毎の数をカウント
If jStatusAsc = "001" Then
jStatusCountAsc001 = jStatusCountAsc001 + 1
End If
If jStatusAsc = "002" Then
jStatusCountAsc002 = jStatusCountAsc002 + 1
End If
If jStatusAsc = "003" Then
jStatusCountAsc003 = jStatusCountAsc003 + 1
End If
If jStatusAsc = "005" Then
jStatusCountAsc005 = jStatusCountAsc005 + 1
End If
If jStatusAsc = "006" Then
jStatusCountAsc006 = jStatusCountAsc006 + 1
End If
If jStatusAsc = "007" Then
jStatusCountAsc007 = jStatusCountAsc007 + 1
End If
If jStatusAsc = "008" Then
jStatusCountAsc008 = jStatusCountAsc008 + 1
End If
If jStatusAsc = "010" Then
jStatusCountAsc010 = jStatusCountAsc010 + 1
End If
If jStatusAsc = "011" Then
jStatusCountAsc011 = jStatusCountAsc011 + 1
End If
If jStatusAsc = "012" Then
jStatusCountAsc012 = jStatusCountAsc012 + 1
End If

'ASCの遅刻時間を計算(当日遅刻のみ集計対象とする)
If jStatusAsc = "010" Then
lteCntTotalAsc = DateAdd("h", Hour(lteCntAsc), lteCntTotalAsc)
lteCntTotalAsc = DateAdd("n", Minute(lteCntAsc), lteCntTotalAsc)
End If

'ASCの早退時間を計算(当日早退のみ集計対象とする)
If jStatusAsc = "011" Then
elyCntTotalAsc = DateAdd("h", Hour(elyCntAsc), elyCntTotalAsc)
elyCntTotalAsc = DateAdd("n", Minute(elyCntAsc), elyCntTotalAsc)
End If

'ASCの遅刻・早退時間を計算その1(当日遅刻早退の集計)
If jStatusAsc = "012" Then
lteElyCntTotalAsc = DateAdd("h", Hour(lteCntAsc), lteElyCntTotalAsc)
lteElyCntTotalAsc = DateAdd("n", Minute(lteCntAsc), lteElyCntTotalAsc)
lteElyCntTotalAsc = DateAdd("h", Hour(elyCntAsc), lteElyCntTotalAsc)
lteElyCntTotalAsc = DateAdd("n", Minute(elyCntAsc), lteElyCntTotalAsc)
End If

'ASCの遅刻・早退時間を計算その2(当日欠勤・当日有給の集計)
If jStatusAsc = "007" Or jStatusAsc = "008" Then
lteElyCntTotalAsc = DateAdd("h", 7, lteElyCntTotalAsc)
End If

'土日祝の出勤日数を計算(【集計】シートの土日祝ASCのステータスが入力されている数)
If sheetSyuukei.Cells(2, j + cnt + 7).Value = "土曜日" Or sheetSyuukei.Cells(2, j + cnt + 7).Value = "日曜日" Then
If sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 7).Value <> "" Then
weekendTotalCount = weekendTotalCount + 1
End If
ElseIf isHoliday(strYear & "/" & Format(strMon, "00") & "/" & Format(sheetSyuukei.Cells(1, j + cnt + 7), "00")) Then
If sheetSyuukei.Cells(i + cnt2 + 3, j + cnt + 7).Value <> "" Then
weekendTotalCount = weekendTotalCount + 1
End If
End If

lteCntAsc = 0
lteCntCopc = 0
elyCntAsc = 0
elyCntCopc = 0

cnt = cnt + 1
Next

'-----------------------------------------
'集計処理
'-----------------------------------------

'名前に入力がある行のみ集計対象とする
If sheetSyuukei.Cells(i + cnt2 + 2, 7).Value <> "" Then

'-----------------------------------------
'各種集計 算出処理
'-----------------------------------------

'COPC関連
If yStatusCountCopc <> 0 Then
'COPC欠勤率
sheetSyuukei.Cells(i + cnt2 + 2, 70).Value = 1 - okCntCopc / yStatusCountCopc
'COPCシフト遵守率
sheetSyuukei.Cells(i + cnt2 + 2, 74).Value = okCntCopc / yStatusCountCopc
End If

'COPC ステータス110の数
sheetSyuukei.Cells(i + cnt2 + 2, 71).Value = yStatusCountCopc
'COPC OKの数
sheetSyuukei.Cells(i + cnt2 + 2, 72).Value = okCntCopc
'COPC NGの数
sheetSyuukei.Cells(i + cnt2 + 2, 73).Value = yStatusCountCopc - okCntCopc

'ASC
If jStatusCountAsc1 <> 0 Then
'ASC当欠当有出勤率
sheetSyuukei.Cells(i + cnt2 + 2, 75).Value = jStatusCountAsc2 / jStatusCountAsc1
End If

'ASC当欠当有回数
sheetSyuukei.Cells(i + cnt2 + 2, 76).Value = jStatusCountAsc007 + jStatusCountAsc008

'ASCの遅刻・早退・早退遅刻時間合計
Dim tmpDate2 As Date '標準時間(1899/12/30 00:00:00)
Dim tmpCnt As Integer
Dim tmpCntStr As String

tmpCnt = DateDiff("n", tmpDate2, lteCntTotalAsc) + DateDiff("n", tmpDate2, elyCntTotalAsc) + DateDiff("n", tmpDate2, lteElyCntTotalAsc)
tmpCntStr = CStr(tmpCnt \ 60) & ":" & Format(CStr(tmpCnt Mod 60), "00")
sheetSyuukei.Cells(i + cnt2 + 2, 78).Value = tmpCntStr

'ASCの当遅刻・当早退・当早退遅刻率
Dim jStatusCountAscTmp As Integer
jStatusCountAscTmp = jStatusCountAsc001 + jStatusCountAsc005 + jStatusCountAsc006 + jStatusCountAsc010 + jStatusCountAsc011 + jStatusCountAsc012
sheetSyuukei.Cells(i + cnt2 + 2, 77).Value = tmpCnt / (jStatusCountAscTmp * 480)

'ASCの遅刻時間合計
tmpCnt = DateDiff("n", tmpDate2, lteCntTotalAsc)
tmpCntStr = CStr(tmpCnt \ 60) & ":" & Format(CStr(tmpCnt Mod 60), "00")
sheetSyuukei.Cells(i + cnt2 + 2, 79).Value = tmpCntStr

'合計算出用
lteTotal = lteTotal + tmpCnt

'ASCの早退時間合計
tmpCnt = DateDiff("n", tmpDate2, elyCntTotalAsc)
tmpCntStr = CStr(tmpCnt \ 60) & ":" & Format(CStr(tmpCnt Mod 60), "00")
sheetSyuukei.Cells(i + cnt2 + 2, 80).Value = tmpCntStr

'合計算出用
elyTotal = elyTotal + tmpCnt

'ASCの遅刻・早退時間合計
tmpCnt = DateDiff("n", tmpDate2, lteElyCntTotalAsc)
tmpCntStr = CStr(tmpCnt \ 60) & ":" & Format(CStr(tmpCnt Mod 60), "00")
sheetSyuukei.Cells(i + cnt2 + 2, 81).Value = tmpCntStr

'合計算出用
lteElyTotal = lteElyTotal + tmpCnt

'ASC土日出勤日数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 82).Value = weekendTotalCount

'ASCの当欠・当有・遅刻・早退時間合計
Dim tmpCnt2 As Integer
Dim tmpCnt3 As Integer

tmpCnt2 = (jStatusCountAsc007 + jStatusCountAsc008) * 480
tmpCnt = DateDiff("n", tmpDate2, lteCntTotalAsc) + DateDiff("n", tmpDate2, elyCntTotalAsc) + DateDiff("n", tmpDate2, lteElyCntTotalAsc)
tmpCnt3 = tmpCnt + tmpCnt2
tmpCntStr = CStr(tmpCnt3 \ 60) & ":" & Format(CStr(tmpCnt3 Mod 60), "00")
sheetSyuukei.Cells(i + cnt2 + 2, 83).Value = tmpCntStr

'ASCの001回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 84).Value = jStatusCountAsc001

'ASCの003回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 85).Value = jStatusCountAsc003

'ASCの008回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 86).Value = jStatusCountAsc008

'ASCの002回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 87).Value = jStatusCountAsc002

'ASCの005回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 88).Value = jStatusCountAsc005

'ASCの006回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 89).Value = jStatusCountAsc006

'ASCの007回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 90).Value = jStatusCountAsc007

'ASCの010回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 91).Value = jStatusCountAsc010

'ASCの011回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 92).Value = jStatusCountAsc011

'ASCの012回数を設定
sheetSyuukei.Cells(i + cnt2 + 2, 93).Value = jStatusCountAsc012

Else
sheetSyuukei.Cells(i + cnt2 + 2, 70).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 71).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 72).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 73).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 74).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 75).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 76).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 77).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 78).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 79).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 80).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 81).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 82).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 83).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 84).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 85).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 86).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 87).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 88).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 89).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 90).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 91).Value = ""
sheetSyuukei.Cells(i + cnt2 + 2, 92).Value = ""
End If

'各種カウントをリセット
jStatusCountAsc1 = 0
jStatusCountAsc2 = 0
jStatusCountAsc001 = 0
jStatusCountAsc002 = 0
jStatusCountAsc003 = 0
jStatusCountAsc005 = 0
jStatusCountAsc006 = 0
jStatusCountAsc007 = 0
jStatusCountAsc008 = 0
jStatusCountAsc010 = 0
jStatusCountAsc011 = 0
jStatusCountAsc012 = 0
yStatusCountCopc = 0
okCntAsc = 0
okCntCopc = 0
lteCntTotalAsc = 0
elyCntTotalAsc = 0
lteElyCntTotalAsc = 0
weekendTotalCount = 0
cnt = 0
cnt2 = cnt2 + 3
Next

'COPC欠勤率(SP)TOTAL
If yStatusCountCopcSp <> 0 Then
sheetSyuukei.Cells(804, 70).Value = ngCntCopcSp / yStatusCountCopcSp
Else
sheetSyuukei.Cells(804, 70).Value = 0
End If

'当遅刻TOTAL
sheetSyuukei.Cells(804, 79).Value = CStr(lteTotal \ 60) & ":" & Format(CStr(lteTotal Mod 60), "00")

'当早退TOTAL
sheetSyuukei.Cells(804, 80).Value = CStr(elyTotal \ 60) & ":" & Format(CStr(elyTotal Mod 60), "00")

'当遅刻早退TOTAL
sheetSyuukei.Cells(804, 81).Value = CStr(lteElyTotal \ 60) & ":" & Format(CStr(lteElyTotal Mod 60), "00")

MsgBox ("集計が完了しました")

End Sub

' 残業時間の合計(日単位)を算出する
' ・1時間 = 1
' ・30分 = 0.5
' ・15分 = 0.25
' として算出する。それ未満(5分や10分など)は切り捨て
Private Sub 算出()

'初期値設定
Set sheetZangyoHayamihyo = Worksheets("【残業時間早見表】")

For j = 1 To 31
Dim tmpZangyo As String
tmpZangyo = WorksheetFunction.Sum(Range(sheetZangyoHayamihyo.Cells(3, j + 7), sheetZangyoHayamihyo.Cells(202, j + 7)).Value)
sheetZangyoHayamihyo.Cells(204, j + 7).Value = tmpZangyo
Next

End Sub

' 実績シートから早見表を作成する
Private Sub 早見表を作成する()

'初期値設定
Set sheetJisseki = Worksheets("【実績】")
Set sheetHayamihyo = Worksheets("【早見表】")

cnt = 0
cnt2 = 0

'-----------------------------------------
'早見表の初期化
'-----------------------------------------

'ブランクで初期化(200行分)
sheetHayamihyo.Range("A3:AL202").Value = ""

'-----------------------------------------
'会社名~氏名をセット
'-----------------------------------------

'A~G列をシート【実績】からコピー
For i = 1 To 200
For j = 1 To 7
sheetHayamihyo.Cells(i + 2, j).Value = sheetJisseki.Cells(i + cnt2 + 2, j).Value
Next
cnt = 0
cnt2 = cnt2 + 3
Next

'-----------------------------------------
'勤務状況をセット
'-----------------------------------------

cnt = 0
cnt2 = 0

'H列以降はステータスを参照して値をセット
For i = 1 To 200
For j = 1 To 31
Dim tmpStatus As String
Dim jVal As String

'シートの出社・退社時間を取得
jTimeFrom = sheetJisseki.Cells(i + cnt2 + 3, j + cnt + 7).Value
jMinFrom = sheetJisseki.Cells(i + cnt2 + 3, j + cnt + 8).Value
jTimeTo = sheetJisseki.Cells(i + cnt2 + 5, j + cnt + 7).Value
jMinTo = sheetJisseki.Cells(i + cnt2 + 5, j + cnt + 8).Value

'実績シートからステータスを取得
tmpStatus = sheetJisseki.Cells(i + cnt2 + 2, j + cnt + 7).Value

If tmpStatus = "001" Then
'通常の場合は「○」をセット
jVal = "○"
ElseIf tmpStatus = "005" Then
'遅刻の場合は出社時刻をセット
jVal = "遅 " & jTimeFrom & ":" & Format(jMinFrom, "00")
ElseIf tmpStatus = "010" Then
'遅刻の場合は出社時刻をセット
jVal = "遅 " & jTimeFrom & ":" & Format(jMinFrom, "00")
ElseIf tmpStatus = "006" Then
'早退の場合は退社時刻をセット
jVal = "早 " & jTimeTo & ":" & Format(jMinTo, "00")
ElseIf tmpStatus = "011" Then
'早退の場合は退社時刻をセット
jVal = "早 " & jTimeTo & ":" & Format(jMinTo, "00")
ElseIf tmpStatus = "012" Then
'遅早の場合は出社・退社時刻をセット
jVal = "遅早 " & jTimeFrom & ":" & Format(jMinFrom, "00") & "-" & jTimeTo & ":" & Format(jMinTo, "00")
ElseIf tmpStatus = "002" Then
'事前欠勤の場合
jVal = "事欠"
ElseIf tmpStatus = "003" Then
'事前有給の場合
jVal = "事有"
ElseIf tmpStatus = "004" Then
'事前振休の場合
jVal = "事振"
ElseIf tmpStatus = "007" Then
'当日欠勤の場合
jVal = "当欠"
ElseIf tmpStatus = "008" Then
'当日有給の場合
jVal = "当有"
ElseIf tmpStatus = "009" Then
'当日振休の場合
jVal = "当振"
Else
'それ以外の場合はブランクをセット
jVal = ""
End If

sheetHayamihyo.Cells(i + 2, j + 7).Value = jVal

cnt = cnt + 1
Next

cnt = 0
cnt2 = cnt2 + 3
Next

MsgBox ("早見表の作成が完了しました")

End Sub

' 実績シートから残業時間早見表を作成する
Sub 残業時間早見表を作成()

Dim zangyoMin As Integer

Dim jTimeTo As String
Dim jMinTo As String

Dim jPosition As String

Dim strYear As String
Dim strMon As String

'初期値設定
Set sheetTejunsyo = Worksheets("手順書")
Set sheetJisseki = Worksheets("【実績】")
Set sheetZangyoHayamihyo = Worksheets("【残業時間早見表】")

strYear = sheetTejunsyo.Cells(7, 2).Value
strMon = sheetTejunsyo.Cells(7, 4).Value

cnt = 0
cnt2 = 0

'-----------------------------------------
'早見表の初期化
'-----------------------------------------

'ブランクで初期化(200行分)
sheetZangyoHayamihyo.Range("A3:AL202").Value = ""

'-----------------------------------------
'会社名~氏名をセット
'-----------------------------------------

'A~G列をシート【実績】からコピー
For i = 1 To 200
For j = 1 To 7
sheetZangyoHayamihyo.Cells(i + 2, j).Value = sheetJisseki.Cells(i + cnt2 + 2, j).Value
Next
cnt = 0
cnt2 = cnt2 + 3
Next

'-----------------------------------------
'残業時間をセット
'-----------------------------------------

cnt = 0
cnt2 = 0

'H列以降はシート【実績】の時間から算出して値をセット
For i = 1 To 200
For j = 1 To 31

'シートの退社時間を取得
jTimeTo = sheetJisseki.Cells(i + cnt2 + 5, j + cnt + 7).Value
jMinTo = sheetJisseki.Cells(i + cnt2 + 5, j + cnt + 8).Value

'シートの役職を取得 B列=2とする★★★
jPosition = sheetJisseki.Cells(i + cnt2 + 2, 2).Value

If jTimeTo <> "" And jMinTo <> "" And (jPosition = "SP" Or jPosition = "ASV") And _
sheetZangyoHayamihyo.Cells(2, j + 7).Value <> "土曜日" And sheetZangyoHayamihyo.Cells(2, j + 7).Value <> "日曜日" And _
Not isHoliday(strYear & "/" & Format(strMon, "00") & "/" & Format(sheetSyuukei.Cells(1, j + cnt + 7), "00")) Then

'日付を取得
Dim tmpStrDate As String
tmpStrDate = sheetJisseki.Cells(1, j + cnt + 7).Value

'正確な退社時間(年月日時分)を算出
Dim date1 As Date
date1 = getCorrectDatetime(tmpStrDate, Format(jTimeTo, "00"), Format(jMinTo, "00"))

'基準の退社時間(基準日の17時)を算出
Dim date2 As Date
date2 = getCorrectDatetime(tmpStrDate, "17", "00")

'退社時間が17時以降である場合のみ残業時間を算出して設定
If date1 > date2 Then
zangyoMin = DateDiff("n", date2, date1)

'残業時間(分)を単位に換算してセルにセット
sheetZangyoHayamihyo.Cells(i + 2, j + 7).Value = (zangyoMin \ 15) / 4
End If

End If

cnt = cnt + 1
zangyoMin = 0
Next

'個人の合計を算出
sheetZangyoHayamihyo.Cells(i + 2, 40).Value = WorksheetFunction.Sum(Range(sheetZangyoHayamihyo.Cells(i + 2, i + 7), sheetZangyoHayamihyo.Cells(i + 2, i + 38)))

cnt = 0
cnt2 = cnt2 + 3
Next

MsgBox ("残業時間早見表の作成が完了しました")

End Sub

' ステータスを設定
Private Function setStatus()

statusCodeAsc = Array("001", "002", "003", "004", "005", "006", "007", "008", "009", "010", "011", "012")

statusCodeCopc = Array("100", "110", "111", "120", "121", "130", "131", "140", "141", "142", "143", "150", "151")

End Function

' 日付の初期値を取得する
Private Function getCorrectDatetime(valDate As String, valHour As String, valMin As String) As Date

Dim stdDateTime As Date

Set sheetTejunsyo = Worksheets("手順書")

'日付を、計算する年月の1日にリセット
stdDateTime = CDate(sheetTejunsyo.Cells(7, 2).Value & "/" & sheetTejunsyo.Cells(7, 4).Value & "/" & valDate & " " & valHour & ":" & valMin & ":00")

'結果を返す
getCorrectDatetime = stdDateTime

End Function

' 祝日を判定する
Private Function isHoliday(valDate As String) As Boolean

Dim rtn As Variant

Set sheetHoliday = Worksheets("祝日")

'祝日シートから祝日かどうかを判定。現状ではA1~A200まで祝日を定義可能
rtn = Application.VLookup(valDate, sheetHoliday.Range("A1:A200"), 1, False)

'結果を返す
If IsError(rtn) Then
isHoliday = False
Else
isHoliday = True
End If

End Function