13
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated at

Organization

【Excel】 日時情報の正体とよくある計算ミス

Tips「日時情報の正体」

知らないと罠にはまる日時情報のお話

Excel内部では、日時データはすべて1900年(or1904年)からの経過日数+1日として保持・計算しており、
書式によって日付形式で表現している。

日付の正体は数値である。

0faf940e233ec888431676af6b534f25.gif

1900年を1として扱う計算方式の場合

シリアル値 書式:数値(0.00) [書式:yyyy/m/d h:mm (日時)] [書式:h:mm (時)]
1 1.00 1900/1/1 0:00 0:00
1.25 1.25 1900/1/1 6:00 6:00
1.5 1.50 1900/1/1 12:00 12:00
2 2.00 1900/1/2 0:00 0:00
367 367.00 1901/1/1 0:00 0:00

シリアル値の整数部:1900年からの経過日数+1日
小数部:時刻

696d2d7123da37d9d99842190668d492.gif

※Excelでは1900年はうるう年として計算される

Microsoft が Windows 版の Excel を開発する際、当時シェアが大きかった Lotus 1-2-3 からの移行を容易にするためこれとの互換性を持たせるようにしたいと考えました。ところが Lotus 1-2-3 の日付データの仕様を調べたところ、閏年の扱いについておかしな仕様になっている事がわかったのです。
(中略)
ですから 1900 年は閏年では無いのですが、Lotus 1-2-3 は 1900 年を閏年として扱っていたのです。
そして Lotus 1-2-3 の日付のシリアル値は 1900 年 1 月 1 日を 1 としていました。
Microsoft Excel の 1900 年 2 月 29 日問題 まとめ - Hebikuzure’s Tech Diary

また、時間計算も上記のシリアル値によって行われる。

さまざまな書式で表した時間

セルの書式設定を変えることにより、同じ値でも様々な形式で表示することができる。
image.png

時間の比較および計算では内部的には以下のような処理が行われる。

計算例

image.png

【時刻比較】昼12時より朝6時は早いか?

日時情報の実体は1900年(or1904年)からの経過日数+1日であるため、大小関係は以下のようになる。

\mbox{過去の日付} < \mbox{未来の日付}\\\\
\mbox{過去の時間} < \mbox{未来の時間}

image.png

行われる計算のイメージ
  12:00 > 6:00 [書式:h:mm]
= 1900/1/0 12:00 > 1900/1/0 6:00 [書式:yyyy/m/d h:mm]
= 0.5 > 0.25 
= TRUE

【時間計算】朝6時から昼12時までの時間を求める

Excelは日時情報を1900年(or1904年)からの経過日数+1日として保持しているため、時間計算を引き算によって行うことができる。

※小数計算になるので、複雑な計算をすると誤差が発生するおそれがあるので注意。

image.png

行われる計算のイメージ
  12:00 - 6:00 [書式:[h]:mm]
= 1900/1/0 12:00 - 1900/1/0 6:00 [書式:yyyy/m/d h:mm]
= 0.5 - 0.25 
= 0.25
= 1900/1/0 6:00 [書式:yyyy/m/d h:mm]
= 6:00 [書式:[h]:mm] (日付情報が書式により省略されている)

誤った計算例

【時間計算】6時間から12時間を引く

この計算は、シリアル値に対応する日時が存在しないため、行うことができない。

image.png

行われる計算のイメージ
  6:00 - 12:00
= 0.25 - 0.5 
= -0.25
= ########## [書式:[h]:mm] (対応する日時が存在しないため書式エラー)

【給与計算】6時間 時給1000円で働いたときの給与

この計算は、Excel内部のシリアル値を無視した計算なので、意図した結果とならない。

image.png

行われる計算のイメージ
  6:00 * 1000
= 0.25 * 1000 
= 250

見かけ上6:006時00分表現されているだけで、実際に保持している値は0.25(=0.25日間)。
「1日間 = 24時間」なので、期待した結果を得るためには、以下の計算を行う必要がある。

image.png

行われる計算のイメージ
  6:00 * 24 * 1000 
= 0.25 * 24 * 1000 
= 6 * 1000
= 6000

使いたい時間単位に合わせて、数式に以下の数字をそれぞれ掛ければ求める計算結果を得ることができる。

【早見表】時間単位変換用係数

目的の計算単位 係数 説明
1 1day = 1day
24 1day = 24h
1440 1day = 24h * 60m
86400 1day = 24h * 60m * 60s

【期間集計】8月1日~3日の売上

次の計算は、書式により省略された時刻情報について考慮されていないため、意図した結果とならない。

まちがい
=SUMIFS([[集計対象]],[[日付列]],">="[[集計開始日]],[[日付列]],"<="&[[集計終了日]])

下図では、8月1日~3日の売上金額がC2~C27セルに記録されている。

  • C2~C27セルを単純にSUMで合計したC28セルの数値
  • C2~C27セルの内「2017年8月~8月3日」のみの売上金額を合計したF4セルの数値

この2つの計算結果は同じ値になっていなければならないハズなのにズレている。
image.png

理由は、H2セルに表示されている2017/8/3は書式情報が省略された2017/8/3 0:00というデータなので、8月3日の売上が殆ど集計されていないから。
image.png

正しく集計できる数式は以下。

正解
=SUMIFS([[集計対象]],[[日付列]],">="[[集計開始日]],[[日付列]],"<"&[[集計終了日]]+1)

image.png

[[日付列]],"<"&[[集計終了日]]+1とすることで、8月3日0:00の24時間後である8月4日0:00より前の期間までを集計することにより、8月3日23時59分59.9999秒のデータも正しく集計できる。

おわりに

Excelの書式機能は便利で強力ですが、見かけの値と実際の値に予期せぬギャップが生じやすく、思わぬ計算ミスを招くことが多々あります。
日時や小数が影響する計算は特に気をつけて処理して、なるべく電卓で再計算することをオススメいたします。。

Excelの罠についてよくまとまっているQiita記事を見つけたので、此方も是非ご一読を。
「電卓使って再計算しろ!」→「Excelなんだから間違うはずないでしょ!」と言いたいけど言えないExcel落とし穴 - Qiita

その他 参考リンク

以下、参考情報

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
13
Help us understand the problem. What are the problem?