エクセルの時間計算で悩まない超簡単な方法
エクセルで、セルに時間を入力すると、入力したとおりの「時間」が表示されます。
また、そのように時間を入力しておくと、時間の足し算・引き算も、非常に簡単にできるので、一見便利そうに見えます。
ところが、このエクセル標準の「時間」の処理には様々な欠点があります。
この記事の目次
エクセルの標準機能で時間を扱う欠点
１÷３×３＝１にならない？
突然ですが、電卓で「１÷３×３＝」と入れた場合に、どういう計算結果になるかを考えてみてください。
普通の電卓だと、計算結果は「１」ではなく「0.99999999999」になってしまいますよね？
この誤差が生じる原因は、電卓が「１÷３」の計算結果を「正確に」表現できていないからです。
「１÷３」は、本来は「0.3333・・・・」と3が無限に続く小数で表現しないといけないのに、桁数の制約で「0.33333333333」ぴったりだと取り扱ってしまいます。
その結果、最後に３をかけても１に戻りません。
これが、いわゆる「計算誤差」と呼ばれるものです。
実は、エクセルの標準機能で時間を管理する場合にも、全く同じ現象が起きてしまいます。
エクセルでは時間は小数で管理される
何も考えずにセルに時間を入力した場合、エクセル内部では「１時間＝２４分の１」「１分＝１４４０分の１」「１秒＝８６４００分の１」で換算された「数値」で管理されます。
これは、「時間のシリアル値」と呼ばれます。
- 例１
-
セルに「8:00」と入れると「8時0分0秒」＝「8÷24」＝「0.333333・・・」という数値に変換されます。
- 例２
-
セルに「16:20」と入れれば「16時20分0秒」＝「16÷24＋20÷1440」＝「0.68055555・・・」という数値に変換されます。
このように、エクセルでは、時間を「小数」で管理しているため、さきほどの電卓での計算と同じような計算結果のずれが起こる可能性があるのです。
演算誤差が発生する可能性があるので注意
特に、給与計算のベースとなる勤務時間の集計など、時間を「正確に」扱いたい場合、この計算結果の「ずれ」が大きな影響を与えることもあるので注意が必要です。
たとえば、下の図の計算結果に違和感を感じないでしょうか？
B4セルで「15:00と14:00の差」である「1時間」を計算しています。
そして、B5セルでは、それを10分単位で切り捨てる処理をしています。
ここで、B5セルはどうなるはずでしょうか？
「1時間」を「10分単位」で切り捨てたら、計算結果は「1時間」になるはずですよね？
でも、実際には、「50分」と誤った計算結果になっています。
もし、給与計算をするときに、このようなミスをしてしまったら致命的です。
さらに、計算結果がおかしくなるだけでなく、if関数やvlookup関数を使った判定でも意図した通りに動かない場合もあります。
（参考：小数をif関数、vlookup関数に使うときは演算誤差に注意）
24時間超の部分が切り捨てられて見える
時刻の足し算の結果、合計が24時間以上になったとき、特別な設定をしないと表示上24時間超の部分が切り捨てた状態で表示されます。
例えば、「19:00」＋「6:00」という計算をすると「1:00」と表示されてしまいます。
実は、エクセル内部では、
と計算されており、このうちの「時刻部分」だけを表示すると「1:00」となってしまうわけです。
セルの書式設定（表示形式）のユーザ定義書式を次のように変更すれば、24時間を超えた部分についても「時間」に反映させることはできるのですが、書式設定もれによるトラブルが起きがちです。
マイナスの数値が正しく表示されない
時刻の計算結果がマイナスになった場合、（オプションの指定を変更しない限り）時刻の表示形式を選んでいると、セル上「#########」と表示されてしまいます。
トラブル無く「時刻」をエクセルで扱う超簡単な解決法
このように、エクセルの標準機能を使って１つのセルに「時刻」を入れてしまうと、様々なトラブルの原因となります。
そこで、エクセルで時刻を取り扱いたいという場合、１つのセルで「時刻」を管理するのを諦めて「時間」を入れる欄と「分」を入れる欄（必要があれば「秒」を入れる欄も）を分けましょう。
「分」「秒」の表示形式
標準の状態だと「分」「秒」の入力欄に「00」、「05」というように入力しても、「0」「5」というように１桁の数字に変化してしまいます。
でも、「分」「秒」を入れる入力欄は、常に数字２桁で表示させるようにしたほうが見た目が綺麗ですよね？
そこで、書式設定の画面で「ユーザ定義書式」に「00」と指定をします。
こうしておくことで、「分」「秒」の欄が常に数字２桁で表示されるようになります。
ある時刻とある時刻の差を計算する方法
ある時刻とある時刻の「差」を計算したい場合には、時刻を全て、分換算（あるいは秒換算）して計算します。
例えば、
「9:00～18:00」までが何分間あるか（あるいは何時間あるか）を計算したいという場合には、次のような計算式を入れます。
J2セルの計算式では、
- 9:00 → 9×60＋0＝540
- 18:00 → 18×60＋0＝1080
として分単位に換算したうえで、それぞれの数値を引き算しています。
もし、最終結果が「時間」単位で欲しいのであれば、H2セルのように最後に60で割って分→時間に換算すれば目的の計算結果が得られます。
このように計算することで、小数の誤差を心配しないで計算をすることができます。
まとめ
時刻に関しては、エクセル標準の機能を使うとややこしいだけで、メリットが感じられないケースが多いです。
計算をする段階では「時間」と「分」を別々のセルに入力するようにして、エクセル標準のシリアル値は使わないようにしましょう。