今回は、時間を10分単位などに丸める方法として、関数CEILINGと関数FLOORの使い方を紹介していこう。これらの関数は「数値」を「指定した基準値」に丸めるときに利用するのが一般的であるが、「時間」に対して活用することも可能だ。数分程度を誤差として扱いたいときの具体的な対処方法として覚えておくと役立つだろう。

時間を計算するときに配慮すべき問題点

今回は、「関数CEILING」と「関数FLOOR」の少し応用的な使い方を紹介していこう。これらの関数は、数値を10単位や100単位などに丸めるときに利用するのが一般的だ。これまでの連載でも紹介してきたように、Excelは「日付」や「時間」を数値として扱うため、これらの関数を使って「時間」を10分単位などに丸めることも可能である。その具体的な活用方法を紹介していこう。

  • 基準値に数値を丸める関数、CEILINGとFLOORの使い方

まずは、今回の連載で利用する表から紹介していこう。以下の表は、あるレンタルスペースにおいて利用者の「入室時刻」と「退出時刻」をまとめたものだ。業種のイメージとしては、カラオケ店でもよいし、ワーキングスペースでも、漫画喫茶でも何でもよい。「30分につき240円」で個室を貸し出すサービス、ということを認識して頂ければ十分だ。

  • 「入室」と「退出」の時刻をまとめた表

この表をもとに各利用者の「利用料金」をExcelで計算していこう。それぞれの「利用時間」は、（退室時刻）－（入室時刻）の数式で算出できる。この考え方は、第9回の連載で紹介した「勤務時間」を計算する場合と基本的に同じだ。

  • 利用時間の計算

  • 算出された利用時間

あとは数式をオートフィルでコピーするだけ。これで各利用者の「利用時間」を求めることができる。

  • 数式をオートフィルでコピー

第9回から第11回の連載を読んだ方なら、ここまでの話はすぐに理解できるだろう。ただし、この表をそのまま実務で使うとなると、少し問題が生じる恐れがある。というのも、利用時間が「1時間1分」や「2時間2分」などの値になってしまう可能性があるからだ（上図で背景を赤く示した部分）。

料金設定が「30分につき240円」の場合、杓子定規に利用料金を計算すると、「1時間1分」の利用者には30分×3（1時間30分）の料金を請求することになる。とはいえ、たった1分オーバーしただけなのにプラス30分の料金を請求する、というのはあまりにも酷な話ではないだろうか？

状況によっては、「受付が混んでいたので数分ほど遅れてしまった・・・」というケースもあるだろう。このような場合、現場の判断で「退室時刻」を調整してもよいが、どこまで許容してよいか迷うケースもあるし、余計な手間が増えてしまうことにもなる。

よって、ある程度は料金計算に“曖昧さ”を残しておく必要がある。このような場合に、関数CEILINGや関数FLOORが活用できる。

基準値に丸める関数、CEILINGとFLOORの使い方

具体的な手順を示す前に、「関数CEILING」と「関数FLOOR」の基本的な使い方を紹介しておこう。それぞれの関数は、以下に示した書式で記述する決まりになっている。

■関数CEILINGの書式 　=CEILING(数値, 基準値)

■関数FLOORの書式 　=FLOOR(数値, 基準値)

第1引数には「丸める前の数値」をセル参照などで指定する。続いて、第2引数に「基準となる数値」（単位）を指定する。たとえば、10単位に丸めたい場合は、第2引数に「10」を指定すればよい。なお、関数CEILINGを使用した場合は「切り上げ」、関数FLOORを使用した場合は「切り下げ」により数値を丸める仕様になっている。

簡単な例も紹介しておこう。たとえば、B4セルの数値を「10単位」に「切り上げ」して丸める場合は、以下の図のように関数CEILINGを記述する。

  • 関数CEILINGの入力

同様に、5行目以降についても関数CEILINGで「10単位に切り上げ」していくと、以下の図のような結果になる。

  • 関数CEILINGにより「切り上げ」された数値

数値を「切り下げ」して丸める関数FLOORの記述例も紹介しておこう。今度は「5単位」に数値を丸めた場合の例を掲載しておこう。

  • 関数FLOORの入力

  • 関数FLOORにより「切り下げ」された数値

どちらも難しい関数ではないので、すぐに使い方を覚えられるだろう。今回の連載でポイントとなるのは、「数値」ではなく「時間」をどのように指定していくかだ。

利用時間を10分単位に「切り下げ」して丸める

話を元に戻して、レンタルスペースの「利用時間」を計算する具体的な手法を紹介していこう。数分程度のタイムオーバーを許容してあげる場合は、利用時間を「10分単位」などに「切り下げ」するのが最も簡単で効果的な手法となる。

この場合、「1時間1分」は「1時間」に切り下げされることになる。同様に、「1時間39分」は「1時間30分」に切り下げされることになる。つまり、最大9分まで「利用時間」を大目に見てあげる（オマケしてあげる）という訳だ。

第10回の連載でも紹介したように、Excelは24時間を「1」として扱う仕組みになっている。このため、1時間は「1/24」という数値になる。これをさらに60分割したものが1分に相当する。よって、10分は「10/60/24」という数値（数式）で指定できる。これを関数FLOORの第2引数に指定し、「利用時間」を10分単位に丸める。

  • 関数FLOORの入力

「Enter」キーを押して結果を確認すると、0.034722・・・という数値が表示される。これは時間を「シリアル値」で示したものだ。

  • 関数FLOORで10分単位（10/60/24）に切り下げた結果

このセルの表示形式に「時刻」を指定して「時:分」の形式で表示すると、以下の図のようになる。「利用時間」を10分単位に丸められていることを確認できるだろう。

  • 表示形式を「時:分」に変更

そのほか、関数FLOORの第2引数に「時間」を直接指定することも可能となっている。この場合はダブルクォーテーションで囲んで「"時:分"」と記述すればよい。よって、10分単位に「切り下げ」する場合は、以下のように関数FLOORを記述しても構わない。こちらの方が直感的に関数を記述できるといえるだろう。

  • 基準値を「時間」で入力した場合

あとは、この関数FLOORをオートフィルでコピーするだけ。これで若干のタイムオーバーを許容してあげらるようになる。

  • 関数FLOORをオートフィルでコピー

なお、セルの数を少しでも減らしたい場合は、「利用時間」の列を省略し、関数FLOORの第1引数で利用時間を計算してもよい。この場合の記述は「=FLOOR(E5-D5,"0:10")」となる。

30分単位に「切り上げ」して請求用の時間を求める

続いては、先ほどの「丸め時間」をもとに利用料金を計算していこう。この方法は色々と考えられるが、今回は関数CEILIGで「30分単位」に「切り上げ」した時間を利用していこう。

30分単位に切り上げる場合も、基本的な考え方は先ほどと同じだ。関数CEILINGの第2引数に「"0:30"」を指定すればよい。

  • 関数CEILINGの入力

その後、表示形式を「時刻」に変更すると、30分単位に「切り上げ」した時間を表示できる。

  • 表示形式を「時:分」に変更

オートフィルで関数CEILINGをコピーすると、各利用者について「30分単位に切り上げた請求用の時間」を求められる。

  • 関数CEILINGをオートフィルでコピー

30分単位で利用料金を計算する

あとは「請求時間」を30分で割り算し、「30分毎の料金」（240円）を掛け算するだけだ。このとき、数式の中に「時間」を直接記述することも可能である。具体的には、先ほど同様に「時間」をダブルクォーテーションで囲んで記述すればよい。

たとえば、（H5セル）÷（30分）×（D2セル）の数式は、以下の図のように記述できる。なお、「30分の料金」を示すD2セルは、オートフィルでコピーしたときにセル番号が変化しないように絶対参照で記述してある。

  • 料金を計算する数式（時間で割り算）

あとは、このセルに「通貨」の表示形式を指定するだけ。これで「利用料金」を計算できる。この例の場合、利用時間（丸め時間）は約50分になるため、1時間ぶんの料金（30分の料金×2）、つまり480円という結果になる。

  • 算出された利用料金

最後に、この数式をオートフィルでコピーすると計算表が完成する。それぞれの結果を見ていくと、料金が正しく計算されていることを確認できるだろう。

  • 数式をオートフィルでコピー

今回の連載で紹介したように、ある程度の時間オーバーを許容してあげる場合には「関数FLOOR」が便利に活用できる。一方、単位時間に切り上げて料金を計算するときには「関数CEILING」が活用できる。ほかにも、さまざまな事案に応用できると思うので、その“手法”と“考え方”をよく理解しておくとよいだろう。