No.011

時刻を5分単位で切り上げまたは切り捨てて表示するには 《CEILING・FLOOR・ROUNDUP》

2003/2007/2010/2013

そのときは勤務データの処理で、時刻データを何分かごとに切り上げたり切り捨てたりするための関数をお問い合わせいただきました。
いくつかの方法がありますが、今日は2つの方法でご紹介します。

例えば、「0:33」は「0:35」、「23:56」は「0：00」となるように、5分単位で切り上げる方法を中心にご案内します。

＜CEILING関数/FLOOR関数＞

基準値の倍数で切り上げるCEILING関数（関数の分類: 数学/三角）を使用します。

Excel2013以降のバージョンの場合は、CEILING.MATH関数（関数の分類: 数学/三角）で同様に指定してください。詳しくは後述＜ヘルプmemo＞をご参照ください。

式は次のとおり、引数［数値］に処理前の時刻データ、引数［基準値］に切り上げる基準単位を指定します。
時刻5分は「"0:5:0"」と書けます。
そのほか、TIME関数を使い「TIME(0,5,0)」を指定することもできます。

　　=CEILING(A2,"0:5:0")
　　=CEILING(A2,TIME(0,5,0))

関数の答えが出た後で、表示形式を［時刻］にしましょう。

基準値の倍数で切り捨てる場合はFLOOR関数（関数の分類: 数学/三角）を使用します。

　　=FLOOR(A2,"0:5:0")

しかし上図のセルC13、FLOOR関数の答えがおかしいですね。
これは、コンピューターの誤差によるものと思われます。時刻データの本来のシリアル値は小数値であり、しかも大変桁数の大きい値です。

このような時刻処理においては考えられる現象ですので、例えば引数［数値］に1秒プラマイしておいてやるといいと思います。
FLOORの場合は1秒加算、CEILINGの場合は1秒減算するというような処置です。
処置に対応した式は次のようになります。

　　=CEILING(A2-"0:0:1","0:5:0")
　　=FLOOR(A2+"0:0:1","0:5:0")

最初に申し上げたのですが、Excel2013からはCEILING.MATH関数、FLOOR.MATH関数をお使いください。

　　=CEILING.MATH(A2-"0:0:1","0:5:0")
　　=FLOOR.MATH(A2+"0:0:1","0:5:0")

＜ROUNDUP関数/ROUNDDOWN関数＞

処理前の時間を5分で割った値をROUNDUP関数（関数の分類: 数学/三角）で切り上げ、5分を乗算することで結果が得られます。こちらは参考にしてください。

　　=ROUNDUP(A2/"0:5:0",0)*"0:5:0"

やはり方法1と同様に誤差が生じる可能性がありますので、1秒プラマイの処置をした方がいいでしょう。式は次のとおりです。

　　=ROUNDUP((A2-"0:0:1")/"0:5:0",0)*"0:5:0"

切り捨てる場合はROUNDDOWN関数（関数の分類: 数学/三角）を使用します。

　　=ROUNDDOWN((A2+"0:0:1")/"0:5:0",0)*"0:5:0"

四捨五入の場合は、MROUND関数またはROUND関数を利用します。
MROUND関数はExcel2003以前のバージョンでは、［分析ツール］アドインを追加する必要があります。
アドインの追加方法は、［ツール］メニュー－［アドイン］をクリックし、［アドイン］ダイアログ ボックスの一覧より［分析ツール］をオンにし［OK］をクリックします。