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

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

▼準備ファイル▼

・準備ファイル

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

・表の説明

Ｃ列の時間を元にＤ～Ｆ列の値を求めていきます。
例えば、Ｃ３「5:32」に対してのＤ３は「5:30」になるような数式を入力をします。
１１行目に関しては列ごとの合計時間を計算します。
合計時間に関しては、２４時間を超える場合の表示形式の設定方法について解説していきます。

・作成（切り捨て５分単位）

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

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

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

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

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

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

５分のシリアル値は１日が「２４時間×６０分」なので、「５／（２４×６０）」になります。
倍数になる値（余りを除く）を求める関数にＦＬＯＯＲ関数があります。

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

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

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

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

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

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

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

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

①

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

②

=FLOOR(C3,"0:05")

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

・作成（切り上げ１０分単位）

切り上げの場合はＣＥＩＬＩＮＧ関数になります。
使い方はＦＬＯＯＲ関数と同じで、基準値（第２引数）に関してはシリアル値でも「"0:10"」でも実現できます。

=CEILING(C3,"0:10")

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

・作成（四捨五入１５分単位）

四捨五入の場合はＭＲＯＵＮＤ関数になります。
使い方はＦＬＯＯＲ関数やＣＥＩＬＩＮＧ関数同じで、基準値（第２引数）に関してはシリアル値でも「"0:15"」でも実現できます。

=MROUND(C3,"0:15")

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

・作成（合計時間）

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

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

その方法は、合計を表示させたいセルを選択し、「[Ｓｈｉｆｔ] ＋ [Ａｌｔ] ＋ [―]」を同時に押すだけです。
※表によっては正しい範囲でＳＵＭ関数が表示されない可能性があるので、その場合は、計算元のセルを含んだ状態で全体選択 （例の場合はＣ３～Ｆ１１を選択） し、 「[Ｓｈｉｆｔ] ＋ [Ａｌｔ] ＋ [―]」 を同時に押すと正しく計算されます。

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

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

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

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

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

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

　

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

リンクの共有はこちらから行えます。

  リンクコピー Twitter Facebook はてなブックマーク Pocket