エクセル関数応用
時間計算で困ったときの確実な対処方法

日付は、1900/1/1を1として、そこからの経過日数であらわされます。
つまり、日付のシリアル値とは、1900/1/1の何日後かの数値ということになります。
雑学話としては、「エクセル雑感.エクセルの日付のお話」をご覧ください。
1日が1なので、通常の足し引き計算はなんら問題がありませんので、あまり意識することはないと思います。
年月の表記においては各種問題もありますが、これらに対する関数は豊富に存在しています。
時刻は、1日(24時間)を1として、その分数（小数）としてあらわされます。
つまり、1時は1/24、2時は2/24です。
同様に分は、さらに1/60となります。
つまり、1分は1/24/60、2分は2/24/60です。
ですので、2014/5/6 14:30は、
1900/1/1の41765日後なので、=41765+14/24+30/24/60=41765.6041666667
となります。
そして、Excelは整数部分を日付、小数以下を時刻として認識します。
従って、通常の書式「hh」では小数部分の時刻のみを取り出します。
整数部分の日付も含めて時刻に(つまり24hを超えて表示)する場合は、
「[hh]」といった書式が必要になります。
ここで、日付・時刻とは、特に時刻とは、時計の針が指すその時刻になります。
決して時間(時刻と時刻の間隔)ではないということです。
したがって、マイナスは決して扱えません、この事を理解していない場合が非常に多く見受けられます。
シリアル値を日付・時刻の表示形式にする各種指定方法は、「エクセル基本操作.日付、時刻に関するあれこれ」
個別の関数等については、以下を参考にしてください。

Excelに限ったことではなく、PC、いやコンピューター全般の問題点として小数の表現方法が存在します。
詳しい解説は、ここでは省きますが、
コンピューターが全て2進数で表現されているため、表現できない小数点数があったり、
計算過程で誤差が生じてしまうというものです。
VBAの小数以下の演算誤差について

これらの事は、Microsoftの各種ページでも解説されていますので、興味があれば読んでみてください。
Excel で浮動小数点演算の結果が正しくない場合がある
浮動小数点演算で丸め誤差を修正する方法
ここでは、この問題により起こる時間計算の誤差の対処方法を記します。
さて、いよいよ本題に入ります。
よくある（かどうかは別にして）出退勤の計算を例に話を進めます。

・出勤時刻
・退勤時刻
・休憩時間
・所定時間
ここから、遅刻・早退・残業時間を求める場合です。
まあ遅刻・早退は、望ましくないので（笑）、残業時間の算出を例にとりましょう。
・出勤時刻　8:15
・退勤時刻　17:15
・休憩時間　1:00
・所定時間　8:00
残業時間=17:15-7:15-1:00:8:00=0:00
シート上で計算してみてください、正しく計算できています。

そう、普通は何も問題ないように思えます。
では、その下に以下のようにいれて、さらにドラッグコピーをします。

この2行分のセル範囲を選択しドラッグコピーすると、

8:25のところから、なにやらおかしくなっています。
手入力で、8:25と入れなおすと、正しく0:00となります。
これは、ドラッグコピーで作成された時刻が、手入力の時刻とわずかに違いがあるということです。
手入力する前の8:25のシリアル値は、0.350694444444445
手入力した後の8:25のシリアル値は、0.350694444444444
わずかに、最後で違いがあります。
この違いは、秒まで表示しても時刻形式では差異が見て取れません。
つまり、いくら眺めていても、この問題は解決しませんし、
全て手入力し直していたのでは、何のためのExcelだかわからなまなってしまいます。
この問題は、何もドラッグコピー時だけの問題ではなく、
何らかの計算結果を時刻表示しているときには、常に付きまとう問題となっています。
ここでは、簡単に再現でき、かつ、理解しやすいようにドラッグコピーで説明しました。
分までなら、あまり計算誤差は発生しませんが、秒まで計算すると頻発します。

右下のFALSEは、2つ左の0:09:00と、１つ左の0:09:00をEXACT関数で比較した結果です。
同じ数値ではないということになります。
秒までの計算式では、むしろ一致することの方が少ないくらいに違いが発生します。
では、本当の本題です。
このような問題を解決するための確実な方法です。

計算をExcelに任せて、小数点数の2進数で計算するから問題なのです。
したがって、要は人間が頭のなかで行っているのと同様に、10進計算すればよいのです。
そのためには、まずは10進計算できる数値に変換してから計算します。
つまり、小数点以下のシリアル値ではなく、整数としての独自シリアル値として扱うのです。
話を分かりやすくするために、分単位で話を進めます。
見た目で、
8:25
とあるなら、これをそのまま1分を1とした数値に変換します。
つまり、8*60+25=505にします。
その方法としては、何通りかありますが、
元のシリアル値が、1日=24時間=1であることから、
A1セルに8:25が入っているとして、
A1*60*24=505となります。
※計算結果のセルは表示形式を標準に変更してください。
*24で1時間が1となり、*60で1分が1となります。
また、
少しややこしくなりますが、基本単位で割れば、基本単位を1とした数値になりますので、
A1/Time(0, 1, 0)=505
これは、
8:25/(1/24/60)=8:25*60*24=505
両者は同じことになります。
さて、これで一見よさそうにみえますし、まずは問題は無いでしょう。
しかし、小数以下の誤差が問題であるといっているのに、
この計算式では、やはり誤差が出てしまうのではないかと不安があります。
※この計算式で誤差がでてしまうかどうかの検証はしていません、

さらには、実際のセル値が、
8:25:58
のように秒数が入ってしまっていて、見た目が8:25となっている場合は、
結果が506となってしまいます。
もちろん、小数以下を表示すれば、
505.983333333
このような数値となっているわけです。
では、人間の見た目通りの計算をしたい場合は、どうしたら良いかです。
それには、Excelで用意されている関数を使います。
HOUR(A1)*60+MINUTE(A1)=505
この計算式なら、実際は8:25:59とセルにはいっていて、見た目だけ8:25となっていても問題ありません。
また、こんな計算方法もあります。
TEXT(G18,"hh")*60+RIGHT(TEXT(G18,"hhmm"),2)
計算式の意味としては理解しやすいかもしれませんが、やはり前者の計算式の方が良いと感じます。
ちなみに、TEXT関数で分だけ取り出そうとして、"mm"の指定は月を取り出してしまいます。
（このTEXT関数で、分のみ指定する方法が無いのは、かなり痛い仕様と言えます）
さて、この1分を1とした数値に変換できたら、後は普通に足し引きして問題ないことは説明が不要でしょう。
でも、最後は、分かりやすいように時間表示にしたくなります。
計算結果が、75、これでは分かりずらい、やはり、1:15のように表示しておきたいところです。
それには、既に登場している、TIME関数を使えば一発で済みます。
B1セルに1分を1とした数値に変換後の計算結果が入っているとして、
TIME(0, B1, 0)
これで、時刻表示に自動変換されます。
TIME関数の引数には、60を超える分や秒を指定しても、正しく変換してくれます。
これで、全ての時間計算に関する問題は解決します。
ちなみに、秒で計算する場合は、
HOUR(A1)*60*60+MINUTE(A1)*60+SECOND(A1)=30359
TIME(0, 0, B1)
このようにすれば良い事になります。
実際には、実務での残業時間計算で1分単位で時間計算することは稀でしょう。
（労基署の言い分としては、1分単位でやりなさいとなりますが、その辺は専門家に委ねるとして）
例えば15分単位にする場合、
出勤時刻は15分毎に切り上げ、つまり、8:25は8:30
退勤時刻は15分単位に切り捨て、つまり、17:35は17:30
このようにしてから、残業時間を計算するというのは昔からよくあることです。
（これで労基署が許してくれるかは別の話ですよ）
このような場合は、
CEILING
FLOOR

これらの関数を使って処理してから、足し引き計算をすることになります。
ちなみに、出勤時刻は15分毎に切り上げた分単位にする場合は、
CEILING(HOUR(A1)*60+MINUTE(A1),15)
このようになります。
元の時刻を15分単位にしてからなら、
CEILING(A1,TIME(0,15,0))
としてから、分に変換しても良いですね。

TIME関数の引数は、
ヘルプにも書かれていますが、「0～32767の範囲で指定します。」
つまり、秒数なら概ね9時間以上となる場合は制限値を超えてしまいます。
そのような場合は、時と合わせて計算する工夫が必要になります。
A1セルに秒数が入っているとして、
単純に、
=TIME(0,0,A1)
とすると、9時間を超える秒数の場合は、#NUMとなってしまいます。
そこで、
A2=FLOOR(A1,60*60)
これは、数値から、時間部分だけを取り出します。
そして、
A3=A1-A2
これは、時間を引いているので、残りの秒数になります。
上記をTIME関数に入れて、
=TIME(A2/60/60,0,A3)
これで計算できます。
(途中の計算方法は、色々な関数の使い方があります)
また、そもそも、TIME関数は24時間を超える値を返しません。
24時間以上は、DATE関数の範囲となります。
この場合も、日(24時間)以上は別途取り出す工夫が必要になります。
上記のように、A1セルに秒数が入っているとして、
A2=FLOOR(A1,60*60*24)
これは、数値から、日部分だけを取り出します。
そして、
A3=A1-A2
これは、数値から、残りの24時間未満を計算しています。
=A2/60/60/24
これで、日数に戻せますし、日数に*24で時間、*24*60で分数に戻せます。
24時間未満の部分をTIME関数で処理して、
24時間を超える部分は、別途加算するようにします。
ちなみに、先に書いているように、1日=1ですので、
シリアル値計算としては、日数は単純に加算できます。

時間計算に限らないのですが、まずはExcelの基本関数をうまく使うことを考えましょう。
質問のほとんどは、基本関数の組み合わせでなんとかなります。
時間計算なら、
TIME
TIMEVALUE
HOUR
MINUTE
SECOND
CEILING
FLOOR
まずは、これらの関数を使って処理できないかを工夫してみてください。
大抵(ほぼ全て)は、これらの関数と、文字列関数の一部を使うだけで解決できます。

同じテーマの記事
新着記事
アクセスランク