エクセルで24時間を超える時間の合計が変になる対策を徹底解説【25時間表示】

 

エクセルの時間の合計では、勤務時間や生産時間などの集計でよく行いますね。

しかし、その合計が変だと大変です。

この記事では、そんな24時間を超える場合の集計を行う方法について解説していきます。

 

対策方法としては、

があります。

サンプルを用いてみていきましょう。

エクセルで24時間を超える時間の合計が変になる原因と対策【条件付き書式】

エクセルで２４時間の合計を表示させたい場合は「セルの書式設定」を変更することで対策することができます。

サンプルでは勤務時間の合計を行っていますが、合計の表示が非常に少ないです。

これは、通常の「時刻」の書式設定では24時間で一度0とカウントされてリセットされてしまうからです。

なので、通常では24時間を超える時間の表示はできません。

例えば50時間は24時間×2と２時間なので、「2：00」と表示されてしまいます。

 

なので、書式設定を変更したいセルを選択し、

「右クリック」→「セルの書式設定」と選択して、「セルの書式設定」のメニューを表示させます。その後は

していきます。 

 

セルの書式設定が変更されて、24時間を超える時間の合計が表示されました。

セルの書式設定では通常の時間の書式は

h:mm

となっています。

ｈ:hour[時間]

ｍ:minute[分]

の略称なのですが、

このhを[　]で囲うことで、25時間以上の表示が可能となります。

 

エクセルで24時間を超える時間の合計が変になる原因と対策【TEXT関数】

上述においては、セルの書式設定で25時間以上の表示の方法を解説しましたが、

関数を使用すると「TEXT関数」を使用することでも同様の処理を行うことができます。

今回は、時間の合計なのでSUM関数と組み合わせて使用していきます。

まず、TEXT関数は

=TEXT(値 , 表示形式)

と入力していきます。

今回は、値のところにSUM関数を使用します。

なので、

=TEXT(SUM(C4:C10),”[h]:mm”)

と入力しましょう。

表示形式は先ほどのセルの書式設定と同様に

[h]:mm

を使用します。

“”（ダブルクォーテーション）で囲うのを忘れないように注意してください。

忘れるとエラーになります。

関数を入力してENTERを押すと、セルの書式設定の時と同様に25時間以上の合計が表示されました。

TEXT関数で表示形式を設定しておくと、式をコピペした時に、ペーストした先でもきちんと表示させることができます。

セルの書式設定は簡単ですが、コピペ先では再度、書式設定を行わなければいけなくなるデメリットもあります。

あまりコピペを行わないようなファイルでは「セルの書式設定」

コピペを頻繁に行うようなファイルでは「TEXT関数」

といった具合に使い分けるよよいですね。

 

 

エクセルで24時間を超える時間の合計が変になる原因と対策【便利マクロ】

「セルの書式設定」を自動で行ってくれるVBAのコードを紹介します。

コードは以下になります。

 

これを「個人用マクロブックの標準モジュール」に書き込んでください。

（作業中のブックでもかまいませんが、拡張子を.xlsmに変更しないと保存できません）

エディターはリボンにある「開発」タブにある「Visual Basic」をクリックすると開きます。

（Alt + F11を押しても開きます。）

 

VBAのコードの解説を行うと

 

SUB ユーザー設定24時間以上（）

マクロのタイトルです。

 Selection.NumberFormatLocal = “[h]:mm”

選択しているセルの書式設定を[h]:mmにせよ

 End Sub

マクロの終了

という意味になります。

 

これをエディターの画面右上にある「実行」をクリックして選択メニューの中から

「Sub/ユーザーフォームの実行」をクリックしましょう。

マクロが起動します。

 

マクロが起動して、選択していたセルの書式設定が変更されて25時間以上が表示されるようになりました。

毎回、マクロを起動させていては面倒です。

Alt + F8を押して、マクロの実行メニューを表示させましょう。

先ほどのマクロの名前を選択して、「オプション」をクリックしましょう。

「マクロオプション」メニューが開くので、

任意のショートカットキーを入力しましょう。

できるだけ、通常のショートカットキーと被らないものがいいです。

例えばCtrl + S や　Ctrl + V　などは保存やペーストのショートカットで便利なのでやめておくのがよいでしょう。

今回は特になにもない「Ctrl + e」としました。

OKをクリックするとショートカットキーが登録されます。

今後は、Ctrl + eのショートカットキーを押すとセルの書式設定が「[h]:mm」に変更されます。

  

まとめ　エクセルで24時間を超える時間の合計が変になる原因と対策

この記事では25時間以上の時間の集計の方法を解説しました。

対策方法としては①セルの書式設定 ②TEXT関数があります。

便利マクロも簡単なので使ってみてください。

以上、エクセルで24時間を超える時間の合計が変になる原因と対策でした。