Excel(エクセル)で時刻や時間を、5分単位や10分単位で切り上げ、切り捨て表示する方法

スポンサーリンク
スポンサーリンク

Introduction

Excel(エクセル)で時刻を5分単位や10分単位1時間単位など、まとまった時間ごとに表示する方法を紹介します。

使用するのは切り上げの場合は「CEILING関数」、切り捨てする場合は「FLOOR関数」を使用します。

それぞれの関数の詳しい説明は下にあります。

Excel(エクセル)で指定した値で数値(端数)を切り上げる方法|CEILING関数の使い方

Excel(エクセル)で指定した値で数値(端数)を切り下げる(切り捨てる)方法|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」で指定する

この2つが重要になります。

※時刻形式は、今回のように1桁で指定しても大丈夫です。

また、2時間単位の場合は「”2:0:0″」になりますし、10秒単位の場合は「”0:0:10″」になります。

完成した関数は

=CEILING(A2,”0:5:0″)

[OK]を押した結果が下の画面。「あれ?時刻になっていない」

エクセルでの時刻は、「0時」を「0」、「24時」を「1」としてその間は小数点の値で認識しています

では、表示形式(見た目)を時刻表示に設定しましょう。

[ホーム]タブ → [数値の書式]をクリックして、

一覧から[時刻]を選びます

時刻表示になりました。

しかし、左の書式と合っていませんね。細かく時刻書式を設定したい場合は、先ほどの[数値の書式]ボタンの右下にある[表示形式]起動ツールをクリックして、

表示された[セルの書式設定]ダイアログボックスの[表示形式]タブの左側で「時刻」が選択されている状態で、画面右側で好きな書式を選択します。

もし、この場所に気に入ったものが無い場合は、左側で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″)

分かりやすくするために、「勤務時間」は全て1時間になるように設定したのですが、赤枠で囲んだ部分が正確に表示されていません。

これは、途中で書いたようにエクセルでの時刻は、「0時」を「0」、「24時」を「1」としてその間は小数点の値で認識してるのが原因です。

小数点の計算で誤差が出る可能性があるのです。

そのため、分計算の場合は、この誤差を無くすために元の時間に1秒足し引きしてしまいましょう。

  • CEILING関数・・・1秒引いてしまう・・・「-“0:0:1″」
  • FLOOR関数・・・1秒足してしまう・・・「+”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時間以上を計算する方法|勤務時間や残業時間の「時」「分」を合計するに詳しい説明があるので興味のある方はご覧ください。

 

【関連】

 

時間の扱いには一工夫必要にゃ