エクセル関数で時間を5分刻みに切り上げ・切り捨てする方法

ここではエクセル関数を使って5分刻みに切り上げる、または切り捨てることができる数式を紹介します。

 

始めに切り上げの方法を、次に切り捨ての方法を解説し、それぞれポイントも説明ます。

 

理解すれば応用として、30分単位など時刻を好きな単位で切り上げ・切り捨てすることができるようになりますよ。

1.時間を5分刻みで切り上げる

まずは、時間を5分刻みで切り上げる方法からです。

 

基本的な手順とポイントを確認します。

 

1-1.5分単位で切り上げる基本的な数式の書き方

時間を5分単位で切り上げる場合には、「CEILING関数」を使います。

 

では、CEILING関数の数式の形と、使用例を見てみましょう。

 

1-1-1.数式の形は

CEILING関数とは「指定した基準値の倍数のうち、一番近い値に切り上げ」した値を返す関数です。

 

CEILING関数の数式の形は

=CEILING(数値,基準値)

です。

項目名 指定する内容 指定の例
数値 切り上げたい時間を指定 A2 または "0:01"
基準値 倍数の基準になる値を指定 "0:5:0" またはC1

「数値」には、「切り上げたい時間」を指定します。

 

時間の入ったセル番号か、時間を「"」で囲んで指定します。

 

「基準値」には、「倍数の基準になる値」を指定します。倍数の基準ってなに?と思いますが、要は「何分(何時間)単位で切り上げるか?」を指定するところです。

 

こちらも、基準値の入ったセル番号か、時間を「"」で囲んで指定します。

 

1-1-2.CEILING関数の使用例

例えばセルA2にある時刻を5分単位で切り上げたい場合、数式は

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

となります。

 

ではCEILING関数の引数ダイアログを見てみましょう。

セルA2の時刻を5分単位に切り上げたい場合、数値は「A2」、基準値は「"0:5:0"」となります。

 

特に基準値の書き方に悩むと思うのですが、時刻の場合ポイントは「""(ダブルクォーテーションで囲むこと」で、囲まないとエラーメッセージが出てしまいます。

その他、入力パターンは次のようになります。

・数値、基準値共に数式に直接入力

 =CEILING("0:01","0:5:0")

・数値、基準値共にセルに入力し参照

 =CEILING(A2,C1)

 ※数値をセルA2、基準値をセルC1に入力した場合

 

これで5分刻みの切り上げることができます。

 

なお、応用として基準値を「"0:10:0"」とすれば10分刻み、「"0:30:0"」とすれば30分刻みになります。

 

1-2.表示とデータの差異に気を付ける

CEILING関数で時刻を切り上げるときに気を付けたいのは「表示とデータ上の値の差異」です。

 

例えば表示上は「00:05」と分単位でも、データ上は「00:05:01」などと秒まで入っていると、切り上げた結果は「00:10」となり、見た目正しく計算されていないように見えます。

例えば上の2つはどちらも表示上「00:05」となっています。しかし、切り上げると結果は違っています。

 

これはセルA2の値が「00:05:00」なのに対して、セルA3の値が「00:05:01」だからです。

 

この問題に関する対策の一つは「表示を秒単位までに変える」ことです。

これで表示とデータの値が一致します。

 

方法は簡単で、対象のセルを範囲指定後、「ホーム」タブにある「数値」内のプルダウンを「時刻」に変えるだけです。

 

また、もう一つの対策はデータの値を分単位に変えることです。

 

こちらも関数を使ってできますが、説明が長くなるので方法は第3章で解説します。

2.時間を5分刻みで切り捨てる

次に、時間を5分刻みで切り捨てる方法です。

 

こちらも基本的な手順とポイントを確認します。

 

2-1.5分単位で切り捨てる基本的な数式の書き方

時間を5分単位で切り捨てる場合には、「FLOOR関数」を使います。

 

では、FLOOR関数の数式の形と、使用例を見てみましょう。

 

1-2-1.数式の形は

FLOOR関数とは「指定した基準値の倍数のうち、一番近い値に切り捨て」した値を返す関数です。

 

FLOOR関数の数式の形は

=FLOOR(数値,基準値)

です。

項目名 指定する内容 指定の例
数値 切り捨てたい時間を指定 A2 または "0:01"
基準値 倍数の基準になる値を指定 "0:5:0" またはC1

「数値」には、「切り捨てたい時間」を指定します。

 

切り捨てたい時間が入ったセル番号か、時間を「"」で囲んで指定します。

 

「基準値」には、「倍数の基準になる値」を指定します。こちらの入れ方はCEILING関数と同じです。

 

1-2-2.FLOOR関数の使用例

例えば、セルA2にある時刻を5分単位で切り捨てたい場合、数式は

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

となります。

 

ではFLOOR関数の引数ダイアログを見てみましょう。

セルA2の時刻を5分単位に切り捨てたい場合、数値は「A2」、基準値は「"0:5:0"」となります。

 

こちらも時刻は「""(ダブルクォーテーションで囲む」ようにしましょう。

 

その他、入力パターンは次のようになります。

・数値、基準値共に数式に直接入力

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

・数値、基準値共にセルに入力し参照

 =FLOOR(A2,C1)

 ※数値をA2に、基準値をセルC1に入力した場合

 

これで、5分刻みで切り捨てることができます。

 

なお、こちらも応用として基準値を「"0:10:0"」とすれば10分刻み、「"0:30:0"」とすれば30分刻みになります。

 

2-2.表示とデータの差異に気を付ける

FLOOR関数で時刻を切り捨てるときに気を付けたいのは、CEILING関数と同様に「表示とデータの差異」です。

 

こちらも秒単位までに表示を変えるか、データの値を分単位に変換するようにしましょう。

3.値を分単位に変換する方法

最後に、データの値を分単位に変換する方法を紹介します。

 

これには、「秒を切り捨てる方法」と「切り上げる方法」の2パターンあります。

 

ただ、分単位に変換すると言ってもこれまで解説してきた方法の応用で、使う関数はCEILING関数かFLOOR関数です。

 

3-1.秒を切り上げて分単位にする

秒を切り上げるにはCEILING関数を使い、基準値を「"0:1:0"」とします。

これで、秒をすべて分単位に切り上げることができます。

 

そして、分単位に切り上げた値をさらに5分刻みに切り上げることもできます。

 

数式の形は、次の通りです。

=CEILING(CEILING(数値,"0:1:0"),基準値)

 

ひとつ例を見てみましょう。

上の例では、セルA2の値「0:05:01」をCEILING関数の基準値を「"0:1:0"」にすることで一度分単位にし、その値をさらにCEILING関数の基準値を「"0:5:0"」にすることで5分刻みにしています。

 

数式は、次の通りです。

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

 

このように、CEILING関数を2つ重ねることで秒の部分を切り上げ、その値を5分刻みに切り上げることができます。

 

なお、5分刻みのところにCEILING関数ではなくFLOOR関数を使えば、「5分刻みに切り捨てる」数式にもできます。

 

3-2.秒を切り捨てて分単位にする

次に秒を切り捨てるにはFLOOR関数を使い、基準値を「"0:1:0"」とします。

これで、秒をすべて切り捨て分単位にすることができます。

 

そして、切り捨てて分単位にした値をさらに5分刻みに切り上げることもできます。

 

数式の形は、次の通りです。

=CEILING(FLOOR(数値,"0:1:0"),基準値)

 

ひとつ例を見てみましょう。

上の例では、セルA2の値「0:05:01」をFLOOR関数の基準値「"0:1:0"」にすることで一度秒単位を切り捨て、その値をさらにCEILING関数の基準値を「"0:5:0"」にすることで5分刻みに切り上げています。

 

数式は、次の通りです。

数式の形は、次の通りです。

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

 

このように、FLOOR関数とCEILING関数を組み合わせることで、秒の部分を切り捨て、その値を5分刻みに切り上げることができます。

 

なお、5分刻みのところにCEILING関数ではなくFLOOR関数を使えば、「5分刻みに切り捨てる」数式にもできます。

 

以上参考になれば幸いです。

 


口コミ・評判のいいExcel本の紹介】


Top