今日を乗り切るExcel研究所

Excel に働かされていませんか

【VBA】時間の計算をしたい【100本ノック】

今回もエクセルの神髄様の Twitter 企画「VBA100本ノック」に便乗させていただきます。 今回はその91本目への解答です。Date 型の使い方もまとめました。

VBA100本ノック 91本目:時間計算(残業時間の月間合計)

方針

実務経験者ならではの実践的かつ実戦的なお題ですね。

今回のお題のポイントは「時間計算」と「複数キーによる集計」です。

日付や時刻の計算はプログラミングの必須課題ですが、個人的にはこれまで VBA で日付や時間を扱った経験がほとんどありません。 これを機に、VBA での時刻の扱いをマスターしたいと思います。

調べると VBA での時間計算にも、いくつかの流儀があるようですが、本解答ではあくまで基本にのっとり日時を表す組込型の「Date 型」を中心に使う方針とします。

集計の方は、ロジックを組まなくても Excel が持つ高度な集計機能を利用する方法がありそうな気がしますが、 今回は時間計算を重点的に集中したいので、凝ったことせずにシンプルなループと Scripting.Dictionary を使ったロジックとします。

また実業務開発を想定して、動きだけでなく、レビューやテスト、変更修正のしやすさなどもできるだけ意識して取り組みたいと思います。

解答:Date 型を使ってみる

Option Explicit
 
Const 始業時刻 = "9:00 AM"
Const 労働時間 = "8:00"
Const 休憩時間 = "1:00"
Const 残業単位 = "0:30"

Private Enum 勤怠
    id = 1
    日付
    出勤時刻
    退勤時刻
End Enum

' 日次残業計算
' 第一引数のdtは未使用。休日残業対応などへの予約。
Function calcNichijiZangyo(dt As Date, syukkin As Date, taikin As Date) As Date
    Dim kinmu As Date
    Dim shigyo As Date
    Dim jikkin As Date
    Dim zangyo As Date
    
    kinmu = CDate(労働時間) + CDate(休憩時間)
    jikkin = taikin - max(syukkin, CDate(始業時刻)) ' 始業前は勤務時間外
    If jikkin > kinmu Then
        zangyo = jikkin - kinmu
        zangyo = truncTime(zangyo, CDate("0:01:00"))  ' 秒切り捨て
    Else
        zangyo = 0 ' 繰越相殺なし
    End If
    calcNichijiZangyo = zangyo
End Function

' 月次残業計算
Function calcGetsujiZangyo(totalZangyo As Date) As Date
    calcGetsujiZangyo = truncTime(totalZangyo, CDate(残業単位)) ' 残業単位で切り捨て
End Function

Sub VBA100_091()
    Dim kintaiData As Range
    Set kintaiData = Worksheets("勤怠").Range("A1").CurrentRegion
    Set kintaiData = Intersect(kintaiData, kintaiData.Offset(1))
        
    Dim zangyoReport As Range
    Set zangyoReport = Worksheets("残業").Range("A1").CurrentRegion
    Set zangyoReport = zangyoReport.Offset(1)
    zangyoReport.ClearContents
    
    Dim getsujiZangyo As Object
    Dim getsuji As Variant
    Dim zangyo As Double
    Set getsujiZangyo = CreateObject("Scripting.Dictionary")
    
    ' 各ID月次ごとに残業時間を集計する
    Dim rec As Range
    For Each rec In kintaiData.Rows
        getsuji = rec.Cells(勤怠.id) & vbTab & Format(rec.Cells(勤怠.日付), "yyyymm")
        zangyo = calcNichijiZangyo(rec.Cells(勤怠.日付), rec.Cells(勤怠.出勤時刻), rec.Cells(勤怠.退勤時刻))
        getsujiZangyo(getsuji) = getsujiZangyo(getsuji) + zangyo
    Next
    
    ' 有効な月次残業時間を算定する
    For Each getsuji In getsujiZangyo.Keys
        zangyo = calcGetsujiZangyo(getsujiZangyo(getsuji))
        getsujiZangyo(getsuji) = zangyo
    Next
    
    ' 出力
    Dim idYM As Variant
    Dim cur As Range
    Set cur = zangyoReport.Rows(1).Resize(, 3)
    For Each getsuji In getsujiZangyo.Keys
        idYM = Split(getsuji, vbTab)
        zangyo = getsujiZangyo(getsuji)
        cur = Array(idYM(0), idYM(1), zangyo)
        Set cur = cur.Offset(1)
    Next
End Sub

Private Function max(a As Variant, b As Variant) As Variant
    max = IIf(a >= b, a, b)
End Function

' 時刻切り捨て計算(正のDateのみ)
Private Function truncTime(ByVal a As Date, ByVal b As Date) As Date
    Dim sa As Long: sa = DateDiff("s", 0, a)
    Dim sb As Long: sb = DateDiff("s", 0, b)
    truncTime = DateAdd("s", (sa \ sb) * sb, 0)
End Function

 



 

考察

VBAでの日時計算は面倒くさいのだろうなとは思っていましたが、想像以上にクセのある世界でした。

Date 型について調べていたら、だいぶ深堀りしすぎてしまいました。

Date 型について

VBA で時刻・時間を扱うには Date 型を使います。

Date 型は日時(DateTime)を保持する型ですが、 時刻(Time)のみの型や時間(期間/Interval)を表す型は用意されていないので、それらも Date 型で扱うことになります。

Date 型の内部表現はシリアル値と呼ばれる Double 値です。

シリアル値の整数部は基準日(1899/12/31)からの日数を、小数部は時刻として扱います。 時刻は 1.0 を24時間とした割合に対応します。 時刻のみを扱う場合、基本的にシリアル値は 1.0 未満で小数部のみ(整数部0)を使います。

’ 日時とシリアル値
? CDbl("2021/01/01 12:34:56")
 44197.5242592593
? CDate(44197.5242592593)
2021/01/01 12:34:56 

? CDate(1)
1899/12/31 
? CDate(0)
0:00:00 
? CDate(0.5)
12:00:00      ' 正午
? CDate(1.5)
1899/12/31 12:00:00 

' 1 秒のシリアル値
? CDbl("00:00:01")
 1.15740740740741E-05     ' = 0.0000115740740740741
? CDate(0.00001157407)
0:00:01 

残業集計値などは24時間を超えた値になりますが、これを Date 型で扱うと 1.0 より大きなシリアル値となり、表示すると日付が出てしまいます。 書式を時刻のみ(hh:mm:ss)にしても24時間以上は周回した時間の表示になってしまいます。

その場合、Excel のセル書式なら、時間(h)の書式を [h][hh] とすれば24時間を超える時間を表示することができます。

ところが、VBA では Format 関数がこの書式をサポートしません。 その代わりに WorksheetFunction.Text メソッドを呼び出します。

? CDate(1.5)
1899/12/31 12:00:00
? WorksheetFunction.Text(1.5, "[hh]:mm:ss")
36:00:00
? Format(1.5, "hh:mm:ss")  ' 24時間で周回する
12:00:00
? Format(1.5, "[hh]:mm:ss")  ' [hh]を使うと壊れる
:12:00
? Format(1.5, "ttttt")
12:00:00

Date 型の生成と変換

Date 型の時刻のリテラル表現は、コロン区切りの時刻形式を # で囲み、#12:34:56# あるいは秒を省略して #12:34# ように書きます。

24 時間以上を指定するとエラーになります。

VBA 的にはそれだけでいいはずなのですが、VBE エディタのおせっかいによって勝手に正規化されます。

    #12:34:56#    ⇒    #12:34:56 AM#
    #13:00:00#    ⇒    #1:00:00 PM#
    #00:00:00#    ⇒    #12:00:00 AM#
    #12:34#       ⇒    #12:34:00 PM#
    #24:00:00#    ⇒    エラー

これでは Date 型変数の初期化や時刻定数の定義に時刻リテラルを使いたくても、おせっかいにより意図が失われ非常に迷惑です。

リテラルではなく、時刻を書式化した文字列の方が断然使い勝手がいいです。

時刻の文字列は CDate 関数や TimeValue 関数によって Date 型に変換できます。

また、Date 値を引数に受け取る関数は基本的に、文字列形式のままでも受け付けてくれます。

? CDate("12:34:56")
12:34:56 
? CDate("2:00:01") ? 
2:00:01 
? CDate("2:00:01 PM") ' 14:00:01 ' 12時間表記
14:00:01 
? CDate("12:34") ' 秒を省略できる
12:34:00 
? CDate("1:2")
1:02:00 
? CDate("00:00:00")
0:00:00
? TimeValue("12:34:56")
12:34:56 
? DateDiff("n", "9:00:00", "19:32:41") ' 文字列形式でもOK
 632 

ただし、関数に24時以上の時刻を与えるとと実行時エラーになるので注意が必要です。

? CDate("23:59:59") 
23:59:59 
? CDate("24:00:00") ' エラーになる

f:id:shego:20210222232028p:plain

時刻の場合はこれでもいいのですが、24時間以上の時間を初期値や定数としたいときには困りものです。

TimeSerial 関数を使えば、24時間以上の Date 値を生成できます。

TimeSerial 関数は整数で指定された時・分・秒からなる Date 型の値を生成するものです。

? TimeSerial(12, 34, 56) ' 時・分・秒を指定
12:34:56 
? TimeSerial(24, 00, 00)
1899/12/31               ' = 24:00:00
? TimeSerial(65, 43, 21)
1900/01/01 17:43:21     ' = 65:43:21

また、Date 型は数値型の一種なので、他の数値型との間で暗黙の型変換が行われます。 1.0 以上の Double 値を Date 型に変換すれば、24時間以上の時間を用意できます。

    Dim dt As Date
    dt = 1.5 ' 36:00:00 の生シリアル値

    Dim i As Long
    i = Now ' 44250  ' 整数に丸め
? TypeName (1.5 + #12:34:56#)
Date
? TypeName (1 + #12:34:56#)
Date

CDate 関数を使えば、明示的に整数値や Double 値を Date 型に変換することができます。

? CDate(1.5)
1899/12/31 12:00:00 
? CDate(44250)
2021/02/23 

以下の図に Date 型への生成と別データ型からの相互変換の方法をまとめました。

f:id:shego:20210221213435p:plain

 



 

Date 型の演算

Date 型は数値型の一種なので、四則演算子による計算が可能です。

時間の計算でも Date 型のまま加算・減算して整合します。

Date 値同士の積や商も計算できますが、結果にシリアル値としての意味はなく、型も Double になります。

? #18:00# + #2:00#
20:00:00 
? #18:00# - #2:00#
 0.666666666666667  ' = 16:00:00
? #1:00# / #0:15#
 4
? #0.15# * 4
 4.16666666666667E-02  ' = 1:00:00 

注意したいのは計算結果が負になる場合です。

Excel のセルは負の時刻を扱えません

f:id:shego:20210223100836p:plain

Date 型は、負のシリアル値でも保持できて計算も可能ですが、負の時刻は書式で表現できません。

Date 型の計算では計算結果が負にならないよう注意した方がいいでしょう。

    dt = #1:00:00 AM# - #1:00:05 AM# ' ??:00:55 をなんとなく期待
    Debug.Print Format(dt, "ttttt")    ' 0:00:05  エラーにはならず、絶対値の時刻が表示される
    Debug.Print (dt < 0)  ' True  シリアル値は負(-8.33333333333333E-02)になっている  

ところで、2進数の浮動小数点で表現されている時刻はあくまで近似値にしかならないので、計算に際してはどうしても誤差が生じます。

できれば、時・分・秒などの整数ベースで計算したいところです。

VBA にはそのための Date 操作関数がいくつか用意されています。

? DateAdd("h", -9, "12:34:56") ' 9時間引く
3:34:56 
? DateDiff("n", "9:00:00 AM", "9:34:56 PM") ' 分単位の時間差(秒を位切り捨ててからの差)
 754 
? Hour("12:34:56") * 60 * 60 + Minute("12:34:56") * 60 + Second("12:34:56") ' 秒単位に変換
 45296 
' Hour 関数は 24以上でエラーになるので注意

便利なのですが、実際に時間計算のロジックに組み込んでみるとどうも煩雑で分かりづらくなります。

いっそのこと、シリアル値をいったん秒単位などの時刻としてキリのいい整数に変換し、整数値として計算したいところです。

理屈ではシリアル値に1日の秒数(86400)を掛けて整数に丸めれば秒単位の整数に変換できます。 それでも厳密には丸め方式などが微妙な誤差として影響しそうなのが悩ましいです。

? CLng(#00:00:01# * 86400) ' 一番近い偶数への丸め(いわゆる銀行丸め)
 1 
? Int(#00:00:01# * 86400) ' 小数部切り捨て
 0 
? WorksheetFunction.Round(#00:00:01# * 86400, 0) ' 四捨五入 
 1 

どうしても誤差なしで Date 値と整数値を相互変換したければ、DateDiff 関数と DateAdd 関数を使うのがよいでしょう。

? DateDiff("s", 0, "12:34:56") ' 秒単位整数への変換
 45296
? DateAdd("s", 45296, 0) ' 秒単位整数をDate型に戻す
12:34:56 

? DateDiff("n", 0, "12:34:56") ' 分単位整数への変換(秒単位切り捨て)
 754 
? DateAdd("n", 754, 0) ' 分単位整数をDate型に戻す
12:34:00 

Date 型の切り捨て

秒や分での切り捨ては DateDiff 関数で簡単にできるのですが、30 分単位で切り捨てるとなるとそうもいきません。

Date 型は Double 型に暗黙的に変換されるので、WorksheetFunction.Floor() などの数値用関数がそのまま使えます。

? CDate(WorksheetFunction.Floor(#12:34:56#, #00:30:00#)) ' 30分単位で切り捨て
12:30:00 
? CDate(WorksheetFunction.Floor_Math(#12:34:56#, #00:30:00#))  ' Excel 2013 以降の推奨
12:30:00 

ただ、これをそのまま使ってもいいのですが、時刻に戻した時にすべて結果で期待値にぴったり合うことが保証できるのかは、何とも言えません。

それを検証する時間も意味もありませんので、ここは自前で切り捨て処理を実装した方が手っ取り早くて安心感も得られます。

もっとも、残業時間の計算にこれほどの精度とこだわりが必要なのかと言われれば、それは出題者さんに聞いてくださいとしか。

参考資料

関連記事

なし