【ExcelVBA】時間の切り上げ、切り捨て、丸め

ボタンをいろいろと作って、マクロにも少し慣れてきたので、
新しくマクロを組んで行こうと思います。

Excelを触ってると、日付や時間を取得する場面があると思います。

日付や時間は、Date関数やNow関数で簡単に取得する事ができますが、
表示時間を5分単位や10分単位で取得して表示させたい。

こんな事を考えた事は、ないでしょうか。

数値の切り上げ、切り捨ては関数が存在していますが、
時間については、よくわかりません。(^_^;)\('_' ) オイオイ...

そこで調べ倒して、自分なりに作ってみたものを
記事にしていこうと思います。


まずは数値の
切り上げ、切り捨て、四捨五入、を調べたのでまとめてみます。

・WorksheetFunction

VBAでもワークシート関数を使う事ができます。
セルに書き込む関数の事です。

・Round   四捨五入
・RoundUp  切り上げ
・RoundDown 切り捨て

数値の小数点を処理するのに使います。

Round(数値,桁)
四捨五入をします。

VBA関数のRoundは、偶数丸めになります。
Excelを触る方なら、銀行丸めの方が馴染みがあるかも。

WorksheetFunctionのRoundは、四捨五入を行ってくれます。

コーディングは、
ActiveSheet.WorkSheetFunction.Round(数値,桁)
ActiveSheetは省略可

実際に、Roundと
WorkSheetFunctionのRoundでの違いを検証してみます。
Roundテスト

Sub Roundテスト()

Dim value(5) As Double

For i = 0 To 4
Dim a As Integer
a = i + 2

Cells(a, 2) = Round(Cells(a, 1))
Cells(a, 3) = WorksheetFunction.Round(Cells(a, 1), 0)

Next

End Sub

実行結果は、
時間丸め①.jpg
Roundは、偶数に四捨五入されると言った方が分かり易いでしょうか。
結果が、偶数になるように処理されまっす。

四捨五入を使う場合は、WorkSheetFunctionのRoundを設定する必要があります。
注意点は、桁の省略ができない所です。
時間丸め②.jpg
桁数は、小数点以下から0、1、2と順番に数えます。
整数にしたいなら、0
0.1のように1位を表示したいなら、1を設定します。
2位以下も同じで、2位→2、3位→3と設定します。

RoundUp(数,値)
小数点以下の指定桁を切り上げます。

コーディングは、
ActiveSheet.WorksheetFunction.RoundUp(数値,桁)

読んで字の如く、指定桁の切り上げになります。
小数点1位を切り上げた場合、0.1~0.9は、全て1になります。

注意点は、Roundと同じで、
・ActiveSheetの省略可
・桁の設定が必要

となります。

RoundDown(数,値)
小数点以下の指定桁を切り捨てます。

コーディングは、
ActiveSheet.WorksheetFunction.RoundDown(数値,桁)

読んで字の如く、指定桁の切り捨てになります。
小数点1位を切り捨てた場合、0.1~0.9は、全て0になります。

注意点は、同じく
・ActiveSheetの省略可
・桁の設定が必要

となります。


ここまでは、数値の丸めについて書きましたが、ご存知の方が多いと思います。

さて、ここから本題の時間の丸めについてですが、
Google先生にお尋ねした所、CEILING関数 とFLOOR関数を使って、
丸める事が出来る事がわかりました。

勉強させて頂いたサイトが、

VBAの勉強を始めてみた さんの
ひっそりとExcel関数のおさらい(時間の切り上げ・切り捨て)
関数の処理の考察、計算方法を説明されてます。

Excelでお仕事! さんの
マクロ上でワークシート関数を使う。
実際のコーディングがあります。非常に助かります。

ふたサイト様、
この場をお借りして、お礼申し上げます。
m(_ _)m


早速コードをお借りして、作ってみようと思います。
今回は、分単位の時間丸めを作ってみます。

丸める単位は、
1分、5分、10分、15分、30分

取得時間から、各分単位で、丸められるようにします。

時間は、四捨五入とはいかないので、
境になる”しきい値”を計算して、判断させようと思います。

計算は、”単位/2を四捨五入したもの”とします。

例えば、5分なら 5分/2を四捨五入すると3になります。
取得時間が3分以上なら切り上げ、3より小さければ切り捨てとします。
時間取得

Sub 時間取得()

Dim time As Date '計算後の時間'
Dim half As Integer 'しきい値'
Dim split As Double '24時間を丸め単位で分割した数値'
Dim remain As Integer '現時刻の"分"の剰余'

Const unit As Integer = 5 '丸め単位'
Const convert As Integer = 1440 '24時間を分に換算'


half = WorksheetFunction.Round(unit / 2, 0)
split = convert / unit

remain = Minute(Now) Mod unit

If remain >= half Then
time = WorksheetFunction.Ceiling(Now, 1 / split)

Else
time = WorksheetFunction.Floor(Now, 1 / split)

End If

MsgBox "現在の時刻:" & Now & vbCrLf & _
"丸め処理後:" & time

End Sub

少し分かりにくいと思うので説明すると、
まず、5分の丸めなので、Ceiling・Floorのどちらに掛けるにしても、
24時間を5分単位に分割する必要があります。

24時間/5分では、計算できないので、
24時間を分換算します。(24時間✕60分)=1440分となります。

1440分/5分=288分割となります。
逆に言えば、1日の内の5分は、1/288日と言う事になります。

後は、この数値を関数に設定して、現時刻の1/288部分を切り上げるか
切り捨てるか処理させます。

しきい値は、前にも説明した単位の半分を四捨五入した値として、
5分なら、3がしきい値になります。

後は、if文で3以上なら切り上げ、3より小さければ切り捨てを設定すればOKです。

コーディングができたので、テストしてみます。
時間丸め③.jpg
問題無く、5分単位で丸められてます。

さて、ここで気になるのが、”ひっそりとExcel関数のおさらい”で考察されている、
Floor関数に掛けると値がおかしくなる部分です。

末尾が5の倍数になる所で、5分切り捨てられる状態になるようです。
なので、少し調べてみる事にします。

コーディングは端折りますが、上で書いた”丸めコード”をループで回して、
手書きした時間を処理してみます。
時間丸め④.jpg
5の倍数全部ではないですが、3カ所で5分の切り捨てが発生しています。

これではまずいので、対策を考えないといけません。

秒の桁を、01秒にするとうまく処理してくれるって事なので、
1分単位の丸めの実装も兼ねて、秒の処理を作ってみます。

Dim calTime As Date '計算した時刻'
Const half As Integer = 30 'しきい値'
Const convert As Integer = 1440 '一日を分に換算'


If Second(Now) >= half Then
calTime = WorksheetFunction.Ceiling(Now, 1 / convert)

Else
calTime = WorksheetFunction.Floor(Now, 1 / convert)

End If

calTime = MinRound(DateAdd("s", 1, calTime))

説明する程でもないですが、
”しきい値”30秒を境に、丸めています。
丸め終わった時刻に、01秒足せば問題ないかと。

例えば、
0:04:25 → 0:04:00 → 0:04:01
0:04:32 → 0:05:00 → 0:05:01

こんな感じに処理が行われます。
どの時刻も秒の部分は、01秒となるように設定しています。

この算出時刻を再度、5分丸めの処理に突っ込めば、
5分の切り捨てが発生しないと思います。

秒の末端処理ができたので、
後は、1分から30分単位の丸めを設定していきます。

まず、シートに単位の設定と時刻表示欄、時間取得ボタンを用意します。
時間丸め⑨.jpg
単位の設定は、
時間丸め⑩.jpg
入力規則のリストを使って、単位設定をします。

おまけで、
時間丸め⑪.jpg
表示書式のユーザー定義に0分単位を設定しておくと、
リストで呼び出された数値に”分単位”が付くようになるので便利です。

丸めが分かり易いように、現時刻と丸め時刻の欄を作って、
同時に表示させます。

ただ、プロシージャが大きくなりすぎたので、
秒丸めと分丸めをFunctionにして分離します。
時間取得プロシージャ

Sub 時間取得()

Dim calTime As Date '計算した時刻'
Dim unit As Integer '単位の数値'

Const celUnit As String = "B3" '単位設定セル'
Const celNow As String = "E2" '現時刻表示セル'
Const celCal As String = "E3" '丸め時刻表示セル'
Const timeFormat As String = "h:mm" '時刻の書式'

unit = Range(celUnit)
calTime = SecRound(Now)


If unit <> 1 Then
calTime = MinRound(DateAdd("s", 1, calTime), unit)

End If

Range(celNow) = Now
Range(celNow).NumberFormatLocal = timeFormat

Range(celCal) = calTime
Range(celCal).NumberFormatLocal = timeFormat

End Sub

SecRoundファンクション

Function SecRound(ByVal nowTime As Date) As Date

Dim calTime As Date '計算した時刻'
Const half As Integer = 30 'しきい値'
Const convert As Integer = 1440 '一日を分に換算'


If Second(nowTime) >= half Then
calTime = WorksheetFunction.Ceiling(nowTime, 1 / convert)

Else
calTime = WorksheetFunction.Floor(nowTime, 1 / convert)

End If

SecRound = calTime

End Function

MinRoundファンクション

Function MinRound(ByVal nowTime As Date, ByVal unit As Integer) As Date

Dim calTime As Date '計算した時刻'
Dim half As Integer 'しきい値'
Dim remain As Integer '分の剰余'
Dim split As Integer '一日を単位時間で分割'

Const convert As Integer = 1440 '一日を分に換算'

half = WorksheetFunction.Round(unit / 2, 0)
split = convert / unit
remain = Minute(nowTime) Mod unit


If remain < half Then
calTime = WorksheetFunction.Floor(nowTime, 1 / split)

Else
calTime = WorksheetFunction.Ceiling(nowTime, 1 / split)

End If

MinRound = calTime

End Function

汚いコードで申し訳ないです。m(_ _)m

秒丸めと分丸めを作ったので、Functionで分離しました。
少しは、可読性が上がるといいのですが… (;^_^A アセアセ・・・

VBAを覚え始めの方は、Functionでググってもらえれば、
情報はたくさん出てくると思います。

簡単に説明すると、計算部分だけ分離してプロシージャを作っています。
時間丸め⑧.jpg
時間取得のプロシージャは、現時刻の取得とシートの情報管理をします。
Functionで計算の分担をしてもらう感じです。

一つのプロシージャに、多量の計算式を書くと読みにくくなるので、
機能毎に分離して書くと、あとあと確認がしやすくなります。

記載したコードをコピーする場合は、標準モジュールに全部コピーして頂ければ使えるかと、
ただ、セル番号が変わると機能しないので、適宜セル番号を変更して頂ければいいかと思います。


話を戻しますが、
これでコーディングができたのでテストしてみます。
時間丸め⑫.jpg
表示形式は分まででいいのですが、比較しやすいように秒も設定しました。
上手く機能しています。

その他の単位も機能するか確認します。
時間丸め⑬.jpg
無事に機能しています。
15分、30分の逆転現象も無事に起こってます。

30分の境界辺りで確認してみます。
時間丸め⑭.jpg
上手く機能してくれました。ヽ(´▽`)/~♪

時間取得は、切り上げるか切り下げるか、なやみ処なんですが、
四捨五入のような処理は、見かけないので参考にでもなれば幸いです。

時間取得については、以上になります。
(^^)/~~デハデハ





この記事へのコメント