PR

謎8.時間の引き算で、結果が「#####」に

【現象】 マイナスの時間を計算できない

⇒【解説】 シリアル値を“Mac版仕様”に変えれば可能に

 「4:00-3:50=0:10」のように、時間の引き算をしてその差を求めたいことがある。だがWindows版のExcelは「4:00-4:30=-0:30」のように、結果がマイナスになるとき、標準で「#####」というエラー表示となってしまう(図1)。

図1 セルに入力した時間を引き算して、「マイナス30分」のように差を求めたいことがある。だが、Excelは標準で「-0:30」のように時間をマイナスで表示できない。引き算の結果がマイナスになると「#####」というエラー表示になる

 Excelは日付と同様、「シリアル値」という仕組みで時間を管理している(図2)。日付のシリアル値は1日に1ずつ増える整数となっているので、時間も同じく1日(24時間)が1となるように管理している。すなわち、1時間が0.0416666…、6時間が0.25、12時間が0.5のように、時間は小数で管理されている。

図2 Excelは時間を、1日を1とする「シリアル値」の仕組みを使って計算する。このシリアル値は、厳密には“時間”ではなく“時刻”を管理する仕組みなので、マイナスの値が扱えない。そのため、Windows版Excelの標準設定では、マイナスの時間がエラーとなる

 しかしながら、シリアル値が管理しているのは、厳密には“時間”ではなく“時刻”である。「0:00」(午前0時)から始まって24時間後の「24:00」には表示が「0:00」に戻るのがその証拠。「24:00」を超える“時間”を表示させるには、セルの表示形式に「ユーザー定義」を選んで「[h]:mm」という特別な書式記号を指定しなければならない。この書式記号も、Excel 5.0で初めて使えるようになったものだ。

 したがって、Excelで時間の計算ができるのは、本来は時刻の管理に使うシリアル値を、便宜上、応用しているだけのこと。そのため、マイナスの時間を計算できないといった限界も生じる。

 ところが、時間のマイナス表示を実現する裏技がある。Excelのオプション画面にある「1904年から計算する」という項目をオンにする方法だ(図3)。これは、シリアル値の仕様をMac版Excelに合わせるためのオプション。シリアル値の起点が、1900年ではなく1904年になるように変更する。シリアル値の0を、1904年1月1日とする仕様だ。このMac版仕様に変えると、Windows版でもマイナスの時間を表示できる(図4)。

 これまた不可解な現象で理由は定かではないが、マイクロソフト自身もマイナスの時間を表示させる方法として紹介しているので、利用して問題はないだろう。ただし、シリアル値の起点が変わることから、入力済みの日付が4年と1日ずれてしまう点には注意が必要だ。

図3 実は、時間のマイナス表示を可能とする裏技がある。Excel 2007では「Officeボタン」のメニューの「Excelのオプション」、Excel 2010では「ファイル」タブの「オプション」を選び、開いた画面の「詳細設定」にある「1904年から計算する」にチェックする。Excel 2003以前では、「ツール」メニューの「オプション」を選び、開いた画面の「計算方法」タブで設定する
図4 時間のマイナス表示が可能になった。ただし、入力済みの日付は4年と1日ずれてしまうので注意。この設定は、ブック(ファイル)単位で保存される