ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.011

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

Excel2003/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))

CEILING関数

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

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

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

FLOOR関数

しかし上図のセル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")

CEILING.MATH関数

<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"

ROUNDUP関数

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

ヘルプmemo

<CEILING、FLOOR関連関数とバージョンについて>

CEILING関数、FLOOR関数ともに、次のような類似の関数が用意されています。

CEILINGでいいますと、CEILING.PRECISE関数、ISO.CEILING関数、CEILING.MATH関数、FLOORでも同様にそれらの関数があります。

CEILING.PRECISE関数、ISO.CEILING関数はExcel2010より登場しますが、関数一覧より選択できるのはCEILING関数とCEILING.PRECISE関数のみ、CEILING関数と引数構成は同じです。

Excel2013になりますと、CEILING関数、CEILING.PRECISE関数は一覧から消え、CEILING.MATH関数のみとなります。
CEILING関数などが使用できなくなったわけではないですが、方向性としてCEILING.MATH関数に一本化されるような方向性です。
引数構成などは、下記<ヘルプmemo>でご確認ください。

ヘルプmemo

<CEILING.MATH関数 (数学/三角関数)>

数値を最も近い整数、または最も近い基準値の倍数に切り上げます。
 数式: =CEILING.MATH(数値, 基準値, モード))
 引数:
  数値... 対象となる数値を指定
  基準値... 倍数の基準となる数値を指定[省略可]
  モード... 負の数値の場合の丸め方を指定[省略可]
 ヒント:
  引数[基準値]を省略した場合(既定)は、正の数値は1、負の数値は-1です。
  引数[モード]は、省略するか0を指定すると、正の数値は0から離れた整数に、
    負の数値は0に近い整数に切り上げます。
    0以外の数値を指定すると、正の数値も負の数値も0から離れた整数に
    切り上げます。

FLOOR.MATH関数 (数学/三角関数)は、CEILING.MATH関数と同様の引数構成で、数値を基準値の倍数に切り捨てます。

関連ヘルプ

奇数行の合計、偶数行の合計、n行おきの合計を求める 《配列数式/IF・SUM・MOD》
フィルターで抽出されたデータの件数、合計をセルに表示したい 《SUBTOTAL》
検索条件に一致するデータの合計を求める 《SUMIF》
OR条件に当てはまるデータの合計を求める 《SUMIF・SUMIFS・SUMPRODUCT・DSUM》
10進数の数値を2進数と16進数に変換するには 《DEC2BIN・DEC2HEX》
ページのトップへ戻る