Excel実践

Excelトップへ
Excel実践一覧へ

動画内の準備ファイルは必要に応じて配布しています。
古いコンテンツだと用意されていない可能性があります。
用意されていないもので、演習に必要であればお問い合わせください。


2022年11月26日【ID:352】

【業務】好みの単位で時間を計算


入力されている時間を好みの単位で計算する方法について解説しています。
好みの単位とは「切り捨て5分単位」や「切り上げ10分単位」などになります。
勤怠入力などで、切り捨てや切り上げが考慮されずに「Ctrl + :」などで入力されていたとしても数式により自動で単位を調整できます。
※「Ctrl + :」は現在の時間を入力するショートカットキーです。

こちらの内容に関して完成ファイルをメンバーページより配布していますが、記事を見ながら実践できるように準備ファイルも配布してます。

▼準備ファイル▼




・準備ファイル

こちらの表の「黄色とオレンジ色」で塗りつぶされたセルに数式を埋めていきます。




・表の説明

C列の時間を元にD~F列の値を求めていきます。
例えば、C3「5:32」に対してのD3は「5:30」になるような数式を入力をします。
11行目に関しては列ごとの合計時間を計算します。
合計時間に関しては、24時間を超える場合の表示形式の設定方法について解説していきます。




・作成(切り捨て5分単位)

日付や時間はシリアル値という数字で管理されています。
シリアル値は1日を[1]として、ある日を基準に数えた通し番号になります。
そのため小数部分が時間になります。

つまり、[0.5]は1日の半分になり、12時を表現しています。
ただ、[0.5]のままだと何時なのかが分かりにくいため、表示形式により分かりやすい見た目にしています。

準備ファイルのC3を選択して[ホーム]→[数値]を確認すると[ユーザー定義]などと表示されているかと思います。

ここを標準にするとC3の値が数字になります。

※内容が確認できたら、表示形式を元に戻します。
 「Ctrl + Z」で1つ前に戻すことができます。

ここまでの内容で、時間の実体が数字であることが分かったかと思います。
数字だということが分かれば、後は5分単位の場合は5分単位のシリアル値の倍数になる値(余りを除く)を求めることで「切り捨て5分単位」を求めることができます。

5分のシリアル値は1日が「24時間×60分」なので、「5/(24×60)」になります。
倍数になる値(余りを除く)を求める関数にFLOOR関数があります。

=FLOOR(数値,基準値) ※数値:対象の値、基準値:倍数

実際にD3に以下のような数式を入力します。

=FLOOR(C3,5/(24*60))

確定すると「0.229167」という数字が表示されます。
これが「切り捨て5分単位」のシリアル値になります。

あとは表示形式を設定します。
表示形式は「h:mm」で設定していきます。

表示形式についての詳しい説明は以下で解説しています。
・セルの表示形式の基礎から応用(メンバー限定)

表示形式を設定すると「5:30」と表示されます。

ちなみに、倍数という理屈を理解していただくため以下①のような少し複雑な式を作成しましたが、5分など時間で計算する場合は以下②のように表現することもできます。

=FLOOR(C3,5/(24*60))

=FLOOR(C3,"0:05")

あとは一番下まで反映させます。




・作成(切り上げ10分単位)

切り上げの場合はCEILING関数になります。
使い方はFLOOR関数と同じで、基準値(第2引数)に関してはシリアル値でも「”0:10″」でも実現できます。

=CEILING(C3,"0:10")

あとは表示形式を設定し、一番下まで反映させます。




・作成(四捨五入15分単位)

四捨五入の場合はMROUND関数になります。
使い方はFLOOR関数やCEILING関数同じで、基準値(第2引数)に関してはシリアル値でも「”0:15″」でも実現できます。

=MROUND(C3,"0:15")

あとは表示形式を設定し、一番下まで反映させます。




・作成(合計時間)

最後に合計時間を計算します。
時間は最初に解説した通り、シリアル値で表現されているため、通常の足し算やSUM関数で求めることができます。

それぞれの項目に関してSUM関数で求めていきます。
ここで小技ですが、複数セルにまとめて合計を求めたい時は、便利なショートカットがあります。

その方法は、合計を表示させたいセルを選択し、「[Shift] + [Alt] + [―]」を同時に押すだけです。
※表によっては正しい範囲でSUM関数が表示されない可能性があるので、その場合は、計算元のセルを含んだ状態で全体選択 (例の場合はC3~F11を選択) し、 「[Shift] + [Alt] + [―]」 を同時に押すと正しく計算されます。

必要に応じて中央揃えするといいかもです。

ただ、値を確認すると24時間を超える表示がないため、正しい値でないことが確認できます。

この原因は表示形式にあります。
合計の範囲を選択して表示形式のユーザー定義を確認すると「h:mm」と表示されているかと思います。

「h」では24時間を超える表示ができません。
ちなみに、「mm」に関しても60分を超える表示ができません。

通常の時間表記を超えて表示させたい場合は「[]」で囲む必要があります。
今回の場合は、「h」に関してのみ24時間を超えて表示させる必要があるため、「[h]:mm」と設定します。
そのようにすると24時間以上で表示させることができるようになります。

というような感じで今回の内容は以上です。
色々応用できるかと思うので、参考になれば幸いです。


パソコンで開く場合は、共有用のリンクコピーがあるのでご活用ください!

▼完成ファイル▼

ダウンロード
※メンバー限定

 



Excelで警告が表示される場合



メンバー募集 メンバー募集
ショートカット学習ツール ショートカット学習ツール

是非、シェアお願いします!

  リンクコピー Twitter Facebook はてなブックマーク Pocket
Excelトップへ
Excel実践一覧へ

- 関連記事 -



- 人気の記事 -



- メンバー限定 [一覧] -



サイト累計閲覧数

864442

講座



ちょっとした機能 便利ツール
✨困ったとき✨ Excel
保護解除ツール


Excelトップへ
Excel実践一覧へ