Introduction
Ver. all 2013 2016 2019 2021 365
Excel（エクセル）で時刻を5分単位や10分単位１時間単位など、まとまった時間ごとに表示する方法を紹介します。
使用するのは切り上げの場合は「CEILING関数」、切り捨てする場合は「FLOOR関数」を使用します。
それぞれの関数の詳しい説明は下にあります。
給与計算に役立ちそうだにゃ
時間単位で表示する方法
今回は、下の表の「時刻」を5分単位で表示するようにしてみます。
5分単位で切り上げてみる
まず、「B列」に5分単位で切り上げた時刻を表示します。
使用するのは「CEILING関数」。指定した数の倍数で切り上げてくれる関数です。
CEILING・・・基準値の倍数で、最も近い値に数値を切り上げる。
=CEILING（数値,基準値）
- 数値・・・切り上げたい数値（対象セル）を指定
- 基準値・・・倍数の基準となる数値を指定（省略不可）
※EXCEL2013からは、新しく「CEILING.MATH関数」が登場しています。ここでは互換性を考えてCEILING関数で説明しますが、「CEILING.MATH関数」を使用する場合は、Excel（エクセル）のCEILING関数とCEILING.MATH関数の違いをご覧ください。
では、やってみます。関数を入れるセルを選択して、［関数の挿入］ボタンをクリック。
［関数の分類］は［すべて表示］にして、「CEILING」を選びます。
表示された［関数の引数］画面の［数値］の場所にはすでに入力してある「時刻」のセル「A2」を選択します。
［基準値］の場所には5分で区切りたい場合は「”0:5:0″」と入力します。
※10分の場合は「”0:10:0″」、30分の場合は「”0:30:0″」。
時刻を指定する場合のポイントは、
- ダブルクォーテーション「”」で囲む
- 時刻形式「00:00:00」で指定する
この２つが重要になります。
※時刻形式は、今回のように1桁で指定しても大丈夫です。
また、2時間単位の場合は「”2:0:0″」になりますし、10秒単位の場合は「”0:0:10″」になります。
完成した関数は
=CEILING(A2,"0:5:0")
［OK］を押した結果が下の画面。「あれ？時刻になっていない」
エクセルでの時刻は、「0時」を「0」、「24時」を「1」としてその間は小数点の値で認識しています。
では、表示形式（見た目）を時刻表示に設定しましょう。
［ホーム］タブ → ［数値の書式］をクリックして、
一覧から［時刻］を選びます。
時刻表示になりました。
しかし、左の書式と合っていませんね。細かく時刻書式を設定したい場合は、先ほどの［数値の書式］ボタンの右下にある［表示形式］起動ツールをクリックして、
表示された［セルの書式設定］ダイアログボックスの［表示形式］タブの左側で「時刻」が選択されている状態で、画面右側で好きな書式を選択します。
もし、この場所に気に入ったものが無い場合は、左側で１番下にある［ユーザー定義］を指定し、右側の下の方にある「h」や「m」を使用した表示形式を選びましょう。
時刻の表示形式に関しては、Excel（エクセル）の表示形式の一覧（日付と時刻・時間）に詳しい説明があるので興味のある方はご覧ください。
今回は、「h:mm」を選択すると、
左の「時刻」と同じ表示形式になります。
あとは、オートフィル機能を使用してコピーすればできあがり。
時間を扱う場合には注意すべきことがあります。最後に書いた「時間計算をする時の注意事項」をチェックしてください。
5分単位で切り捨ててみる
今度は5分単位で切り捨ててみます。
やり方は切り上げる時と一緒ですが、使用する関数が「CEILING関数」から「FLOOR関数」に変わります。
FLOOR関数・・・基準値の倍数で、最も近い値に数値を切り下げる。
FLOOR（数値,基準値）
- 数値・・・切り下げたい数値（対象セル）を指定
- 基準値・・・倍数の基準となる数値を指定
※EXCEL2013からは、新しく「FLOOR.MATH関数」が登場しています。ここでは互換性を考えてFLOOR関数で説明しますが、「FLOOR.MATH関数」を使用する場合は、Excel（エクセル）のFLOOR関数とFLOOR.MATH関数の違いをご覧ください。
では、やってみます。［関数の挿入］画面で選ぶ関数は「FLOOR関数」になります。
入力する引数も先ほどと同じ。
完成した関数は
=FLOOR(A2,"0:5:0")
やはり、数値で結果が表示されるので、時刻の表示形式に変更しましょう。
後は、下にコピーすれば完了です。
切り上げるときの「CEILING関数」とまったく一緒ですね。
時間計算をする時の注意事項
時間計算をする際には、注意したいことがあります。
下の表をご覧ください。
「出勤時刻」と「退勤時刻」が入力され、「勤務時間」の場所には「退勤時刻」から「出勤時刻」を引いたものを10分単位で切り捨てた式が入っています。
=FLOOR(B2-A2,"0:10:0")
分かりやすくするために、「勤務時間」は全て１時間になるように設定したのですが、赤枠で囲んだ部分が正確に表示されていません。
これは、途中で書いたようにエクセルでの時刻は、「0時」を「0」、「24時」を「1」としてその間は小数点の値で認識してるのが原因です。
小数点の計算で誤差が出る可能性があるのです。
そのため、分計算の場合は、この誤差を無くすために元の時間に1秒足し引きしてしまいましょう。
- CEILING関数・・・１秒引いてしまう・・・「-“0:0:1″」
- FLOOR関数・・・１秒足してしまう・・・「+”0:0:1″」
こうすれば正確に表示されます。「分」計算の場合は「秒」は考えに入れておらず、それぞれ切り上げ、切り捨てされるので問題にならないということ。
先ほどの式は、FLOOR関数なので、下のように1秒足すように修正します。
=FLOOR(B2-A2+"0:0:01","0:10:0")
これで正確に時間が計算されるようになります。
まとめ
時刻を指定する場合は、途中の説明にあったように、引数の［基準値］をダブルクォーテーション「”」で囲んであげるのがポイントです。
また、時間を区切る場合もちょっと注意が必要です。下の画像をご覧ください。
「分」ではなく「時」を2時間単位にしたものです。
本来、「0:00」になっている場所は「24:00」になって欲しい場所です。
エクセルは、「24時間を超えた場合は、24で割った余りの時間を表示する」ことになってるので、この結果になります。
しかし表示形式で対応することができます。修正したい時間のセル全体を選択して、
［表示形式］起動ツールをクリックして、
表示された［セルの書式設定］ダイアログボックスの［表示形式］タブの左側で「ユーザー定義」の場所を選び、右側の［種類］の場所で自分で書式を作成します。
最初に設定されていた時間の場所である「h」の文字を「［］」で囲んであげます。
これでOK。表示がちゃんと足された時間になります。
時間の表示に関しては、Excel（エクセル）で24時間以上を計算する方法｜勤務時間や残業時間の「時」「分」を合計するに詳しい説明があるので興味のある方はご覧ください。
【関連】
時間の扱いには一工夫必要にゃ