エクセル関数超技 | 時間計算で困ったときの確実な対処方法 | Excel関数の解説、関数サンプルと高等テクニック



最終更新日:2015-08-25

時間計算で困ったときの確実な対処方法


Excelで非常に多い質問の一つが時間計算です、

日付・時刻はエクセルのデータ形式の一つですが、

特に時間計算は面倒で対処方法も書籍・ネットでもあまりみかけません。

日付に関しては便利な関数も用意されていますし、

なにより、1日が数値の1(これをシリアル値と言います)で表されているので、大抵はなんとかなっているようです。


対して、時刻のシリアル値は小数以下になっていて、かつ、関数もあまり用意されていない為にどうしてよいか分からなくなってしまうことが多いのでしょう。

以下で、確実に時間計算を行う鉄板の方法を紹介します。

まずは、その前に、事前知識が必要になります。

ちょっと難しい話になりますが、さくっと読み流してみてください。

・日付・時刻のシリアル値とは

日付は、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における小数の問題

Excelに限ったことではなく、PC、いやコンピューター全般の問題点として小数の表現方法が存在します。
詳しい解説は、ここでは省きますが、
コンピューターが全て2進数で表現されているため、表現できない小数点数があったり、
計算過程で誤差が生じてしまうというものです。
これらの事は、Microsoftの各種ページでも解説されていますので、興味があれば読んでみてください。
Excel で浮動小数点演算の結果が正しくない場合がある
浮動小数点演算で丸め誤差を修正する方法

ここでは、この問題がある為に発生する時間計算の誤差の対処方法を記します。

さて、いよいよ本題に入ります。
よくある(かどうかは別にして)出退勤の計算を例に話を進めます。

・どんな時に問題が発生するか

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

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

出勤時刻 退勤時刻 休憩時間 所定時間 残業時間
8:15 17:15 1:00 8:00 0:00
8:16 17:16 1:00 8:00 0:00

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

出勤時刻 退勤時刻 休憩時間 所定時間 残業時間
8:15 17:15 1:00 8:00 0:00
8:16 17:16 1:00 8:00 0:00
8:17 17:17 1:00 8:00 0:00
8:18 17:18 1:00 8:00 0:00
8:19 17:19 1:00 8:00 0:00
8:20 17:20 1:00 8:00 0:00
8:21 17:21 1:00 8:00 0:00
8:22 17:22 1:00 8:00 0:00
8:23 17:23 1:00 8:00 0:00
8:24 17:24 1:00 8:00 0:00
8:25 17:25 1:00 8:00 ##########
8:26 17:26 1:00 8:00 0:00
8:27 17:27 1:00 8:00 ##########
8:28 17:28 1:00 8:00 0:00
8:29 17:29 1:00 8:00 ##########
8:30 17:30 1:00 8:00 ##########

8:25のところから、なにやらおかしくなっています。
手入力で、8:25と入れなおすと、正しく0:00となります。
これは、ドラッグコピーで作成された時刻が、手入力の時刻とわずかに違いがあるということです。
手入力する前の8:25のシリアル値は、0.350694444444445
手入力した後の8:25のシリアル値は、0.350694444444444
わずかに、最後で違いがあります。
この違いは、秒まで表示しても時刻形式では再が見て取れません。

つまり、いくら眺めていても、この問題は解決しませんし、
全て手入力し直していたのでは、何のためのExcelだかわからなまなってしまいます。
この問題は、何もドラッグコピー時だけの問題ではなく、
何らかの計算結果を時刻表示しているときには、常に付きまとう問題となっています。
ここでは、簡単に再現でき、かつ、理解しやすいようにドラッグコピーで説明しました。

分までなら、あまり計算誤差は発生しませんがも秒まで計算すると頻発します。

開始時刻 0:08:25 0:23:43  
終了時刻 0:17:25 0:32:43  
経過時間 0:09:00 0:09:00 FALSE

右下のFALSEは、2つ左の0:09:00と、1つ左の0:09:00を比較した結果です。
同じ数値ではないということになります。
秒までの計算式では、むしろ一致することの方が少ないくらいに違いが発生します。

では、本当の本題です。
このような問題を解決するための確実な方法です。

・確実な時間計算方法

計算をExcelに任せて、小数点数の2進数で計算するから問題なのです。
したがって、要は人間が頭のなかで行っているのと同様に、10進計算すればよいのです。
そのためには、まずは10進計算できる数値に変換してから計算します。
つまり、小数点以下のシリアル値ではなく、整数としての独自シリアル値として扱うのです。
話を分かりやすくするために、分単位で話を進めます。

見た目で、
8:25
とあるなら、これをそのまま1分を1とした数値に変換します。
つまり、8*60+25=505にします。
その方法としては、何通りかありますが、
元のシリアル値が、1日=24時間=1であることから、
A1セルに8:25が入っているとして、
A1*60*24=505となります。
※計算結果のセルは表示形式を標準に変更してください。
*60で1時間が1となり、*60で1分が1となります。
また、
少しややこしくなりますが、基本単位で割れば、基本単位を1とした数値になりますので、

A1/Time(0, 1, 0)=505
これは、
8:25/(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関数の制限について

TIME関数の引数は、
ヘルプにも書かれていますが、「032767の範囲で指定します。」
つまり、秒数なら概ね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


まずは、これらの関数を使って処理できないかを工夫してみてください。
大抵(ほぼ全て)は、これらの関数と、文字列関数の一部を使うだけで解決できます。

いろいろと時間計算が面倒なことはご理解いただけたかと思います。
本当の意味での結論としては、
細かい時間にとらわれない仕事をしたいものだということです。




同じテーマ「エクセル関数超技」の記事

VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
複数条件の合計・件数
入力規則のリストを、追加・削除に自動対応で作成

新着記事 ・・・新着記事一覧を見る

空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
6.変数とデータ型(Dim)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.定数と型宣言文字(Const)|ExcelマクロVBA入門



  • >
  • >
  • >
  • 時間計算で困ったときの確実な対処方法

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。




    ↑ PAGE TOP