ひっそりとExcel関数のおさらい（時間の切り上げ・切り捨て）
エクセルやVBAで、時間のデータを切り上げ・切り捨てするにはどうすればいいでしょうか？
うーん･･････。Round関数？DateAdd関数？
通常の数値と違い、年・月・日・時・分・秒 をプログラム上でどう扱うか悩む人は少なからずいると思います。（私もその一人です）
と、いっても時間を都合よく切り捨て・切り上げするための専用の関数はないので、何かしら別の関数で代用することになります。
時間データも元をたどれば、シリアル値という数値データであることを利用して、数値を切り上げ・切り捨てすることができる CEILING関数 と FLOOR関数を使用しましょう。
目次
時間を切り上げる（CEILING関数）
=CEILING(数値,基準値)
CEILING関数は第1引数の数値を、第2引数に指定した基準値の倍数のうち一番近いものに切り上げします。
･･････と言葉で説明しても、とっても分かりにくいと思うので、簡単な例で示すと、
=CEILING(数値,5) であれば
| 数値 | 返り値 |
|---|---|
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
| 6 | 10 |
| 7 | 10 |
| 8 | 10 |
| 9 | 10 |
| 10 | 10 |
| 11 | 15 |
| 12 | 15 |
| 13 | 15 |
| 14 | 15 |
| 15 | 15 |
というふうに、 5 きざみで切り上げされたものが、返り値として返ってきます。
では、ここから本題です。
この関数を使って、時間を切り上げるにはどうしたらいいでしょう？
時間はシリアル値で、24時間を 1 とし、それよりも細かい 時・分・秒 は小数点以下で表されます。12時間であれば 0.5 、1時間であれば 0.0416666666666667 というふうに。
なので、時間を1時間きざみで切り上げしたいのであれば、 =CEILING(ｼﾘｱﾙ値,0.0416666666666667) とすればいいことが分かります。
でも、この書き方はメンドウなので、 =CEILING(ｼﾘｱﾙ値,1/24) または、時間と認識できる文字列で =CEILING(ｼﾘｱﾙ値,"1:00") としましょう。
※以下、ｼﾘｱﾙ値は時間と表記します
5分きざみで切り上げする例
=CEILING(時間,5/(24*60))
| 時間 | 時間のシリアル値 | 返り値 | しきい値［5/(24*60)のn倍］ | n |
|---|---|---|---|---|
| 0:11 | 0.007638888888888880 | 0:15 | 0.010416666666666700 | 3 |
| 0:12 | 0.008333333333333320 | 0:15 | 0.010416666666666700 | 3 |
| 0:13 | 0.009027777777777760 | 0:15 | 0.010416666666666700 | 3 |
| 0:14 | 0.009722222222222200 | 0:15 | 0.010416666666666700 | 3 |
| 0:15 | 0.010416666666666600 | 0:15 | 0.010416666666666700 | 3 |
| 0:16 | 0.011111111111111100 | 0:20 | 0.013888888888888900 | 4 |
| 0:17 | 0.011805555555555500 | 0:20 | 0.013888888888888900 | 4 |
| 0:18 | 0.012500000000000000 | 0:20 | 0.013888888888888900 | 4 |
| 0:19 | 0.013194444444444400 | 0:20 | 0.013888888888888900 | 4 |
| 0:20 | 0.013888888888888800 | 0:20 | 0.013888888888888900 | 4 |
| 0:21 | 0.014583333333333200 | 0:25 | 0.017361111111111100 | 5 |
| 0:22 | 0.015277777777777600 | 0:25 | 0.017361111111111100 | 5 |
| 0:23 | 0.015972222222222000 | 0:25 | 0.017361111111111100 | 5 |
| 0:24 | 0.016666666666666400 | 0:25 | 0.017361111111111100 | 5 |
| 0:25 | 0.017361111111110800 | 0:25 | 0.017361111111111100 | 5 |
| 0:26 | 0.018055555555555200 | 0:30 | 0.020833333333333300 | 6 |
| 0:27 | 0.018749999999999600 | 0:30 | 0.020833333333333300 | 6 |
| 0:28 | 0.019444444444444000 | 0:30 | 0.020833333333333300 | 6 |
| 0:29 | 0.020138888888888400 | 0:30 | 0.020833333333333300 | 6 |
| 0:30 | 0.020833333333332800 | 0:30 | 0.020833333333333300 | 6 |
こんなふうに、時間が、直近のしきい値を超えていなければ、直近のしきい値に切り上げされ、返り値となって返されます。なお、n（倍数）はユーザーが特に意識することはなく、関数が自動的に増減します。
時間を切り捨てる（FLOOR関数）
=FLOOR(数値,基準値)
FLOOR関数は第1引数の数値を、第2引数に指定した基準値の倍数のうち一番近いものに切り捨てします。
考え方は、CEILING関数と同じであり、「切り上げ」が「切り捨て」になっているだけなので省略します。
5分きざみで切り捨てする例
=FLOOR(時間,5/(24*60))
| 時間 | 時間のシリアル値 | 返り値 | しきい値［5/(24*60)のn倍］ | n |
|---|---|---|---|---|
| 0:11 | 0.00763888888888888 | 0:10 | 0.00694444444444444 | 2 |
| 0:12 | 0.00833333333333332 | 0:10 | 0.00694444444444444 | 2 |
| 0:13 | 0.00902777777777776 | 0:10 | 0.00694444444444444 | 2 |
| 0:14 | 0.00972222222222220 | 0:10 | 0.00694444444444444 | 2 |
| 0:15 | 0.01041666666666660 | 0:10 | 0.01041666666666670 | 3 |
| 0:16 | 0.01111111111111110 | 0:15 | 0.01041666666666670 | 3 |
| 0:17 | 0.01180555555555550 | 0:15 | 0.01041666666666670 | 3 |
| 0:18 | 0.01250000000000000 | 0:15 | 0.01041666666666670 | 3 |
| 0:19 | 0.01319444444444440 | 0:15 | 0.01041666666666670 | 3 |
| 0:20 | 0.01388888888888880 | 0:15 | 0.01388888888888890 | 4 |
| 0:21 | 0.01458333333333320 | 0:20 | 0.01388888888888890 | 4 |
| 0:22 | 0.01527777777777760 | 0:20 | 0.01388888888888890 | 4 |
| 0:23 | 0.01597222222222200 | 0:20 | 0.01388888888888890 | 4 |
| 0:24 | 0.01666666666666640 | 0:20 | 0.01388888888888890 | 4 |
| 0:25 | 0.01736111111111080 | 0:20 | 0.01736111111111110 | 5 |
| 0:26 | 0.01805555555555520 | 0:25 | 0.01736111111111110 | 5 |
| 0:27 | 0.01874999999999960 | 0:25 | 0.01736111111111110 | 5 |
| 0:28 | 0.01944444444444400 | 0:25 | 0.01736111111111110 | 5 |
| 0:29 | 0.02013888888888840 | 0:25 | 0.01736111111111110 | 5 |
| 0:30 | 0.02083333333333280 | 0:25 | 0.01736111111111110 | 5 |
この表はCEILING関数の時と同じように見えますが（切り上げが、切り捨てになっただけ）、でも、この表はどこかがおかしいです。お気づきでしょうか？
FLOOR関数の不具合？
例えば、0:15 であれば、切り捨てられずに返り値が 0:15 となってほしいのですが、切り捨てられてしまっています。次の 0:20 、 0:25 、 0:30 も同じです。
なぜ、そうなるのか？
しきい値と、シリアル値を見比べると、しきい値のほうが僅かに大きいことが分かります。
なので、FLOOR関数さんは、「まだしきい値を超えてないので、切り捨てだね！」と判断して、一つ手前のしきい値で返り値を返してしまっているのです。
これは、厳密に言うとFLOOR関数の不具合ではなく、浮動小数点の演算誤差によって発生してしまっています。
FLOOR関数に限った話ではありません。
CEILING関数の場合にも、しきい値の誤差はありますが、切り上げ方向のため問題にならなかっただけです。
誤差があることはわかった。じゃあ、どうすればいいの？
方法はいくつかあるけど、例えば、第1引数が誤差分を確実に上回るようにしたらどうでしょう？
例：FLOOR(時間 + 1秒,5/(24*60))
1秒の箇所は、実際には 1/(24*60*60) や "00:00:01" と記述します。
VBAで使う際の注意点
VBAでも、ワークシート関数として CEILING関数 、 FLOOR関数 を使うことができます。ただし、第2引数を文字列 "0:05"とすることはできないようなので、下記のようにする必要があります。
- 直接数値で指定する：5/(24*60)
- 日付リテラルで指定する：#0:05#（自動的に #12:05:00 AM# のように変換されるが、意味は同じなので気にしなくてもいい）
- Date型の変数で指定する：事前に、変数 = "0:05" のように代入して引数に指定
- 関数で日付シリアル値に変換する：TimeValue("0:05")
