Tips「日時情報の正体」
知らないと罠にはまる日時情報のお話
Excel内部では、日時データはすべて1900年(or1904年)からの経過日数+1日として保持・計算しており、
書式によって日付形式で表現している。
日付の正体は数値である。
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日
小数部：時刻
※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
また、時間計算も上記のシリアル値によって行われる。
###さまざまな書式で表した時間
セルの書式設定を変えることにより、同じ値でも様々な形式で表示することができる。
- 数値の日付または時刻として書式設定 - support.office.com
- 日付をお好みの形式に設定する - support.office.com
- Excel で 24 時間を越える時間の合計を計算する方法 - support.office.com
時間の比較および計算では内部的には以下のような処理が行われる。
計算例
###【時刻比較】昼12時より朝6時は早いか？
日時情報の実体は1900年(or1904年)からの経過日数+1日であるため、大小関係は以下のようになる。
\mbox{過去の日付} < \mbox{未来の日付}\\\\
\mbox{過去の時間} < \mbox{未来の時間}
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日として保持しているため、時間計算を引き算によって行うことができる。
※小数計算になるので、複雑な計算をすると誤差が発生するおそれがあるので注意。
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時間を引く
この計算は、シリアル値に対応する日時が存在しないため、行うことができない。
6:00 - 12:00
= 0.25 - 0.5
= -0.25
= ########## [書式：[h]:mm] (対応する日時が存在しないため書式エラー)
###【給与計算】6時間 時給1000円で働いたときの給与
この計算は、Excel内部のシリアル値を無視した計算なので、意図した結果とならない。
6:00 * 1000
= 0.25 * 1000
= 250
見かけ上6:00や6時00分と表現されているだけで、実際に保持している値は0.25（=0.25日間）。
「1日間 = 24時間」なので、期待した結果を得るためには、以下の計算を行う必要がある。
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つの計算結果は同じ値になっていなければならないハズなのにズレている。
理由は、H2セルに表示されている2017/8/3は書式情報が省略された2017/8/3 0:00というデータなので、8月3日の売上が殆ど集計されていないから。
正しく集計できる数式は以下。
=SUMIFS([[集計対象]],[[日付列]],">="[[集計開始日]],[[日付列]],"<"&[[集計終了日]]+1)
[[日付列]],"<"&[[集計終了日]]+1とすることで、8月3日0:00の24時間後である8月4日0:00より前の期間までを集計することにより、8月3日23時59分59.9999秒のデータも正しく集計できる。
#おわりに
Excelの書式機能は便利で強力ですが、見かけの値と実際の値に予期せぬギャップが生じやすく、思わぬ計算ミスを招くことが多々あります。
日時や小数が影響する計算は特に気をつけて処理して、なるべく電卓で再計算することをオススメいたします。。
Excelの罠についてよくまとまっているQiita記事を見つけたので、此方も是非ご一読を。
「電卓使って再計算しろ！」→「Excelなんだから間違うはずないでしょ！」と言いたいけど言えないExcel落とし穴 - Qiita
その他 参考リンク
以下、参考情報