ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.013

《条件付き書式》 1904年オプション使用せずに、負の時刻を計算に使用するには

2003/2007/2010/2013

負の時刻を扱った計算をしたい、という場合に、[Excel のオプション]の設定[1904年から計算する]チェック ボックスをオンにする方法があります。
簡単にできるのですが、ただこの設定の問題点は、そのブックに日付が入力されていた場合に4年と1日ズレてしまうということです。
詳しくはヘルプ「負の時刻を入力し、計算に使用するには(1904年オプション使用)」の「ヘルプmemo」をご参照ください。

そこで今回は、この1904年オプションの設定を使用せずに、負の時刻を計算できないかという課題に取り組んだものを1例ご案内します。

まずおさらいです。
時刻のシリアル値は24時間を 0 から 1未満の小数値で表したもので、お昼の12時はど真ん中ですので、数値のシリアル値は 0.5 、朝6時は 0.25、夕方6時は 0.75 となります。

そして24時は 1 となりますが、これは1日、整数値は日のシリアル値になります。
24時以上の時刻のシリアル値はまた 0 に戻り小数値となります。
例えば、30:00 と入力したとき、そのまま表示されますが、表示形式を時刻に設定すると、6:00 になります。

こういった時刻表示の特徴を借りて、正の時刻は通常どおり、負の時刻の場合は 100日(2400時間)をプラスした時刻を入力することに決めて、条件付き書式で表示形式を負の時刻に設定します。
例えば、「-6:00」と表示したい場合、2400時間を足した「2406:00」と入力します。

既定では「2406:00」と入力した場合、表示形式を[時刻]にすると「6:00」、表示形式を[標準]にすると「100.25」と表示されますが、条件付き書式によって、100(2400時間)以上の値が入力された場合、表示形式を(強制的に)負の時刻にするようにします。

では、その条件付き書式の設定です。

時刻を入力するセル(正負どちらも)を選択し、[ホーム]タブ-[スタイル]グループ-[条件付き書式]▼-[ルールの管理]をクリックし、[条件付き書式ルールの管理]ダイアログ ボックスを開きます。

条件付き書式

[新規ルール]ボタンをクリックし、[新しい書式ルール]ダイアログ ボックスを開き、[指定の値を含むセルだけを書式設定]を指定します。
まずは正の時刻の表示形式の設定で、次のように指定します。
  [セルの値][次の値より小さい]「100」
続けて[書式]ボタンをクリックし、[セルの書式設定]ダイアログ ボックスの[表示形式]タブで、[分類]を[ユーザー定義]に指定し、[種類]ボックスに「h:mm」と指定します。

条件付き書式 正の時刻

正の時刻のルールが作成できました。続けて負の時刻の設定です。
[条件付き書式ルールの管理]ダイアログ ボックスで[新規ルール]ボタンをクリックし、同様に次のような条件および書式を設定します。
次のセルのみを書式設定:
  [セルの値][次の値以上]「100」
[書式]ボタンより先ほどと同様に[表示形式]タブで[ユーザー定義]を指定し、[種類]ボックスに「-h:mm」と指定します。

条件付き書式 負の時刻

以上の条件付き書式の設定で、正の時刻は通常どおりの入力、負の時刻で「-6:00」としたい場合は、「2406:00」と100日(2400時間)をプラスした時刻で入力します。

負の時刻入力と条件付き書式

上図のセルD列には、時刻を数値化したものです。(使用した式はE列)
時刻の数値化は、8:30 の場合 8.5 といった数値データで表す方法です。
今回、セルの見た目ではわかりづらいので、参考にしてください。

ところでなぜ、100日(2400時間)としたかというと、24(時間)の倍数であることと入力がわかりやすいといった理由です。
さてここまでが、負の時刻入力のための説明です。

今度は、時刻の計算をしたいと思います。
B列に時刻が入力されていて、時刻の合計値を求めたいと思います。
もちろん入力値には負の時刻を表す「2406:00」などと入力した値も含まれ、答えは合計時刻(24時間を超えた時刻)になる可能性もあるとします。
(ただし、入力する時刻は ±24:00 以内、合計時刻は ±100時間を超えないとする)

では、1つずついきましょう。

正の時刻の合計は、100未満の値の合計で、次の式となります。(セルB8)

  =SUMIF(B2:B6,"<100",B2:B6)

負の時刻の合計は、100以上の値の合計です、次の式となります。(セルB9)

  =SUMIF(B3:B6,">=100",B3:B6)

しかし、負の時刻は見た目はよいのですが、実際毎度100日(2400時間)が個数分加算されています。
セルD9の数値化された値をみていただければと思います。

とすると、2400時間、つまり100(日)の倍数を引いた半端な値が、実際の負の時刻の合計ということです。
これは、負の合計値(セルB9)を100で割った余りが相当しますので、次の式で求めることができます。(セルB10)

  =MOD(B9,100)

正の時刻負の時刻合計

さて正の時刻、負の時刻の合計が求めることができました。
セルB10の負の時刻の合計は2400時間(100日)を足しているものではないので、条件付き書式が設定されていても、マイナスは表示されません。

これらの時間の負の時刻の合計を求めてみましょう。安易に足し算はできません。
IF関数を使用して、正の時刻合計が負の時刻合計より大きい場合、「正時刻-負時刻」の通常引き算、負の方が大きい場合は「負時刻-正時刻」の引き算の値に2400をプラスし、さきほどの条件付き書式にによって負の時刻の値にします。(セルB12)

  =IF(B8>B10,B8-B10,B10-B8+2400)

合計値が24時を超えない場合は、これでいけるかと思います。

合計値が24時を超える場合は、正の時刻および負の時刻の条件付き書式の書式の設定を、それぞれ「[h]:mm」(正)、「-[h]:mm」(負)と設定します。

少しややこしいではありますが、こういった方法で負の時刻を扱い計算する、また別のシートやセルで普通の日付を1904年スタート換算せずに通常どおりの入力ができるようになります。

なお、時間の数値化については ヘルプ「時刻の「8:30」を「8.5」と数値に変換する」を、24時以上の時刻の表示については ヘルプ「《表示形式》 24時間を越えた経過時間を表示するには(時刻の合計について)」に詳しくありますのでご参照ください。

twitter hatena line pocket

関連ヘルプ

《保護》 一部のセルだけ変更可能にし、他のセルは変更できないように保護したい(変更不可セルが大部分)
《保護》 一部のセルを変更できないように保護したい(大部分のセルが変更可能)
《入力規則》 セルに日本語入力のオン/オフや全角/半角などを設定する
《保護》 数式バーにセルの数式や内容が表示されないようにしたい
《入力規則》 セルに▼が表示され、ドロップダウン リストから入力できるようにするには
《入力規則》 入力規則のリスト入力で、別シートのリスト項目を表示するには
《名前》 特定のセル範囲に名前を定義する
《名前》 定義された名前を編集、削除するには
《入力規則》 重複データが入力できないよう、メッセージを表示し規制する
《条件付き書式》 土日の列または行に色をつける
《入力規則》 入力規則でリストの設定を行なったが、リストが表示されない
《保護》 非表示にしているシートを再表示できないようにする